mysql备份外键丢失,从备份还原PostgreSQL数据库，没有外键约束问题

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?

解决方案

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.