Restore PostgreSQL db from backup without foreign key constraint issue

Question

I have a postgresql db with about 85+ tables. I make backups regularly using pg_dump (via php-pgadmin) in copy mode and the size of the backup file is almost 10-12 MB. Now the problem I am facing is that whenever I try to restore the database, foreign key constraint problem occur. The scenario is as follows:

There are two tables: 1) users and 2) zones. I have stored the id of zone in users table to identify the user's zone and have set it as foreign key.

When I take the db dump, the entries for table zones come only after that of table users. I think it's due to the first letter of table name: u comes before z, and therefore when I restore the database, a foreign key constraint problem occurs and the execution stops. The same problem occurs when I try to restore the db structure, it says the table zones does not exist in the database since the structure of zones comes after that of users in the dump file.

Is there any solution for this? Is there any other backup method feasible?

Answer 1

Sounds like you're getting an SQL dump rather than a binary dump from pg_dump. That would give you a big pile of SQL with the schema (including FKs) at the top followed by a bunch of INSERTs to reload the data. A binary dump from pg_dump would serve you better, it looks like you need a bit of extra configuration to tell PhpPgAdmin where pg_dump is. Then you'd feed that binary dump into pg_restore and pg_restore would rebuild everything in the proper order to avoid referential integrity issues (or, more accurately, pg_restore would restore all the data then add the constraints).

PhpPgAdmin seems to want to work with plain SQL dumps rather than pg_restore. I find this hard to believe but I can't find anything in the documentation about invoking pg_restore. If this is true then you'll probably have to hand-edit the SQL dump and move all the FKs to the end.

You could also try adding SET CONSTRAINTS ALL DEFERRED; at the top of your SQL dump, that should delay constraint checking until the end of the transaction, you'll also want to make sure that the entire block of INSERTs is contained within a transaction.

If PhpPgAdmin really can't invoke pg_restore then you're better off using using pg_dump and pg_restore by hand so that you have the necessary control over your backup procedures. Sorry but any database admin tool that can't handle backing up a database with FKs is worse than useless. Hopefully someone that knows their way around PhpPgAdmin will show up and let us know how to use pg_restore with PhpPgAdmin.

Answer 2

I found that you could add in the beginning of the sql (this will stop foreign key checks):

SET session_replication_role = replica;

and at the end (to restore the checks):

SET session_replication_role = origin;

Answer 3

If it helps anybody: none of the previous solutions suggested worked for me (there was some INSERTs made referencing data that was dumped later on, independent if it was in binary format, or plain SQL queries).

What I did: I used schemaspy, a script that -among other features, such as a really useful html diagram of the underlying ER model - it generates two very useful lists: a "insertion order" (where all of your tables are listed as an optimal order in order to perform insertions, considering existing restrictions and dependences), and a "deletion order" (very useful in order to DROP tables).

If you want a sample, check this http://schemaspy.sourceforge.net/sample/. In particular, there are two sample lists I mentioned just above (tried to post direct links but spam prevention mechanism allows me to post just 2 links).

Answer 4

I faced that situation once, I had the backup of the structure and the data in separated files, so this is how I was able to restore the information:

ALTER TABLE table_name DISABLE TRIGGER ALL;

restore the database from pg Admin

ALTER TABLE table_name ENABLE TRIGGER ALL;