Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a postgresql database that lost some records. I want to take a pg_dump SQL dump of the database from a few days ago and merge it with the current production database. A lot of the records are going to be duplicates so it can skip the duplicates. What is the best way to do this?

share|improve this question

Not knowing what kind of dump you are working with (plain, tar, or custom)...

If you have dblink available then you could restore to a different database and pull the data over to the production box.

A more convoluted solution might be to

  1. restore the dump to a different database,
  2. change the schema/table names to not conflict with anything in the production database,
  3. dump the renamed tables and use that dump to get the renamed tables into the production database.
  4. After all that you can then use plain SQL to find/insert the missing data.
share|improve this answer

patch could be you friend could try creating a new dump, we'll call it current_dump and do a diff against old_dump then use

   diff old_dump new_dump > patch.input
    (copy the new_dump) to another file at this point new.db.dump)
    patch new.db.dump patch.input

You might have to sort the sort the dump files before you do the diff and depending on how big your database is this might not really be practical.

Also be very careful and do this against a backup until you are sure its right.

Otherwise, maybe there are ETL type tools that can do this.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.