1. Courses
  2. Database Administration with PostgreSQL
  3. Capstone

Capstone project for Database Administration with PostgreSQL

Make Mangrove's order database production-grade

Mangrove Market's order database (PostgreSQL 16, about 800 GB, growing about 1.5 GB a day) runs on a single server. Nightly pg_dump files exist but nobody has ever restored one, there is no replica, the app connects as a superuser, and a few queries time out at the evening peak, when orders run at four times normal on festival days. Head office has set the targets: lose at most 5 minutes of orders (RPO) and be back within 1 hour (RTO) after losing the server. Build the evidence that the database meets them. Use a local PostgreSQL (a virtual machine or containers are fine) with a scaled-down copy of the order schema and generated data, and keep a log of every command and result.

What you hand in

  1. A backup and restore runbook with physical backups, WAL archiving and point-in-time recovery, plus the log of one timed restore drill to a chosen moment
  2. A replica setup with lag monitoring and a failover runbook, with the log of one tested switchover or failover
  3. A tuning report: the top queries from pg_stat_statements, each change made with its reason, and before and after numbers
  4. A role and access matrix with the SQL that creates it (owner, migration, app, read-only and audit roles, plus any row-level security policies)
  5. An upgrade and capacity note: the path to the next major version, the disk forecast and the alerts with their runbook links

Check your work

Tick each item when your project meets it. Be strict: this is the rubric the reviewer uses.

  • The RPO and RTO are stated, the design can meet them (WAL archiving, not only nightly dumps), and the timed restore drill shows they were met
  • Backups are monitored: last good backup age, archive failures and restore drills on a schedule, with who is alerted
  • Failover was tested, not only written down, including how clients find the new primary and how split brain is avoided
  • Each tuning change is justified by data from pg_stat_statements or EXPLAIN (ANALYZE, BUFFERS), with before and after numbers
  • No application connects as a superuser; each role has only the privileges it needs, and default privileges cover new tables
  • The upgrade path, the disk forecast and the alert runbooks are concrete enough for someone else on call to follow

0 of 6 checked

Model answer

The model answer opens once your capstone passes the review, so you can compare your work with an outline of a strong submission.

Hand in your capstone

Your links are checked automatically, then the work is scored against the rubric above, item by item, with comments. A pass counts toward the course certificate and shows on your profile.

Loading…