Re: Spurious error messages from pg_restore

There may be implementation reasons for this, but try to see it from a
user's point of view. An error, to a user, means "something went wrong;
whatever you tried to do didn't work". If a message is harmless, it
should be, at best, a warning. From a more practical point of view, it's
not realistic review all the "does not exist" messages, as you suggest -
there are literally thousands of them. So, as it is, I cannot be sure
whether the restore succeeded or failed and that's bad.

Also, let's take a step back and consider what "restore" means to a
user. It does not mean "drop some objects, create some objects, insert
some rows, etc." Those are implementation details. To a user, restore
means "make the database/schema/table exactly like it was when I did the
backup". The current state of the object is irrelevant. So I can't even
see any good reason for the --clean option to exist at all.

It may not be possible to achieve this in practice given the way
pg_restore is implemented, I don't know, but I think it should be the
aim. If something as simple as adding "IF EXISTS" and "OR REPLACE" can
make a major difference then it would be a step in the right direction.

Basically, as a user, I find PG backups/restores to be a bit of a
minefield. You can go through it, but you have to do it /just right/.
The reason I originally added the --clean option to my script is that
the restore failed without it (since I did have some objects in the
database). So now at least I know that to restore the entire DB I need
to drop it, re-create it, and run pg_restore without --clean (and ignore
those 3 errors I mentioned). It took me a long time to figure this out,
though, and such pain points are unnecessary. Backups and restores
should "just work". They do in MSSQL and I think they can in Postgres, too.

Regards,

Evan

On 04.09.2013 23:26, David Johnston wrote:
> Evan Martin wrote
>> When I use pg_restore with --clean to restore a PostgreSQL 9.2.4database
>> into a new, blank database it generates thousands of error messages like
>> this:
>>
>> pg_restore: [archiver (db)] could not execute query: ERROR: schema
>> "myschema" does not exist
>> Command was: DROP INDEX myschema.some_index;
> The "--clean" parameter is an "option" that you can enable if you feel it
> will provide value to your routine. If you are installing into a "new,
> blank database" the clean option has no value to you and you should not be
> using it.
>
> The resulting log, when using this option, needs to be processed using tools
> like "grep" so that you can:
> A) identify and scan over all those "does not exist" messages
> B) isolate and review any other message not containing "does not exist"
>
> There are lots on inter-related pieces involved in the whole dump/restore
> process. Your current use-case and example for the "--clean" option are not
> going to convince anyone that something requires fixing.
>
> That said bringing up stuff like this is always welcome. It helps people to
> learn and also provides insight to the developers as to what kinds of usage
> exists in the wild. Additional application of "IF EXISTS" likely would be
> welcome but the "--clean" option is rarely going to be noise-less.
>
> David J.
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/Spurious-error-messages-from-pg-restore-tp5769545p5769625.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>