SQL queries and scripts for PostgreSQL database administration

Tamilselvan Perumal June 01, 2023


Every PostgreSQL DBA ends up with a personal notebook of queries: how big is this database, who is blocking that session, when did autovacuum last run, how do I give this user read-only access? This post is mine, cleaned up and organised so you can find what you need in seconds.

It contains 59 ready-to-use recipes in 12 sections. Each one has a short title, a note where it matters, and code you can copy straight into psql or the shell.

Written for PostgreSQL 13 and later on Linux. Paths like $PGDATA and service names (for example postgresql-16) depend on how PostgreSQL was installed. Lines starting with -- are SQL comments; lines starting with # are shell comments.

Contents

01Connect and find your way around

Connect with psql
psql -h dbhost -p 5432 -U postgres -d appdb

-- Inside psql
\conninfo                 -- who and where am I connected?
\c otherdb                -- switch database
\q                        -- quit
Essential psql meta-commands
\l+        -- databases with size and tablespace
\dn+       -- schemas
\dt+ app.* -- tables in schema app, with size
\d+ app.orders   -- describe a table: columns, indexes, constraints
\di+       -- indexes
\dv        -- views
\ds        -- sequences
\du        -- roles
\db+       -- tablespaces
\dx        -- installed extensions
\x auto    -- expanded output for wide rows
\timing on -- show execution time of every query
\? and \h  -- help for meta-commands and SQL syntax
Current session details
SELECT current_database(), current_user, session_user,
       current_schema(), inet_server_addr(), inet_server_port();

↑ back to contents

02Server information

Version
SELECT version();
SHOW server_version;
-- From the OS
postgres -V
cat $PGDATA/PG_VERSION
Start time and uptime
SELECT pg_postmaster_start_time()                    AS started_at,
       date_trunc('second', now() - pg_postmaster_start_time()) AS uptime;
Where are the data directory and config files?
SHOW data_directory;
SHOW config_file;     -- postgresql.conf
SHOW hba_file;        -- pg_hba.conf
SHOW ident_file;      -- pg_ident.conf
Look up any setting
SHOW work_mem;

SELECT name, setting, unit, source, context, pending_restart
FROM   pg_settings
WHERE  name IN ('shared_buffers','work_mem','max_connections','timezone');
Change a setting and reload
context in pg_settings tells you what is needed: user/superuser = per session, sighup = reload, postmaster = restart.
ALTER SYSTEM SET log_min_duration_statement = '2s';
SELECT pg_reload_conf();

-- When was the config last reloaded? Any errors in the files?
SELECT pg_conf_load_time();
SELECT sourcefile, name, setting, error FROM pg_file_settings WHERE error IS NOT NULL;
Time zone and current time
SHOW timezone;
SELECT now(), clock_timestamp(), current_setting('TimeZone');

↑ back to contents

03Databases

Create a database
CREATE DATABASE appdb
  OWNER      app_owner
  TABLESPACE ts_app
  ENCODING   'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE   'en_US.UTF-8'
  TEMPLATE   template0;

-- or from the shell
createdb -O app_owner appdb
List databases with size
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size,
       datconnlimit
FROM   pg_database
WHERE  NOT datistemplate
ORDER  BY pg_database_size(datname) DESC;
Rename, limit connections, drop
You cannot drop the database you are connected to. WITH (FORCE) (PostgreSQL 13+) disconnects other sessions first.
ALTER DATABASE appdb RENAME TO appdb_old;
ALTER DATABASE appdb CONNECTION LIMIT 50;   -- -1 = unlimited, 0 = block new logins

DROP DATABASE appdb_old;
DROP DATABASE appdb_old WITH (FORCE);       -- PG 13+
dropdb -e appdb_old                          -- from the shell

↑ back to contents

04Schemas, tables and objects

Schemas
CREATE SCHEMA app AUTHORIZATION app_owner;
DROP SCHEMA app;            -- fails if not empty
DROP SCHEMA app CASCADE;    -- drops everything inside, use with care

