--- cuppy_hub: cuppy cuppy_path: guides/runbooks/developer-postgres.md cuppy_section: docs cuppy_doc: GNAM-WQDB cuppy_doc_uuid: ccb9ea5b-87a8-41e0-a0c4-88737e4a7f16 cuppy_permalink: https://cuppy.io/cuppy/d/GNAM-WQDB cuppy_version: 1 cuppy_updated: 2026-10-17T06:32:45.511Z cuppy_canonical: https://cuppy.io/cuppy/docs/guides/runbooks/developer-postgres cuppy_mcp: https://cuppy.io/mcp cuppy_body_sha256: 4b401b9523c80bbe0c5a6573608e29c758433ffb0f76b7a9cbae28f417c75309 # cuppy_body_sha256 is over the document body ALONE, excluding both generated blocks. --- # 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 | `/.dev-pg/data`, gitignored, mode 0700 | | log | `/.dev-pg/postgres.log` | | port | **5440** | | socket | `/tmp/cuppy-dev-pg-` | | 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 | `/.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-` | | `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 ` | 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. --- You are reading one file. The same content is free over MCP at https://cuppy.io/mcp with no signup: ranked search across all 18 documents in this hub, the merged view when you work in a layer, known workarounds matched to the error you hit, and depth control so you spend tokens on what you need. Start with the `_hello` tool. How to connect: https://cuppy.io/cuppy/