Accessing Database files on a "read-only" medium...like a CD.
I'm curious as to whether anybody has gotten PostgreSQL to work with a
database that lives on some sort of read-only medium...like a CD.
I've looked around in the newsgroups and I've seen a comment by Bruce
Momjian that it can't currently be done...and I've seen a different comment
by Tom Lane that he thought that it probably could...So...I dunno.
I've taken a database and set the read-only attributes on its files and
tried to access it via psql...and couldn't...it complained about not being
able to open pg_class.
SO...I dug around through the code a little and found where the error was
coming from and changed the code so that if the open attempt with O_RDWR
fails, the code tries again with O_RDONLY. This was in md.c...in the mdopen
function.
This did work....I was then able to open the database and do queries and
whatnot. Trying to insert into the table didn't give any errors...until I
tried to select the record back out, at which time it started giving me
errors such as:
ERROR: cannot write block 7548 of pole: Permission denied
At that point, it seems that your screwed...in that even if you shut down
postgres and restart it, somewhere it knows that that database has data that
needs to be written to disk, and it refuses to continue until it does so.
OTHER than that one problem...Is anyone aware of any other problems that my
change might cause? To be really useful, it would be necessary to go
through and make additional changes so that it can recover from a failed
write to the "read-only" database. But it seems like it would be okay as
long as you carefully avoid changing the database.
Kelly Harmon <kelly.harmon@byers.com> wrote in message news:9pr7f7$k0j$1@news.tht.net...
SO...I dug around through the code a little and found where the error was
coming from and changed the code so that if the open attempt with O_RDWR
fails, the code tries again with O_RDONLY. This was in md.c...in the mdopen
function.This did work....I was then able to open the database and do queries and
whatnot. Trying to insert into the table didn't give any errors...until I
tried to select the record back out, at which time it started giving me
errors such as:ERROR: cannot write block 7548 of pole: Permission denied
At that point, it seems that your screwed...in that even if you shut down
postgres and restart it, somewhere it knows that that database has data that
needs to be written to disk, and it refuses to continue until it does so.
Isn't it the WAL who 'remembers' this info?
--
Serguei A. Mokhov
Import Notes
Resolved by subject fallback
"Serguei Mokhov" <sa_mokho@alcor.concordia.ca> writes:
Kelly Harmon <kelly.harmon@byers.com> wrote in message news:9pr7f7$k0j$1@news.tht.net...
At that point, it seems that your screwed...in that even if you shut down
postgres and restart it, somewhere it knows that that database has data that
needs to be written to disk, and it refuses to continue until it does so.
Isn't it the WAL who 'remembers' this info?
Both WAL and pg_log *must* be on writable media, so there's really no
chance of putting the whole of a $PGDATA tree onto a CD. However one
could imagine putting individual databases (or even individual tables)
onto CD. One thing you'd have to watch out for is that Postgres
may try to update on-row commit status bits even during a read-only
operation such as SELECT. The best way to deal with that would be to
VACUUM the table or database before moving it to read-only storage.
VACUUM would leave the status bits all set correctly.
We've talked repeatedly about implementing a notion of tablespaces
to allow DBAs to exercise more control over where tables are kept.
Maybe it'd make sense to allow tablespaces to be marked read-only, too.
regards, tom lane