对PostgreSQL数据库转储（pg_dump）进行排序

Question

我正在创建两个pg_dumps，DUMP1和DUMP2。

DUMP1和DUMP2完全相同，除了DUMP2是以与DUMP1相反的顺序转储的。

有没有办法对这两个DUMP进行排序，使得使用diff时这两个DUMP文件完全相同？

我正在使用PHP和Linux。我尝试在Linux中使用“sort”，但那行不通...

谢谢！

- littleK

Answer 1

根据您之前的问题, 我猜您想要做的是比较两个数据库是否相同，包括数据。

正如我们所看到的, pg_dump不会表现出确定性。其中一个文件与另一个文件相反的事实可能只是巧合。

下面是一种可以进行完整比较（包括模式和数据）的方法。

首先，使用此方法比较模式。

其次，通过将所有数据转储到文件中并按一致的顺序进行比较来比较数据。 通过首先按名称对表进行排序，然后在每个表内按主键列排序来保证顺序。

以下查询生成COPY语句。

select
    'copy (select * from '||r.relname||' order by '||
    array_to_string(array_agg(a.attname), ',')||
    ') to STDOUT;'
from
    pg_class r,
    pg_constraint c,
    pg_attribute a
where
    r.oid = c.conrelid
    and r.oid = a.attrelid
    and a.attnum = ANY(conkey)
    and contype = 'p'
    and relkind = 'r'
group by
    r.relname
order by
    r.relname

运行该查询将会给你一个语句列表，例如：copy (select * from test order by a,b) to STDOUT; 将它们全部放在一个文本文件中，并通过每个数据库的psql运行它们，然后比较输出文件。你可能需要调整输出设置为COPY。

- cope360

Answer 2

如果：

• 性能不如顺序重要
• 你只关心数据而不关心模式
• 你有能力重新创建两个转储文件（你不必使用现有的转储文件）

你可以按照确定的顺序以CSV格式转储数据，如下所示：

COPY (select * from your_table order by some_col) to stdout
      with csv header delimiter ',';

查看 COPY (v14)

- Risadinha

Answer 3

这是解决问题的另一个方案：https://github.com/tigra564/pgdump-sort

它可以对DDL和DML进行排序，包括将易变值（例如序列值）重置为一些规范值以最小化结果差异。

- Sergey Myasnikov

Answer 4

我的解决方案是编写一个程序来处理pg_dump的输出。请随意下载PgDumpSort，它可以按主键对转储进行排序。使用默认的512MB Java内存，每个表可以处理高达1000万条记录，因为记录信息（主键值、文件偏移量）保存在内存中。

您可以使用这个小的Java程序，例如：

java -cp ./pgdumpsort.jar PgDumpSort db.sql

您将获得一个名为“db-sorted.sql”的文件，或者可以指定输出文件名：

java -cp ./pgdumpsort.jar PgDumpSort db.sql db-$(date +%F).sql

已排序的数据在一个名为"db-2013-06-06.sql"的文件中。

现在，您可以使用差异（diff）创建补丁。

diff --speed-large-files -uN db-2013-06-05.sql db-2013-06-06.sql >db-0506.diff

这可以让您创建增量备份，通常要小得多。要恢复文件，您需要使用补丁将其应用于原始文件。

 patch -p1 < db-0506.diff

（源代码位于JAR文件内）

- bebbo

---

非常好，我只需要一种快速比较预先和后续南方脚本数据库转储以进行QA目的的方法。快速简便，感谢您。在两个转储上运行，查找孤立数据，即可立即保证我们不会级联丢失任何数据。 - Wayne Weeks

Answer 5

解析转储文件可能并不值得花费这样的精力。

将DUMP2恢复到临时数据库中，然后按正确顺序转储临时数据库会快得多。

- Mike

---

你能澄清一下你的意思吗？你是说要将DUMP 2恢复到一个临时数据库中，但是你能解释一下“以正确的顺序转储临时数据库”的含义吗？谢谢！ - littleK

哦，你的意思是将DUMP2恢复到一个临时数据库中，然后再转储该数据库... - littleK