Up to PostgreSQL 10 the wal segment size was fixed once PostgreSQL was compiled and installed. When you wanted to have something else than the default you needed to tell that to the configure command when building from source, e.g.
[email protected]:/home/postgres/postgresql/ [PGDEV] ./configure --with-wal-segsize=64MB
For PostgreSQL 11 two commits landed (1 and 2) which change this. Lets have a look.
When you check the current development documentation for initdb you’ll notice a new parameter called “–wal-segsize”. This allows to change the WAL segment size from the default of 16MB when you initialize you new cluster:
[email protected]:/home/postgres/ [PGDEV] initdb --wal-segsize=32 /var/tmp/aa The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locales COLLATE: en_US.utf8 CTYPE: en_US.utf8 MESSAGES: en_US.utf8 MONETARY: de_CH.UTF-8 NUMERIC: de_CH.UTF-8 TIME: en_US.UTF-8 The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. creating directory /var/tmp/aa ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. You can now start the database server using: pg_ctl -D /var/tmp/aa -l logfile start
Btw: You can also use pg_controldata to get the size of the WAL segments:
[email protected]:/home/postgres/ [PGDEV] pg_controldata | grep "Bytes per WAL segment" Bytes per WAL segment: 33554432 [email protected]:/home/postgres/ [PGDEV] echo "33554432/1024/1024" | bc 32
The other commit added the same option to pg_resetwal.
[email protected]:/home/postgres/ [PGDEV] pg_resetwal -D /var/tmp/aa/ --wal-segsize=64 Write-ahead log reset [email protected]:/home/postgres/ [PGDEV] pg_controldata | grep "Bytes per WAL segment" Bytes per WAL segment: 67108864 [email protected]:/home/postgres/ [PGDEV] echo "67108864/1024/1024" | bc 64
So, hopefully this September when PostgreSQL 11 should be released, you do not need to install additional binaries for changing the WAL segment size. This will also allow major upgrades with pg_upgrade to a new cluster with a different WAL segment size. Nice, makes things easier.