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?

Actually i send the dump i got from phppgadmin as sql via phppgadmin interface itself..... — Black Rider, Mar 19 '11 at 18:49

score 9 · Answer 1 · answered Sep 17 '18 at 11:19

9

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;

answered Sep 17 '18 at 11:19

venimus

5,907
2
28
38

mu is too short · Accepted Answer · 2011-03-19T19:41:25.807

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.

Yea pg_restore was what i wanted.. Now the data migration is working well but a new problem now.... when i import the db structure, and user pg_restore to restore it to a new database, error like "sequence X already exists" comes up.. i found that the problem is the data type of auto-increment fields are bigserial in the db dump, so postgres automatically creates a sequence based on the column name.. but below the create table statement the dump have a create sequence statement which creates a sequence with same name as the one auto generated by postgres and this throws an error..any solution? — Black Rider, Mar 23 '11 at 03:56
@Midhun: You should be restoring into an empty database, then `pg_restore` will set everything up. Or, if you have the schema already in place but no data, you can tell `pg_restore` to just restore the data. Sounds like the schema is already there and your restore is trying to restore the schema as well. — mu is too short, Mar 23 '11 at 04:22

score 4 · Answer 3 · answered Jul 13 '12 at 03:03

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).

these are the sample lists!!! http://schemaspy.sourceforge.net/sample/insertionOrder.txt http://schemaspy.sourceforge.net/sample/deletionOrder.txt — jquinter, Jul 13 '12 at 03:03

score 2 · Answer 4 · answered Dec 19 '19 at 03:38

2

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;

answered Dec 19 '19 at 03:38

Gabrielkdc

105
1
11

1

restoring from file with psql i had to include the schema prefix `public.` e. g. `ALTER TABLE public.table_name DISABLE TRIGGER ALL;` and `ALTER TABLE public.table_name ENABLE TRIGGER ALL;` – Jonathan Oct 28 '21 at 05:13

score 0 · Answer 5 · answered Mar 19 '11 at 07:38

0

using pgdump(via php-pgadmin)

Are you sure PhpPgAdmin is using pg_dump to create backups? I have never seen any dump made by pg_dump, having problems with foreign keys when restoring the dump.

PhpPgAdmin is just a PHP script and in most cases it won't have permissions to start a program like pg_dump.

answered Mar 19 '11 at 07:38

Frank Heikens

117,544
24
142
135

i believed that PhpPgAdmin uses pgdump for making backups... am i wrong??? [link]http://en.wikipedia.org/wiki/PhpPgAdmin[link] says it can use pg_dump.. but im not sure if the interface uses pg_dump when the dumping is done... Can someone please confirm it? – Black Rider Mar 19 '11 at 18:53

score 0 · Answer 6 · answered Mar 19 '11 at 18:52

0

I would remove the fk creation up front and add it to the end of the script.

answered Mar 19 '11 at 18:52

Kuberchaun

29,160
7
51
59

score 0 · Answer 7 · edited Jan 18 '23 at 05:42

I had a backup of my remote Postgres database as a .dump file, mybackup.dump

Now while restoring it on my local DB I used below command:

pg_restore -d local_db_name mybackup.dump

But I faced the same issue of foreign key which is highlighted in the question, because of the sequence issue for the backed up data tables so to ignore the foreign key issue for data restoration, I first executed above command where table creation queries got success but data restoration failed due to foreign key issue, then I ran below command which helped to ignore the foreign key check and restored the data:

pg_restore -d local_db_name mybackup.dump --disable-triggers --superuser=postgres --data-only

As can see I have passed some extra params as disable triggers and data only restoration (ignore the colour change of text from '--disable...'), also need to pass the superuser which may be mostly Postgres but checked it once on your target machine.

Restore PostgreSQL db from backup without foreign key constraint issue

7 Answers7

Linked