Return to Answer

added 126 characters in body

edited Jul 12, 2022 at 5:47

62.7k
4
58
94

WAL segments are not files that are kept around for ever. After they have been archived and are no longer necessary fro crash recovery, PostgreSQL automatically deletes them. You must never manually modify anything in pg_wal (or anywhere else in the data directory, with the exception of the configuration files), on pain of data corruption.

The correct way to reduce the amount of WAL would be to set max_wal_size, but note that small values can lead to increased write I/O, which is bad for performance.

All that said, there is something wrong with your database cluster. If you are using the default WAL segments size, you must have over 100GB of WAL. That is a clear indication that something is wrong. Either the archiver is not working (check pg_stat_archiver) or there is a stale replication slot that holds back WAL (check pg_replication_slots). Once you have fixed the problem, run CHECKPOINT (or wait for the next to happen by itself), and PostgreSQL will remove the extra WAL segments.

Yet another possibility is that you have wal_keep_size (wal_keep_segments on older versions) set to a very high value.

WAL segments are not files that are kept around for ever. After they have been archived and are no longer necessary fro crash recovery, PostgreSQL automatically deletes them. You must never manually modify anything in pg_wal (or anywhere else in the data directory, with the exception of the configuration files), on pain of data corruption.

The correct way to reduce the amount of WAL would be to set max_wal_size, but note that small values can lead to increased write I/O, which is bad for performance.

All that said, there is something wrong with your database cluster. If you are using the default WAL segments size, you must have over 100GB of WAL. That is a clear indication that something is wrong. Either the archiver is not working (check pg_stat_archiver) or there is a stale replication slot that holds back WAL (check pg_replication_slots). Once you have fixed the problem, run CHECKPOINT (or wait for the next to happen by itself), and PostgreSQL will remove the extra WAL segments.

WAL segments are not files that are kept around for ever. After they have been archived and are no longer necessary fro crash recovery, PostgreSQL automatically deletes them. You must never manually modify anything in pg_wal (or anywhere else in the data directory, with the exception of the configuration files), on pain of data corruption.

The correct way to reduce the amount of WAL would be to set max_wal_size, but note that small values can lead to increased write I/O, which is bad for performance.

All that said, there is something wrong with your database cluster. If you are using the default WAL segments size, you must have over 100GB of WAL. That is a clear indication that something is wrong. Either the archiver is not working (check pg_stat_archiver) or there is a stale replication slot that holds back WAL (check pg_replication_slots). Once you have fixed the problem, run CHECKPOINT (or wait for the next to happen by itself), and PostgreSQL will remove the extra WAL segments.

Yet another possibility is that you have wal_keep_size (wal_keep_segments on older versions) set to a very high value.

answered Jul 11, 2022 at 12:00

62.7k
4
58
94

WAL segments are not files that are kept around for ever. After they have been archived and are no longer necessary fro crash recovery, PostgreSQL automatically deletes them. You must never manually modify anything in pg_wal (or anywhere else in the data directory, with the exception of the configuration files), on pain of data corruption.

The correct way to reduce the amount of WAL would be to set max_wal_size, but note that small values can lead to increased write I/O, which is bad for performance.

All that said, there is something wrong with your database cluster. If you are using the default WAL segments size, you must have over 100GB of WAL. That is a clear indication that something is wrong. Either the archiver is not working (check pg_stat_archiver) or there is a stale replication slot that holds back WAL (check pg_replication_slots). Once you have fixed the problem, run CHECKPOINT (or wait for the next to happen by itself), and PostgreSQL will remove the extra WAL segments.