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
– joop, Commented Nov 25, 2013 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
– Dariusz Mydlarz, Commented Nov 25, 2013 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
– joop, Commented Nov 25, 2013 at 17:12

Dariusz Mydlarz · Accepted Answer · 2013-11-25 18:38:49Z

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, 2013 at 18:38

Dariusz Mydlarz

3,0327 gold badges34 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joop Over a year ago

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

Collectives™ on Stack Overflow

How to merge dump into database from PostgreSQL?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related