Re: [GENERAL] Move rows from one database to other - Mailing list pgsql-general

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/