Thread: [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