PostgreSQL DBA Cheat Sheet: Tested psql Commands and Admin Queries

This is the PostgreSQL cheat sheet I keep open during on-call: the psql shortcuts and catalog queries that answer the questions a DBA gets every day. Who is connected? What is blocking? Which table is eating the disk? Is the standby keeping up?

Every query below was run on PostgreSQL 16 with a sample database and a live streaming standby, and the outputs are real. The queries work on PostgreSQL 13 and later. Many cheat sheets online still use functions that were renamed or removed years ago; section 12 lists those, with the errors they now give.

01 Connect with psql02 psql meta-commands03 Server & configuration04 Databases & table sizes05 Schemas & objects06 Users & privileges07 Sessions & connections08 Locks & blocking09 Export & import CSV10 Backup quick reference11 Replication health12 Fixed from older cheat sheets

01 Connect with psql

$ shell

psql -h dbhost -p 5432 -U app_owner -d shopdb
psql "postgresql://app_owner@dbhost:5432/shopdb?sslmode=require"
psql "service=shopdb_prod"                  # entry from ~/.pg_service.conf
psql -h dbhost -U app_owner -d shopdb -f script.sql -v ON_ERROR_STOP=1

Keep passwords out of scripts and history: put them in ~/.pgpass (host:port:database:user:password, chmod 600). For scripts, always add -v ON_ERROR_STOP=1 so psql stops at the first error instead of carrying on and exiting 0.

02 psql meta-commands

Command	What it does	Command	What it does
\l	List databases	\c shopdb	Connect to another database
\dn+	Schemas with owners and rights	\dt sales.*	Tables in a schema
\d+ sales.orders	Columns, indexes, constraints, triggers, size	\di	Indexes
\dv / \dm	Views / materialized views	\df	Functions and procedures
\sf name	Show a function's source	\dx	Installed extensions
\du	Roles and their attributes	\dp table	Privileges on a table
\conninfo	Who and where am I connected?	\x auto	Vertical output for wide rows
\timing on	Show query time	\watch 5	Re-run the last query every 5 s
\gx	Run this query with vertical output	\e	Edit the query buffer in $EDITOR
\i file.sql	Run a script	\o file.txt	Send output to a file
\s	Command history	\h ALTER TABLE	SQL syntax help
\?	All psql commands	\q	Quit

output

shopdb=# \conninfo
You are connected to database "shopdb" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432".
shopdb=# \timing on
shopdb=# SELECT count(*) FROM sales.orders;
 count
--------
 300000
Time: 12.820 ms

03 Server & configuration

Version and uptime

SQL

SELECT version();
SELECT pg_postmaster_start_time() AS started_at,
       date_trunc('second', now() - pg_postmaster_start_time()) AS uptime;

Where are the files?

SQL

SELECT name, setting FROM pg_settings
WHERE name IN ('data_directory','config_file','hba_file','log_directory');

output

      name      |                 setting
----------------+-----------------------------------------
 config_file    | /etc/postgresql/16/main/postgresql.conf
 data_directory | /var/lib/postgresql/16/main
 hba_file       | /etc/postgresql/16/main/pg_hba.conf
 log_directory  | log

Settings changed from the default, and pending restarts

SQL

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

SELECT name, setting FROM pg_settings WHERE pending_restart;   -- edited, but need a restart

Reload the configuration without a restart

SQL

SELECT pg_reload_conf();          -- or from the shell:  pg_ctl reload -D $PGDATA

Reload vs restart: pg_hba.conf changes and most parameters apply on reload. Parameters such as shared_buffers and max_connections need a restart; the pending_restart query shows which ones are waiting.

04 Databases & table sizes

All databases by size (skips the ones you cannot access)

SQL

SELECT datname AS database, pg_get_userbyid(datdba) AS owner,
       CASE WHEN has_database_privilege(datname, 'CONNECT')
            THEN pg_size_pretty(pg_database_size(datname)) ELSE 'no access' END AS size
FROM pg_database WHERE NOT datistemplate
ORDER BY CASE WHEN has_database_privilege(datname, 'CONNECT')
              THEN pg_database_size(datname) END DESC NULLS LAST;

output

 database |   owner   |  size
