3

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/*
1
  • 1
    Why do you think increasing the WAL segment size will increase performance (of whatever you want to increase performance of) in your particular case? – mustaccio May 18 at 18:11
3

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.

3
  • 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 May 18 at 18:13
  • Btw how do I verify the WAL segment size? – tallharish May 18 at 18:20
  • @tallharish: try show wal_segment_size; as a SQL command. – Daniel Vérité May 18 at 19:01
3

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.

4
  • Thanks Laurenz. Let me try this next time :). Meanwhile, how do I verify the WAL segment size? – tallharish May 18 at 18:21
  • ls -l pg_wal is a simple method. – Laurenz Albe May 18 at 18:23
  • Thanks Laurenz. du -hcs ./var/lib/postgresql/13/main/pg_wal/ – tallharish May 18 at 18:32
  • 1
    No, it is the size of the individual files, not the disk space occupied by the directory. – Laurenz Albe May 18 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.