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?

Dump data from machine A to file A.dmp
Restore data from file A.dmp to machine B
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?

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 ...) — joop, Nov 25 '13 at 12:32
So if I do that, how to merge 2 schemas? You mean something like `insert into schema1.table1 select * from schema2.table1` ? — Dariusz Mydlarz, Nov 25 '13 at 15:48
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) — joop, Nov 25 '13 at 17:12

score 12 · Accepted Answer · answered Nov 25 '13 at 18:38

12

I finally did it this way:

Export to dump with:

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

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

Import with psql
```
\i <dumpfile.sql>
```

answered Nov 25 '13 at 18:38

Dariusz Mydlarz

2,940
6
31
59

2

Excellent hack. (BTW: take care of sequences!) Don't forget to drup the rules afterwards ... – joop Nov 26 '13 at 08:57

How to merge dump into database from PostgreSQL?

1 Answers1

Linked