Postgres WAL file not getting deleted

Question

I've got the following command for archive

archive_command = 'rsync -av %p /home/archive/%f'  

We clean files from there.

But the problem is that after the file has been copied to the archive folder, it is not deleted from /var/lib/pgsql/9.6/data/pg_xlog.

I don't see any error in Postgres logs. This is Postgres 9.6 with one master one standby configuration.

Additionally it would helpful to know the procedure to delete these old WAL files once they are processed.

Answer 1

There are several reasons why WAL files don't get removed from pg_xlog:

• max_wal_size is set higher than the amount of WAL in pg_xlog.

• wal_keep_segments is set higher than the number of WAL files in pg_xlog.

• There is a replication slot at a WAL location older than the WAL files.

To find out about the replication slots, try the following query:

SELECT slot_name,
       lpad((pg_control_checkpoint()).timeline_id::text, 8, '0') ||
       lpad(split_part(restart_lsn::text, '/', 1), 8, '0') ||
       lpad(substr(split_part(restart_lsn::text, '/', 2), 1, 2), 8, '0')
       AS wal_file
FROM pg_replication_slots;

This will give you the name of the oldest WAL file each replication slot still needs.