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.