pg_wal is filling up with old wal files! How to fix it?
Recently we noticed that data directory of our postgres instance filling up. Upon investigation we found out that it is because archive_command in postgresql.conf has typo and because of this wal files does not move from pl_wal to backup location.
command which was not working
archive_command = ‘Testing Backup ! -f /data/backups/xxx001/WALarchive/%f && cp %p /data/backups/xxx0001/WALarchive/%f’
I see there were two problem in this command
1st — This command is using two words ‘Testing Backup” to check if file exist at /data/backups/xxx001/WALarchive/
2nd — There was typo in destination file location directory xxx0001 was not existing in /data/backups/ instead xxx001 directory was there.
It does not require postgres database restart.
Step 1: We require to change archive_command in postgresql.conf to
archive_command = ‘Testing ! -f /data/backups/xxx001/WALarchive/%f && cp %p /data/backups/xxx001/WALarchive/%f’
Step 2: Reload configuration file
Step 3: Verify
select value from pg_settings and you should see correct archive_command has taken effect.
SELECT setting,name FROM pg_settings WHERE name like ‘archive_command’;