CODEWITHSUNDEEP
postgresql
Vinai
Vinai

Reputation: 11

pg_wal directory is full in postgres db

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 |

Upvotes: 1

Views: 5607

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246082

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.

Upvotes: 3

Related Questions