Postgresql 13 Change WAL segment size

Question

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/*

Answer 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.

Answer 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.