HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Postgresql 13 Change WAL segment size

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlsizesegmentwalchange

Problem

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

Solution

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.

Context

StackExchange Database Administrators Q#291795, answer score: 6

Revisions (0)

No revisions yet.