PostgreSQL Setup
Migration in progress. api.legendary-arena.com and PostgreSQL are moving from Render to a self-hosted DigitalOcean Ubuntu host behind Cloudflare. Render remains rollback-ready until decommission phase.
PostgreSQL Setup
Install PostgreSQL 18 locally on the API host, lock network exposure to localhost, create app role/database, and apply baseline tuning that scales to the host’s RAM (1 GB shared_buffers on the 4 GB droplet).
Summary
This page owns infra/scripts/30-postgres.ps1 .
The script:
- installs PostgreSQL 18
- enables and starts service
- enforces localhost-only listen
- sets initial memory and WAL tuning values
- adds
pg_stat_statementstoshared_preload_libraries, merging with any library already listed, and checks that the running server loaded it - creates role and database, and the
pg_stat_statementsextension in it aspostgres(the extension isn’t trusted, so the app role can’t create it)
Every run restarts PostgreSQL. A re-run is safe (each setting is set, not appended), but on the live box it’s a quiet-hour step: la-server stays up through the restart, but requests in flight for those few seconds fail.
Query statistics
Production carried the pg_stat_statements extension over from Render, but until the library was preloaded the view collected nothing. With it loaded:
sudo -u postgres psql -d la -c "select calls, round(total_exec_time) as ms, left(query, 80) from pg_stat_statements order by total_exec_time desc limit 10"
It holds a fixed block of shared memory sized by pg_stat_statements.max (5,000 statements by default). select pg_stat_statements_reset() starts a fresh window.
Execute
Run as root with explicit database password:
sudo LA_DB_PASSWORD='replace-me' pwsh -File infra/scripts/30-postgres.ps1
Optional overrides:
sudo LA_DB_NAME=la LA_DB_USER=la LA_DB_PASSWORD='replace-me' LA_DB_PORT=5432 pwsh -File infra/scripts/30-postgres.ps1
Verify
sudo systemctl status postgresql --no-pager
sudo -u postgres psql -c "\l"
sudo -u postgres psql -c "\du"
sudo ss -ltnp | grep 5432
sudo -u postgres psql -tAc 'SHOW shared_preload_libraries'
Expected:
- service active
- role and database exist
shared_preload_librariesincludespg_stat_statements- port 5432 bound only to localhost addresses
Environment contract
Set app connection string using localhost:
DATABASE_URL=postgres://la:replace-me@localhost:5432/la
Safety note
Do not run app builds or migration experiments against production data directly during rehearsal. Always use restored copies until formal cutover.
References
- Plan section 4.4 postgres-setup page stub
- Plan section 5.1 database base checklist