I need to restore a PostgreSQL DB from a backup that was created using "custom format" so that a single table could be extracted from it later. (Per the documentation individual tables cannot be automatically extracted from a plain text backup.)
Because the backup was created as a binary file I do not have the option of editing it, and can only restore it with
pg_restore. Because it is not loaded in the DB, I don't have the option of setting the constrains disabled. (catch-22)
When I try to use
pg_restore to restore the custom dump, it fails because of foreign key constraints.
I have looked at perhaps a dozen or more posts on
pg_restore and foreign key constraints, but they all require that the DB be already loaded in PostgreSQL so that I can disable the constraints.
I need to restore the entire DB from scratch, both the structure and the data. The backup already exists. I am not looking for a different way to dump it in the first place (although I'd like to learn about that if that is the only option). I have not found anything in the documentation for either
pg_restore that addresses this.
I'm finding it difficult to think that PostgreSQL would allow a non-restorable dump to be created. Surely there is a simple answer to this?
The Question: Is there a way to restore a custom format dumped PostgreSQL DB, both schema and data, that contains numerous foreign key constraints?
I am more than slightly surprised, but as zsheep suggested, it appears the backup was damaged.
Starting from scratch, I can now go through the entire process of backup, remove the existing DB, and restore from backup (confirming at each step) with no failures or errors:
pg_dump -Fc -f DBnnn-BLANK my_db
psql -U my_user -d postgres
drop database my_db;\q
createdb -T template1 my_db
pg_restore -d my_db DBnnn-BLANK