pg_wal directory is full in postgres db

Question

I have postgres 10 where pg_wal is getting full. I have files pg_wal directory from Dec'2020. I have enabled "archive" but I was getting archiver error and my application stopped working. Since this issue is on Production, I have disabled the archive.

Is it safe to remove old files from pg_wal which is dated on Dec'2020 and Jan'2021?

I can see this:

postgres=# SELECT * FROM pg_replication_slots;

 slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn  | c onfirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+--------------+---------------------
  m9t0179g |        |  physical |        |          |         f |      f |            |      |              |   50/B780000 |
       m9t |        |  physical |        |          |         f |      f |            |      |              | 3B0/EB000920 |

As per the replication_slots output, Is it safe to remove them? — Vinai
– Vinai, Commented Dec 20, 2021 at 12:11

Laurenz Albe · Accepted Answer · 2022-12-09 03:15:59Z

4

No. Never manually remove files.

If you set archive_mode to off (and restart) or change archive_command to something like /bin/true (and reload), then PostgreSQL will start deleting old WAL segments all by itself. Just give it a little time.

If that alone doesn't do the trick, there must be something else that blocks removing WAL segments, like a stale replication slot. In your case, the two stale inactive replication slots are the problem. Get rid of them with

SELECT pg_drop_replication_slot('m9t0179g');
SELECT pg_drop_replication_slot('m9t');

Both slots are suspicious because they are not active, that is, currently not in use. At least the first one is certainly stale, since it is 3.5 TB behind the second one.

edited Dec 9, 2022 at 3:15

answered Dec 13, 2021 at 9:18

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vinai Over a year ago

Thank you for the update. Once I delete these replication slots, do I need to restart the services? Will the old files in pg_wal directory be automatically cleaned as per value defined in "wal_keep_segments"?

Laurenz Albe Over a year ago

It will be cleaned automatically.

Marc Over a year ago

How did you recognize that the replication slots are "stale"?

Laurenz Albe Over a year ago

@Marc I have added an explanation.

Collectives™ on Stack Overflow

pg_wal directory is full in postgres db

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related