Advanced PostgreSQL for Enterprise

Section 1: Processes, Memory and the Write-Ahead Log

• The postmaster, backend processes and background workers (checkpointer, background writer, WAL writer, autovacuum launcher)
• Shared buffers, work_mem and maintenance_work_mem, and how each uses server resources
• How WAL, checkpoints and full page writes work, and how they tie into backup and replication
• The PostgreSQL versioning policy: one major release a year and 5 years of support

Section 2: MVCC, Transaction Isolation and Locking

• Tuple visibility (xmin/xmax) and why PostgreSQL needs VACUUM
• Read Committed, Repeatable Read and Serializable, and their effect on applications
• Conflicting row and table lock modes, and inspecting them with pg_locks
• Lab: demonstrate lock waits and anomalies at each isolation level

Section 3: Lab: Linux Essentials for Database Work

• Manage services with systemd and read logs with journalctl
• Check disk space and I/O with df, du and iostat, and inspect memory and processes
• Users, file permissions and secure SSH key access
• Lab: health-check an Ubuntu 24.04 server before installing the database

Section 4: Lab: Installing and Tuning PostgreSQL 17

• Install from the PGDG repository on Ubuntu 24.04 and use the postgresql-common tools (pg_lsclusters, pg_ctlcluster)
• Enable data checksums when the cluster is created, and why you should
• postgresql.conf, pg_hba.conf and ALTER SYSTEM, tuning memory, checkpoint and autovacuum settings to the server size
• Managing extensions and what to watch for during upgrades
• Lab: tune the primary node against a production checklist

Section 5: Workshop Day 1: Connections, Privileges and PgBouncer

• Why a high max_connections is dangerous, and PgBouncer session, transaction and statement pooling
• Role hierarchies with least privilege, and the pg_maintain role added in PostgreSQL 17
• SCRAM-SHA-256 authentication and enabling SSL/TLS
• Workshop: create application, read-only and admin roles for NovaMart, enable TLS and deploy PgBouncer
• Measure with pgbench before and after connection pooling

Section 6: EXPLAIN ANALYZE and Planner Statistics

• Read EXPLAIN (ANALYZE, BUFFERS), including the SERIALIZE and MEMORY options added in PostgreSQL 17
• Seq scan, index scan, bitmap scan, nested loop, hash join and merge join
• Find bad row estimates, and use ANALYZE and extended statistics
• Lab: read the plan of an order report query and find why it is slow

Section 7: Lab: Index Types and Slow Query Analysis

• B-tree, GIN, GiST, BRIN, partial indexes and covering indexes (INCLUDE)
• pg_stat_statements, auto_explain and log_min_duration_statement
• A process from spotting the problem through the fix to proving the result with numbers
• Lab: tune 5 slow NovaMart queries until they meet the target

Section 8: Lab: Observability with Prometheus and Grafana

• Key pg_stat views: pg_stat_activity, pg_stat_database, pg_stat_io, pg_stat_checkpointer and pg_locks
• Install postgres_exporter and node_exporter and connect them to Prometheus
• Import and customise Grafana dashboards for the database
• How to connect to the Prometheus and Grafana stack your team already runs
• Lab: compare results before and after query tuning on the dashboard

Section 9: VACUUM, Bloat and Transaction ID Wraparound

• Per-table autovacuum tuning, and the new VACUUM memory management in PostgreSQL 17 that uses less memory
• Measure bloat and remove it with pg_repack without long table locks
• Monitor age(datfrozenxid), use REINDEX CONCURRENTLY and schedule maintenance
• Lab: create bloat on the orders table and measure before and after the fix

Section 10: Workshop Day 2: Partitioning and Data Archiving

• Range, list and hash partitioning, and partition pruning
• Use pg_partman to create partitions ahead of time automatically
• DETACH PARTITION CONCURRENTLY and moving old data to separate storage
• Workshop: convert the NovaMart orders table into a monthly partitioned table and follow the result in Grafana

Section 11: VM Snapshots versus Database Backups

• Limits of VM snapshots: data consistency, no restore to an arbitrary point in time, and an RPO tied to the snapshot schedule
• Using VM snapshots and database backups so that they complement each other
• Deriving RPO/RTO from business requirements
• A conceptual comparison of pgBackRest and Barman

Section 12: Lab: Logical and Physical Backups and PITR

• pg_dump / pg_restore and where they still fit
• Incremental pg_basebackup, added in PostgreSQL 17, with pg_combinebackup
• Set up WAL archiving and point-in-time recovery with recovery_target_time and timelines
• Lab: take an incremental backup, combine it and restore it on a test server

Section 13: Lab: pgBackRest and DR Drills

