PostgreSQL psql Cheatsheet

psql                                                   # connect as current OS user to same-named DB
psql -U appuser -d appdb -h db.internal -p 5432         # explicit user / db / host / port
psql -U appuser -d appdb -h db.internal -W             # force password prompt
psql "postgresql://appuser:secret@db.internal:5432/appdb?sslmode=require"   # connection URI
psql "host=db.internal dbname=appdb user=appuser sslmode=verify-full"       # keyword/value form
PGPASSWORD=secret psql -U appuser -d appdb              # password via env (avoid in shared shells)

\c dbname                    # connect to a different database
\c dbname user host port     # switch db + role + host in one go
\conninfo                    # who am I / where am I connected
\l        (\list)            # list all databases
\dn                          # list schemas
\dt                          # list tables in current schema
\dt schema.*                 # list tables in a specific schema
\dt+                         # tables WITH size and description
\d tablename                 # describe a table (columns, indexes, constraints, FKs)
\d+ tablename                # describe + storage, stats target, comments
\di                          # list indexes
\dv                          # list views
\df                          # list functions
\du                          # list roles and their attributes
\dp  (\z)                    # list table access privileges (GRANTs)
\ds                          # list sequences
\dx                          # list installed extensions
\sf funcname                 # show a function's source

\x                           # toggle expanded output (one column per line — great for wide rows)
\x auto                      # expanded only when a row is too wide for the terminal
\timing                      # toggle query execution timing on/off
\pset null '∅'               # render NULL visibly instead of blank
\pset pager off              # stop piping output through less
\e                           # open the last query in $EDITOR
\i script.sql                # run a SQL file from inside the session
\ir script.sql               # run a file relative to the current script's dir
\watch 5                     # re-run the last query every 5 seconds
\copy ... to 'file'          # client-side COPY (see below)
\set VERBOSITY verbose       # full error detail incl. SQLSTATE
\q                           # quit

psql -d appdb -c "SELECT version();"                   # run one command, exit
psql -d appdb -f migrate.sql                           # run a whole file
psql -d appdb -Atc "SELECT count(*) FROM users"        # -A unaligned -t tuples-only → clean scalar
psql -v ON_ERROR_STOP=1 -f migrate.sql                 # ABORT on first error (essential in deploys)
psql -1 -v ON_ERROR_STOP=1 -f migrate.sql              # wrap the whole file in one transaction
psql -d appdb -f migrate.sql -L migrate.log            # tee session output to a log

\copy users TO 'users.csv' WITH (FORMAT csv, HEADER)
\copy users FROM 'users.csv' WITH (FORMAT csv, HEADER)
\copy (SELECT id,email FROM users WHERE active) TO 'active.csv' WITH (FORMAT csv, HEADER)

CREATE ROLE app_runtime LOGIN PASSWORD 'x';            -- a login role (a "user")
CREATE ROLE readonly NOLOGIN;                          -- a group role
GRANT CONNECT ON DATABASE appdb TO app_runtime;
GRANT USAGE ON SCHEMA public TO app_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_runtime;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT readonly TO analytics_user;                      -- grant a group role to a user

-- Make privileges apply to FUTURE tables too (the common gotcha):
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;

REVOKE INSERT ON payments FROM app_runtime;

CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created ON orders (created_at DESC);
CREATE INDEX idx_users_lower_email ON users (lower(email));      -- expression index
CREATE INDEX idx_orders_open ON orders (user_id) WHERE status = 'open';   -- partial index
DROP INDEX idx_users_email;

EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';             -- plan only, no execution
EXPLAIN ANALYZE SELECT ...;                                      -- actually runs it, shows real timing
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;                           -- + shared/read buffer counts

SELECT current_database(), current_user, version();
SELECT pg_size_pretty(pg_database_size('appdb'));               -- DB size, human-readable
SELECT pg_size_pretty(pg_total_relation_size('orders'));        -- table + indexes + toast
SELECT * FROM pg_stat_activity WHERE state != 'idle';           -- what's running right now
SELECT pg_terminate_backend(<pid>);                             -- kill a stuck backend
SHOW statement_timeout;                                         -- current session setting

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

