PostgreSQL for operators¶
Status: AI draft, 2026-11-16. Not yet verified by me. Commands were run in a throwaway
postgres:18container (PostgreSQL 18.7) and the output below is real; replication and PgBouncer are from the documentation only.
In one line¶
An operator's PostgreSQL: connect with psql and the right sslmode; grant through roles, schema usage and default privileges; keep connections under max_connections with a pooler; back up logically with pg_dump or physically with base backups plus WAL for point-in-time recovery; and find slow or blocked work with pg_stat_activity, locks and EXPLAIN.
The plain idea¶
You do not need to be a database administrator to run PostgreSQL well, but you will be the one paged when the app says "too many connections", "permission denied" or "the database is slow". Each of those maps to a few views and commands. This page is that kit; RDS and Aurora change who patches and backs up, not these questions (RDS and Aurora).
How it works¶
flowchart TD
APP["App instances<br/>(many connections)"] --> PB["PgBouncer<br/>(pool)"]
PB --> PRI["Primary<br/>(one process per connection)"]
PRI -->|"WAL stream"| REP["Standby replica<br/>(read-only, failover target)"]
PRI -->|"WAL archive"| ARC["Archive (S3)"]
BB["Base backup<br/>(nightly)"] --> ARC
ARC -->|"restore + replay WAL<br/>to a point in time"| NEW["Restored server"]
Connecting¶
psql "host=db.internal.example.com port=5432 dbname=app user=app_ro sslmode=verify-full sslrootcert=/etc/ssl/certs/rds-ca.pem"
sslmodedecides whether TLS is used and checked. The default is prefer: use TLS if the server offers it, but silently fall back and never verify the certificate. Useverify-full(encrypt and check the certificate and hostname) for anything over a network (TLS and certificates).- With
require, a server without TLS refuses the connection. Real output from the container, which has TLS off:
psql: error: connection to server at "127.0.0.1", port 5432 failed: server does not support SSL, but SSL was required
Roles and privileges¶
PostgreSQL has only roles; a "user" is a role that can log in. Access is layered: the role must be able to connect to the database, use the schema, and have privileges on each table (and on sequences for inserts that use them). The usual pattern is an owner role that owns objects, plus read-write and read-only roles:
create role app_owner nologin;
create role app_rw login password 'example-only';
create role app_ro login password 'example-only';
create schema shop authorization app_owner;
grant usage on schema shop to app_rw, app_ro;
grant select, insert, update, delete on all tables in schema shop to app_rw;
grant select on all tables in schema shop to app_ro;
alter default privileges for role app_owner in schema shop grant select on tables to app_ro;
alter default privileges for role app_owner in schema shop grant select, insert, update, delete on tables to app_rw;
GRANT ... ON ALL TABLES covers only tables that exist now. Default privileges cover tables app_owner creates later; without them, every migration silently locks the app out of new tables. The result, as \dp shows it:
Schema | Name | Type | Access privileges |
--------+--------+-------+------------------------------+
shop | orders | table | app_owner=arwdDxtm/app_owner+|
| | | app_rw=arwd/app_owner +|
| | | app_ro=r/app_owner |
The letters are privileges: r select, a insert, w update, d delete, D truncate, x references, t trigger, m maintain. Two classic errors, both real:
app_rw> insert into shop.orders (customer, amount) values ('cust-1', 10);
ERROR: permission denied for sequence orders_id_seq
app_ro> delete from shop.orders where id = 1;
ERROR: permission denied for table orders
The first is fixed with grant usage on all sequences in schema shop to app_rw (plus the matching default privilege); after that the insert returns INSERT 0 1. The second is the read-only role doing its job.
Connections and pooling¶
- Each connection is a server process with its own memory.
max_connectionswas 100 in the container (9 of 100 connections in useat rest, counting background processes). Raising it costs memory per connection and needs a restart. - Apps with many instances, or Lambda functions, each holding a small pool, add up fast. A pooler shares a few server connections among many clients.
- PgBouncer pool modes: session (a server connection per client connection; all features work), transaction (a server connection only for the length of a transaction; far fewer server connections, but session state such as
SET, advisory locks and some prepared-statement setups do not carry over), statement (rarely used). On AWS, RDS Proxy plays this role.
Replication and failover, in outline¶
- Streaming replication ships the write-ahead log (WAL) from the primary to standbys, which replay it and can serve reads. Asynchronous by default (a failover can lose the last transactions); synchronous replication waits for a standby to confirm.
- Failover promotes a standby; something must decide when (Patroni, a Kubernetes operator, or the managed service) and move clients to the new primary (DNS, a virtual IP, or the pooler).
- Logical replication copies changes per table, between major versions too, which makes it a tool for low-downtime upgrades and migrations (Migration patterns).
Backups¶
| Kind | Tool | Good for | Limits |
|---|---|---|---|
| Logical | pg_dump (one database), pg_dumpall --globals-only (roles) |
Moving data, single-table restores, version upgrades | Slow for large databases; a snapshot at one moment only |
| Physical | pg_basebackup or storage snapshots, plus archived WAL |
Full restores and point-in-time recovery | Same major version; whole cluster |
pg_dump does not include roles; dump them separately with pg_dumpall --globals-only or a restore will fail on missing owners. A custom-format dump lists its contents (real output, trimmed):
$ pg_dump -U postgres -d app -Fc -f /tmp/app.dump
$ pg_restore --list /tmp/app.dump
6; 2615 16388 SCHEMA - shop app_owner
221; 1259 16390 TABLE shop orders app_owner
3445; 0 16390 TABLE DATA shop orders app_owner
3296; 2606 16400 CONSTRAINT shop orders orders_pkey app_owner
3294; 1259 16403 INDEX shop orders_customer_idx app_owner
Point-in-time recovery restores a base backup and replays archived WAL up to a chosen moment, for example just before a bad DROP TABLE. A backup you have never restored is a hope, not a backup (Backup and DR).
Seeing what is slow or stuck¶
pg_stat_activity shows every session. Here one transaction holds a row lock while sleeping, and a second session waits for it (real output, trimmed):
pid | application_name | state | wait_event_type | wait_event | blocked_by | running_for
-----+------------------+--------+-----------------+---------------+------------+----------------
140 | psql | active | Timeout | PgSleep | {} | 00:00:04.011558
147 | report-job | active | Lock | transactionid | {140} | 00:00:02.007331
pg_blocking_pids(147) returned {140}: find what 140 is doing, then pg_cancel_backend(140) (cancel its query) or pg_terminate_backend(140) (end the session) if it must go. Watch also for idle in transaction sessions: they hold locks and block vacuum.
EXPLAIN ANALYZE runs a query and shows the plan with real timings. Before and after an index on 50,000 rows (PostgreSQL 18 includes buffer counts by default):
Seq Scan on orders (cost=0.00..993.01 rows=99 width=30) (actual time=0.018..3.843 rows=100.00 loops=1)
Filter: (customer = 'cust-42'::text)
Rows Removed by Filter: 49901
Execution Time: 3.886 ms
Bitmap Heap Scan on orders (cost=5.06..229.20 rows=99 width=30) (actual time=0.121..0.238 rows=100.00 loops=1)
Recheck Cond: (customer = 'cust-42'::text)
-> Bitmap Index Scan on orders_customer_idx (cost=0.00..5.03 rows=99 width=0) (actual time=0.083..0.083 rows=100.00 loops=1)
Execution Time: 0.326 ms
"Rows Removed by Filter: 49901" is the tell: the database read everything to return 100 rows.
What a managed service changes¶
RDS or Aurora take over installation, minor patching, backups and PITR, replication and failover, and the superuser: you get a rds_superuser-style admin role instead. You still own roles and grants, connection counts, indexes and queries, parameter tuning, major upgrades and restore tests.
A concrete example¶
sample-app on PostgreSQL in 10.0.0.0/16:
- Roles
app_owner(migrations),app_rw(the service),app_ro(reports), with default privileges so new tables work for both. - The service connects through PgBouncer in transaction mode with
sslmode=verify-full; reports connect to a read replica. - Nightly physical backups plus continuous WAL archiving; a monthly restore test into a scratch instance;
pg_dumpall --globals-onlywith every backup. - Alerts on connections above 80 percent of
max_connections, replication lag, and transactions idle for more than 5 minutes. - Slow query log (
log_min_duration_statement) feeds a weekly review of the worst queries withEXPLAIN ANALYZE.
Why not just...?¶
- ...connect as the
postgressuperuser from the app? Any bug or injection then has full control, including dropping the database. Use the least-privileged role. - ...raise
max_connectionsto 2,000? Each connection is a process with memory; past a point throughput falls. Pool instead. - ...rely on
pg_dumpfor a 2 TB database? Dumps and restores take hours and give no point-in-time recovery. Use physical backups with WAL archiving. - ...kill every long query? Some are legitimate (reports, migrations). Find what blocks what first with
pg_blocking_pids.
Commands to know¶
These were run in the container unless noted.
| Command | What it does |
|---|---|
psql "host=<h> dbname=<db> user=<u> sslmode=verify-full" |
Connect with TLS verification (not run with TLS here). |
\du, \dn+, \dp <table>, \ddp (inside psql) |
Roles, schemas with privileges, table privileges, default privileges. |
select * from pg_stat_activity where state <> 'idle'; |
What is running and waiting. |
select pg_blocking_pids(<pid>); |
Who blocks a session. |
select pg_cancel_backend(<pid>); / pg_terminate_backend(<pid>) |
Cancel a query / end a session. |
explain (analyze, buffers) <query>; |
Real plan, timings and buffer use (runs the query). |
pg_dump -Fc -d app -f app.dump / pg_restore --list app.dump |
Logical backup in custom format / list its contents. |
pg_dumpall --globals-only > globals.sql |
Roles and tablespaces (not run here). |
show max_connections; / show wal_level; |
Settings (100 and replica in the container). |
How it breaks¶
| Symptom | Layer | Check | Likely cause |
|---|---|---|---|
FATAL: sorry, too many clients already |
Connections | pg_stat_activity count by application |
Leak, no pooling, autoscaling or Lambda fan-out |
permission denied for table on a new table |
Privileges | \ddp, table owner |
Missing default privileges, or the table was created by a different role |
permission denied for sequence |
Privileges | Sequence grants | usage on sequences not granted |
| Queries pile up waiting | Locks | pg_blocking_pids, idle in transaction sessions |
Long transaction, migration taking a lock, forgotten open transaction |
| One query suddenly slow | Plan | EXPLAIN ANALYZE, table statistics |
Missing index, stale statistics, plan change after data growth |
| Disk keeps growing | WAL or bloat | Replication slots, archive status, dead tuples | Inactive replication slot holding WAL, failing archive, vacuum not keeping up |
| Restore fails: role does not exist | Backup | Globals in the backup | pg_dump without pg_dumpall --globals-only |
Interview questions¶
1. The app reports "too many clients already". What do you do now and later?
Answer
- Now: count connections by application and state in
pg_stat_activity; terminate idle leaked sessions if safe. - Later: a pooler (PgBouncer or RDS Proxy), sane pool sizes per instance, fix leaks; raise
max_connectionsonly with memory headroom.
2. New tables created by migrations are not readable by the app. Why?
Answer
GRANT ON ALL TABLESonly covered existing tables.- Set
ALTER DEFAULT PRIVILEGES FOR ROLE <owner>so future tables (and sequences) get grants; check the migration runs as that owner.
3. pg_dump versus physical backups?
Answer
pg_dump: logical, one database, portable across versions, good for small data and single objects; no PITR; roles needpg_dumpall --globals-only.- Physical: base backup plus WAL archive; whole cluster, same major version, point-in-time recovery, faster for large data.
4. Queries are hanging. How do you find the cause?
Answer
pg_stat_activityforwait_event_type = Lockandidle in transaction.pg_blocking_pidsto find the blocker; see what it runs and since when.- Decide: wait, cancel the query, or terminate the session; then fix the long transaction or the migration.
5. What does sslmode=prefer really give you?
Answer
- TLS if the server offers it, otherwise plain text, and the certificate is never checked: no protection against a man in the middle.
- Use
verify-fullwith the CA certificate.
6. Session versus transaction pooling in PgBouncer?
Answer
- Session: one server connection per client connection; everything works, fewer savings.
- Transaction: server connection only during a transaction; big savings, but session state (
SET, advisory locks, some prepared statements) does not persist.
7. How would you upgrade a major PostgreSQL version with little downtime?
Answer
- Logical replication to a new-version server (or Blue/Green on RDS), catch up, switch clients during a short write pause.
- Test extensions and queries on the new version; keep the old one for rollback.
Try it¶
About 10 minutes with Docker:
docker run -d --name pg -e POSTGRES_PASSWORD=example-only -e POSTGRES_DB=app postgres:18, thendocker exec -it pg psql -U postgres -d app.- Create the three roles and schema above; as
app_ownercreate a table; confirmapp_rocan read it only after default privileges are set. - In two
psqlsessions, open a transaction that updates a row and leave it; update the same row from the other session; find the blocker withpg_stat_activity. - Take a
pg_dump -Fc, drop the table, and restore just that table withpg_restore -t. - Clean up:
docker rm -f pg.
Zoom out¶
Most PostgreSQL incidents an operator sees are about connections, permissions, locks and backups, not exotic internals. With these, a managed database stops being a black box (RDS and Aurora, Redis and caching).
Sources¶
- PostgreSQL documentation: libpq SSL support (sslmode)
- PostgreSQL documentation: privileges
- PostgreSQL documentation: ALTER DEFAULT PRIVILEGES
- PostgreSQL documentation: pg_dump
- PostgreSQL documentation: continuous archiving and point-in-time recovery
- PostgreSQL documentation: monitoring statistics (pg_stat_activity)
- PostgreSQL documentation: EXPLAIN
- PgBouncer: features and pooling modes