如何减少EDB PostgreSQL 9.6实例WAL文件数量及调整WAL与检查点配置

Alright, let's break down how to fix your EDB PostgreSQL 9.6 WAL file issues—this is a typical scenario when write workloads or misconfiguration lead to storage bloat, so let's walk through each solution step by step.

WAL files stick around until PostgreSQL confirms they're no longer needed (for archiving, replication, or checkpoints). Here's how to trim the count:

• Verify WAL archiving is working correctly: If you've enabled archive_mode, make sure your archive_command is executing successfully. A stuck archiver will leave WAL files piling up. You can check the PostgreSQL logs for errors related to archiving, or manually run the archive command to test it (e.g., cp /path/to/current/wal /path/to/archive/test). Also ensure the archive directory has proper permissions and enough space.
• Check replication health (if using standby instances): Primary servers retain WAL files that standbys haven't yet replayed. Run SELECT * FROM pg_stat_replication; to check if the standby's replay_location is close to the primary's pg_current_wal_lsn(). If there's a large gap, troubleshoot the standby's performance (e.g., slow IO, network latency) to get it caught up.
• Tune wal_keep_segments: This parameter defines how many WAL segments the primary keeps for standbys. The default is 32 (512MB with 16MB segments). If your standby is stable and catches up quickly, reduce this to a lower value (e.g., 16 or 8) via ALTER SYSTEM SET wal_keep_segments = 16; then reload the config with SELECT pg_reload_conf();.
• Ensure checkpoints are completing: Checkpoints trigger the cleanup of old WAL files (once archived/replicated). Monitor the pg_stat_bgwriter view to confirm checkpoints are running on schedule—look for checkpoints_timed and checkpoints_req metrics.

Important note: You can't change WAL segment size on a running instance—this is set during database initialization. Here's the safe way to do it:

1. Take a full database backup: Use pg_dumpall to export all databases, roles, and configurations, or use EDB's native backup tools if you have them. Store the backup in a safe location.
2. Stop the PostgreSQL service: Use your system's service manager (e.g., systemctl stop edb-as-9.6 on RHEL/CentOS, service edb-as-9.6 stop on Debian/Ubuntu).
3. Move or delete the existing data directory: For safety, rename the current data directory instead of deleting it (e.g., mv /var/lib/edb/as9.6/data /var/lib/edb/as9.6/data_backup).
4. Initialize a new data directory with smaller WAL segments: Run initdb -D /var/lib/edb/as9.6/data -W 8MB (replace 8MB with your desired size—4MB is the minimum, but 8MB is a common middle ground). Adjust the path to match your installation.
5. Restore your backup: Use psql -f /path/to/your/backup.sql postgres to restore the full backup into the new data directory.
6. Start the service and verify: Launch PostgreSQL, then check the WAL directory (e.g., /var/lib/edb/as9.6/data/pg_wal)—new WAL files should be the size you specified.

⚠️ Heads up: Smaller WAL segments mean more frequent file switches, which can add minor IO overhead. Test this in a staging environment first to ensure it doesn't impact your workload.

PostgreSQL 9.6 uses these key checkpoint parameters to control WAL generation and cleanup:

• checkpoint_segments: This sets the maximum number of WAL segments generated between checkpoints (default 32 = 512MB). Reducing this triggers checkpoints more frequently, which helps clean up WAL files sooner. Try setting it to 16 (256MB) with ALTER SYSTEM SET checkpoint_segments = 16; then reload the config.
• checkpoint_completion_target: Controls how smoothly checkpoints run, as a fraction of checkpoint_timeout. The default is 0.5; increasing it to 0.8 spreads the checkpoint IO over a longer period, reducing peaks and helping WAL clean up more consistently. Set it with ALTER SYSTEM SET checkpoint_completion_target = 0.8; and reload.
• checkpoint_timeout: The maximum time between automatic checkpoints (default 5 minutes). If you reduce checkpoint_segments, this might not trigger as often, but you can adjust it (e.g., to 10 minutes) if your workload benefits from less frequent checkpoints.
• archive_timeout: If using archiving, set this to a non-zero value (e.g., 300 seconds) to force WAL switches even if the segment isn't full. This ensures old WAL files get archived quickly instead of sitting idle. Use ALTER SYSTEM SET archive_timeout = 300; and reload.

Final Tips #

• Always back up your postgresql.conf before making changes, and test config tweaks in a staging environment first.
• Monitor WAL metrics regularly: Use pg_ls_waldir() to list WAL files and their sizes, and pg_stat_bgwriter to track checkpoint performance.

内容的提问来源于stack exchange，提问作者Dheeraj Nambiar