pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems with pg_restore
12+ messages / 7 participants
[nested] [flat]
* Problems with pg_restore
@ 2003-03-18 13:22 lists <lists@insyde.com.br>
0 siblings, 1 reply; 12+ messages in thread
From: lists @ 2003-03-18 13:22 UTC (permalink / raw)
To: pgsql-admin
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?
TIA,
Ricardo
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Problems with pg_restore
@ 2003-03-24 18:43 Robert Treat <xzilla@users.sourceforge.net>
parent: lists <lists@insyde.com.br>
0 siblings, 0 replies; 12+ messages in thread
From: Robert Treat @ 2003-03-24 18:43 UTC (permalink / raw)
To: lists <lists@insyde.com.br>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* problems with pg_restore
@ 2003-07-15 14:55 Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>
0 siblings, 1 reply; 12+ messages in thread
From: Kuhn, Dylan K (4520500D) @ 2003-07-15 14:55 UTC (permalink / raw)
To: pgsql-admin
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 plpgsql_call_handler already exists with same argument types
Ok, I had installed PLPGSQL in template1, which has been useful in other situations. 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 comments may only be applied to the current database
I'm not sure how to get around this one. Can an archived database with comments be restored to a database with a different name? Are the comments the only thing missing from the restored database, or could there be other things?
thanks,
Dylan Kuhn
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: problems with pg_restore
@ 2003-07-15 16:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2003-07-15 16:26 UTC (permalink / raw)
To: Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>; +Cc: pgsql-admin; pgsql-hackers@postgresql.org
"Kuhn, Dylan K (4520500D)" <Dylan.Kuhn@navy.mil> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: problems with pg_restore
@ 2003-07-15 16:54 Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>
0 siblings, 0 replies; 12+ messages in thread
From: Kuhn, Dylan K (4520500D) @ 2003-07-15 16:54 UTC (permalink / raw)
To: pgsql-admin
> 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.
I agree. I plan to delete it and try again, and avoid using database comments in the future. This seems to work:
DELETE FROM ONLY pg_description
WHERE pg_description.objoid=
(SELECT pg_database.oid FROM pg_database WHERE pg_database.datname=current_database())
AND pg_description.classoid=
(SELECT pg_class.relfilenode FROM pg_class WHERE pg_class.relname='pg_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 - probably anyone who tries to use database comments has noticed the limitations.
-dylan-
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: problems with pg_restore
@ 2003-07-15 19:50 Rod Taylor <rbt@rbt.ca>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Rod Taylor @ 2003-07-15 19:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>; pgsql-admin@postgresql.org, PostgreSQL Development <pgsql-hackers@postgresql.org>
> 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?
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.';
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: problems with pg_restore
@ 2003-07-15 20:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rod Taylor <rbt@rbt.ca>
0 siblings, 2 replies; 12+ messages in thread
From: Tom Lane @ 2003-07-15 20:03 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>; pgsql-admin@postgresql.org, PostgreSQL Development <pgsql-hackers@postgresql.org>
Rod Taylor <rbt@rbt.ca> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: problems with pg_restore
@ 2003-07-15 20:26 Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>
0 siblings, 0 replies; 12+ messages in thread
From: Kuhn, Dylan K (4520500D) @ 2003-07-15 20:26 UTC (permalink / raw)
To: pgsql-admin; +Cc: PostgreSQL Development <pgsql-hackers@postgresql.org>
> 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 :-(
>
The behavior and syntax of COMMENT ON DATABASE was surprising to me from the start. IMHO, a warning that doesn't affect my current transaction is a more pleasant surprise than an error that aborts it. It would be nice if there 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-
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: problems with pg_restore
@ 2003-07-15 20:30 Rod Taylor <rbt@rbt.ca>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: Rod Taylor @ 2003-07-15 20:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>; pgsql-admin@postgresql.org, PostgreSQL Development <pgsql-hackers@postgresql.org>
> 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.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [HACKERS] problems with pg_restore
@ 2003-07-15 20:47 Alvaro Herrera <alvherre@dcc.uchile.cl>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Alvaro Herrera @ 2003-07-15 20:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rod Taylor <rbt@rbt.ca>; Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>; pgsql-admin@postgresql.org, PostgreSQL Development <pgsql-hackers@postgresql.org>
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 (<alvherre[a]dcc.uchile.cl>)
"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)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [HACKERS] problems with pg_restore
@ 2003-07-16 14:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@dcc.uchile.cl>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2003-07-16 14:41 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@dcc.uchile.cl>; +Cc: Rod Taylor <rbt@rbt.ca>; Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>; pgsql-admin@postgresql.org, PostgreSQL Development <pgsql-hackers@postgresql.org>
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?
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: [HACKERS] problems with pg_restore
@ 2003-07-16 22:37 Andrew Biagioni <andrew.biagioni@e-greek.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Andrew Biagioni @ 2003-07-16 22:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; Rod Taylor <rbt@rbt.ca>; Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>; pgsql-admin@postgresql.org, PostgreSQL Development <pgsql-hackers@postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2003-07-16 22:37 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-18 13:22 Problems with pg_restore lists <lists@insyde.com.br>
2003-03-24 18:43 ` Robert Treat <xzilla@users.sourceforge.net>
2003-07-15 14:55 problems with pg_restore Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>
2003-07-15 16:26 ` Re: problems with pg_restore Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 19:50 ` Re: problems with pg_restore Rod Taylor <rbt@rbt.ca>
2003-07-15 20:03 ` Re: problems with pg_restore Tom Lane <tgl@sss.pgh.pa.us>
2003-07-15 20:30 ` Re: problems with pg_restore Rod Taylor <rbt@rbt.ca>
2003-07-15 20:47 ` Re: [HACKERS] problems with pg_restore Alvaro Herrera <alvherre@dcc.uchile.cl>
2003-07-16 14:41 ` Re: [HACKERS] problems with pg_restore Tom Lane <tgl@sss.pgh.pa.us>
2003-07-16 22:37 ` Re: [HACKERS] problems with pg_restore Andrew Biagioni <andrew.biagioni@e-greek.net>
2003-07-15 16:54 Re: problems with pg_restore Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>
2003-07-15 20:26 Re: problems with pg_restore Kuhn, Dylan K (4520500D) <Dylan.Kuhn@navy.mil>
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