1

I want to have advice about WAL management. I am expecting a large numbers of wal files (the database is very large and it has like 30 - 40 millions of records in totals) since I am setting up a Logical replication for some tables.

Currently, using this query :

SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';

I find 6817 files. My goal is to reduce this number to just 10 - 20 files maximum.

  1. How do this without affecting any other things such as restore etc ? Currently, the min WAL size is 1GB and the max is 4GB.
  2. Suppose I just want 10 files. How to zip the rest 6807 files ?
  3. If I want to do the whole process automatically, what do I have to do / set ?

I am using PG 12.11 running on Ubuntu 20.04

Thanks. BTW, I am new to PG.

Improve this question
6
  • The only way to reduce the WAL size is to run fewer DML statements. Commented Jul 11, 2022 at 11:11
  • Ok. Nice. How can I safely reduce my existing WAL files then ? Commented Jul 11, 2022 at 11:23
  • Postgres will remove no longer needed WAL segments automatically until min_wal_size is reached. It might take a while though, but eventually they will be gone. Never, ever mess with the pg_wal directory manually. Never Commented Jul 11, 2022 at 11:26
  • I find some articles about archiving the wal files, like dba.stackexchange.com/questions/115147/how-i-compact-wal-files and endpointdev.com/blog/2017/03/…. How come ? Commented Jul 11, 2022 at 11:52
  • Check your max_wal_size setting, and reduce to be 80MB. That should do it. If not, then you might have problem with broken archiving, or old replication slots, or wal_keep_size, or prepared transactions. Hard to say. For debugging I'd suggest you ask on irc/slack/discord, as it will more of a conversation than "here you go: do a, b, c". Commented Jul 11, 2022 at 11:54

1 Answer 1

Reset to default
2

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.

Improve this answer
1
  • Hi @Laurenz Albe, So I check : --- pg_stat_archiver : archived_count 0, failed_count 0, stats_reset 30 June 2022. --- pg_replication_slots : catalog_xmin 6954547, restart_lsn 96/2CCAD398, confirmed_flush_lsn 96/2CCAD3D0, active CHECKED. plugin PGOUTPUT, slot_type Logical, slot_name test_mysub, active_pid 1382293. --- checkpoint_timeout : 300 s What do I have to look ? What do I have to fix ? Thanks Commented Jul 12, 2022 at 5:33

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.