How to compare data between two databases in PostgreSQL?

Question

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.

[dbForge Data Compare for PostgreSQL](https://www.devart.com/dbforge/postgresql/datacompare/) solves such issues. — Devart, Oct 24 '17 at 09:07
https://www.postgrescompare.com/downloads compares data now too — Neil Anderson, Jun 03 '20 at 14:50

score 100 · Accepted Answer · edited Jun 26 '20 at 16:26

100

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)

edited Jun 26 '20 at 16:26

Rob Bednark

25,981
23
80
125

answered Jan 26 '11 at 13:04

18

Only liquibase.org and Aqua Data Studio seems to compare data, other just compare schema. – Amir Ali Akbari Jan 06 '15 at 16:49
@AmirAliAkbari Liquibase *does* support schema diffs – Jan 06 '15 at 18:55
2

It seems that `apgdiff` does not support inherit tables well, and exception throws immediately when I trying to use it. `WbSchemaDiff` works very well, surprise! – vr3C Dec 30 '15 at 08:26
@semparatus the same for me. `apgdiff` doesn't seem to work any more. – Freddy Jan 22 '16 at 01:46
1

@AmirAliAkbari liquibase doesn't seem to compare data when the tables exist and have the same structure. – aditsu quit because SE is EVIL May 13 '16 at 13:56
Another option for schema diffs - pgcmp: https://github.com/cbbrowne/pgcmp – Dharmendar Kumar 'DK' Oct 02 '18 at 21:37

score 46 · Answer 2 · answered Jan 26 '11 at 12:56

46

Try using pg_dump on both databases and diffing the files.

answered Jan 26 '11 at 12:56

Julio Santos

3,837
2
26
47

23

+1 for simple and direct. But do we know for sure that pg_dump will dump data from identical databases in the same order if, say, the tables were built in different orders? (I'd hope the order is based on constraint dependencies, not at all caring about time of creation, but hope doesn't scale well.) – Mike Sherrill 'Cat Recall' Jan 31 '11 at 01:36
10

you can use -a -d and | sort . But this data may not be importable, however it would be ok for basic checking. – Cem Güler Feb 28 '11 at 10:41
This should be higher up in the results, to be honest. One shouldn't have to rely on a diff to save the day so these full blown, heavy, java based solutions seem like overkill. However, it does make sense to sanity check your migrations and `pg_dump` is fine for that. If you see significant diffs with `pg_dump` you're probably trying to compare things that are beyond being comparable. At least for comparing PG dbs. – sas Jan 16 '18 at 08:16
1

Sadly this only works on smaller databases, as diff can't handle some big dumps I have. Otherwise it's (still !) really the only workable solution I found. Though I'm using `psql -c '\x' -c 'SELECT... ORDER BY...'` instead of `pg_dump`. – nyov Sep 01 '19 at 10:48
pgadmin is much better because it find the diff and generate a script to equalize both dbs. And the installations is a breeze. – Josir May 21 '22 at 14:08

score 14 · Answer 3 · edited Sep 04 '19 at 12:25

14

Another free app (that can only compare structure, but not data):

DBeaver - you can select databases, tables, etc to compare with each other

edited Sep 04 '19 at 12:25

ax.

58,560
8
81
72

answered Sep 21 '16 at 12:57

iki

153
1
4

1

Could you please explain better how to compare data from 2 databases with DBeaver? – Erica Nov 16 '16 at 11:30
1

As far as I know DBeaver only allows metadata comparison, not data comparison. – Erica Dec 27 '16 at 14:16
Very nice tool. Its true that its not very intuitive how to do it at first. You must first select 2 or more object so you can see this option. – ihebiheb Mar 02 '18 at 09:46
@nicola The paid version can compare data – tstenner Jun 08 '22 at 10:40

Neil Anderson · Answer 4 · 2019-07-04T19:39:35.750

6

I'm working on a comprehensive comparison tool for Postgres. It will be free while in beta.

PostgresCompare

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.

edited Jul 04 '19 at 19:39

answered Apr 21 '17 at 17:06

Neil Anderson

1,265
12
19

1

Data is also very important. Schema alone isn't enough. – Houman May 17 '17 at 09:38
1

Hi @Houman. Sorry for the late reply. You are right, data will be the next step. The great thing about building the schema comparison tool first is that all the code for discovering tables etc. can be shared between them. – Neil Anderson Jun 02 '17 at 15:25
I came across this answer while building a simple schema comparision tool myself. I went through your website and the tool looks very promising. Can't wait to try out the beta version – Avantika Saini Aug 21 '18 at 12:46
The alpha is available right now @AvantikaSaini and if you give it a try please let me know how it goes so I can improve it for everybody. – Neil Anderson Aug 23 '18 at 00:58
You should make an educational version of license. The price is too high for educational purposes. – reinaldoluckman Jul 01 '19 at 03:01
@reinaldoluckman there's a fully functional free trial and I'm always open to extending that for those who need more time. – Neil Anderson Jul 04 '19 at 19:40

score 5 · Answer 5 · answered Sep 12 '19 at 07:56

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.

score 2 · Answer 6 · answered Sep 24 '17 at 16:39

2

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]

answered Sep 24 '17 at 16:39

Oleg Tsarev

146
7

This seems to be the only tool generating diff scripts comparing one database and one dump file. Usually other tools compare two databases. Thanks to this feature, developers can work work a local dev database, then commit and distribute their modifications by vcs without creating migration scripts, just executing dbtoyaml. Oher team developers can update their local databases with a single command (yamltodb). This workflow works a bit like visual studio database project. – andreav Apr 04 '18 at 05:53

dmarkey · Answer 7 · 2019-12-20T13:03:18.317

1

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.

https://github.com/dmarkey/pgdatadiff

edited Dec 20 '19 at 13:03

answered Dec 20 '19 at 12:54

dmarkey

193
2
8

score 1 · Answer 8 · answered May 10 '22 at 15:25

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

score 0 · Answer 9 · answered Dec 27 '16 at 14:08

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.

score 0 · Answer 10 · answered May 13 '20 at 12:27

0

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.

answered May 13 '20 at 12:27

Mohsen Zahedi

651
2
10
28

score 0 · Answer 11 · answered Sep 03 '21 at 11:32

0

Use https://github.com/covrom/goerd for simple databases (without triggers, stored procs and nesting tables).

answered Sep 03 '21 at 11:32

Roman Tsovanyan

11
2

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Noam Yizraeli Sep 07 '21 at 11:32

score 0 · Answer 12 · answered Sep 28 '22 at 16:10

0

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.

answered Sep 28 '22 at 16:10

Ben

355
2
11

Can you merge data changes from one table to another in DataGrip? – Andrew Jan 31 '23 at 11:58
Just checked - I can't see a way of doing this as of 2022.3.2. – Ben Feb 01 '23 at 14:03

sarh · Answer 13 · 2023-02-07T15:50:21.653

0

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.

edited Feb 07 '23 at 15:50

answered Nov 28 '22 at 20:37

sarh

6,371
4
25
29

How to compare data between two databases in PostgreSQL?

13 Answers13

There are several tools out there:

Linked