---------------------------------------------------------------------- -- File : pg-init-template.sql -- Desc : init templates for postgres cluster {{ pg_cluster }} -- Time : {{ '%Y-%m-%d %H:%M' | strftime }} -- Host : {{ pg_instance }} @ {{ inventory_hostname }}:{{ pg_port }} -- Path : /pg/tmp/pg-init-template.sql -- Note : ANSIBLE MANAGED, DO NOT CHANGE! -- License : Apache-2.0 @ https://pigsty.io/docs/about/license/ -- Copyright : 2018-2026 Ruohang Feng / Vonng (rh@vonng.com) ---------------------------------------------------------------------- --==================================================================-- -- Executions -- --==================================================================-- -- {{ pg_bin_dir}}/psql template1 -AXtwqf /pg/tmp/pg-init-template.sql -- this sql scripts is responsible for post-init procedure -- it will -- * create system users such as replicator, monitor user, admin user -- * create system default roles -- * create schema, extensions in template1 & postgres -- * create monitor views in template1 & postgres --==================================================================-- -- Default Privileges -- --==================================================================-- {% for priv in pg_default_privileges %} ALTER DEFAULT PRIVILEGES FOR ROLE {{ pg_dbsu }} {{ priv }}; {% endfor %} {% for priv in pg_default_privileges %} ALTER DEFAULT PRIVILEGES FOR ROLE {{ pg_admin_username }} {{ priv }}; {% endfor %} -- for additional business admin, they can SET ROLE to dbrole_admin {% for priv in pg_default_privileges %} ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" {{ priv }}; {% endfor %} --==================================================================-- -- Schemas -- --==================================================================-- {% for schema in pg_default_schemas %} {% if schema is string %} CREATE SCHEMA IF NOT EXISTS "{{ schema }}"; {% elif schema is mapping and 'name' in schema %} {% if schema.state is defined and schema.state == 'absent' %} DROP SCHEMA IF EXISTS "{{ schema.name }}" CASCADE; {% else %} CREATE SCHEMA IF NOT EXISTS "{{ schema.name }}"{% if 'owner' in schema and schema.owner is not none and schema.owner != '' %} AUTHORIZATION "{{ schema.owner }}"{% endif %}; {% endif %} {% endif %} {% endfor %} -- revoke public creation REVOKE CREATE ON SCHEMA public FROM PUBLIC; --==================================================================-- -- Extensions -- --==================================================================-- {% for extension in pg_default_extensions %} {% if extension is string %} CREATE EXTENSION IF NOT EXISTS "{{ extension }}" CASCADE; {% elif extension is mapping and 'name' in extension %} {% if extension.state is defined and extension.state == 'absent' %} DROP EXTENSION IF EXISTS "{{ extension.name }}" CASCADE; {% else %} CREATE EXTENSION IF NOT EXISTS "{{ extension.name }}"{% if 'schema' in extension %} WITH SCHEMA "{{ extension.schema }}"{% endif %}{% if 'version' in extension %} VERSION '{{ extension.version }}'{% endif %} CASCADE; {% endif %} {% endif %} {% endfor %} -- always enable file_fdw and default server fs CREATE EXTENSION IF NOT EXISTS file_fdw; CREATE SERVER IF NOT EXISTS fs FOREIGN DATA WRAPPER file_fdw; --==================================================================-- -- Backup Privileges -- --==================================================================-- -- grant backup privileges to replication user GRANT USAGE ON SCHEMA pg_catalog TO "{{ pg_replication_username }}"; GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO "{{ pg_replication_username }}"; GRANT EXECUTE ON FUNCTION pg_catalog.set_config(text, text, boolean) TO "{{ pg_replication_username }}"; GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO "{{ pg_replication_username }}"; {% if pg_version|int < 15 %} GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(text, boolean, boolean) TO "{{ pg_replication_username }}"; GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(boolean, boolean) TO "{{ pg_replication_username }}"; {% endif %} GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO "{{ pg_replication_username }}"; GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal() TO "{{ pg_replication_username }}"; GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_replay_lsn() TO "{{ pg_replication_username }}"; GRANT EXECUTE ON FUNCTION pg_catalog.txid_current() TO "{{ pg_replication_username }}"; GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot() TO "{{ pg_replication_username }}"; GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot) TO "{{ pg_replication_username }}"; GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint() TO "{{ pg_replication_username }}"; --==================================================================-- -- Monitor Schema -- --==================================================================-- ---------------------------------------------------------------------- -- cleanse & create monitor schema ---------------------------------------------------------------------- CREATE SCHEMA IF NOT EXISTS monitor; GRANT USAGE ON SCHEMA monitor TO "{{ pg_monitor_username }}"; GRANT USAGE ON SCHEMA monitor TO "{{ pg_admin_username }}"; GRANT USAGE ON SCHEMA monitor TO "{{ pg_replication_username }}"; ALTER USER "{{ pg_monitor_username }}" SET search_path TO monitor,public; --==================================================================-- -- Heartbeat Table -- --==================================================================-- -- table to hold heartbeat records DROP TABLE IF EXISTS monitor.heartbeat CASCADE; CREATE TABLE IF NOT EXISTS monitor.heartbeat ( id VARCHAR(64) PRIMARY KEY, ts TIMESTAMPTZ, lsn BIGINT, txid BIGINT ); COMMENT ON TABLE monitor.heartbeat IS 'heartbeat table, contains one row only'; REVOKE INSERT,UPDATE,DELETE ON TABLE monitor.heartbeat FROM dbrole_readwrite; GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE monitor.heartbeat TO pg_monitor; -- function to generate & return generated heartbeat record CREATE OR REPLACE FUNCTION monitor.upsert_heartbeat() RETURNS monitor.heartbeat SET search_path = '' AS $$ INSERT INTO monitor.heartbeat(id, ts, lsn, txid) VALUES (coalesce(current_setting('cluster_name', true), 'unknown'), now(), pg_current_wal_lsn() - '0/0'::PG_LSN, pg_current_xact_id()::text::BIGINT) ON CONFLICT(id) DO UPDATE SET ts=EXCLUDED.ts, lsn=EXCLUDED.lsn, txid=EXCLUDED.txid RETURNING *; $$ LANGUAGE SQL VOLATILE; COMMENT ON FUNCTION monitor.upsert_heartbeat() IS 'upsert a heartbeat record'; REVOKE ALL ON FUNCTION monitor.upsert_heartbeat() FROM PUBLIC; REVOKE ALL ON FUNCTION monitor.upsert_heartbeat() FROM dbrole_readonly; REVOKE ALL ON FUNCTION monitor.upsert_heartbeat() FROM dbrole_offline; GRANT EXECUTE ON FUNCTION monitor.upsert_heartbeat() TO pg_monitor; -- function to be used by monitor tools CREATE OR REPLACE FUNCTION monitor.beating() RETURNS TABLE (cls TEXT, ts TIMESTAMPTZ, lsn PG_LSN, lsn_int BIGINT, txid BIGINT, status TEXT) SET search_path = '' AS $$ SELECT id AS cls, ts , '0/0'::PG_LSN + lsn AS lsn, lsn AS lsn_int, txid, CASE WHEN pg_is_in_recovery() THEN 'recovery' ELSE 'leading' END AS status FROM (SELECT (CASE WHEN pg_is_in_recovery() THEN (SELECT h FROM monitor.heartbeat h) ELSE monitor.upsert_heartbeat() END).*) d; $$ LANGUAGE SQL VOLATILE; COMMENT ON FUNCTION monitor.beating() IS 'refresh heartbeat on leader and return status'; REVOKE ALL ON FUNCTION monitor.beating() FROM PUBLIC; REVOKE ALL ON FUNCTION monitor.beating() FROM dbrole_readonly; REVOKE ALL ON FUNCTION monitor.beating() FROM dbrole_offline; GRANT EXECUTE ON FUNCTION monitor.beating() TO pg_monitor; -- function to return explain plan of given query CREATE OR REPLACE FUNCTION monitor.explain(query TEXT) RETURNS JSON SET search_path = '' AS $$ DECLARE result JSON; BEGIN EXECUTE format('EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) %s', query) INTO result; RETURN result; END; $$ LANGUAGE plpgsql; COMMENT ON FUNCTION monitor.explain(TEXT) IS 'return verbose explain json plan for given query text'; --==================================================================-- -- Monitor Views -- --==================================================================-- ---------------------------------------------------------------------- -- Table bloat estimate : monitor.pg_table_bloat ---------------------------------------------------------------------- DROP FUNCTION IF EXISTS monitor.pg_table_bloat() CASCADE; DROP FUNCTION IF EXISTS monitor.pg_index_bloat() CASCADE; DROP VIEW IF EXISTS monitor.pg_table_bloat CASCADE; DROP VIEW IF EXISTS monitor.pg_index_bloat CASCADE; -- table bloat func CREATE OR REPLACE FUNCTION monitor.pg_table_bloat() RETURNS TABLE(datname TEXT,nspname TEXT,relname TEXT,tblid OID,size BIGINT,ratio FLOAT) SET search_path = '' AS $$SELECT CURRENT_CATALOG AS datname, nspname, relname , tblid , bs * tblpages AS size, CASE WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff)/tblpages::FLOAT ELSE 0 END AS ratio FROM ( SELECT ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff, tblpages, fillfactor, bs, tblid, nspname, relname, is_na FROM ( SELECT ( 4 + tpl_hdr_size + tpl_data_size + (2 * ma) - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END - CASE WHEN ceil(tpl_data_size)::INT % ma = 0 THEN ma ELSE ceil(tpl_data_size)::INT % ma END ) AS tpl_size, (heappages + toastpages) AS tblpages, heappages, toastpages, reltuples, toasttuples, bs, page_hdr, tblid, nspname, relname, fillfactor, is_na FROM ( SELECT tbl.oid AS tblid, ns.nspname , tbl.relname, tbl.reltuples, tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, coalesce(toast.reltuples, 0) AS toasttuples, coalesce(substring(array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor, current_setting('block_size')::numeric AS bs, CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma, 24 AS page_hdr, 23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END + CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size, sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size, bool_or(att.atttypid = 'pg_catalog.name'::regtype) OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON att.attrelid = tbl.oid JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid WHERE NOT att.attisdropped AND tbl.relkind = 'r' AND nspname NOT IN ('pg_catalog','information_schema') GROUP BY tbl.oid, ns.nspname, tbl.relname, tbl.reltuples, tbl.relpages, toast.relpages, toast.reltuples, tbl.reloptions ) AS s ) AS s2 ) AS s3 WHERE NOT is_na; $$ LANGUAGE SQL SECURITY DEFINER; -- index bloat func CREATE OR REPLACE FUNCTION monitor.pg_index_bloat() RETURNS TABLE(datname TEXT,nspname TEXT,relname TEXT,tblid OID,idxid OID,size BIGINT,ratio FLOAT) SET search_path = '' AS $$ SELECT CURRENT_CATALOG AS datname, nspname, idxname AS relname, tblid, idxid, relpages::BIGINT * bs AS size, COALESCE((relpages - ( reltuples * (6 + ma - (CASE WHEN index_tuple_hdr % ma = 0 THEN ma ELSE index_tuple_hdr % ma END) + nulldatawidth + ma - (CASE WHEN nulldatawidth % ma = 0 THEN ma ELSE nulldatawidth % ma END)) / (bs - pagehdr)::FLOAT + 1 )), 0) / relpages::FLOAT AS ratio FROM ( SELECT nspname, idxname, indrelid AS tblid, indexrelid AS idxid, reltuples, relpages, current_setting('block_size')::INTEGER AS bs, (CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END) AS ma, 24 AS pagehdr, (CASE WHEN max(COALESCE(pg_stats.null_frac, 0)) = 0 THEN 2 ELSE 6 END) AS index_tuple_hdr, sum((1.0 - COALESCE(pg_stats.null_frac, 0.0)) * COALESCE(pg_stats.avg_width, 1024))::INTEGER AS nulldatawidth FROM pg_attribute JOIN ( SELECT pg_namespace.nspname, ic.relname AS idxname, ic.reltuples, ic.relpages, pg_index.indrelid, pg_index.indexrelid, tc.relname AS tablename, regexp_split_to_table(pg_index.indkey::TEXT, ' ')::INTEGER AS attnum, pg_index.indexrelid AS index_oid FROM pg_index JOIN pg_class ic ON pg_index.indexrelid = ic.oid JOIN pg_class tc ON pg_index.indrelid = tc.oid JOIN pg_namespace ON ic.relnamespace = pg_namespace.oid JOIN pg_am ON ic.relam = pg_am.oid WHERE pg_am.amname = 'btree' AND ic.relpages > 0 AND pg_namespace.nspname NOT IN ('pg_catalog', 'information_schema') ) ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname AND ((pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE)) OR (pg_stats.tablename = ind_atts.idxname AND pg_stats.attname = pg_attribute.attname)) WHERE pg_attribute.attnum > 0 GROUP BY nspname, idxname, indrelid, indexrelid, reltuples, relpages ) est $$ LANGUAGE SQL SECURITY DEFINER; CREATE OR REPLACE VIEW monitor.pg_table_bloat AS SELECT * FROM monitor.pg_table_bloat(); COMMENT ON VIEW monitor.pg_table_bloat IS 'postgres table bloat estimate'; CREATE OR REPLACE VIEW monitor.pg_index_bloat AS SELECT * FROM monitor.pg_index_bloat(); COMMENT ON VIEW monitor.pg_index_bloat IS 'postgres index bloat estimate (btree)'; GRANT EXECUTE ON FUNCTION monitor.pg_table_bloat() TO pg_monitor; GRANT EXECUTE ON FUNCTION monitor.pg_index_bloat() TO pg_monitor; GRANT SELECT ON monitor.pg_table_bloat TO pg_monitor; GRANT SELECT ON monitor.pg_index_bloat TO pg_monitor; ---------------------------------------------------------------------- -- Relation Bloat : monitor.pg_bloat ---------------------------------------------------------------------- DROP VIEW IF EXISTS monitor.pg_bloat CASCADE; CREATE OR REPLACE VIEW monitor.pg_bloat AS SELECT coalesce(ib.datname, tb.datname) AS datname, coalesce(ib.nspname, tb.nspname) AS nspname, coalesce(ib.tblid, tb.tblid) AS tblid, coalesce(tb.nspname || '.' || tb.relname, ib.nspname || '.' || ib.tblid::RegClass) AS tblname, tb.size AS tbl_size, CASE WHEN tb.ratio < 0 THEN 0 ELSE round(tb.ratio::NUMERIC, 6) END AS tbl_ratio, (tb.size * (CASE WHEN tb.ratio < 0 THEN 0 ELSE tb.ratio::NUMERIC END)) ::BIGINT AS tbl_wasted, ib.idxid, ib.nspname || '.' || ib.relname AS idxname, ib.size AS idx_size, CASE WHEN ib.ratio < 0 THEN 0 ELSE round(ib.ratio::NUMERIC, 5) END AS idx_ratio, (ib.size * (CASE WHEN ib.ratio < 0 THEN 0 ELSE ib.ratio::NUMERIC END)) ::BIGINT AS idx_wasted FROM monitor.pg_index_bloat ib FULL OUTER JOIN monitor.pg_table_bloat tb ON ib.tblid = tb.tblid; COMMENT ON VIEW monitor.pg_bloat IS 'postgres relation bloat detail'; GRANT SELECT ON monitor.pg_bloat TO pg_monitor; ---------------------------------------------------------------------- -- monitor.pg_index_bloat_human ---------------------------------------------------------------------- DROP VIEW IF EXISTS monitor.pg_index_bloat_human CASCADE; CREATE OR REPLACE VIEW monitor.pg_index_bloat_human AS SELECT idxname AS name, tblname, idx_wasted AS wasted, pg_size_pretty(idx_size) AS idx_size, round(100 * idx_ratio::NUMERIC, 2) AS idx_ratio, pg_size_pretty(idx_wasted) AS idx_wasted, pg_size_pretty(tbl_size) AS tbl_size, round(100 * tbl_ratio::NUMERIC, 2) AS tbl_ratio, pg_size_pretty(tbl_wasted) AS tbl_wasted FROM monitor.pg_bloat WHERE idxname IS NOT NULL; COMMENT ON VIEW monitor.pg_index_bloat_human IS 'postgres index bloat info in human-readable format'; GRANT SELECT ON monitor.pg_index_bloat_human TO pg_monitor; ---------------------------------------------------------------------- -- monitor.pg_table_bloat_human ---------------------------------------------------------------------- DROP VIEW IF EXISTS monitor.pg_table_bloat_human CASCADE; CREATE OR REPLACE VIEW monitor.pg_table_bloat_human AS SELECT tblname AS name, idx_wasted + tbl_wasted AS wasted, pg_size_pretty(idx_wasted + tbl_wasted) AS all_wasted, pg_size_pretty(tbl_wasted) AS tbl_wasted, pg_size_pretty(tbl_size) AS tbl_size, tbl_ratio, pg_size_pretty(idx_wasted) AS idx_wasted, pg_size_pretty(idx_size) AS idx_size, round(idx_wasted::NUMERIC * 100.0 / idx_size, 2) AS idx_ratio FROM (SELECT datname, nspname, tblname, coalesce(max(tbl_wasted), 0) AS tbl_wasted, coalesce(max(tbl_size), 1) AS tbl_size, round(100 * coalesce(max(tbl_ratio), 0)::NUMERIC, 2) AS tbl_ratio, coalesce(sum(idx_wasted), 0) AS idx_wasted, coalesce(sum(idx_size), 1) AS idx_size FROM monitor.pg_bloat WHERE tblname IS NOT NULL GROUP BY datname,nspname,tblname ) d; COMMENT ON VIEW monitor.pg_table_bloat_human IS 'postgres table bloat info in human-readable format'; GRANT SELECT ON monitor.pg_table_bloat_human TO pg_monitor; ---------------------------------------------------------------------- -- Activity Overview: monitor.pg_session ---------------------------------------------------------------------- DROP VIEW IF EXISTS monitor.pg_session CASCADE; CREATE OR REPLACE VIEW monitor.pg_session AS SELECT coalesce(datname, 'all') AS datname, numbackends, active, idle, ixact, max_duration, max_tx_duration, max_conn_duration FROM ( SELECT datname, count(*) AS numbackends, count(*) FILTER ( WHERE state = 'active' ) AS active, count(*) FILTER ( WHERE state = 'idle' ) AS idle, count(*) FILTER ( WHERE state = 'idle in transaction' OR state = 'idle in transaction (aborted)' ) AS ixact, max(extract(epoch from now() - state_change)) FILTER ( WHERE state = 'active' ) AS max_duration, max(extract(epoch from now() - xact_start)) AS max_tx_duration, max(extract(epoch from now() - backend_start)) AS max_conn_duration FROM pg_stat_activity WHERE backend_type = 'client backend' AND pid <> pg_backend_pid() GROUP BY ROLLUP (datname) ORDER BY 1 NULLS FIRST ) t; COMMENT ON VIEW monitor.pg_session IS 'postgres activity group by session'; GRANT SELECT ON monitor.pg_session TO pg_monitor; ---------------------------------------------------------------------- -- Sequential Scan: monitor.pg_seq_scan ---------------------------------------------------------------------- DROP VIEW IF EXISTS monitor.pg_seq_scan CASCADE; CREATE OR REPLACE VIEW monitor.pg_seq_scan AS SELECT schemaname AS nspname, relname, seq_scan, seq_tup_read, seq_tup_read / seq_scan AS seq_tup_avg, idx_scan, n_live_tup + n_dead_tup AS tuples, round(n_live_tup * 100.0::NUMERIC / (n_live_tup + n_dead_tup), 2) AS live_ratio FROM pg_stat_user_tables WHERE seq_scan > 0 and (n_live_tup + n_dead_tup) > 0 ORDER BY seq_scan DESC; COMMENT ON VIEW monitor.pg_seq_scan IS 'table that have seq scan'; GRANT SELECT ON monitor.pg_seq_scan TO pg_monitor; ---------------------------------------------------------------------- -- Lock Waiting: monitor.pg_lock_waiting ---------------------------------------------------------------------- DROP VIEW IF EXISTS monitor.pg_lock_waiting CASCADE; CREATE VIEW monitor.pg_lock_waiting AS WITH RECURSIVE activity as (select pg_blocking_pids(pid) blocked_by, *, extract(seconds FROM age(clock_timestamp(), xact_start)) as xact_wait, extract(seconds FROM age(clock_timestamp(), {% if pg_version|int >= 14 %} (select max(l.waitstart) from pg_locks l where a.pid = l.pid) -- PG 14 and newer {% else %} state_change -- PG 13 and below {% endif %} )) as lock_wait -- "pg_locks.waitstart" – PG14+ only; for older versions: age(clock_timestamp(), state_change) as wait_age from pg_stat_activity a where state is distinct from 'idle'), blockers as (select array_agg(distinct c order by c) as pids from (select unnest(blocked_by) from activity) as dt(c)), tree as (select activity.*, 1 as level, activity.pid as top_blocker_pid, array [activity.pid] as path, array [activity.pid]::int[] as all_blockers_above from activity, blockers where array [pid] <@ blockers.pids and blocked_by = '{}'::int[] union all select activity.*, tree.level + 1 as level, tree.top_blocker_pid, path || array [activity.pid] as path, tree.all_blockers_above || array_agg(activity.pid) over () as all_blockers_above from activity, tree where not array [activity.pid] <@ tree.all_blockers_above and activity.blocked_by <> '{}'::int[] and activity.blocked_by <@ tree.all_blockers_above) SELECT pid, blocked_by, case when wait_event_type = 'Lock' then 'waiting' else state end as state, wait_event_type, wait_event, xact_wait, lock_wait, age(backend_xid) AS xid_age, -- 2147483647 - age(backend_xmin) as xmin_ttf, datname, usename, level - 1 AS level, (select count(distinct t1.pid) from tree t1 where array [tree.pid] <@ t1.path and t1.pid <> tree.pid) as blkd, format('%s %s%s', lpad('[' || pid::text || ']', 9, ' '), repeat('.', level - 1) || case when level > 1 then ' ' end, left(query, 1000) ) as query FROM tree WHERE datname = CURRENT_DATABASE() ORDER BY top_blocker_pid, level, pid; COMMENT ON VIEW monitor.pg_lock_waiting IS 'lock waiting tree'; GRANT SELECT ON monitor.pg_lock_waiting TO pg_monitor; --==================================================================-- -- Functions -- --==================================================================-- {% if pg_version|int >= 13 %} ---------------------------------------------------------------------- -- pg_shmem auxiliary function (PG13+ only) ---------------------------------------------------------------------- DROP FUNCTION IF EXISTS monitor.pg_shmem() CASCADE; CREATE OR REPLACE FUNCTION monitor.pg_shmem() RETURNS SETOF pg_shmem_allocations SET search_path = '' AS $$SELECT * FROM pg_shmem_allocations;$$ LANGUAGE SQL SECURITY DEFINER; COMMENT ON FUNCTION monitor.pg_shmem() IS 'security wrapper for system view pg_shmem'; REVOKE ALL ON FUNCTION monitor.pg_shmem() FROM PUBLIC; REVOKE ALL ON FUNCTION monitor.pg_shmem() FROM dbrole_readonly; REVOKE ALL ON FUNCTION monitor.pg_shmem() FROM dbrole_offline; GRANT EXECUTE ON FUNCTION monitor.pg_shmem() TO pg_monitor; {% endif %} ---------------------------------------------------------------------- -- monitor.pgbouncer_auth for pgbouncer_auth_query ---------------------------------------------------------------------- {% if pgbouncer_enabled|bool %} CREATE OR REPLACE FUNCTION monitor.pgbouncer_auth(p_username TEXT) RETURNS TABLE(username TEXT, password TEXT) SET search_path = '' AS $$BEGIN RAISE WARNING 'PgBouncer auth request: %', p_username; RETURN QUERY SELECT rolname::TEXT, rolpassword::TEXT FROM pg_authid WHERE NOT rolsuper AND rolname = p_username; END; $$ LANGUAGE plpgsql SECURITY DEFINER; REVOKE ALL ON FUNCTION monitor.pgbouncer_auth(p_username TEXT) FROM PUBLIC; REVOKE ALL ON FUNCTION monitor.pgbouncer_auth(p_username TEXT) FROM dbrole_readonly; REVOKE ALL ON FUNCTION monitor.pgbouncer_auth(p_username TEXT) FROM dbrole_offline; {% endif %} --==================================================================-- -- Foreign Tables -- --==================================================================-- ---------------------------------------------------------------------- -- current log ---------------------------------------------------------------------- CREATE TYPE monitor.log_level AS ENUM ( 'LOG','INFO','NOTICE','WARNING','ERROR','FATAL','PANIC','DEBUG' ); COMMENT ON TYPE monitor.log_level IS 'PostgreSQL Log Level'; -- current log DROP FOREIGN TABLE IF EXISTS monitor.pg_log; CREATE FOREIGN TABLE monitor.pg_log ( ts TIMESTAMPTZ, -- ts username TEXT, -- user name datname TEXT, -- database name pid INTEGER, -- process_id conn TEXT, -- connect_from sid TEXT, -- session id sln BIGINT, -- session line number cmd_tag TEXT, -- command tag stime TIMESTAMPTZ, -- session start time vxid TEXT, -- virtual transaction id txid BIGINT, -- transaction id level monitor.log_level, -- log level code VARCHAR(5), -- sql state error code msg TEXT, -- message detail TEXT, -- detail hint TEXT, -- hint iq TEXT, -- internal query iqp INTEGER, -- internal query position context TEXT, -- context q TEXT, -- query qp INTEGER, -- query position location TEXT, -- location appname TEXT -- application name {% if pg_version|int >= 13%} ,backend TEXT -- backend_type (PG13) {% endif %} {% if pg_version|int >= 14%} ,leader_pid INTEGER -- parallel group leader pid, if this is worker {% endif %} {% if pg_version|int >= 14%} ,query_id BIGINT -- query id of the current query {% endif %} ) SERVER fs OPTIONS (program $$cat $(cat /pg/data/current_logfiles | awk '{print $2}');$$, format 'csv'); COMMENT ON FOREIGN TABLE monitor.pg_log IS 'current log file foreign table'; REVOKE ALL ON monitor.pg_log FROM PUBLIC; REVOKE ALL ON monitor.pg_log FROM dbrole_offline; REVOKE ALL ON monitor.pg_log FROM dbrole_readonly; REVOKE ALL ON monitor.pg_log FROM dbrole_readwrite; REVOKE ALL ON monitor.pg_log FROM dbrole_admin; GRANT SELECT ON monitor.pg_log TO pg_monitor; ---------------------------------------------------------------------- -- pgbackrest information ---------------------------------------------------------------------- DROP FOREIGN TABLE IF EXISTS monitor.pgbackrest_info CASCADE; CREATE FOREIGN TABLE IF NOT EXISTS monitor.pgbackrest_info (data JSONB) SERVER fs OPTIONS (PROGRAM $$pgbackrest --stanza={{ pg_cluster }} --output=json info$$ , FORMAT 'text'); REVOKE ALL ON monitor.pgbackrest_info FROM PUBLIC; REVOKE ALL ON monitor.pgbackrest_info FROM dbrole_offline; REVOKE ALL ON monitor.pgbackrest_info FROM dbrole_readonly; REVOKE ALL ON monitor.pgbackrest_info FROM dbrole_readwrite; GRANT SELECT ON monitor.pgbackrest_info TO pg_monitor; DROP VIEW IF EXISTS monitor.pgbackrest; CREATE OR REPLACE VIEW monitor.pgbackrest AS SELECT name, value ->> 'type' AS bk_type, (value ->> 'error')::BOOLEAN AS bk_error, current_archive ->> 'min' as wal_min, current_archive ->> 'max' as wal_max, value ->> 'label' AS bk_label, value ->> 'prior' AS bk_prior, (value -> 'timestamp' ->> 'start')::NUMERIC AS bk_start_ts, (value -> 'timestamp' ->> 'stop')::NUMERIC AS bk_stop_ts, to_timestamp((value -> 'timestamp' ->> 'start')::NUMERIC) AS bk_start_at, to_timestamp((value -> 'timestamp' ->> 'stop')::NUMERIC) AS bk_stop_at, value -> 'lsn' ->> 'start' AS bk_start_lsn, value -> 'lsn' ->> 'stop' AS bk_stop_lsn, (value -> 'info' ->> 'size')::BIGINT AS bk_size, (value -> 'info' ->> 'delta')::BIGINT AS bk_delta, (value -> 'info' -> 'repo' ->> 'size')::BIGINT AS bk_repo_size, (value -> 'info' -> 'repo' ->> 'delta')::BIGINT AS bk_repo_delta, value -> 'reference' AS bk_reference, value -> 'annotation' AS bk_annotation FROM (SELECT value ->> 'name' AS name, value -> 'backup' AS backups, value -> 'archive' -> (jsonb_array_length(value -> 'archive') - 1) AS current_archive FROM monitor.pgbackrest_info i, jsonb_array_elements(i.data)) z, jsonb_array_elements(z.backups); REVOKE ALL ON monitor.pgbackrest FROM PUBLIC; REVOKE ALL ON monitor.pgbackrest FROM dbrole_offline; REVOKE ALL ON monitor.pgbackrest FROM dbrole_readonly; REVOKE ALL ON monitor.pgbackrest FROM dbrole_readwrite; REVOKE ALL ON monitor.pgbackrest FROM dbrole_admin; GRANT SELECT ON monitor.pgbackrest TO pg_monitor; ---------------------------------------------------------------------- -- patroni information ---------------------------------------------------------------------- DROP FOREIGN TABLE IF EXISTS monitor.patroni_info CASCADE; CREATE FOREIGN TABLE IF NOT EXISTS monitor.patroni_info (data JSONB) SERVER fs OPTIONS (PROGRAM $$curl http{% if patroni_ssl_enabled|bool %}s{% endif %}://127.0.0.1:{{ patroni_port }}/cluster$$ , FORMAT 'text'); REVOKE ALL ON monitor.patroni_info FROM PUBLIC; REVOKE ALL ON monitor.patroni_info FROM dbrole_offline; REVOKE ALL ON monitor.patroni_info FROM dbrole_readonly; REVOKE ALL ON monitor.patroni_info FROM dbrole_readwrite; GRANT SELECT ON monitor.patroni_info TO pg_monitor; DROP FOREIGN TABLE IF EXISTS monitor.patroni_conf CASCADE; CREATE FOREIGN TABLE IF NOT EXISTS monitor.patroni_conf (data JSONB) SERVER fs OPTIONS (PROGRAM $$cat patroni.dynamic.json$$ , FORMAT 'text'); REVOKE ALL ON monitor.patroni_conf FROM PUBLIC; REVOKE ALL ON monitor.patroni_conf FROM dbrole_offline; REVOKE ALL ON monitor.patroni_conf FROM dbrole_readonly; REVOKE ALL ON monitor.patroni_conf FROM dbrole_readwrite; GRANT SELECT ON monitor.patroni_conf TO pg_monitor; DROP VIEW IF EXISTS monitor.patroni; CREATE OR REPLACE VIEW monitor.patroni AS SELECT value ->> 'name' AS name, CASE value ->> 'role' WHEN 'leader' THEN 'primary' ELSE value ->> 'role' END AS role, value ->> 'host' AS host, value ->> 'port' AS port, value ->> 'state' AS state, (value ->> 'timeline')::INTEGER AS timeline, (value ->> 'lag')::BIGINT AS lag, value ->> 'api_url' AS url, value -> 'tags' ->> 'replicatefrom' AS replicatefrom, coalesce((value -> 'tags' -> 'nofailover') ::BOOLEAN, false)::BOOLEAN AS nofailover, coalesce((value -> 'tags' -> 'clonefrom') ::BOOLEAN, false)::BOOLEAN AS clonefrom, coalesce((value -> 'tags' -> 'noloadbalance') ::BOOLEAN, false)::BOOLEAN AS noloadbalance, coalesce((value -> 'tags' -> 'nosync') ::BOOLEAN, false)::BOOLEAN AS nosync, value -> 'tags' AS tags FROM monitor.patroni_info i, jsonb_array_elements(data -> 'members'); REVOKE ALL ON monitor.patroni FROM PUBLIC; REVOKE ALL ON monitor.patroni FROM dbrole_offline; REVOKE ALL ON monitor.patroni FROM dbrole_readonly; REVOKE ALL ON monitor.patroni FROM dbrole_readwrite; GRANT SELECT ON monitor.patroni TO pg_monitor; -- get process status DROP FOREIGN TABLE IF EXISTS monitor.process_status CASCADE; CREATE FOREIGN TABLE monitor.process_status ( username TEXT, pid INTEGER, cpu NUMERIC, mem NUMERIC, vsz BIGINT, rss BIGINT, tty TEXT, stat TEXT, start TEXT, uptime TEXT, command TEXT ) SERVER fs OPTIONS (PROGRAM $$ ps aux | awk '{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,substr($0,index($0,$11))}' OFS='\037' $$ , FORMAT 'csv', DELIMITER E'\037', HEADER 'TRUE'); REVOKE ALL ON monitor.process_status FROM PUBLIC; REVOKE ALL ON monitor.process_status FROM dbrole_offline; REVOKE ALL ON monitor.process_status FROM dbrole_readonly; REVOKE ALL ON monitor.process_status FROM dbrole_readwrite; GRANT SELECT ON monitor.process_status TO pg_monitor; -- get disk usage DROP FOREIGN TABLE IF EXISTS monitor.disk_free CASCADE; CREATE FOREIGN TABLE monitor.disk_free ( fsname TEXT, fstype TEXT, total_1m BIGINT, used_1m BIGINT, avail_1m BIGINT, percent TEXT, itotal BIGINT, iused BIGINT, ifree BIGINT, ipercent TEXT, mountpoint TEXT ) SERVER fs OPTIONS (PROGRAM $$ df -ml --output=source,fstype,size,used,avail,pcent,itotal,iused,iavail,ipcent,target | tail -n +2 | awk '{print $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11}' OFS='\037' $$, FORMAT 'csv', DELIMITER E'\037' ); REVOKE ALL ON monitor.disk_free FROM PUBLIC; REVOKE ALL ON monitor.disk_free FROM dbrole_offline; REVOKE ALL ON monitor.disk_free FROM dbrole_readonly; REVOKE ALL ON monitor.disk_free FROM dbrole_readwrite; GRANT SELECT ON monitor.disk_free TO pg_monitor; --==================================================================-- -- Customize Logic -- --==================================================================-- -- This script will be executed on primary instance among a newly created -- postgres cluster. it will be executed as dbsu on template1 database -- add your customization logic here (make sure they are idempotent)