Re: Spurious error messages from pg_restore - Mailing list pgsql-general
| From | Evan Martin |
|---|---|
| Subject | Re: Spurious error messages from pg_restore |
| Date | |
| Msg-id | 52288BD0.1050208@realityexists.net Whole thread Raw |
| In response to | Re: Spurious error messages from pg_restore (David Johnston <polobo@yahoo.com>) |
| List | pgsql-general |
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:
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 wroteWhen 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.
pgsql-general by date: