Re: Merging two database dumps

Am 13.06.2018 um 13:17 schrieb Alex O'Ree:
> I have a situation with multiple postgres servers running all with the 
> same databases and table structure. I need to periodically export the 
> data from each of there then merge them all into a single server. On  
> occasion, it's feasible for the same record (primary key) to be stored 
> in two or more servers

what should happen in this case?

>
> I was using pgdump without the --insert option however I just noticed 
> that pgrestore will stop inserting into a table when the conflict 
> occurs, leaving me with an incomplete set.
>

Other solution:

* create the tables on the destination server without the PK or with an 
other, new PK (maybe SERIAL)
* use logical replication to replicate the table from all your 
source-db's to the destination table, see more here: 
https://www.2ndquadrant.com/en/resources/pglogical/

your problem seems as a typical task for logical replication to me. You 
needs 9.4 at least.


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com