PostgreSQL, update existing rows with pg_resotre

Question

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 advices.

Answer 1

Do this:

pg_dump -t table1 production_database > /tmp/old_production_database_table1.sql
pg_dump -t table1 devel_database > /tmp/devel_database_table1.sql
psql production_database
truncate table1
\i /tmp/devel_database_table1.sql
\i /tmp/old_production_database_table1.sql

You'll get a lot of duplicate primary key errors on second \i, but it'll do what you want: all rows from devel will be updated, all rows not in devel will not be updated nor deleted.

If you have any references to table1 then you'll have to drop them before and recreate them after importing. Especially check for on delete cascade, set null or set default references to table1 - you'd loose data in other tables if you have those.