Move rows from one database to other

I want to move table rows from one database to an central database.

Both run PostgreSQL.

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

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

I don't want to code this myself, since there a tons of possible race conditions:

- inserts can happen during syncing.
- 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

--
Thomas Guettler http://www.thomas-guettler.de/