----------+-----------+---------
 appdb    | postgres  | 65 MB
 shopdb   | app_owner | 44 MB
 postgres | postgres  | 7503 kB

Top 10 tables: total, data, indexes and estimated rows

SQL

SELECT schemaname AS schema, relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid))       AS data,
       pg_size_pretty(pg_indexes_size(relid))        AS indexes,
       n_live_tup AS est_rows
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

output

 schema |   table_name   |  total  |  data   | indexes | est_rows
--------+----------------+---------+---------+---------+----------
 sales  | orders         | 28 MB   | 18 MB   | 9840 kB |   300000
 sales  | customers      | 8384 kB | 3744 kB | 4600 kB |    50000
 hr     | employees      | 96 kB   | 32 kB   | 32 kB   |      500

total includes indexes and TOAST. Single objects: SELECT pg_size_pretty(pg_database_size('shopdb')); and SELECT pg_size_pretty(pg_total_relation_size('sales.orders'));

Tables using more than half of all table space

SQL

SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_stat_user_tables
WHERE pg_total_relation_size(relid) >
      0.5 * (SELECT sum(pg_total_relation_size(relid)) FROM pg_stat_user_tables);

Empty tables

SQL

SELECT schemaname, relname FROM pg_stat_user_tables WHERE n_live_tup = 0 ORDER BY 1, 2;

Row counts are estimates: n_live_tup and reltuples come from statistics, not a count. Since PostgreSQL 14 a never-analyzed table shows reltuples = -1, so the old "reltuples = 0" test misses it. For an exact number use SELECT count(*) (slow on big tables), or run ANALYZE first.

05 Schemas & objects

Schemas with owner and table count

SQL

SELECT n.nspname AS schema, pg_get_userbyid(n.nspowner) AS owner,
       count(c.oid) FILTER (WHERE c.relkind IN ('r','p')) AS tables
FROM pg_namespace n
LEFT JOIN pg_class c ON c.relnamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog','information_schema')
  AND n.nspname NOT LIKE 'pg_toast%' AND n.nspname NOT LIKE 'pg_temp%'
GROUP BY 1, 2 ORDER BY 1;

output

 schema |       owner       | tables
--------+-------------------+--------
 hr     | app_owner         |      2
 public | pg_database_owner |      0
 sales  | app_owner         |      3

Find tables by name

SQL

SELECT table_schema, table_name FROM information_schema.tables
WHERE table_name ILIKE 'order%' AND table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog','information_schema')
ORDER BY 1, 2;

Columns of a table

SQL

SELECT ordinal_position AS pos, column_name, data_type,
       coalesce(character_maximum_length, numeric_precision) AS len,
       is_nullable AS null_ok, column_default
FROM information_schema.columns
WHERE table_schema = 'sales' AND table_name = 'orders'
ORDER BY ordinal_position;

Tables without a primary key

SQL

SELECT n.nspname AS schema, c.relname AS table_name
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 k WHERE k.conrelid = c.oid AND k.contype = 'p')
ORDER BY 1, 2;

output

 schema |   table_name
--------+----------------
 hr     | import_staging
 sales  | order_audit

Why it matters: tables without a primary key cannot be replicated with logical replication for UPDATE/DELETE (unless you set REPLICA IDENTITY), and duplicates creep in.

All constraints with their definitions (PK, FK, unique, check)

SQL

SELECT conrelid::regclass AS table_name, conname,
       CASE contype WHEN 'p' THEN 'PRIMARY KEY' WHEN 'f' THEN 'FOREIGN KEY'
                    WHEN 'u' THEN 'UNIQUE'      WHEN 'c' THEN 'CHECK' END AS type,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE connamespace NOT IN ('pg_catalog'::regnamespace, 'information_schema'::regnamespace)
  AND contype IN ('p','f','u','c')
ORDER BY 1, 3;

output

   table_name    |         conname         |    type     |                     definition
-----------------+-------------------------+-------------+---------------------------------------------------
 sales.orders    | orders_amount_check     | CHECK       | CHECK ((amount >= (0)::numeric))
 sales.orders    | orders_customer_id_fkey | FOREIGN KEY | FOREIGN KEY (customer_id) REFERENCES sales.customers(customer_id)
 sales.orders    | orders_pkey             | PRIMARY KEY | PRIMARY KEY (order_id)

