# 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.

# 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.