Storing database in WORM devices

<GGoshen@axsone.com> writes:

I would like to store the complete database into a WORM device (Write Once
Read Many). I would like to access this database directly from the WORM
device and perform read only SQL statements against this device.

Does anyone have such installation, or can determine if this is possible?

AFAIK Postgres will not currently run on a read-only filesystem.

-Doug

Why? Any specific reason that you are aware of ?
Are there any writes done to the database when read only SQL statements are issued?

-----Original Message-----
From: Douglas McNaught [mailto:doug@mcnaught.org]
Sent: Wednesday, May 11, 2005 2:51 PM
To: Goshen, Galit
Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Storing database in WORM devices

<GGoshen@axsone.com> writes:

I would like to store the complete database into a WORM device (Write Once
Read Many). I would like to access this database directly from the WORM
device and perform read only SQL statements against this device.

Does anyone have such installation, or can determine if this is possible?

AFAIK Postgres will not currently run on a read-only filesystem.

-Doug

I think simply initialising the system causes writes in the system
tables and the WAL...

I'm sure someone more knowledgeable can chime in.

Alex. Turner
netEconomist

Isn't there some way to trick PostgreSQL with a RAM disk, like for the WAL?

Rick

pgsql-admin-owner@postgresql.org wrote on 05/11/2005 02:31:55 PM:

Why? Any specific reason that you are aware of ?
Are there any writes done to the database when read only SQL
statements are issued?

-----Original Message-----
From: Douglas McNaught [mailto:doug@mcnaught.org]
Sent: Wednesday, May 11, 2005 2:51 PM
To: Goshen, Galit
Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Storing database in WORM devices

<GGoshen@axsone.com> writes:

I would like to store the complete database into a WORM device (Write

Once

Read Many). I would like to access this database directly from the WORM
device and perform read only SQL statements against this device.

Does anyone have such installation, or can determine if this is

possible?

I would like to clarify something.
I intend to create the database on a re-writable device (not WORM). At some point, when I no longer want to add/modify the database, I plan to copy it to the WORM device. Then I would like to be able to access the database on the WORM device for reading purposes only.

I think that the pg_listener catalog is being written for any user connection. I am not sure whether I could store the system catalogs separated from the application table spaces.

-----Original Message-----
From: Alex Turner [mailto:armtuk@gmail.com]
Sent: Wednesday, May 11, 2005 3:41 PM
To: Goshen, Galit
Cc: doug@mcnaught.org; pgsql-general@postgresql.org;
pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Storing database in WORM devices

I think simply initialising the system causes writes in the system
tables and the WAL...

I'm sure someone more knowledgeable can chime in.

Alex. Turner
netEconomist

If you simply put your database tables in their own tablespace, then
move that tablespace to a WORM device, I can't see why that wouldn't
work as long as you keep all the system tables etc.. on the regular RW
tablespace

Alex Turner
netEconomist

Tom Lane suggested a vacuum freeze (? or something like that) for archival
read only data. I got the impression the template databases are freeze
dried for freshness (good to the last bit?) That feature might help as
well in the transition from read-write to read-only.

Rick

Alex Turner
<armtuk@gmail.com

To

Sent by: "GGoshen@axsone.com"
pgsql-admin-owner <GGoshen@axsone.com>
@postgresql.org cc
doug@mcnaught.org,
pgsql-general@postgresql.org,
05/11/2005 03:02 pgsql-admin@postgresql.org
PM Subject
Re: [ADMIN] [GENERAL] Storing
database in WORM devices
Please respond to
Alex Turner
<armtuk@gmail.com

If you simply put your database tables in their own tablespace, then
move that tablespace to a WORM device, I can't see why that wouldn't
work as long as you keep all the system tables etc.. on the regular RW
tablespace

Alex Turner
netEconomist

On 5/11/05, GGoshen@axsone.com <GGoshen@axsone.com> wrote:

I would like to clarify something.
I intend to create the database on a re-writable device (not WORM). At

some point, when I no longer want to add/modify the database, I plan to
copy it to the WORM device. Then I would like to be able to access the
database on the WORM device for reading purposes only.

I think that the pg_listener catalog is being written for any user

