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/
