Legendary Arena Lab

PostgreSQL Setup

data

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_statements to shared_preload_libraries, merging with any library already listed, and checks that the running server loaded it
  • creates role and database, and the pg_stat_statements extension in it as postgres (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_libraries includes pg_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