On 02/21/2017 12:53 AM, Thomas Güttler wrote:
I want to move table rows from one database to an central database.
You actually talking about moving from ~100 databases to the central database, correct?
Both run PostgreSQL.
Are all the Postgres instances the same version and what is the version or versions?
My use case looks like this: There are N satellite databases in different data centers. N is about 100 at the moment. There is one central database. I need a way to reliably move rows from the satellite databases to the central one
Two ways I can think of: https://www.postgresql.org/docs/9.6/static/dblink.html https://www.postgresql.org/docs/9.6/static/postgres-fdw.html
Example The rows of host1 look like this: host1, 2017-02-21, abc host1, 2017-02-20, def host1, 2017-02-19, ghi The rows of host2 look like this: host2, 2017-02-21, foo host2, 2017-02-20, bar host2, 2017-02-19, blu After syncing, all lines which were transferred should be deleted on the satellite databases. The central table should look like this (it has the same schema) host1, 2017-02-21, abc host1, 2017-02-20, def host1, 2017-02-19, ghi host2, 2017-02-21, foo host2, 2017-02-20, bar host2, 2017-02-19, blu
Is there a Primary Key on the satellite tables or some way of determining unique rows?
Is there any existing overlap between the data in the central database and the satellite databases?
I don't want to code this myself, since there a tons of possible race conditions:
How much data are you talking about moving from each database? How active are the satellite databases?
- inserts can happen during syncing.
Can UPDATEs happen?
- Network can break during syncing. - inserts into the central table can break (e.g. disk full): No loss at the satellite database must happen. - ... How to solve this with PostgreSQL? Regards, Thomas Güttler
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
