1

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

Dennis Williamson
  • 62,149
  • 16
  • 116
  • 151

3 Answers3

1

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

Community
  • 1
Peter Eisentraut
  • 3,665
  • 1
  • 24
  • 21
0

In any environment I've seen, this is typically not done on a table by table basis.

  • There are a number of possibilities for any row: SELECT / INSERT / UPDATE / ALTER ... you would need to take care of all of these possibilities.

  • Tables are not the only things that live inside databases, especially PostgreSQL databases. Sequences, functions, aggregates, views etc. can possibly all need to be copied over.

If I were in your situation, I'd dump the whole database and use it to restore a clean version.

pg_dump --clean --no-owner --no-privileges dbname ## other pg_dump / psql options
Philip Reynolds
  • 9,799
  • 1
  • 34
  • 33
  • That's not possible in my case, because I have some tables that must be preserved in development and production DB (customers, ...). –  Nov 27 '09 at 10:29
0

Maybe using dblink and inserting only what you need from a select?

http://www.postgresonline.com/journal/index.php?/archives/44-Using-DbLink-to-access-other-PostgreeSQL-Databases-and-Servers.html
http://www.postgresql.org/docs/current/static/dblink.html
skuda21
  • 173
  • 1
  • 6