Re: [External] Merging two database dumps

[Vijaykumar Jain <vjain@xxxxxxxxxxxxx>]

You can try one option, although just a thought in the air 😊

Use postgres FDW  ex. https://robots.thoughtbot.com/postgres-foreign-data-wrapper

 

Create foreign tables in the relevant server schema

 

And then union/union all  😊 or your custom constraint on the destination table where you dump the rows.

 

For ex.

You have server1, server2, server3

 

And you have server4 as your new single server.

 

You create FDW of server1, server2, server3 on server 4 and then import table into respective server schema.

 

server1.table1, server2.table1, server3.table1

 

and then

insert into server4.table1  select * from( select * from server1.table1 union select * from server2.table1 union select * from server3.table1) a;

 

something 😊

 

 

Thanks,

Vijay

 

From: Alex O'Ree <spyhunter99@xxxxxxxxx>
Date: Wednesday, June 13, 2018 at 4:47 PM
To: "pgsql-general@xxxxxxxxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Subject: [External] Merging two database dumps

 

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

 

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.

 

Question is what are my other options to skip over the conflicting record when merging? 

 

From the docs, it appears that making dumps with the --insert option may be the only way to go however performance is an issue. In this case would dropping all indexes help?