SELECT nspname, pg_get_userbyid(nspowner) AS owner
FROM   pg_namespace
WHERE  nspname NOT LIKE 'pg_%' AND nspname <> 'information_schema';
Object count per schema and type
SELECT n.nspname AS schema,
       CASE c.relkind WHEN 'r' THEN 'table' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence'
                      WHEN 'v' THEN 'view'  WHEN 'm' THEN 'mat view' WHEN 'p' THEN 'partitioned table'
                      ELSE c.relkind::text END AS type,
       count(*)
FROM   pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE  n.nspname NOT IN ('pg_catalog','information_schema') AND n.nspname NOT LIKE 'pg_toast%'
GROUP  BY 1, 2 ORDER BY 1, 2;
Schema sizes
SELECT n.nspname AS schema,
       pg_size_pretty(SUM(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','m','p')
AND    n.nspname NOT IN ('pg_catalog','information_schema')
GROUP  BY n.nspname
ORDER  BY SUM(pg_total_relation_size(c.oid)) DESC;
Top 10 largest tables (with index and TOAST size)
SELECT schemaname, relname,
       pg_size_pretty(pg_table_size(relid))          AS table_size,
       pg_size_pretty(pg_indexes_size(relid))        AS index_size,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM   pg_stat_user_tables
ORDER  BY pg_total_relation_size(relid) DESC
LIMIT  10;
Size of one table, index or column
SELECT pg_size_pretty(pg_total_relation_size('app.orders'));   -- table + indexes + TOAST
SELECT pg_size_pretty(pg_relation_size('app.orders_pkey'));    -- one index

-- Total bytes used by one column
SELECT pg_size_pretty(SUM(pg_column_size(order_notes))) FROM app.orders;
Physical file behind a table
SELECT pg_relation_filepath('app.orders');   -- relative to $PGDATA
Views, sequences and partitions
SELECT schemaname, viewname FROM pg_views
WHERE  schemaname NOT IN ('pg_catalog','information_schema');

SELECT schemaname, sequencename, last_value, increment_by FROM pg_sequences;
SELECT setval('app.orders_id_seq', 100000);   -- reset a sequence

-- Partitions of a partitioned table
SELECT inhrelid::regclass AS partition,
       pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM   pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE  inhparent = 'app.sales'::regclass;
Foreign keys of a table
SELECT conname, pg_get_constraintdef(oid) AS definition
FROM   pg_constraint
WHERE  conrelid = 'app.orders'::regclass AND contype = 'f';

↑ back to contents

05Indexes

Create and drop indexes
CONCURRENTLY avoids blocking writes on a busy table. It takes longer and cannot run inside a transaction block.
CREATE INDEX idx_orders_cust ON app.orders (customer_id);
CREATE INDEX CONCURRENTLY idx_orders_date ON app.orders (order_date);

-- Partial index: only the rows you actually query
CREATE INDEX idx_orders_open ON app.orders (order_date) WHERE status = 'OPEN';

DROP INDEX CONCURRENTLY idx_orders_date;
List indexes with size and usage
SELECT s.schemaname, s.relname AS table_name, s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
       s.idx_scan,
       pg_get_indexdef(s.indexrelid) AS definition
FROM   pg_stat_user_indexes s
ORDER  BY pg_relation_size(s.indexrelid) DESC;
Rebuild indexes
REINDEX INDEX CONCURRENTLY app.idx_orders_cust;   -- PG 12+
REINDEX TABLE CONCURRENTLY app.orders;
Watch an index build in progress
SELECT pid, relid::regclass AS table_name, index_relid::regclass AS index_name,
       phase, blocks_done, blocks_total,
       round(100.0 * blocks_done / NULLIF(blocks_total,0), 1) AS pct
FROM   pg_stat_progress_create_index;

↑ back to contents

06Users, roles and privileges

Create users and roles
In PostgreSQL a user is simply a role with the LOGIN attribute.
CREATE ROLE app_read NOLOGIN;                                   -- a group role
CREATE ROLE dev_user LOGIN PASSWORD 'Str0ng#Pass' VALID UNTIL '2027-03-31';
CREATE ROLE dba_admin LOGIN SUPERUSER PASSWORD 'An0ther#Pass';  -- use sparingly

GRANT app_read TO dev_user;          -- add a user to a group role
Change or remove a user
Before dropping a role, move or drop what it owns, in every database.
ALTER ROLE dev_user PASSWORD 'New#Pass2026';
ALTER ROLE dev_user VALID UNTIL 'infinity';
ALTER ROLE dev_user NOLOGIN;          -- lock the account
ALTER ROLE dev_user SUPERUSER;        -- or NOSUPERUSER

REASSIGN OWNED BY dev_user TO app_owner;
DROP OWNED BY dev_user;
DROP ROLE dev_user;
List roles and memberships
SELECT rolname, rolsuper, rolcanlogin, rolcreatedb, rolconnlimit, rolvaliduntil
FROM   pg_roles WHERE rolname NOT LIKE 'pg_%' ORDER BY 1;

-- Who is a member of which role?
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
ORDER  BY 1, 2;
Grant access: database → schema → objects
Access is layered. A user needs CONNECT on the database and USAGE on the schema before table grants are of any use.
GRANT CONNECT ON DATABASE appdb TO app_read;
GRANT USAGE   ON SCHEMA app      TO app_read;
GRANT SELECT  ON ALL TABLES IN SCHEMA app TO app_read;

-- Make the grant apply to tables created in future too
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_read;

-- Write access, functions, tablespaces
GRANT INSERT, UPDATE, DELETE ON app.orders TO app_write;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_write;
GRANT EXECUTE ON FUNCTION app.calc_tax(numeric) TO app_write;
GRANT CREATE ON TABLESPACE ts_app TO app_owner;
Revoke access
REVOKE INSERT, UPDATE, DELETE ON app.orders FROM app_write;
REVOKE ALL ON ALL TABLES IN SCHEMA app FROM dev_user;
REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;   -- stop every role connecting by default
What can a user do?
-- Table privileges
SELECT table_schema, table_name, string_agg(privilege_type, ', ') AS privileges
FROM   information_schema.role_table_grants
WHERE  grantee = 'app_read'
GROUP  BY 1, 2 ORDER BY 1, 2;

-- Quick yes/no checks
SELECT has_table_privilege('app_read', 'app.orders', 'SELECT'),
       has_schema_privilege('app_read', 'app', 'USAGE'),
       has_database_privilege('app_read', 'appdb', 'CONNECT');
search_path per user
SHOW search_path;
ALTER ROLE dev_user SET search_path = app, public;
ALTER ROLE dev_user IN DATABASE appdb SET search_path = app;

-- Settings stored per role / database
SELECT r.rolname, d.datname, s.setconfig
FROM   pg_db_role_setting s
LEFT   JOIN pg_roles r ON r.oid = s.setrole
LEFT   JOIN pg_database d ON d.oid = s.setdatabase;

↑ back to contents

07Tablespaces

Create, rename and drop
The directory must exist, be empty and be owned by the postgres OS user. A tablespace must be empty before it can be dropped.
-- As root:  mkdir -p /pgdata/ts_app && chown postgres:postgres /pgdata/ts_app
CREATE TABLESPACE ts_app OWNER app_owner LOCATION '/pgdata/ts_app';
ALTER TABLESPACE ts_app RENAME TO ts_app_data;
ALTER TABLESPACE ts_app_data OWNER TO dba_admin;
DROP TABLESPACE ts_app_data;
List tablespaces with size and location
SELECT spcname, pg_get_userbyid(spcowner) AS owner,
       pg_tablespace_location(oid) AS location,
       pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM   pg_tablespace;

-- Default tablespace of each database
SELECT d.datname, t.spcname
FROM   pg_database d JOIN pg_tablespace t ON t.oid = d.dattablespace;
Default and temporary tablespaces
SHOW default_tablespace;
SET default_tablespace = ts_app;                       -- this session
ALTER DATABASE appdb SET default_tablespace = ts_app;  -- new sessions in appdb

SHOW temp_tablespaces;
ALTER SYSTEM SET temp_tablespaces = 'ts_temp';
SELECT pg_reload_conf();
Move objects to another tablespace
Moving rewrites the data and takes an ACCESS EXCLUSIVE lock. Plan downtime for large tables.
ALTER TABLE app.orders SET TABLESPACE ts_app;
ALTER INDEX app.idx_orders_cust SET TABLESPACE ts_idx;

-- Whole database: nobody may be connected to it
ALTER DATABASE appdb SET TABLESPACE ts_app;

↑ back to contents

08Sessions and locks

Current sessions
SELECT pid, usename, datname, client_addr, application_name, state,
       now() - query_start AS running_for,
       wait_event_type, wait_event,
       left(query, 80) AS query
FROM   pg_stat_activity
WHERE  backend_type = 'client backend'
ORDER  BY query_start;
Cancel or terminate a session
Try pg_cancel_backend first: it stops the query and keeps the connection.
SELECT pg_cancel_backend(12345);      -- cancel the running query
SELECT pg_terminate_backend(12345);   -- end the whole session

-- End all sessions of one user (except your own)
SELECT pg_terminate_backend(pid)
FROM   pg_stat_activity
WHERE  usename = 'dev_user' AND pid <> pg_backend_pid();
Who is blocking whom
SELECT a.pid AS blocked_pid, a.usename AS blocked_user,
       now() - a.query_start AS waiting_for, left(a.query, 60) AS blocked_query,
       b.pid AS blocking_pid, b.usename AS blocking_user, b.state AS blocking_state,
       left(b.query, 60) AS blocking_query
FROM   pg_stat_activity a
CROSS  JOIN LATERAL unnest(pg_blocking_pids(a.pid)) AS bp(pid)
JOIN   pg_stat_activity b ON b.pid = bp.pid;
All locks held and waited for
SELECT l.pid, a.usename, l.locktype, l.relation::regclass AS object,
       l.mode, l.granted, left(a.query, 60) AS query
FROM   pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
WHERE  l.pid <> pg_backend_pid()
ORDER  BY l.granted, l.pid;

↑ back to contents

09VACUUM, ANALYZE and bloat

Everyday maintenance
ANALYZE app.orders;                 -- refresh optimizer statistics
VACUUM app.orders;                  -- mark dead rows as reusable
VACUUM (VERBOSE, ANALYZE) app.orders;
vacuumdb -d appdb -z -j 4           -- whole database from the shell, 4 parallel jobs
VACUUM FULL: reclaims space, but locks the table
VACUUM FULL rewrites the table and holds an ACCESS EXCLUSIVE lock for the whole run. On production prefer the pg_repack extension.
SELECT pg_size_pretty(pg_total_relation_size('app.orders'));   -- before
VACUUM FULL app.orders;
SELECT pg_size_pretty(pg_total_relation_size('app.orders'));   -- after
Last vacuum / analyze and dead rows
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup,0), 1) AS dead_pct,
       last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  20;
