Postgresql 13 Change WAL segment size

All we need is an easy explanation of the problem, so here it is.

On my Ubuntu 20 server, I installed PostgreSQL 13 using the apt manager. I read some articles on performance tuning of PostgreSQL [1] and thought of increasing the WAL segment size (from default of 16MB). I see the following instruction, however, I don’t know where to run this.

initdb -D ./data --wal-segment=1024

I guess the documentation refers to installing from PostgreSQL source code, which I don’t intend to do. How do I go about change the WAL segment size?

[1] https://software.intel.com/content/dam/develop/external/us/en/documents/Open-Source-Database-Tuning-Guide-on-3rd-Generation-Intel-Xeon-Scalable-Processors.pdf


Following Daniel’s answer, I did the following steps

$ pg_lsclusters
$ sudo pg_dropcluster --stop 13 main
$ sudo pg_createcluster 13 main -- --wal-segsize=256
$ sudo pg_ctlcluster 13 main start

You can verify the size of the WAL segments as

# du -hcs /var/lib/postgresql/13/main/pg_wal/*

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Ubuntu/Debian packages for Postgres have their own layer on top of initdb and pg_ctl to control multiple instances and the integration with systemd.

The command that may be used to create an instance with specific options in Debian/Ubuntu is:

pg_createcluster [options] version name [-- initdb options]

use pg_lsclusters to see the list of already existing clusters. Possibly you want to drop the existing default cluster named main using the default segment size that you don’t want, in order to have one single Postgres instance with the desired wal segment size.

Method 2

If you want to change the WAL segment size of an existing cluster, you can use pg_resetwal.

Warning: for that, run pg_resetwal only on a cluster that has been shut down cleanly. Running pg_resetwal on a crashed cluster will cause potential data loss.

The command would look somewhat like this:

/usr/lib/postgresql/13/bin/pg_resetwal -D /var/lib/postgresql/13/main --wal-segsize 64

You may need to increase min_wal_size if you increase the WAL segment size.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply