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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general