0

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)?

Improve this question
5
  • do all tables have a surrogate key? Commented Feb 18, 2019 at 16:05
  • 1
    does the client write to the database? Commented Feb 18, 2019 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). Commented Feb 18, 2019 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" Commented Feb 18, 2019 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. Commented Feb 19, 2019 at 0:11

1 Answer 1

Reset to default
1

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.