I'm reading http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html and to me it seems like it will remove any and all WAL segments from my slave server that aren't required to perform recovery upon the slave server crashing (so basically everything except the last 1 or few segments, depending on how far behind my slave is running). I'd like to be able to perform emergency point in time recovery, in the unfortunate case of data corruption or accidental deletion, if possible. To do this, am I left with the following choices:
- Keeping all WAL segments since the last base backup was created (in my case, this is the one that the slave server was created from)
- Making base backups all the time or doing something with PGBarman, etc.?
- Am I misunderstanding something completely?
If option 1 is used, I presume the pg archive cleanup command is rendered useless to me. If option 2 is used, is it true that I should use the cleanup command as intended (deleting all the extra WAL segments immediately after they're committed)? Is there something more that I'm missing (see option 3)?
Note: I'm using Postgres 9.2.4
Best Answer
You're correct. If you want PITR capability, you need to keep all archives between when the base backup was taken and when you want to be able to recover to.
For this reason it's often a good idea to do a fresh base backup (say) weekly, and rotate them out, so you keep a base backup plus a week of archives.
PgBarman should be able to help you with this.
Archive cleanup would not be desirable if you want PITR.