postgresql 如何比较PostgreSQL中两个数据库之间的数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4804779/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to compare data between two databases in PostgreSQL?
提问by sennin
Is it possible to compare two databases with identical structure? Let say that I have two databases DB1 and DB2 and I want to check if there is a difference in data between them.
是否可以比较具有相同结构的两个数据库？假设我有两个数据库 DB1 和 DB2，我想检查它们之间的数据是否存在差异。
采纳答案by a_horse_with_no_name
There are several tools out there:
有几种工具：
(note that most of these tools can only compare structure, but not data)
（注意这些工具大部分只能比较结构，不能比较数据）
Free Ones:
免费的：
- pgquarrel: http://eulerto.github.io/pgquarrel
- apgdiff: http://apgdiff.com/
- Liquibase (Cross DBMS): http://www.liquibase.org
- WbSchemaDiff (Cross DBMS): http://www.sql-workbench.net/manual/compare-commands.html
- Migra: https://migra.djrobstep.com/
- pgquarrel：http://eulerto.github.io/pgquarrel
- apgdiff：http://apgdiff.com/
- Liquibase（跨数据库管理系统）：http: //www.liquibase.org
- WbSchemaDiff（跨 DBMS）：http://www.sql-workbench.net/manual/compare-commands.html
- Migra：https://migra.djrobstep.com/
Commercial:
商业的：
- DB Comparer: http://www.sqlmanager.net/en/products/postgresql/dbcomparer
- Aqua Data Studio: http://docs.aquafold.com/docs-diff-schema.html
- DB Solo: http://www.dbsolo.com/index.html(30 day trial)
- PostgresCompare: https://www.postgrescompare.com/
- 数据库比较器：http://www.sqlmanager.net/en/products/postgresql/dbcomparer
- Aqua 数据工作室：http: //docs.aquafold.com/docs-diff-schema.html
- DB Solo：http: //www.dbsolo.com/index.html（30 天试用）
- PostgresCompare：https: //www.postgrescompare.com/
回答by Júlio Santos
Try using pg_dumpon both databases and diffing the files.
尝试pg_dump在两个数据库上使用并比较文件。
回答by iki
Another free app (that can only compare structure, but not data):
另一个免费应用程序（只能比较结构，不能比较数据）：
DBeaver- you can select databases, tables, etc to compare with each other
DBeaver- 您可以选择数据库、表等进行比较
回答by i.bondarenko
I have evaluated a lot of the tools and found following solution:
我评估了很多工具并找到了以下解决方案：
Schema comparison:
架构比较：
The most interesting were Liquibase, Persyas and PgCodeKeeper:
最有趣的是 Liquibase、Persyas 和 PgCodeKeeper：
(issue) Liquebase converts:
(问题) Liquebase 转换：
SET DEFAULT nextval('myschema.name_id_seq'::regclass)
into
进入
BIGSERIAL
So it was rejected to use
所以被拒绝使用
(issue) Persyas worked fine untill I added some additional schema and it starts throwing following:
(问题) Persyas 工作正常，直到我添加了一些额外的架构，它开始抛出以下内容：
pyrseas_1 | TypeError: 'NoneType' object is not iterable
So I have found PgCodeKeeperit works perfectly and it is alive (you can check releases). I use following command:
所以我发现PgCodeKeeper它工作得很好并且它是活的（你可以检查发布）。我使用以下命令：
./pgcodekeeper-cli.sh -E -I ./ignore.txt \
-s "jdbc:postgresql://localhost/postgres?user=postgres&password=123" \
-t "jdbc:postgresql://localhost/postgres?user=postgres&password=123" \
-o /result/schema-diff-$(date +'%m%d%y_%H%M%S').sql
Data comparison:I have tried to use Liquebase and it just does not work you can see the steps I tried in my unanswered question about data difference of two databases with Liquebase
数据比较：我尝试使用 Liquebase 但它不起作用你可以看到我在我的未回答问题中尝试的步骤关于两个数据库与 Liquebase 的数据差异
So I have found another project SQL Workbench/JIt works really nice and generate reall diff in sql. I use following command:
所以我找到了另一个项目SQL Workbench/J它工作得非常好，并在 sql 中生成真正的差异。我使用以下命令：
java -jar /sqlworkbench.jar -command="'WbDataDiff -excludeTables=$EXCLUDE_TABLES \
-referenceConnection=$REFERENCE_CONNECTION \
-targetConnection=$TARGET_CONNECTION -referenceSchema= \
-targetSchema= -file=$DIFF_RESULT_FILE -singleFile=true \
-includeDelete=true -ignoreMissingTarget=true ;'"
Both tools support objects filtration. It is really convenient.
这两种工具都支持对象过滤。这真的很方便。
Migrations
迁移
And finally I use Liquebase just for migration/version tracking.
最后，我仅使用 Liquebase 进行迁移/版本跟踪。
回答by Neil Anderson
I'm working on a comprehensive comparison tool for Postgres. It will be free while in beta.
我正在为 Postgres 开发一个全面的比较工具。在测试版期间它将是免费的。
Initially this is just schema (DDL) comparison but we will probably expand to data also. I believe this is a tool that a lot of shops require in order to move from their current RDBMS without having to also change how their development environments, operations etc work too.
最初这只是模式 (DDL) 比较，但我们也可能会扩展到数据。我相信这是许多商店需要的工具，以便从他们当前的 RDBMS 迁移，而不必也改变他们的开发环境、操作等的工作方式。
回答by Oleg Tsarev
The best tool which I ever seen https://pythonhosted.org/Pyrseas/
我见过的最好的工具 https://pythonhosted.org/Pyrseas/
Get dump from database A dbtoyaml ...
Generate migration from A => B yamltodb ... [file generated in step 1]
从数据库中获取转储 A dbtoyaml ...
从 A => B yamltodb 生成迁移 ... [步骤 1 中生成的文件]
回答by dmarkey
I created a tool to compare 2 live PostgreSQL databases(not dumps), table data and sequences. Pretty early days but achieved what I wanted it to do, maybe it can help you too.
我创建了一个工具来比较 2 个实时 PostgreSQL 数据库（不是转储）、表数据和序列。很早的时候但实现了我想要它做的事情，也许它也可以帮助你。
回答by Mohsen Zahedi
回答by nicola
I'm also looking for a tool to compare data in databases (in particular I was interested in comparing Redshift DB). So far the best I found is https://www.dbbest.com/products/database-compare-suite/#close. Unfortunately the free trial expires after one day.
我也在寻找一种工具来比较数据库中的数据（特别是我对比较 Redshift DB 感兴趣）。到目前为止，我发现的最好的是https://www.dbbest.com/products/database-compare-suite/#close。不幸的是，免费试用在一天后到期。