Watch a vacuum in progress
SELECT p.pid, p.relid::regclass AS table_name, p.phase,
       p.heap_blks_scanned, p.heap_blks_total,
       round(100.0 * p.heap_blks_scanned / NULLIF(p.heap_blks_total,0), 1) AS pct
FROM   pg_stat_progress_vacuum p;
Autovacuum: global and per table
For a large, busy table the default 20% scale factor is far too high. Lower it on that table only.
SELECT name, setting FROM pg_settings WHERE name LIKE 'autovacuum%' ORDER BY 1;

ALTER TABLE app.orders SET (autovacuum_vacuum_scale_factor = 0.02,
                            autovacuum_analyze_scale_factor = 0.01);
ALTER TABLE app.orders RESET (autovacuum_vacuum_scale_factor);

-- Tables with custom settings
SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT NULL;
Statistics target for a skewed column
ALTER TABLE app.orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE app.orders;
Exact bloat with pgstattuple
Reads the whole table, so run it off-peak.
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT table_len, tuple_percent, dead_tuple_percent, free_percent
FROM   pgstattuple('app.orders');

↑ back to contents

10WAL and archiving

Enable WAL archiving
archive_mode needs a restart; archive_command only needs a reload. The test ! -f part stops an existing archive file from being overwritten.
-- As the postgres OS user
mkdir -p /pgarchive

ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = 'test ! -f /pgarchive/%f && cp %p /pgarchive/%f';

