2

I need to take incremental backup of Postgres DB. I need to capture (backup of) daily changes on a database. So that I can restore DB with any particular data I want.

As per Continuous Archiving, I am doing below steps:

  1. I configured postgresql.conf, and added wal_level, archive_mode and archive_command
  2. Restart server
  3. Added some data
  4. pg_start_backup('label');
  5. Take backup of data dir
  6. pg_stop_backup();
  7. Added more data
  8. Take backup of wal dir and pg_xlog dir
  9. Replace backup data (from step 5) in data dir
  10. Start server
  11. Copy pg_xlog (from step 8) in current pg_xlog dir
  12. Added recovery.conf and added restore_command
  13. Restart DB

Now I want data which I added in step 7. (I had taken backup of WAL file and pg_xlog at that time). So I consider this data as a backup of a particuar day. And I want to restore my database with this date's data.

Now If I replace those files ie. WAL and pg_xlog and recreate recovery.conf and restart db, I do not get the data in step 7, still old one.

Please let me know if I am missing anything.

Improve this question
0

1 Answer 1

Reset to default
2

In order to restore a backup, you need to have the base archive of all the data files, plus a sequence of xlogs. An "incremental backup" can be made, of just some more xlogs in the sequence. Note that if you have any missing xlogs, then recovery will stop early.

So it's not clear here exactly what you've done, unless you changed the level of detail you're mentioning part way through your list. When you make a copy of more segments that have been put into the archive directory after adding more data, you need to ensure that all the data has been archived: using pg_start_backup and pg_stop_backup usually does this for you, but you don't mention it the second time. You need to at least do a pg_switch_xlog to have the current xlog segment immediately archived.

If you think that recovery is not consuming enough xlog segments, look at the recovery log to see if it tried to take them all. And have your recovery command make some sort of mark on which xlog files were taken.

Improve this answer
5
  • Now suppose IF I want to take incremental backup at evey end of day, do I need to, Commented Dec 12, 2012 at 13:38
  • Question 1) Now suppose If I want to take incremental backup at evey end of day, do I need to, 1)pg_start_backup(); 2)pg_stop_backup(); And then need to copy WAL files from the directory I ahve specified in archive_command and pg_xlog.? Do I need to take this step eveyday, (i mean pg_start_backup() and pg_stop_backup()). Commented Dec 12, 2012 at 13:54
  • Question 2) Now when I talking incremental backup eveyday, and now I want to restore. Do I need to restore files from base backup eveytime i mean for doing restore first i have to copy files from base backup? Or if I have restoreed from base backup once, I do not require to copy data files eveytime. Whether I require to take data files or not. All I need to replace or copy backed up WAL files and pg_xlog dir files to currunt WAL and pg_xlog files. Is it true? Commented Dec 12, 2012 at 13:54
  • Question 3 ) As we say incremental backup, can we only take backup of only added WAL files for that particular day. ======================================================================================== I have base backup with me. Now can u explain step by step how to take per day backup and restore at any particular day? Thanks in advance. Commented Dec 12, 2012 at 13:55
  • You need to do pg_switch_xlog() copy files from your xlog archive that are not part of any previous backup. In order to restore, you must have the base backup and all xlog files up to the point that you want to restore too, yes. Once you have restored a db and started running it, you can't apply more xlog files. Commented Dec 12, 2012 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.