Postgresql – Generate data delta between two databases PostgreSQL

postgresqlpostgresql-9.6

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

Best Answer

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.

Related Solutions

Postgresql – Multiple databases, in multiple geographic locations while preserving PostgreSQL triggers and data consistency

Streaming replication fires the triggers on the master only. The slaves never see the actual table writes, but rather re-run the recovery logs from the master. Because this is essentially a recovery situation, it means that the binary tables are guaranteed to be brought up to a consistent level. No triggers are necessary. Triggers may be possible with Slony or Bucardo but I am not 100% sure. My initial thought suggests these should allow triggers in downstream databases but you don;t want to run the same triggers on both databases!

In current releases you can't cascading replication outside of Slony. In 9.2 streaming replication will allow for cascading.

Hope this helps.

Postgresql – Correct pg_hba.conf to allow script to run locally with a specific db-user

Since you want the backup user to connect locally from the database server. I assume the backup is running on a dedicated backup account on the OS.

Using pg_hba.conf you can specify like:

# TYPE  DATABASE USER   ADDRESS METHOD
local   all      backup_user    peer map=local_peers_backup_user

(method peer (with local/peer an address is not needed))

combine this with a pg_ident.conf like:

# MAPNAME                SYSTEM-USERNAME   PG-USERNAME
local_peers_backup_user  postgres          backup_user
local_peers_backup_user  OSBKPUSER         backup_user

This allows the os user postgres and OSBKUSER to connect to all databases in the cluster as the postgres database user named backup_user, using peer authentication. This works using sockets, on linux.

connected as your OSBKPUSER running psql suffices to get connected as backup_user, assuming that you already prepared the environment variables for the OSBKPUSER and OSBKPUSER has only 1 map. Otherwise run psql -U backup_user.

This connect method is secure, if you trust that the access to your dbserver is secured.

Best Answer

Related Solutions

Postgresql – Multiple databases, in multiple geographic locations while preserving PostgreSQL triggers and data consistency

Postgresql – Correct pg_hba.conf to allow script to run locally with a specific db-user

Related Question