Postgresql – What does this error mean when doing a pg_restore on a Postgres 9.6.2 database

pg-restorepostgresqlpostgresql-9.6

So I have a rails 5.0.x application running on Heroku with Postgres 9.6.1. I pull down and do a pg_restore --exit-on-error --verbose --clean --no-acl --no-owner -h localhost -d database_development ~/Documents/Backups/myproduction.dump

I get the following error:

pg_restore: connecting to database for restore
pg_restore: dropping FK CONSTRAINT trial_versions fk_rails_f888baa05c
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3091; 2606 16768 FK CONSTRAINT trial_versions fk_rails_f888baa05c u7dok58iar41mh
pg_restore: [archiver (db)] could not execute query: ERROR:  constraint "fk_rails_f888baa05c" of relation "trial_versions" does not exist
    Command was: ALTER TABLE ONLY "public"."trial_versions" DROP CONSTRAINT "fk_rails_f888baa05c";

What is this error telling me? I've never had this issue before. This just started happening recently and I need to first understand the error before I can even begin to troubleshoot it.

Best Answer

The database the dump file was taken from has a certain foreign key constraint defined. But your target database does not. Probably because someone went and added it to the dumped database since the last dump. Since you specified --clean, it is trying to drop all the objects that exist in the dump file from your target database before recreating them. It is failing, because it doesn't exist in your target database to be dropped.

You can add --if-exists to the restore command. Or you can remove --exit-on-error from the command, and just ignore the error message you get.

Related Question