1

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

Improve this question
1
  • Could you add the exact error message that you encounter during the restore? It might help to find the root cause. Your question is valid, but is missing the details. Adding the details to your question via the edit button might help us to assist you in receiving an answer. (Possibly add the pg_dump command that was issued too).
    – John K. N.
    Dec 13, 2019 at 14:40

2 Answers 2

Reset to default
1

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

Improve this answer
4
  • Yes, --disable-triggers also failed.
    – MickeyfAgain_BeforeExitOfSO
    Dec 12, 2019 at 22:33
  • then do the pg_restore --schema flag see if that works, what version of PG is this?? You can always just dump a schema only from the live database if there are no structure changes, or create just that table in a new database and restore that way. If that fails the backup may be damaged.
    – zsheep
    Dec 12, 2019 at 22:37
  • I will try these things but will be away from my machine for a few days so don't think I don't appreciate your help if I seem uncommunicative for a while. PG 11. I am really hoping for a fully-automated-no-manual-intervention-restore-into-nothing-from-scratch solution....
    – MickeyfAgain_BeforeExitOfSO
    Dec 12, 2019 at 22:41
  • FYI if this continues to go south go to the postgresql general mailing list with this problem. Guys who know the C code inside and out and write Postgresql all give helping hands..
    – zsheep
    Dec 12, 2019 at 22:44
0

...backup that was created using "custom format" so that a single table could be extracted from it later.

I need to restore the entire DB from scratch, both the structure and the data.

Your original intention was to restore just one table, but now you want to restore the entire thing? If so, I don't understand the problem. If you restore the entire thing into an empty database, the objects should be restored in an order where it just works. Are you not restoring into an empty database?

Could you share the command lines both for taking the backup, and for restoring it?

Improve this answer
1
  • I needed to have the option of restoring the entire DB when used for a new installation, or restoring a single table containing version information when updating an existing installation. The complete DB includes a number of tables with look up data that is fixed for every installation/version combination, as well as empty tables that become populated on an installation-specific basis. In the case where all that was needed was the version information overwriting the existing DB was not an option, and restoring the entire DB into a dummy DB seemed undesirable.
    – MickeyfAgain_BeforeExitOfSO
    Dec 16, 2019 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.