The 17 jobs, word for word

Each job below is what the agent was given: one prompt, as plain text, on a server it has root on. The checks are listed by name and weight. Every check must pass; there is no partial credit. The shell that implements them and the fixtures that build each server are not published, because this suite is re-run against new models and a published grader invites fitting to it.

PG01

Install Postgres from the vendor apt repo + app db + scoped login role · day zero · cost of an error: degraded · structure: single-function · 1 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG01 · Install PostgreSQL from the vendor apt repo, app database, scoped login role (control) Port of Percona-Lab/dbaai_runner suites/postgres-install.toml (Apache-2.0; see NOTICE): the task text with {{db}} resolved to "PostgreSQL 17", the six checks one to one with Vadim's names and weights. Grading is binary (every check must pass); weights are recorded per check for the interop ticket (#23).

The prompt, word for word:

Install PostgreSQL 17 from the vendor's own apt repository on this server.
Create a database called `app` and a login role called `app_rw` whose access is limited to that database. The cluster must start again by itself after a reboot, and it must not accept connections from anywhere but this machine.
You have root over SSH on this server (node1).

Checks (all must pass):

• postgres is running and starts at boot · weight 2 · on node1
• it answers as the database and version asked for · weight 1 · on node1
• the app database exists · weight 2 · on node1
• the app_rw role exists and can log in · weight 1 · on node1
• app_rw is scoped to the app database · weight 1 · on node1
• 5432 is not open to the world · weight 1 · on node1

PG02

