Advanced Database Operations: Recovery, Security and Performance

ULC-001 taught you to run an instance and ULC-002 taught you to query one. Neither made you the person who is called when the database is the reason the business has stopped. This course does. You inherit **Ardenfield Freight**: one PostgreSQL 16 cluster carrying three tenants' consignments, with no WAL archiving, an application role that is a superuser, a `trust` line somebody added to pg_hba.conf "for now", a slow-query list nobody has looked at, and no monitoring. Over ten lessons you turn it into a service that can be operated: a backup strategy derived from stated RPO and RTO targets, a point-in-time recovery drilled and timed until the recovered rows are on the screen, SCRAM authentication and a least-privilege role hierarchy, row-level security that the server enforces even against the table's owner, pgaudit on the statements that matter, an index set designed from `EXPLAIN (ANALYZE, BUFFERS)` and measured before and after, autovacuum tuned for the hot table, `pg_stat_statements` and lock views behind two operational views, and a streaming standby that you promote in a controlled drill and then rebuild. Two exercises start from a fault rather than a task. A table stops being maintained and its statistics tell you why before you touch it. Three sessions pile up on one relation and the query that is suffering turns out not to be blocked by the session that is at fault. You work both the way an on-call engineer works: observe, form one hypothesis, test it, fix, verify, write it down. Every configuration change is worked through a change record with a rollback that exists, and every claim about recovery is backed by output you captured. Each exercise runs on PostgreSQL 16 on your own lab machine; the SQL Server equivalents are taught beside them as documented concepts and T-SQL, so an ULC-001 graduate can carry each skill across. The final project is an engagement: **Westmarch Utilities**, three districts sharing one database and one superuser, to be delivered back as an operable service with a runbook and evidence that a recovery actually worked.