connection. I am not sure whether I could store the system catalogs
separated from the application table spaces.

-----Original Message-----
From: Alex Turner [mailto:armtuk@gmail.com]
Sent: Wednesday, May 11, 2005 3:41 PM
To: Goshen, Galit
Cc: doug@mcnaught.org; pgsql-general@postgresql.org;
pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Storing database in WORM devices

I think simply initialising the system causes writes in the system
tables and the WAL...

I'm sure someone more knowledgeable can chime in.

Alex. Turner
netEconomist

On 5/11/05, GGoshen@axsone.com <GGoshen@axsone.com> wrote:

Why? Any specific reason that you are aware of ?
Are there any writes done to the database when read only SQL statements

are issued?

-----Original Message-----
From: Douglas McNaught [mailto:doug@mcnaught.org]
Sent: Wednesday, May 11, 2005 2:51 PM
To: Goshen, Galit
Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Storing database in WORM devices

<GGoshen@axsone.com> writes:

I would like to store the complete database into a WORM device (Write

Once

Read Many). I would like to access this database directly from the

WORM

device and perform read only SQL statements against this device.

Does anyone have such installation, or can determine if this is

possible?

AFAIK Postgres will not currently run on a read-only filesystem.

-Doug

---------------------------(end of

broadcast)---------------------------

TIP 9: the planner will ignore your desire to choose an index scan if

your

joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Wed, May 11, 2005 at 03:51:43PM -0400, GGoshen@axsone.com scratched on the wall:

I would like to clarify something.
I intend to create the database on a re-writable device (not WORM).
At some point, when I no longer want to add/modify the database, I
plan to copy it to the WORM device. Then I would like to be able to
access the database on the WORM device for reading purposes only.

What you are basically saying is you want a read-only database on a
CD-R (which is essentially a WORM device). As has been discussed
many times in the past, Postgres does not support this very well, if
at all.

-j

--
Jay A. Kreibich | CommTech, Emrg Net Tech Svcs
jak@uiuc.edu | Campus IT & Edu Svcs
<http://www.uiuc.edu/~jak&gt; | University of Illinois at U/C

I know it would be a hard approach but... perhaps ON DELETE and ON UPDATE
triggers would help?

Richard_D_Levine@raytheon.com writes:

Tom Lane suggested a vacuum freeze (? or something like that) for archival
read only data. I got the impression the template databases are freeze
dried for freshness (good to the last bit?) That feature might help as
well in the transition from read-write to read-only.

Yes, you'd definitely need to do that before you could hope to put a
table on read-only storage. Other issues to think about:
- pg_xlog and pg_clog are NEVER read-only
- temp files, which are normally made in a database's default
tablespace

In PG 8.0 it should be pretty easy to vacuum freeze all the tables in a
tablespace (that is not the default tablespace of its database) and then
copy the tablespace directory tree to CD and hack the symlink for it.
I have not actually tried that but in theory it should work. Don't
forget to checkpoint or stop the server before trying to copy files.

It might work to freeze a database's default tablespace in the same
way, if you first change the pgsql_tmp subdirectory into a symlink that
points somewhere that will be writable. I'm not totally sure of this
though (the relcache init file in particular is something that could
burn you).

The main problem with any of this of course is the tight tie between
the read-only and read-write parts of the database. You couldn't,
say, take the WORM device and mount it in another PG installation and
expect usable results.

regards, tom lane

juan.paredes@gmail.com (Juan Miguel Paredes) writes:

I know it would be a hard approach but... perhaps ON DELETE and ON
UPDATE triggers would help?

No, that's not even related to the real problem.

The problem is that the ability to have transactions is deeply
pervasive, and requires a writable store even though you don't imagine
you're doing updates to the data.

Version 8 probably moves this closer to reality with the addition of
tablespace support. Using that, you could take "finished" tables, and
put them into a particular tablespace. VACUUM FREEZE them to pretty
well eliminate the need to touch them again. Then take that
tablespace offline, turn it into a CDROM, and mount it back in the
same location.

If you do all that, and make the entire tablespace read-only, I could
imagine it sorta working. Though I wouldn't want to bet money on the
outcome, at this point...
--
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/&gt;