Thread: Best way to sync table DML between databases
Hi All, I have two servers each running pg9.4.4 database instances. I need to determine the best way to keep a large 20gb table on server A synchronised onto server B... At the moment, I use pg_dump to periodically dump the table on server A, then psql to reload into server B. This is fine, but means I have to pull 100% of the table each time rather than just the changes. This option does not offer real time accuracy on server B. I have considered using a table trigger on row (update, insert or delete) and then using db_link or postgres_fdw to sync the changes, but am concerned that a table trigger is synchronous... so a db_link or fdw could incur a lengthy delay. I have also considered using table OIDs to track changes, then just periodically sync the difference. I have considered using postgre_fdw and then 'refresh concurrently materialized view' on server B. I have considered using logical decoding to read the wal files, then extract the changes. Can anyone explain the best way to synchronise JUST the changes on a table between servers please? thanks Jim -- View this message in context: http://postgresql.nabble.com/Best-way-to-sync-table-DML-between-databases-tp5868715.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Mon, 5 Oct 2015 06:20:28 -0700 (MST) jimbosworth <jimbos5000@mail.com> wrote: > Hi All, > > I have two servers each running pg9.4.4 database instances. > I need to determine the best way to keep a large 20gb table on server A > synchronised onto server B... > > At the moment, I use pg_dump to periodically dump the table on server A, > then psql to reload into server B. This is fine, but means I have to pull > 100% of the table each time rather than just the changes. This option does > not offer real time accuracy on server B. > > I have considered using a table trigger on row (update, insert or delete) > and then using db_link or postgres_fdw to sync the changes, but am concerned > that a table trigger is synchronous... so a db_link or fdw could incur a > lengthy delay. > > I have also considered using table OIDs to track changes, then just > periodically sync the difference. > > I have considered using postgre_fdw and then 'refresh concurrently > materialized view' on server B. > > I have considered using logical decoding to read the wal files, then extract > the changes. > > Can anyone explain the best way to synchronise JUST the changes on a table > between servers please? Sounds like a problem custom-made to be solved by Slony: http://slony.info/ -- Bill Moran
Thanks Bill, but is there no way to just track row changes on a postgres table without using 3rd party replication solutions or a patched version of postgres? Im not in a position to change the database setup on server A. Server B is mine to maintain, hence why postgres_fdw and some means of tracking just the DML would work ok. -- View this message in context: http://postgresql.nabble.com/Best-way-to-sync-table-DML-between-databases-tp5868715p5868720.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 10/05/2015 09:46 AM, jimbosworth wrote: > Im not in a position to change the database setup on server A. Can you have the owners/maintainers do the needed changes to setup replication? Or that is 100% out of the question?