How to merge dump into database from PostgreSQL?

Question

I'm working on the same databse schema on different machines (PostgreSQL). I would like to know, how to merge data from one machine to another. Schema has many tables (around 10). What I want to achieve?

1. Dump data from machine A to file A.dmp
2. Restore data from file A.dmp to machine B
3. When a record already exists in machine B, I would like to don't insert it into machine B.

I was trying dump data from machine A to simple SQL inserts commands, but when I'm trying to restore it, I am getting duplicate key errors. What is more, I would like to restore data from command line (I have to import 250 MB data), because now I'm trying to do it manually with pgAdmin.

What is the best way to do it?

Christelchristen

Simplest would probably be to import it into a (temp) new schema, and merge from there. The merge will still need some work. (sequences, FK constraints ...) – Chancelor

So if I do that, how to merge 2 schemas? You mean something like insert into schema1.table1 select * from schema2.table1 ? – Christelchristen

Yes, but including not exists : insert into schema1.table1 dst select * from schema2.table1 WHERE NOT EXISTS (SELECT * FROM schema2.table1 nx WHERE nx.pk = dst.pk); PLUS: in the correct "topological" order to allow FKs to be populated before being referenced. Note: there might be smarter ways to do it, but for only ten tables it is not too much work IMO) – Chancelor

Answer 1

I finally did it this way:

1. Export to dump with:

   pg_dump -f dumpfile.sql --column-inserts -a -n <schema> -U <username> <dbname>
   

2. Set skip unique for all tables

   CREATE OR REPLACE RULE skip_unique AS ON INSERT TO <table>
       WHERE (EXISTS (SELECT 1 FROM <table> WHERE users.id = new.id)) 
       DO INSTEAD NOTHING
   

3. Import with psql

   \i <dumpfile.sql>
   

Christelchristen

Excellent hack. (BTW: take care of sequences!) Don't forget to drup the rules afterwards ... – Chancelor