Search Postgresql Archives

Re: Move rows from one database to other

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux