Merging two backups form a postgresql database

Question

I have a kind of big postgresql database and two backup files (each uncompressed dump file ~56 mb) representing 2 states of the data in time.

• 1st state - backup from a couple days ago
• 2nd state - backup current state of db, minus deleted records and plus the new data added in those two days

this is the same database, and the records have a lot of relations to be handled manually

so I want to join this two backups in order to get a third snapshot of my DB that has the same data as the first state plus the extra data found in state 2.

Anyone can suggest a tool to do this? or a way to do this?

Is a rails project with a psql db.

I already try to merge the two files using the file merge mac app, but because of the size of the files is a really slow process, and there are a lot of differences (a lot of rows added and deleted) to manually pick each conflict.

Answer 1

I don't know an easy way, but I'd try this:

1. Restore both databases individually and dump out only the data using INSERT statements (instead of COPY). Something like pg_dump -a --inserts.
2. Restore the schema from the original backup into the target database.
3. Disable all foreign key constraints.
4. Restore the first dump created in 1.
5. Restore the second dump created in 1., ignore errors.
6. Enable foreign key constraints.

Answer 2

At the end that solution Peter proposed wasn't the one, In any case it help me to figure out the one that finally did it... I explained here in detail http://fespinoza.github.io/2014/01/04/merge-two-backups-of-a-postgresql-database/