PostgreSQL Common Commands - CodeSwordsman

High-frequency psql commands for connections, execution plans, pg_dump backups, role grants and lock troubleshooting across versions 12 to 17.

PostgreSQL 12–17Updated 2026-10-3029 commands

Connection & Meta Commands

psql -h 127.0.0.1 -p 5432 -U postgres -d appdb -W

Connect with a URI string that carries options such as sslmode, handy in containers and CI

psql "postgresql://app:pass@10.0.0.12:5432/appdb?sslmode=require"

List databases with their encoding and owner

\l

List tables in the current schema with sizes; use \dn for schemas

\dt+

Show a table structure including indexes, constraints and triggers

\d <TABLE>

List roles with their attributes and memberships

\du

Switch to another database, which reconnects under the hood

\c appdb

Run the query and report real timing, row counts and buffer usage

explain (analyze, buffers) select * from orders where user_id = 42;

Show active sessions and their running statements

select pid, state, wait_event_type, query from pg_stat_activity where state <> 'idle';

Toggle statement timing to see how long each query takes

\timing

Paginate results; for deep pages prefer keyset pagination over a large offset

select * from orders order by id desc limit 20 offset 40;

Toggle expanded display so wide rows stay readable

\x

Custom-format dump of one database, enabling parallel and selective restores

pg_dump -h 127.0.0.1 -U postgres -Fc -d appdb -f appdb.dump

Combine with pg_restore -j for parallel restores; plain SQL dumps compress worse

Dump the schema without any data

pg_dump -U postgres -s -d appdb -f appdb-schema.sql

Dump every database plus global objects such as roles and tablespaces

pg_dumpall -U postgres -f all.sql

Restore a custom-format dump with four parallel jobs

pg_restore -h 127.0.0.1 -U postgres -d appdb -j 4 appdb.dump

Run a plain SQL dump file to restore it

psql -h 127.0.0.1 -U postgres -d appdb -f appdb-schema.sql

Create a login role; in PostgreSQL a user is simply a role with login

create role app login password '<PASSWORD>';

Create a database owned by the given role

create database appdb owner app;

Grant connection and schema usage privileges

grant connect, usage on schema public to app;

Grant CRUD privileges on existing tables

grant select, insert, update, delete on all tables in schema public to app;

This does not cover tables created later; use ALTER DEFAULT PRIVILEGES or change the owner

Show privileges granted on a table

\dp orders

Set a default search_path for the role so callers need not qualify schema names

alter role app set search_path = app, public;

Find the slowest statements on average; the extension must be loaded first

select query, calls, mean_exec_time from pg_stat_statements order by mean_exec_time desc limit 10;

Show sessions waiting for a lock they cannot get

select a.pid, a.query, l.mode, l.granted
from pg_locks l
join pg_stat_activity a on a.pid = l.pid
where not l.granted;

Risky

Terminate a session; its transaction is rolled back

select pg_terminate_backend(12345);

Reclaim dead tuples and refresh statistics to improve plans

vacuum (analyze, verbose) orders;

Risky

Rebuild an index online without blocking writes

reindex index concurrently idx_orders_user;

Show database size in human-readable units

select pg_size_pretty(pg_database_size('appdb'));