Views, their SQL, and materialized views

SQL

SELECT schemaname, viewname, viewowner FROM pg_views
WHERE schemaname NOT IN ('pg_catalog','information_schema');

SELECT pg_get_viewdef('sales.v_customer_totals'::regclass, true);

SELECT schemaname, matviewname, ispopulated,
       pg_size_pretty(pg_total_relation_size(format('%I.%I', schemaname, matviewname)::regclass)) AS size
FROM pg_matviews;

Triggers with full definition

SQL

SELECT tgrelid::regclass AS table_name, tgname AS trigger_name,
       tgenabled AS enabled, pg_get_triggerdef(oid) AS definition
FROM pg_trigger WHERE NOT tgisinternal;

Functions and procedures

SQL

SELECT n.nspname AS schema, p.proname AS name,
       CASE p.prokind WHEN 'f' THEN 'function' WHEN 'p' THEN 'procedure'
                      WHEN 'a' THEN 'aggregate' WHEN 'w' THEN 'window' END AS kind,
       pg_get_function_identity_arguments(p.oid) AS arguments, l.lanname AS language
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language  l ON l.oid = p.prolang
WHERE n.nspname NOT IN ('pg_catalog','information_schema')
ORDER BY 1, 2;

output

 schema |      name       |   kind    |     arguments     | language
--------+-----------------+-----------+-------------------+----------
 sales  | archive_shipped | procedure | IN p_days integer | plpgsql
 sales  | trg_order_audit | function  |                   | plpgsql

For the source code of one routine: SELECT pg_get_functiondef('sales.archive_shipped'::regproc); or \sf sales.archive_shipped in psql.

06 Users & privileges

Roles and their attributes

SQL

SELECT rolname, rolsuper AS super, rolcanlogin AS login, rolcreatedb AS createdb,
       rolreplication AS repl, rolconnlimit AS conn_limit, rolvaliduntil AS valid_until
FROM pg_roles WHERE rolname !~ '^pg_' ORDER BY rolname;

output

  rolname   | super | login | createdb | repl | conn_limit | valid_until
------------+-------+-------+----------+------+------------+-------------
 app_owner  | f     | t     | f        | f    |         -1 |
 postgres   | t     | t     | t        | t    |         -1 |
 replicator | f     | t     | f        | t    |         -1 |
 report_ro  | f     | t     | f        | f    |         20 |

Avoid pg_shadow in scripts: some cheat sheets list users from pg_shadow, which shows password hashes. Use pg_roles (it masks them). Also check that hashes are SCRAM-SHA-256, not md5: SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';

Connection limit per user

SQL

ALTER ROLE report_ro CONNECTION LIMIT 20;
ALTER ROLE report_ro CONNECTION LIMIT -1;     -- -1 = no limit

SELECT rolname, rolconnlimit FROM pg_roles WHERE rolconnlimit <> -1;

Who can do what on a table, and role membership

SQL

SELECT grantee, table_schema, table_name,
       string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants
WHERE table_schema = 'sales'
GROUP BY 1, 2, 3 ORDER BY 3;

SELECT r.rolname AS role, m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
WHERE m.rolname !~ '^pg_' ORDER BY 2, 1;

Drop a user safely

first attempt

DROP ROLE report_ro;

output

ERROR:  role "report_ro" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema sales
privileges for table sales.customers
privileges for table sales.orders ...

SQL

-- run in EVERY database where the role owns objects or has grants
REASSIGN OWNED BY report_ro TO app_owner;   -- keep the objects, change owner
DROP OWNED BY report_ro;                    -- remove its remaining grants
-- then, once:
DROP ROLE report_ro;

Careful: DROP OWNED drops objects the role still owns. Always run REASSIGN OWNED first unless you really want them gone.

07 Sessions & connections

Connections in use vs max_connections

SQL

SELECT count(*) AS connections,
       current_setting('max_connections')::int AS max_connections,
       round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct_used
FROM pg_stat_activity WHERE backend_type = 'client backend';

Connections by database, user and state

SQL

