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_dump
or 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?
---- EDIT:
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
pg_dump
command that was issued too).