I have a big database with a lot of data ~50GB that spans around 40 tables - running PostgreSQL 9.6. This database is copied and sits in number of client sites without an Internet connection. Changes that are made to this database should be delivered to the client sites.

I'm looking for a way to generate a delta script between the current version of the database to the previous and execute this script in each client's site (open source/ freeware solution - RedGate is not an option).

The main assumption is that the schema is the same - so the delta is only in data (insert / update / remove rows)

I have checked a number of proposed tools (here for example) and the one that worked nice was SQLWorkbench-J using its WbDataDiff command. But its very slow - even if the databases resides on the same machine. I assume that its because the delta is calculated externally to support different databases sources.

Does anyone have a suggested way to do that? Is there a way to make it run internally on the PostgreSQL instance to increase the performance (I understand that joins cannot be calculated using different databases according to here - is using the "foreign data wrapper" may help in this situation)?

  • do all tables have a surrogate key? – Evan Carroll Feb 18 '19 at 16:05
  • 1
    does the client write to the database? – Evan Carroll Feb 18 '19 at 16:10
  • The question that @EvanCarroll asked is important. Are the databases in the client sites modified by the clients or are they read only? In the second case, you could setup the Postgres instances at the client sites as readonly replicas (and do log shipping). – ypercubeᵀᴹ Feb 18 '19 at 16:20
  • All the tables have primary key and the tables are just read only. What do you mean by log shipping? You mean WAL files? I prefer something "stateless" – sborpo Feb 18 '19 at 22:28
  • @sborpo it's not any more or less stateless to apply a diff you create, than one postgresql creates. It's just more error prone because yours isnt' versioned. The wal log is postgresql's log. – Evan Carroll Feb 19 '19 at 0:11

The traditional mechanism if you can

  • Dump the tables at point-in-time into a CSV (\COPY with ORDER BY)
  • Modify the tables, or wait for modiications
  • Dump the tables again at a point-in-time (\COPY with ORDER BY)
  • Diff the CSVs
  • Thennn
    • If the rows are not present in the second versions, write a DELETE (or batch for a larger delete)
    • Otherwise write a INSERT ON CONFLICT .. DO UPDATE (or batch for a larger INSERT)

This handles INSERTS, UPDATES and DELETES and only requires you to check for the presence of the surrogate key. If you don't have a surrogate key, things obviously get a lot more complex. And, even more complex if you have no UNIQUE keys at all.

Alternatively, dump the table as a 50gB CSV as an .xz (or other compressed format) and just be done with it. Probably compresses to a few GB, in a few hours.

| improve this answer | |

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.