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@aklaver.com
