PostgreSQL for Production Sysadmins

Part I

Architecture and the Process Model

What a PostgreSQL database cluster is and is not, the one-process-per-connection model, shared memory, the background processes, and the first three instruments an operator reaches for.

7 lessons

Part II

Installation, Packaging and Service Management

Choosing a version against the support calendar, distribution versus upstream packaging, what initdb commits you to permanently, service management across distributions, and multiple clusters on one host.

6 lessons

Part III

Configuration Architecture

The configuration file hierarchy and precedence, settings contexts, proving whether a change needs a reload or a restart, inspecting effective configuration, and the failure modes of ALTER SYSTEM.

6 lessons

Part IV

Connections, Sessions and Pooling

What a connection costs, what max_connections actually reserves, reading pg_stat_activity as an instrument, idle in transaction, connection exhaustion, and what transaction pooling breaks.

7 lessons

Part V

Authentication, Roles and TLS

pg_hba.conf as first-match-wins policy, what each authentication method proves, SCRAM and the MD5 deprecation, the sslmode ladder, role membership and ownership, and superuser blast radius.

7 lessons

Part VI

Storage, Pages and TOAST

The durability chain from COMMIT to platter, relations and forks and segments, the 8 KiB page, free space and visibility maps, TOAST, and how to reason about a storage platform without declaring a winner.

6 lessons

Part VII

MVCC, Transactions and Visibility

Why PostgreSQL keeps old row versions, what UPDATE really does to a page, transaction IDs and snapshots, isolation levels as PostgreSQL implements them, and long-running transactions as an operational hazard.

6 lessons

Part VIII

VACUUM, Autovacuum and Wraparound

What VACUUM reclaims and what it does not, autovacuum thresholds and workers, tuning from evidence, the failure modes behind "autovacuum cannot keep up", transaction ID wraparound, bloat measurement, and ANALYZE.

7 lessons

Part IX

Locks, Blocking and Deadlocks

The lock conflict matrix that decides everything, row versus table locks, finding the blocker with pg_locks and pg_blocking_pids, deadlock detection and evidence, the DDL lock queue, and whether killing a session is safe.

6 lessons

Part X

Query Planning, Indexes and Performance Method

How the planner chooses, reading a plan, the safety boundary of EXPLAIN ANALYZE, estimates that go wrong, B-tree maintenance cost, index bloat and REINDEX CONCURRENTLY, and a methodology that starts with evidence.

8 lessons

Part XI

Memory and Resource Management

The shared versus per-backend memory map, shared_buffers against the page cache, the work_mem trap, maintenance_work_mem, temporary file spilling, and Linux memory pressure with cgroups and the OOM killer.

6 lessons

Part XII

WAL, Checkpoints and Crash Recovery

The write-ahead logging durability contract, segments and LSNs and generation rate, what retains WAL and what releases it, checkpoint cost, crash recovery, and why an immediate shutdown changes what happens next.

7 lessons

Part XIII

Backup, Archiving and Point-in-Time Recovery

Backup success is not recovery capability: logical and physical backups and their real limits, WAL archiving and archive integrity, point-in-time recovery performed against a chosen target, and proving a restore rather than reporting one.

8 lessons

Part XIV

Replication, Slots and Read Replicas

Streaming replication end to end, WAL sender and receiver, building a standby, the four distinct meanings of replication lag, the synchronous trade-off, replication slots and unbounded WAL retention, and what a read replica asks of the application.

8 lessons

Part XV

High Availability, Failover and Disaster Recovery

Why replication alone is not high availability, promotion and timelines, split-brain and fencing, what an HA stack must supply beyond PostgreSQL, client routing, validating a failover, rejoining a former primary, and mapping RPO and RTO to real numbers.

8 lessons

Part XVI

Observability, Logging and Alerting

Production logging and what statement logging exposes, the statistics views that matter and the ones that changed between releases, wait events, pg_stat_statements, correlating database evidence with the operating system, and alerts worth waking someone for.

7 lessons

Part XVII

Capacity, Maintenance and Upgrades

Planning capacity for far more than the current database size, telling five kinds of disk-full apart, the resource cost of planned maintenance, schema change as an operations problem, minor upgrades, the major upgrade options compared, pg_upgrade, and rehearsal.

8 lessons

Part XVIII

Platforms, Corruption and Production Architecture

What a managed service still leaves you, what a container does not solve for a stateful database, PostgreSQL on Kubernetes and on distributed storage, corruption signals and careful response, checksums, and a defensible reference architecture.

8 lessons

Part Labs

Labs

Disposable-container labs covering installation, configuration, authentication and TLS, locking and deadlocks, MVCC and autovacuum, plans and statistics, WAL and checkpoints, backup and point-in-time recovery, replication and promotion, pooling, and a major version upgrade rehearsal.

0 lessons

Part Runbooks

Runbooks

Operational procedures for a database that will not start, connection and authentication failures, blocking sessions and deadlocks, autovacuum and transaction age, disk-full and WAL growth, archive failure, replication lag and failed replicas, safe promotion, backup and restore, point-in-time recovery, upgrades and disaster recovery.

0 lessons

Part Checklists

Checklists

Production readiness reviews for deployment, security hardening, storage, connections and pooling, backup and point-in-time recovery, replication, high availability, monitoring and alerting, capacity, planned maintenance, upgrades, and disaster recovery.

0 lessons

Part Breakfix

Break/Fix Scenarios

Evidence-first diagnosis of startup, access, transaction, vacuum, lock, planner, WAL, archiving, replication, failover, backup, recovery, resource and corruption failures, each reproduced against a real PostgreSQL instance.

0 lessons

Part Capstone

Production Capstone

Build and defend a complete production PostgreSQL architecture: secure deployment, controlled connections, monitored durability, a proven point-in-time recovery, a streaming replica, a validated failover, and twelve injected incidents to survive.

1 lesson

Part Final

Final Assessment

Final theory assessment plus a final practical assessment of an inherited estate carrying realistic availability, durability, capacity, performance and security defects.

0 lessons