How to compare data between two databases in PostgreSQL?

Asked 26/1, 2011 at 12:54 Answered 28/11, 2022 at 20:37

119

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.

Demonology answered 26/1, 2011 at 12:54 Comment(4)

See also https://mcmap.net/q/188861/-postgres-pg_dump-dumps-database-in-a-different-order-every-time/453605 – Willis 19/11, 2015 at 16:42

dbForge Data Compare for PostgreSQL solves such issues. – Dina 24/10, 2017 at 9:7

postgrescompare.com/downloads compares data now too – Lilialiliaceous 3/6, 2020 at 14:50

github.com/akaihola/pgtricks#pg_dump_splitsort – Cloakroom 10/5, 2022 at 15:19

103

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)

Hagioscope answered 26/1, 2011 at 13:4 Comment(6)

Only liquibase.org and Aqua Data Studio seems to compare data, other just compare schema. – Simard 6/1, 2015 at 16:49

@AmirAliAkbari Liquibase does support schema diffs – Hagioscope 6/1, 2015 at 18:55

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! – Rascally 30/12, 2015 at 8:26

@semparatus the same for me. apgdiff doesn't seem to work any more. – Hodgepodge 22/1, 2016 at 1:46

@AmirAliAkbari liquibase doesn't seem to compare data when the tables exist and have the same structure. – Delladelle 13/5, 2016 at 13:56

Another option for schema diffs - pgcmp: github.com/cbbrowne/pgcmp – Nerissa 2/10, 2018 at 21:37

Try using pg_dump on both databases and diffing the files.

Grievance answered 26/1, 2011 at 12:56 Comment(5)

+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.) – Generalization 31/1, 2011 at 1:36

you can use -a -d and | sort . But this data may not be importable, however it would be ok for basic checking. – Runty 28/2, 2011 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. – Slipperwort 16/1, 2018 at 8:16

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. – Volvulus 1/9, 2019 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. – Replenish 21/5, 2022 at 14:8

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

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

Flem answered 21/9, 2016 at 12:57 Comment(4)

Could you please explain better how to compare data from 2 databases with DBeaver? – Schnook 16/11, 2016 at 11:30

As far as I know DBeaver only allows metadata comparison, not data comparison. – Schnook 27/12, 2016 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. – Warfore 2/3, 2018 at 9:46

@nicola The paid version can compare data – Flagrant 8/6, 2022 at 10:40

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.

Lilialiliaceous answered 21/4, 2017 at 17:6 Comment(6)

Data is also very important. Schema alone isn't enough. – Weinrich 17/5, 2017 at 9:38

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. – Lilialiliaceous 2/6, 2017 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 – Grave 21/8, 2018 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. – Lilialiliaceous 23/8, 2018 at 0:58

You should make an educational version of license. The price is too high for educational purposes. – Zetes 1/7, 2019 at 3:1

@Zetes there's a fully functional free trial and I'm always open to extending that for those who need more time. – Lilialiliaceous 4/7, 2019 at 19:40

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.

Nonjoinder answered 12/9, 2019 at 7:56 Comment(0)

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]

Yvor answered 24/9, 2017 at 16:39 Comment(1)

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. – Devaluation 4/4, 2018 at 5:53

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

Cloakroom answered 10/5, 2022 at 15:25 Comment(0)

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

Wrath answered 20/12, 2019 at 12:54 Comment(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.

Misinterpret answered 28/11, 2022 at 20:37 Comment(0)

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.

Schnook answered 27/12, 2016 at 14:8 Comment(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.

Westleigh answered 13/5, 2020 at 12:27 Comment(0)

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

Milkmaid answered 3/9, 2021 at 11:32 Comment(1)

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. – Peggie 7/9, 2021 at 11:32

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.

Fenestra answered 28/9, 2022 at 16:10 Comment(2)

Can you merge data changes from one table to another in DataGrip? – Autoroute 31/1, 2023 at 11:58

Just checked - I can't see a way of doing this as of 2022.3.2. – Fenestra 1/2, 2023 at 14:3

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

There are several tools out there:

Recommended topics

Hot tags