Install and manage extensions (which are bundled vs external) · day zero · cost of an error: wasted-effort · structure: single-function · 1 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG02 · Make three extensions usable in the app database: two ship with PostgreSQL, one is a package (control) Second control of the set, and the only "usage" job: the cluster is already running and the work needs a connection and one OS package. It exists so that a wave where the multi-step jobs fail can be read as "the multi-step jobs failed" and not "the cell is broken". The one thing it can expose is the per-database mistake: extensions live inside a database, so CREATE EXTENSION in postgres leaves app with nothing. Grading is binary (every check must pass); weights are recorded per check for the interop ticket (#23).

The prompt, word for word:

This server runs PostgreSQL 17 with a database called `app` that an application connects to as the role `app_rw`.
Make three extensions available to that application: `pgcrypto`, `hstore` and `pgvector`. Two of them are part of PostgreSQL; one is not. When you are finished, a session connected to `app` as the application role must be able to use all three.
Leave the other databases on this cluster as they are, and leave the application's data as it is.
You have root over SSH on this server (node1); the `postgres` operating-system account administers the cluster.

Checks (all must pass):

• the three extensions exist in the app database · weight 2 · on node1
• pgcrypto works · weight 1 · on node1
• hstore works · weight 1 · on node1
• pgvector works · weight 2 · on node1
• the application role can use them, not only postgres · weight 1 · on node1
• the other databases were left alone · weight 1 · on node1
• the application's data is untouched · weight 1 · on node1
• the application role was not made a superuser · weight 1 · on node1

PG03

Configure a query-statistics extension · day zero · cost of an error: degraded · structure: multi-step · 1 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG03 · Configure pg_stat_monitor, not pg_stat_statements (issue #33). Fixture fx_percona_repo_bare: a bare machine with Percona's repository configured and no PostgreSQL. The job names a Percona extension that does the same thing as a famous one. The substitution is the finding: pg_stat_statements is in every model's training data, has a different view, different GUC names and ships with the server, so a model that reaches for it produces a cluster that collects query statistics and does not do the job. The grader records the substitution separately from the failure so the article can count it. Grading is binary (every check must pass).

The prompt, word for word:

Install Percona Distribution for PostgreSQL 17 on this server, from the Percona repository that is already configured on the machine, and create a database called `app`.
Set up `pg_stat_monitor` so that query statistics for `app` are collected from the moment the server starts. Keep the statistics in buckets of five minutes, and keep the last two hours of buckets.
The settings must be in force when you are finished, and must survive a restart. The cluster must be running on port 5432 and come up on its own after a reboot.
You have root over SSH on this server (node1); the `postgres` operating-system account administers the cluster.

Checks (all must pass):

• a PostgreSQL 17 cluster is running, on 5432, and starts at boot · weight 2 · on node1
• the app database exists · weight 1 · on node1
• pg_stat_monitor is loaded at server start and in force · weight 3 · on node1
• the extension exists in app · weight 2 · on node1
• it records queries that ran · weight 3 · on node1
• the buckets are the size and the number that were asked for · weight 3 · on node1
• the famous extension did not stand in for it · weight 1 · on node1

PG04

Tune a fresh cluster for its machine (shared_buffers, work_mem, WAL, planner) · day zero · cost of an error: degraded · structure: multi-step · 1 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG04 · Tune a fresh cluster for its machine: shared_buffers, planner, WAL, memory budget (control) Port of Percona-Lab/dbaai_runner suites/postgres-tune.toml, stage two (Apache-2.0; see NOTICE): the task text with {{db_name}} resolved to "PostgreSQL 17", the nine checks one to one with Vadim's names and weights, plus one check of ours (the listener stays local). Single stage on the seeded image: PG01 already measures the install, so the cluster the agent tunes is the packaged one, not its own. Grading is binary (every check must pass); weights are recorded per check for the interop ticket (#23).

The prompt, word for word:

PostgreSQL 17 on this server is running on the configuration it was installed with. Those defaults are sized for a far smaller machine than this one, and this server has nothing else to do: it is a dedicated database server.
Tune it for the hardware it actually has. Read the memory, the CPU count and the kind of disk off the server rather than assuming them, and size the cluster to what you find: the shared buffers and what the planner believes about this machine above all, and whatever else about the stock configuration is wrong for a server like this one.
The rules:
- The settings must survive a restart of the service, and they must be in force
  when you are finished. A value written into a file that the running server has
  never read is not a configured server.
- The cluster must be running when you are done, and must still come up on its
  own after a reboot.
- Leave room for the operating system, and for the per-connection memory that
  whatever max_connections you choose can claim at once. The machine must not be
  able to over-commit itself.
- Do not buy speed with durability: a committed transaction must still survive
  the machine losing power.
- The `app` database and the `app_rw` role must still work exactly as they did,
  and the server must keep accepting connections only from this machine.

Report what you changed, the value you chose for each setting, and why that value suits this particular server.
You have root over SSH on this server (node1); the `postgres` operating-system account administers the cluster.

Checks (all must pass):

• postgres is still running and still starts at boot · weight 3 · on node1
• shared_buffers is sized to this machine's memory · weight 4 · on node1
• the sizing came from a file and nothing waits for a restart · weight 5 · on node1
• memory is not over-committed · weight 4 · on node1
• commits are still durable · weight 2 · on node1
• the write-ahead log has room between checkpoints · weight 2 · on node1
• the planner knows how much memory this machine has · weight 2 · on node1
• the planner knows the disk is not a spinning platter · weight 1 · on node1
• the app database and its role came through it · weight 2 · on node1
• 5432 is still not open to the world · weight 1 · on node1

PG05

Streaming replication across two servers over a private network · day zero · cost of an error: outage · structure: multi-step · 2 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG05 · Streaming replication across two servers over the private network (control) Port of Percona-Lab/dbaai_runner suites/postgres-replication.toml (Apache-2.0; see NOTICE): Vadim's task text with {{db}} resolved to "PostgreSQL 17" and the servers named, his nine checks one to one with his names, weights and host semantics ("*" = both servers, "one" = exactly one server). Roles are not pre-assigned: which node is the primary is part of the job, so no check names a node. Grading is binary (every check must pass); weights are recorded per check for the interop ticket (#23).

The prompt, word for word:

These two servers, node1 and node2, are identical and freshly built. Install PostgreSQL 17 on both and set up streaming replication between them: one primary taking writes, one standby following it and serving reads.
Decide which server takes which role and say which you chose. Replication traffic must go over the private network the two servers share, not over the public internet, and the standby must refuse writes of its own.
You have root over SSH on both servers; the `postgres` operating-system account administers each cluster. The servers resolve each other by name on the private network (`getent hosts node2` on node1 gives node2's private address, and the other way round).

Checks (all must pass):

• postgres is running on both servers · weight 2 · on *
• both servers are the database and version asked for · weight 1 · on *
• one server takes writes and ships its write-ahead log · weight 1 · on one
• one server is a standby whose receiver is streaming · weight 3 · on one
• the standby refuses writes · weight 1 · on one
• the primary sees the standby connected · weight 2 · on one
• replication reads from the peer's private address · weight 2 · on one
• a marker row can be written on the primary · weight 1 · on one
• the marker arrived on the standby · weight 4 · on one

PG06

Major version upgrade with pg_upgrade (16 -> 17), extensions included · day two · cost of an error: catastrophic · structure: version-trap · 1 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG06 · In-place major upgrade 16 -> 17 with pg_upgrade, extensions included. Pre-state = the seeded flavour of vm-ubuntu2404-pg16: one 16/main cluster on 5432, pg_stat_statements preloaded from conf.d, pgvector from its own package with app.embeddings and an HNSW index on it, the seed tables. No fixture: the image is the pre-state. Checks read the seed checksum from PGEVAL_SEED_CHECKSUM (the image's images.json) and the two other table checksums as constants computed once from the same seed with the same helper (README).

The prompt, word for word:

This server runs PostgreSQL 16 from the PGDG apt repository: one cluster, 16/main, on port 5432, serving the database `app` for an application that connects to localhost:5432 as `app_rw`. The database uses two extensions, `vector` (pgvector) and `pg_stat_statements`.
Upgrade this cluster in place to PostgreSQL 17. When you are done:
- the application must reach PostgreSQL 17 on port 5432 without changing any of its
  settings, and the server must keep accepting connections only from this machine;
- all data must be intact; - both extensions must work in the new cluster; - the 16 cluster must no longer be running; - the 17 cluster must come up on its own after a reboot.
Upgrade what is on disk. Do not rebuild the database from an application-side export.
You have root over SSH on this server (node1); the `postgres` operating-system account administers the cluster.

Checks (all must pass):

• 17 answers on 5432 · weight 3 · on node1
• no 16 cluster online · weight 3 · on node1
• data intact · weight 5 · on node1
• extensions work, not just exist · weight 4 · on node1
• 17 build of the extension installed · weight 1 · on node1
• in force and durable · weight 3 · on node1
• writable, and the application still gets in · weight 3 · on node1
• planner statistics present (recorded, never fails) · weight 1 · on node1

PG08

Back up and restore with pgBackRest (repo + stanza + full/incremental) · day two · cost of an error: catastrophic · structure: multi-step · 1 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG08 · pgBackRest: repository, stanza, full + incremental backup, single-table restore after an incident Day two, multi-step, cost of error catastrophic. Drill shape from CARI Issue 1 d2_backup_restore: back up, run the incident, restore one table while the incident log survives. Fixture: environments/fixtures/fx_incident_payments. Grading is binary (every check must pass); weights are recorded per check.

The prompt, word for word:

This server runs PostgreSQL 17: cluster 17/main on port 5432, database `app`. There are no backups of this cluster.
Set up pgBackRest for it with a local repository on this server, so the cluster can be restored from that repository: WAL archiving through pgBackRest, a stanza for the cluster, then a full backup, and after the full backup an incremental one.
Then run the incident drill: execute /opt/pgeval/incident.sh. It damages the table app.payments on purpose and records that it ran in app.incident_log. Do not run it before your backups are complete; the drill is void if it never ran.
Finally, restore app.payments to exactly its state before the incident: same rows, same values. Only that table may be restored. app.incident_log and everything else in the database is live state that must survive. The cluster must be running on 5432 and accepting writes when you finish.
You have root over SSH on this server (node1); the PostgreSQL superuser is reachable as the `postgres` OS account.

Checks (all must pass):

• backups exist: a stanza with a full and a later incremental · weight 3 · on node1
• archiving in force: archive_mode on, archive_command through pgbackrest, nothing pending a restart · weight 2 · on node1
• archive round trip: pgbackrest check passes for every stanza · weight 2 · on node1
• the drill ran and its record survived · weight 3 · on node1
• app.payments is back to its pre-incident state · weight 4 · on node1
• other state untouched: app.customers unchanged · weight 1 · on node1
• cluster serving on 5432, accepting writes, enabled at boot · weight 2 · on node1
• the repository is on this server · weight 1 · on node1

PG10

Migrate community PostgreSQL to Percona Distribution (package-swap path) · day two · cost of an error: catastrophic · structure: multi-step · 1 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG10 · Migrate community PostgreSQL 17 to Percona Distribution in place (issue #40). Fixture fx_percona_migration: a community PGDG cluster with tenants and two extensions in use, Percona's repository configured and nothing installed from it. The repository is given because the job is the migration, not the search. The MySQL twin of this job (#3, in-place migration) failed 2 of 3 and 3 of 3 in the measured runs, and the reason was always the same shape: the packages get swapped and the data does not survive, or the data survives and the server underneath is still the old one. The sentinel file inside the data directory is what separates a real in-place migration from a dump reloaded into a fresh cluster, because no initdb can produce it. Grading is binary (every check must pass).

The prompt, word for word:

This server runs community PostgreSQL 17 from the PGDG apt repository: cluster 17/main on port 5432, database `app`, with the extensions `pg_stat_statements` and `pgcrypto` in use. Percona's apt repository is already configured on this machine.
Move this server to Percona Distribution for PostgreSQL 17. Keep the same major version, the same cluster, the same data and the same port.
When you are finished: the running server must come from Percona's packages, Percona's `pg_stat_monitor` extension must be available to create in `app`, the extensions already in use must still work, and the cluster must be running on 5432 and coming up on its own after a reboot.
Migrate what is on disk. Do not export the data and load it back in.
You have root over SSH on this server (node1); the `postgres` operating-system account administers the cluster.

Checks (all must pass):

• the running server comes from Percona's packages · weight 4 · on node1
• it is still PostgreSQL 17 on port 5432, and starts at boot · weight 2 · on node1
• Percona's own extension is available to create · weight 3 · on node1
• the data came across · weight 4 · on node1
• it is the same cluster on disk, not a fresh one loaded from a dump · weight 4 · on node1
• the extensions that were in use still work · weight 3 · on node1
• the community server package is gone · weight 2 · on node1

PG11

Point-in-time recovery to a timestamp from a WAL archive · day two · cost of an error: catastrophic · structure: multi-step · 1 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG11 · Point-in-time recovery to a timestamp from a base backup and a WAL archive (issue #41). Fixture fx_pitr_incident: archiving on, a base backup taken with -X none (restorable only together with the archive), a legitimate batch, a recorded timestamp T, then the incident (rows after T, amounts rewritten on a third of the pre-T rows, a fifth of them deleted). The job is rare and catastrophic, which is why frequency and cost of error are separate columns in this set: at one blended priority this job drops out, and it is where the loudest measured MySQL result came from, a model reporting success with none of the rows in the database. Two Postgres-specific traps sit inside it: recovery configuration moved out of recovery.conf in version 12, so a model working from older memory writes a file the server ignores or refuses; and recovery_target_action defaults to pause, so a recovery that reached the right target correctly is still a read-only cluster until it is promoted. Grading is binary (every check must pass).

After the runs: The definition above was written before the runs. Its archiving check asks for archiving to work after the recovery. The prompt asks to keep the base backup and the WAL archive intact, not for archiving afterwards, and PG16's check accepts archiving turned off. The article treats that as a disputed reading, under "How strict the checks are".

The prompt, word for word:

This server runs PostgreSQL 17: cluster 17/main on port 5432, database `app`. WAL archiving is on, segments are archived to /var/lib/pgeval/wal_archive, and a base backup of the cluster is at /var/lib/pgeval/base.
/opt/pgeval/incident.txt holds a timestamp. At that moment the database was in a known-good state. Shortly after it, an operator's script ran against `app` and damaged the table `app.payments`; nothing written after that timestamp can be trusted.
Recover the cluster to exactly its state as of that timestamp. When you are done the cluster must be running on port 5432, accepting writes, and coming up on its own after a reboot. The base backup and the WAL archive are the only trusted copies of this data: keep both intact.
You have root over SSH on this server (node1); the `postgres` operating-system account administers the cluster.

Checks (all must pass):

• the cluster is out of recovery and accepting writes · weight 3 · on node1
• app.payments is the table as it stood at the timestamp · weight 4 · on node1
• a recovery happened, the rows were not edited back into place · weight 3 · on node1
• the base backup and the archive are still intact · weight 3 · on node1
• it serves from the standard location, on 5432, and starts at boot · weight 2 · on node1
• archiving is still on for the next incident · weight 1 · on node1

PG12

Migrate roles, passwords and grants between clusters · day two · cost of an error: catastrophic · structure: multi-step · 2 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG12 · Migrate roles, passwords, memberships and grants from one cluster to another Day two, multi-step, cost of error catastrophic. Fixture: environments/fixtures/fx_two_clusters_roles (node1 = source with ten application roles and their grants; node2 = same data, no roles, a sentinel row). Every check runs on node2; the ones that need the source compare live against node1 over the private network (qh) or against the values the fixture recorded (PGEVAL_FX_*). Grading is binary; weights recorded.

The prompt, word for word:

Two PostgreSQL 17 servers on a private network: node1 and node2 (both names resolve; the private addresses are in /etc/hosts on each server). node1 has been serving the `app` database for months, with a set of application roles: logins, group roles, passwords, and the grants that go with them. node2 already holds the same `app` schema and data, but none of the roles.
Migrate the roles from node1 to node2 so that the same clients, with the same credentials, can connect to node2 and get exactly the access they have on node1: no more, no less. Passwords must keep working as they are. node2's data is live and must not be dropped or reloaded.
You have root over SSH on both servers; the PostgreSQL superuser is reachable as the `postgres` OS account on each, and the superuser's TCP password is the same on both servers.

Checks (all must pass):

• the application roles exist on node2 with the same attributes as on node1 · weight 3 · on node2
• password verifiers transferred, not re-set · weight 2 · on node2
• memberships identical, including ADMIN, INHERIT and SET options · weight 2 · on node2
• the privilege matrix on node2 equals node1's · weight 4 · on node2
• real logins work on node2 with the original passwords, role settings and refusals included · weight 3 · on node2
• default privileges identical to node1's · weight 2 · on node2
• no privilege escalation: no application role is superuser, node2's postgres verifier unchanged · weight 2 · on node2
• node2's data untouched: table checksums and the local sentinel row · weight 3 · on node2

PG13

Logical replication: publication/subscription, slot lifecycle, conflict handling · day zero · cost of an error: outage · structure: orchestration · 2 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG13 · Logical replication of app.orders between two independent clusters (issue #43). Fixture fx_logical_conflict_pair: two clusters that know nothing about each other, wal_level at the packaged default on both, and a subscriber that already holds rows of its own plus one row that collides with the publisher by primary key and differs from it by value. Physical replication (PG05) copies a whole cluster and has no such thing as a conflict. Logical replication has to be told what to copy, needs a restart to make logical decoding possible, and stops on the first conflict. The three traps: ALTER SYSTEM SET wal_level with a reload instead of a restart; reading pg_subscription.subenabled as proof that anything replicated; and clearing the conflict by truncating the subscriber's table, which also deletes the rows the prompt says must stay. Grading is binary (every check must pass).

After the runs: The definition above was written before the runs. Its publisher-table check fails any change to node1's orders, and in the runs it also failed attempts that did not edit node1 to settle the conflict: they wrote test orders, or updated an existing one, on node1 after replication was running, to watch it work, and left the writes there. The prompt does not say node1 must stay unchanged. The article treats that as a disputed reading, under "How strict the checks are".

The prompt, word for word:

`node1` and `node2` each run PostgreSQL 17 with a database called `app`. They are separate servers with separate data; they share a private network and each resolves the other's name.
Set up logical replication so that every row of the table `app.orders` on `node1` is replicated to `app.orders` on `node2`, and keeps arriving as rows are inserted and updated on `node1`.
`node2` already holds some orders of its own. Those must stay. Where an order exists on both servers, `node1` is the source of truth and its version must win.
Replication traffic must go over the private network, not the public one. When you are finished, leave nothing behind on `node1` that holds write-ahead log for a consumer that is not there any more.
You have root over SSH on both servers; the `postgres` operating-system account administers each cluster, and a credential for the `replicator` role is at /etc/pgeval/secrets/replicator.pw on both.

Checks (all must pass):

• the publisher can decode logically, and it is in force · weight 2 · on node1
• the table finished its initial copy · weight 4 · on node2
• the apply worker is running · weight 2 · on node2
• the publisher has one active slot and no orphans · weight 3 · on node1
• an insert on the publisher arrives at the subscriber · weight 3 · on node2
• an update on the publisher arrives at the subscriber · weight 2 · on node2
• the conflicting row was resolved toward the publisher · weight 3 · on node2
• the subscriber's own rows survived · weight 3 · on node2
• the publisher's table was not edited to make the conflict go away · weight 1 · on node1
• replication traffic goes over the private network · weight 1 · on node1

PG14

Diagnose and repair broken replication: slot bloat, WAL retention, lag · day two · cost of an error: outage · structure: diagnostic · 2 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG14 · Diagnose and repair broken streaming replication: stale credentials, orphaned slot, WAL retention Pre-state from environments/fixtures/fx_replication_broken (node1 primary, node2 standby, two faults on node1). The checks read the fixture record as PGEVAL_FX_<KEY> (CONTRACTS §4): ORPHAN_SLOT, LIVE_SLOT, PGWAL_MAX_BYTES, SENTINEL_PATH, SENTINEL_TOKEN, REPLICATOR_VERIFIER, PRIMARY_IP, STANDBY_IP. Grading is binary (every check must pass); weights are recorded per check.

The prompt, word for word:

Two PostgreSQL 17 servers on a private network run as a streaming-replication pair: node1 is the primary, node2 a hot standby. Monitoring reports that node2 stopped following node1 some time ago, and that disk usage under node1's WAL directory has grown and is still growing. Credentials for service roles are kept in /etc/pgeval/secrets/ on each server.
Diagnose and repair. When you are done: node2 must follow node1 again with no data loss; the WAL growth on node1 must be resolved so the disk stops filling; node1 must keep serving writes throughout. Repair the existing standby: do not rebuild node2 from a new base backup, and keep its data directory. Do not change any role's password on the primary.
You have root over SSH on both servers; the PostgreSQL superuser is reachable as the `postgres` operating-system account on each. The servers resolve each other as node1 and node2 on the private network.

Checks (all must pass):

• the standby's receiver is streaming again · weight 3 · on node2
• the primary sees the standby connected from its private address · weight 2 · on node1
• no replication slot retains WAL without a consumer · weight 3 · on node1
• the slot the standby uses, if any, exists and is active on the primary · weight 1 · on node2
• a row written on the primary arrives on the standby · weight 4 · on node2
• WAL on the primary is recycled once nothing holds it · weight 3 · on node1
• the standby was repaired, not rebuilt · weight 3 · on node2
• the credential rotation on the primary stands · weight 2 · on node1
• the primary keeps accepting writes · weight 1 · on node1
• the standby serves reads · weight 1 · on node2

PG15

Online schema change without holding ACCESS EXCLUSIVE (CONCURRENTLY, lock_timeout) · day two · cost of an error: catastrophic · structure: multi-step · 1 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG15 · Online schema change on a live 5M-row table: a NOT NULL column with a default, an index, a foreign key, while the application keeps reading and writing (issue #45). Fixture: fx_bloat_under_load in mode "mixed" (healthy table + the mixed read/write workload). The fixture's manifest hard-codes update-heavy, so this task names the mixed variant manifest (environments/fixtures/fx_bloat_under_load_mixed/fixture.yaml → ../fx_bloat_under_load/apply.sh --mode mixed). Grading is binary (every check must pass). The window every log check reads is [T0, grading time]: T0 = PGEVAL_T0 from the runner, or the fixture's workload_started_at in the proofs tool.

The prompt, word for word:

You are the on-call engineer for a PostgreSQL 17 server on this machine (node1, root over SSH). The application is live: a service called `pgeval-workload` reads and writes the table `app.events` (about 5 million rows) around the clock, and it must keep working while you make the change below.
Ship this schema change on `app.events`:
1. a new column `region text NOT NULL` with the default value 'eu' for existing and future rows;
2. an index on `(account_id, created_at)`;
3. a foreign key from `app.events.account_id` to `app.accounts(id)`.
Constraints: the application must not see errors, and no application statement may wait on a lock for more than 2 seconds at any point. Do not stop, pause or restart the `pgeval-workload` service. Do not delete or rewrite existing rows. The change must be complete when you finish, nothing left half-built.
The `postgres` OS account administers the cluster (peer authentication). The application connects as `app_rw`; its connection settings are in /etc/pgeval/workload.env.

Checks (all must pass):

• the cluster answers · weight 1 · on node1
• region column: text, NOT NULL, default 'eu', every row reads 'eu' · weight 2 · on node1
• index on (account_id, created_at) exists and is valid · weight 2 · on node1
• foreign key events.account_id -> accounts.id exists and is validated · weight 2 · on node1
• no invalid index left behind in the database · weight 1 · on node1
• the application service kept running (active, not restarted, no stall) · weight 3 · on node1
• the application saw no errors and no latency spike in the window · weight 3 · on node1
• data intact: cold rows unchanged, no rows lost · weight 2 · on node1

PG16

Cluster will not start: corrupt pg_control, bad permissions, or a full pg_wal · day two · cost of an error: catastrophic · structure: diagnostic · 1 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG16 · The cluster will not start: pg_wal full behind an orphan slot and failed archiving, data directory owned by root (issue #46). Fixture fx_wont_start. Three causes, one symptom, which is how an outage arrives. Space is the visible one, and clearing it is the trap: pg_resetwal starts the cluster in a minute and throws away the last thousand committed rows. The recorded checksum is taken before the crash and never touches the VM, so that trade is visible in the record instead of looking like a fast recovery. Grading is binary (every check must pass); the runner also fails the attempt outright if pg_resetwal appears in the command log. There is deliberately no check that the .ready segments still exist somewhere. The harm of deleting WAL by hand is losing committed transactions, and that is what the data check measures. Segments themselves legitimately disappear during an honest recovery: PostgreSQL recycles them once they are archived or once archiving is turned off, and an agent may point archive_command at a different directory entirely. A check that looked for them would fail the recoveries this task is meant to accept.

After the runs: The definition above was written before the runs. In the runs, pg_resetwal did not lose rows every time: two of the three attempts that ran it kept every committed row, and the third left a database the rig could not read. The slot check also fails an attempt that kept the slot and capped it with max_slot_wal_keep_size, which meets the prompt, and the archiving check accepts archiving turned off, which trades away the next recovery. The article covers all three, under "What a failure leaves behind" and "How strict the checks are".

The prompt, word for word:

PostgreSQL 17 on this machine is down and will not start. The last thing the on-call channel saw was the application reporting write failures, then connection refused. Somebody may already have tried to fix it.
Bring the cluster back into service.
Every transaction the application committed before the outage must still be there afterwards: losing data is worse than staying down longer. Whatever caused this must not be able to fill the machine up again the moment the server starts writing.
You have root over SSH on this server (node1); the `postgres` operating-system account administers the cluster. The database is `app` and the application connects as `app_rw`.

Checks (all must pass):

• the cluster is running, in production, and starts at boot · weight 3 · on node1
• it accepts writes again · weight 2 · on node1
• every committed row is still there · weight 4 · on node1
• the slot that held the WAL is gone · weight 3 · on node1
• pg_wal has room to keep working · weight 3 · on node1
• archiving works again, or is off on purpose · weight 3 · on node1
• the data directory belongs to postgres again · weight 2 · on node1

Forbidden in the command log (an attempt that runs these fails whatever the checks say): pg_resetwal, pg_reset_wal

PG17

Autovacuum is not keeping up: a bloating table under sustained write load · day two · cost of an error: degraded · structure: multi-step · 1 node(s) · graded 600s after the agent stops

Why this job is in the set, from its definition:

PG17 · Autovacuum not keeping up: a bloating table under sustained updates, graded ten minutes later (issue #47). Fixture fx_bloat_under_load in its default mode (update-heavy): autovacuum disabled on the table, three bulk passes over the hot range, the update-heavy workload at 1,500 statements/s. The grader runs grade_delay_s = 600 after the agent's last command, so a one-off VACUUM with no settings change is undone by the workload before it is looked at. Grading is binary (every check must pass). The window every log check reads is [T0, grading time]: T0 = PGEVAL_T0 from the runner, or the fixture's workload_started_at in the proofs tool.

The prompt, word for word:

You are the on-call engineer for a PostgreSQL 17 server on this machine (node1, root over SSH). The application service `pgeval-workload` updates the table `app.events` (about 5 million rows) continuously and must keep doing so at its current rate.
Over the last hour the table has grown far faster than its row count and its queries are slowing down; the dashboard shows dead tuples piling up. Get the bloat under control, and make sure it stays under control after you leave.
Constraints: the application must not see errors, and no application statement may wait on a lock for more than 2 seconds. Do not stop, pause or restart the `pgeval-workload` service. Do not delete rows.
The `postgres` OS account administers the cluster (peer authentication). The application connects as `app_rw`; its connection settings are in /etc/pgeval/workload.env.

Checks (all must pass):

• the cluster answers · weight 1 · on node1
• dead-tuple ratio on app.events is under control ten minutes later · weight 3 · on node1
• autovacuum will keep running on app.events · weight 3 · on node1
• a vacuum has actually run on app.events recently · weight 2 · on node1
• the application service kept running (active, not restarted, no stall) · weight 3 · on node1
• the application saw no errors and no latency spike in the window · weight 3 · on node1
• no exclusive-lock rewrite under load (VACUUM FULL signature) · weight 2 · on node1
• data intact: immutable columns unchanged, ids unchanged, no rows lost or added · weight 2 · on node1

PG18

Transaction ID wraparound: cluster approaching a forced shutdown · day two · cost of an error: catastrophic · structure: diagnostic · 1 node(s) · graded 0s after the agent stops

Why this job is in the set, from its definition:

PG18 · Transaction id wraparound: a few million transactions from the forced shutdown, with the freeze horizon held by a prepared transaction (issue #48). Fixture fx_xid_wraparound. Rare and unrecoverable if mishandled, which is why frequency and cost of error are separate columns in this set. The visible problem is the warning in the log, and the obvious answer, VACUUM FREEZE everywhere, does not move the horizon at all while a prepared transaction from a crashed application is holding it. A model that vacuums, sees no change and vacuums harder is the failure this job measures. The second half is that the protection has to work after the agent leaves: autovacuum was turned off by the previous admin, and leaving it off means the same shutdown again in a few weeks. Grading is binary (every check must pass).

The prompt, word for word:

PostgreSQL 17 on this machine is logging warnings that it will shut down soon to prevent transaction id wraparound. When it does, the application stops being able to write.
Get this cluster out of wraparound danger, and make sure the protection goes on working after you leave.
Do not lose committed data. One thing worth knowing: the application does a nightly import that uses two-phase commit, and the last one did not finish cleanly.
You have root over SSH on this server (node1); the `postgres` operating-system account administers the cluster. The database is `app` and the application connects as `app_rw`.

Checks (all must pass):

• the cluster is out of wraparound danger · weight 4 · on node1
• the transaction that was holding the horizon is resolved · weight 3 · on node1
• the protection is on again · weight 3 · on node1
• no per-table exception was left behind that keeps a table out of the sweep · weight 2 · on node1
• the cluster takes writes and starts at boot · weight 2 · on node1
• committed data is intact · weight 4 · on node1

PG19

Connection exhaustion: 60 application workers against max_connections = 20 · day two · cost of an error: outage · structure: multi-step · 1 node(s) · graded 120s after the agent stops

Why this job is in the set, from its definition:

PG19 · Connection exhaustion: 60 application workers against max_connections = 20, no over-commit allowed (issue #49). Fixture fx_connection_exhaustion: max_connections = 20, memory settings sized from /proc/meminfo so that 20 connections fit the over-commit rule and 40 do not, and the connection-storm workload already logging "too many clients already" before handover. The job has one honest solution shape: put something between the application and the cluster that holds the connections. The grader never names it. It asks for the outcome instead: no refusals, the cluster not carrying sixty backends, the memory rule still satisfied. "Raise max_connections to 1000" satisfies the first and violates the third, and that is the trap the job is here to catch. Graded 120 s after the agent stops, so a change that only holds while the agent watches is undone by then. The cluster must stay on its own port: the grader reaches it through /opt/pgeval/pg.sh over the default local socket, as every other task does, so a pooler goes beside the cluster and the application moves to it, not the other way round. The prompt says so in the operator's words.

After the runs: The definition above was written before the runs. The prompt allows pointing the application at a different endpoint and does not require it, while the checks ask for something between the application and the cluster. In the runs, some attempts stopped the refusals within the memory rule and left the application connected directly. The article treats that as a disputed reading, under "How strict the checks are".

The prompt, word for word:

You are the on-call engineer for a PostgreSQL 17 server on this machine (node1, root over SSH).
The application service `pgeval-workload` runs 60 workers. Each one opens a connection, runs two statements and disconnects, over and over. Right now many of those connections are being refused and the application is losing work.
Make every application connection succeed, and keep it that way when the worker count grows again next month.
Constraints: this machine's memory is what it is, so the cluster must stay within it. Every connection the server is willing to accept has to be affordable at the same time, with the memory settings you leave behind. The database must keep answering on the port and socket it uses today, because other things on this machine talk to it there. You may point the application at a different endpoint and restart it, but it must go on doing the same work as the same user against the same database, at the rate it does now. Do not delete or rewrite the application's data.
The `postgres` OS account administers the cluster (peer authentication). The application's connection settings are in /etc/pgeval/workload.env.

Checks (all must pass):

• the cluster answers and starts at boot · weight 2 · on node1
• the application service is still the one the fixture started · weight 2 · on node1
• no application connection is refused any more · weight 4 · on node1
• the application no longer connects straight to the cluster · weight 3 · on node1
• the cluster is not carrying every worker at once · weight 3 · on node1
• the memory the cluster promises still fits the machine · weight 4 · on node1
• the application's data is untouched · weight 2 · on node1