Re: Move rows from one database to other

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Tue, Feb 21, 2017 at 9:27 AM, William Ivanski <william.ivanski@xxxxxxxxx> wrote:

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.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

William Ivanski - Microsoft MVP

Depending on how much data you want to move, and if the tables have the same structure, you might also want to consider using

pg_dump -a

OR

multiple instances of

on satellite
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

on central
COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

A BIG consideration is: 
Does the Central DB have the same table structures as all satellite DB's?
Does the Central DB already have records in the tables.
Do all Satellite tables have unique records for each other?

As Adrian stated, it would be very helpful if you provided us with all O/S and PostgreSQL vesions involved.

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.