Re: Move rows from one database to other

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(dot)klaver(at)aklaver(dot)com