Geek Answers Handbook

PostgreSQL: update a table with new records from the same table on a remote server

We have a PostgreSQL production server and many workstations with isolated development environments. Each of them has its own local PostgreSQL server (without replication with the production server). Developers should periodically receive updates stored on the server.

I am trying to figure out how to dump the contents of several selected tables from the server in order to update tables on development workstations. The biggest problem is that the tables I'm trying to synchronize may diverge (developers can add but not delete - new fields in the tables through Django ORM, and the production database schema remains unchanged for a long time).

enter image description here

, , .

, (, pg_dump -U remote_user -h remote_server -t table_to_copy source_db | psql target_db) .

UPD. , () .

+4
1

.

customer.

  • . , , envs;

  • , , customer_$. - "" . , copy_id / copy_stamp;

  • INSERT INTO customer_$ SELECT ... . , , . -w, -w "customer_id IN (SELECT id FROM cust2copy)";

  • () . pg_dump:

    • --column-inserts, , env copying . "" ,
    • --table / -t, .

  • env , (1) (2) ;

  • ;

  • : , , INSERT, , . , :

    • PRIMARY KEY. (1) (2) (3) (4) ID ;
    • UNIQUE KEY, , UPDATE ;
    • FOREIGN KEY, , ,
    • CHECK, .

  • , , .

. , #7 ID ID, , , , ..

, , . , , 50 , .

.

+2

More articles:


All Articles