Author: Rajasekhar amudala
PostgreSQL DBA Reference
What does initdb do?
Initializes a new PostgreSQL database cluster — setting up everything the server needs before it can start for the first time.
Typical Command
$ /pgbin/pgsql/17.4/bin/initdb \
--pgdata=/pgData/pgsql/17.4 \
--waldir=/pgWal/pgsql/17.4 \
--wal-segsize=128 \
--encoding=UTF8 \
--locale=en_US.UTF-8 \
--data-checksums
--pgdata=/pgData/pgsql/17.4 \
--waldir=/pgWal/pgsql/17.4 \
--wal-segsize=128 \
--encoding=UTF8 \
--locale=en_US.UTF-8 \
--data-checksums
Execution Flow
1
Validates PGDATA directory
Checks the target directory exists, is empty, and has correct
700 permissions owned by the postgres user. Aborts if conditions aren't met.2
Creates directory structure
Builds all subdirectories under PGDATA:
base/, global/, pg_wal/, pg_xact/, and many more. Creates a symlink to --waldir if specified.3
Writes PG_VERSION and config files
Stamps the cluster with the PostgreSQL version. Generates
postgresql.conf, pg_hba.conf, and pg_ident.conf with defaults.4
Creates shared system catalogs
Bootstraps cluster-wide catalogs in
global/ — including pg_database, pg_roles, pg_tablespace, and other system tables.5
Bootstraps template databases
Creates
template0 (clean, read-only), template1 (customizable default template), and the postgres admin database.6
Initializes WAL
Creates the WAL directory (or symlink), writes the first WAL segment at LSN
0/1000000 using the configured segment size.7
Cluster is ready
All done. Start the server with
pg_ctl start -D /pgData/pgsql/17.4 and begin configuration.PGDATA Directory Structure Created
/pgData/pgsql/17.4/
├── base/ # Default tablespace — user databases live here
├── global/ # Cluster-wide tables (pg_database, pg_roles…)
├── pg_wal → /pgWal/… # WAL segments (symlink when --waldir used)
├── pg_xact/ # Transaction commit status
├── pg_multixact/ # Multi-transaction status
├── pg_subtrans/ # Subtransaction status
├── pg_twophase/ # Prepared transactions (2PC)
├── pg_replslot/ # Replication slots
├── pg_logical/ # Logical replication state
├── pg_tblspc/ # Symlinks to additional tablespaces
├── pg_stat/ # Persistent statistics files
├── pg_stat_tmp/ # Temporary statistics
├── pg_snapshots/ # Exported transaction snapshots
├── pg_commit_ts/ # Commit timestamp data
├── pg_notify/ # LISTEN/NOTIFY state
├── postgresql.conf # Main server configuration
├── pg_hba.conf # Client authentication rules
├── pg_ident.conf # OS → DB user identity mapping
└── PG_VERSION # Cluster version marker (e.g. "17")
├── base/ # Default tablespace — user databases live here
├── global/ # Cluster-wide tables (pg_database, pg_roles…)
├── pg_wal → /pgWal/… # WAL segments (symlink when --waldir used)
├── pg_xact/ # Transaction commit status
├── pg_multixact/ # Multi-transaction status
├── pg_subtrans/ # Subtransaction status
├── pg_twophase/ # Prepared transactions (2PC)
├── pg_replslot/ # Replication slots
├── pg_logical/ # Logical replication state
├── pg_tblspc/ # Symlinks to additional tablespaces
├── pg_stat/ # Persistent statistics files
├── pg_stat_tmp/ # Temporary statistics
├── pg_snapshots/ # Exported transaction snapshots
├── pg_commit_ts/ # Commit timestamp data
├── pg_notify/ # LISTEN/NOTIFY state
├── postgresql.conf # Main server configuration
├── pg_hba.conf # Client authentication rules
├── pg_ident.conf # OS → DB user identity mapping
└── PG_VERSION # Cluster version marker (e.g. "17")
System Databases Created
| Database | OID | Purpose |
|---|---|---|
| template0 | 4 | Clean, read-only pristine template. Never modified. Used for restores and encoding changes. |
| template1 | 1 | Default template for CREATE DATABASE. Can be customized with extensions, schemas, etc. |
| postgres | 5 | Default admin database. Used for initial connections and DBA work. |
Configuration Files Generated
| File | Controls |
|---|---|
| postgresql.conf | Server parameters — memory, connections, WAL settings, checkpoints, autovacuum, logging, etc. |
| pg_hba.conf | Client authentication — who can connect, from which hosts, using which method (scram-sha-256, md5, trust, peer, etc.) |
| pg_ident.conf | OS user → database user identity mapping for ident and peer authentication methods. |
Immutable Properties — Set Once at initdb
⚠
Cannot be changed without reinitializing the cluster
These properties are baked into the cluster at
initdb time. Changing them requires a full pg_dumpall + initdb + restore cycle.--wal-segsize
WAL segment size. Default: 16 MB. Powers of 2 only (1–1024 MB). Larger = fewer files, better for high-throughput systems.
--data-checksums
Enables page-level checksums to detect I/O corruption. Strongly recommended for production. Small CPU overhead.
--encoding
Character encoding for template0 (e.g. UTF8). All databases default to this unless overridden at CREATE DATABASE.
--locale
Locale for collation, character classification, number formatting (e.g. en_US.UTF-8). Affects sort order of text.
Block size
Compile-time default: 8 KB per page. Affects storage layout and I/O granularity. Cannot be changed post-compile.
Superuser role
The OS user running initdb becomes the database superuser. Typically
postgres.What Comes Next
ℹ
After initdb — start and configure
Once initdb completes, start the cluster with
pg_ctl start -D /pgData/pgsql/17.4 -l /var/log/postgresql/startup.log, then tune postgresql.conf for your workload (memory, connections, WAL archiving, etc.).| Next Step | Command / File |
|---|---|
| Start the server | pg_ctl start -D /pgData/pgsql/17.4 |
| Connect as superuser | psql -U postgres |
| Configure memory / connections | postgresql.conf |
| Configure client access | pg_hba.conf |
| Enable WAL archiving | archive_mode = on, archive_command |
| Take a base backup | pg_basebackup -D /pg_Backup/... |