Why pg_wal directory holding 16MB WAL file of last 24hr in PostgreSQL13?

Question

In my PostgreSQL pg_wal directory total wal segments sum about 200GB where max_wal_size 8GB.

We archived WAL file at desired location using archive_command but wal file not removed from pg_wal directory, I am not sure why?

I have 3 node DB cluster maintained by pgpool. Where node3 is down due to some reason. Because of that replication slot is not active. Here node1 is primary and node2 is standby node which is working fine and replication slot active of that.

Basically I want to know why PostgreSQL is not removing those WAL file form pg_wal directory? And how to solve this?

I am assuming that, it is holding those WAL file because of that replication slot of node3 which is not active.

If I am right then, what should I do in this case ? Should I delete that replication slot of node3 to solve this issue.

Note: I don't want to recover that node3 right now.

Answer 1

Yes, the problem is very likely a replication slot.

Run the following statement on the primary node to see how much each replication slot is lagging:

SELECT slot_name,
       pg_wal_lsn_diff(
          pg_current_wal_lsn(),
          restart_lsn
       ) AS bytes_behind,
       active,
       wal_status
FROM pg_replication_slots
WHERE wal_status <> 'lost'
ORDER BY restart_lsn;

Once you identified the stale replication slot, drop it with

SELECT pg_drop_replication_slot('slotname');

Then wait for the next checkpoint or run

CHECKPOINT;

Now the extra WAL segments should be gone.

Find some more background information in my article.