PostgreSQL, update existing rows with pg_restore
- by woky
Hello.
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).
