What initdb commits you to, permanently

initdb runs once, takes a few seconds, and makes decisions that outlive every other choice about the cluster. Some of them can be revisited with a restart. Some require taking the cluster offline. And some cannot be changed at all without creating a new cluster and moving the data into it — which, for a production database, means a migration with downtime rather than a configuration change.

Nothing warns you at the time. initdb succeeds, the cluster works, and the consequence arrives months later.

The three classes of decision

The bottom three are the subject of this lesson.

Data checksums: the PostgreSQL 18 change

Data checksums attach a checksum to every data page, verified on read. They turn silent corruption into a loud error, which is the difference between discovering storage corruption when it happens and discovering it when a customer reports wrong data.

PostgreSQL 18 enables them by default. Every earlier release did not. This is one of the most consequential default changes in the release and it is easy to miss.

The proof is in the tool’s own help text. PostgreSQL 18’s initdb carries a flag to disable checksums, which exists only because the default flipped:

$ docker run --rm postgres:18 initdb --help | grep data-checksums

-- PostgreSQL 18
-k, --data-checksums      use data page checksums
    --no-data-checksums   do not use data page checksums

-- PostgreSQL 16, for comparison
-k, --data-checksums      use data page checksums

Confirm the state of any cluster in two ways:

$ psql -U postgres -c 'SHOW data_checksums'

 data_checksums
----------------
on
(1 row)

$ pg_controldata "$PGDATA" | grep -i checksum
Data page checksum version:           1

Unlike encoding and locale, checksums can be changed after the fact, with the cluster shut down cleanly:

# The cluster must be stopped. pg_checksums refuses to run otherwise.
pg_checksums --enable --progress -D "$PGDATA"

This rewrites every page in the cluster to add its checksum, so it takes time proportional to the data size and generates a great deal of I/O. It is a maintenance-window operation on a large database, not a quick fix, and Part XVII treats it as one.

Encoding and locale: the decisions with no undo

$ psql -U postgres -c 'SELECT datname, pg_encoding_to_char(encoding) AS encoding, datcollate, datctype, datlocprovider FROM pg_database ORDER BY datname'

  datname  | encoding | datcollate |  datctype  | datlocprovider
-----------+----------+------------+------------+----------------
postgres  | UTF8     | en_US.utf8 | en_US.utf8 | c
shop      | UTF8     | en_US.utf8 | en_US.utf8 | c
template0 | UTF8     | en_US.utf8 | en_US.utf8 | c
template1 | UTF8     | en_US.utf8 | en_US.utf8 | c
(4 rows)

Encoding decides which byte sequences are valid text. UTF8 is the right answer for essentially every new deployment, and a cluster created with a legacy single-byte encoding cannot represent characters outside it. Changing it means dumping the data, creating a new database with the correct encoding, and loading it back — with whatever transformation the change requires.

Collation — datcollate — decides sort order. It is the one that causes production incidents, because it does not merely affect ORDER BY output. B-tree indexes on text columns are stored in collation order. An index is a sorted structure, and the sort it was built with is the collation in force at the time.

datlocprovider names which library implements the collation: c for the C library, i for ICU, b for PostgreSQL’s own builtin provider. The value above is c, meaning the operating system’s glibc decides sort order for these databases.

Choosing at initdb time

# An explicit, deliberate initialisation rather than accepting defaults.
initdb \
  --pgdata="$PGDATA" \
  --encoding=UTF8 \
  --locale-provider=icu \
  --icu-locale=en-US \
  --data-checksums \
  --auth-local=peer \
  --auth-host=scram-sha-256

Each of those is a decision worth stating explicitly even when it matches the default, because an explicit value in a provisioning script is a decision somebody made and can be reviewed, while an omitted flag is a default that may change between versions — as checksums just did.

--auth-local and --auth-host seed the initial pg_hba.conf. They are not permanent, since the file can be edited afterwards, but starting from scram-sha-256 rather than trust avoids a window in which the cluster accepts unauthenticated connections. Part V covers the file properly.

--wal-segsize sets the WAL segment size, defaulting to 16 MB, and it is genuinely permanent. Very high-write clusters sometimes use a larger segment to reduce file churn. It is not a decision to make speculatively, and Part XII gives the reasoning.

Auditing an existing cluster

# The permanent per-database settings
psql -U postgres -c \
  "SELECT datname, pg_encoding_to_char(encoding) AS encoding,
          datcollate, datctype, datlocprovider
     FROM pg_database ORDER BY datname"

# The permanent cluster-wide settings
pg_controldata "$PGDATA" | grep -iE 'checksum|segment size|catalog version|state'

# Which collations exist, and which provider each uses
psql -U postgres -c \
  "SELECT collname, collprovider, collversion
     FROM pg_collation WHERE collname NOT LIKE 'pg_%' LIMIT 10"

pg_collation.collversion is worth knowing about. PostgreSQL records the collation library version an index was built against where the provider supplies one, and warns when it detects a mismatch. It is a partial defence rather than a complete one — it does not cover every provider and every case — but a warning about a collation version mismatch in the log is never noise, and Part XVIII treats it as a corruption-class signal.

Production discipline

1. State every initdb option explicitly in provisioning code, even where it matches the current default. Defaults change between versions, as checksums did in 18.
2. Decide the collation provider deliberately. libc ties sort order to the operating system’s C library version; ICU and the builtin provider do not.
3. Reindex text indexes after any operating system upgrade beneath a libc-collated cluster, and after moving a data directory between hosts. The failure is silent and returns wrong results rather than errors.
4. Check checksum agreement before a major upgrade. pg_upgrade requires the source and target to match, and the 18 default makes a mismatch likely for anything upgraded from 17 or earlier.
5. Read pg_controldata on any cluster you inherit. It reports the permanent decisions, and it works on a stopped cluster.

Cross-course references

• Linux for Production Sysadmins — Part XI (Packages) and Part LXV (Kernel upgrades) cover the operating system upgrades that change glibc beneath a database, which is the trigger for the collation hazard above.
• Ansible for Production Sysadmins — Part XII (Idempotency) covers why a provisioning role should state these values rather than rely on defaults, since initdb is not idempotent and runs once.
• Ceph & Distributed Storage — Part CXVIII (Data integrity) covers the storage-layer checksums that complement the page checksums discussed here.

Quiz