From lists@insyde.com.br Tue Mar 18 13:21:19 2003 X-Original-To: pgsql-admin@postgresql.org Received: from itaqui.terra.com.br (itaqui.terra.com.br [200.176.3.19]) by postgresql.org (Postfix) with ESMTP id DC020474E4F for ; Tue, 18 Mar 2003 08:21:19 -0500 (EST) Received: from marova.terra.com.br (marova.terra.com.br [200.176.3.39]) by itaqui.terra.com.br (Postfix) with ESMTP id 840163BC63A for ; Tue, 18 Mar 2003 10:21:22 -0300 (BRT) Received: from insyde (200-171-250-65.customer.telesp.net.br [200.171.250.65]) (authenticated user insyde09) by marova.terra.com.br (Postfix) with ESMTP id 290B83DC09F for ; Tue, 18 Mar 2003 10:21:22 -0300 (BRT) Message-ID: <00bd01c2ed51$630374e0$41faabc8@insyde> From: "lists" To: Subject: Problems with pg_restore Date: Tue, 18 Mar 2003 10:22:11 -0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00BA_01C2ED38.3D26CE20" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200303/311 X-Sequence-Number: 7951 This is a multi-part message in MIME format. ------=_NextPart_000_00BA_01C2ED38.3D26CE20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I've made a dump from PostgreSQL 7.2.2 under Win2k with the command: $pg_dump -o -C -b -Ft -i -O -v DataBaseName > DBN.dump Then I've tried to restore it to another instalation, in another server (Re= dHat 7.3, PostgreSQL 7.2.4) $pg_restore -Ft DBN.dump -d DataBaseName But then I get this message: pg_restore: [archiver] out of memory Is there any workaround? TIA, Ricardo ------=_NextPart_000_00BA_01C2ED38.3D26CE20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
I've made a dump from PostgreSQL 7.2.2&nbs= p;under=20 Win2k with the command:
 
    $pg_dump -o -C -b -Ft -= i -O -v=20 DataBaseName > DBN.dump
 
Then I've tried to restore it to another= =20 instalation, in another server (RedHat 7.3, PostgreSQL 7.2.4)
 
    $pg_restore -Ft DBN.dum= p -d=20 DataBaseName
 
But then I get this message:
 
    pg_restore: [archiver] = out of=20 memory
 
Is there any workaround?
 
