Merging Postgresql dump with production database

Question

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?

Answer 1

I would restore the dump in another database. Then, for the table I suspect to have lost data, I would compare it to the table restored in the other database. If you don't know which table has lost data, you have to program this comparison on each table of the database.

Remember that you can't perform sql queries using multiple databases.

You could rename the table you want to examine and dump/restore it into the database containing the table having lost data. And then, you could do a sql query like this one :

select * from foo where var1 not in (select var1 from foo_restored);

Answer 2

You could try RULES. Have a look at http://www.pointwriter.com/blog/index.php?/archives/6-REPLACE-in-PostgreSQL.html

Adapting this example:

CREATE RULE "insert_ignore" AS
    ON INSERT TO "your_table"
    WHERE
      EXISTS(SELECT 1 FROM your_table WHERE key=NEW.key [...])
    DO INSTEAD
      NOTHING;

This is untested so try it in a safe environment instead.