PostgreSQL Skill Cookbook

CREATE INDEX CONCURRENTLY my_table_col_idx ON my_table (col);
-- Verify it's valid + ready
SELECT indexrelid::regclass, indisvalid, indisready FROM pg_index WHERE indrelid='my_table'::regclass;

SELECT
  blocked.pid           AS blocked_pid,
  blocked.usename       AS blocked_user,
  age(now(), blocked.xact_start)  AS blocked_xact_age,
  blocked.wait_event_type || '/' || blocked.wait_event AS wait,
  blocker.pid           AS blocker_pid,
  blocker.usename       AS blocker_user,
  blocker.state         AS blocker_state,
  age(now(), blocker.xact_start)  AS blocker_xact_age,
  left(blocked.query, 120) AS blocked_query,
  left(blocker.query, 120) AS blocker_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS blocker_pid ON true
JOIN pg_stat_activity blocker ON blocker.pid = blocker_pid
WHERE blocked.wait_event_type = 'Lock'
ORDER BY blocked_xact_age DESC;

SELECT
  pid, locktype, relation::regclass AS table, mode, granted, fastpath,
  waitstart  -- PG14+
FROM pg_locks
WHERE pid IN (12345, 12346)  -- pids from step 1
ORDER BY pid, granted DESC;

-- Try graceful cancel first (interrupts current statement, transaction stays open)
SELECT pg_cancel_backend(12345);

-- If that fails, terminate (closes the connection — DON'T run on walsenders / logical-replication apply workers)
SELECT pg_terminate_backend(12345);

SELECT
  n.nspname AS schema, c.relname AS table,
  c.reltuples::bigint AS live_estimate,
  s.n_dead_tup        AS dead_tuples,
  round(100.0 * s.n_dead_tup / NULLIF(c.reltuples, 0), 1) AS dead_pct,
  pg_size_pretty(pg_relation_size(c.oid))            AS heap_size,
  pg_size_pretty(pg_total_relation_size(c.oid))      AS total_size,
  s.last_autovacuum,
  s.last_vacuum
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relkind IN ('r', 'p') AND c.reltuples > 10000
ORDER BY dead_pct DESC NULLS LAST LIMIT 20;

SELECT
  'long_txn' AS source, pid, usename,
  age(now(), xact_start) AS age,
  age(backend_xmin)      AS xmin_age,
  state, left(query, 120) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC LIMIT 5
UNION ALL
SELECT 'slot' AS source, NULL::int, slot_name, NULL,
  age(xmin), 'active=' || active, slot_name
FROM pg_replication_slots WHERE xmin IS NOT NULL
ORDER BY 5 DESC LIMIT 5;
UNION ALL
SELECT 'prepared_txn' AS source, NULL::int, owner, age(prepared),
  age(transaction), gid, gid
FROM pg_prepared_xacts LIMIT 5;

SELECT
  pid, datname, relid::regclass AS table, phase,
  pg_size_pretty(heap_blks_total::bigint * 8192)   AS heap_total,
  pg_size_pretty(heap_blks_scanned::bigint * 8192) AS heap_scanned,
  index_vacuum_count,
  num_dead_item_ids   -- PG17+ (renamed from num_dead_tuples)
FROM pg_stat_progress_vacuum
JOIN pg_stat_activity USING (pid);

VACUUM (VERBOSE, ANALYZE, PARALLEL 4) my_table;

-- Run on primary
SELECT
  application_name,
  client_addr,
  state,
  sync_state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)    AS pending_bytes,
  pg_wal_lsn_diff(sent_lsn, write_lsn)               AS write_lag_bytes,
  pg_wal_lsn_diff(write_lsn, flush_lsn)              AS flush_lag_bytes,
  pg_wal_lsn_diff(flush_lsn, replay_lsn)             AS replay_lag_bytes,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS total_lag,
  write_lag, flush_lag, replay_lag                   AS time_lags
FROM pg_stat_replication
ORDER BY total_lag DESC;

SELECT
  slot_name, slot_type, database, active, wal_status,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
  inactive_since,            -- PG17+
  invalidation_reason        -- PG17+
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

