从没有外键约束问题的备份中恢复 PostgreSQL 数据库

Restore PostgreSQL db from backup without foreign key constraint issue

提问by Black Rider

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:

我有一个包含大约 85 个以上表的 postgresql 数据库。我pg_dump在复制模式下定期使用（通过 php-pgadmin）进行备份，备份文件的大小几乎是 10-12 MB。现在我面临的问题是，每当我尝试恢复数据库时，都会出现外键约束问题。场景如下：

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

有两个表： 1)users和 2) zones。我已将区域的 id 存储在users表中以标识用户的区域并将其设置为外键。

When I take the db dump, the entries for table zonescome only after that of table users. I think it's due to the first letter of table name: ucomes 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 zonesdoes not exist in the database since the structure of zonescomes after that of usersin the dump file.

当我进行 db dump 时， table 的条目zones只出现在table 的条目之后users。我认为这是由于表名的第一个字母：u出现在 之前z，因此当我恢复数据库时，出现外键约束问题并且执行停止。当我尝试恢复 db 结构时出现同样的问题，它说zones数据库中不存在该表，因为 的结构zones出现在users转储文件中的结构之后。

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

有什么解决办法吗？有没有其他可行的备份方法？

采纳答案by mu is too short

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_dumpwould serve you better, it looks like you need a bit of extra configuration to tell PhpPgAdmin where pg_dumpis. Then you'd feed that binary dump into pg_restoreand pg_restorewould rebuild everything in the proper order to avoid referential integrity issues (or, more accurately, pg_restorewould restore all the data then add the constraints).

听起来您正在从pg_dump. 这会给你一大堆带有模式（包括 FK）的 SQL，然后是一堆 INSERT 来重新加载数据。来自的二进制转储pg_dump会更好地为您服务，看起来您需要一些额外的配置来告诉 PhpPgAdmin 在哪里pg_dump。然后您将该二进制转储输入pg_restore并pg_restore以正确的顺序重建所有内容以避免参照完整性问题（或者，更准确地说，pg_restore将恢复所有数据然后添加约束）。

PhpPgAdmin seems to want to work with plain SQL dumpsrather 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.

PhpPgAdmin 似乎想使用普通的 SQL 转储而不是pg_restore. 我觉得这很难相信，但我在文档中找不到任何关于调用pg_restore. 如果这是真的，那么您可能必须手动编辑 SQL 转储并将所有 FK 移到最后。

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.

您还可以尝试SET CONSTRAINTS ALL DEFERRED;在 SQL 转储的顶部添加，这应该将约束检查延迟到事务结束，您还需要确保整个 INSERT 块都包含在事务中。

If PhpPgAdmin really can't invoke pg_restorethen you're better off using using pg_dumpand pg_restoreby 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_restorewith PhpPgAdmin.

如果 PhpPgAdmin 真的无法调用，pg_restore那么最好使用 usingpg_dump和pg_restore手动，以便对备份过程进行必要的控制。抱歉，任何无法处理使用 FK 备份数据库的数据库管理工具都比无用更糟糕。希望知道如何使用 PhpPgAdmin 的人会出现并告诉我们如何使用pg_restorePhpPgAdmin。

回答by jquinter

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

如果它对任何人有帮助：之前建议的解决方案都不适用于我（有一些 INSERT 引用了稍后转储的数据，独立于二进制格式或纯 SQL 查询）。

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

我做了什么：我使用了schemaspy，一个脚本 - 除了其他功能，例如底层 ER 模型的一个非常有用的 html 图 - 它生成两个非常有用的列表：“插入顺序”（其中所有表都列为执行插入的最佳顺序，考虑到现有的限制和依赖性）和“删除顺序”（对于删除表非常有用）。

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

如果你想要一个样本，请检查这个http://schemaspy.sourceforge.net/sample/。特别是，我在上面提到了两个示例列表（尝试发布直接链接，但垃圾邮件预防机制允许我只发布 2 个链接）。

回答by venimus

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

我发现你可以在sql的开头添加（这将停止外键检查）：

SET session_replication_role = replica;

SET session_replication_role = replica;

and at the end (to restore the checks):

最后（恢复支票）：

SET session_replication_role = origin;

SET session_replication_role = origin;

回答by Gabrielkdc

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

从 pg Admin 恢复数据库

ALTER TABLE table_name ENABLE TRIGGER ALL;

回答by Frank Heikens

using pgdump(via php-pgadmin)

使用 pgdump（通过 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 正在使用 pg_dump 创建备份吗？我从未见过任何由 pg_dump 进行的转储，在恢复转储时遇到外键问题。

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

PhpPgAdmin 只是一个 PHP 脚本，在大多数情况下它没有权限启动像 pg_dump 这样的程序。

回答by Kuberchaun

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

我会删除前面的 fk 创建并将其添加到脚本的末尾。