---
cuppy_hub: cuppy
cuppy_path: guides/runbooks/production-postgres-cluster.md
cuppy_section: docs
cuppy_doc: T7FD-XHGS
cuppy_doc_uuid: 99d2464e-f706-4af7-ad43-ef7272991d50
cuppy_permalink: https://cuppy.io/cuppy/d/T7FD-XHGS
cuppy_version: 1
cuppy_updated: 2026-10-17T06:32:45.511Z
cuppy_canonical: https://cuppy.io/cuppy/docs/guides/runbooks/production-postgres-cluster
cuppy_mcp: https://cuppy.io/mcp
cuppy_body_sha256: 11accb644d7cddbce1ce35aebab523a20bed468142be50ae6db1c051db31a5de
# cuppy_body_sha256 is over the document body ALONE, excluding both generated blocks.
---

# Runbook: provision the dedicated cuppy Postgres cluster

**Status: PENDING OPERATOR.** This is item 5 of `EPIC-PLAN.md` §1. CUP-DEV-STD-001 §5 puts
cluster provisioning, role creation and secrets in the operator's hand; nothing below has
been applied. Prepared by FEAT-009 (sprint S1).

**Why a second cluster and not a second database.** F3: "run a second Postgres cluster for
cuppy alone … with its own data directory, superuser, `pg_hba`, and backup schedule, bound
to localhost. Nothing from `datashield_gtm` or the other apps shares it." Sizing
(`shared_buffers`, `work_mem`) is then for cuppy's workload instead of competing with the
Library's cluster, and backups go to a separate target so one restore never touches the
other.

## Preconditions, all currently true on this host

| | checked |
|---|---|
| PostgreSQL 16 server binaries at `/usr/lib/postgresql/16/bin` | yes, 16.13 |
| `pg_createcluster` on `PATH` | yes, `/usr/bin/pg_createcluster` |
| **pgvector installed**, so `CREATE EXTENSION vector` needs no package work | yes, 0.6.0 |
| `ltree`, `pgcrypto` installed | yes, 1.2 and 1.3 |
| port 5433 free | confirm with `ss -ltnp \| grep 5433` before starting |

pgvector was the open question this runbook was written to answer. It is present, so there
is **no operator package item** — only the cluster, the roles, the extensions and
pgBackRest.

## 1. Create the cluster

```bash
sudo pg_createcluster 16 cuppy --port 5433 --start-conf manual
```

Then edit `/etc/postgresql/16/cuppy/postgresql.conf`. Only these lines differ from the
package default; every number is the operator's to tune, and none of them is read by the
application:

```
listen_addresses = '127.0.0.1'      # F3: bound to localhost
port = 5433
shared_buffers = 1GB                # sized for cuppy alone, not shared with datashield_gtm
work_mem = 16MB
maintenance_work_mem = 256MB
effective_cache_size = 3GB
wal_level = replica                 # pgBackRest needs replica or logical
archive_mode = on
archive_command = 'pgbackrest --stanza=cuppy archive-push %p'
max_wal_size = 4GB
log_min_duration_statement = 1000
```

`/etc/postgresql/16/cuppy/pg_hba.conf` — loopback only, scram on TCP, peer on the socket
so the bootstrap superuser needs no stored password:

```
local   all   postgres                 peer
host    all   all        127.0.0.1/32  scram-sha-256
host    all   all        ::1/128       scram-sha-256
```

```bash
sudo pg_ctlcluster 16 cuppy start
sudo systemctl enable postgresql@16-cuppy      # if the cluster should come back on boot
```

## 2. Roles, extensions and the database

One script, the same one the developer cluster runs, so dev and production cannot drift:
`scripts/db/bootstrap-roles.sql`. It creates `cuppy_rls` (nologin), the three login roles,
the database owned by `cuppy_migrate`, the `extensions` schema with `vector`, `ltree` and
`pgcrypto`, the per-role `search_path`, and it asserts at the end that no non-bootstrap
role holds `BYPASSRLS`.

Generate three passwords and keep them out of the shell history and out of git:

