How can I confidently determine if pg_restore succeeded when success sometimes leads to exit code 1?

Question

When pg_restore --clean --dbname=my_database backup_file.sql to restore a database dump to an empty database, recovery is successful, but with the following warning message:

 pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 161; 1259 16549 TABLE example_table root pg_restore: [archiver (db)] could not execute query: ERROR: table "example_table" does not exist Command was: DROP TABLE public.example_table; WARNING: errors ignored on restore: 1 

As you can see from the message, the restore is complete. There were errors, but pg_restore claims to have ignored them. I was also able to manually query the database to make sure that all the data that I expected to be in the dump was present in the database after recovery.

The problem is that the above command has the status 1, not 0. When the database execution is restored programmatically (as I intend to do, when I automate this process), this is problematic, since my script must be able to reliably determine whether it succeeded whether to recover or not.

Is there a way to make pg_restore ignore warnings when determining exit status? Or is there an alternative pg_restore method that I can use to get more accurate success / failure information? How can I restore the database and reliably determine programmatically whether I was able to recover?

Please note that I am currently using PostgreSQL 9.1.

Answer 1

Turns out Postgres doesn't actually know that the error mentioned in the question is relatively harmless; this is not why the error is ignored. The reason pg_restore actually ignores this error, because pg_restore by default to ignore almost all errors that occur during the recovery process. If you care about a success / failure recovery state, this is probably not the behavior you want. Running pg_restore with the parameters --exit-on-error or --single-transaction will fix this, but it will also make Postgres treat the error in the question above as a complete fatal error, and not just a warning (because again I don’t really know that it is normal for this particular command to crash).

The best solution for this is to take measures to prevent an error from occurring in the first place. In this case, you will probably do this by deleting the tables with a separate command before running pg_restore and leave the --clean .