PostgreSQL for Production Sysadmins — Final Theory Assessment

1. Q1. A cluster PANICs with "could not write to file pg_wal/xlogtemp.61: No space left on device". pg_wal holds 1.4 GB against a max_wal_size of 1 GB, with 88 files marked .ready. What does that combination indicate?

   Archiving is failing, so segments are retained A bug in checkpoint pacing, since the checkpointer is required to keep pg_wal at or below max_wal_size at all times The WAL volume is undersized for max_wal_size and should be grown to twice the configured value plus a margin A replication slot is holding WAL, because a slot is the only mechanism that can retain segments past max_wal_size

2. Q2. During that incident, two suggestions are made in the channel: delete the oldest files in pg_wal, and set fsync = off so the cluster starts. What is the correct characterisation of these?

   Both are acceptable emergency measures provided they are reverted afterwards Deleting WAL is acceptable; disabling fsync is not, because it persists Both convert a recoverable outage into permanent data loss and neither is ever the fix Disabling fsync is acceptable; deleting WAL is not, because segments may still be needed

3. Q3. VACUUM FREEZE runs for four hours on a large table, exits zero, and its VERBOSE output contains no "new relfrozenxid" line. What has happened?

   The vacuum was cancelled by a conflicting lock before it could complete maintenance_work_mem was too small, so the vacuum made multiple index passes and ran out of time The table has no rows old enough to freeze, so there was nothing to do Something is holding the freeze horizon, so vacuum was not permitted to advance relfrozenxid

4. Q4. A nightly report runs 60 queries inside one transaction over six hours, reading only a reporting schema. Autovacuum on unrelated OLTP tables reports "0 removed" with millions "not yet removable". Why are tables the report never touches affected?

   The report holds a snapshot, which sets a cluster-wide floor for vacuum The reporting schema shares a tablespace with the OLTP tables, so their pages compete for the same free space map Autovacuum workers are all occupied by the reporting schema, starving every other table of a worker for the whole window The report holds an ACCESS SHARE lock that conflicts with the SHARE UPDATE EXCLUSIVE lock vacuum takes on every table

5. Q5. An engineer runs VACUUM FULL on a 74 MB table during business hours. Every query on that table stops for eleven minutes, and services that never touch it also time out. The rewrite itself takes 159 ms. What accounts for the eleven minutes?

   VACUUM FULL rebuilds every index serially after the heap rewrite, and that index work dominates the total runtime The table was larger on disk than pgstattuple reported, so the rewrite copied considerably more than was expected An idle in transaction session held the table, and later queries queued behind the pending lock The connection pool exhausted its slots first, which blocked the VACUUM FULL itself until sessions were terminated

6. Q6. work_mem is raised from 4 MB to 256 MB cluster-wide to stop reporting queries spilling to disk. The reports get slower and a backend is later killed with signal 9. What is the most complete explanation?

   The larger work_mem exceeded shared_buffers, leaving the planner with less operating system page cache to work against The reports were slow because of stale statistics on the largest tables, which raising work_mem does not address at all 256 MB is still below the size of the working set, so the queries continued to spill and the change gained nothing Spilling was never the bottleneck, and a larger allowance both changes plans and multiplies worst-case memory

7. Q7. The OOM killer terminates one backend on a busy cluster. What is the effect on the other sessions?

   Only that backend dies, and every other session continues entirely uninterrupted with its transactions intact Other sessions continue running but lose any server-side prepared statements they had created earlier The postmaster terminates every other session and the cluster enters crash recovery Sessions are terminated only where they were sharing a lightweight lock with the backend the kernel chose

8. Q8. Which of these is NOT what raises the risk of an out-of-memory kill on a PostgreSQL host?

   A query building a single array over every row of a large table A large sort with work_mem set far above what the host can supply A high max_connections combined with a large per-role work_mem Many concurrent sessions each running plans with several memory-consuming nodes

9. Q9. A batch job fails four nights running with "password authentication failed". The password is rotated three times and each rotation is verified with psql on the database host, successfully. What is the most likely situation?

   The secrets store and the database have diverged during one of the rotations, and a fourth rotation performed carefully in both places will resolve it The job is connecting to a replica that has not yet received the role change through streaming replication The password is correct and something else is failing, while loopback trust makes every local test pass The job driver uses md5 while the server requires scram-sha-256, so the verifier can never match

10. Q10. An audit finds cleartext PostgreSQL traffic to one replica. TLS was enabled estate-wide eight months ago and signed off. No application has errored. What is the most probable cause?

    The replica was rebuilt with ssl off, and clients using the libpq default downgraded silently The replica certificate expired at some point during those eight months, so every client fell back to an unencrypted connection rather than failing The replica reloaded a corrupt certificate file, and PostgreSQL responded by serving plaintext connections instead of refusing them The audit captured traffic on the streaming replication port, which is never encrypted regardless of the ssl setting

11. Q11. Which sslmode value both encrypts the connection and establishes that the server is the one you meant to reach?

    require verify-ca prefer, when a CA certificate is available verify-full

12. Q12. A cluster refuses a connection with "remaining connection slots are reserved for roles with the SUPERUSER attribute". What does that specific message tell you?

    Every slot including the superuser reserve is gone, so no further connection of any kind will be accepted The connecting role lacks the LOGIN attribute and would be refused regardless of how much capacity remained The ordinary and pg_use_reserved_connections tiers are exhausted; only superuser slots remain max_connections has been set below the number of background worker processes this cluster requires to start

13. Q13. A four-second storage stall is followed by two hours of high latency, with storage healthy from minute one, CPU saturated in user time and iowait under 2 percent. Connections rose from 90 to 465 in thirty seconds. What is happening?

    The storage array is still degraded and is misreporting its own health to the monitoring system A checkpoint storm triggered by the stall is saturating the write path and has not yet subsided A connection storm: the pool grew on latency, and past the knee more connections cost throughput The buffer cache was invalidated by the stall, so every query in the workload is now reading from disk rather than memory

14. Q14. A replication lag alert fires most Sunday mornings and is always found to be a false alarm. It was eventually muted, and a genuine four-hour lag on a Tuesday went unnoticed. What is wrong with the metric?

    It samples too infrequently to catch the short lag excursions that occur during the weekly batch window It reads from the standby, which has no way to see how far ahead the primary has advanced since the last sample It measures time since the last replayed commit, which grows with the clock on an idle primary It uses replay_lsn rather than flush_lsn, so it reports apply lag when receive lag is what matters here

15. Q15. A standby has been disconnected for six weeks and the lag dashboard has shown a flat zero throughout. What is the most likely reason the graph looked healthy?

    The collector was reading from the standby, which reported itself internally consistent The metric returned NULL because the standby has no row in pg_stat_replication, and the monitoring rendered NULL as zero The dashboard was averaging over a window long enough to hide the gap The standby was still receiving WAL through the archive, so it genuinely was not behind

16. Q16. A standby is rebooted for a kernel patch and every write on the primary stops. The primary is up, answering reads, and logs no error. What is the configuration, and what are the waiting backends doing?

    The standby holds a replication slot the primary is blocked on; backends wait on IO hot_standby_feedback is on and the standby is holding the vacuum horizon; backends wait on Lock The primary has exhausted max_wal_senders; backends wait on LWLock The standby is the only entry in synchronous_standby_names; backends wait at IPC / SyncRep

17. Q17. Immediately after a successful pg_promote(), pg_controldata reports "Latest checkpoint TimeLineID: 1". What should the operator conclude?

    The promotion failed and the promote request should be issued a second time The standby was promoted onto the old timeline, which will cause a conflict when the former primary rejoins the cluster The control file is corrupt, and the cluster should be restarted so that it is rewritten from the write-ahead log The promotion succeeded; pg_controldata reports the last checkpoint, which has not yet run

18. Q18. Two clusters have both been accepting writes for fifty minutes after a partition. Which statement is correct?

    A quorum setting on the primary would have prevented the second promotion The clusters can be reconciled by replaying one side’s WAL onto the other PostgreSQL has no setting that prevents this, and resolving it discards one side’s committed work Whichever cluster has the higher timeline number is authoritative and the other is safe to discard automatically

19. Q19. A recovery is configured from the team template. It reaches its recovery target, immediately ends recovery, selects a new timeline and becomes writable - and only then does anyone see that the target was an hour late. Reaching a different target now costs a fresh restore of the base backup. Which single line in that template made the mistake expensive?

    recovery_target_inclusive = off, which excluded the transaction at the target recovery_target_timeline = latest, which followed a timeline branch recovery_target_action = promote, overriding a default that would have paused restore_command pointing at one archive copy rather than two

20. Q20. A recovery fails with "recovery ended before configured recovery target was reached". What are the two possible causes?

    A corrupt WAL segment, or a checksum failure during replay The target is beyond the end of the archive, or it is before the base backup recovery.signal is missing, or restore_command is misconfigured The target timeline does not exist, or the history file is absent

21. Q21. A CREATE INDEX CONCURRENTLY is cancelled mid-build. Write latency rises 30 percent and stays there, while no query plan changes. What was left behind?

    A partially written index file that the next checkpoint will clean up An index with indisvalid false and indisready true: ignored by the planner, maintained by every write An index with indisvalid false and indisready false, occupying disk but costing nothing A lock on the table that prevents the planner from considering any index on it

22. Q22. Identical byte-level corruption is written into one page of a table on two clusters, one with data checksums and one without. What is the difference in behaviour?

    The checksummed cluster repairs the page from WAL; the other returns the damaged row Both raise an error, but only the checksummed cluster names the block The checksummed cluster refuses to answer; the other returns the corrupted row and reports success, with nothing in the log The checksummed cluster increments checksum_failures; the other logs a warning but still returns correct data

23. Q23. A cluster shows "checkpoints are occurring too frequently" and write latency spikes every ninety seconds, with each checkpoint logged as "checkpoint starting: wal". What is the fix?

    Lower checkpoint_completion_target so checkpoints finish faster Raise max_wal_size so checkpoints are driven by time rather than WAL volume Lower checkpoint_timeout so checkpoints are smaller Increase shared_buffers so fewer buffers are dirty at each checkpoint

24. Q24. pg_upgrade --check fails with "old cluster does not use data checksums but the new one does". Which resolution is preferable and why?

    initdb the new cluster with --no-data-checksums, because it avoids a scan of the old cluster Upgrade to an intermediate major version first, which normalises the setting Enable checksums on the source with pg_checksums --enable, because it keeps a protection rather than discarding one Set ignore_checksum_failure on the new cluster, which allows the mismatch

25. Q25. Which of these can hold the vacuum freeze horizon and prevent relfrozenxid from advancing? Select all that apply.

    An open transaction that has written, holding a backend_xmin A prepared transaction that was never committed or rolled back An inactive logical replication slot holding a catalog_xmin A large number of dead tuples awaiting cleanup An index that has not been reindexed since the last major upgrade

26. Q26. Transaction-mode connection pooling breaks features scoped to a session. Which of these are affected? Select all that apply.

    SET issued outside a transaction Temporary tables created outside a transaction Session-level advisory locks taken with pg_advisory_lock Row-level locks taken with SELECT FOR UPDATE inside a transaction LISTEN/NOTIFY registrations Foreign key constraint enforcement

27. Q27. Which of these are true of a pg_dump of a single database? Select all that apply.

    It excludes roles and role memberships It excludes tablespace definitions It holds a snapshot open for its whole duration, preventing vacuum from removing dead rows cluster-wide It includes the code of every extension the database uses It can be restored into a different major version

28. Q28. A plain VACUUM returns disk space to the filesystem when it removes dead tuples.

    True False

29. Q29. A cancelled VACUUM retains the progress it had made, so the next run resumes from where it stopped.

    True False

30. Q30. SELECT pg_reload_conf() returning true confirms that the new configuration was accepted.

    True False

31. Q31. pg_stat_user_tables.last_autovacuum records the time at which the most recent autovacuum on that table finished.

    True False

32. Q32. Setting synchronous_standby_names to name a single standby increases the availability of the cluster.

    True False

33. Q33. Cancelling a backend that is waiting at IPC / SyncRep returns an error to the client, so no partially replicated transaction is reported as successful.

    True False

34. Q34. Data checksums are verified whenever a page is read, including when it is read from shared buffers.

    True False

35. Q35. A physical base backup can be restored into a cluster running a different PostgreSQL major version.

    True False

36. Q36. A vacuum ran for hours and its VERBOSE output contains no "new relfrozenxid" line. Name the three categories of thing that could be responsible, and the one query that finds all of them.

37. Q37. Explain why testing a database credential with psql on the database host frequently proves nothing, and state what a valid test requires.

38. Q38. Diagnose and remediate this incident. State the evidence you would gather, the remediation, and what you would roll back if the remediation were wrong.

    A primary has been slow since 02:00. Disk usage on the data volume grew 40 GB overnight and recovered partially by morning. Autovacuum has run hundreds of times across the night and its log lines report "tuples: 0 removed" with millions "dead but not yet removable". The affected tables include several the nightly analytics job has never read. An engineer has already raised autovacuum_max_workers from 3 to 8 and lowered the scale factors, which increased the number of runs and changed nothing else. The problem stops when the analytics job finishes at 04:00.

39. Q39. A failover has gone wrong. State the evidence you would gather, the remediation, and the rollback.

    A network partition at 02:40 isolated the primary from the monitoring network but not from the application network. The failover automation promoted the standby at 02:42 after failing to reach the primary. It is now 03:30. Both clusters answer pg_is_in_recovery() with false. Customers report orders that have disappeared and orders that were never confirmed. Reporting totals from the two servers disagree by a hundred and twenty orders. Neither server has logged anything unusual.

40. Q40. A backup capability has failed silently. State the evidence, the remediation, and the rollback.

    A production database must be restored after a mistaken migration. The nightly logical backup job has reported success every night for eleven months. The restore of last night's dump completes and the application starts, but every table is empty. The job's log shows "restore failed" was never printed. The script contains: pg_dump ... | tee backup.log, followed by a check on $?.

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