Legendary Arena Lab

PostgreSQL Setup

wiki

PostgreSQL Setup

Migration in progress. api.legendary-arena.com and its PostgreSQL are moving off Render onto a self-hosted DigitalOcean Ubuntu droplet fronted by Cloudflare, per docs/PLAN.md . Until decommission, Render stays warm as the rollback target.

Summary

Provisions the self-hosted PostgreSQL 18 instance that backs the app. The database runs on localhost co-located with the server, so there is no cross-provider hop between app and data. Tuning targets the 8 GB droplet.

Mechanics

The owning artifact infra/scripts/30-postgres.ps1 requires root and requires LA_DB_PASSWORD (it throws if unset). It runs idempotently in five stages:

  1. PGDG repo — if the postgresql-<major> package isn’t already available, it adds the PostgreSQL PGDG apt repo: installs the ACCC4CF8 signing key and writes /etc/apt/sources.list.d/pgdg.list for the detected Ubuntu codename.

  2. Install PG18 — installs postgresql-18 plus postgresql-client-18 and enables the service.

  3. Configure — edits /etc/postgresql/18/main/postgresql.conf, setting localhost-only listen, the port, scram encryption, and baseline tuning for an 8 GB box:

    listen_addresses = 'localhost'
port = 5432
password_encryption = 'scram-sha-256'
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
wal_buffers = 16MB
max_wal_size = 4GB
min_wal_size = 1GB

  4. pg_hba scram — ensures pg_hba.conf has scram-sha-256 host lines for 127.0.0.1/32 and ::1/128, then restarts postgres.

  5. Create role + db — creates or updates role la (LOGIN + password) and database la OWNER la using IF NOT EXISTS guards, then prints the DATABASE_URL hint.

Environment overrides: PG_MAJOR (18), LA_DB_NAME (la), LA_DB_USER (la), LA_DB_PASSWORD (required), LA_DB_PORT (5432). Co-location matches the plan’s “server + DB co-located, no cross-provider hop” (docs/PLAN.md ).

Interactions

  • Deploy Server — consumes the resulting DATABASE_URL and runs the app’s migrations against this database.
  • Secrets and Env — DATABASE_URL (postgres://la:...@localhost:5432/la) is one of the values in the env contract, stored in /etc/la/.env.
  • Render-to-DigitalOcean Migration — self-hosting the DB is a core migration goal; backups and restore are handled by separate planned scripts (50/51), not yet present.

Edge Cases

  • LA_DB_PASSWORD is required — the script throws if it is unset, so no role is ever created with an empty or default password.
  • listen_addresses = 'localhost' means NO remote DB connections are accepted; this is by design because the app is co-located on the same box.
  • The 8 GB tuning block assumes the 8 GB droplet — revisit shared_buffers/effective_cache_size if you step down to a 4 GB box per docs/PLAN.md .
  • During the Phase 3 dress rehearsal you restore a COPY of prod and never point the build at the live prod DB (docs/PLAN.md ).
  • scram-sha-256 requires clients that support it; older drivers negotiating md5 will fail to authenticate.

Execute

# Provision PostgreSQL 18 (root required; password is mandatory)
sudo LA_DB_PASSWORD='change-me' pwsh -File infra/scripts/30-postgres.ps1

# With non-default name / user / port
sudo LA_DB_NAME='la' LA_DB_USER='la' LA_DB_PORT='5432' \
     LA_DB_PASSWORD='change-me' pwsh -File infra/scripts/30-postgres.ps1

Verify

# Role la exists
sudo -u postgres psql -c '\du'
# Expected: a row listing role "la" with the Login attribute

# Server is accepting connections on localhost
pg_isready -h localhost
# Expected: localhost:5432 - accepting connections

# Database la exists
sudo -u postgres psql -l | grep la
# Expected: a line for database "la" owned by la

References