Postgres Commands

Most engineers meet PostgreSQL through an ORM and only ever open psql when something is broken. That is exactly the wrong moment to be learning the tooling. This reference is organized by the operational task you’re doing — connect, inspect, grant, migrate, diagnose, unblock, reclaim, back up, replicate — with the version caveats and lock behaviour that decide whether a command is safe to run on a live database at 3am.

Connecting and psql essentials

psql is not just a SQL prompt — its backslash meta-commands are the fastest way to inspect a database, and they are the thing most people never learn.

Three habits worth forming immediately:

# 1. Never put a password in the command line or a shell variable that gets logged.
#    ~/.pgpass, mode 0600, is read automatically:
echo "db.internal:5432:appdb:appuser:secret" >> ~/.pgpass && chmod 600 ~/.pgpass

# 2. ON_ERROR_STOP for every non-interactive script. Without it, psql runs the
#    remaining statements after a failure — which is how half-applied migrations happen.
psql -v ON_ERROR_STOP=1 -f migration.sql

# 3. Make destructive sessions obvious to yourself.
export PSQL_EDITOR=vim
psql -h prod-db -d appdb    # then: \set PROMPT1 '%[%033[1;31m%]PROD%[%033[0m%]=# '

Inspecting a database

Before you tune anything, find out what is actually big and what is actually busy.

-- Database sizes, largest first
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database ORDER BY pg_database_size(datname) DESC;

-- Table sizes including indexes and TOAST
SELECT relname AS table,
       pg_size_pretty(pg_total_relation_size(c.oid))  AS total,
       pg_size_pretty(pg_relation_size(c.oid))        AS heap,
       pg_size_pretty(pg_indexes_size(c.oid))         AS indexes
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog','information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

-- Indexes nobody uses (candidates for removal — check on a replica first)
SELECT relname AS table, indexrelname AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan AS scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 means “not used since the stats were last reset” — not “never used”. Check pg_stat_get_db_stat_reset_time() before you drop anything, and remember that an index backing a unique or foreign-key constraint is doing a job even at zero scans.

Roles, privileges and access

Postgres permissions trip people up because access needs two grants that most people only remember one of: USAGE on the schema, and a privilege on the object inside it.

The single most common permission failure looks like this:

-- This alone does NOT work:
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO analyst;
-- => still: ERROR: permission denied for schema reporting

-- Both are needed:
GRANT USAGE ON SCHEMA reporting TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO analyst;

-- And for tables created LATER by the migration user:
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA reporting
  GRANT SELECT ON TABLES TO analyst;

Authentication happens before any of this, in pg_hba.conf — evaluated top to bottom, first match wins:

# TYPE  DATABASE  USER   ADDRESS         METHOD
host    appdb     app    10.0.0.0/8      scram-sha-256
hostssl all       all    0.0.0.0/0       scram-sha-256
local   all       postgres                peer

sudo -u postgres psql -c 'SELECT pg_reload_conf();'   # no restart needed for pg_hba

A rule that never matches produces no pg_hba.conf entry for host; a matching rule with the wrong method produces password authentication failed.

Schema changes that don’t take the site down

Every ALTER TABLE takes a lock. The question is only which lock and for how long — and an ACCESS EXCLUSIVE lock held behind a long-running query will queue every subsequent read.

The two-line habit that prevents most migration outages:

SET lock_timeout = '3s';
SET statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN shipped_at timestamptz;

Without lock_timeout, an ALTER TABLE that cannot get its lock waits — and while it waits, it holds a place in the lock queue that blocks every read behind it. A three-second failure you retry is enormously better than a five-minute stall you have to diagnose live.

CREATE INDEX CONCURRENTLY deserves its own note: it cannot run inside a transaction block, it takes roughly twice as long, and if it fails it leaves an invalid index behind that still costs write overhead while never being used for reads:

-- Find the wreckage of a failed concurrent build
SELECT indexrelid::regclass AS index FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY idx_orders_customer;   -- then rebuild

Diagnosing slow queries

