Skip to main content
RunBook Academy

← All assessments

Final assessment · expert · ~180 min · pass ≥ 80%

PostgreSQL for Production Sysadmins — Final Practical Assessment

Objectives

  • Audit an unfamiliar PostgreSQL estate from its own evidence rather than from its documentation
  • Distinguish a defect that is costing something now from one that will cost something later
  • Rank findings by consequence and likelihood rather than by how easy they are to fix
  • Produce remediations whose blast radius and rollback are stated before they are applied
  • Identify, for each defect, the monitoring gap that let it persist

9 graded questions · last verified 2026-08-28

Assessment questions

Objective questions are scored automatically. Scenario answers use a second-stage rubric for evidence, remediation, and rollback.

Knowledge check · 9 questions

  1. Q1. Audit the backup and recovery capability of this estate. State the evidence you would gather, what you would remediate, and what you would roll back if a remediation went wrong.

    The nightly backup job has reported success for eleven months. Its script contains: pg_dump -Fc -d orders | tee /var/log/backup.log if [ $? -ne 0 ]; then alert "backup failed"; fi There is no record of a restore ever having been performed. The wiki page states a recovery time objective of four hours and a recovery point objective of fifteen minutes. pg_stat_archiver shows last_archived_time from six days ago and failed_count of 41,882.

  2. Q2. Audit the availability and failover capability. State the evidence, the remediation, and the rollback.

    Two standbys are configured. pg_stat_replication on the primary shows one row, application_name "walreceiver", streaming. The second standby's host is up; its primary_conninfo names 10.12.4.7, an address that was renumbered nine weeks ago. The lag dashboard has shown a flat zero for both standbys throughout. synchronous_standby_names contains a single name. No failover has been rehearsed. The failover automation promotes on a failed health check.

  3. Q3. Audit the security posture. State the evidence, the remediation, and the rollback.

    pg_hba_file_rules shows trust on 127.0.0.1/32 and ::1/128 and host all all 0.0.0.0/0 scram-sha-256. SHOW ssl returns on for the primary and off for the second standby, which serves reporting traffic. No application connection string sets sslmode. Four roles have rolvaliduntil in the past. The application role holds CREATEDB and is used by three separate services. A previous engineer's personal role still exists with LOGIN and SUPERUSER.

  4. Q4. Audit the maintenance and capacity position. State the evidence, the remediation, and the rollback.

    The largest table is 1.4 TB and has grown from 400 GB in fourteen months while its row count grew 30 percent. autovacuum_count for it is in the hundreds and last_autovacuum is recent. n_dead_tup is 1.1 billion and has never fallen. The server log contains "canceling autovacuum task" at 02:14 every night. age(datfrozenxid) for the database is 1,204,551,232 and rising. An inactive logical replication slot named debezium_orders was created eleven months ago.

  5. Q5. Audit the connection and performance configuration ahead of the workload tripling. State the evidence, the remediation, and the rollback.

    max_connections is 900, raised from 500 nine months ago and from 200 the year before. Forty application instances each run a pool with a minimum and maximum of 25. work_mem is 256 MB cluster-wide on a 64 GB host with shared_buffers at 16 GB. PgBouncer runs in transaction mode with default_pool_size 200. pg_stat_activity shows 812 sessions idle, 640 of them for over twenty minutes. Average query duration is 1.8 ms.

  6. Q6. Name your first three remediations for this estate, in order, and justify the ordering in terms of consequence and likelihood rather than effort.

  7. Q7. For any three findings in this estate, state the single query or log line that would have surfaced each of them, and say why nobody was running it.

  8. Q8. The estate is about to take on three times its current workload. Name two findings whose consequence changes materially at that scale, and explain what changes.

  9. Q9. The wiki page states a recovery time objective of four hours and a recovery point objective of fifteen minutes. Explain what this estate can actually deliver, and what it would take to meet the stated numbers.

Passing score: 80%. Answers are checked in this browser.

How to work this assessment

Read the running system first. The wiki page is eleven months old and describes an estate that has been rebuilt since; every disagreement between the document and reality is its own finding.

Work in this order, because it is the order of consequence:

  1. Durability. Can this estate recover? From what point? How long would it take?
  2. Availability. What survives the loss of a host, and has anybody demonstrated it?
  3. Security. What can reach the cluster, who can authenticate, and what does the wire carry?
  4. Maintenance. What is accumulating, and when does it stop being survivable?
  5. Performance and capacity. What breaks at three times the load?

What each finding must contain

Evidence. The command and the output. “The backups are probably not tested” is a suspicion; the absence of any restore record plus a script reading $? after a pipe is a finding.

Consequence, and when. A defect that costs nothing until a specific event is ranked by the likelihood of that event. An abandoned replication slot costs nothing today and ends in a cluster that will not assign transaction IDs.

The monitoring gap. What would have surfaced this. Almost every defect here is visible in one query or one log line that nobody is running — and in several cases the metric that exists is worse than none, because it reports health it has not observed.

The three-item constraint

You will be asked for your first three remediations. That is not an artificial limit; it is what you will be given time for.

Marking

Each scenario is marked on evidence, remediation and rollback — and the rollback is the section most often left empty. A remediation whose reversal you cannot state has not been thought through, and several of the remediations available here are irreversible: dropping a slot, rotating a credential, terminating a session’s work.

The short-answer questions are marked on reasoning rather than on recall. There is more than one defensible ordering for the third remediation, and saying why you chose yours is worth more than choosing the one this assessment happens to prefer.