Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
If you only want to recover deleted rows (did I understand correctly?) from the second backup, perhaps this SO answer may help stackoverflow.com/questions/6151632/… –  foibs Dec 13 '13 at 19:30
add comment

migrated from stackoverflow.com Dec 13 '13 at 19:06

This question came from our site for professional and enthusiast programmers.

2 Answers

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.
share|improve this answer
    
I want to try this, in any case is too much trouble for you give me the commands please? –  fespinozacast Dec 13 '13 at 22:54
    
the steps 3, 5 and 6 –  fespinozacast Dec 13 '13 at 22:55
add comment
up vote 0 down vote accepted

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/

share|improve this answer
add comment

Your Answer

 
discard

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.