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?
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.