PostgreSQL WAL archiving fails with 'archive_command' permission denied after automated logrotate changed owner

Environment

Primary PostgreSQL 13 on Ubuntu 20.04, wal_level=replica, archive_mode=on, archive_command='bash /opt/pg/archive-wal.sh %p %f', WALs archived to NFS mount /mnt/pg_archive; cron-driven logrotate runs daily at 02:00.

Symptoms

WAL archival stops at 02:05 with repeated errors in PostgreSQL log: 'archive command failed with exit code 1' and WAL files pile up in pg_wal directory; replication to hot standby falls behind and streaming eventually disconnects when disk fills.

Observations

Checking system logs shows logrotate ran just before the failure and changed ownership of /opt/pg/archive-wal.sh to root:root with mode 700; previously it was postgres:postgres 750. Running the script as the postgres user now returns 'Permission denied' when trying to write to /mnt/pg_archive because the NFS directory retained original perms but the script is not executable by postgres. Manual chmod/chown fixes allow archive to resume.

Already attempted

Re-applied original ownership/chmod and restarted PostgreSQL; WAL archiving resumed. Added verbose logging to archive-wal.sh to capture failures. Reviewed logrotate config for /var/log/postgresql but found an extra global postrotate script that invoked a permissions fix incorrectly targeting /opt/pg; disabled it temporarily.

Question

What safer practices and checks should I put in place so automated rotation and maintenance cannot accidentally change ownership or permissions of PostgreSQL helper scripts and stop WAL archiving again, and what automated recovery steps are advisable when archive_command fails?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely 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%

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.

Suggested Fixes

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.

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).

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.