On Mon, 5 Oct 2015 06:20:28 -0700 (MST) jimbosworth <jimbos5000@xxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
