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. Memory tuning follows the droplet’s RAM.

Mechanics

The owning artifact infra/scripts/30-postgres.ps1 requires root and LA_DB_PASSWORD, refuses database or role names that are not plain lower-case identifiers, and stops on the first failing command. It is idempotent. Stages:

  1. PGDG repo — if postgresql-<major> is not yet available, it downloads the ACCC4CF8 signing key with curl -o and dearmors it with gpg -o. The key never passes through the PowerShell pipeline, which would corrupt the binary. Then it writes /etc/apt/sources.list.d/pgdg.list for the Ubuntu codename.

  2. Install PG18 — postgresql-18 + postgresql-client-18, service enabled.

  3. Configure — localhost-only listen, the port, scram passwords, and memory settings sized from the machine’s RAM, so a droplet rehearsed small and resized up before cutover gets the right values on a re-run:

    listen_addresses = 'localhost'
    port = 5432
    password_encryption = 'scram-sha-256'
    shared_buffers = <25% of RAM>          # 2048MB on 8 GB
    effective_cache_size = <75% of RAM>    # 6144MB on 8 GB
    maintenance_work_mem = <RAM/16, max 1GB>
    wal_buffers = 16MB
    max_wal_size = 4GB
    min_wal_size = 1GB
    
  4. pg_hba scram — scram-sha-256 host lines for 127.0.0.1/32 and ::1/128, then a restart.

  5. Role + database — runs as postgres through psql’s \gexec:

    • CREATE ROLE la LOGIN only if missing, then always ALTER ROLE … PASSWORD, so a re-run rotates the password;
    • CREATE DATABASE la OWNER la only if missing. CREATE DATABASE cannot run inside a DO block, which is why the old version could not work.

    Values are passed as psql variables over stdin (not argv) and quoted by format(%I, %L).

  6. Login check — connects as la over 127.0.0.1 with the password, proving the role, database, and pg_hba rules work together. Then it 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 ). The provision smoke workflow runs it twice with a password containing a quote, a double quote, and a backslash.

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.
  • Resizing the droplet. Memory settings are computed from RAM at run time. After a resize (rehearse small, cut over at 8 GB, or step down to 4 GB later), re-run the script to re-size them; it also restarts Postgres.
  • 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