-- What is slow, cumulatively? Requires pg_stat_statements in shared_preload_libraries.
SELECT calls,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       round(total_exec_time::numeric)   AS total_ms,
       rows,
       left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total_exec_time, not mean_exec_time. A 40ms query called two million times costs far more than a 9-second report someone runs at midnight, and it is usually much easier to fix.

Then read the plan for the real thing:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';

ANALYZE in EXPLAIN (ANALYZE) actually executes the query. On an UPDATE or DELETE, wrap it:

BEGIN;
EXPLAIN (ANALYZE) DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

Index choice, briefly: B-tree for equality and ranges (the default, and right almost always); GIN for jsonb containment and full-text; BRIN for naturally ordered huge tables like append-only time series; partial indexes (WHERE status = 'open') when queries only ever touch a slice; and column order in a composite index matters — equality columns first, then the range column.

Connections, locks and blocked sessions

This is the section you will actually open during an incident.

-- Who is doing what, longest transaction first
SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       wait_event_type, wait_event,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY xact_age DESC NULLS LAST;

-- Who is blocking whom (PG 9.6+)
SELECT blocked.pid          AS blocked_pid,
       blocked.usename      AS blocked_user,
       blocking.pid         AS blocking_pid,
       blocking.usename     AS blocking_user,
       left(blocked.query, 60)  AS blocked_query,
       left(blocking.query, 60) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Reach for pg_cancel_backend first: it is the gentler tool, and for a runaway SELECT it is all you need. pg_terminate_backend is what you want for a session stuck idle in transaction, because there is no query to cancel — the session is holding locks while doing nothing at all.

Connection exhaustion is the other half of this section. Postgres uses a process per connection, so “just raise max_connections” trades one problem for memory pressure and context-switching. The real answer is a pooler — PgBouncer in transaction mode — in front of the database:

