On Tue, Feb 21, 2017 at 11:10 AM, Thomas Güttler <guettliml@xxxxxxxxxxxxxxxxxx> 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 schema
Does 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.
