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