Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to merge dump into database from PostgreSQL?

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?

like image 841
Dariusz Mydlarz Avatar asked Nov 25 '13 08:11

Dariusz Mydlarz


1 Answers

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>
    
like image 102
Dariusz Mydlarz Avatar answered Sep 21 '22 13:09

Dariusz Mydlarz