From GGoshen@axsone.com Wed May 11 18:27:11 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F0E9352951; Wed, 11 May 2005 15:27:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60317-07; Wed, 11 May 2005 18:27:05 +0000 (GMT) Received: from gatekeeper.axsone.com (unknown [204.142.130.8]) by svr1.postgresql.org (Postfix) with SMTP id 9E5F952A7D; Wed, 11 May 2005 15:27:02 -0300 (ADT) Received: from pubsrv03.axsone.com by gatekeeper.axsone.com via smtpd (for svr1.postgresql.org [200.46.204.71]) with SMTP; Wed, 11 May 2005 14:27:04 -0400 Received: from pubsrv06.axsone.com (unknown [204.142.131.18]) by pubsrv03.axsone.com (Postfix) with ESMTP id 9DEB36EC80; Wed, 11 May 2005 14:31:07 -0400 (EDT) Received: from mailsrv1.ctronsoft.com ([192.168.10.71]) by pubsrv06.axsone.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 11 May 2005 14:26:22 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C55657.06576C59" Subject: Storing database in WORM devices Date: Wed, 11 May 2005 14:27:01 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Storing database in WORM devices Thread-Index: AcVWVwZger05uzaBRr6S8Ob9IISAxQ== From: To: , X-OriginalArrivalTime: 11 May 2005 18:26:22.0875 (UTC) FILETIME=[EF1CDEB0:01C55656] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.229 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, HTML_MESSAGE, NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200505/116 X-Sequence-Number: 17545 This is a multi-part message in MIME format. ------_=_NextPart_001_01C55657.06576C59 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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. =20 Does anyone have such installation, or can determine if this is = possible? =20 Galit. =20 ------_=_NextPart_001_01C55657.06576C59 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I would like to = store the=20 complete database into a WORM device (Write Once Read Many). I would = like to=20 access this database directly from the WORM device and perform read only = SQL=20 statements against this device.
 
Does anyone have = such=20 installation, or can determine if this is possible?
 
Galit.
 

