1
Diagnostic step 1
Move PostgreSQL helper scripts to a directory excluded from system-wide maintenance (e.g., /usr/local/pgsql/bin) and set strict ownership postgres:postgres and mode 750; update archive_command accordingly.
FULL AI ANALYSIS
PostgreSQL WAL archiving fails with 'archive_command' permission denied after automated logrotate changed owner
Generated Oct 3, 2026, 12:35:03 AM
Analysis summary
Root cause is an automated maintenance script (logrotate or global postrotate) incorrectly applying permission changes to the PostgreSQL helper script, causing archive_command to fail. Preventative steps include isolating PostgreSQL helper scripts from system-wide maintenance, enforcing ownership/permissions via configuration management, and adding health checks/alerts for WAL accumulation. Immediate recovery should protect data integrity while avoiding destructive permission changes.
Possible causes
System-wide logrotate or postrotate script mistakenly altering ownership/permissions of PostgreSQL helper scripts
95%Human error during manual maintenance changing sensitive file permissions
20%Misconfigured backup/cleanup cron job changing file modes in parent directory
15%NFS mount permission mismatch combined with script executed as non-root user
10%Suggested fixes
2
Diagnostic step 2
Add a lightweight monitoring check: alert when pg_wal directory size or number of WAL files exceeds a threshold and when archival fails (parse PostgreSQL logs and send alert via existing monitoring).
3
Diagnostic step 3
Harden logrotate/postrotate scripts: add explicit include/exclude paths, test on a staging host, and require a dry-run approval for changes that affect system-owned paths.
4
Diagnostic step 4
Create a safe recovery script that verifies file ownership/mode for archive_command helper and NFS writable state and runs as a privileged operator (with audit logging) — document manual steps for immediate fix and avoid automated blind fixes.
5
Diagnostic step 5
Consider enabling 'archive_mode = on' and using a robust archiver (e.g., rsync with checksums or a dedicated backup agent) and place archives on a location whose permissions are managed by configuration management (Ansible/Puppet).