-- On standby
SELECT
  pid, usename, state, wait_event_type, wait_event,
  age(now(), xact_start) AS xact_age,
  left(query, 120) AS query
FROM pg_stat_activity
WHERE wait_event = 'RecoveryConflictSnapshot'
   OR backend_xmin IS NOT NULL
ORDER BY xact_age DESC;

SELECT pg_is_in_recovery();         -- t = standby, f = primary
SELECT pg_last_wal_replay_lsn();    -- standby only
SELECT pg_last_wal_receive_lsn();   -- standby only
SELECT pg_current_wal_lsn();        -- primary only

# 1. Stop application traffic. Note the recovery target time (epoch or '2026-05-14 03:42:17+00')
RECOVERY_TARGET='2026-05-14 03:42:17+00'

# 2. Provision a fresh host (do NOT recover in place — preserve evidence)
# 3. Install matching PG major version
PG_VERSION=18

# 4. Restore base backup
pgbackrest --stanza=main --type=time --target="$RECOVERY_TARGET" restore
# (or pg_basebackup output extracted to /var/lib/postgresql/18/data)

# 5. Configure recovery
cat >> /var/lib/postgresql/18/data/postgresql.auto.conf <<EOF
restore_command = 'pgbackrest --stanza=main archive-get %f "%p"'
recovery_target_time = '$RECOVERY_TARGET'
recovery_target_action = 'pause'   # don't promote until verified
recovery_target_inclusive = on
EOF

touch /var/lib/postgresql/18/data/recovery.signal

# 6. Start server (will replay WAL up to target, then pause)
pg_ctl -D /var/lib/postgresql/18/data start

# 7. Verify before promoting
psql -c "SELECT pg_last_wal_replay_lsn(), now(), pg_is_in_recovery();"
psql -c "SELECT count(*) FROM critical_table WHERE created_at < '$RECOVERY_TARGET';"
# Inspect critical data; if wrong target, stop server and adjust recovery_target_time

# 8. Promote when satisfied
psql -c "SELECT pg_wal_replay_resume();"
# After replay catches up to target, server auto-promotes (if recovery_target_action = 'promote')
# OR explicitly:
pg_ctl -D /var/lib/postgresql/18/data promote

-- 1. Extension inventory + version status
SELECT e.extname, e.extversion, av.default_version,
       CASE WHEN e.extversion != av.default_version THEN 'UPGRADE AVAILABLE' ELSE 'CURRENT' END AS status
FROM pg_extension e
JOIN pg_available_extensions av ON av.name = e.extname
WHERE e.extname != 'plpgsql'
ORDER BY status, e.extname;

-- 2. Tables with deprecated/removed types (e.g., abstime/reltime/tinterval gone in PG12)
SELECT format('%I.%I.%I', n.nspname, c.relname, a.attname) AS column,
       format_type(a.atttypid, a.atttypmod) AS type
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_type t ON t.oid = a.atttypid
WHERE NOT a.attisdropped AND a.attnum > 0
  AND t.typname IN ('abstime', 'reltime', 'tinterval', 'unknown')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

-- 3. Permissions / roles audit
SELECT rolname, rolsuper, rolreplication, rolbypassrls
FROM pg_roles WHERE NOT rolname LIKE 'pg_%' ORDER BY rolname;

# 1. Run --check on a clone to validate
/usr/lib/postgresql/18/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/16/main \
  --new-datadir=/var/lib/postgresql/18/main \
  --old-bindir=/usr/lib/postgresql/16/bin \
  --new-bindir=/usr/lib/postgresql/18/bin \
  --check

# 2. Stop both clusters
pg_ctlcluster 16 main stop
pg_ctlcluster 18 main stop

# 3. Actual upgrade
/usr/lib/postgresql/18/bin/pg_upgrade ...same args... --link

# 4. Start new cluster
pg_ctlcluster 18 main start

# 5. PG17 and earlier need ANALYZE; PG18+ pg_upgrade preserves planner stats
# PG≤17:
vacuumdb --all --analyze-in-stages --jobs=$(nproc)
# PG18+: skipped, but verify a sample plan
psql -c "EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM critical_table WHERE indexed_col = ?;"

