skip to content

When you run initdb for a new PostgreSQL 18 cluster, which options deserve a deliberate choice, and what changed about data checksums?

level: middleimportance: should knowfreq 40%

answer

  1. decisions frozen at creation
  2. the authentication default is too open
  3. 18 flipped a default
  4. pg_upgrade wants a match

basics

~10 s

Choose authentication (-A, --auth-local, --auth-host; the default is trust), the superuser and password, WAL location and segment size, and group access. Since PostgreSQL 18, initdb enables data checksums by default; --no-data-checksums opts out.

solid answer

~40 s

`initdb` fixes a cluster's starting point, so a few options deserve a decision. Authentication: without `-A`, `--auth-local` or `--auth-host` it writes `trust` into `pg_hba.conf` and prints a warning; `--auth-local=peer --auth-host=scram-sha-256` plus `--pwfile` or `-W` is a sane start. Layout: `-X/--waldir` puts `pg_wal` on another volume, `--wal-segsize` sets the segment size (default 16 MB), and `-g` allows group read for backups. In **PostgreSQL 18** data checksums are **on by default**; `--no-data-checksums` opts out. That matters for upgrades: `pg_upgrade` requires matching checksum settings, so moving a non-checksum 17 cluster to 18 means creating the new cluster with `--no-data-checksums` or enabling checksums on the old one first. `-N/--no-sync` is for tests only.

code

bash · 10 lines
bash
# as the OS user that will own the server, never root
initdb -D /srv/pg/inventory \
  --auth-local=peer --auth-host=scram-sha-256 \
  -U postgres --pwfile=/etc/pg-secrets/superuser.pw \
  --waldir=/srv/pgwal/inventory \
  --allow-group-access
# PostgreSQL 18 prints: Data page checksums are enabled.

# a target cluster for pg_upgrade from a non-checksum 17 cluster
initdb -D /srv/pg/inventory-18 --no-data-checksums

go deeper

for a junior

Know that initdb creates the cluster once, must run as the owning OS user, and that PostgreSQL 18 turns data checksums on by default.

for a middle

Walk through the options that matter: authentication defaults to trust, superuser password, waldir, wal-segsize, group access, and the checksum flag in both directions.

for a senior

Connect initdb choices to later operations: pg_upgrade's checksum match, pg_wal on its own volume, and which values only an offline tool can change afterwards.

for a principal

Turn cluster creation into a reviewed template so every environment gets the same checksums, authentication and WAL layout, and upgrades never discover a mismatch on the night.

