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'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:

  1. 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)
  2. Making base backups all the time or doing something with PGBarman, etc.?
  3. 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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks, Craig. I'll give PG Barman a shot, soon. Also, I only just realized that 2nd Quadrant was the creator of the project. –  orokusaki Jul 25 '13 at 17:01
1  
@orokusaki Indeed; as well as repmgr, the streaming replication features in Pg, and more :-) –  Craig Ringer Jul 25 '13 at 23:45
    
Oh my gosh, I had no idea. Thanks for all your great work (to the company and you yourself of course). –  orokusaki Jul 26 '13 at 15:32

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.