pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Storing database in WORM devices
12+ messages / 8 participants
[nested] [flat]

* Storing database in WORM devices
@ 2005-05-11 18:27  GGoshen@axsone.com
  0 siblings, 1 reply; 12+ messages in thread

From: GGoshen@axsone.com @ 2005-05-11 18:27 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-admin

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.
 

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [GENERAL] Storing database in WORM devices
@ 2005-05-11 18:51  Douglas McNaught <doug@mcnaught.org>
  parent: GGoshen@axsone.com
  0 siblings, 0 replies; 12+ messages in thread

From: Douglas McNaught @ 2005-05-11 18:51 UTC (permalink / raw)
  To: GGoshen@axsone.com; +Cc: pgsql-general@postgresql.org; pgsql-admin

<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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [GENERAL] Storing database in WORM devices
@ 2005-05-11 19:31  GGoshen@axsone.com
  0 siblings, 2 replies; 12+ messages in thread

From: GGoshen@axsone.com @ 2005-05-11 19:31 UTC (permalink / raw)
  To: doug@mcnaught.org; +Cc: pgsql-general@postgresql.org; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [GENERAL] Storing database in WORM devices
@ 2005-05-11 19:41  Alex Turner <armtuk@gmail.com>
  parent: GGoshen@axsone.com
  1 sibling, 0 replies; 12+ messages in thread

From: Alex Turner @ 2005-05-11 19:41 UTC (permalink / raw)
  To: GGoshen@axsone.com <GGoshen@axsone.com>; +Cc: doug@mcnaught.org; pgsql-general@postgresql.org; pgsql-admin

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
>



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [GENERAL] Storing database in WORM devices
@ 2005-05-11 19:42  Richard_D_Levine@raytheon.com
  parent: GGoshen@axsone.com
  1 sibling, 0 replies; 12+ messages in thread

From: Richard_D_Levine@raytheon.com @ 2005-05-11 19:42 UTC (permalink / raw)
  To: GGoshen@axsone.com; +Cc: doug@mcnaught.org; pgsql-admin; pgsql-admin-owner@postgresql.org; pgsql-general@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [GENERAL] Storing database in WORM devices
@ 2005-05-11 19:51  GGoshen@axsone.com
  0 siblings, 2 replies; 12+ messages in thread

From: GGoshen@axsone.com @ 2005-05-11 19:51 UTC (permalink / raw)
  To: armtuk@gmail.com; +Cc: doug@mcnaught.org; pgsql-general@postgresql.org; pgsql-admin

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
>



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [GENERAL] Storing database in WORM devices
@ 2005-05-11 20:02  Alex Turner <armtuk@gmail.com>
  parent: GGoshen@axsone.com
  1 sibling, 1 reply; 12+ messages in thread

From: Alex Turner @ 2005-05-11 20:02 UTC (permalink / raw)
  To: GGoshen@axsone.com <GGoshen@axsone.com>; +Cc: doug@mcnaught.org; pgsql-general@postgresql.org; pgsql-admin

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
> >
>



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [GENERAL] Storing database in WORM devices
@ 2005-05-11 20:31  Richard_D_Levine@raytheon.com
  parent: Alex Turner <armtuk@gmail.com>
  0 siblings, 1 reply; 12+ messages in thread

From: Richard_D_Levine@raytheon.com @ 2005-05-11 20:31 UTC (permalink / raw)
  To: Alex Turner <armtuk@gmail.com>; +Cc: doug@mcnaught.org, "GGoshen@axsone.com" <GGoshen@axsone.com>; pgsql-admin; pgsql-admin-owner@postgresql.org; pgsql-general@postgresql.org

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





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [GENERAL] Storing database in WORM devices
@ 2005-05-11 20:32  Jay A. Kreibich <jak@uiuc.edu>
  parent: GGoshen@axsone.com
  1 sibling, 1 reply; 12+ messages in thread

From: Jay A. Kreibich @ 2005-05-11 20:32 UTC (permalink / raw)
  To: GGoshen@axsone.com; +Cc: armtuk@gmail.com; doug@mcnaught.org; pgsql-general@postgresql.org; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [GENERAL] Storing database in WORM devices
@ 2005-05-11 20:40  Juan Miguel Paredes <juan.paredes@gmail.com>
  parent: Jay A. Kreibich <jak@uiuc.edu>
  0 siblings, 1 reply; 12+ messages in thread

From: Juan Miguel Paredes @ 2005-05-11 20:40 UTC (permalink / raw)
  To: jak@uiuc.edu; +Cc: GGoshen@axsone.com; armtuk@gmail.com; doug@mcnaught.org; pgsql-general@postgresql.org; pgsql-admin

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
>

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [GENERAL] Storing database in WORM devices
@ 2005-05-11 22:25  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Richard_D_Levine@raytheon.com
  0 siblings, 0 replies; 12+ messages in thread

From: Tom Lane @ 2005-05-11 22:25 UTC (permalink / raw)
  To: Richard_D_Levine@raytheon.com; +Cc: Alex Turner <armtuk@gmail.com>; doug@mcnaught.org, "GGoshen@axsone.com" <GGoshen@axsone.com>; pgsql-admin; pgsql-general@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



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: [GENERAL] Storing database in WORM devices
@ 2005-05-11 22:47  Chris Browne <cbbrowne@acm.org>
  parent: Juan Miguel Paredes <juan.paredes@gmail.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Chris Browne @ 2005-05-11 22:47 UTC (permalink / raw)
  To: pgsql-admin

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/;



^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2005-05-11 22:47 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-11 18:27 Storing database in WORM devices GGoshen@axsone.com
2005-05-11 18:51 ` Douglas McNaught <doug@mcnaught.org>
2005-05-11 19:31 Re: [GENERAL] Storing database in WORM devices GGoshen@axsone.com
2005-05-11 19:41 ` Alex Turner <armtuk@gmail.com>
2005-05-11 19:42 ` Richard_D_Levine@raytheon.com
2005-05-11 19:51 Re: [GENERAL] Storing database in WORM devices GGoshen@axsone.com
2005-05-11 20:02 ` Alex Turner <armtuk@gmail.com>
2005-05-11 20:31   ` Richard_D_Levine@raytheon.com
2005-05-11 22:25     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-11 20:32 ` Jay A. Kreibich <jak@uiuc.edu>
2005-05-11 20:40   ` Juan Miguel Paredes <juan.paredes@gmail.com>
2005-05-11 22:47     ` Chris Browne <cbbrowne@acm.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox