Users can use the following configuration items to configure the infrastructure and database cluster.
In general, most parameters can be used directly with default values.
The infrastructure section requires very little modification, and the only modification usually involved is a textual substitution of the IP address of the meta-node.
In contrast, users need to focus on the definition and configuration of database clusters. The database cluster will be deployed on database nodes and the user must provide the [identity information](identity/#identity parameter) of the database cluster with the [connection information](identity/#connection information) of the database nodes. The identity information (e.g., cluster name, instance number) is used to describe the entities in the database cluster, while the connection information (e.g., IP address) is used to access the database node. Also, the user should define the default business user and business database together with the cluster creation.
Where to create the directory, what purpose to create the cluster, which IP ports to listen on, what connection pooling mode to use
In this section, identity information is a mandatory parameter, other than that there are very few default parameters to change.
The default parameters are rarely changed other than by pg_conf you can use the default database cluster templates (Normal Transactional OLTP / Normal Analytical OLAP / Core Financial CRIT / Micro Virtual Machine TINY). If you wish to create custom templates, you can clone the default configuration in roles/postgres/templates and adopt it with your own modifications, see [Patroni template customization](. /../reference/patroni).
# complete example of user/role definition for production user- name:dbuser_meta # example production user have read-write accesspassword:DBUser.Meta # example user's password, can be encryptedlogin:true# can login, true by default (should be false for role)superuser:false# is superuser? false by defaultcreatedb:false# can create database? false by defaultcreaterole:false# can create role? false by defaultinherit:true# can this role use inherited privileges?replication:false# can this role do replication? false by defaultbypassrls:false# can this role bypass row level security? false by defaultconnlimit:-1# connection limit, -1 disable limitexpire_at:'2030-12-31'# 'timestamp' when this role is expiredexpire_in:365# now + n days when this role is expired (OVERWRITE expire_at)roles:[dbrole_readwrite] # dborole_admin|dbrole_readwrite|dbrole_readonlypgbouncer:true# add this user to pgbouncer? false by default (true for production user)parameters:# user's default search pathsearch_path:publiccomment:test user
----------------------------------------------------------------------
-- File : pg-user-dbuser_meta.sql
-- Path : /pg/tmp/pg-user-dbuser_meta.sql
-- Time : 2021-03-22 22:52
-- Note : managed by ansible, DO NOT CHANGE
-- Desc : creation sql script for user dbuser_meta
----------------------------------------------------------------------
--==================================================================--
-- EXECUTION --
--==================================================================--
-- run as dbsu (postgres by default)
-- createuser -w -p 5432 'dbuser_meta';
-- psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbuser_meta.sql
--==================================================================--
-- CREATE USER --
--==================================================================--
CREATEUSER"dbuser_meta";--==================================================================--
-- ALTER USER --
--==================================================================--
-- options
ALTERUSER"dbuser_meta";-- password
ALTERUSER"dbuser_meta"PASSWORD'DBUser.Meta';-- expire
-- expire at 2022-03-22 in 365 days since 2021-03-22
ALTERUSER"dbuser_meta"VALIDUNTIL'2022-03-22';-- conn limit
-- remove conn limit
-- ALTER USER "dbuser_meta" CONNECTION LIMIT -1;
-- parameters
ALTERUSER"dbuser_meta"SETsearch_path=public;-- comment
COMMENTONROLE"dbuser_meta"IS'test user';--==================================================================--
-- GRANT ROLE --
--==================================================================--
GRANT"dbrole_readwrite"TO"dbuser_meta";--==================================================================--
-- PGBOUNCER USER --
--==================================================================--
-- user will not be added to pgbouncer user list by default,
-- unless pgbouncer is explicitly set to 'true', which means production user
-- User 'dbuser_meta' will be added to /etc/pgbouncer/userlist.txt via
-- /pg/bin/pgbouncer-create-user 'dbuser_meta' 'DBUser.Meta'
--==================================================================--
- name:meta # name is the only required field for a databaseowner:postgres # optional, database ownertemplate:template1 # optional, template1 by defaultencoding:UTF8 # optional, UTF8 by default , must same as template database, leave blank to set to db defaultlocale:C # optional, C by default , must same as template database, leave blank to set to db defaultlc_collate:C # optional, C by default , must same as template database, leave blank to set to db defaultlc_ctype:C # optional, C by default , must same as template database, leave blank to set to db defaultallowconn:true# optional, true by default, false disable connect at allrevokeconn:false# optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)tablespace:pg_default # optional, 'pg_default' is the default tablespaceconnlimit:-1# optional, connection limit, -1 or none disable limit (default)schemas:[public,monitor] # create additional schemaextensions:# optional, extension name and where to create- {name: postgis, schema:public}parameters:# optional, extra parameters with ALTER DATABASEenable_partitionwise_join:truepgbouncer:true# optional, add this database to pgbouncer list? true by defaultcomment:pigsty meta database # optional, comment string for database
```sql
----------------------------------------------------------------------
-- File : pg-init-roles.sql
-- Path : /pg/tmp/pg-init-roles
-- Time : 2021-03-16 21:24
-- Note : managed by ansible, DO NOT CHANGE
-- Desc : creation sql script for default roles
----------------------------------------------------------------------
–###################################################################–
– dbrole_readonly –
–###################################################################–
– run as dbsu (postgres by default)
– createuser -w -p 5432 –no-login’dbrole_readonly';
– psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbrole_readonly.sql
–==================================================================–
– CREATE USER –
–==================================================================–
CREATE USER “dbrole_readonly” NOLOGIN;
–==================================================================–
– ALTER USER –
–==================================================================–
– options
ALTER USER “dbrole_readonly” NOLOGIN;
– password
– expire
– conn limit
– parameters
– comment
COMMENT ON ROLE “dbrole_readonly” IS ‘role for global readonly access’;
–==================================================================–
– GRANT ROLE –
–==================================================================–
–==================================================================–
– PGBOUNCER USER –
–==================================================================–
– user will not be added to pgbouncer user list by default,
– unless pgbouncer is explicitly set to ‘true’, which means production user
– User ‘dbrole_readonly’ will NOT be added to /etc/pgbouncer/userlist.txt
–###################################################################–
– dbrole_readwrite –
–###################################################################–
– run as dbsu (postgres by default)
– createuser -w -p 5432 –no-login’dbrole_readwrite';
– psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbrole_readwrite.sql
–==================================================================–
– CREATE USER –
–==================================================================–
CREATE USER “dbrole_readwrite” NOLOGIN;
–==================================================================–
– ALTER USER –
–==================================================================–
– options
ALTER USER “dbrole_readwrite” NOLOGIN;
– password
– expire
– conn limit
– parameters
– comment
COMMENT ON ROLE “dbrole_readwrite” IS ‘role for global read-write access’;
–==================================================================–
– GRANT ROLE –
–==================================================================–
GRANT “dbrole_readonly” TO “dbrole_readwrite”;
–==================================================================–
– PGBOUNCER USER –
–==================================================================–
– user will not be added to pgbouncer user list by default,
– unless pgbouncer is explicitly set to ‘true’, which means production user
– User ‘dbrole_readwrite’ will NOT be added to /etc/pgbouncer/userlist.txt
–###################################################################–
– dbrole_offline –
–###################################################################–
– run as dbsu (postgres by default)
– createuser -w -p 5432 –no-login’dbrole_offline';
– psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbrole_offline.sql
–==================================================================–
– CREATE USER –
–==================================================================–
CREATE USER “dbrole_offline” NOLOGIN;
–==================================================================–
– ALTER USER –
–==================================================================–
– options
ALTER USER “dbrole_offline” NOLOGIN;
– password
– expire
– conn limit
– parameters
– comment
COMMENT ON ROLE “dbrole_offline” IS ‘role for restricted read-only access (offline instance)';
–==================================================================–
– GRANT ROLE –
–==================================================================–
–==================================================================–
– PGBOUNCER USER –
–==================================================================–
– user will not be added to pgbouncer user list by default,
– unless pgbouncer is explicitly set to ‘true’, which means production user
– User ‘dbrole_offline’ will NOT be added to /etc/pgbouncer/userlist.txt
–###################################################################–
– dbrole_admin –
–###################################################################–
– run as dbsu (postgres by default)
– createuser -w -p 5432 –no-login’dbrole_admin’;
– psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbrole_admin.sql
–==================================================================–
– CREATE USER –
–==================================================================–
CREATE USER “dbrole_admin” NOLOGIN BYPASSRLS;
–==================================================================–
– ALTER USER –
–==================================================================–
– options
ALTER USER “dbrole_admin” NOLOGIN BYPASSRLS;
– password
– expire
– conn limit
– parameters
– comment
COMMENT ON ROLE “dbrole_admin” IS ‘role for object creation’;
–==================================================================–
– GRANT ROLE –
–==================================================================–
GRANT “dbrole_readwrite” TO “dbrole_admin”;
GRANT “pg_monitor” TO “dbrole_admin”;
GRANT “pg_signal_backend” TO “dbrole_admin”;
–==================================================================–
– PGBOUNCER USER –
–==================================================================–
– user will not be added to pgbouncer user list by default,
– unless pgbouncer is explicitly set to ‘true’, which means production user
– User ‘dbrole_admin’ will NOT be added to /etc/pgbouncer/userlist.txt
–###################################################################–
– postgres –
–###################################################################–
– run as dbsu (postgres by default)
– createuser -w -p 5432 –superuser’postgres';
– psql -p 5432 -AXtwqf /pg/tmp/pg-user-postgres.sql
–==================================================================–
– CREATE USER –
–==================================================================–
CREATE USER “postgres” SUPERUSER;
–==================================================================–
– ALTER USER –
–==================================================================–
– options
ALTER USER “postgres” SUPERUSER;
– password
– expire
– conn limit
– parameters
– comment
COMMENT ON ROLE “postgres” IS ‘system superuser’;
–==================================================================–
– GRANT ROLE –
–==================================================================–
–==================================================================–
– PGBOUNCER USER –
–==================================================================–
– user will not be added to pgbouncer user list by default,
– unless pgbouncer is explicitly set to ‘true’, which means production user
– User ‘postgres’ will NOT be added to /etc/pgbouncer/userlist.txt
–###################################################################–
– replicator –
–###################################################################–
– run as dbsu (postgres by default)
– createuser -w -p 5432 –replication’replicator';
– psql -p 5432 -AXtwqf /pg/tmp/pg-user-replicator.sql
–==================================================================–
– CREATE USER –
–==================================================================–
CREATE USER “replicator” REPLICATION BYPASSRLS;
–==================================================================–
– ALTER USER –
–==================================================================–
– options
ALTER USER “replicator” REPLICATION BYPASSRLS;
– password
– expire
– conn limit
– parameters
– comment
COMMENT ON ROLE “replicator” IS ‘system replicator’;
–==================================================================–
– GRANT ROLE –
–==================================================================–
GRANT “pg_monitor” TO “replicator”;
GRANT “dbrole_readonly” TO “replicator”;
–==================================================================–
– PGBOUNCER USER –
–==================================================================–
– user will not be added to pgbouncer user list by default,
– unless pgbouncer is explicitly set to ‘true’, which means production user
– User ‘replicator’ will NOT be added to /etc/pgbouncer/userlist.txt
–###################################################################–
– dbuser_monitor –
–###################################################################–
– run as dbsu (postgres by default)
– createuser -w -p 5432 ‘dbuser_monitor’;
– psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbuser_monitor.sql
–==================================================================–
– CREATE USER –
–==================================================================–
CREATE USER “dbuser_monitor” ;
–==================================================================–
– ALTER USER –
–==================================================================–
– options
ALTER USER “dbuser_monitor” ;
– password
– expire
– conn limit
ALTER USER “dbuser_monitor” CONNECTION LIMIT 16;
– parameters
– comment
COMMENT ON ROLE “dbuser_monitor” IS ‘system monitor user’;
–==================================================================–
– GRANT ROLE –
–==================================================================–
GRANT “pg_monitor” TO “dbuser_monitor”;
GRANT “dbrole_readonly” TO “dbuser_monitor”;
–==================================================================–
– PGBOUNCER USER –
–==================================================================–
– user will not be added to pgbouncer user list by default,
– unless pgbouncer is explicitly set to ‘true’, which means production user
– User ‘dbuser_monitor’ will NOT be added to /etc/pgbouncer/userlist.txt
–###################################################################–
– dbuser_admin –
–###################################################################–
– run as dbsu (postgres by default)
– createuser -w -p 5432 –superuser’dbuser_admin';
– psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbuser_admin.sql
–==================================================================–
– CREATE USER –
–==================================================================–
CREATE USER “dbuser_admin” SUPERUSER BYPASSRLS;
–==================================================================–
– ALTER USER –
–==================================================================–
– options
ALTER USER “dbuser_admin” SUPERUSER BYPASSRLS;
– password
– expire
– conn limit
– parameters
– comment
COMMENT ON ROLE “dbuser_admin” IS ‘system admin user’;
–==================================================================–
– GRANT ROLE –
–==================================================================–
GRANT “dbrole_admin” TO “dbuser_admin”;
–==================================================================–
– PGBOUNCER USER –
–==================================================================–
– user will not be added to pgbouncer user list by default,
– unless pgbouncer is explicitly set to ‘true’, which means production user
– User ‘dbuser_admin’ will NOT be added to /etc/pgbouncer/userlist.txt
–###################################################################–
– dbuser_stats –
–###################################################################–
– run as dbsu (postgres by default)
– createuser -w -p 5432 ‘dbuser_stats’;
– psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbuser_stats.sql
–==================================================================–
– CREATE USER –
–==================================================================–
CREATE USER “dbuser_stats” ;
–==================================================================–
– ALTER USER –
–==================================================================–
– options
ALTER USER “dbuser_stats” ;
– password
ALTER USER “dbuser_stats” PASSWORD ‘DBUser.Stats’;
– expire
– conn limit
– parameters
– comment
COMMENT ON ROLE “dbuser_stats” IS ‘business offline user for offline queries and ETL’;
–==================================================================–
– GRANT ROLE –
–==================================================================–
GRANT “dbrole_offline” TO “dbuser_stats”;
–==================================================================–
– PGBOUNCER USER –
–==================================================================–
– user will not be added to pgbouncer user list by default,
– unless pgbouncer is explicitly set to ‘true’, which means production user
– User ‘dbuser_stats’ will NOT be added to /etc/pgbouncer/userlist.txt
–==================================================================–
– PASSWORD OVERWRITE –
–==================================================================–
ALTER ROLE “replicator” PASSWORD ‘DBUser.Replicator’;
ALTER ROLE “dbuser_monitor” PASSWORD ‘DBUser.Monitor’;
ALTER ROLE “dbuser_admin” PASSWORD ‘DBUser.Admin’;
–==================================================================–
</details>
## pg-init-template.sql
[`pg-init-template.sql`](https://github.com/Vonng/pigsty/blob/master/roles/postgres/templates/pg-init-template.sql) 是用于初始化 `template1` 数据的脚本模板。PG模板中的变量,大抵都是通过该SQL模板渲染为最终执行的SQL命令。该模板会被渲染至集群主库的`/pg/tmp/pg-init-template.sql`并执行。
Pigsty强烈建议通过提供自定义的`pg-init`脚本完成复杂的定制。如无必要,尽量不要改动`pg-init-template.sql`中的原有逻辑。
```sql
--==================================================================--
-- Executions --
--==================================================================--
-- 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_name in pg_default_schemas %}
CREATE SCHEMA IF NOT EXISTS "{{ schema_name }}";
{% endfor %}
-- revoke public creation
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
--==================================================================--
-- Extensions --
--==================================================================--
{% for extension in pg_default_extensions %}
CREATE EXTENSION IF NOT EXISTS "{{ extension.name }}"{% if 'schema' in extension %} WITH SCHEMA "{{ extension.schema }}"{% endif %};
{% endfor %}
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 }}";
DROP VIEW IF EXISTS monitor.pg_table_bloat_human;
DROP VIEW IF EXISTS monitor.pg_index_bloat_human;
DROP VIEW IF EXISTS monitor.pg_table_bloat;
DROP VIEW IF EXISTS monitor.pg_index_bloat;
DROP VIEW IF EXISTS monitor.pg_session;
DROP VIEW IF EXISTS monitor.pg_kill;
DROP VIEW IF EXISTS monitor.pg_cancel;
DROP VIEW IF EXISTS monitor.pg_seq_scan;
– Table bloat estimate
CREATE OR REPLACE VIEW monitor.pg_table_bloat AS
SELECT CURRENT_CATALOG AS datname, nspname, relname , 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_size100) ) ) + 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 1,2,3,4,5,6,7,8,9,10
) AS s
) AS s2
) AS s3
WHERE NOT is_na;
COMMENT ON VIEW monitor.pg_table_bloat IS ‘postgres table bloat estimate’;
– Index bloat estimate
CREATE OR REPLACE VIEW monitor.pg_index_bloat AS
SELECT CURRENT_CATALOG AS datname, nspname, idxname AS relname, 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,
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 pg_namespace.oid = ic.relnamespace
JOIN pg_am ON ic.relam = pg_am.oid
WHERE pg_am.amname = ‘btree’ AND ic.relpages > 0 AND 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 1, 2, 3, 4, 5, 6
) est
LIMIT 512;
COMMENT ON VIEW monitor.pg_index_bloat IS ‘postgres index bloat estimate (btree-only)';
– table bloat pretty
CREATE OR REPLACE VIEW monitor.pg_table_bloat_human AS
SELECT nspname || ‘.’ || relname AS name,
pg_size_pretty(size) AS size,
pg_size_pretty((size * ratio)::BIGINT) AS wasted,
round(100 * ratio::NUMERIC, 2) as ratio
FROM monitor.pg_table_bloat ORDER BY wasted DESC NULLS LAST;
COMMENT ON VIEW monitor.pg_table_bloat_human IS ‘postgres table bloat pretty’;
– index bloat pretty
CREATE OR REPLACE VIEW monitor.pg_index_bloat_human AS
SELECT nspname || ‘.’ || relname AS name,
pg_size_pretty(size) AS size,
pg_size_pretty((size * ratio)::BIGINT) AS wasted,
round(100 * ratio::NUMERIC, 2) as ratio
FROM monitor.pg_index_bloat;
COMMENT ON VIEW monitor.pg_index_bloat_human IS ‘postgres index bloat pretty’;
– pg session
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 (1)
ORDER BY 1 NULLS FIRST
) t;
COMMENT ON VIEW monitor.pg_session IS ‘postgres session stats’;
– pg kill
CREATE OR REPLACE VIEW monitor.pg_kill AS
SELECT pid,
pg_terminate_backend(pid) AS killed,
datname AS dat,
usename AS usr,
application_name AS app,
client_addr AS addr,
state,
extract(epoch from now() - state_change) AS query_time,
extract(epoch from now() - xact_start) AS xact_time,
extract(epoch from now() - backend_start) AS conn_time,
substring(query, 1, 40) AS query
FROM pg_stat_activity
WHERE backend_type = ‘client backend’
AND pid <> pg_backend_pid();
COMMENT ON VIEW monitor.pg_kill IS ‘kill all backend session’;
– quick cancel view
DROP VIEW IF EXISTS monitor.pg_cancel;
CREATE OR REPLACE VIEW monitor.pg_cancel AS
SELECT pid,
pg_cancel_backend(pid) AS cancel,
datname AS dat,
usename AS usr,
application_name AS app,
client_addr AS addr,
state,
extract(epoch from now() - state_change) AS query_time,
extract(epoch from now() - xact_start) AS xact_time,
extract(epoch from now() - backend_start) AS conn_time,
substring(query, 1, 40)
FROM pg_stat_activity
WHERE state = ‘active’
AND backend_type = ‘client backend’
and pid <> pg_backend_pid();
COMMENT ON VIEW monitor.pg_cancel IS ‘cancel backend queries’;
– seq scan
DROP VIEW IF EXISTS monitor.pg_seq_scan;
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,
n_live_tup / (n_live_tup + n_dead_tup) AS dead_ratio
FROM pg_stat_user_tables
WHERE seq_scan > 0
and (n_live_tup + n_dead_tup) > 0
ORDER BY seq_tup_read DESC
LIMIT 50;
COMMENT ON VIEW monitor.pg_seq_scan IS ‘table that have seq scan’;
{% if pg_version >= 13 %}
– pg_shmem auxiliary function
– PG 13 ONLY!
CREATE OR REPLACE FUNCTION monitor.pg_shmem() RETURNS SETOF
pg_shmem_allocations AS $$ SELECT * FROM pg_shmem_allocations;$$ LANGUAGE SQL SECURITY DEFINER;
COMMENT ON FUNCTION monitor.pg_shmem() IS ‘security wrapper for pg_shmem’;
{% endif %}
–==================================================================–
– Customize Logic –
–==================================================================–
– This script will be execute on primary instance among a newly created
– postgres cluster. it will be executed as dbsu on template1 database
– put your own customize logic here
– make sure they are idempotent
</details>
一个实际的渲染样例(`pg-test`)如下所示:
<details>
```sql
----------------------------------------------------------------------
-- File : pg-init-template.sql
-- Ctime : 2018-10-30
-- Mtime : 2021-02-27
-- Desc : init postgres cluster template
-- Path : /pg/tmp/pg-init-template.sql
-- Author : Vonng(fengruohang@outlook.com)
-- Copyright (C) 2018-2021 Ruohang Feng
----------------------------------------------------------------------
--==================================================================--
-- Executions --
--==================================================================--
-- 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 --
--==================================================================--
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT USAGE ON SCHEMAS TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON TABLES TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON SEQUENCES TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT USAGE ON SCHEMAS TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON TABLES TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON SEQUENCES TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT EXECUTE ON FUNCTIONS TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT INSERT, UPDATE, DELETE ON TABLES TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT USAGE, UPDATE ON SEQUENCES TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dbrole_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT CREATE ON SCHEMAS TO dbrole_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT USAGE ON SCHEMAS TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT SELECT ON TABLES TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT SELECT ON SEQUENCES TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT USAGE ON SCHEMAS TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT SELECT ON TABLES TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT SELECT ON SEQUENCES TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT EXECUTE ON FUNCTIONS TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT INSERT, UPDATE, DELETE ON TABLES TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT USAGE, UPDATE ON SEQUENCES TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dbrole_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT CREATE ON SCHEMAS TO dbrole_admin;
-- for additional business admin, they can SET ROLE to dbrole_admin
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT USAGE ON SCHEMAS TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT SELECT ON TABLES TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT SELECT ON SEQUENCES TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT USAGE ON SCHEMAS TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT SELECT ON TABLES TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT SELECT ON SEQUENCES TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT EXECUTE ON FUNCTIONS TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT INSERT, UPDATE, DELETE ON TABLES TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT USAGE, UPDATE ON SEQUENCES TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dbrole_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT CREATE ON SCHEMAS TO dbrole_admin;
--==================================================================--
-- Schemas --
--==================================================================--
CREATE SCHEMA IF NOT EXISTS "monitor";
-- revoke public creation
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
--==================================================================--
-- Extensions --
--==================================================================--
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pgstattuple" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_qualstats" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_buffercache" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pageinspect" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_prewarm" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_visibility" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_freespacemap" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_repack" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "postgres_fdw";
CREATE EXTENSION IF NOT EXISTS "file_fdw";
CREATE EXTENSION IF NOT EXISTS "btree_gist";
CREATE EXTENSION IF NOT EXISTS "btree_gin";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "intagg";
CREATE EXTENSION IF NOT EXISTS "intarray";
--==================================================================--
-- Monitor Views --
--==================================================================--
----------------------------------------------------------------------
-- cleanse
----------------------------------------------------------------------
CREATE SCHEMA IF NOT EXISTS monitor;
GRANT USAGE ON SCHEMA monitor TO "dbuser_monitor";
GRANT USAGE ON SCHEMA monitor TO "dbuser_admin";
GRANT USAGE ON SCHEMA monitor TO "replicator";
DROP VIEW IF EXISTS monitor.pg_table_bloat_human;
DROP VIEW IF EXISTS monitor.pg_index_bloat_human;
DROP VIEW IF EXISTS monitor.pg_table_bloat;
DROP VIEW IF EXISTS monitor.pg_index_bloat;
DROP VIEW IF EXISTS monitor.pg_session;
DROP VIEW IF EXISTS monitor.pg_kill;
DROP VIEW IF EXISTS monitor.pg_cancel;
DROP VIEW IF EXISTS monitor.pg_seq_scan;
----------------------------------------------------------------------
-- Table bloat estimate
----------------------------------------------------------------------
CREATE OR REPLACE VIEW monitor.pg_table_bloat AS
SELECT CURRENT_CATALOG AS datname, nspname, relname , 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 1,2,3,4,5,6,7,8,9,10
) AS s
) AS s2
) AS s3
WHERE NOT is_na;
COMMENT ON VIEW monitor.pg_table_bloat IS 'postgres table bloat estimate';
----------------------------------------------------------------------
-- Index bloat estimate
----------------------------------------------------------------------
CREATE OR REPLACE VIEW monitor.pg_index_bloat AS
SELECT CURRENT_CATALOG AS datname, nspname, idxname AS relname, 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,
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 pg_namespace.oid = ic.relnamespace
JOIN pg_am ON ic.relam = pg_am.oid
WHERE pg_am.amname = 'btree' AND ic.relpages > 0 AND 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 1, 2, 3, 4, 5, 6
) est
LIMIT 512;
COMMENT ON VIEW monitor.pg_index_bloat IS 'postgres index bloat estimate (btree-only)';
----------------------------------------------------------------------
-- table bloat pretty
----------------------------------------------------------------------
CREATE OR REPLACE VIEW monitor.pg_table_bloat_human AS
SELECT nspname || '.' || relname AS name,
pg_size_pretty(size) AS size,
pg_size_pretty((size * ratio)::BIGINT) AS wasted,
round(100 * ratio::NUMERIC, 2) as ratio
FROM monitor.pg_table_bloat ORDER BY wasted DESC NULLS LAST;
COMMENT ON VIEW monitor.pg_table_bloat_human IS 'postgres table bloat pretty';
----------------------------------------------------------------------
-- index bloat pretty
----------------------------------------------------------------------
CREATE OR REPLACE VIEW monitor.pg_index_bloat_human AS
SELECT nspname || '.' || relname AS name,
pg_size_pretty(size) AS size,
pg_size_pretty((size * ratio)::BIGINT) AS wasted,
round(100 * ratio::NUMERIC, 2) as ratio
FROM monitor.pg_index_bloat;
COMMENT ON VIEW monitor.pg_index_bloat_human IS 'postgres index bloat pretty';
----------------------------------------------------------------------
-- pg session
----------------------------------------------------------------------
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 (1)
ORDER BY 1 NULLS FIRST
) t;
COMMENT ON VIEW monitor.pg_session IS 'postgres session stats';
----------------------------------------------------------------------
-- pg kill
----------------------------------------------------------------------
CREATE OR REPLACE VIEW monitor.pg_kill AS
SELECT pid,
pg_terminate_backend(pid) AS killed,
datname AS dat,
usename AS usr,
application_name AS app,
client_addr AS addr,
state,
extract(epoch from now() - state_change) AS query_time,
extract(epoch from now() - xact_start) AS xact_time,
extract(epoch from now() - backend_start) AS conn_time,
substring(query, 1, 40) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
AND pid <> pg_backend_pid();
COMMENT ON VIEW monitor.pg_kill IS 'kill all backend session';
----------------------------------------------------------------------
-- quick cancel view
----------------------------------------------------------------------
DROP VIEW IF EXISTS monitor.pg_cancel;
CREATE OR REPLACE VIEW monitor.pg_cancel AS
SELECT pid,
pg_cancel_backend(pid) AS cancel,
datname AS dat,
usename AS usr,
application_name AS app,
client_addr AS addr,
state,
extract(epoch from now() - state_change) AS query_time,
extract(epoch from now() - xact_start) AS xact_time,
extract(epoch from now() - backend_start) AS conn_time,
substring(query, 1, 40)
FROM pg_stat_activity
WHERE state = 'active'
AND backend_type = 'client backend'
and pid <> pg_backend_pid();
COMMENT ON VIEW monitor.pg_cancel IS 'cancel backend queries';
----------------------------------------------------------------------
-- seq scan
----------------------------------------------------------------------
DROP VIEW IF EXISTS monitor.pg_seq_scan;
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,
n_live_tup / (n_live_tup + n_dead_tup) AS dead_ratio
FROM pg_stat_user_tables
WHERE seq_scan > 0
and (n_live_tup + n_dead_tup) > 0
ORDER BY seq_tup_read DESC
LIMIT 50;
COMMENT ON VIEW monitor.pg_seq_scan IS 'table that have seq scan';
----------------------------------------------------------------------
-- pg_shmem auxiliary function
-- PG 13 ONLY!
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION monitor.pg_shmem() RETURNS SETOF
pg_shmem_allocations AS $$ SELECT * FROM pg_shmem_allocations;$$ LANGUAGE SQL SECURITY DEFINER;
COMMENT ON FUNCTION monitor.pg_shmem() IS 'security wrapper for pg_shmem';
--==================================================================--
-- Customize Logic --
--==================================================================--
-- This script will be execute on primary instance among a newly created
-- postgres cluster. it will be executed as dbsu on template1 database
-- put your own customize logic here
-- make sure they are idempotent
5 - Customize ACL
Configure access control in Pigsty
PostgreSQL中的ACL包括两部分,用户权限体系(Privileges) 与 Host Based Authentication (HBA)