Re: pg_restore questions

William Garrison wrote:
> I have been optimizing my pg_restores (postgres 8.2.9 on Windows) and I
> am confused by some of the results I get when combining various
> command-line options.
>
> The -c option for "clean" does not do DROP IF EXISTS statements, it just
> does DROP. This results in an error if the object does not exist. So
> the -c option creates the requirement that the schema must already
> exist. Was that intentional? This means that -c is incompatible with
> -1 (single transaction) unless the existing matches the schema of the
> database that was dumped since because it won't ignore errors if -1 is
> specified. Which means I lose my optimizations (a prior thread
> concluded that -1 is necessary for the COPY optimization during restores)

Never tried combining the two. In fact, I'm not sure I've ever used the
"clean" option in anger. You could check if that's still the case in 8.3
and if so either (a) raise a bug report or (b) raise a bug report and
supply a patch :-)

> The -C option for "create" does not work with -1 (single transaction),
> since it results in an error stating that CREATE DATABASE commands
> cannot be part of a transaction. It seems to me that the pg_restore
> command should know this, and create the database first, then start the
> transaction.

That's reasonable.

> Another problem with -C is that if I haven't created the database
> already, it gives an error that it doesn't exist. I thought that -C was
> supposed to create the database for me. It seems like it checks if the
> database exists first. Is that because I am using the -d option?
> (Didn't try removing that, and my restore is now running...) Maybe -d
> checks for the database before -C can create it? In that case, -C
> should have complained when it tried to create a database that was
> already there. Either way, I seem to have to manually create the
> database before running pg_restore.

The -d option is the database to connect to, so if you're using -C too
you need to do something like:
pg_restore -U postgres -d existing_db -C new_db
It is covered in the manuals, but there are a lot of options, so it
would be easy to miss.

> I realized that I need to do the restore in two steps: one to create the
> schema, and another to restore the data. This will allow me to create
> the database from scratch, without relying on -c to drop things, then
> after it creates the schema I can load the data using -1 for speed. So
> I manually created the database, and did a pg_restore with -C -s. But
> when I tried to do a data-only restore with -a, it complained about the
> foreign key constraints: (I removed the table names and stuff since it
> is under NDA)

You're better off just creating an empty db and doing the schema+data at
the same time. Doing the schema first will mean all your indexes,
triggers etc. are in place. Doing both together lets it build indexes in
one go after the data is in place.

Try the combination of -d/-C as described above, I think that's what
you're after.

--
Richard Huxton
Archonet Ltd