Postgres WAL full and database stopped/exited with error - No space left on device

Question

I am new to postgresql.

We had a full disk issue on PostgreSQL DB where data and WAL was set.

WAL was taking almost 65 GB. This is a lower environment and we don't need to restore to a point. Compared to Oracle, we can put this DB to no archive log mode.

I wasn't sure of the step I needed to take. In some posts, I read deleting WAL can be disastrous like the database going to an inconsistent state.

I understood that WAL is not supposed to be this big. No physical replicas are present for this DB. But when I checked

select * from pg_replication_slots;

I saw the below entry.

[local]:7432 =# select * from pg_replication_slots;
 |                          slot_name                            |    plugin     | slot_type | datoid |       database       | temporary | active | active_pid |  xmin  | catalog_xmin | restart_lsn | confirmed_flush_lsn|
 |---------------------------------------------------------------|---------------|-----------|--------|----------------------|-----------|--------|------------|--------|--------------|-------------|--------------------|
 |uat_postgresql_f_00016430_02c0d965_7608_c14a_8c3b_b1a259abd32d | test_decoding | logical   |  16430 | xxa_domain_services  | f         | f      |     [null] | [null] |     73310839 | 50/155EFB78 | 50/155F08E0        |
(1 row)

Is this something that not allowing WAL to get cleared?

Or how should I troubleshoot the reason for WAL getting bigger and bigger?

Answer 1

Yes, that is probably the reason. The replication slot marks a position in the WAL (50/155EFB78), indicating that something still needs WAL from that position on. So PostgreSQL doesn't discard any WAL older than the position.

Now this replication slot is not active, so nobody advances the position, which causes your trouble. Either restart the consumer that uses the replication slot or get rid of it:

SELECT pg_drop_replication_slot('uat_postgresql_f_00016430_02c0d965_7608_c14a_8c3b_b1a259abd32d');

But first you will have to restart PostgreSQL. For that, you have to extend the size of the file system. Don't randomly delete files, that will make your problem worse.