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

• mustaccio: Why do you think increasing the WAL segment size will increase performance (of whatever you want to increase performance of) in your particular case?

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.

• tallharish: Thanks Daniel. I did the following to drop and create a new cluster. pg_lsclusters sudo pg_dropcluster --stop 13 main sudo pg_createcluster 13 main -- --wal-segsize=256 sudo pg_ctlcluster 13 main start
• tallharish: Btw how do I verify the WAL segment size?
• Daniel Vérité: @tallharish: try show wal_segment_size; as a SQL command.

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.

• tallharish: Thanks Laurenz. Let me try this next time :). Meanwhile, how do I verify the WAL segment size?
• Laurenz Albe: ls -l pg_wal is a simple method.
• tallharish: Thanks Laurenz. du -hcs ./var/lib/postgresql/13/main/pg_wal/
• Laurenz Albe: No, it is the size of the individual files, not the disk space occupied by the directory.
• Alexi Theodore: What happens to replication slots/servers if this is done to the primary? Does the secondary need to have the same wal-segsize as the primary?