Thread: Storing database in WORM devices

Storing database in WORM devices

From
Date:
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.
 

Re: Storing database in WORM devices

From
Douglas McNaught
Date:
<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

Re: Storing database in WORM devices

From
Date:
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

Re: Storing database in WORM devices

From
Alex Turner
Date:
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
>

Re: [ADMIN] Storing database in WORM devices

From
Richard_D_Levine@raytheon.com
Date:
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


Re: Storing database in WORM devices

From
Date:
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
>

Re: Storing database in WORM devices

From
Alex Turner
Date:
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
> >
>

Re: [ADMIN] Storing database in WORM devices

From
"Jay A. Kreibich"
Date:
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

Re: [ADMIN] Storing database in WORM devices

From
Richard_D_Levine@raytheon.com
Date:
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



Re: [ADMIN] Storing database in WORM devices

From
Juan Miguel Paredes
Date:
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

Re: [ADMIN] Storing database in WORM devices

From
Tom Lane
Date:
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