PostgreSQL, update existing rows with pg_restore

postgresql restore dump

7,428

Proceed to https://stackoverflow.com/questions/115369/do-you-source-control-your-databases.

7,428

Dennis Williamson

Stackathlon Leader Boards #SOreadytohelp The Bridge Builder I am the first user to earn at least 50,000 reputation points each on all three of Stack Overflow, Server Fault and Super User. I was also the first user to surpass the 10K, 20K, 30K and 40K point levels each on all three of those sites. ###Are you a Stackathlete? ###Check the standings on Profile:I'm a Unix/Linux devops engineer/sysadmin/programmer Devotion to Duty Try my unofficial Printifier for xkcd.

Updated on September 17, 2022

Comments

Dennis Williamson over 1 year
I need to sync two PostgreSQL databases (some tables from development db to production db) sometimes.

So I came up with this script:
```
[...]
pg_dump -a -F tar -t table1 -t table2 -U user1 dbname1 | \
pg_restore -a -U user2 -d dbname2
[...]
```
The problem is that this works just for newly added rows. When I edit non-PK column I get constraint error and row isn't updated. For each dumped row I need to check if it exists in destination database (by PK) and if so delete it before INSERT/COPY.

Thanks for your advice.

(Previously posted on stackoverflow.com, but IMHO this is better place for this question).
Admin over 14 years

That's not possible in my case, because I have some tables that must be preserved in development and production DB (customers, ...).