-- Restart (archive_mode / wal_level)
pg_ctl -D $PGDATA restart        -- or: sudo systemctl restart postgresql-16
Archiver status and manual WAL switch
SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
FROM   pg_stat_archiver;

SELECT pg_switch_wal();            -- close the current WAL file and archive it
SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());
WAL directory size
SELECT count(*) AS wal_files, pg_size_pretty(SUM(size)) AS total_size
FROM   pg_ls_waldir();

↑ back to contents

11Monitoring and performance

Set up pg_stat_statements
Needs a restart because of shared_preload_libraries. If the parameter already lists other libraries, keep them in the list.
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- restart PostgreSQL, then in each database you want to query:
CREATE EXTENSION pg_stat_statements;
Top queries by total time
SELECT queryid, calls,
       round(total_exec_time::numeric/1000, 1) AS total_sec,
       round(mean_exec_time::numeric, 1)       AS mean_ms,
       rows, left(query, 80) AS query
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

SELECT pg_stat_statements_reset();   -- start a fresh measurement window
Temp file usage per database
Large values mean sorts and hashes are spilling to disk. Look at work_mem and the queries involved.
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_size, stats_reset
FROM   pg_stat_database
WHERE  datname IS NOT NULL
ORDER  BY temp_bytes DESC;
Cache hit ratio
SELECT datname,
       round(100.0 * blks_hit / NULLIF(blks_hit + blks_read,0), 2) AS cache_hit_pct
