I am looking for a good method of determining what WAL-segments can be deleted from our backup-archive.
We are using Postgresql 9.3 and do nightly basebackups along with archiving of WAL-segments.
We would like to prevent our backup-archive from growing forever, and thus delete basebackups and WAL-segments older than say 1 year.
Is there a good way of finding the oldest WAL-segment needed for a given basebackup? (If not, what is the typical method for deleting old WAL-segments?)
You can look inside the backup_label file in the base backup. It has the starting WAL file required to recover from that backup. If you look at the .backup file that's been archived to the log archive it also contains the last segment required.