------_=_NextPart_001_01C55657.06576C59-- From doug@mcnaught.org Wed May 11 19:11:06 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6E59C53531; Wed, 11 May 2005 16:11:06 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68101-05; Wed, 11 May 2005 19:10:58 +0000 (GMT) Received: from asmodeus.mcnaught.org (sp-260-1.net4.netcentrix.net [4.21.254.118]) by svr1.postgresql.org (Postfix) with ESMTP id 19F355295D; Wed, 11 May 2005 16:10:52 -0300 (ADT) Received: from [68.158.108.175] (helo=Douglas-McNaughts-Powerbook.local) by asmodeus.mcnaught.org with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA:32) (Exim 4.50) id 1DVwbZ-0007Sr-CV; Wed, 11 May 2005 15:10:50 -0400 To: Cc: , Subject: Re: [GENERAL] Storing database in WORM devices References: From: Douglas McNaught Date: Wed, 11 May 2005 14:51:12 -0400 In-Reply-To: (GGoshen@axsone.com's message of "Wed, 11 May 2005 14:27:01 -0400") Message-ID: User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.0.50 (darwin) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200505/118 X-Sequence-Number: 17547 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 From GGoshen@axsone.com Wed May 11 19:32:02 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A8CA0538E0; Wed, 11 May 2005 16:32:02 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 73281-01; Wed, 11 May 2005 19:31:57 +0000 (GMT) Received: from gatekeeper.axsone.com (unknown [204.142.130.8]) by svr1.postgresql.org (Postfix) with SMTP id A17F552951; Wed, 11 May 2005 16:31:55 -0300 (ADT) Received: from pubsrv03.axsone.com by gatekeeper.axsone.com via smtpd (for svr1.postgresql.org [200.46.204.71]) with SMTP; Wed, 11 May 2005 15:31:57 -0400 Received: from pubsrv06.axsone.com (unknown [204.142.131.18]) by pubsrv03.axsone.com (Postfix) with ESMTP id C59596EF1D; Wed, 11 May 2005 15:36:01 -0400 (EDT) Received: from mailsrv1.ctronsoft.com ([192.168.10.71]) by pubsrv06.axsone.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 11 May 2005 15:31:16 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Subject: Re: [GENERAL] Storing database in WORM devices Date: Wed, 11 May 2005 15:31:55 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [GENERAL] Storing database in WORM devices Thread-Index: AcVWXSYEdFIn4ME6QHC9y4Y2HfJlQwAAoNOw From: To: Cc: , X-OriginalArrivalTime: 11 May 2005 19:31:16.0703 (UTC) FILETIME=[0003DEF0:01C55660] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.21 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200505/119 X-Sequence-Number: 17548 Why? Any specific reason that you are aware of ? Are there any writes done to the database when read only SQL statements = are issued? =20 -----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 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. > =20 > Does anyone have such installation, or can determine if this is = possible? AFAIK Postgres will not currently run on a read-only filesystem. -Doug From armtuk@gmail.com Wed May 11 19:41:25 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A15515322E for ; Wed, 11 May 2005 16:41:25 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 74649-05 for ; Wed, 11 May 2005 19:41:20 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by svr1.postgresql.org (Postfix) with ESMTP id EF1BF53228 for ; Wed, 11 May 2005 16:41:19 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 68so426684wri for ; Wed, 11 May 2005 12:41:21 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=emBUwHRnJd95t5QVKfffG4IwSgNquR9O8dKy7HFFk+QrtlpKIEp7KlQhKio0Ka5ZLfn0HL5i64J2V7AEh0vKT9gCCKj4bmfb0m3PsgkiYIjf7ljeayqA6OZHKItW6cgs4mdKKqchKF7Oguu+tN59caCayC7vduEEFcnrPW8eCjs= Received: by 10.54.41.77 with SMTP id o77mr623289wro; Wed, 11 May 2005 12:41:21 -0700 (PDT) Received: by 10.54.86.16 with HTTP; Wed, 11 May 2005 12:41:21 -0700 (PDT) Message-ID: <33c6269f05051112412482456@mail.gmail.com> Date: Wed, 11 May 2005 15:41:21 -0400 From: Alex Turner Reply-To: Alex Turner To: "GGoshen@axsone.com" Subject: Re: [GENERAL] Storing database in WORM devices Cc: doug@mcnaught.org, pgsql-general@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.512 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP, TO_ADDRESS_EQ_REAL X-Spam-Level: X-Archive-Number: 200505/120 X-Sequence-Number: 17549 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 wrote: > Why? Any specific reason that you are aware of ? > Are there any writes done to the database when read only SQL statements a= re issued? >=20 >=20 > -----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 >=20 > writes: >=20 > > I would like to store the complete database into a WORM device (Write O= nce > > 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 possibl= e? >=20 > AFAIK Postgres will not currently run on a read-only filesystem. >=20 > -Doug >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if you= r > joining column's datatypes do not match > From Richard_D_Levine@raytheon.com Wed May 11 19:43:01 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 696EE53B12; Wed, 11 May 2005 16:43:01 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 74613-06; Wed, 11 May 2005 19:42:56 +0000 (GMT) Received: from dfw-gate4.raytheon.com (dfw-gate4.raytheon.com [199.46.199.233]) by svr1.postgresql.org (Postfix) with ESMTP id B3AD453228; Wed, 11 May 2005 16:42:54 -0300 (ADT) Received: from ds02c00.directory.ray.com (ds02c00.directory.ray.com [147.25.138.118]) by dfw-gate4.raytheon.com (8.12.10/8.12.10) with ESMTP id j4BJgjXj012543; Wed, 11 May 2005 14:42:49 -0500 (CDT) Received: from ds02c00 (localhost [127.0.0.1]) by ds02c00.directory.ray.com (Switch-3.1.7/Switch-3.1.0) with ESMTP id j4BJggXB018464; Wed, 11 May 2005 19:42:42 GMT Received: from ds02c00.directory.ray.com with LMTP by ds02c00 (2.0.6/sieved-2-0-build-559); Wed, 11 May 2005 19:42:41 +0000 Received: from notesserver5.ftw.us.ray.com (notesserver5.ftw.us.ray.com [151.168.145.35]) by ds02c00.directory.ray.com (Switch-3.1.7/Switch-3.1.0) with ESMTP id j4BJgXcX018402 sender Richard_D_Levine@raytheon.com; Wed, 11 May 2005 19:42:33 GMT In-Reply-To: Subject: Re: [GENERAL] Storing database in WORM devices To: Cc: doug@mcnaught.org, pgsql-admin@postgresql.org, pgsql-admin-owner@postgresql.org, pgsql-general@postgresql.org X-Mailer: Lotus Notes Release 6.5.2 June 01, 2004 Message-ID: From: Richard_D_Levine@raytheon.com Date: Wed, 11 May 2005 14:42:31 -0500 X-MIMETrack: Serialize by Router on NotesServer5/HDC(Release 6.5.2|June 01, 2004) at 05/11/2005 02:42:33 PM MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII X-SPAM: 0.00 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.21 tagged_above=0 required=5 tests=AWL, NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200505/121 X-Sequence-Number: 17550 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 > > > 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 From GGoshen@axsone.com Wed May 11 19:52:20 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 85E125386F; Wed, 11 May 2005 16:52:20 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77036-08; Wed, 11 May 2005 19:52:17 +0000 (GMT) Received: from gatekeeper.axsone.com (unknown [204.142.130.8]) by svr1.postgresql.org (Postfix) with SMTP id 4EBE452951; Wed, 11 May 2005 16:52:15 -0300 (ADT) Received: from pubsrv03.axsone.com by gatekeeper.axsone.com via smtpd (for svr1.postgresql.org [200.46.204.71]) with SMTP; Wed, 11 May 2005 15:52:17 -0400 Received: from pubsrv06.axsone.com (unknown [204.142.131.18]) by pubsrv03.axsone.com (Postfix) with ESMTP id 14B3A6EF38; Wed, 11 May 2005 15:56:22 -0400 (EDT) Received: from mailsrv1.ctronsoft.com ([192.168.10.71]) by pubsrv06.axsone.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 11 May 2005 15:51:04 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Subject: Re: [GENERAL] Storing database in WORM devices Date: Wed, 11 May 2005 15:51:43 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [GENERAL] Storing database in WORM devices Thread-Index: AcVWYWoBGcsZuNGXR062NJrZ4bG6PQAAGE3g From: To: Cc: , , X-OriginalArrivalTime: 11 May 2005 19:51:04.0078 (UTC) FILETIME=[C3BEEAE0:01C55662] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.209 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200505/122 X-Sequence-Number: 17551 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. =20 -----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 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? >=20 >=20 > -----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 >=20 > writes: >=20 > > 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? >=20 > AFAIK Postgres will not currently run on a read-only filesystem. >=20 > -Doug >=20 > ---------------------------(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 > From armtuk@gmail.com Wed May 11 20:02:46 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 777A353993 for ; Wed, 11 May 2005 17:02:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78655-05 for ; Wed, 11 May 2005 20:02:44 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.194]) by svr1.postgresql.org (Postfix) with ESMTP id 4751D5322E for ; Wed, 11 May 2005 17:02:43 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 68so433563wri for ; Wed, 11 May 2005 13:02:45 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=VjXSsctEjtN2RypTlSyf4CohvBgVbfAay/bXC9Nq/w5mFMXiMtVS0MQG6p3H7yh+eb41NWTHr9qpBXq9Z/K7DD80nRVAIUbeL4qATD6QLXZIQYFCuKkTnemeJ77BbSRSRAQkKgYSJNGU1S9mG6Bt/sMfpYfXIH6242zuFL3CO68= Received: by 10.54.76.2 with SMTP id y2mr653100wra; Wed, 11 May 2005 13:02:45 -0700 (PDT) Received: by 10.54.86.16 with HTTP; Wed, 11 May 2005 13:02:45 -0700 (PDT) Message-ID: <33c6269f05051113026338b8ef@mail.gmail.com> Date: Wed, 11 May 2005 16:02:45 -0400 From: Alex Turner Reply-To: Alex Turner To: "GGoshen@axsone.com" Subject: Re: [GENERAL] Storing database in WORM devices Cc: doug@mcnaught.org, pgsql-general@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.516 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP, TO_ADDRESS_EQ_REAL X-Spam-Level: X-Archive-Number: 200505/123 X-Sequence-Number: 17552 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 wrote: > I would like to clarify something. > I intend to create the database on a re-writable device (not WORM). At so= me 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. >=20 > I think that the pg_listener catalog is being written for any user connec= tion. I am not sure whether I could store the system catalogs separated fro= m the application table spaces. >=20 > -----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 >=20 > I think simply initialising the system causes writes in the system > tables and the WAL... >=20 > I'm sure someone more knowledgeable can chime in. >=20 > Alex. Turner > netEconomist >=20 > On 5/11/05, 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 > > > > 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 WO= RM > > > device and perform read only SQL statements against this device. > > > > > > Does anyone have such installation, or can determine if this is possi= ble? > > > > 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 y= our > > joining column's datatypes do not match > > > From Richard_D_Levine@raytheon.com Wed May 11 20:37:03 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4B56553B16; Wed, 11 May 2005 17:37:03 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83022-09; Wed, 11 May 2005 20:37:01 +0000 (GMT) Received: from bos-gate6.raytheon.com (bos-gate6.raytheon.com [199.46.198.237]) by svr1.postgresql.org (Postfix) with ESMTP id 4FF0853E25; Wed, 11 May 2005 17:36:57 -0300 (ADT) Received: from ds02e00.directory.ray.com (ds02e00.directory.ray.com [147.25.130.245]) by bos-gate6.raytheon.com (8.12.10/8.12.10) with ESMTP id j4BKVsvV018922; Wed, 11 May 2005 16:31:54 -0400 (EDT) Received: from ds02e00 (localhost [127.0.0.1]) by ds02e00.directory.ray.com (Switch-3.1.7/Switch-3.1.0) with ESMTP id j4BKVl3I000689; Wed, 11 May 2005 20:31:47 GMT Received: from ds02e00.directory.ray.com with LMTP by ds02e00 (2.0.6/sieved-2-0-build-559); Wed, 11 May 2005 20:31:45 +0000 Received: from notesserver5.ftw.us.ray.com (notesserver5.ftw.us.ray.com [151.168.145.35]) by ds02e00.directory.ray.com (Switch-3.1.7/Switch-3.1.0) with ESMTP id j4BKVV1o000594 sender Richard_D_Levine@raytheon.com; Wed, 11 May 2005 20:31:32 GMT In-Reply-To: <33c6269f05051113026338b8ef@mail.gmail.com> Subject: Re: [GENERAL] Storing database in WORM devices To: Alex Turner Cc: doug@mcnaught.org, "GGoshen@axsone.com" , pgsql-admin@postgresql.org, pgsql-admin-owner@postgresql.org, pgsql-general@postgresql.org X-Mailer: Lotus Notes Release 6.5.2 June 01, 2004 Message-ID: From: Richard_D_Levine@raytheon.com Date: Wed, 11 May 2005 15:31:31 -0500 X-MIMETrack: Serialize by Router on NotesServer5/HDC(Release 6.5.2|June 01, 2004) at 05/11/2005 03:31:33 PM MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII X-SPAM: 0.00 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.209 tagged_above=0 required=5 tests=AWL, NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200505/129 X-Sequence-Number: 17558 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 To Sent by: "GGoshen@axsone.com" pgsql-admin-owner @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 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 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 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 > > > > 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 From jak@uiuc.edu Wed May 11 20:32:44 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 182EF53CED; Wed, 11 May 2005 17:32:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84127-03; Wed, 11 May 2005 20:32:41 +0000 (GMT) Received: from expredir5.cites.uiuc.edu (expredir5.cites.uiuc.edu [128.174.5.96]) by svr1.postgresql.org (Postfix) with ESMTP id 1286253CD6; Wed, 11 May 2005 17:32:38 -0300 (ADT) Received: from firefox.cites.uiuc.edu (tantris.near.uiuc.edu [128.174.250.228]) by expredir5.cites.uiuc.edu (8.12.11/8.12.11) with ESMTP id j4BKWSk5023234; Wed, 11 May 2005 15:32:33 -0500 (CDT) Received: by firefox.cites.uiuc.edu (Postfix, from userid 79964) id 54D4C3A231F; Wed, 11 May 2005 15:32:28 -0500 (CDT) Date: Wed, 11 May 2005 15:32:28 -0500 From: "Jay A. Kreibich" To: GGoshen@axsone.com Cc: armtuk@gmail.com, doug@mcnaught.org, pgsql-general@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [GENERAL] Storing database in WORM devices Message-ID: <20050511203227.GA8262@uiuc.edu> Reply-To: jak@uiuc.edu References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-URL: http://www.uiuc.edu/~jak (includes PGP key) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200505/128 X-Sequence-Number: 17557 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 | University of Illinois at U/C From juan.paredes@gmail.com Wed May 11 20:40:38 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D417853D73 for ; Wed, 11 May 2005 17:40:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84064-10 for ; Wed, 11 May 2005 20:40:37 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.202]) by svr1.postgresql.org (Postfix) with ESMTP id 10C9F53D6D for ; Wed, 11 May 2005 17:40:34 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 68so344071wra for ; Wed, 11 May 2005 13:40:37 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=HT8KmB0IvHHIdS+VugfS8bmDiRl+rmNixsOnHtEhmH3W92gqPZkUIQtmED87I4G202jnRmfThAver/ziadJHuAD9Md50tJqN469rN04FBv6pUjt1X9rpSL6T+mJVW6t3P1cQrU39syuKRvaXsD7mA+6xmYj3n2JMIWDmdcbsc8Q= Received: by 10.54.159.16 with SMTP id h16mr492709wre; Wed, 11 May 2005 13:40:37 -0700 (PDT) Received: by 10.54.4.42 with HTTP; Wed, 11 May 2005 13:40:37 -0700 (PDT) Message-ID: <9e0a28690505111340324940ef@mail.gmail.com> Date: Wed, 11 May 2005 16:40:37 -0400 From: Juan Miguel Paredes Reply-To: Juan Miguel Paredes To: jak@uiuc.edu Subject: Re: [GENERAL] Storing database in WORM devices Cc: GGoshen@axsone.com, armtuk@gmail.com, doug@mcnaught.org, pgsql-general@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: <20050511203227.GA8262@uiuc.edu> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_866_26014345.1115844037069" References: <20050511203227.GA8262@uiuc.edu> X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200505/130 X-Sequence-Number: 17559 ------=_Part_866_26014345.1115844037069 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I know it would be a hard approach but... perhaps ON DELETE and ON UPDATE= =20 triggers would help? On 5/11/05, Jay A. Kreibich wrote:=20 >=20 > On Wed, May 11, 2005 at 03:51:43PM -0400, GGoshen@axsone.com scratched on= =20 > 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. >=20 > 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. >=20 > -j >=20 > -- > Jay A. Kreibich | CommTech, Emrg Net Tech Svcs > jak@uiuc.edu | Campus IT & Edu Svcs > | University of Illinois at U/C >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? >=20 > http://archives.postgresql.org > ------=_Part_866_26014345.1115844037069 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I know it would be a hard approach but... perhaps ON DELETE and ON UPDATE t= riggers would help?

