Long running queries
select datname,usename,current_timestamp - query_start as runtime,client_addr,application_name,query from pg_stat_activity where state = 'active' and backend_type = 'client backend' order by runtime desc;
View Backup progress
SELECT pid, phase, 100.0*((backup_streamed*1.0)/backup_total) AS "progress%" FROM pg_stat_progress_basebackup;
View COPY FROM % progress:
SELECT (SELECT relname FROM pg_class WHERE oid = relid), 100.0*((bytes_processed*1.0)/bytes_total) AS "progress%" FROM pg_stat_progress_copy;
View COPY TO % progress
SELECT relname, 100.0*((tuples_processed*1.0)/(case reltuples WHEN 0 THEN 10 WHEN -1 THEN 10 ELSE reltuples END)) AS "progress%" FROM pg_stat_progress_copy JOIN pg_class on oid = relid;
check if a query is waiting for another query
\x SELECT pid,usename,wait_event_type,wait_event,state,backend_type,query FROM pg_stat_activity where backend_type = 'client backend';
view lock waits
SELECT bl.locktype, bl.mode AS lock_type, bl.pid AS blocked_pid, a.usename AS blocked_user, a.query AS blocked_query, now() - a.query_start AS waiting_duration, kl.pid AS blocking_pid, ka.usename AS blocking_user, ka.query AS blocking_query, now() - ka.query_start AS locking_duration FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid WHERE NOT bl.granted;
view locks on tables
SELECT pg_namespace.nspname as schemaname, pg_class.relname as tablename, pg_locks.mode as lock_type, age(now(),pg_stat_activity.query_start) AS time_running FROM pg_class JOIN pg_locks on pg_locks.relation = pg_class.oid JOIN pg_database on pg_database.oid = pg_locks.database JOIN pg_namespace on pg_namespace.oid = pg_class.relnamespace JOIN pg_stat_activity on pg_stat_activity.pid = pg_locks.pid WHERE pg_class.relkind = 'r' AND pg_database.datname = current_database();
view object/object type/tablespace
SELECT spcname, relname, CASE WHEN relpersistence = 't' THEN 'temp ' WHEN relpersistence = 'u' THEN 'unlogged ' ELSE '' END || ( CASE WHEN relkind = 'r' THEN 'table' WHEN relkind = 'p' THEN 'partitioned table' WHEN relkind = 'f' THEN 'foreign table' WHEN relkind = 't' THEN 'TOAST table' WHEN relkind = 'v' THEN 'view' WHEN relkind = 'm' THEN 'materialized view' WHEN relkind = 'S' THEN 'sequence' WHEN relkind = 'c' THEN 'type' ELSE 'index' END ) as objtype FROM pg_class c join pg_tablespace ts ON ( CASE WHEN c.reltablespace = 0 THEN ( SELECT dattablespace FROM pg_database WHERE datname = current_database() ) ELSE c.reltablespace END ) = ts.oid WHERE relname NOT LIKE 'pg_toast%' AND relnamespace NOT IN ( SELECT oid FROM pg_namespace WHERE nspname IN ( 'pg_catalog', 'information_schema' ) );
You can use the following query to kill all backends that have an open transaction but have been doing nothing for the last 10 minutes:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND current_timestamp - state_change > '10 min';
While using a two-phase commit (2PC), you may end up in a situation where you have something locked but cannot find the backend that holds the locks. look up the pg_locks table for those entries with an empty pid value.
SELECT t.schemaname || '.' || t.relname AS tablename, l.pid, l.granted FROM pg_locks l JOIN pg_stat_user_tables t ON l.relation = t.relid;
To see whether a table is currently in active use
CREATE TEMPORARY TABLE tmp_stat_user_tables AS SELECT * FROM pg_stat_user_tables; SELECT * FROM pg_stat_user_tables n JOIN tmp_stat_user_tables t ON n.relid=t.relid AND (n.seq_scan,n.idx_scan,n.n_tup_ins,n.n_tup_upd,n.n_tup_del) <> (t.seq_scan,t.idx_scan,t.n_tup_ins,t.n_tup_upd,t.n_tup_del);
check the space that's being used by temporary tablespaces:
WITH temporary_tablespaces AS (SELECT
unnest(string_to_array(
current_setting('temp_tablespaces'), ',')
) AS temp_tablespace
)
SELECT tt.temp_tablespace,
pg_tablespace_location(t.oid) AS location,
pg_tablespace_size(t.oid) AS size
FROM temporary_tablespaces tt
JOIN pg_tablespace t ON t.spcname = tt.temp_tablespace
ORDER BY 1;
display reloptions for tables and their toast tables
ALTER TABLE a
SET ( toast.autovacuum_enabled = off);
SELECT n.nspname
, c.relname
, array_to_string(c.reloptions || ARRAY(SELECT 'toast.' || x FROM unnest(tc.reloptions) AS x), ', ')
AS relopts
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON c.reltoastrelid = tc.oid
inner JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema');
Look at the age of the oldest snapshots that are running
select now() - CASE WHEN backend_xid IS NOT null THEN xact_start ELSE query_start end AS age , pid , backend_xid AS xid , backend_xmin AS xmin , state from pg_stat_activity WHERE backend_type = 'client backend' ORDER BY 1 DESC;
prepared transactions and locks
SELECT l.locktype, x.database, l.relation, l.page, l.tuple, l.classid, l.objid, l.objsubid, l.mode, x.transaction, x.gid, x.prepared, x.owner FROM pg_locks l JOIN pg_prepared_xacts x ON l.virtualtransaction = '-1/' || x.transaction::text; SELECT DISTINCT x.database, l.relation FROM pg_locks l JOIN pg_prepared_xacts x ON l.virtualtransaction = '-1/' || x.transaction::text WHERE l.locktype != 'transactionid';
WAL generation per second
select pg_current_wal_lsn() as last_lsn, now() as ts;
select pg_size_pretty((pg_current_wal_lsn() - :'last_lsn') / (extract('epoch' from now()) - extract('epoch' from timestamptz :'ts'))::int) || '/sec';
Finding Long-Running Queries in PostgreSQL
SELECT COALESCE(now() - query_start, '0 seconds'::INTERVAL) AS duration , * FROM pg_stat_activity WHERE state != 'idle' AND pid != pg_backend_pid() ORDER BY 1 DESC, backend_start;
Killing a single connection
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = :pid AND pid != pg_backend_pid();
Killing every query & connection
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = :database_name AND pid != pg_backend_pid();
Calculating Table Size in PostgreSQL
SELECT n.nspname AS schema_name
, c.relname AS table_name
, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
, pg_size_pretty(pg_table_size(c.oid)) AS table_size
, pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND n.nspname NOT LIKE 'pg_toast%'
ORDER BY 1, 2;
Finding the largest tables
SELECT n.nspname AS schema_name
, c.relname AS table_name
, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
, pg_size_pretty(pg_table_size(c.oid)) AS table_size
, pg_size_pretty(pg_indexes_size(c.oid)) AS index_size
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND n.nspname NOT LIKE 'pg_toast%'
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 10;
Finding the largest schemas
SELECT n.nspname AS schema_name
, pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS total_size
, pg_size_pretty(sum(pg_table_size(c.oid))) AS table_size
, pg_size_pretty(sum(pg_indexes_size(c.oid))) AS index_size
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND n.nspname NOT LIKE 'pg_toast%'
GROUP BY n.nspname
ORDER BY sum(pg_total_relation_size(c.oid)) DESC
LIMIT 10;
see every constraint in our database with this query
SELECT ns.nspname AS schema
, class.relname AS "table"
, con.conname AS "constraint"
, con.condeferrable AS "deferrable"
, con.condeferred AS deferred
FROM pg_constraint con
INNER JOIN pg_class class ON class.oid = con.conrelid
INNER JOIN pg_namespace ns ON ns.oid = class.relnamespace
WHERE con.contype IN ('p', 'u')
AND ns.nspname != 'pg_catalog'
ORDER BY 1, 2, 3;
Query page data.
CREATE FUNCTION heap_page(relname text, pageno integer) RETURNS TABLE(ctid tid, state text, xmin text, xmax text) AS $$ SELECT (pageno,lp)::text::tid AS ctid, CASE lp_flags WHEN 0 THEN 'unused' WHEN 1 THEN 'normal' WHEN 2 THEN 'redirect to '||lp_off WHEN 3 THEN 'dead' END AS state, t_xmin || CASE WHEN (t_infomask & 256) > 0 THEN ' c' WHEN (t_infomask & 512) > 0 THEN ' a' ELSE '' END AS xmin, t_xmax || CASE WHEN (t_infomask & 1024) > 0 THEN ' c' WHEN (t_infomask & 2048) > 0 THEN ' a' ELSE '' END AS xmax FROM heap_page_items(get_raw_page(relname,pageno)) ORDER BY lp; $$ LANGUAGE sql;
How to check the replication delay in PostgreSQL?
SELECT pg_is_in_recovery() AS is_slave, pg_last_wal_receive_lsn() AS receive, pg_last_wal_replay_lsn() AS replay, pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() AS synced, (EXTRACT(EPOCH FROM now()) - EXTRACT(EPOCH FROM pg_last_xact_replay_timestamp()))::int AS lag;
Resolving users and role membership in PostgreSQL
SELECT oid, rolname, rolcanlogin FROM pg_authid WHERE oid > 16384; SELECT * FROM pg_auth_members WHERE roleid > 16384; WITH RECURSIVE x AS ( SELECT member::regrole, roleid::regrole AS role, member::regrole || ' -> ' || roleid::regrole AS path FROM pg_auth_members AS m WHERE roleid > 16384 UNION ALL SELECT x.member::regrole, m.roleid::regrole, x.path || ' -> ' || m.roleid::regrole FROM pg_auth_members AS m JOIN x ON m.member = x.role ) SELECT member, role, path FROM x ORDER BY member::text, role::text;
