Re: Move rows from one database to other

You can try OmniDB: http://www.omnidb.com.br/en_index.aspx

OmniDB has a Convert feature, where you can set a data transfer, even if
the target table exists.

Em ter, 21 de fev de 2017 às 11:18, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
escreveu:

> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--

William Ivanski - Microsoft MVP