FROM   pg_stat_database WHERE datname IS NOT NULL;
Rotate the server log
SELECT pg_rotate_logfile();
SELECT pg_current_logfile();
Installed and available extensions
SELECT extname, extversion FROM pg_extension;
SELECT name, default_version, installed_version
FROM   pg_available_extensions ORDER BY name;

↑ back to contents

12Backup and data movement

COPY (server side) vs \copy (client side)
COPY reads and writes files on the database server and needs superuser or the pg_write_server_files role. \copy in psql uses files on your machine and needs no special rights.
COPY app.orders TO '/tmp/orders.csv' WITH (FORMAT csv, HEADER);
COPY app.orders (id, customer_id) TO '/tmp/orders_ids.csv' WITH (FORMAT csv);
COPY app.orders FROM '/tmp/orders.csv' WITH (FORMAT csv, HEADER);

\copy app.orders TO 'orders.csv' WITH (FORMAT csv, HEADER)
\copy (SELECT * FROM app.orders WHERE order_date > now() - interval '7 days') TO 'last7.csv' CSV HEADER
Logical backup with pg_dump
The custom format (-Fc) or directory format (-Fd) is the most flexible: compressed, selective restore, parallel restore.
pg_dump -d appdb -Fc -f appdb.dump                 # whole database
pg_dump -d appdb -Fd -j 4 -f appdb_dir             # directory format, 4 parallel jobs
pg_dump -d appdb -n app -Fc -f app_schema.dump     # one schema
pg_dump -d appdb -t app.orders -Fc -f orders.dump  # one table
pg_dump -d appdb --schema-only -f appdb_ddl.sql    # DDL only

pg_dumpall --globals-only -f globals.sql           # roles and tablespaces
Restore with pg_restore
pg_restore -l appdb.dump | head                          # list the contents
pg_restore -d appdb_new -j 4 appdb.dump                  # parallel restore
pg_restore -d appdb_new -t orders appdb.dump             # one table only
psql -f globals.sql postgres                             # restore roles first
Physical backup with pg_basebackup
pg_basebackup -h dbhost -U replicator -D /backup/base_$(date +%F) \
              -Ft -z -Xs -P

↑ back to contents

Conclusion

Most day-to-day PostgreSQL questions are answered by a handful of catalog views: pg_stat_activity, pg_stat_user_tables, pg_settings, pg_roles and pg_stat_statements. Learn what each one holds and the rest of this list becomes easy to remember.

Bookmark this page, and tell me in the comments which command you would add.

Tamilselvan Perumal · Lead Product Engineer & Senior DBA

Tags:
Tamilselvan Perumal

Posted by: Tamilselvan Perumal

I'm Tamilselvan Perumal, an Oracle ACE Apprentice and Senior DBA with 12+ years running Oracle, PostgreSQL, SQL Server, MongoDB and CockroachDB for Tier-1 global banks. On TechStarAdmin I share hands-on, production-tested guides on performance tuning, high availability, security and migrations.