SELECT count(*) AS used,
       (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max
FROM pg_stat_activity;

Hitting the ceiling produces too many clients already and, once the reserved slots go, remaining connection slots are reserved.

Vacuum, bloat and transaction wraparound

Postgres never overwrites a row in place — an UPDATE writes a new version and leaves the old one dead. Vacuum reclaims those. When vacuum cannot keep up, you get bloat; when it cannot run at all, you eventually get a database that refuses writes.

-- Tables most in need of vacuum
SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC NULLS LAST;

-- Transaction age — the wraparound clock. Watch this.
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY xid_age DESC;

VACUUM FULL is not “a better VACUUM”. It takes an ACCESS EXCLUSIVE lock for the whole rewrite and needs enough free disk for a second copy of the table. On a live system, pg_repack does the same job without the outage.

Backups and recovery

The distinction that matters operationally: a logical dump (pg_dump) is portable across versions and lets you restore a single table, but restoring a large one is slow because every index is rebuilt. A physical backup (pg_basebackup plus archived WAL) restores fast and supports point-in-time recovery, but it is tied to the same major version and architecture.

Replication and read replicas

-- On the primary: who is replicating, and how far behind?
SELECT client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- On a replica: how stale is the data I'm reading?
SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag;

-- Replication slots — an inactive slot will fill your disk with retained WAL
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Long queries on a hot standby get cancelled when replay needs to remove rows they are reading — that is canceling statement due to conflict with recovery. The trade-off is hot_standby_feedback = on (the replica tells the primary what it still needs, at the cost of bloat on the primary) or a raised max_standby_streaming_delay (lets the replica fall further behind instead).

Configuration that actually matters

Ignore the hundreds of settings; a handful decide almost everything.

ALTER SYSTEM SET log_min_duration_statement = '1s';
SELECT pg_reload_conf();     -- enough for most settings

-- Did it actually take effect, or does it need a restart?
SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart;

work_mem is the one people get wrong most often. It is allocated per sort or hash node, per parallel worker — so a query with four sorts running three workers can consume many times the configured value. Set it modestly at the global level and raise it per-session for the specific reporting query that needs it.

Troubleshooting specific errors

The Postgres failures engineers hit most often, each with a dedicated guide:

• duplicate key value violates unique constraint — an insert collided with an existing key; find the row, then use ON CONFLICT.
• duplicate key from an out-of-sync sequence — a serial fell behind the table; resync with setval.
• relation does not exist — search_path, a quoted case-sensitive name, or the wrong schema.
• permission denied for table — the schema USAGE plus GRANT pair, and default privileges.
• column does not exist — usually quoting, or a stale view definition.
• deadlock detected — two transactions taking the same locks in different orders.
• too many clients already — connection exhaustion; the answer is pooling.
• could not serialize access — expected under REPEATABLE READ/SERIALIZABLE; retry the transaction.
• canceling statement due to conflict with recovery — replica replay versus a long read.
• no pg_hba.conf entry for host — no authentication rule matched the connection.

For anything else, search the DevOps error library or paste the message into the Incident Assistant.

Production checklist

• lock_timeout before every DDL. A migration that waits blocks everything queued behind it.
• statement_timeout and idle_in_transaction_session_timeout per role. Set them on the application role, not globally, so migrations and backups aren’t killed.
• Alert on age(datfrozenxid), not just on disk. Wraparound stops writes entirely.
• Alert on inactive replication slots. They retain WAL until the disk fills.
• CREATE INDEX CONCURRENTLY on live tables — and check for invalid indexes afterwards.
• pg_dumpall --globals-only alongside pg_dump. Roles are not in the data dump.
• Time a real restore this quarter. That measured number is your RTO.
• Pool connections before raising max_connections.
• GRANT USAGE ON SCHEMA and ALTER DEFAULT PRIVILEGES — the two halves everyone forgets.
• Turn on log_min_duration_statement. It costs almost nothing and answers most questions.

Frequently asked questions

What is the difference between pg_cancel_backend and pg_terminate_backend?

pg_cancel_backend cancels the currently running query but leaves the connection and its transaction open — the gentler option, and enough for a runaway SELECT. pg_terminate_backend closes the entire connection and rolls back its transaction. Use terminate for a session stuck idle in transaction, because there is no query to cancel; that session is holding locks while doing nothing.

Why does my query get slower after a big data load?

The planner is working from stale statistics. After a bulk insert, update or restore, the row estimates it uses can be wildly wrong, which makes it choose a nested loop where it should hash-join. Run ANALYZE tablename — it is cheap and non-blocking. Autovacuum will get there eventually, but “eventually” is not helpful during the load window.

Is VACUUM FULL better than VACUUM?

No — it is a different, far more disruptive operation. Plain VACUUM marks dead rows reusable and does not block reads or writes; it is what you want almost always. VACUUM FULL rewrites the entire table to return space to the operating system, taking an ACCESS EXCLUSIVE lock for the duration and needing enough free disk for a second copy. On a live system use pg_repack, which achieves the same result without the outage.

How many connections should max_connections be?

Lower than you think, with a pooler in front. Postgres forks a process per connection, so each one costs memory and scheduler time whether it is busy or idle. Most workloads run better with max_connections in the low hundreds and PgBouncer in transaction mode absorbing thousands of client connections. Raising max_connections to fix connection exhaustion usually just converts it into a memory problem.

Why did my new table lose its permissions?

Because GRANT ... ON ALL TABLES IN SCHEMA applies only to tables that exist at the moment you run it. Anything created later starts with no grants. ALTER DEFAULT PRIVILEGES fixes it going forward — but it attaches to the role that creates the object, so you must name the migration role with FOR ROLE migrator, not just run it as postgres.

Can I add a NOT NULL column to a big table safely?

Adding the column is fast — since PostgreSQL 11 a non-volatile default no longer rewrites the table. SET NOT NULL is the expensive half, because it scans every row under an ACCESS EXCLUSIVE lock. On PostgreSQL 12 and newer you can avoid the scan: add a CHECK (col IS NOT NULL) NOT VALID constraint, VALIDATE it under a weaker lock, then SET NOT NULL — which the planner satisfies from the validated constraint instead of rescanning.

Related resources

• Guide: Linux Commands — the host-level diagnosis that sits underneath every database incident.
• Guide: System Design — where the database fits in the wider architecture, and what to do before sharding.
• Stack hub: Postgres command centre — the top Postgres errors, tools and runbook in one place.
• Troubleshooting hub: PostgreSQL database errors — the full error cluster.
• Tool: Incident Assistant — paste a symptom, get an ordered triage plan.