如何验证pg_dump归档文件生成的归档文件是正确有效的？ [英] How to verify that an archive produced by pg_dump archive is OK and valid?

问题描述

我正在使用我的 pg_dump.exe 和 pg_restore.exe 从postgresql备份/还原数据。 。

I am working on backup/restore data from postgresql for what I use pg_dump.exe and pg_restore.exe.

为了恢复备份文件，我必须删除数据库中的实际表，如果转储文件不正常，这可能是自杀的工作。

In order to restore backup file I have to delete actual tables in database what can be "suicidal job" if something with dumped file isn't OK.

我可以使用 7z t mydump.gz来检查转储文件的完整性，就像第一次测试一样。

I can check dumped file for integrity with (say) '7z t mydump.gz' what I do like first test.

但是我想知道，因为这个档案是原始的PG档案，PostgreSQL是否包含任何技术来检查这个'.gz'档案，所以我可以在删除实际表之前采取这个技巧？

But I would like to know, because this archive is original PG archive did PostgreSQL contain any technique to check this '.gz' archive which I can take before deleting actual tables?

如果这样做，如何进行正确的检查？

And if do, how to do a proper check?

编辑：这是我实际的转储命令：

This is my actual command for dumping:

C：\Program Files（x86）\PostgreSQL\9.1\bin\pg_dump.exe-主机localhost --port 5432 --username postgres --no-password --verbose -F t --file C：\Users\User 1\Desktop\mydatabase.gz mydatabase

"C:\Program Files (x86)\PostgreSQL\9.1\bin\pg_dump.exe" --host localhost --port 5432 --username "postgres" --no-password --verbose -F t --file "C:\Users\User 1\Desktop\mydatabase.gz" "mydatabase"

推荐答案

您似乎正在尝试验证刚刚发布的PostgreSQL转储的有效性和正确性

You appear to be trying to verify the validity and correctness of a PostgreSQL dump you've just made.

您的主要误解是，您不必将转储还原到创建该转储的数据库中。。您可以还原到同一群集上的另一个数据库，也可以还原到另一个群集（服务器）上的数据库以免产生额外的疑虑。验证转储是否已恢复并且没有错误，并且数据是否与您期望的一样。

Your key misunderstanding is that you do not have to restore the dump into the same database you created it from. You can restore to another database on the same cluster, or for extra paranoia a database on another cluster (server). Verify that the dump restored without error and that the data is as you expect it to be.

要避免出现更多的疑惑，请停止PostgreSQL服务器并复制数据目录中的文件。 。这样，您也可以进行文件级备份。请注意，PostgreSQL数据目录的文件级副本只能由在同一平台上使用相同选项构建的PostgreSQL的同一主要版本（8.1 / 8.2 / ...）读取，仅 -因此，如果datadir来自Windows x64上的9.2.x，只能由安装了9.2.x的另一台Windows x64主机读取。

For extra paranoia, stop the PostgreSQL server and copy the files in the data directory. That way you have a file-level backup too. Note that file-level copies of PostgreSQL data directories can only be read by the same major (8.1/8.2/...) version of PostgreSQL built with the same option on the same platform - so, if the datadir is from 9.2.x on Windows x64, it can only be read by another Windows x64 host with 9.2.x installed.

如果您担心原始的数据库，那么您可能没有备份。这是一个关键问题。您需要立即阅读有关备份和还原的文档章节，然后获得适当的自动化备份方案。看看酒保。

If you're worried about your original database then you probably don't have backups. This is a critical problem. You need to go and urgently read the documentation chapter on backup and restore and get a good automated backup scheme in place. Have a look at barman.

问题编辑后更新：

-F t 是一个奇怪的选择；普通的SQL转储或 -F c 通常更有意义。

-F t is an odd choice; plain SQL dumps or -F c usually makes more sense.

您生成的文件不是 .gz （gzip压缩）文件，无论如何，它是 .tar 存档，未压缩。可以将其提取到包含SQL文件的目录中。

The file you've produced is not a .gz (gzip compressed) file, anyway, it's a .tar archive, not compressed. It can be extracted into a directory full of SQL files.

要对其进行测试，请使用 pg_restore 将其还原到使用 createdb 或 CREATE DATABASE 命令创建的新空数据库。

To test it, use pg_restore to restore it to a new empty database created with createdb or the CREATE DATABASE command.

这篇关于如何验证pg_dump归档文件生成的归档文件是正确有效的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！