current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 1 down vote favorite
1

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?

share|improve this question
    
Actually i send the dump i got from phppgadmin as sql via phppgadmin interface itself..... – Black Rider Mar 19 '11 at 18:49
up vote 6 down vote accepted

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.

share|improve this answer
    
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 3:56
1  
@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 4:22
up vote 2 down vote

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

share|improve this answer
    
these are the sample lists!!! schemaspy.sourceforge.net/sample/insertionOrder.txt schemaspy.sourceforge.net/sample/deletionOrder.txt – jquinter Jul 13 '12 at 3:03
up vote 0 down vote

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.

share|improve this answer
    
i believed that PhpPgAdmin uses pgdump for making backups... am i wrong??? [link]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
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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