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
Best Answer
Yes there is.
Have you tried the pg_restore --disable-triggers??? yet
If that fails to work which would be odd
You can restore just the schema with the --schema flag, then modify the table "drop the constraints" in question" then restore the table contents
couple of useful articles Different Dumps
and my long ago written wiki article on backups