-- Connection params + auth still working?
SELECT version(), current_setting('server_version_num');

-- Replication slots intact? (preserved by pg_upgrade since PG17)
SELECT slot_name, slot_type, active FROM pg_replication_slots;

-- Extensions match new cluster?
SELECT extname, extversion FROM pg_extension;

-- Workload-wide: top queries by total time
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  round(total_exec_time::numeric / 1000, 2) AS total_sec,
  round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
  left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) <the slow query>;

BEGIN;
SET LOCAL work_mem = '256MB';
SET LOCAL random_page_cost = 1.1;
SET LOCAL plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS) <the slow query>;
ROLLBACK;

WITH stats AS (
  SELECT
    n.nspname || '.' || c.relname AS table,
    c.reltuples::bigint AS approx_rows,
    s.n_dead_tup,
    s.n_live_tup,
    s.last_autovacuum,
    s.last_autoanalyze,
    s.autovacuum_count,
    age(now(), s.last_autovacuum) AS time_since_av,
    round(100.0 * s.n_dead_tup / NULLIF(s.n_live_tup, 0), 1) AS dead_pct
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  JOIN pg_stat_user_tables s ON s.relid = c.oid
  WHERE c.relkind IN ('r', 'p')
)
SELECT * FROM stats
WHERE dead_pct > 10 AND approx_rows > 100000
ORDER BY dead_pct DESC;

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,    -- vacuum when 2% dead (vs 20% default)
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 500,
  autovacuum_vacuum_cost_delay = 2,         -- ms per cost-cycle (lower = faster)
  autovacuum_vacuum_cost_limit = 2000,      -- raise from default 200
  autovacuum_naptime = 30                    -- if launcher needs to revisit fast
);

SELECT relname, reloptions FROM pg_class
WHERE relkind = 'r' AND reloptions IS NOT NULL;

ALTER TABLE events SET (
  autovacuum_vacuum_insert_scale_factor = 0.05,   -- run after 5% inserts (for FREEZE-prep)
  autovacuum_vacuum_insert_threshold = 10000
);

-- Watch autovacuum activity for 5 minutes
SELECT pg_stat_reset();  -- baseline
-- wait 5 minutes under workload
SELECT relname, autovacuum_count, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'events';

SELECT
  state, count(*),
  array_agg(DISTINCT application_name) AS apps
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state ORDER BY count DESC;

SELECT
  application_name, usename, count(*),
  max(age(now(), backend_start)) AS oldest_conn_age
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, usename
ORDER BY count DESC;

# pgbouncer.ini
[databases]
appdb = host=primary dbname=appdb

