Thread: Storing database in WORM devices
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?
Galit.
<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 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 >
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? > > AFAIK Postgres will not currently run on a read-only filesystem. > > -Doug > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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 thedatabase, I plan to copy it to the WORM device. Then I would like to be able to access the database on the WORM devicefor 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 systemcatalogs 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 >
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/modifythe database, I plan to copy it to the WORM device. Then I would like to be able to access the database on theWORM 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 thesystem 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 > > >
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> | University of Illinois at U/C
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
I know it would be a hard approach but... perhaps ON DELETE and ON UPDATE triggers would help?
On 5/11/05, Jay A. Kreibich <jak@uiuc.edu> wrote:
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> | University of Illinois at U/C
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
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