Обсуждение: [repost] pg_restore doesn't work with custom format?
This looks like a bug, or can somebody explain how I'm supposed to restore a custom archive dump? $ createdb foo CREATE DATABASE $ psql foo foo=# create table users (id serial); NOTICE: CREATE TABLE will create implicit sequence 'users_id_seq' for SERIAL column 'users.id' NOTICE: CREATE TABLE/UNIQUE will create implicit index 'users_id_key' for table 'users' CREATE foo=# \q $ pg_dump --blobs --clean --create --file=/tmp/foo.dump --format=c --compress=9 foo $ dropdb foo DROP DATABASE $ pg_restore --create -d test /tmp/foo.dump Archiver(db): Could not execute query. Code = 7. Explanation from backend: 'ERROR: ProcedureCreate: procedure pltcl_call_handler already exists with same arguments '. $ pg_restore --create --clean -d test /tmp/foo.dump Archiver(db): Could not execute query. Code = 7. Explanation from backend: 'ERROR: index "users_id_key" does not exist '. What's the magic command that I am looking for?? Allan
Allan Engelhardt <allane@cybaea.com> writes:
> This looks like a bug, or can somebody explain how I'm supposed to
> restore a custom archive dump?
It looks to me like pg_restore is a tad confused, or at least confusing,
about how it handles selection of database. One thing we ought to
figure out is whether a -d switch on the command line overrides the
original database name taken from the dump file. I would think that
it should: if I say -d then that's where I want the data restored to.
But it doesn't work that way at the moment. What seems to actually
happen if you specify --create is that it first connects to the -d
database for just long enough to issue a CREATE DATABASE command for
the same dbname seen in the dump file, then switches to that database.
On the other hand, if you *don't* say --create then it does indeed
restore into the -d database. This is inconsistent to say the least.
I suggest that -d ought always to be the target database if it's
specified. If we are given --create, then connect to template1
initially to create the DB, same as "createdb" would do. If -d is
not given, then use the dbname in the dump file as the default target.
The handling of --clean --create is even more broken: it connects
to the -d database, tries to issue the DROP commands there, ending
with a DROP DATABASE for the dump file's dbname; then it recreates
and connects to that dbname and restores there. This is just plain
silly and can never succeed (if you do make -d equal to the target,
so that the initial DROPs work, then the DROP DATABASE will fail).
My vote would be that pg_restore ought NEVER issue a DROP DATABASE.
It's just too damn risky to do that. Let the user do it by hand
first, if that's what he really wants.
In short, I think the behavior ought to be:
1. Target database is named by -d switch, else default to dbname
from dump file.
2. If --create specified, connect to template1 and issue CREATE DATABASE
for target.
3. Connect to target database.
4. If --clean specified, issue DROP commands. (Note that --clean
--create is not too sensible since DROPs cannot be needed in a new
database. Should we ignore --clean if --create was given too?)
5. Issue create and data loading commands.
Comments?
regards, tom lane
> $ createdb foo
> CREATE DATABASE
> $ psql foo
> foo=# create table users (id serial);
> NOTICE: CREATE TABLE will create implicit sequence 'users_id_seq' for
> SERIAL column 'users.id'
> NOTICE: CREATE TABLE/UNIQUE will create implicit index 'users_id_key'
> for table 'users'
> CREATE
> foo=# \q
> $ pg_dump --blobs --clean --create --file=/tmp/foo.dump --format=c
> --compress=9 foo
> $ dropdb foo
> DROP DATABASE
> $ pg_restore --create -d test /tmp/foo.dump
> Archiver(db): Could not execute query. Code = 7. Explanation from
> backend: 'ERROR: ProcedureCreate: procedure pltcl_call_handler already
> exists with same arguments
> '.
> $ pg_restore --create --clean -d test /tmp/foo.dump
> Archiver(db): Could not execute query. Code = 7. Explanation from
> backend: 'ERROR: index "users_id_key" does not exist
> '.
> What's the magic command that I am looking for??
> Allan
On Fri, 19 Oct 2001, Tom Lane wrote: > 1. Target database is named by -d switch, else default to dbname > from dump file. > > 2. If --create specified, connect to template1 and issue CREATE DATABASE > for target. Should there be an option for what database as template for doing the create when you're doing a --create? Just wondering if there might be a case where you'd want to create based on template0 if you're moving between machines where template1 is different and you don't want the extra stuff. > 3. Connect to target database. > > 4. If --clean specified, issue DROP commands. (Note that --clean > --create is not too sensible since DROPs cannot be needed in a new > database. Should we ignore --clean if --create was given too?) > > 5. Issue create and data loading commands. Seems sensible. I think pg_dump should probably follow similar rules for --create and --clean when making dump files that you're going to run through psql.
> On Fri, 19 Oct 2001, Tom Lane wrote:
>> 1. Target database is named by -d switch, else default to dbname
>> from dump file.
After further looking at this, I've decided that the existing behavior
of pg_dump and pg_restore is actually reasonable, it's just poorly
documented. Accordingly, I fixed the documents.
The only code change I applied was to disallow the combination of
--create and --clean options. This combination doesn't have any
practical use (if you're going to create a fresh database, what's
the point of issuing DROP commands for the individual items?),
and it isn't implemented correctly (the DROPs are applied to the
wrong database). I don't see any point in trying to make it work
sensibly, since it has no value anyway.
regards, tom lane
what document have you updated?
Can we download it?
Felipe
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: terça-feira, 23 de outubro de 2001 20:19
To: Stephan Szabo
Cc: Allan Engelhardt; Philip Warner; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] [repost] pg_restore doesn't work with custom
format?
> On Fri, 19 Oct 2001, Tom Lane wrote:
>> 1. Target database is named by -d switch, else default to dbname
>> from dump file.
After further looking at this, I've decided that the existing behavior
of pg_dump and pg_restore is actually reasonable, it's just poorly
documented. Accordingly, I fixed the documents.
The only code change I applied was to disallow the combination of
--create and --clean options. This combination doesn't have any
practical use (if you're going to create a fresh database, what's
the point of issuing DROP commands for the individual items?),
and it isn't implemented correctly (the DROPs are applied to the
wrong database). I don't see any point in trying to make it work
sensibly, since it has no value anyway.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Felipe Nascimento <Felipe.Nascimento@multivalor.com.br> writes: > what document have you updated? The CVS version of the pg_dump/pg_restore reference pages. > Can we download it?=09 Sure, from the CVS server, or at the usual places for snapshots, or just read the development docs online... http://candle.pha.pa.us/main/writings/pgsql/sgml/reference-client.html regards, tom lane