Postgres Incremental Backup (Continuous Archiving WAL)

Question

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.

Answer 1

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.

• user1897306: Now suppose IF I want to take incremental backup at evey end of day, do I need to,
• user1897306: 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()).
• user1897306: 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?
• user1897306: 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.
• araqnid: 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.