[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
pool_mode = transaction      # most apps work fine here
max_client_conn = 5000        # what apps connect to
default_pool_size = 30        # backends per (user, db)
server_idle_timeout = 600

ALTER ROLE webapp SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE webapp SET idle_session_timeout = '30min';
ALTER ROLE webapp SET statement_timeout = '30s';
ALTER ROLE webapp SET lock_timeout = '5s';
ALTER ROLE webapp CONNECTION LIMIT 200;

-- 1. Create the parent table partitioned by day
CREATE TABLE events (
  id        bigint generated always as identity,
  occurred_at timestamptz NOT NULL,
  user_id   bigint NOT NULL,
  payload   jsonb,
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

-- 2. Install pg_partman
CREATE EXTENSION pg_partman WITH SCHEMA partman;

-- 3. Template table for indexes/constraints (applies to NEW partitions only)
CREATE TABLE events_template (LIKE events);
CREATE INDEX ON events_template (user_id);
CREATE INDEX ON events_template USING gin (payload jsonb_path_ops);

-- 4. Register with partman
SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control      => 'occurred_at',
  p_interval     => '1 day',
  p_premake      => 7,                      -- create 7 days ahead
  p_template_table => 'public.events_template'
);

-- 5. Configure 90-day retention
UPDATE partman.part_config
SET retention = '90 days',
    retention_keep_table = false,           -- drop, don't detach
    retention_keep_index = false
WHERE parent_table = 'public.events';

-- 6. Install pg_cron, schedule maintenance hourly
CREATE EXTENSION pg_cron;
SELECT cron.schedule(
  'partman-events',
  '0 * * * *',
  $$ CALL partman.run_maintenance_proc() $$
);

-- Future partitions pre-created?
SELECT inhrelid::regclass FROM pg_inherits WHERE inhparent='events'::regclass ORDER BY 1;

-- pg_cron job scheduled?
SELECT * FROM cron.job WHERE jobname='partman-events';

-- Recent runs successful?
SELECT * FROM cron.job_run_details WHERE jobname='partman-events' ORDER BY start_time DESC LIMIT 5;

-- 1. Add CHECK as NOT VALID (instant, no scan)
ALTER TABLE users ADD CONSTRAINT users_email_not_null CHECK (email IS NOT NULL) NOT VALID;

-- 2. Backfill if needed
UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;

-- 3. Validate (SHARE UPDATE EXCLUSIVE, doesn't block reads/writes)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- 4. PG18+: convert to real NOT NULL via NOT VALID
ALTER TABLE users ALTER COLUMN email SET NOT NULL;   -- now fast because CHECK guarantees it

-- 1. NOT VALID FK takes brief ACCESS EXCLUSIVE, no scan
ALTER TABLE orders ADD CONSTRAINT orders_user_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- 2. Backfill / fix orphan rows
DELETE FROM orders WHERE user_id NOT IN (SELECT id FROM users);

-- 3. Validate (SHARE UPDATE EXCLUSIVE on both tables, allows DML)
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_fk;

-- CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE, allows reads + writes
CREATE INDEX CONCURRENTLY orders_user_id_idx ON orders (user_id);

-- Verify it's valid (not INVALID from a failed build)
SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index WHERE indrelid='orders'::regclass AND NOT indisvalid;

-- If invalid, drop and retry
DROP INDEX CONCURRENTLY orders_user_id_idx;

SELECT
  rolname,
  rolsuper, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls,
  rolconnlimit,
  rolvaliduntil,
  array_to_string(rolconfig, ', ') AS guc_overrides
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%'
ORDER BY rolsuper DESC, rolname;

WITH RECURSIVE membership AS (
  SELECT roleid, member, ARRAY[roleid] AS path, 1 AS depth
  FROM pg_auth_members
  UNION ALL
  SELECT am.roleid, m.member, m.path || am.roleid, m.depth + 1
  FROM pg_auth_members am
  JOIN membership m ON am.member = m.roleid
  WHERE NOT am.roleid = ANY(m.path)
)
SELECT
  r1.rolname AS member_of,
  r2.rolname AS member,
  depth
FROM membership
JOIN pg_roles r1 ON r1.oid = roleid
JOIN pg_roles r2 ON r2.oid = member
ORDER BY r2.rolname, depth;

-- Per-database; loop over all databases for cluster-wide picture
SELECT current_database(), n.nspname AS schema, c.relname AS object,
       CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view'
                     WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
                     WHEN 'm' THEN 'matview' WHEN 'p' THEN 'part_table' END AS type
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_roles r ON r.oid = c.relowner
WHERE r.rolname = 'soon_to_be_dropped'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

# Per-database, reassign ownership then drop privileges
for db in $(psql -tAc "SELECT datname FROM pg_database WHERE NOT datistemplate"); do
  psql -d "$db" -c "REASSIGN OWNED BY soon_to_be_dropped TO new_owner;"
  psql -d "$db" -c "DROP OWNED BY soon_to_be_dropped;"
done

# Cluster-wide
psql -c "DROP ROLE soon_to_be_dropped;"

SELECT
  pid,
  age(clock_timestamp(), xact_start) AS xact_age,
  state,
  wait_event_type || '/' || COALESCE(wait_event, '') AS wait,
  usename, application_name,
  left(query, 200) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state != 'idle'
ORDER BY xact_start ASC NULLS LAST;

SELECT
  n.nspname AS schema,
  c.relname AS object,
  CASE c.relkind
    WHEN 'r' THEN 'table' WHEN 'p' THEN 'part_table'
    WHEN 'i' THEN 'index' WHEN 'I' THEN 'part_index'
    WHEN 'm' THEN 'matview' WHEN 't' THEN 'toast'
    WHEN 'S' THEN 'sequence' WHEN 'v' THEN 'view' END AS type,
  pg_size_pretty(pg_relation_size(c.oid))       AS relation_size,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p', 'i', 'I', 'm', 'S')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;

SELECT
  n.nspname AS schema, c.relname AS table,
  c.reltuples::bigint AS approx_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conrelid = c.oid AND contype = 'p'
  )
ORDER BY c.reltuples DESC;

SELECT
  conrelid::regclass AS child_table,
  conname AS fk_name,
  pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (i.indkey::int[])[0:cardinality(c.conkey)-1] = c.conkey::int[]
  );