```bash
APP_PW=$(head -c 24 /dev/urandom | base64 | tr -d '\n=' | tr '+/' 'ab')
JOBS_PW=$(head -c 24 /dev/urandom | base64 | tr -d '\n=' | tr '+/' 'ab')
MIG_PW=$(head -c 24 /dev/urandom | base64 | tr -d '\n=' | tr '+/' 'ab')

sudo -u postgres /usr/lib/postgresql/16/bin/psql -p 5433 -d postgres \
  -v ON_ERROR_STOP=1 -f scripts/db/bootstrap-roles.sql \
  -v cuppy_db=cuppy \
  -v role_app=cuppy_app -v role_jobs=cuppy_jobs \
  -v role_migrate=cuppy_migrate -v role_rls=cuppy_rls \
  -v app_password="$APP_PW" -v jobs_password="$JOBS_PW" -v migrate_password="$MIG_PW"
```

The script prints `bootstrap complete` with the database name and the extension list.

**The role matrix it creates** (V3):

| role | connects | owns | may |
|---|---|---|---|
| `cuppy_app` | yes | nothing | DML on application tables, under RLS, with the V3 column grants. No DDL, no `SET ROLE` |
| `cuppy_jobs` | yes | nothing | what the `*_jobs_all` policies permit, plus the topology reads |
| `cuppy_migrate` | yes | every table `cuppy_rls` does not, `FORCE` on, so the owner is filtered too | DDL only, under one advisory lock |
| `cuppy_rls` | **no** | `hubs`, `hub_members`, `hub_grants`, `org_members`, `tenant_members`, `tenants` (RLS on, `FORCE` off) plus `breakglass_tickets` and every `app_*` function | nothing on its own. It is the identity the `SECURITY DEFINER` bodies run as |

`cuppy_migrate` is granted membership in `cuppy_rls`, which it needs to hand that role
ownership of the six tables and the functions. It is `NOINHERIT`, so a migration takes
those privileges only with an explicit `SET LOCAL ROLE`.

**No role in the cluster has `BYPASSRLS`, `cuppy_migrate` included.** The bootstrap
superuser exists for `CREATE EXTENSION` and role creation and appears in no connection
string. Z3 refuses to boot unless
`SELECT count(*) FROM pg_roles WHERE rolbypassrls AND rolname <> 'postgres'` returns zero.

## 3. Connection settings for the application

Into the vault, and into the service's environment as these names. **Never as
`DATABASE_URL`**: the ecosystem file pins that variable to `datashield_gtm`, and the
connection module refuses a DSN naming that database, port 5432, a non-loopback host, or
any role but the three (CLAUDE.md rule 3).

```
CUPPY_DATABASE_URL=postgresql://cuppy_app:<APP_PW>@127.0.0.1:5433/cuppy
CUPPY_JOBS_DATABASE_URL=postgresql://cuppy_jobs:<JOBS_PW>@127.0.0.1:5433/cuppy
CUPPY_MIGRATE_DATABASE_URL=postgresql://cuppy_migrate:<MIG_PW>@127.0.0.1:5433/cuppy
```

Secrets rotation is operator-hand (CUP-DEV-STD-001 §5). Re-running
`bootstrap-roles.sql` with new passwords is the rotation path; it is idempotent.

## 4. Apply the migrations

```bash
cd apps/api
CUPPY_MIGRATE_DATABASE_URL='postgresql://cuppy_migrate:<MIG_PW>@127.0.0.1:5433/cuppy' \
  npx tsx src/scripts/migrate.ts
```

Each file runs in one transaction holding `pg_advisory_xact_lock(1685026937)` — V3's key —
and the run holds `pg_advisory_lock` on the same session, so the backfill dance V3
describes (`NO FORCE` … backfill … `FORCE`) is under the lock as specified. The runner
records each filename with a sha256 and refuses to proceed if a landed migration's file
has changed. `--test` adds the gate-test fixtures and **must not be used on production**:
it creates `rls_probe*` tables.

Expected output on a fresh cluster: `applied 14, already present 0`.

