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.
How to compare data between two databases in PostgreSQL?
124.9k Views Asked by sennin At
13
There are 13 best solutions below
0
On
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.
0
On
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
0
On
There is another GUI diff tool, currently it is for Windows only: KS DB Merge Tools for PostgreSQL. Not an open source, but pretty functional free version available - allows to compare schema, diff and merge table data and some programming objects:
I am the author of that tool.
0
On
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.
1
On
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]
1
On
Use https://github.com/covrom/goerd for simple databases (without triggers, stored procs and nesting tables).
0
On
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.
0
On
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.
2
On
If you already own a license for JetBrains DataGrip, it is worth checking out the Compare feature that is built into that tool - see vendor documentation on the feature here.
One nice feature is you can select arbitrary rows and columns from the two tables you wish to compare, and then compare the results in the output pane. This gives you another way of ignoring things like identity columns which may have valid reasons to be different in each database.
6
On
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.
4
On
Another free app (that can only compare structure, but not data):
DBeaver - you can select databases, tables, etc to compare with each other
There are several tools out there:
(note that most of these tools can only compare structure, but not data)
Free Ones:
Commercial: