208 commands · 12 cheat sheets · SQL
Operations, Replication, and Storage master quick reference
Browse 208 commands from 12 focused cheat sheets on page 1 of 1. Each example opens its matching detailed section.
Operations, Replication, and Storage · 15 commands
PostgreSQL Backup, Restore, and Point-in-Time Recovery Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Plain SQL dump | pg_dump --dbname=appdb --format=plain --file=appdb.sql | View examples |
| Custom archive | pg_dump --dbname=appdb --format=custom --file=appdb.dump | View examples |
| Parallel directory dump | pg_dump --dbname=appdb --format=directory --jobs=4 --file=appdb.dir | View examples |
| Dump cluster globals | pg_dumpall --globals-only --file=globals.sql | View examples |
| Dump definitions only | pg_dump --dbname=appdb --schema-only --file=schema.sql | View examples |
| Dump data only | pg_dump --dbname=appdb --data-only --format=custom --file=data.dump | View examples |
| Inspect archive contents | pg_restore --list appdb.dump | View examples |
| Restore and create database | pg_restore --create --dbname=postgres appdb.dump | View examples |
| Restore in parallel | pg_restore --dbname=restore_test --jobs=4 appdb.dump | View examples |
| Drop archived objects first | pg_restore --clean --if-exists --dbname=restore_test appdb.dump | View examples |
| Restore plain SQL | psql --
SET=ON_ERROR_STOP=
ON --dbname=restore_test --file=appdb.sql | View examples |
| Take a physical base backup | pg_basebackup --pgdata=/backup/base --format=plain --wal-method=stream --progress | View examples |
| Verify backup manifest | pg_verifybackup /backup/base | View examples |
| Set a recovery time | recovery_target_time = '2026-08-12 14:30:00+00' | View examples |
| Pause at the recovery target | recovery_target_action = 'pause' | View examples |
Operations, Replication, and Storage · 29 commands
PostgreSQL Connection Authentication and pg_hba.conf Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Require SCRAM for one application path | hostssl orders +orders_login 10.24.0.0/16 scram-sha-256 | View examples |
| Reject a specific source before a broad allow | host all all 10.24.9.18/32 reject | View examples |
| Reject non-TLS TCP connections | hostnossl all all 0.0.0.0/0 reject | View examples |
| Match a database named after the role | hostssl sameuser all 10.24.0.0/16 scram-sha-256 | View examples |
| Match members of a login role | hostssl orders +orders_login 10.24.0.0/16 scram-sha-256 | View examples |
| Match physical replication connections | hostssl replication replicator 10.24.40.12/32 scram-sha-256 | View examples |
| Include an ordered policy directory | include_dir "pg_hba.d" | View examples |
| Include an optional local policy | include_if_exists "pg_hba.local.conf" | View examples |
| Locate the active HBA path | SHOW hba_file; | View examples |
| Find parse errors in HBA files | SELECT file_name, line_number, error
FROM pg_hba_file_rules
WHERE error IS NOT NULL; | View examples |
| Review normalized rule order | SELECT rule_number,
type,
database,
user_name,
address,
auth_method
FROM pg_hba_file_rules
ORDER BY rule_number; | View examples |
| Request a configuration reload | SELECT pg_reload_conf(); | View examples |
| Store newly set passwords as SCRAM verifiers | ALTER SYSTEM
SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf(); | View examples |
| Reset a password without exposing it in SQL history | \password orders_app | View examples |
| Require SCRAM in the final HBA policy | hostssl orders orders_app 10.24.0.0/16 scram-sha-256 | View examples |
| Require TLS, a client certificate, and SCRAM | hostssl orders +orders_login 10.24.0.0/16 scram-sha-256 clientcert=verify-full | View examples |
| Authenticate with a client certificate | hostssl orders +orders_login 10.24.0.0/16 cert map=client_certs | View examples |
| Verify the server and require channel binding | sslmode=verify-full gssencmode=disable sslrootcert=/etc/postgresql/app-ca.crt channel_binding=require require_auth=scram-sha-256 | View examples |
| Authenticate a local socket user with peer | local admin postgres peer map=local_admins | View examples |
| Map an OS account to a database role | local_admins db-operator postgres | View examples |
| Use ident only on a tightly controlled network | host admin db_operator 10.24.60.0/24 ident map=managed_hosts | View examples |
| Use encrypted LDAP search and bind | hostssl all +directory_login 10.24.0.0/16 ldap ldapserver=ldap.example.com ldaptls=1 ldapbasedn="dc=example,dc=com" | View examples |
| Authenticate a Kerberos principal with GSSAPI | host all +staff 10.24.0.0/16 gss include_realm=1 krb_realm=EXAMPLE.COM map=corp_gss | View examples |
| Require GSSAPI-encrypted transport | hostgssenc all +staff 10.24.0.0/16 gss include_realm=1 krb_realm=EXAMPLE.COM map=corp_gss | View examples |
| Scope a password-file entry tightly | db.example.com:5432:orders:orders_app:replace-
WITH-managed-secret | View examples |
| Restrict a Unix password file | chmod 0600 /run/secrets/orders.pgpass | View examples |
| Connect through a named service profile | psql service=orders-prod | View examples |
| Verify the current authenticated session | SELECT current_user,
current_database(),
inet_client_addr(),
ssl
FROM pg_stat_ssl
JOIN pg_stat_activity
USING (pid)
WHERE pid = pg_backend_pid(); | View examples |
| Label canary connection attempts | psql 'service=orders-canary application_name=hba-rollout-canary' | View examples |
Operations, Replication, and Storage · 17 commands
PostgreSQL Database Configuration and Session Settings Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Show one effective value | SHOW statement_timeout; | View examples |
| Read a setting safely | SELECT current_setting('app.request_id', true); | View examples |
| Inspect source and context | SELECT name,
setting,
unit,
context,
source,
pending_restart
FROM pg_settings
WHERE name = 'shared_buffers'; | View examples |
| Set for this session | SET SESSION statement_timeout = '30s'; | View examples |
| Set for one transaction | SET LOCAL lock_timeout = '2s'; | View examples |
| Set through a function | SELECT set_config('app.request_id', 'req-9f2a', true); | View examples |
| Reset one override | RESET statement_timeout; | View examples |
| Set a database default | ALTER DATABASE appdb SET statement_timeout = '60s'; | View examples |
| Set a role default | ALTER ROLE report_reader
SET default_transaction_read_only =
ON; | View examples |
| Set a role-database default | ALTER ROLE report_reader IN DATABASE appdb
SET statement_timeout = '15s'; | View examples |
| Write a cluster override | ALTER SYSTEM SET log_min_duration_statement = '1s'; | View examples |
| Reload configuration | SELECT pg_reload_conf(); | View examples |
| Validate file entries | SELECT sourcefile, sourceline, name, setting, error
FROM pg_file_settings
WHERE error IS NOT NULL OR NOT applied; | View examples |
| Find pending restarts | SELECT name, setting, sourcefile
FROM pg_settings
WHERE pending_restart
ORDER BY name; | View examples |
| Delegate one setting | GRANT
SET
ON PARAMETER statement_timeout TO app_operator; | View examples |
| Use a trusted search path | SET LOCAL search_path = pg_catalog, app; | View examples |
| Inspect capacity settings | SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('work_mem', 'max_connections', 'max_wal_senders'); | View examples |
Operations, Replication, and Storage · 14 commands
PostgreSQL LISTEN, NOTIFY, and Event Messaging Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Listen on a channel | LISTEN order_events; | View examples |
| Stop one listener | UNLISTEN order_events; | View examples |
| Stop all listeners | UNLISTEN *; | View examples |
| Send a literal payload | NOTIFY order_events, 'order:8421'; | View examples |
| Send a dynamic payload | SELECT pg_notify('order_events', json_build_object('id', 8421)::text); | View examples |
| Notify from a trigger | CREATE TRIGGER orders_notify AFTER INSERT
ON app.orders FOR EACH ROW EXECUTE FUNCTION app.notify_order(); | View examples |
| Persist an outbox event | INSERT INTO app.event_outbox (topic, aggregate_id, payload)
VALUES ('order.created', 8421, '{"version":1}'::jsonb); | View examples |
| Claim pending events | SELECT event_id
FROM app.event_outbox
WHERE published_at IS NULL
ORDER BY event_id FOR
UPDATE SKIP LOCKED
LIMIT 100; | View examples |
| Identify this backend | SELECT pg_backend_pid(); | View examples |
| List active registrations | SELECT * FROM pg_listening_channels(); | View examples |
| Measure queue occupancy | SELECT pg_notification_queue_usage(); | View examples |
| Inspect queue capacity | SHOW max_notify_queue_pages; | View examples |
| Find old listener transactions | SELECT pid, xact_start, state
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND xact_start IS NOT NULL
ORDER BY xact_start; | View examples |
| Version an event hint | SELECT pg_notify('order_events', json_build_object('v', 1, 'event_id', 9001)::text); | View examples |
Operations, Replication, and Storage · 16 commands
PostgreSQL Logical Replication Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Enable logical WAL | ALTER SYSTEM SET wal_level = 'logical'; | View examples |
| Inspect worker capacity | SHOW max_replication_slots; SHOW max_wal_senders; SHOW max_logical_replication_workers; | View examples |
| Publish explicit tables | CREATE PUBLICATION app_pub FOR TABLE app.customers, app.orders; | View examples |
| Restrict operations | CREATE PUBLICATION audit_pub FOR TABLE app.audit_log
WITH (publish = 'insert'); | View examples |
| Filter published rows | CREATE PUBLICATION eu_pub FOR TABLE app.accounts
WHERE (region = 'EU'); | View examples |
| Publish selected columns | CREATE PUBLICATION profile_pub FOR TABLE app.profiles (id, display_name, updated_at); | View examples |
| Choose replica identity index | ALTER TABLE app.events REPLICA IDENTITY
USING INDEX events_replica_key; | View examples |
| Use full-row identity | ALTER TABLE app.legacy_rows REPLICA IDENTITY FULL; | View examples |
| Create subscription | CREATE SUBSCRIPTION app_sub CONNECTION :'publisher_conninfo' PUBLICATION app_pub; | View examples |
| Create disabled subscription | CREATE SUBSCRIPTION app_sub CONNECTION 'host=publisher dbname=app user=logical_sub' PUBLICATION app_pub
WITH (connect=false); | View examples |
| Refresh table membership | ALTER SUBSCRIPTION app_sub REFRESH PUBLICATION
WITH (copy_data = true); | View examples |
| Stop subscription workers | ALTER SUBSCRIPTION app_sub DISABLE; | View examples |
| Keep table-owner execution | ALTER SUBSCRIPTION app_sub
SET (run_as_owner = false, password_required = true); | View examples |
| Inspect apply workers | SELECT subname,
worker_type,
pid,
received_lsn,
latest_end_lsn
FROM pg_stat_subscription; | View examples |
| Measure retained WAL | SELECT slot_name,
active,
wal_status,
pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots; | View examples |
| Enable failover slot handling | ALTER SUBSCRIPTION app_sub SET (failover = true); | View examples |
Operations, Replication, and Storage · 15 commands
PostgreSQL Monitoring: Sessions, Waits, and Progress Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| List active sessions | SELECT pid,
usename,
application_name,
state,
query_start
FROM pg_stat_activity
WHERE state = 'active'; | View examples |
| Measure transaction age | clock_timestamp() - xact_start AS transaction_age | View examples |
| Find idle transactions | WHERE state = 'idle in transaction'
ORDER BY xact_start NULLS LAST | View examples |
| Inspect wait events | SELECT pid, wait_event_type, wait_event
FROM pg_stat_activity
WHERE wait_event IS NOT NULL; | View examples |
| Find direct blockers | SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0; | View examples |
| Bound lock waiting | SET LOCAL lock_timeout = '2s'; | View examples |
| Rank total execution time | SELECT queryid, calls, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20; | View examples |
| Rank mean execution time | SELECT queryid, calls, mean_exec_time
FROM pg_stat_statements
WHERE calls >= 100
ORDER BY mean_exec_time DESC; | View examples |
| Calculate cache hit ratio | blks_hit::numeric / NULLIF(blks_hit + blks_read, 0) | View examples |
| Inspect backend I/O | SELECT backend_type,
object,
context,
reads,
read_time,
writes,
write_time
FROM pg_stat_io; | View examples |
| Monitor VACUUM progress | SELECT pid,
relid::regclass,
phase,
heap_blks_scanned,
heap_blks_total
FROM pg_stat_progress_vacuum; | View examples |
| Monitor index creation | SELECT pid,
relid::regclass,
phase,
blocks_done,
blocks_total
FROM pg_stat_progress_create_index; | View examples |
| Measure replay byte lag | pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_byte_lag | View examples |
| Inspect replication slots | SELECT slot_name,
slot_type,
active,
restart_lsn,
wal_status
FROM pg_replication_slots; | View examples |
| Refresh cached statistics | SELECT pg_stat_clear_snapshot(); | View examples |
Operations, Replication, and Storage · 14 commands
PostgreSQL postgres_fdw and Foreign Tables Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Enable postgres_fdw | CREATE EXTENSION postgres_fdw; | View examples |
| Define a remote server | CREATE SERVER warehouse_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db.internal', dbname 'warehouse', sslmode 'verify-full'); | View examples |
| Grant server usage | GRANT USAGE
ON FOREIGN SERVER warehouse_server TO reporting_role; | View examples |
| Map a local role | CREATE USER MAPPING FOR reporting_role SERVER warehouse_server OPTIONS (user 'warehouse_reader', password :'fdw_password'); | View examples |
| Import selected tables | IMPORT FOREIGN SCHEMA analytics
LIMIT TO (daily_sales, customers)
FROM SERVER warehouse_server INTO warehouse_fdw; | View examples |
| Define a foreign table | CREATE FOREIGN TABLE warehouse_fdw.daily_sales (sale_date date, total numeric) SERVER warehouse_server OPTIONS (schema_name 'analytics'); | View examples |
| Inspect shipped SQL | EXPLAIN (VERBOSE, COSTS)
SELECT *
FROM warehouse_fdw.daily_sales
WHERE sale_date >= DATE '2026-08-01'; | View examples |
| Request remote estimates | ALTER SERVER warehouse_server OPTIONS (ADD use_remote_estimate 'true'); | View examples |
| Collect local estimates | ANALYZE warehouse_fdw.daily_sales; | View examples |
| Tune rows per fetch | ALTER FOREIGN TABLE warehouse_fdw.daily_sales OPTIONS (ADD fetch_size '1000'); | View examples |
| Batch foreign inserts | ALTER FOREIGN TABLE warehouse_fdw.staging_sales OPTIONS (ADD batch_size '200'); | View examples |
| Insert into a foreign table | INSERT INTO warehouse_fdw.staging_sales (sale_id, amount)
VALUES ($1, $2); | View examples |
| List open FDW connections | SELECT * FROM postgres_fdw_get_connections(); | View examples |
| Close an unused connection | SELECT postgres_fdw_disconnect('warehouse_server'); | View examples |
Operations, Replication, and Storage · 19 commands
PostgreSQL psql CLI and Meta-Commands Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Connect with a service | psql service=reporting | View examples |
| Override database and role | psql -d appdb -U app_reader | View examples |
| Suppress startup files | psql -X -d appdb | View examples |
| Stop after an error | psql -X -v ON_ERROR_STOP=1 -f migration.sql appdb | View examples |
| Wrap a file atomically | psql -X -1 -v ON_ERROR_STOP=1 -f migration.sql appdb | View examples |
| Execute one SQL command | psql -X -d appdb -c 'SELECT current_timestamp;' | View examples |
| Set a script variable | psql -v tenant=acme -f report.sql appdb | View examples |
| Quote a literal variable | SELECT :'tenant' AS tenant; | View examples |
| Quote an identifier | SELECT count(*) FROM :"table_name"; | View examples |
| List relations | \dt app.* | View examples |
| Describe a relation | \d+ app.orders | View examples |
| Show the current connection | \conninfo | View examples |
| Enable expanded display | \x auto | View examples |
| Select CSV output | \pset format csv | View examples |
| Hide headings and row counts | \t on | View examples |
| Include relative to script | \ir fragments/indexes.sql | View examples |
| Print the query buffer | \p | View examples |
| Capture one result row | SELECT current_user AS actor \gset session_ | View examples |
| Time subsequent commands | \timing on | View examples |
Operations, Replication, and Storage · 16 commands
PostgreSQL Streaming Replication and Failover Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Inspect sender capacity | SHOW wal_level; SHOW max_wal_senders; SHOW max_replication_slots; | View examples |
| Create replication login | CREATE ROLE standby_rep LOGIN REPLICATION; \password standby_rep | View examples |
| Create physical slot | SELECT *
FROM pg_create_physical_replication_slot('standby_a'); | View examples |
| Seed a standby | pg_basebackup -h primary.example -U standby_rep -D /pgdata/18 -R -X stream -S standby_a -C --progress | View examples |
| Configure upstream | primary_conninfo = 'host=primary.example user=standby_rep application_name=standby_a sslmode=verify-full' | View examples |
| Use permanent slot | primary_slot_name = 'standby_a' | View examples |
| Inspect WAL senders | SELECT application_name,
state,
sync_state,
sent_lsn,
flush_lsn,
replay_lsn
FROM pg_stat_replication; | View examples |
| Measure replay byte lag | SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication; | View examples |
| Check standby replay | SELECT pg_is_in_recovery(),
pg_last_wal_receive_lsn(),
pg_last_wal_replay_lsn(),
pg_last_xact_replay_timestamp(); | View examples |
| Require priority standbys | synchronous_standby_names = 'FIRST 1 (standby_a, standby_b)' | View examples |
| Require standby quorum | synchronous_standby_names = 'ANY 2 (standby_a, standby_b, standby_c)' | View examples |
| Wait for replay | SET LOCAL synchronous_commit = 'remote_apply'; | View examples |
| Inspect recovery conflicts | SELECT datname,
confl_snapshot,
confl_lock,
confl_bufferpin,
confl_deadlock
FROM pg_stat_database_conflicts; | View examples |
| Verify server role | SELECT pg_is_in_recovery(); | View examples |
| Promote an approved standby | SELECT pg_promote(wait => true, wait_seconds => 60); | View examples |
| Preview pg_rewind | pg_rewind --target-pgdata=/pgdata/18 --source-server='host=new-primary dbname=postgres user=rewind' --dry-run --progress | View examples |
Operations, Replication, and Storage · 17 commands
PostgreSQL Tablespaces, Storage, and Fillfactor Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Create a tablespace | CREATE TABLESPACE fastspace LOCATION '/srv/postgresql/fastspace'; | View examples |
| List tablespaces | SELECT spcname, pg_tablespace_location(oid)
FROM pg_tablespace; | View examples |
| Grant create privilege | GRANT CREATE ON TABLESPACE fastspace TO app_owner; | View examples |
| Place a new table | CREATE TABLE app.events (id bigint, payload jsonb) TABLESPACE fastspace; | View examples |
| Place a new index | CREATE INDEX events_id_idx
ON app.events (id) TABLESPACE fastspace; | View examples |
| Move a table | ALTER TABLE app.events SET TABLESPACE fastspace; | View examples |
| Set heap fillfactor | ALTER TABLE app.sessions SET (fillfactor = 80); | View examples |
| Reset heap fillfactor | ALTER TABLE app.sessions RESET (fillfactor); | View examples |
| Set index fillfactor | ALTER INDEX app.events_tenant_time_idx
SET (fillfactor = 80); | View examples |
| Repack an index concurrently | REINDEX INDEX CONCURRENTLY app.events_tenant_time_idx; | View examples |
| Prefer external storage | ALTER TABLE app.events ALTER COLUMN payload
SET STORAGE EXTERNAL; | View examples |
| Set TOAST autovacuum scale | ALTER TABLE app.events
SET (toast.autovacuum_vacuum_scale_factor = 0.05); | View examples |
| Set a vacuum scale factor | ALTER TABLE app.job_queue
SET (autovacuum_vacuum_scale_factor = 0.02); | View examples |
| Reset table overrides | ALTER TABLE app.job_queue RESET (autovacuum_vacuum_scale_factor); | View examples |
| Show a relation tablespace | SELECT reltablespace
FROM pg_class
WHERE oid = 'app.events'::regclass; | View examples |
| Measure a tablespace | SELECT pg_size_pretty(pg_tablespace_size('fastspace')); | View examples |
| Drop an empty tablespace | DROP TABLESPACE fastspace; | View examples |
Operations, Replication, and Storage · 18 commands
PostgreSQL VACUUM, Autovacuum, and Bloat Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Vacuum one table | VACUUM public.orders; | View examples |
| Vacuum and refresh statistics | VACUUM (ANALYZE, VERBOSE) public.orders; | View examples |
| Inspect table maintenance health | SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC; | View examples |
| Find stale planner statistics | SELECT relname, n_mod_since_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC; | View examples |
| Monitor active vacuum work | SELECT pid,
relid::regclass,
phase,
heap_blks_scanned,
heap_blks_total
FROM pg_stat_progress_vacuum; | View examples |
| Identify autovacuum sessions | SELECT pid,
backend_type,
query_start,
wait_event_type,
wait_event
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker'; | View examples |
| Inspect autovacuum settings | SELECT name, setting, unit
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name; | View examples |
| Tune a high-churn table | ALTER TABLE public.orders
SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 5000); | View examples |
| Inspect table overrides | SELECT relname, reloptions
FROM pg_class
WHERE oid = 'public.orders'::regclass; | View examples |
| Rank database XID age | SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC; | View examples |
| Rank table XID age | SELECT oid::regclass, age(relfrozenxid) AS xid_age
FROM pg_class
WHERE relkind IN ('r','m')
ORDER BY xid_age DESC; | View examples |
| Find old transaction horizons | SELECT pid, xact_start, backend_xmin, age(backend_xmin)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC; | View examples |
| Find old prepared transactions | SELECT gid, prepared, owner, database, age(transaction)
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC; | View examples |
| Inspect replication slot horizons | SELECT slot_name, slot_type, active, xmin, catalog_xmin
FROM pg_replication_slots
ORDER BY slot_name; | View examples |
| Measure HOT update use | SELECT relname, n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC; | View examples |
| Reserve heap page space | CREATE TABLE account_state (account_id bigint PRIMARY KEY, status text NOT NULL)
WITH (fillfactor = 80); | View examples |
| Compare table and total size | SELECT pg_relation_size('public.orders'),
pg_total_relation_size('public.orders'); | View examples |
| Rank total relation sizes | SELECT relid::regclass,
pg_total_relation_size(relid) AS bytes
FROM pg_stat_user_tables
ORDER BY bytes DESC; | View examples |
Operations, Replication, and Storage · 18 commands
PostgreSQL WAL, Checkpoints, and Durability Cheat Sheet
| Use | Syntax | Examples |
|---|---|---|
| Check fsync | SHOW fsync; | View examples |
| Check full-page writes | SHOW full_page_writes; | View examples |
| Inspect the sync method | SHOW wal_sync_method; | View examples |
| Use durable local commit | SET LOCAL synchronous_commit = on; | View examples |
| Allow asynchronous commit | SET LOCAL synchronous_commit = off; | View examples |
| Wait for replay | SET LOCAL synchronous_commit = remote_apply; | View examples |
| Show checkpoint interval | SHOW checkpoint_timeout; | View examples |
| Show WAL soft limit | SHOW max_wal_size; | View examples |
| Spread checkpoint writes | ALTER SYSTEM SET checkpoint_completion_target = 0.9; | View examples |
| Inspect checkpoint counters | SELECT * FROM pg_stat_checkpointer; | View examples |
| Inspect WAL generation | SELECT wal_records, wal_fpi, wal_bytes, wal_buffers_full
FROM pg_stat_wal; | View examples |
| Check archive status | SELECT archived_count,
failed_count,
last_failed_wal,
stats_reset
FROM pg_stat_archiver; | View examples |
| Check replication slots | SELECT slot_name, active, restart_lsn, wal_status
FROM pg_replication_slots; | View examples |
| Request a WAL switch | SELECT pg_switch_wal(); | View examples |
| Force a checkpoint | CHECKPOINT; | View examples |
| Check checkpoint role membership | SELECT pg_has_role(current_user, 'pg_checkpoint', 'MEMBER'); | View examples |
| Read the current WAL LSN | SELECT pg_current_wal_lsn(); | View examples |
| Inspect checksum state | SHOW data_checksums; | View examples |