14

I occasionally download publicly available data sets in the form of Postgres dBs. These datasets are updated/modified/expanded over time by the repository host.

Is there a Postgres command or tool (ideally FOSS) that can show the differences between an OLD and NEW Postgres database ? (A working assumption is that 95% of the entries are unchanged and that the tables and relationships also do not change).

  • 1
    What did you end up using? None of the answers really seem to have a solution. – nyov Sep 1 '19 at 10:28
10

Have a look at the Stack Overflow Q & A, How to check difference between two databases in PostgreSQL?; in particular Another PostgreSQL Diff Tool (apgdiff) (schema comparison only). Apgdiff is recommended by Alexander Kuznetsov - a published database book author and bit of a guru.

Maybe also look at Liquibase or Compare Database Table Data.

There are also commercial products - e.g. Altova Database Differencing with DiffDog

|improve this answer|||||
  • 1
    apgdiff doesn't compare table data, metaqa is last released 2008 and is broken. liquibase's database diff is not geared towards the OPs use-case: "Data differences (limited), not checked by default" – nyov Sep 1 '19 at 11:03
2

There is also:

|improve this answer|||||
  • 1
    pgdiff does not compare table data. – nyov Sep 1 '19 at 10:24
0

If you need something fairly quick and dirty to compare schemas, I wrote this gist to help compare a running schema to a "latest" schema -- something maybe hosted in SCM. It's working fairly well for a ~50 tables project.

|improve this answer|||||
  • Does not compare table data. – nyov Sep 1 '19 at 10:23
  • That was okay for my needs. I only needed to compare schemas - the data inside was not as important. I'm sure it's not far from being able to compare data if you can somehow control the order of the dump to be deterministic across versioins/installs/etc. – Buddy Sep 2 '19 at 13:22
  • It's okay. I was just frustrated that nobody had a good answer to the actual question sofar (Which specifically states "A working assumption is that 95% of the entries are unchanged and that the tables and relationships also do not change".) – nyov Sep 2 '19 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.