mrcharles
0e67f17112
Enforcement (check-rules/hooks/shellcheck) per ADOPTING pattern; perf/ SoR from the #685 session (memcensus, pg role-caps SQL, applied sysctl, planned daemon.json); questions-v1 (API token + dockerd window asks). Meat + verification: https://projects.knownelement.com/issues/727#note-3913
23 lines
1.2 KiB
SQL
23 lines
1.2 KiB
SQL
-- perf/pg-role-caps.sql — platform Postgres role caps + scaling, tsys-cloudron [#685]
|
|
--
|
|
-- Applied live 2026-09-02 (canary-first: booking.roguetechnologies.net alone,
|
|
-- verified 61 -> 10 conns + app 200, then the rest). This file is the
|
|
-- idempotent re-apply SoR for IaC. Narrative + measurements: Discourse t/316.
|
|
-- Rollback (instant, no restarts): ALTER ROLE <username> CONNECTION LIMIT -1;
|
|
--
|
|
-- Run inside the postgresql container:
|
|
-- docker exec -i postgresql su - postgres -c 'psql' < perf/pg-role-caps.sql
|
|
--
|
|
-- Re-run safe: the \gexec block only caps app roles (rolname LIKE 'user%')
|
|
-- still at -1, so new apps get the default 20-cap as they are added.
|
|
-- Also already persisted (postgresql.auto.conf, survives restarts):
|
|
-- ALTER SYSTEM SET shared_buffers = '2GB'; -- needed the two 18:0x CDT restarts
|
|
|
|
SELECT 'ALTER ROLE ' || rolname || ' CONNECTION LIMIT 20;'
|
|
FROM pg_roles WHERE rolname LIKE 'user%' AND rolconnlimit = -1
|
|
\gexec
|
|
ALTER SYSTEM SET max_connections = 700;
|
|
ALTER SYSTEM SET idle_in_transaction_session_timeout = '15min';
|
|
SELECT rolname, rolconnlimit FROM pg_roles WHERE rolconnlimit <> -1 ORDER BY rolconnlimit, rolname;
|
|
SHOW max_connections;
|