Runbook: the developer Postgres cluster

Hand: agent. No root, no sudo, no pm2, no shared cluster. Item: FEAT-009, sprint S1. For the production cluster, which is operator-hand, see RUNBOOK-prod-postgres-cluster.md.

This cluster exists so an agent can build and test Part V's isolation model without touching anything shared. It is a second PostgreSQL 16 cluster, initialised into a directory this user owns, listening on loopback on a port nothing else uses.

What the host already provides

Verified on this host, 2026-10-11:

server binaries	/usr/lib/postgresql/16/bin (PostgreSQL 16.13, Ubuntu 24.04 build)
pgvector	present, version 0.6.0 — vector.so and vector.control are installed
ltree	present, version 1.2
pgcrypto	present, version 1.3
uuid-ossp	present, version 1.1 (not used: gen_random_uuid() is in core since 13)

initdb and pg_ctl both run as an unprivileged user, so no operator step is needed for the developer cluster. pgvector being already installed means item 5 of EPIC-PLAN.md §1 needs no package work either — only pg_createcluster, the roles and pgBackRest.

Commands

scripts/dev-pg.sh init      create the data directory, roles, extensions, two databases
scripts/dev-pg.sh start     start it
scripts/dev-pg.sh stop      stop it (fast shutdown)
scripts/dev-pg.sh status    cluster state, the role matrix, the extension list
scripts/dev-pg.sh reset     stop, delete the data directory, init again

init is run once. After a reboot, start.

What init builds

data directory	<repo>/.dev-pg/data, gitignored, mode 0700
log	<repo>/.dev-pg/postgres.log
port	5440
socket	/tmp/cuppy-dev-pg-<uid>
databases	cuppy (development) and cuppy_test (the suite)
roles	cuppy_app, cuppy_jobs, cuppy_migrate (login) and cuppy_rls (nologin)
superuser	postgres — named so because Z3 asserts rolname <> 'postgres' when counting rolbypassrls, and a differently named bootstrap superuser fails that check by construction
credentials	<repo>/.dev-pg/dev-pg.env, mode 0600, gitignored. One DSN per role, for both databases
auth	local socket trust (the directory is 0700 and owned by this user); TCP scram-sha-256, so every loopback connection the application makes needs a password

Ports 5432 and 5433 are refused by name in the script: 5432 is the shared production cluster (datashield_gtm and friends), 5433 is reserved for the operator-provisioned cuppy production cluster (F3). Every other value is a variable with an environment override; see the head of scripts/dev-pg.sh.

The socket directory must stay short: the kernel caps a unix socket path at 107 bytes, and an agent scratch directory under /tmp/claude-*/… already exceeds it. The script checks the length and refuses rather than failing inside pg_ctl.

Applying the migrations

set -a; . .dev-pg/dev-pg.env; set +a
cd apps/api
npx tsx src/scripts/migrate.ts          # the production sequence
npx tsx src/scripts/migrate.ts --test   # plus the gate-test fixtures
npx tsx src/scripts/migrate.ts --list   # what has landed

The runner connects as cuppy_migrate and reads CUPPY_MIGRATE_DATABASE_URL. It never reads DATABASE_URL: the process environment pm2 hands this service pins that variable to a database belonging to another application, and CLAUDE.md rule 3 forbids using it. There is no fallback, so an unset CUPPY_DATABASE_URL is an unconfigured database rather than a default one.

Running the gate tests

Nothing to configure. apps/api/test/global-setup.ts probes the cluster once per run, applies both migration directories to cuppy_test, seeds the fixture graph, and writes a verdict to apps/api/.test-storage-mcp/pg-probe.json. With the cluster down the V3 gate suite skips and prints its reason as the name of a passing test, so the full suite stays green offline:

cd apps/api && npx vitest run
#   cluster up:   940 passed
#   cluster down: 893 passed | 48 skipped

Two safety interlocks, because the fixture seeder truncates every table:

• loadDevPgEnv() builds the three DSNs config reads only from the CUPPY_TEST_*

set, and clears them when any is missing, so a partial env cannot leave the runner pointed at the development database.

• seedFixture() reads current_database() and refuses to run unless the name ends in

_test.

Troubleshooting

symptom	cause
could not start server, nothing in the log	socket path over 107 bytes. Set CUPPY_DEV_PG_SOCKET_DIR to something short
fe_sendauth: no password supplied	TCP is scram. Source .dev-pg/dev-pg.env, or connect over the socket with -h /tmp/cuppy-dev-pg-<uid>
port 5433 is reserved	by design. 5433 belongs to the operator's cuppy cluster
role cuppy_app is absent from migration 001	the cluster was initialised without scripts/db/bootstrap-roles.sql. Run reset
a migration fails with permission denied for table <one of the six>	that table is owned by cuppy_rls; the statement must run under SET LOCAL ROLE cuppy_rls, or needs GRANT REFERENCES back to cuppy_migrate. Both patterns are in 005 and 006

What this cluster is not

It is not a staging environment and holds no real data. It is destroyed and rebuilt by reset without ceremony. The production cluster is provisioned once, by the operator, from the other runbook, and this file never touches it.