---
name: administering-postgresql
description: 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.
---

<!--
Author: Simon-Pierre Boucher
Contact: contact@spboucher.ai
-->

# Administering PostgreSQL

## When to use / when NOT to use
- **Use for:** server-side PostgreSQL work — roles/privileges, `postgresql.conf` tuning, autovacuum, monitoring, extensions, upgrades.
- **Do NOT use for:** writing application SQL (`writing-sql-queries`), query tuning (`optimizing-sql-performance`), backup/restore strategy (`backing-up-databases`), or MySQL/SQLite.

## Core rules

1. **Roles: one login role per human/service, privileges via group roles.**
   - ✅ `CREATE ROLE app_rw NOLOGIN; GRANT app_rw TO svc_api;`
   - ❌ Granting table privileges directly to a dozen login roles.
2. **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.
3. **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.
4. **Never raise `max_connections` to fix connection errors — add a pooler.** PgBouncer in transaction mode with `max_connections` ≤ 200 beats 2000 raw connections.
   - ✅ App → PgBouncer (pool_size 20) → Postgres.
   - ❌ `max_connections = 5000`.
5. **Never disable autovacuum.** For hot tables, tune per-table instead: `ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.02);`
6. **Measure before touching anything:** enable `pg_stat_statements` (`shared_preload_libraries`), and use `pg_stat_activity` / bloat queries from the reference file.
7. **Extensions go through migrations,** not psql one-offs: `CREATE EXTENSION IF NOT EXISTS pg_trgm;` in a versioned migration so every environment matches.
8. **Upgrades: `pg_upgrade --link` for same-host major upgrades; logical replication when downtime must be near zero.** Always run `ANALYZE` after either.

## Workflow

1. 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`.
2. Apply the smallest change that addresses the goal (one knob or one grant at a time).
3. 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;`).
4. Validate: re-run the measurement from step 1 and confirm the change took effect (`SHOW shared_buffers;`, `\du`, `\l+`) and improved the metric.
5. Record what changed and why in the project's migration/ops notes.

## Edge cases & failure modes
- **`pg_stat_statements` missing** → it needs `shared_preload_libraries = 'pg_stat_statements'` + restart, then `CREATE EXTENSION pg_stat_statements;`.
- **Permission denied after GRANT** → almost always missing `GRANT USAGE ON SCHEMA` or missing default privileges (rule 2).
- **Config change has no effect** → check `pg_settings.pending_restart`; some knobs (e.g. `shared_buffers`) need a full restart.
- **Managed Postgres (RDS/Cloud SQL)** → no filesystem or `postgresql.conf`; use parameter groups / flags, and superuser is unavailable — use the provider's admin role.
- **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.

## References
Copy-paste monitoring SQL, role templates, and upgrade commands: see [references/patterns.md](references/patterns.md).