How to compare data between two databases in PostgreSQL?
PostgresqlSchema ComparePostgresql Problem Overview
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.
Postgresql Solutions
Solution 1 - Postgresql
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 (schema diff)
- apgdiff http://apgdiff.com/ (schema diff)
- Liquibase (Cross DBMS): http://www.liquibase.org (schema diff)
- pgAdmin https://www.pgadmin.org (schema diff in pgAdmin4)
- WbDataDiff (Cross DBMS): http://www.sql-workbench.net/manual/compare-commands.html#command-data-diff (data diff)
- WbSchemaDiff (Cross DBMS): http://www.sql-workbench.net/manual/compare-commands.html
- Migra https://migra.djrobstep.com/ (schema diff)
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/ (14 day trial, compares schema and data)
Solution 2 - Postgresql
Try using pg_dump
on both databases and diffing the files.
Solution 3 - Postgresql
Another free app (that can only compare structure, but not data):
DBeaver - you can select databases, tables, etc to compare with each other
Solution 4 - Postgresql
I'm working on a comprehensive comparison tool for Postgres. It will be free while in beta.
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.
Solution 5 - Postgresql
I have evaluated a lot of the tools and found following solution:
Schema comparison:
The most interesting were Liquibase, Persyas and PgCodeKeeper:
(issue) Liquebase converts:
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:
pyrseas_1 | TypeError: 'NoneType' object is not iterable
So I have found PgCodeKeeper it works perfectly and it is alive (you can check releases). I use following command:
./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
So I have found another project SQL Workbench/J It works really nice and generate reall diff in sql. I use following command:
java -jar /sqlworkbench.jar -command="'WbDataDiff -excludeTables=$EXCLUDE_TABLES \
-referenceConnection=$REFERENCE_CONNECTION \
-targetConnection=$TARGET_CONNECTION -referenceSchema=$1 \
-targetSchema=$1 -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.
Solution 6 - Postgresql
The best tool which I ever seen https://pythonhosted.org/Pyrseas/
-
Get dump from database A dbtoyaml ...
-
Generate migration from A => B yamltodb ... [file generated in step 1]
Solution 7 - Postgresql
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.
Solution 8 - Postgresql
The tool pgtricks has a command called pg_dump_splitsort
You can execute it on a dump.
Example:
pg_dump > pre-changes.sql
mkdir pre-changes
cd pre-changes
pg_dump_splitsort ../pre-changes.sql
now do some changes to your DB
pg_dump > post-changes.sql
mkdir post-changes
cd post-changes
pg_dump_splitsort ../post-changes.sql
cd ..
meld pre-changes post-changes
Solution 9 - Postgresql
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.
Solution 10 - Postgresql
In my opinion Dbforge is the most powerful tool for comapring data in postgresql .It's a product of Devart company.you can download here.
Solution 11 - Postgresql
Use https://github.com/covrom/goerd for simple databases (without triggers, stored procs and nesting tables).