• Full, differential and incremental backups, parallel backup, compression and encryption
• Retention policies and a repository on S3-compatible storage (MinIO)
• Verify integrity with pgbackrest verify, pg_amcheck and data checksums
• Run a restore drill, measure the real RTO and set a schedule for backup testing
• Lab: configure pgBackRest with MinIO and take a full backup of NovaMart

Section 14: Security and PDPA

• Row-Level Security (RLS) by user or tenant, and its effect on performance
• pgAudit and choosing a sensible audit scope
• Column-level encryption with pgcrypto, and data masking
• A checklist of technical measures for personal data under PDPA

Section 15: Workshop Day 3: Recovering from an Accidental Delete

• The instructor simulates an accidental delete of NovaMart order data
• Find the time of the incident in the logs and restore with pgBackRest PITR to just before it
• Check data correctness and measure the RTO actually achieved
• Workshop: enable RLS and pgAudit on the customer tables

Section 16: Streaming and Logical Replication

• Asynchronous and synchronous streaming replication and their effect on latency and RPO
• Replication slots, the risk of a full disk from a stale slot, and max_slot_wal_keep_size
• Logical replication and logical slot failover, added in PostgreSQL 17
• Lab: build a replica on pg2 and measure replication lag

Section 17: Lab: Automatic Failover with Patroni and etcd

• DCS architecture, the leader lock and configuring patroni.yml
• Set up a 3-node etcd cluster on pg1, pg2 and infra
• Use patronictl for automatic failover and planned switchover
• Change PostgreSQL settings with patronictl edit-config and rebuild a replica with Patroni bootstrap
• Lab: bring the existing primary under Patroni without losing data

Section 18: Load Balancing and Preventing Split-Brain

• HAProxy health checks through the Patroni REST API, with separate read-write and read-only ports
• Running PgBouncer with HAProxy and what it means for application code
• etcd quorum and rehearsing network partitions with ufw on Ubuntu
• Lab: cut one node off the network and confirm that no split-brain occurs

Section 19: Lab: Alerting and Log Analysis

• Key metrics: replication lag, connection saturation, transaction ID age, disk usage and long-running transactions
• Write alert rules in Prometheus and route notifications through Alertmanager
• Configure logging and analyse logs with pgBadger
• Lab: create an HA alert rule set and test that it really fires

Section 20: Workshop Day 4: The NovaMart HA Cluster

• Grow the system into Patroni (primary on pg1, replica on pg2) with a 3-node etcd cluster
• Put HAProxy and PgBouncer in front of the cluster
• Add a Grafana dashboard for cluster status
• Workshop: stop the primary while pgbench is running, measure failover time and check that alerts fire correctly
• Record failover time, replication lag and the measured impact on the application in the workbook

Section 21: Lab: Upgrading and Migrating from 17 to 18

• Minor upgrades and the release cycle: PostgreSQL 17 is supported until November 2029 and 18 until November 2030
• Major upgrade with pg_upgrade in a Patroni cluster, and PostgreSQL 18 changes to know, such as data checksums on by default and statistics carried over
• Minimal-downtime upgrades with logical replication and pg_createsubscriber
• Sync the data, verify correctness, cut over and plan the rollback
• Lab: upgrade the NovaMart cluster to PostgreSQL 18

Section 22: Lab: Locks, Queries and Resource Usage

• Lock contention and deadlocks: pg_locks, pg_blocking_pids, lock_timeout and statement_timeout
• Long-running and idle-in-transaction sessions that hold back VACUUM
• Queries with abnormal resource use, and stopping them safely with pg_cancel_backend and pg_terminate_backend
• Lab: trace a query that locks a table using Grafana and pg_stat_activity

Section 23: Troubleshooting and Runbooks

• A full disk from WAL, logs or a stale replication slot: finding the cause and fixing it
• High replication lag: separating network, disk and long-running queries on the replica
• Data corruption: checking with pg_amcheck and the recovery options (demonstration)
• What a good runbook contains: symptoms, checks, fix steps and escalation criteria
• Write runbooks for 8 common incidents

Section 24: PostgreSQL on Kubernetes (Demonstration)

• The Kubernetes operator idea and a CloudNativePG demo: create a cluster, fail over and back up to object storage
• Running PostgreSQL on VMs compared with Kubernetes, and decision criteria
• Storage, backup and major upgrade considerations when running on Kubernetes
• Approaches for moving databases from VMs to Kubernetes

Section 25: Master Workshop: Game Day

• Learners work in teams of 3 on a chain of incidents on the cluster built over days 1-4
• Simulated incidents: primary failure, high replication lag, a query locking a table, a nearly full disk and an accidental delete
• Detect each one from Grafana and alerts, then fix it with the runbook
• Restore with pgBackRest and report the RPO/RTO actually achieved
• Wrap up the lessons learned and a checklist for applying them to real systems