PostgreSQL Setup
PostgreSQL Setup
Migration in progress.
api.legendary-arena.comand its PostgreSQL are moving off Render onto a self-hosted DigitalOcean Ubuntu droplet fronted by Cloudflare, perdocs/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:
PGDG repo — if
postgresql-<major>is not yet available, it downloads theACCC4CF8signing key withcurl -oand dearmors it withgpg -o. The key never passes through the PowerShell pipeline, which would corrupt the binary. Then it writes/etc/apt/sources.list.d/pgdg.listfor the Ubuntu codename.Install PG18 —
postgresql-18+postgresql-client-18, service enabled.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 = 1GBpg_hba scram —
scram-sha-256host lines for127.0.0.1/32and::1/128, then a restart.Role + database — runs as
postgresthrough psql’s\gexec:CREATE ROLE la LOGINonly if missing, then alwaysALTER ROLE … PASSWORD, so a re-run rotates the password;CREATE DATABASE la OWNER laonly if missing.CREATE DATABASEcannot run inside aDOblock, 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).Login check — connects as
laover127.0.0.1with the password, proving the role, database, andpg_hbarules work together. Then it prints theDATABASE_URLhint.
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_URLand 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_PASSWORDis 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-256requires clients that support it; older drivers negotiatingmd5will 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
infra/scripts/30-postgres.ps1— owning provisioning script.docs/PLAN.md— migration plan, co-location and 8 GB box facts, Phase 3 dress-rehearsal rule.content/data/postgres-setup.md— source operator page.- Deploy Server
— downstream consumer of
DATABASE_URL. - Secrets and Env
— where
DATABASE_URLlives in the env contract.