TIA,
Ricardo
------=_NextPart_000_00BA_01C2ED38.3D26CE20-- From xzilla@users.sourceforge.net Mon Mar 24 18:43:55 2003 X-Original-To: pgsql-admin@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 682B247631B for ; Mon, 24 Mar 2003 13:43:55 -0500 (EST) Received: from localhost.localdomain (unknown [65.217.53.66]) by postgresql.org (Postfix) with ESMTP id 76C6D47630E for ; Mon, 24 Mar 2003 13:43:54 -0500 (EST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by localhost.localdomain (8.12.5/8.12.5) with ESMTP id h2OJIX6P002419; Mon, 24 Mar 2003 14:18:33 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id h2OIhop23582; Mon, 24 Mar 2003 13:43:50 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id F3HTKQML; Mon, 24 Mar 2003 13:43:49 -0500 Subject: Re: Problems with pg_restore From: Robert Treat To: lists Cc: pgsql-admin@postgresql.org In-Reply-To: <00bd01c2ed51$630374e0$41faabc8@insyde> References: <00bd01c2ed51$630374e0$41faabc8@insyde> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 24 Mar 2003 13:43:49 -0500 Message-Id: <1048531429.13853.280.camel@camel> Mime-Version: 1.0 X-Spam-Status: No, hits=-38.0 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_XIMIAN autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/396 X-Sequence-Number: 8036 On Tue, 2003-03-18 at 08:22, lists wrote: > Hi, > > I've made a dump from PostgreSQL 7.2.2 under Win2k with the command: > > $pg_dump -o -C -b -Ft -i -O -v DataBaseName > DBN.dump > > Then I've tried to restore it to another instalation, in another server > (RedHat 7.3, PostgreSQL 7.2.4) > > $pg_restore -Ft DBN.dump -d DataBaseName > > But then I get this message: > > pg_restore: [archiver] out of memory > > Is there any workaround? Didn't see any other response to this, I'd suggest using pg_dump's -h option to point the pg_dump on your linux box to your win2k server and dump it directly. You might want to send a message to the -cgwyn group, I'm wondering if there are incompatibilities between windows/linux tar dumps. Robert Treat From Dylan.Kuhn@navy.mil Tue Jul 15 14:55:16 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id A6E2DCC3D2 for ; Tue, 15 Jul 2003 14:55:16 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 10212-03 for ; Tue, 15 Jul 2003 14:55:38 +0000 (GMT) Received: from Gate4-sdni.nmci.navy.mil (gate5-sandiego.nmci.navy.mil [138.163.0.44]) by svr1.postgresql.org (Postfix) with ESMTP id A9D1ECB9AA for ; Tue, 15 Jul 2003 11:55:13 -0300 (ADT) Received: from Nawesdnims01.nadsuswe.nads.navy.mil by Gate4-sdni.nmci.navy.mil via smtpd (for developer.postgresql.org [64.117.224.193]) with ESMTP; Tue, 15 Jul 2003 14:46:41 +0100 Received: (private information removed) Received: from no.name.available by Gate1-sdni.nadsuswe.nads.navy.mil via smtpd (for [10.0.0.166]) with ESMTP; Tue, 15 Jul 2003 14:49:46 -0700 Received: (private information removed) Received: (private information removed) Received: (private information removed) X-MimeOLE: Produced By Microsoft Exchange V6.0.5762.3 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C34AE1.1D2DA270" Subject: problems with pg_restore Date: Tue, 15 Jul 2003 07:55:21 -0700 Message-ID: <78ED1F30F0D85B4698FBD6CF2A5AC8F5A576F9@NAWECHLKEX01VA.nadsuswe.nads.navy.mil> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: problems with pg_restore Thread-Index: AcNK4R0u0E0JU2xQREa6LgaQVMScGQ== From: "Kuhn, Dylan K (4520500D)" To: X-OriginalArrivalTime: 15 Jul 2003 14:55:21.0626 (UTC) FILETIME=[1D4E17A0:01C34AE1] X-Scanned-By: MIMEDefang 2.1 (www dot roaringpenguin dot com slash mimedefang) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/136 X-Sequence-Number: 9476 This is a multi-part message in MIME format. ------_=_NextPart_001_01C34AE1.1D2DA270 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a 200GB archive I created like this: % pg_dump -f db1.pga -Fc -b db1 To check that it is good, I tried to restore to another database: % pg_restore -d db2 db1.pga [...quickly restores sequences, then...] pg_restore: [archiver (db)] could not execute query: ERROR: function plpgsq= l_call_handler already exists with same argument types Ok, I had installed PLPGSQL in template1, which has been useful in other si= tuations. I drop db2, recreate it, and drop the PLPGSQL language and call = handler, then try again: % pg_restore -d db2 db1.pga [...restores seqs, tables, keys, then spends several quiet hours (on blobs = I assume)...] pg_restore: [archiver (db)] could not execute query: ERROR: Database commen= ts may only be applied to the current database I'm not sure how to get around this one. Can an archived database with com= ments be restored to a database with a different name? Are the comments th= e only thing missing from the restored database, or could there be other th= ings? thanks, Dylan Kuhn ------_=_NextPart_001_01C34AE1.1D2DA270 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable problems with pg_restore

I have a 200GB archive I created like this= :

% pg_dump -f db1.pga -Fc -b db1

To check that it is good, I tried to resto= re to another database:

% pg_restore -d db2 db1.pga
[...quickly restores sequences, then...]<= /FONT>
pg_restore: [archiver (db)] could not exe= cute query: ERROR: function plpgsql_call_handler already exists with same a= rgument types

Ok, I had installed PLPGSQL in template1, = which has been useful in other situations.  I drop db2, recreate it, a= nd drop the PLPGSQL language and call handler, then try again:

% pg_restore -d db2 db1.pga
[...restores seqs, tables, keys, then spe= nds several quiet hours (on blobs I assume)...]
pg_restore: [archiver (db)] could not exe= cute query: ERROR: Database comments may only be applied to the current dat= abase

I'm not sure how to get around this one.&n= bsp; Can an archived database with comments be restored to a database with = a different name?  Are the comments the only thing missing from the re= stored database, or could there be other things?

thanks,
Dylan Kuhn

------_=_NextPart_001_01C34AE1.1D2DA270-- From tgl@sss.pgh.pa.us Tue Jul 15 16:26:04 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 47CE4CC409; Tue, 15 Jul 2003 16:26:04 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 30270-07; Tue, 15 Jul 2003 16:26:31 +0000 (GMT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 13A18CC419; Tue, 15 Jul 2003 13:26:02 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h6FGQTQ3023212; Tue, 15 Jul 2003 12:26:29 -0400 (EDT) To: "Kuhn, Dylan K (4520500D)" Cc: pgsql-admin@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: problems with pg_restore In-reply-to: <78ED1F30F0D85B4698FBD6CF2A5AC8F5A576F9@NAWECHLKEX01VA.nadsuswe.nads.navy.mil> References: <78ED1F30F0D85B4698FBD6CF2A5AC8F5A576F9@NAWECHLKEX01VA.nadsuswe.nads.navy.mil> Comments: In-reply-to "Kuhn, Dylan K (4520500D)" message dated "Tue, 15 Jul 2003 07:55:21 -0700" Date: Tue, 15 Jul 2003 12:26:29 -0400 Message-ID: <23211.1058286389@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/137 X-Sequence-Number: 9477 "Kuhn, Dylan K (4520500D)" writes: > [ tries to restore a dump into a database with a different name ] > pg_restore: [archiver (db)] could not execute query: ERROR: Database commen= > ts may only be applied to the current database > I'm not sure how to get around this one. Can an archived database with com= > ments be restored to a database with a different name? Hm. Evidently not :-(. The COMMENT ON DATABASE facility is a bit bogus anyway (since there's no way to make the comments visible across databases). You might be best advised not to use it. Hackers: this seems like an extremely bad side-effect of what we thought was a simple addition of a helpful check. I am thinking we should either remove the check again, or downgrade it to a WARNING (though I'm not quite sure how to phrase the warning ...). Any thoughts? regards, tom lane From Dylan.Kuhn@navy.mil Tue Jul 15 16:54:19 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 8D126CC3F8 for ; Tue, 15 Jul 2003 16:54:19 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 39388-07 for ; Tue, 15 Jul 2003 16:54:46 +0000 (GMT) Received: from Gate1-sdni.nmci.navy.mil (gate2-sandiego.nmci.navy.mil [138.163.0.41]) by svr1.postgresql.org (Postfix) with ESMTP id E8A09CC42F for ; Tue, 15 Jul 2003 13:54:16 -0300 (ADT) Received: from Nawesdnims02.nadsuswe.nads.navy.mil by Gate1-sdni.nmci.navy.mil via smtpd (for developer.postgresql.org [64.117.224.193]) with ESMTP; Tue, 15 Jul 2003 16:48:52 -0700 Received: (private information removed) Received: (private information removed) Received: (private information removed) Received: (private information removed) Received: (private information removed) X-MimeOLE: Produced By Microsoft Exchange V6.0.5762.3 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: problems with pg_restore Date: Tue, 15 Jul 2003 09:54:25 -0700 Message-ID: <78ED1F30F0D85B4698FBD6CF2A5AC8F5A576FA@NAWECHLKEX01VA.nadsuswe.nads.navy.mil> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] problems with pg_restore Thread-Index: AcNK7doDlk1tYQqZRCauZ9nG6X5URQAAuDRg From: "Kuhn, Dylan K (4520500D)" To: X-OriginalArrivalTime: 15 Jul 2003 16:54:26.0588 (UTC) FILETIME=[C008C5C0:01C34AF1] X-Scanned-By: MIMEDefang 2.1 (www dot roaringpenguin dot com slash mimedefang) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/138 X-Sequence-Number: 9478 =20 > Hm. Evidently not :-(. The COMMENT ON DATABASE facility is=20 > a bit bogus > anyway (since there's no way to make the comments visible across > databases). You might be best advised not to use it. I agree. I plan to delete it and try again, and avoid using database comme= nts in the future. This seems to work: DELETE FROM ONLY pg_description WHERE pg_description.objoid=3D (SELECT pg_database.oid FROM pg_database WHERE pg_database.datname=3Dc= urrent_database()) AND pg_description.classoid=3D (SELECT pg_class.relfilenode FROM pg_class WHERE pg_class.relname=3D'p= g_database'); > (though I'm > not quite sure how to phrase the warning ...). Any thoughts? I would have been happy enough with the existing message as a warning - pro= bably anyone who tries to use database comments has noticed the limitations. -dylan- From rbt@rbt.ca Tue Jul 15 19:50:00 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 054A6CC406; Tue, 15 Jul 2003 19:50:00 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 33046-01; Tue, 15 Jul 2003 19:50:16 +0000 (GMT) Received: from mailav.tor1.inquent.com (mail.inquent.com [216.208.117.106]) by svr1.postgresql.org (Postfix) with ESMTP id 81C42CC48E; Tue, 15 Jul 2003 16:49:48 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mailav.tor1.inquent.com (Postfix) with ESMTP id 569BF10258; Tue, 15 Jul 2003 15:43:25 -0400 (EDT) Received: from [10.0.2.7] (unknown [10.0.2.7]) by mailav.tor1.inquent.com (Postfix) with ESMTP id 15BD11024D; Tue, 15 Jul 2003 15:43:23 -0400 (EDT) Subject: Re: problems with pg_restore From: Rod Taylor To: Tom Lane Cc: "Kuhn, Dylan K (4520500D)" , pgsql-admin@postgresql.org, PostgreSQL Development In-Reply-To: <23211.1058286389@sss.pgh.pa.us> References: <78ED1F30F0D85B4698FBD6CF2A5AC8F5A576F9@NAWECHLKEX01VA.nadsuswe.nads.navy.mil> <23211.1058286389@sss.pgh.pa.us> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-FtbH+8YWkVQ//RA39VP+" Message-Id: <1058298650.9981.117.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.3 Date: 15 Jul 2003 19:50:51 +0000 X-Virus-Scanned: by AMaViS perl-11 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/182 X-Sequence-Number: 9522 --=-FtbH+8YWkVQ//RA39VP+ Content-Type: text/plain Content-Transfer-Encoding: quoted-printable > Hm. Evidently not :-(. The COMMENT ON DATABASE facility is a bit bogus > anyway (since there's no way to make the comments visible across > databases). You might be best advised not to use it. >=20 > Hackers: this seems like an extremely bad side-effect of what we thought > was a simple addition of a helpful check. I am thinking we should > either remove the check again, or downgrade it to a WARNING (though I'm > not quite sure how to phrase the warning ...). Any thoughts? How about going the other way and removing the requirement to explicitly state the database? COMMENT ON DATABASE IS 'This comment is on the current database.'; --=-FtbH+8YWkVQ//RA39VP+ Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA/FFsa6DETLow6vwwRAr4dAJ9NEqifqWv1tDRq49xWlFGNZni/xQCcD4cu JbQ7mxQatlZTkdVI0xL7nrA= =Jqlk -----END PGP SIGNATURE----- --=-FtbH+8YWkVQ//RA39VP+-- From tgl@sss.pgh.pa.us Tue Jul 15 20:02:45 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 74D28CC472 for ; Tue, 15 Jul 2003 20:02:45 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 09128-03 for ; Tue, 15 Jul 2003 20:03:12 +0000 (GMT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 17E58CC422 for ; Tue, 15 Jul 2003 17:02:44 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h6FK3DQ3008300; Tue, 15 Jul 2003 16:03:13 -0400 (EDT) To: Rod Taylor Cc: "Kuhn, Dylan K (4520500D)" , pgsql-admin@postgresql.org, PostgreSQL Development Subject: Re: problems with pg_restore In-reply-to: <1058298650.9981.117.camel@jester> References: <78ED1F30F0D85B4698FBD6CF2A5AC8F5A576F9@NAWECHLKEX01VA.nadsuswe.nads.navy.mil> <23211.1058286389@sss.pgh.pa.us> <1058298650.9981.117.camel@jester> Comments: In-reply-to Rod Taylor message dated "15 Jul 2003 19:50:51 -0000" Date: Tue, 15 Jul 2003 16:03:13 -0400 Message-ID: <8299.1058299393@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/139 X-Sequence-Number: 9479 Rod Taylor writes: >> Hackers: this seems like an extremely bad side-effect of what we thought >> was a simple addition of a helpful check. I am thinking we should >> either remove the check again, or downgrade it to a WARNING (though I'm >> not quite sure how to phrase the warning ...). Any thoughts? > How about going the other way and removing the requirement to explicitly > state the database? > COMMENT ON DATABASE IS 'This comment is on the current database.'; Won't help us for reading existing pg_dump scripts, although perhaps it would be useful going forward. Given the current implementation, it seems like there are three possible behaviors for COMMENT ON DATABASE when the database name isn't the same as the current database: 1. Raise error (what we're doing now). Simple but breaks dump scripts for the restore-into-different-DB scenario. 2. Do nothing, store the comment in the current DB's pg_description (what we did in 7.2). Now that I think about it, this also fails for different-database restore, since very possibly the attempt to look up the DB name will fail --- you'll get a no-such-database error instead of the present error, but it's still unhelpful. 3. Ignore the specified DB name, store the comment as the description of the current DB; possibly give a warning saying we're doing so. This would allow correct restoration of dumps into different DBs, but I think people would find it awfully surprising :-( regards, tom lane From Dylan.Kuhn@navy.mil Tue Jul 15 20:26:54 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 1EAD2CC480; Tue, 15 Jul 2003 20:26:54 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 38226-01; Tue, 15 Jul 2003 20:27:10 +0000 (GMT) Received: from Gate1-sdni.nmci.navy.mil (gate2-sandiego.nmci.navy.mil [138.163.0.41]) by svr1.postgresql.org (Postfix) with ESMTP id B5E78CC4A9; Tue, 15 Jul 2003 17:26:41 -0300 (ADT) Received: from Nawesdnims02.nadsuswe.nads.navy.mil by Gate1-sdni.nmci.navy.mil via smtpd (for developer.postgresql.org [64.117.224.193]) with ESMTP; Tue, 15 Jul 2003 20:21:17 -0700 Received: (private information removed) Received: (private information removed) Received: (private information removed) Received: (private information removed) Received: (private information removed) X-MimeOLE: Produced By Microsoft Exchange V6.0.5762.3 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: problems with pg_restore Date: Tue, 15 Jul 2003 13:26:37 -0700 Message-ID: <78ED1F30F0D85B4698FBD6CF2A5AC8F5A576FB@NAWECHLKEX01VA.nadsuswe.nads.navy.mil> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] problems with pg_restore Thread-Index: AcNLDCN8ZVScGlR1SF+ztBFAO7MVaQAAOP/A From: "Kuhn, Dylan K (4520500D)" To: Cc: "PostgreSQL Development" X-OriginalArrivalTime: 15 Jul 2003 20:26:37.0698 (UTC) FILETIME=[645DFE20:01C34B0F] X-Scanned-By: MIMEDefang 2.1 (www dot roaringpenguin dot com slash mimedefang) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/140 X-Sequence-Number: 9480 > Given the current implementation, it seems like there are=20 > three possible > behaviors for COMMENT ON DATABASE when the database name=20 > isn't the same > as the current database: >=20 > 1. Raise error (what we're doing now). Simple but breaks dump scripts > for the restore-into-different-DB scenario. >=20 > 2. Do nothing, store the comment in the current DB's pg_description > (what we did in 7.2). Now that I think about it, this also fails > for different-database restore, since very possibly the attempt > to look up the DB name will fail --- you'll get a no-such-database > error instead of the present error, but it's still unhelpful. >=20 > 3. Ignore the specified DB name, store the comment as the description > of the current DB; possibly give a warning saying we're doing so. > This would allow correct restoration of dumps into different DBs, > but I think people would find it awfully surprising :-( >=20 The behavior and syntax of COMMENT ON DATABASE was surprising to me from th= e start. IMHO, a warning that doesn't affect my current transaction is a m= ore pleasant surprise than an error that aborts it. It would be nice if th= ere was an easier way to undo a mistake, but again I would rather finish my= transaction with an incorrect comment than have to do it over again, so I'= d take door #3. Could there be a #4, drop support for COMMENT ON DATABASE, ignoring it and = issuing a warning that it is no longer supported? It's hard to argue that = it's very useful as it stands, and you've already recommended against using= it. -dylan-=20=20 From rbt@rbt.ca Tue Jul 15 20:29:59 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id B5EB4CC480; Tue, 15 Jul 2003 20:29:59 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 62571-08; Tue, 15 Jul 2003 20:30:26 +0000 (GMT) Received: from mailav.tor1.inquent.com (mail.inquent.com [216.208.117.106]) by svr1.postgresql.org (Postfix) with ESMTP id 7E39ECC4A9; Tue, 15 Jul 2003 17:29:58 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mailav.tor1.inquent.com (Postfix) with ESMTP id BC6791025B; Tue, 15 Jul 2003 16:23:35 -0400 (EDT) Received: from [10.0.2.7] (unknown [10.0.2.7]) by mailav.tor1.inquent.com (Postfix) with ESMTP id A9BBA1025C; Tue, 15 Jul 2003 16:23:32 -0400 (EDT) Subject: Re: problems with pg_restore From: Rod Taylor To: Tom Lane Cc: "Kuhn, Dylan K (4520500D)" , pgsql-admin@postgresql.org, PostgreSQL Development In-Reply-To: <8299.1058299393@sss.pgh.pa.us> References: <78ED1F30F0D85B4698FBD6CF2A5AC8F5A576F9@NAWECHLKEX01VA.nadsuswe.nads.navy.mil> <23211.1058286389@sss.pgh.pa.us> <1058298650.9981.117.camel@jester> <8299.1058299393@sss.pgh.pa.us> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-qX5OfVgMaDzmhwOnlwGH" Message-Id: <1058301058.9981.126.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.3 Date: 15 Jul 2003 20:30:59 +0000 X-Virus-Scanned: by AMaViS perl-11 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/183 X-Sequence-Number: 9523 --=-qX5OfVgMaDzmhwOnlwGH Content-Type: text/plain Content-Transfer-Encoding: quoted-printable > 3. Ignore the specified DB name, store the comment as the description > of the current DB; possibly give a warning saying we're doing so. > This would allow correct restoration of dumps into different DBs, > but I think people would find it awfully surprising :-( I like this one for 7.4 (with warning) but remove the requirement to supply a dbname at all with a warning about the deprecated syntax in 7.4. 7.4 pg_dump should not provide dbname. Remove the ability to supply database name completely in 7.5. --=-qX5OfVgMaDzmhwOnlwGH Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA/FGSC6DETLow6vwwRAsIYAJ94q/v09bC1W/xbB3m45PRQu1db8QCghg4l ADFzi7B/wx79kSSdQhF/eH4= =mG3y -----END PGP SIGNATURE----- --=-qX5OfVgMaDzmhwOnlwGH-- From alvherre@dcc.uchile.cl Tue Jul 15 20:46:46 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id D6EBCCC493; Tue, 15 Jul 2003 20:46:46 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 01786-06; Tue, 15 Jul 2003 20:47:12 +0000 (GMT) Received: from cm-lcon2-53-101.cm.vtr.net (CM-lcon2-53-101.cm.vtr.net [200.83.53.101]) by svr1.postgresql.org (Postfix) with ESMTP id 5A6A9CC406; Tue, 15 Jul 2003 17:46:44 -0300 (ADT) Received: by cm-lcon2-53-101.cm.vtr.net (Postfix, from userid 500) id 7C5AC80081; Tue, 15 Jul 2003 16:47:04 -0400 (CLT) Date: Tue, 15 Jul 2003 16:47:04 -0400 From: Alvaro Herrera To: Tom Lane Cc: Rod Taylor , "Kuhn, Dylan K (4520500D)" , pgsql-admin@postgresql.org, PostgreSQL Development Subject: Re: [HACKERS] problems with pg_restore Message-ID: <20030715204704.GC9410@dcc.uchile.cl> References: <78ED1F30F0D85B4698FBD6CF2A5AC8F5A576F9@NAWECHLKEX01VA.nadsuswe.nads.navy.mil> <23211.1058286389@sss.pgh.pa.us> <1058298650.9981.117.camel@jester> <8299.1058299393@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <8299.1058299393@sss.pgh.pa.us> User-Agent: Mutt/1.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/184 X-Sequence-Number: 9524 On Tue, Jul 15, 2003 at 04:03:13PM -0400, Tom Lane wrote: > Given the current implementation, it seems like there are three possible > behaviors for COMMENT ON DATABASE when the database name isn't the same > as the current database: There's a fourth possibility: ignore the command and issue a WARNING. Restores the database in both cases (in same database and in a different one), and sets the correct comment only if the database name is correct, giving a hint that the comment should be manually set. -- Alvaro Herrera () "I can't go to a restaurant and order food because I keep looking at the fonts on the menu. Five minutes later I realize that it's also talking about food" (Donald Knuth) From tgl@sss.pgh.pa.us Wed Jul 16 14:43:07 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 4F87ECC631 for ; Wed, 16 Jul 2003 14:43:07 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 39905-03 for ; Wed, 16 Jul 2003 14:43:08 +0000 (GMT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id F197ACC68D for ; Wed, 16 Jul 2003 11:42:45 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h6GEfdQ3018173; Wed, 16 Jul 2003 10:41:40 -0400 (EDT) To: Alvaro Herrera Cc: Rod Taylor , "Kuhn, Dylan K (4520500D)" , pgsql-admin@postgresql.org, PostgreSQL Development Subject: Re: [HACKERS] problems with pg_restore In-reply-to: <20030715204704.GC9410@dcc.uchile.cl> References: <78ED1F30F0D85B4698FBD6CF2A5AC8F5A576F9@NAWECHLKEX01VA.nadsuswe.nads.navy.mil> <23211.1058286389@sss.pgh.pa.us> <1058298650.9981.117.camel@jester> <8299.1058299393@sss.pgh.pa.us> <20030715204704.GC9410@dcc.uchile.cl> Comments: In-reply-to Alvaro Herrera message dated "Tue, 15 Jul 2003 16:47:04 -0400" Date: Wed, 16 Jul 2003 10:41:39 -0400 Message-ID: <18172.1058366499@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/141 X-Sequence-Number: 9481 Alvaro Herrera writes: > On Tue, Jul 15, 2003 at 04:03:13PM -0400, Tom Lane wrote: >> Given the current implementation, it seems like there are three possible >> behaviors for COMMENT ON DATABASE when the database name isn't the same >> as the current database: > There's a fourth possibility: ignore the command and issue a WARNING. Hmm, that seems like a reasonable choice. Anyone have an objection? regards, tom lane From andrew.biagioni@e-greek.net Wed Jul 16 22:38:10 2003 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 0F2BACC78B; Wed, 16 Jul 2003 22:38:10 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 14475-04; Wed, 16 Jul 2003 22:38:24 +0000 (GMT) Received: from proteus.affordablehost.com (proteus.affordablehost.com [216.46.203.177]) by svr1.postgresql.org (Postfix) with ESMTP id 732C7CC7C4; Wed, 16 Jul 2003 19:37:58 -0300 (ADT) Received: from [66.92.74.181] (helo=e-greek.net) by proteus.affordablehost.com with esmtp (Exim 4.20) id 19cuuF-0005JS-4Y; Wed, 16 Jul 2003 18:37:51 -0400 Message-ID: <3F15D3C0.4060707@e-greek.net> Date: Wed, 16 Jul 2003 18:37:52 -0400 From: Andrew Biagioni User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3.1) Gecko/20030425 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Alvaro Herrera , Rod Taylor , "Kuhn, Dylan K (4520500D)" , pgsql-admin@postgresql.org, PostgreSQL Development Subject: Re: [HACKERS] problems with pg_restore References: <78ED1F30F0D85B4698FBD6CF2A5AC8F5A576F9@NAWECHLKEX01VA.nadsuswe.nads.navy.mil> <23211.1058286389@sss.pgh.pa.us> <1058298650.9981.117.camel@jester> <8299.1058299393@sss.pgh.pa.us> <20030715204704.GC9410@dcc.uchile.cl> <18172.1058366499@sss.pgh.pa.us> In-Reply-To: <18172.1058366499@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------020801000605030407020604" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/142 X-Sequence-Number: 9482 This is a multi-part message in MIME format. --------------020801000605030407020604 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >Alvaro Herrera writes: > > >>On Tue, Jul 15, 2003 at 04:03:13PM -0400, Tom Lane wrote: >> >> >>>Given the current implementation, it seems like there are three possible >>>behaviors for COMMENT ON DATABASE when the database name isn't the same >>>as the current database: >>> >>> > > > >>There's a fourth possibility: ignore the command and issue a WARNING. >> >> > >Hmm, that seems like a reasonable choice. Anyone have an objection? > My personal experience would lead me to believe that this is the best option. Count it a vote in favor. > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > Andrew Biagioni --------------020801000605030407020604 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote:
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
  
On Tue, Jul 15, 2003 at 04:03:13PM -0400, Tom Lane wrote:
    
Given the current implementation, it seems like there are three possible
behaviors for COMMENT ON DATABASE when the database name isn't the same
as the current database:
      

  
There's a fourth possibility:  ignore the command and issue a WARNING.
    

Hmm, that seems like a reasonable choice.  Anyone have an objection?

My personal experience would lead me to believe that this is the best option.  Count it a vote in favor.
			regards, tom lane

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

               http://archives.postgresql.org

        Andrew Biagioni

--------------020801000605030407020604--