postgres-admin

What this skill does

# PostgreSQL Administration (DBA / Server Operations)

## Overview

This skill covers **operating a PostgreSQL server**: configuring it, securing access,
backing it up, replicating it, upgrading it, and watching it run. It is about the
**server and cluster**, not about writing application SQL.

**Mental model — the cluster hierarchy:**

```
PostgreSQL instance (one postmaster, one data directory $PGDATA, one port)
└── cluster = all databases managed by that instance
    ├── global objects: roles/users, tablespaces, replication slots  (shared, cluster-wide)
    └── database  →  schema  →  object (table/view/sequence/function)
```

- **One running server** (`postgres`/postmaster) serves **many databases**; roles and
  tablespaces are **cluster-global**, not per-database.
- Configuration lives in **`postgresql.conf`** + **`postgresql.auto.conf`** in `$PGDATA`;
  authentication in **`pg_hba.conf`**; runtime state in the **`pg_stat_*`** views.
- The server is a **long-running daemon**: most admin actions are *reload* (cheap, no
  downtime) or, for a minority of settings, *restart* (brief downtime).
- Write-ahead log (**WAL**) is the foundation of crash recovery, backups (PITR), and
  replication — almost everything operational ties back to it.

## Related skills (disambiguation)

| If you need… | Use skill |
|---|---|
| **Server config, roles/auth, backups, replication, upgrades, monitoring** | **postgres-admin** (this skill) |
| The `psql` client, meta-commands (`\d`, `\dt`), scripting the shell | `psql` |
| SQL syntax, DDL/DML, data types, functions, query writing | `postgres-sql` |
| Query tuning, `EXPLAIN`, indexes, **autovacuum mechanics/tuning** | `postgres-performance` |
| contrib extensions (`pg_stat_statements`, `postgis`, `pgcrypto`, …) | `postgres-extensions` |

> Overlap notes: this skill covers the **server-config / GUC** side of vacuum, checkpoints,
> and WAL (the knobs and what they mean operationally); deep autovacuum/query tuning lives in
> `postgres-performance`. `pg_stat_statements` is a contrib module — monitoring with it is in
> `postgres-extensions`; this skill points you at the built-in `pg_stat_*` views.

## Version awareness

PostgreSQL ships **one major version per year** (pg10 = 2017 … **pg18 = 2025 stable**,
**pg19 = 2026 beta**). This skill tags features added in **PostgreSQL 10 or later** inline as
`(pgNN+)` **only where a release note sources it**; anything from the **9.x era or earlier is
bedrock** and left untagged. Full sourced map: [references/version-features.md](references/version-features.md).

**Check what you're running** (annotations are a hard floor — a `(pg17+)` feature simply does
not exist on pg16):

```bash
psql -c "SHOW server_version"          # e.g. 18.1
psql -c "SELECT version()"             # full build string
postgres --version                     # the server binary
pg_config --version                    # build/devel version
psql -c "SHOW server_version_num"      # 180001 → easy to compare numerically
```

## 1. Server configuration

Settings are **GUCs** (Grand Unified Configuration). Three layers, last-wins:
`postgresql.conf` → `postgresql.auto.conf` (written by `ALTER SYSTEM`) → per-session `SET`.

```sql
SHOW shared_buffers;                       -- one setting
SHOW all;                                  -- everything
SELECT name, setting, unit, context, pending_restart
  FROM pg_settings WHERE name = 'work_mem';
SELECT name, setting, source, sourcefile, sourceline   -- where did this value come from?
  FROM pg_settings WHERE source NOT IN ('default','override');

ALTER SYSTEM SET work_mem = '64MB';        -- persists to postgresql.auto.conf (since 9.4)
ALTER SYSTEM RESET work_mem;               -- remove the override
SELECT pg_reload_conf();                   -- apply reload-able changes, no downtime
SET work_mem = '128MB';                    -- this session only
RESET work_mem;                            -- back to the configured value
```

```bash
pg_ctl reload   -D $PGDATA     # SIGHUP — apply reloadable settings (also: systemctl reload)
pg_ctl restart  -D $PGDATA     # required for some settings (see below)
```

**Reload vs restart** — a setting's `context` in `pg_settings` tells you:

| `context` | How to apply | Examples |
|---|---|---|
| `user` / `superuser` | `SET` in-session, or reload | `work_mem`, `statement_timeout` |
| `sighup` | **reload** (`pg_reload_conf()`) | `log_*`, `autovacuum`, `archive_command`, `checkpoint_timeout`, `max_wal_size` |
| `postmaster` | **restart** | `shared_buffers`, `max_connections`, `listen_addresses`, `port`, `wal_level`, `shared_preload_libraries` |

After editing, `pending_restart = true` in `pg_settings` flags settings that need a restart.
`ALTER SYSTEM` **cannot** set a few bootstrap params (e.g. it warns on settings that have no
effect post-start). Full GUC tour, key parameters, and units: [references/config.md](references/config.md).

## 2. Roles & privileges

Roles are **cluster-global**. A "user" is just a role `WITH LOGIN`.

```sql
CREATE ROLE app LOGIN PASSWORD 'secret';                 -- a login user
CREATE ROLE readonly NOLOGIN;                            -- a group role
CREATE ROLE deployer LOGIN CREATEDB CREATEROLE;
ALTER ROLE app VALID UNTIL '2027-01-01' CONNECTION LIMIT 20;
ALTER ROLE app SET search_path = app, public;            -- per-role GUC default

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; -- existing tables only
ALTER DEFAULT PRIVILEGES IN SCHEMA public                -- future tables too
  GRANT SELECT ON TABLES TO readonly;
GRANT readonly TO app;                                   -- role membership

REVOKE INSERT ON orders FROM app;
```

- **Privileges don't apply retroactively** — `GRANT … ON ALL TABLES` covers tables that exist
  *now*; use `ALTER DEFAULT PRIVILEGES` for objects created *later* (and it's per-creator).
- **`public` schema hardening (pg15+):** new clusters **no longer grant `CREATE` on `public`
  to `PUBLIC`** (CVE-2018-1058 mitigation). On upgraded clusters, consider
  `REVOKE CREATE ON SCHEMA public FROM PUBLIC` yourself.
- **Predefined roles** grant capabilities without superuser: `pg_monitor`,
  `pg_read_all_settings`, `pg_read_all_stats`, `pg_signal_backend`, `pg_read_all_data` /
  `pg_write_all_data` (pg14+), `pg_database_owner` (pg14+), `pg_checkpoint` (pg15+),
  `pg_use_reserved_connections` (pg16+), `pg_create_subscription` (pg16+), `pg_maintain`
  (pg17+, with the `MAINTAIN` privilege), `pg_signal_autovacuum_worker` (pg18+).
- **Role membership options (pg16+):** `GRANT g TO u WITH INHERIT {TRUE|FALSE}, SET
  {TRUE|FALSE}, ADMIN {TRUE|FALSE}` — membership inheritance is now per-grant, not just the
  member's `INHERIT` attribute. (Pre-16, new memberships always inherited per the member role.)

Full role attribute / GRANT / object-privilege reference: [references/auth-roles.md](references/auth-roles.md).

## 3. Authentication (`pg_hba.conf`)

`pg_hba.conf` is matched **top-to-bottom, first match wins** (no fall-through). Format:

```
# TYPE   DATABASE  USER       ADDRESS            METHOD
local    all       all                           peer            # Unix socket, OS-user match
host     all       all        127.0.0.1/32       scram-sha-256    # TCP, localhost
hostssl  app       app        10.0.0.0/8         scram-sha-256    # require TLS
host     all       all        0.0.0.0/0          reject           # deny the rest
```

```bash
psql -c "SELECT pg_reload_conf()"          # pg_hba changes apply on RELOAD, not restart
psql -c "TABLE pg_hba_file_rules"          # see parsed rules + any errors (pg10+)
psql -c "TABLE pg_ident_file_mappings"     # parsed pg_ident.conf (pg15+)
```

- **Methods:** `scram-sha-256` (pg10+, the modern default — set `password_encryption =
  scram-sha-256`, the **default since pg14**), `md5` (legacy, **deprecated pg18+**), `peer`
  (local OS user), `cert` (TLS client cert), `ldap`, `gss`/`sspi`, `radius`, `trust` (no
  auth — dev only), `reject`, and `oauth` (pg18+