What is pg_archivecleanup?
pg_archivecleanup is a utility provided by PostgreSQL designed to clean up old Write-Ahead Logging (WAL) files from a directory that is being used as an archive location. This tool is particularly useful when running a PostgreSQL server in a hot standby or streaming replication setup, where archived WAL files can accumulate and eventually consume significant disk space.
How pg_archivecleanup Works
The tool looks at the replication slot or the WAL history file of the standby server to determine which WAL files are no longer required for the standby server to catch up with the primary.
pg_archivecleanup then removes these unnecessary WAL files from the archive.
Using pg_archivecleanup to Maintain WAL Archives
pg_archivecleanup, ensure that you have a proper backup strategy in place, as it will permanently delete WAL files from the archive.
Cleaning Up the Archive Directory
pg_archivecleanup /path/to/archive WALfile_name
This command will remove all WAL files in the
/path/to/archive directory that are older than the
Automating Cleanup with Recovery Configuration
To automate the cleanup process, you can include
pg_archivecleanup in the
recovery.conf file (for versions before PostgreSQL 12) or the
postgresql.conf file (from PostgreSQL 12 onwards) of the standby server.
archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'
This configuration tells PostgreSQL to run
pg_archivecleanup automatically, using the last restart point of the standby server to determine which files can be safely deleted.
Use Cases for pg_archivecleanup
- Disk Space Management: Regularly remove unneeded WAL files to prevent the archive directory from using up too much disk space.
- Standby Server Maintenance: Ensure that the standby server has all the required WAL files for recovery without keeping unnecessary files.
- Backup Retention Policies: Implement retention policies for WAL archives in environments where backups are taken periodically and older archives are no longer needed.
Common Mistakes and Issues
- Accidental Deletion: Be cautious with the
WALfile_nameargument to avoid accidentally deleting required WAL files, which could disrupt replication or recovery processes.
- Incorrect Archive Path: Ensure that the correct archive directory path is provided. Mistakes in the path can lead to the deletion of the wrong files or no action being taken.
- Filesystem Permissions: The user running
pg_archivecleanupmust have the necessary permissions to delete files from the WAL archive directory.
- Permission Denied: If
pg_archivecleanupreports permission errors, check the filesystem permissions and ensure the PostgreSQL user can write to and delete files in the archive directory.
- Replication Issues: After running
pg_archivecleanup, if the standby server cannot find required WAL files, make sure you didn’t remove files that were still needed for replication.
- Automation Failures: If the automatic cleanup isn’t working, verify the
archive_cleanup_commandconfiguration in the standby server’s configuration file.
pg_archivecleanup is an essential tool for managing WAL archives in a PostgreSQL environment. It helps maintain a tidy archive directory by removing obsolete WAL files, thus preventing unnecessary disk space usage. When used carefully and configured properly,
pg_archivecleanup can significantly simplify the maintenance of standby servers and support efficient replication setups.
For detailed guidance on using
pg_archivecleanup, including all command-line options, refer to the official PostgreSQL documentation.