SELECT datname, usename, state, count(*)
FROM pg_stat_activity WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3 ORDER BY 4 DESC;

What is running right now (longest first)

SQL

SELECT pid, usename, datname, application_name AS app, client_addr, state,
       date_trunc('second', now() - query_start) AS running_for,
       wait_event_type || ':' || wait_event AS waiting_on,
       left(query, 50) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY query_start;

output (some columns trimmed)

 pid | usename  |     app      |        state        | running_for |     waiting_on     |              query
-----+----------+--------------+---------------------+-------------+--------------------+---------------------------------
 407 | postgres | billing_job  | idle in transaction | 00:00:04    | Client:ClientRead  | UPDATE sales.orders SET status=
 415 | postgres | shipping_api | active              | 00:00:03    | Lock:transactionid | UPDATE sales.orders SET status=

Idle in transaction: the silent lock holders

SQL

SELECT pid, usename, application_name,
       date_trunc('second', now() - state_change) AS idle_in_tx_for,
       left(query, 40) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change;

A session that opened a transaction and went quiet still holds its locks. Protect the server with idle_in_transaction_session_timeout (for example ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';).

08 Locks & blocking

Who is blocking whom

SQL

SELECT blocked.pid             AS blocked_pid,
       blocked.usename         AS blocked_user,
       date_trunc('second', now() - blocked.query_start) AS waiting_for,
       left(blocked.query, 45) AS blocked_query,
       blocker.pid             AS blocking_pid,
       blocker.state           AS blocker_state,
       left(blocker.query, 45) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocker ON blocker.pid = ANY (pg_blocking_pids(blocked.pid));

output (some columns trimmed)

 blocked_pid | waiting_for |        blocked_query          | blocking_pid |    blocker_state    |        blocking_query
-------------+-------------+-------------------------------+--------------+---------------------+-------------------------------
         415 | 00:00:03    | UPDATE sales.orders SET statu |          407 | idle in transaction | UPDATE sales.orders SET statu

Locks held and waited for in this database

SQL

SELECT l.pid, l.locktype, l.relation::regclass AS relation, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE a.datname = current_database() AND l.relation IS NOT NULL
  AND l.pid <> pg_backend_pid()
ORDER BY l.granted, l.pid;

Cancel or terminate

SQL

SELECT pg_cancel_backend(407);      -- stop the current query, keep the session
SELECT pg_terminate_backend(407);   -- end the session and roll back its transaction

In our test the blocker was idle in transaction. pg_cancel_backend returned t but the other session stayed blocked, because there was no running query to cancel. Only pg_terminate_backend released the lock:

output

after cancel:    blocked sessions = 1
after terminate: blocked sessions = 0

Before you terminate: check what the session is (application, user, how long the transaction has been open). Terminating rolls back all its uncommitted work.

09 Export & import CSV

Export with \copy (works for any user, file is written on your machine)

psql (\copy must be on ONE line)

\copy (SELECT customer_id, name, email FROM sales.customers ORDER BY customer_id) TO '/tmp/customers.csv' WITH (FORMAT csv, HEADER)

/tmp/customers.csv

customer_id,name,email
1,Customer 1,c1@example.com
2,Customer 2,c2@example.com

One-liner from the shell (PostgreSQL 12+)

$ shell

psql -h dbhost -U report_ro -d shopdb --csv \
     -c "SELECT status, count(*) AS orders, sum(amount) AS revenue FROM sales.orders GROUP BY status" \
     -o status.csv

# from a remote server over ssh
ssh dbhost "psql -d shopdb -c \"COPY (SELECT * FROM sales.customers) TO STDOUT WITH CSV HEADER\"" > customers.csv

Import

psql

\copy sales.customers(name, email) FROM 'new_customers.csv' WITH (FORMAT csv, HEADER)

COPY vs \copy: server-side COPY ... TO/FROM file reads and writes files on the database server and needs the pg_write_server_files / pg_read_server_files role. \copy runs through psql and uses your machine's files, so it is the one to use as a normal user.

10 Backup quick reference

$ shell

pg_dump -h dbhost -U postgres -Fc -f shopdb.dump shopdb                 # one compressed file (recommended)
pg_dump -h dbhost -U postgres -Fd -j 4 -f shopdb_dir shopdb             # directory format, 4 parallel jobs
pg_dump -U postgres -Fc -t sales.orders -f orders.dump shopdb            # one table
pg_dump -U postgres -Fc -t 'sales.order*' -T sales.order_audit -f o.dump shopdb   # pattern, minus one table
pg_dump -U postgres -Fc -n sales -N hr -f sales.dump shopdb              # one schema
pg_dump -U postgres --schema-only -f shopdb_ddl.sql shopdb               # DDL only
pg_dumpall -U postgres --globals-only -f globals.sql                     # roles and tablespaces

pg_restore -l shopdb.dump                                                # list contents (verify)
createdb -U postgres shopdb_copy
pg_restore -U postgres -d shopdb_copy -j 4 shopdb.dump                   # parallel restore

pg_dump never includes roles or tablespaces, so keep a pg_dumpall --globals-only next to your dumps. For a full backup and restore tool with checksums and verification, see my pg_toolkit post.

11 Replication health

On the primary: connected standbys and lag

SQL

SELECT application_name, 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;

output

 application_name | client_addr |   state   | sync_state | replay_lag_bytes |    write_lag    |   replay_lag
------------------+-------------+-----------+------------+------------------+-----------------+-----------------
 16/standby       | 127.0.0.1   | streaming | async      | 0 bytes          | 00:00:00.001443 | 00:00:00.011197

Replication slots: inactive slots keep WAL forever

SQL

SELECT slot_name, slot_type, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Watch inactive slots: a slot whose standby is gone keeps every WAL file until the disk fills. Drop it with SELECT pg_drop_replication_slot('name');, or cap it with max_slot_wal_keep_size (PostgreSQL 13+).

On the standby: am I in recovery, and how far behind?

on the STANDBY

SELECT pg_is_in_recovery() AS is_standby;

SELECT pg_last_wal_receive_lsn() AS received,
       pg_last_wal_replay_lsn()  AS replayed,
       pg_last_xact_replay_timestamp() AS last_replayed_tx,
       CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN interval '0'
            ELSE date_trunc('second', now() - pg_last_xact_replay_timestamp()) END AS replay_delay;

SELECT status, sender_host, sender_port, slot_name FROM pg_stat_wal_receiver;

output

 received  |  replayed  |       last_replayed_tx        | replay_delay
------------+------------+-------------------------------+--------------
 0/1D6A89C0 | 0/1D6A89C0 | 2026-10-28 07:16:21.937077+00 | 00:00:00

  status   | sender_host | sender_port | slot_name
-----------+-------------+-------------+-----------
 streaming | localhost   |        5432 | standby1

12 Fixed from older cheat sheets

These commands appear in many PostgreSQL cheat sheets. Here is what they do on a current server:

Old command	What happens now	Use instead
SELECT pg_reload_config();	ERROR: function does not exist (it never existed)	SELECT pg_reload_conf();
pg_last_xlog_receive_location(), pg_last_xlog_replay_location()	ERROR: renamed in PostgreSQL 10	pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()
pgc.consrc in pg_constraint	ERROR: column does not exist (removed in 12)	pg_get_constraintdef(oid)
count(distinct(numbackends)) FROM pg_stat_database	Wrong answer: returned 2 with 1 connection; it counts distinct values, not connections	sum(numbackends) or count(*) FROM pg_stat_activity
... FROM pg_shadow	Works, but prints password hashes	pg_roles
WHERE c.reltuples = 0 for empty tables	Misses never-analyzed tables (they show -1 since PG 14)	n_live_tup = 0 after ANALYZE
pg_dumpall --tablespaces-only > allroles.sql	Works, but the file name says roles	--roles-only / --globals-only with matching names
Master / slave	Old terminology	Primary / standby

Tip: save the queries you use most in a file and load them with \i, or add shortcuts to ~/.psqlrc, e.g. \set blocking 'SELECT ... ;' then type :blocking.

© 2026 Tamilselvan Perumal · TechStarAdmin. All queries tested on PostgreSQL 16.15 with a streaming standby; they work on PostgreSQL 13 and later. Please link back instead of copying.
Originally published at techstaradmin.blogspot.com. Test on a non-production system before running terminate or drop commands.