spb/ultra-sharp-agent-skills Public

1---2name: administering-postgresql3description: Administers PostgreSQL servers — roles and privileges, configuration tuning with sane starting values, autovacuum, monitoring with pg_stat_statements, extensions, and version upgrades. Use when the user asks to configure or tune a Postgres server, create roles or grant permissions, fix autovacuum or bloat, monitor a Postgres instance, install an extension, or plan a Postgres upgrade. Do not use for writing or tuning application queries, backup strategy, or other database engines.4---56<!--7Author: Simon-Pierre Boucher8Contact: contact@spboucher.ai9-->1011# Administering PostgreSQL1213## When to use / when NOT to use14- **Use for:** server-side PostgreSQL work — roles/privileges, `postgresql.conf` tuning, autovacuum, monitoring, extensions, upgrades.15- **Do NOT use for:** writing application SQL (`writing-sql-queries`), query tuning (`optimizing-sql-performance`), backup/restore strategy (`backing-up-databases`), or MySQL/SQLite.1617## Core rules18191. **Roles: one login role per human/service, privileges via group roles.**20   - ✅ `CREATE ROLE app_rw NOLOGIN; GRANT app_rw TO svc_api;`21   - ❌ Granting table privileges directly to a dozen login roles.222. **New objects need default privileges, not just `GRANT`.** `GRANT` covers existing tables only; run `ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_ro;` for future ones.233. **Config starting values, then measure:** `shared_buffers` = 25% of RAM (cap ~8GB before measuring), `effective_cache_size` = 50–75% of RAM, `work_mem` = 16–64MB — it is **per sort/hash per query**, so `max_connections × work_mem` must fit in RAM.244. **Never raise `max_connections` to fix connection errors — add a pooler.** PgBouncer in transaction mode with `max_connections` ≤ 200 beats 2000 raw connections.25   - ✅ App → PgBouncer (pool_size 20) → Postgres.26   - ❌ `max_connections = 5000`.275. **Never disable autovacuum.** For hot tables, tune per-table instead: `ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02);`286. **Measure before touching anything:** enable `pg_stat_statements` (`shared_preload_libraries`), and use `pg_stat_activity` / bloat queries from the reference file.297. **Extensions go through migrations,** not psql one-offs: `CREATE EXTENSION IF NOT EXISTS pg_trgm;` in a versioned migration so every environment matches.308. **Upgrades: `pg_upgrade --link` for same-host major upgrades; logical replication when downtime must be near zero.** Always run `ANALYZE` after either.3132## Workflow33341. State the goal (new role, config change, slow server, extension, upgrade) and capture current state first: version, `SELECT * FROM pg_settings WHERE source <> 'default';`, top queries from `pg_stat_statements`.352. Apply the smallest change that addresses the goal (one knob or one grant at a time).363. Reload or restart as required: `SELECT pg_reload_conf();` for reloadable settings; note in your reply if a restart is required (`SELECT name FROM pg_settings WHERE pending_restart;`).374. Validate: re-run the measurement from step 1 and confirm the change took effect (`SHOW shared_buffers;`, `\du`, `\l+`) and improved the metric.385. Record what changed and why in the project's migration/ops notes.3940## Edge cases & failure modes41- **`pg_stat_statements` missing** → it needs `shared_preload_libraries = 'pg_stat_statements'` + restart, then `CREATE EXTENSION pg_stat_statements;`.42- **Permission denied after GRANT** → almost always missing `GRANT USAGE ON SCHEMA` or missing default privileges (rule 2).43- **Config change has no effect** → check `pg_settings.pending_restart`; some knobs (e.g. `shared_buffers`) need a full restart.44- **Managed Postgres (RDS/Cloud SQL)** → no filesystem or `postgresql.conf`; use parameter groups / flags, and superuser is unavailable — use the provider's admin role.45- **Out-of-disk from bloat** → do NOT run `VACUUM FULL` on a hot table in peak hours (exclusive lock); use `pg_repack` or schedule a window.4647## References48Copy-paste monitoring SQL, role templates, and upgrade commands: see [references/patterns.md](references/patterns.md).49