SELECT
  n.nspname AS schema,
  c.relname AS table,
  i.relname AS index,
  pg_size_pretty(pg_relation_size(i.oid)) AS size,
  s.idx_scan,
  s.last_idx_scan    -- PG16+
FROM pg_stat_user_indexes s
JOIN pg_class i ON i.oid = s.indexrelid
JOIN pg_class c ON c.oid = s.relid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_index ix ON ix.indexrelid = i.oid
WHERE s.idx_scan = 0
  AND NOT ix.indisunique AND NOT ix.indisprimary
  AND pg_relation_size(i.oid) > 1024*1024
ORDER BY pg_relation_size(i.oid) DESC;

SELECT
  blocked.pid AS blocked_pid, blocker.pid AS blocker_pid,
  age(now(), blocked.xact_start) AS blocked_age,
  blocked.wait_event,
  left(blocked.query, 100) AS blocked_query,
  left(blocker.query, 100) AS blocker_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bp ON true
JOIN pg_stat_activity blocker ON blocker.pid = bp
WHERE blocked.wait_event_type = 'Lock';

-- Live walsenders
SELECT application_name, client_addr, state, sync_state,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS total_lag,
  replay_lag
FROM pg_stat_replication
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) DESC;

-- Slot retention
SELECT slot_name, slot_type, active, wal_status,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
  inactive_since        -- PG17+
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

SELECT
  pid, usename, application_name, state,
  age(now(), xact_start) AS xact_age,
  age(now(), state_change) AS state_age,
  backend_xmin, age(backend_xmin) AS xmin_age,
  left(query, 200) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC LIMIT 10;

SELECT
  e.extname, e.extversion AS installed,
  av.default_version AS available,
  CASE WHEN e.extversion != av.default_version THEN 'UPGRADE AVAILABLE' ELSE 'CURRENT' END AS status,
  n.nspname AS schema
FROM pg_extension e
LEFT JOIN pg_available_extensions av ON av.name = e.extname
JOIN pg_namespace n ON n.oid = e.extnamespace
ORDER BY status, e.extname;

SELECT
  pg_partition_tree('events'::regclass) AS leaf_set;

-- Detailed:
SELECT
  c.oid::regclass AS partition,
  pg_get_expr(c.relpartbound, c.oid) AS partition_bound,
  pg_size_pretty(pg_total_relation_size(c.oid)) AS size,
  s.n_dead_tup, s.last_autovacuum
FROM pg_partition_tree('events'::regclass) pt
JOIN pg_class c ON c.oid = pt.relid
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
ORDER BY c.oid::regclass::text;

SELECT
  name, setting, unit, source, category, short_desc
FROM pg_settings
WHERE source NOT IN ('default', 'override')
ORDER BY category, name;

WITH RECURSIVE rm AS (
  SELECT roleid, member, 1 AS depth, rolname AS root
  FROM pg_auth_members am
  JOIN pg_roles r ON r.oid = am.roleid
  UNION ALL
  SELECT rm.roleid, am.member, rm.depth + 1, rm.root
  FROM rm
  JOIN pg_auth_members am ON am.roleid = rm.member
)
SELECT
  rm.root AS parent_role,
  r.rolname AS member,
  rm.depth
FROM rm
JOIN pg_roles r ON r.oid = rm.member
ORDER BY rm.root, rm.depth, r.rolname;