Re: Move rows from one database to other

On Tue, Feb 21, 2017 at 11:10 AM, Thomas Güttler <
guettliml(at)thomas-guettler(dot)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 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 [, ...] ) ]
COMMIT;

BEGIN;
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.