[GENERAL] 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/