Skip to content

Instantly share code, notes, and snippets.

@darmawan01

darmawan01/admin.sql

Forked from phil-hildebrand/admin.sql
Created July 7, 2021 02:44
Show Gist options
  • Save darmawan01/867c2f5c8cb431231a0c81baedb5a708 to your computer and use it in GitHub Desktop.
Save darmawan01/867c2f5c8cb431231a0c81baedb5a708 to your computer and use it in GitHub Desktop.
Download ZIP
Handy PostgreSQL Monitoring Scripts
Raw
admin.sql
-- turn off paging (less/more)
psql> \pset pager off
/*
Pager usage is off.
*/
-- find an object name by id
SELECT OID, relname
FROM pg_class
WHERE oid = 1234;
/*
oid | relname
-----+---------
(0 rows)
*/
-- find config info/pending restarts by name
SELECT name, setting, pending_restart
FROM pg_settings
WHERE name LIKE '%wal%';
/*
name | setting | pending_restart
------------------------------+-----------+-----------------
max_wal_senders | 8 | f
max_wal_size | 1024 | f
min_wal_size | 80 | f
wal_block_size | 8192 | f
wal_buffers | 2048 | f
wal_compression | off | f
wal_consistency_checking | | f
wal_keep_segments | 0 | f
wal_level | replica | f
wal_log_hints | on | f
wal_receiver_status_interval | 10 | f
wal_receiver_timeout | 300000 | f
wal_retrieve_retry_interval | 5000 | f
wal_segment_size | 2048 | f
wal_sender_timeout | 60000 | f
wal_sync_method | fdatasync | f
wal_writer_delay | 200 | f
wal_writer_flush_after | 128 | f
(18 rows)
*/
-- reload config changes dynamically
SELECT pg_reload_conf();
/*
pg_reload_conf
----------------
t
(1 row)
*/
Raw
databases_tables.sql
-- Get Databases with their size
SELECT
pg_database.datname as database,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
WHERE datistemplate=false
AND pg_database_size(pg_database.datname) > 0;
/*
database | size
----------+---------
postgres | 7613 kB
psql_db | 7637 kB
pg_db_1 | 7757 kB
pg_db_2 | 7757 kB
test11 | 8797 kB
(5 rows)
*/
-- Get tables with their size (excluding index size)
SELECT
table_name,
pg_size_pretty(pg_total_relation_size(table_name)) as table_size
FROM information_schema.tables
WHERE table_schema not in ('pg_catalog', 'information_schema')
AND table_type='BASE TABLE'
ORDER BY pg_total_relation_size(table_name) DESC;
/*
table_name | table_size
------------+------------
tabl3 | 1088 kB
tbl_1 | 16 kB
(2 rows)
*/
-- Get tables with their size (including indexs, TOAST, map etc.) and approximate number of visible rows
/* As per postgres docs: The number of rows is only an estimate used by the planner.
* It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX. */
SELECT
table_name,
pg_size_pretty(pg_total_relation_size(table_name)) as table_size,
pg_size_pretty(pg_total_relation_size(table_name) - pg_relation_size(table_name)) as index_toast_size,
(SELECT cast(reltuples as bigint) AS approximate_row_count FROM pg_class WHERE relname = table_name) as approximate_rows
FROM information_schema.tables
WHERE table_schema not in ('pg_catalog', 'information_schema')
AND table_type='BASE TABLE'
ORDER BY pg_total_relation_size(table_name) DESC;
/*
table_name | table_size | index_toast_size | approximate_rows
------------+------------+------------------+------------------
tabl3 | 1088 kB | 296 kB | 4741
tbl_1 | 16 kB | 16 kB | 10
(2 rows)
*/
-- Get object sizes (including materialized views)
SELECT relname AS objectname
, relkind AS objecttype
, reltuples AS entries
, pg_size_pretty(pg_table_size(oid)) AS size
FROM pg_class
WHERE relkind IN ('r', 'i', 'm')
ORDER BY pg_table_size(oid) DESC LIMIT 5;
/*
objectname | objecttype | entries | size
-------------------------------------+------------+-------------+---------
deduped_reviews | m | 6.90744e+06 | 9171 MB
reviews_2018 | r | 6.12466e+06 | 5076 MB
reviews_2017 | r | 4.18376e+06 | 3342 MB
i_deduped_reviews_id_location_id_ts | i | 6.89427e+06 | 2436 MB
i_deduped_reviews_location_id | i | 6.89427e+06 | 2281 MB
(5 rows)
*/
-- Get table's vacuum/analyze status
SELECT psut.relname,
to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,
to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,
to_char(pg_class.reltuples, '9G999G999G999') AS n_tup,
to_char(psut.n_dead_tup, '9G999G999G999') AS dead_tup,
to_char(CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
+ (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
* pg_class.reltuples), '9G999G999G999') AS av_threshold,
CASE
WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
+ (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
* pg_class.reltuples) < psut.n_dead_tup
THEN '*'
ELSE ''
END AS expect_av
FROM pg_stat_user_tables psut
JOIN pg_class on psut.relid = pg_class.oid
ORDER BY 1;
/*
relname | last_vacuum | last_autovacuum | n_tup | dead_tup | av_threshold | expect_av
-----------------------+------------------+------------------+----------------+----------------+----------------+-----------
deduped_reviews | 2018-12-04 05:10 | 2018-11-29 12:52 | 6,841,022 | 4,644,265 | 1,368,254 | *
reviews_2016 | 2018-12-04 00:06 | | 1,620,157 | 266,186 | 324,081 |
reviews_2017 | 2018-12-04 00:08 | | 4,183,278 | 826,730 | 836,706 |
(3 rows)
*/
Raw
postgres_connections.sql
-- Get current session info:
SELECT
datname as database ,usename as user ,client_addr,state, count(*) as total_connections,query
FROM pg_stat_activity
WHERE pid<>pg_backend_pid()
GROUP BY usename,client_addr,datname,state,query;
/*
database | user | client_addr | state | total_connections | query
----------+----------+-------------+--------+-------+---------------------------------------------------------------
test11 | dba | 192.168.2.1 | idle | 1 | delete from tabl3 where id in (select id from tabl3 LIMIT 2);
| postgres | | | 1 |
postgres | postgres | | active | 1 | SELECT pg_sleep(20);
| slave | 127.0.0.1 | active | 1 |
(4 rows)
*/
-- Get current session info with Duration
SELECT
datname as database ,usename as user ,client_addr,state,
now() - backend_start as duration, count(*) as total_connections,query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid<>pg_backend_pid()
GROUP BY usename,client_addr,datname,state, now() - backend_start, query
/*
database | user | client_addr | state | duration | total_connections | query
----------+---------+-------------+---------------------+------------------------+-------------------+--------------------------------------------------------------------
postgres | datadog | 127.0.0.1 | idle in transaction | 00:00:12.81569 | 1 | select split_part(split_part(version(), 'PostgreSQL ', 2), ' ', 1)
| slave | 10.10.48.81 | active | 3 days 20:13:40.662896 | 1 |
(2 rows)
*/
-- Get db connection count by user
SELECT
datname as database ,usename as user ,client_addr,state, count(*) as total_connections
FROM pg_stat_activity
WHERE pid<>pg_backend_pid()
GROUP BY usename,client_addr,datname,state;
/*
database | user | client_addr | state | total_connections
----------+----------------+-------------+---------------------+-------------------
| | | | 4
| slave | 10.10.48.81 | active | 1
beacon | beacon_service | 10.10.17.23 | idle | 6
postgres | datadog | 127.0.0.1 | idle | 1
| postgres | | | 1
postgres | datadog | 127.0.0.1 | idle in transaction | 1
beacon | beacon_service | 10.10.17.24 | idle | 36
(7 rows)
*/
-- Active sessions by state
SELECT
datname as database ,usename as user ,client_addr,state, count(*) as total_connections,query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid<>pg_backend_pid()
GROUP BY usename,client_addr,datname,state,query;
/*
database | user | client_addr | state | total_connections | query
----------+----------+-------------+--------+-------+------------------------------------------------------------------------------------------------------------------------------------------
test11 | dba | 192.168.2.1 | active | 1 | insert into tabl3 (cool_int,cool_json) values ( 113,'{"name":"testdata","ts": "var_created", "userId": "1037" , "username": "dba"}' );
postgres | postgres | | active | 1 | SELECT pg_sleep(20);
| slave | 127.0.0.1 | active | 1 |
(3 rows)
*/
-- Queries running for greater than x ms
SELECT
(now() - query_start) as query_time_taken,
datname as database ,usename as user,
query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND (now() - query_start) > interval '300 ms';
/*
query_time_taken | database | user | query
------------------+----------+------------+----------------------
00:00:06.432377 | test11 | app_level3 | SELECT pg_sleep(20);
(1 row)
*/
-- Get blocking queries
SELECT pid,
usename,
pg_blocking_pids(pid) AS blocked_by,
query AS blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
pid | usename | blocked_by | blocked_query
-----+---------+------------+---------------
(0 rows)
/* References:
* https://www.postgresql.org/docs/10/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
* http://bajis-postgres.blogspot.com/2014/01/handy-queries-of-postgresql-let-us.html
*/
Raw
replication.sql
-- View current replication status (on Primary)
SELECT usename as user, application_name, client_addr, client_port, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;
/*
user | application_name | client_addr | client_port | state | sent_lsn | write_lsn | flush_lsn | replay_lsn
-------+------------------+-------------+-------------+-----------+-----------+-----------+-----------+------------
slave | walreceiver | 127.0.0.1 | 47050 | streaming | 0/3B35F70 | 0/3B35F70 | 0/3B35F70 | 0/3B35F70
(1 row)
*/
-- View replication lag (on Secondary): Version 10 and up
SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;
-- View replication lag (on Secondary): Version less than 10
SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;
/* References:
* https://severalnines.com/blog/postgresql-streaming-replication-deep-dive
* https://www.postgresql.org/docs/10/monitoring-stats.html#PG-STAT-REPLICATION-VIEW
*/
Raw
users_roles.sql
-- View all roles with attributes and parent role using psql command
/*
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
app | Create DB | {}
backups | Replication | {}
datadog | | {mon}
dba | Superuser, Create role, Create DB | {}
dev | | {}
devadmin | Create DB | {}
mon | | {}
mozdba | Superuser | {dba}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
psql_app | | {app}
repl | Cannot login, Replication | {}
slave | Replication | {repl}
*/
-- View all roles with attributes and parent role using query
/*
* equivalent query for /du command, found using psql -E
*/
SELECT r.rolname, r.rolsuper, r.rolinherit,
r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
r.rolconnlimit, r.rolvaliduntil,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
WHERE r.rolname !~ '^pg_'
ORDER BY 1;
/*
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
app | Create DB | {}
backups | Replication | {}
datadog | | {mon}
dba | Superuser, Create role, Create DB | {}
dev | | {}
devadmin | Create DB | {}
mon | | {}
mozdba | Superuser | {dba}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
psql_app | | {app}
repl | Cannot login, Replication | {}
slave | Replication | {repl}
*/
-- View all roles a user/role belongs to (recursively)
WITH RECURSIVE cte AS (
SELECT oid, rolname FROM pg_roles WHERE rolname = 'app_level3'
UNION ALL
SELECT a.roleid, pg_get_userbyid(a.roleid)
FROM cte
JOIN pg_auth_members a ON a.member = cte.oid
)
SELECT * FROM cte;
/*
oid | rolname
-------+------------
16505 | app_level3
16390 | psql_app
16385 | app
(3 rows)
*/
-- View the recursive hierarchy of all roles/users across all schemas
WITH RECURSIVE
cte1 as (
SELECT b.oid, b.rolname, m.roleid as parentid
FROM pg_catalog.pg_auth_members m
RIGHT OUTER JOIN pg_catalog.pg_roles b ON (m.member = b.oid)
WHERE b.rolname !~ '^pg_'
),
cte2 as (
SELECT oid, rolname, parentid, CAST(rolname AS varchar(100)) AS inheritance_map
FROM cte1
WHERE parentid IS NULL
UNION ALL
SELECT c1.oid, c1.rolname, c1.parentid,
CAST(c2.inheritance_map || '->' || c1.rolname AS varchar(100)) AS inheritance_map
FROM cte1 c1 INNER JOIN cte2 c2
ON (c1.parentid = c2.oid)
)
SELECT * FROM cte2;
/*
oid | rolname | parentid | inheritance_map
-------+------------+----------+---------------------------
16389 | repl | | repl
16386 | mon | | mon
10 | postgres | | postgres
16394 | backups | | backups
16388 | devadmin | | devadmin
16387 | dev | | dev
16384 | dba | | dba
16385 | app | | app
16393 | slave | 16389 | repl->slave
16392 | datadog | 16386 | mon->datadog
16391 | mozdba | 16384 | dba->mozdba
16390 | psql_app | 16385 | app->psql_app
16505 | app_level3 | 16390 | app->psql_app->app_level3
*/
-- View object ownership for all roles
SELECT relowner as userid,
pg_get_userbyid(relowner) as username,
CASE
WHEN relkind in ('r','t' ,'f','p') THEN 'table'
WHEN relkind in ('m', 'v') THEN 'view'
WHEN relkind='S' THEN 'sequence'
WHEN relkind='i' THEN 'index'
WHEN relkind='c' THEN 'composite_type'
END as type,
array_agg(relname) as owned_objs
FROM pg_class
WHERE relnamespace IN (
SELECT oid
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
)
GROUP BY 1,2,3
ORDER BY 2;
/*
userid | username | type | owned_objs
--------+------------+----------+--------------------------------------
16505 | app_level3 | index | {app_table_pkey,app_table1_pkey}
16505 | app_level3 | sequence | {app_table_id_seq,app_table1_id_seq}
16505 | app_level3 | table | {app_table,app_table1}
16384 | dba | table | {tabl3}
16384 | dba | index | {tabl3_pkey}
16384 | dba | sequence | {tabl3_id_seq}
(6 rows)
*/
/* References:
* https://www.postgresql.org/docs/10/catalog-pg-class.html
* http://bajis-postgres.blogspot.com/2014/01/handy-queries-of-postgresql-let-us.html
* https://dba.stackexchange.com/questions/56096/how-to-get-all-roles-that-a-user-is-a-member-of-including-inherited-roles
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment