PostgreSQL Administration

Section 1: PostgreSQL Architecture and the Version Lifecycle

• The process model: postmaster, backends, checkpointer, WAL writer and autovacuum
• How shared buffers, WAL and checkpoints work together when data is written
• MVCC, tuple versions and why PostgreSQL needs vacuum
• The 5-year support policy: 9.x through 13 are end of life and PostgreSQL 18 is the current stable release
• The risks of running unsupported versions, and a look at what is coming in PostgreSQL 19

Section 2: Lab: Installing PostgreSQL 18 on Linux and Docker

• Install from the PGDG repository and manage the service with systemd
• Run PostgreSQL 18 in Docker with a volume for persistent data
• initdb, the data directory layout, and data checksums enabled by default in 18
• Separate tablespaces and disks for data and WAL
• Use psql like a professional and manage servers with pgAdmin 4

Section 3: Lab: Configuring postgresql.conf and Sizing for the Hardware

• Configuration precedence, ALTER SYSTEM, and which settings need a restart or only a reload
• shared_buffers, work_mem, maintenance_work_mem and effective_cache_size
• max_wal_size, checkpoint_timeout and their effect on system I/O
• Asynchronous I/O in PostgreSQL 18: io_method with worker and io_uring
• Set up logging so problems can be analyzed from day one

Section 4: Roles, Privileges and Schemas

• Roles, users and groups in PostgreSQL and privilege inheritance
• GRANT and REVOKE at the database, schema, table and column level
• Design schemas around least privilege for applications and teams
• ALTER DEFAULT PRIVILEGES for objects created in the future
• Predefined roles such as pg_read_all_data, pg_monitor and pg_maintain

Section 5: Lab: Authentication, TLS, RLS and Auditing

• Write a tight pg_hba.conf and understand rule matching order
• Move passwords to SCRAM-SHA-256, since MD5 passwords are deprecated in 18
• OAuth authentication, new in PostgreSQL 18, and connecting it to an identity provider
• Enable TLS for connections and enforce it with hostssl
• Row-Level Security to separate data per user, and audit logging with pgAudit

Section 6: Vacuum, Autovacuum and Routine Maintenance

• Dead tuples, bloat and transaction ID wraparound
• Tune autovacuum at the server level and per table for write-heavy tables
• ANALYZE and the statistics the query planner relies on
• Detect and reduce table and index bloat
• REINDEX CONCURRENTLY and maintenance that does not lock out users

Section 7: Lab: Logical and Physical Backup

• pg_dump and pg_restore: file formats, parallel jobs and selective restore
• pg_dumpall for roles and global objects
• pg_basebackup for backing up the whole cluster
• Incremental backup and combining backups with pg_combinebackup
• Choose a backup method that fits the data size and the organization's RPO/RTO

Section 8: Lab: WAL Archiving, PITR and pgBackRest

• Set up WAL archiving and verify the archive is complete and continuous
• Restore to a chosen moment with point-in-time recovery
• pgBackRest: configure a repository and run full, differential and incremental backups
• Define retention and keep backups off the database server
• Run scheduled restore tests to prove the backups actually work

Section 9: Lab: Monitoring and Performance Analysis

• pg_stat_activity, pg_stat_database and pg_locks for the current state
• Find resource-heavy queries with pg_stat_statements
• Inspect I/O load with pg_stat_io and read plans with EXPLAIN ANALYZE
• Capture plans of slow queries automatically with auto_explain
• Build dashboards with postgres_exporter, Prometheus and Grafana

Section 10: Lab: Connection Management with PgBouncer

• The cost of a PostgreSQL connection and the problems with too many open connections
• Install and configure PgBouncer in front of the database
• Choose a pooling mode: session, transaction or statement
• Limitations of transaction mode and adapting applications to it

Section 11: Lab: Streaming Replication

• Build a standby with pg_basebackup and enable hot standby for read queries
• Synchronous versus asynchronous replication and the trade-off between latency and data loss
• Replication slots: the benefits, and the risk of WAL piling up until the disk fills
• Track replication lag and promote a standby manually

Section 12: Logical Replication

• Publications and subscriptions for replicating selected tables or across versions
• Failover slots in PostgreSQL 17 so subscribers keep working after a failover
• Convert a physical standby into a logical subscriber with pg_createsubscriber
• Limitations around DDL and sequences, and verifying data consistency

Section 13: Lab: High Availability with Patroni

• The Patroni, etcd and HAProxy architecture and the role of each part
• Build a 3-node cluster and manage it with patronictl
• Rehearse a planned switchover and a failover when the primary goes down
• Prevent split-brain and rejoin the old primary to the cluster

Section 14: Major Version Upgrades with Minimal Downtime

• pg_upgrade in --link mode and the new --swap mode in 18, with pre-checks using --check
• PostgreSQL 18 keeps planner statistics after pg_upgrade, so the system recovers faster
• Upgrade through logical replication to cut downtime to the switchover window
• Plan upgrades from old releases such as 9.x, test extensions and prepare a rollback plan

Section 15: PostgreSQL on Kubernetes and Managed Services

• CloudNativePG: the operator, the Cluster resource, backup and failover on Kubernetes
• AWS RDS and Aurora, Azure Database for PostgreSQL and Google Cloud SQL
• What the provider handles and what the DBA still owns
• Criteria for choosing between VMs, Kubernetes and managed services

Section 16: Lab: Capstone 3-Node HA Cluster

• Build a 3-node Patroni cluster with pgBackRest and monitoring
• Simulate a primary failure and verify failover and application reconnection
• Simulate an accidental delete and restore to a point in time before the incident
• Wrap up with a runbook and checklist for daily, monthly and emergency tasks