# Rails — disable the automatic transaction for this migration
class AddIndexToOrders < ActiveRecord::Migration[7.1]
  disable_ddl_transaction!
  def change
    add_index :orders, :user_id, algorithm: :concurrently
  end
end

# Django — mark the migration non-atomic
class Migration(migrations.Migration):
    atomic = False
    operations = [migrations.AddIndex(...)]   # use AddIndexConcurrently from django.contrib.postgres

-- Detect and clean up a failed concurrent build before retrying:
SELECT indexrelid::regclass AS index, indrelid::regclass AS table
FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY idx_orders_user_id;   -- then re-create

# Full logical backup in custom format (best for restore flexibility)
pg_dump -Fc -U appuser -h db.internal -d appdb -f "appdb-$(date -u +%Y%m%dT%H%M%SZ).dump"

# Schema-only (fast pre-migration snapshot of structure)
pg_dump -s -U appuser -d appdb -f schema.sql

# Just one table before a risky data migration
pg_dump -Fc -t payments -U appuser -d appdb -f payments-pre-deploy.dump

# Restore into a fresh DB, 4 parallel workers
createdb appdb_restore
pg_restore -j 4 -U appuser -d appdb_restore appdb-20260710T120000Z.dump

# Restore just one table into the live DB
pg_restore -U appuser -d appdb -t payments payments-pre-deploy.dump

# pg_dumpall — roles and global objects (NOT captured by pg_dump)
pg_dumpall -U postgres --roles-only -f roles.sql

#!/usr/bin/env bash
set -euo pipefail

STAMP=$(date -u +%Y%m%dT%H%M%SZ)
DUMP="/var/backups/pg/appdb-${STAMP}.dump"

pg_dump -Fc -U "$PGUSER" -h "$PGHOST" -d "$PGDATABASE" -f "$DUMP"
# Verify the dump is readable — a truncated dump is worse than no dump
pg_restore --list "$DUMP" > /dev/null

echo "backup ok: $DUMP ($(du -h "$DUMP" | cut -f1))"

-- Migration role — owns the schema, runs DDL during deploys only
CREATE ROLE app_migrate LOGIN PASSWORD 'MIGRATE_SECRET';
GRANT ALL ON DATABASE appdb TO app_migrate;
GRANT ALL ON SCHEMA public TO app_migrate;

-- Runtime role — DML only, no DDL, no DROP
CREATE ROLE app_runtime LOGIN PASSWORD 'RUNTIME_SECRET';
GRANT CONNECT ON DATABASE appdb TO app_runtime;
GRANT USAGE ON SCHEMA public TO app_runtime;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_runtime;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_runtime;

-- Critical: default privileges so tables created by app_migrate are usable by app_runtime.
-- Run this AS app_migrate (or set the FOR ROLE), since default privileges apply to the creating role.
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrate IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE app_migrate IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO app_runtime;

EXPLAIN (ANALYZE, BUFFERS, FORMAT text)
SELECT * FROM orders WHERE user_id = 42 AND status = 'open';

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT round(mean_exec_time::numeric, 1) AS avg_ms,
       calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;                                    -- the 10 queries burning the most time

-- At the top of a migration script, or set per-role for the migration user:
SET lock_timeout = '5s';          -- give up acquiring a lock after 5s instead of blocking the world
SET statement_timeout = '30s';    -- abort any single statement that runs longer than 30s

-- Persist for the migration role so every migration inherits the guard:
ALTER ROLE app_migrate SET lock_timeout = '5s';
ALTER ROLE app_migrate SET statement_timeout = '60s';

ALTER ROLE app_runtime CONNECTION LIMIT 40;
SHOW max_connections;                        -- cluster-wide ceiling (default 100)

-- On the SOURCE database:
CREATE PUBLICATION app_pub FOR ALL TABLES;

-- On the TARGET database (schema must already exist there):
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=old-primary dbname=appdb user=replicator password=x'
  PUBLICATION app_pub;

-- Watch replication lag on the source before cutting over:
SELECT application_name,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag
FROM pg_stat_replication;