Infrastructure at your Service

Daniel Westermann

The WAL segment size becomes changeable in PostgreSQL 11

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.

postgres@pgbox:/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:

postgres@pgbox:/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:

postgres@pgbox:/home/postgres/ [PGDEV] pg_controldata | grep "Bytes per WAL segment"
Bytes per WAL segment:                33554432
postgres@pgbox:/home/postgres/ [PGDEV] echo "33554432/1024/1024" | bc
32

The other commit added the same option to pg_resetwal.

postgres@pgbox:/home/postgres/ [PGDEV] pg_resetwal -D /var/tmp/aa/ --wal-segsize=64
Write-ahead log reset
postgres@pgbox:/home/postgres/ [PGDEV] pg_controldata | grep "Bytes per WAL segment"
Bytes per WAL segment:                67108864
postgres@pgbox:/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.

 

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure