Re: [repost] pg_restore doesn't work with custom format?

Allan Engelhardt <allane(at)cybaea(dot)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