patternsqlMinor
Postgresql 13 Change WAL segment size
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.
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
You can verify the size of the WAL segments as
initdb -D ./data --wal-segment=1024I 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 startYou 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
Warning: for that, run
The command would look somewhat like this:
You may need to increase
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.