Thread: [GENERAL] Move rows from one database to other
On Tue, Feb 21, 2017 at 9:27 AM, William Ivanski <william.ivanski@gmail.com> wrote:
Depending on how much data you want to move, and if the tables have the same structure, you might also want to consider usingYou 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@aklaver.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.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general --William Ivanski - Microsoft MVP
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Il 21/02/2017 15:38, Melvin Davidson ha scritto:
Depending on how much data you want to move, and if the tables have the same structure, you might also want to consider usingpg_dump -aORmultiple instances ofon 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.
AFAIK pg_dump is version-independent (I use it to upgrade some small clusters), given that you are using the one from the newest version and you are dumping from old and restoring from new.
Another thing that I saw is that pg_dump is less I/O stressful (I can almost safely dump databases from my production server in traffic hours, even if I never tried doing it in rush hours :-) ) and therefore slower than COPY, but on the other side, using COPY, the risk is to have all I/O bandwidth eaten by data transfer, especially if the amount of data to be transferred is considerable. I tried some times to COPY across different clusters with different versions, but I always had issues.
My 2 cents.
Cheers
Moreno.
--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Tue, Feb 21, 2017 at 11:10 AM, Thomas Güttler <guettliml@thomas-guettler.de> wrote:
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?
yes, same schemaDoes the Central DB already have records in the tables.
yes, movement of rows should happen every ten minutes.Do all Satellite tables have unique records for each other?
Yes, UUID primary key.As Adrian stated, it would be very helpful if you provided us with all O/S and PostgreSQL vesions involved.
Versions are 9.5 and 9.6
I have other concerns: atomar transaction. Movement should happen completely or not all.
I don't think you can do this reliable (atomic transaction) with "copy table_name".
Regards,
Thomas
--
Thomas Guettler http://www.thomas-guettler.de/
>I have other concerns: atomar transaction. Movement should happen completely or not all.
>I don't think you can do this reliable (atomic transaction) with "copy table_name".
You can if you wrap it in a transaction:
EG:
BEGIN;
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
COMMIT;
BEGIN;
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
COMMIT;
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.