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
> > 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 > > > >