## initdb fixes the cluster's starting point `initdb` creates a PostgreSQL **database cluster**: one data directory, its shared catalogs, the `postgres`, `template1` and `template0` databases, and default configuration files. Several choices made here are cheap at creation and expensive later, so in an interview "just run initdb" is a weak answer. It must run as the operating-system user that will own the server, never as root (it refuses), and it refuses a directory that already contains files. ## The options worth a decision | Option | What it sets | Default | |---|---|---| | `-A`, `--auth-local`, `--auth-host` | Methods written into `pg_hba.conf` for local-socket and TCP lines | `trust`, with a warning | | `-U` | Name of the bootstrap superuser | the OS user running `initdb` | | `-W` / `--pwfile` | Password for that superuser | none | | `-k` / `--no-data-checksums` | Page checksums for every object in every database | on (PostgreSQL 18) | | `-X` / `--waldir` | Where `pg_wal` lives; it becomes a symbolic link | inside the data directory | | `--wal-segsize` | Size of each WAL file, a power of 2 from 1 to 1024 MB | 16 MB | | `-g` / `--allow-group-access` | Group read access (`0750` dirs, `0640` files) | owner only (`0700`/`0600`) | | `-c name=value` | A server setting forced during `initdb` and written into `postgresql.conf` | none | | `-E`, `--locale`, `--locale-provider` | Encoding and locale defaults for the template databases | taken from the environment | Locale and collation providers are a subject of their own; the point here is only that `initdb` sets the defaults every later database copies. ## Data checksums: on by default since 18 Up to PostgreSQL 17, `initdb` created clusters **without** data checksums unless you passed `-k` (`--data-checksums`). **PostgreSQL 18 flipped that default**: a plain `initdb` enables them and prints `Data page checksums are enabled.`, and the new option `--no-data-checksums` turns them off. What checksums detect and what they cost belong to the WAL-and-durability topic; the lifecycle consequences are these: - **Upgrades must match.** `pg_upgrade` refuses to combine a non-checksum old cluster with a checksum-enabled new one and stops with `old cluster does not use data checksums but the new one does`. Two fixes: create the new 18 cluster with `--no-data-checksums`, or enable checksums on the old cluster first with `pg_checksums --enable`, which needs the cluster shut down cleanly and can take a long time on a large cluster. - **Automation that pinned `-k`** keeps working; automation that relied on checksums being off now gets them. - **Checking a cluster**: `SHOW data_checksums;` returns `on` or `off`, and `pg_controldata` prints a non-zero `Data page checksum version` when they are on. ## Authentication: do not ship trust Without an authentication option, `initdb` writes `trust` for local connections and warns `enabling "trust" authentication for local connections`. `trust` lets any local operating-system user connect as any database role, including the superuser. Good practice: 1. Use `--auth-local=peer` so local socket logins map to the matching OS user. 2. Use `--auth-host=scram-sha-256` for TCP connections. 3. Give the bootstrap superuser a password with `--pwfile` (first line of a file) or `-W` (interactive prompt), otherwise password logins for it are impossible. Pick `-U` deliberately too. The bootstrap superuser it names is the role with ID 1, and PostgreSQL will not let anyone remove its `SUPERUSER` attribute later, so it is the account of last resort for the whole cluster. ## Options for tests only - `-N` / `--no-sync` returns without waiting for files to reach disk; an operating-system crash soon after can leave the new data directory corrupt. Fine for CI, wrong for production. - `--no-sync-data-files` skips syncing the database directories and is meant for tools that sync them separately. - `--sync-method=syncfs` (Linux) syncs whole file systems instead of walking files; `fsync` is the default. ## What is hard to change later Some values are reported by read-only settings because they are baked into the cluster: `data_checksums`, `wal_segment_size` and `data_directory_mode` cannot be changed with `ALTER SYSTEM`. Changing the segment size afterwards means an offline `pg_resetwal --wal-segsize`; changing checksums means an offline `pg_checksums`. Authentication, by contrast, is just `pg_hba.conf` and a reload. Put the effort where the cost of a mistake is highest.

  • pg_upgrade from a 17 cluster without checksums to a freshly initialised 18 cluster fails. What is the message and what are your two fixes?
    It stops with `old cluster does not use data checksums but the new one does`. Either re-run `initdb` for the 18 cluster with `--no-data-checksums`, or stop the 17 cluster cleanly and run `pg_checksums --enable` on it before upgrading; the latter rewrites the data files and can take a long time on a large cluster.
  • How do you tell whether an existing cluster has data checksums?
    Connected, run `SHOW data_checksums;`, a read-only setting that reports `on` or `off`. Offline, `pg_controldata` shows `Data page checksum version`, which is non-zero when checksums are enabled.
  • Why choose --wal-segsize at initdb rather than later?
    It sets the size of every WAL file for the cluster (default 16 MB, a power of 2 up to 1024 MB) and is exposed only as the read-only `wal_segment_size`. Larger segments mean fewer files for archiving and high-WAL systems. Changing it later requires an offline `pg_resetwal --wal-segsize`, so decide at creation.

  • initdb writes scram-sha-256 into pg_hba.conf unless told otherwise.
  • PostgreSQL 18 still leaves data checksums off unless you pass -k.
  • --no-sync is a safe way to speed up a production initdb.
  • initdb can run as root and drop privileges afterwards.
  • The WAL segment size can be changed with ALTER SYSTEM and a restart.

Assumes PostgreSQL 18, where initdb enables data checksums by default; in 17 and earlier they were off unless -k was given.