How do I reliably determine whether pg_restore succeeded, when success sometimes results in an exit code of 1?

Question

When running pg_restore --clean --dbname=my_database backup_file.sql to restore a database dump to an empty database, the restore succeeds, 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 the message indicates, the restore succeeded. There were errors, but pg_restore claims to have ignored them. I was also able to manually query the database to verify that all data I expected to be in the dump was present in the database after the restore.

The problem is the command above exited with a status of 1, not 0. When performing database restores programmatically (as I intend to do when I automate this process), this is problematic, since my script needs to be able to reliably determine whether the restore succeeded or not.

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

Note that I am currently using PostgreSQL 9.1.

Just adding the `--clean` option helped me. I was trying to read the exit status in bash using `$?`. When running it without `--clean` option, `$?` returned `1`, whereas running it with `--clean` option `$?` returned `0` — Manoj, Jul 03 '18 at 05:23

score 23 · Accepted Answer · edited May 23 '17 at 10:29

Turns out Postgres doesn't actually know that the error mentioned in the question is relatively harmless; that's not why the error is ignored. The reason pg_restore is actually ignoring that error is because pg_restore is configured by default to ignore nearly all errors that occur during the restore process. If you care about the success/failure status of the restore, this probably isn't the behavior you want. Running pg_restore with the --exit-on-error or --single-transaction options will remedy this, but will also cause Postgres to treat the error in the question above as a full blown fatal error, not just a warning (because again, it doesn't actually know that it's okay for that particular command to fail).

The best solution to this is to take steps to prevent the error from happening in the first place. In this case, you'd probably do this by dropping tables with a separate command before running pg_restore, and leaving off the --clean option.

How do I reliably determine whether pg_restore succeeded, when success sometimes results in an exit code of 1?

1 Answers1