## 5. pgBackRest (Z9)

Z9: "a daily base backup, continuous WAL archiving to object storage, fourteen-day
point-in-time recovery, thirty-five-day retention, a monthly restore drill into a scratch
cluster recorded in the ledger."

`/etc/pgbackrest/pgbackrest.conf` — a **separate repository path from the existing
cluster's**, which is the F3 requirement that one restore never touches the other:

```
[cuppy]
pg1-path=/var/lib/postgresql/16/cuppy
pg1-port=5433

[global]
repo1-path=/var/lib/pgbackrest/cuppy
repo1-retention-full=5
repo1-retention-diff=14
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=<generate, into the vault>
start-fast=y
compress-type=zst
archive-async=y
spool-path=/var/spool/pgbackrest
log-level-console=info
log-level-file=detail
```

```bash
sudo -u postgres pgbackrest --stanza=cuppy stanza-create
sudo -u postgres pgbackrest --stanza=cuppy check          # verifies archiving works
sudo -u postgres pgbackrest --stanza=cuppy --type=full backup
```

Schedule, as `postgres`:

```
15 2 * * 0   pgbackrest --stanza=cuppy --type=full backup
15 2 * * 1-6 pgbackrest --stanza=cuppy --type=diff backup
```

Retention: `repo1-retention-full=5` weekly fulls plus continuous WAL gives more than the
thirty-five days Z9 asks for and more than fourteen days of PITR. Adjust
`repo1-retention-*` if the object-storage target changes; the numbers above are the
operator's, not the application's.

**The monthly restore drill**, recorded in `tracking/metrics/ledger.jsonl`:

```bash
sudo -u postgres pgbackrest --stanza=cuppy --delta \
  --pg1-path=/var/lib/postgresql/16/drill restore
# start the scratch cluster on a free port, then:
#   select count(*) from hubs;  select max(applied_at) from schema_migrations;
```

Until the cutover, `scripts/backup-sqlite.sh` keeps the nightly `.backup` of
`storage/app.db` with seven copies (Z9's interim half, FEAT-005).

## 6. Verify, and what to paste back

```bash
sudo -u postgres /usr/lib/postgresql/16/bin/psql -p 5433 -d cuppy -X -c "select version()"
sudo -u postgres /usr/lib/postgresql/16/bin/psql -p 5433 -d cuppy -X -c \
  "select extname, extversion, extnamespace::regnamespace from pg_extension order by 1"
sudo -u postgres /usr/lib/postgresql/16/bin/psql -p 5433 -d cuppy -X -c \
  "select rolname, rolcanlogin, rolbypassrls from pg_roles where rolname like 'cuppy%' order by 1"
sudo -u postgres /usr/lib/postgresql/16/bin/psql -p 5433 -d cuppy -X -c \
  "select c.relname, c.relrowsecurity, c.relforcerowsecurity, c.relowner::regrole
     from pg_class c join pg_namespace n on n.oid = c.relnamespace
    where n.nspname = 'public' and c.relkind in ('r','p') order by 1"
sudo -u postgres pgbackrest --stanza=cuppy info
```

Four things must be true before S1 closes, and the outputs above are the evidence
(CUP-DEV-STD-001 R5 — "verified" as free text is banned):

1. `vector`, `ltree` and `pgcrypto` are installed **in the `extensions` schema**.
2. `rolbypassrls` is false for every `cuppy*` role.
3. `relforcerowsecurity` is false on exactly the six tables whose owner is `cuppy_rls`,
   and true on every other RLS table.
4. `pgbackrest info` shows one full backup and WAL archiving `ok`.

## Rollback

```bash
sudo pg_ctlcluster 16 cuppy stop
sudo pg_dropcluster 16 cuppy --stop
sudo rm -rf /var/lib/pgbackrest/cuppy
```

Nothing on the shared cluster is touched by any step in this runbook, and no application
reads the cuppy DSNs until they are set, so a rollback before step 3 is invisible to
production.


---
<!-- cuppy -->
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/
<!-- /cuppy -->