How to increase Postgres WAL size?

Question

PostgreSQL 10.7

I keep getting the message:

HINT: Consider increasing the configuration parameter "max_wal_size".

So, I updated the following parameters and reloaded the configuration.

checkpoint_timeout = 30min
max_wal_size = 2GB
min_wal_size = 80MB

However, the WAL size stays at 16MB. What did I miss?

Btw, I use repmgr for replication wal_level = replica.

Answer 1

max_wal_size controls the total size of all WAL segments, not the size of each segment.

So with a value of 2GB, you are saying that about 128 of those segments can be created before they get re-used.

The hint is about increasing the total number of files (hence the mentioning of max_wal_size), it's not about the size of each segment (file).

---

Up to Postgres 10, the size of each segment is fixed to 16MB

Starting with Postgres 11, you can change the size of each segment but only when you initialize a new database cluster (but again: that's not what that hint is about).

Answer 2

Because of the "title"...

Particular WAL file size can also be changed since PostgreSQL 11 not only using initdb command when creating cluster, but also later when cluster is already running using pg_resetwal command.

1. Check current WAL file size setting (default is 16 MB).

   a) Using parameter.

   psql -c "show wal_segment_size"

   b) Check actual WAL file size.

   ls -lh $PGDATA/pg_wal/

   c) With select.

   SELECT name, size FROM pg_ls_waldir() where name not like '%history' and name not like '%backup';

2. Stop PostgreSQL cluster (on Red Hat).

   systemctl stop postgresql-15.service

3. Change WAL file size.

   pg_resetwal -D $PGDATA --wal-segsize=64

   Size is in MB. Default is 16. Parameter can be set between 1 and 1024.

   Note: Command on Red Hat 8 is at path: /usr/pgsql-15/bin/pg_resetwal

4. Start PostgreSQL cluster (on Red Hat).

   systemctl start postgresql-15.service

5. Repeat step 1 and to see new WAL file setting.

Additional info:

• official PostgreSQL documentation search for "wal-segsize"
• The WAL segment size becomes changeable in PostgreSQL 11