Merging two backups form a postgresql database

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.

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

• Restore both databases individually and dump out only the data using INSERT statements (instead of COPY). Something like pg_dump -a --inserts.

• Restore the schema from the original backup into the target database.

• Disable all foreign key constraints.

• Restore the first dump created in 1.

• Restore the second dump created in 1., ignore errors.

• Enable foreign key constraints.

StackExchange Database Administrators Q#54986, answer score: 4