Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to compare data between two databases in PostgreSQL?

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.

like image 705
sennin Avatar asked Jan 26 '11 12:01

sennin


People also ask

Can I refer two objects from two databases in a query in PostgreSQL?

There is no way to query a database other than the current one.

How do I compare text in PostgreSQL?

We can compare the string using like clause in PostgreSQL, we can also compare the string using the =, != , <>, <, >, <= and >= character string operator. Basically character string operator in PostgreSQL is used to compare the string and return the result as we specified input within the query.


2 Answers

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)
like image 119
a_horse_with_no_name Avatar answered Sep 22 '22 19:09

a_horse_with_no_name


Try using pg_dump on both databases and diffing the files.

like image 36
Julio Santos Avatar answered Sep 23 '22 19:09

Julio Santos