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.



Sun, 7 Oct 2001 23:20:10 -0400
"Kelly Harmon" <kelly.harmon@byers.com>
Accessing Database files on a "read-only" medium...like a CD.
>
> 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:
>


Okay...I made a CD of a reasonably sized database....about 100MB in 3
tables.  Then I deleted the original database files from the appropriate
directory and replaced the files with symbolic links to the files on the CD.

THEN I cranked up the modified PostgreSQL code and tried it out and it
worked.  I could run various select statements with no obvious troubles.

SO...it is possible to run a database off of a CD...with a relatively minor
code change.  Though, of course, you have to have to trick Postgres into it.

And Postgres is not very forgiving if it ever figures out that it's been
tricked...that definitely needs to be worked out.

I'm very interested in hearing any other "gotcha's" that y'all may know of.

Thanks!



Mon, 8 Oct 2001 03:46:15 -0400
"Kelly Harmon" <kelly.harmon@byers.com>
Re: Accessing Database files on a "read-only" medium...like a CD.
>
> And Postgres is not very forgiving if it ever figures out that it's been
> tricked...that definitely needs to be worked out.
>

What I'm sort of leaning towards is to catch these attempted writes early
on, and not have to deal with a lot of cleaning up after the fact.

I guess Postgres already supports the concept of a "read-only" database from
a user permissions perspective, right?  So maybe take advantage of that
existing functionality?





Mon, 8 Oct 2001 04:00:44 -0400
"Kelly Harmon" <kelly.harmon@byers.com>
Re: Accessing Database files on a "read-only" medium...like a CD.

I wonder if you shut down the postmaster and restart if that would make
it work again.  I can't imagine where it would store table size
information if the area is read-only.  Adding data, full vacuum, restart
postmaster should allow read-only databases.

---------------------------------------------------------------------------

> 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.
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 12 Oct 2001 14:21:03 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Accessing Database files on a "read-only" medium...like