PostgreSQL Write Ahead Logs Archive Mode

I'm trying to figure out various things surrounding PostgreSQL and how backups should work together with WAL and Commvault Simpana. Simpana is telling me that everything is ok, but leaves files lying around in the WAL Archive directory.

Let the journey begin.

Environment

PostgreSQL & OS Version

PostgreSQL 9.3 is running on an Ubuntu 14.04.3 LTS server.

Postgres WAL Config

The postgres.conf file is set as follows for WAL archiving.

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
#wal_level = minimal                    # minimal, archive, or hot_standby
wal_level = archive

[...]

# - Archiving -
archive_mode = on
#archive_mode = off             # allows archiving to be done
                                # (change requires restart)
archive_command = 'cp %p /pgsql-backup/archive/postgres1/%f'
                                # command to use to archive a logfile segment
                                # archive_command = ''           
                                # command to use to archive a logfile segment
                                # placeholders: %p = path of file to archive
                                #               %f = file name only
                                # e.g. 'test ! -f /mnt/server/archivedir/%f    && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0            # force a logfile segment switch after this
                                # number of seconds; 0 disables

If the test ... part is left in the archive_command it breaks the Simpana backup, which is why we omitted it.

The above configuration should result in the WAL files being copied from the /pg_xlog/ directory to the /pgsql-backup/archive/postgres1/ directory, when ...

• no longer required, because of a pg_basebackup

• a WAL file is full (default 16MB) and no longer in use

Commvault Simpana

The client

We have the same issue with Simpana postgres backup.
The documentation states:


What are all the files that get backed up as part of Log Only backups?
Only the transaction log files that are relevant to the current backup
cycle are backed up. The entire Log directory content will not be
backed up. For example, if you run a log backup at time T1 and later a
full backup (data and log) at time T2; the transaction log files
generated between T1 and T2 will not be part of the latest full backup
as they are not relevant for the current backup cycle. Also, .backup
files, which are temporary files created by PostgreSQL Server as part
of log archiving, are not backed up as part of log backups. How does
log file cleanup happen when Archive Delete option is enabled?


How does log file cleanup happen when Archive Delete option is
enabled? With the Archive Delete option enabled, only those
transaction log files that are backed up as part of log backup are
deleted. The transaction log files that were not deleted during the
earlier backup attempts are not cleaned up. Also, due to continuous
archiving of transaction log files, you may see new transaction log
files in the WAL directory after successful log backups. Therefore,
the transaction log files that are not part of any log backup as well
as the temporary files created by PostgreSQL Server will be left
behind in the Log directory.

So:
If you dont make an extra log-only backup, just before the next full backup; All these wals never get backupped/deleted;
and worse:
You can never PITR to this time period in case of disk failure.

StackExchange Database Administrators Q#133144, answer score: 2