PostgreSQL WAL Retention and Clean Up: pg_archivecleanup

WAL retention is a critical topic in PostgreSQL database management. However, DBAs often encounter situations such as:

• Several TBs of WALs accumulating in archive destinations
• WALs filling up the pg_wal/pg_xlog directory due to failed archiving
• Required WALs no longer being preserved

While external backup tools handle retention policies well, PostgreSQL also includes a simple utility: pg_archivecleanup. This tool is often overlooked but can be very useful.

It supports two primary use cases:

• Standalone cleanup of old WAL files from a filesystem
• Cleanup of WAL files on standby systems

It operates in:

• Dry-run mode (-n)
• Delete mode (-d)

Dry-run (-n)

Lists WALs older than a given WAL file:

Delete Mode (-d)

Removes WALs and logs actions:

Understanding WAL Retention Criteria

Careful planning is required to avoid losing critical WAL files:

Criteria 1: Preserve WALs from the oldest backup still within retention and PITR requirements.

Criteria 2: Preserve WALs from backup start to completion for consistent restores.

Criteria 3: Preserve WALs needed by standby systems (from restart point).

Criteria 4: Preserve WALs required for crash recovery (from last checkpoint).

Handling Backup-Based Retention

Backup tools generate history files with a .backup extension:

0000000200000000000000C9.000002D0.backup

Use this as a reference for cleanup:

Standby Cleanup

Use archive_cleanup_command:

Note: Multiple standby systems sharing the same archive may require custom logic.

Checkpoint-Based Retention

Retrieve checkpoint WAL info:

Custom Retention Strategies

Most real-world implementations combine multiple criteria using scripts or backup tools. pg_archivecleanup can be used as part of these workflows.

Note: It can also use .partial WAL files as reference.

Code Walkthrough

The pg_archivecleanup source is small (~400 lines) and located in:

/src/bin/pg_archivecleanup/pg_archivecleanup.c

Key functions:

• Initialize() – Validates archive directory
• TrimExtension() – Removes file extensions
• CleanupPriorWALFiles() – Iterates and deletes older files
• SetWALFileNameForCleanup() – Determines cleanup reference WAL

Execution flow:

Initialize → SetWALFileNameForCleanup → CleanupPriorWALFiles

This utility is simple, customizable, and a good candidate for extension via scripts or direct modification.

Enjoy the flexibility PostgreSQL offers for customization. Happy coding.