On 5/11/05, = Jay A. Kreibich <jak@uiuc.edu>= ; wrote:
On Wed, May 11, 2005 at 03:51:43= PM -0400, GGoshen@axsone.com scra= tched 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.<= br>
What you are basically saying is you want a read-only database on a<= br>CD-R (which is essentially a WORM device).  As has been discus= sed
many times in the past, Postgres does not support this very well, if
at = all.

  -j

--
      = ;            &n= bsp; Jay A. Kreibich | CommTech, Emrg Net Tech Svcs
  &nb= sp;            =         jak@uiuc.edu | Campus IT & Edu Svcs
    &nbs= p;    <http://www.ui= uc.edu/~jak> | University of Illinois at U/C

----------------= -----------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

   &n= bsp;          http://archives.postgresql.org

------=_Part_866_26014345.1115844037069-- From tgl@sss.pgh.pa.us Wed May 11 22:26:11 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 234FE5295D for ; Wed, 11 May 2005 19:26:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03379-03 for ; Wed, 11 May 2005 22:26:02 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id EF72653623 for ; Wed, 11 May 2005 19:26:01 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j4BMPtwT024819; Wed, 11 May 2005 18:25:55 -0400 (EDT) To: Richard_D_Levine@raytheon.com Cc: Alex Turner , doug@mcnaught.org, "GGoshen@axsone.com" , pgsql-admin@postgresql.org, pgsql-general@postgresql.org Subject: Re: [GENERAL] Storing database in WORM devices In-reply-to: References: Comments: In-reply-to Richard_D_Levine@raytheon.com message dated "Wed, 11 May 2005 15:31:31 -0500" Date: Wed, 11 May 2005 18:25:55 -0400 Message-ID: <24818.1115850355@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/139 X-Sequence-Number: 17568 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 From cbbrowne@acm.org Wed May 11 23:13:07 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A226D53C91 for ; Wed, 11 May 2005 20:13:07 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 10018-08 for ; Wed, 11 May 2005 23:12:58 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id DBFD853497 for ; Wed, 11 May 2005 20:12:57 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id j4BNCvLO012326 for ; Wed, 11 May 2005 23:12:57 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id j4BN3xH6011041 for pgsql-admin@postgresql.org; Wed, 11 May 2005 23:03:59 GMT (envelope-from news) From: Chris Browne X-Newsgroups: pgsql.admin Subject: Re: [GENERAL] Storing database in WORM devices Date: Wed, 11 May 2005 18:47:13 -0400 Organization: cbbrowne Computing Inc Lines: 27 Message-ID: <60is1ptm6m.fsf@dba2.int.libertyrms.com> References: <20050511203227.GA8262@uiuc.edu> <9e0a28690505111340324940ef@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Jumbo Shrimp, linux) Cancel-Lock: sha1:FfePD8qft2q1QvjWUOb+1lRWWPw= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200505/140 X-Sequence-Number: 17569 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.''"