pg-meta:hosts:{10.10.10.10:{pg_seq:1, pg_role:primary , pg_offline_query:true}}vars:pg_cluster:pg-metapg_databases:# define business databases on this cluster, array of database definition- name:meta # REQUIRED, `name` is the only mandatory field of a database definitionbaseline:cmdb.sql # optional, database sql baseline path, (relative path among ansible search path, e.g files/)pgbouncer:true# optional, add this database to pgbouncer database list? true by defaultschemas:[pigsty] # optional, additional schemas to be created, array of schema namesextensions:# optional, additional extensions to be installed: array of `{name[,schema]}`- {name:postgis , schema:public }- {name:timescaledb }comment:pigsty meta database # optional, comment string for this databaseowner:postgres # optional, database owner, postgres by defaulttemplate:template1 # optional, which template to use, template1 by defaultencoding:UTF8 # optional, database encoding, UTF8 by default. (MUST same as template database)locale:C # optional, database locale, C by default. (MUST same as template database)lc_collate:C # optional, database collate, C by default. (MUST same as template database)lc_ctype:C # optional, database ctype, C by default. (MUST same as template database)tablespace:pg_default # optional, default tablespace, 'pg_default' by default.allowconn:true# optional, allow connection, true by default. false will disable connect at allrevokeconn:false# optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)register_datasource:true# optional, register this database to grafana datasources? true by defaultconnlimit:-1# optional, database connection limit, default -1 disable limitpool_auth_user:dbuser_meta # optional, all connection to this pgbouncer database will be authenticated by this userpool_mode:transaction # optional, pgbouncer pool mode at database level, default transactionpool_size:64# optional, pgbouncer pool size at database level, default 64pool_size_reserve:32# optional, pgbouncer pool size reserve at database level, default 32pool_size_min:0# optional, pgbouncer pool size min at database level, default 0pool_max_db_conn:100# optional, max database connections at database level, default 100- {name:grafana ,owner:dbuser_grafana ,revokeconn:true ,comment:grafana primary database }- {name:bytebase ,owner:dbuser_bytebase ,revokeconn:true ,comment:bytebase primary database }- {name:kong ,owner:dbuser_kong ,revokeconn:true ,comment:kong the api gateway database }- {name:gitea ,owner:dbuser_gitea ,revokeconn:true ,comment:gitea meta database }- {name:wiki ,owner:dbuser_wiki ,revokeconn:true ,comment:wiki meta database }pg_users:# define business users/roles on this cluster, array of user definition- name:dbuser_meta # REQUIRED, `name` is the only mandatory field of a user definitionpassword:DBUser.Meta # optional, password, can be a scram-sha-256 hash string or plain textlogin:true# optional, can log in, true by default (new biz ROLE should be false)superuser:false# optional, is superuser? false by defaultcreatedb:false# optional, can create database? false by defaultcreaterole:false# optional, can create role? false by defaultinherit:true# optional, can this role use inherited privileges? true by defaultreplication:false# optional, can this role do replication? false by defaultbypassrls:false# optional, can this role bypass row level security? false by defaultpgbouncer:true# optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)connlimit:-1# optional, user connection limit, default -1 disable limitexpire_in:3650# optional, now + n days when this role is expired (OVERWRITE expire_at)expire_at:'2030-12-31'# optional, YYYY-MM-DD 'timestamp' when this role is expired (OVERWRITTEN by expire_in)comment:pigsty admin user # optional, comment string for this user/roleroles:[dbrole_admin] # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}parameters:{}# optional, role level parameters with `ALTER ROLE SET`pool_mode:transaction # optional, pgbouncer pool mode at user level, transaction by defaultpool_connlimit:-1# optional, max database connections at user level, default -1 disable limit- {name:dbuser_view ,password:DBUser.Viewer ,pgbouncer:true ,roles:[dbrole_readonly], comment:read-only viewer for meta database}- {name:dbuser_grafana ,password:DBUser.Grafana ,pgbouncer:true ,roles:[dbrole_admin] ,comment:admin user for grafana database }- {name:dbuser_bytebase ,password:DBUser.Bytebase ,pgbouncer:true ,roles:[dbrole_admin] ,comment:admin user for bytebase database }- {name:dbuser_kong ,password:DBUser.Kong ,pgbouncer:true ,roles:[dbrole_admin] ,comment:admin user for kong api gateway }- {name:dbuser_gitea ,password:DBUser.Gitea ,pgbouncer:true ,roles:[dbrole_admin] ,comment:admin user for gitea service }- {name:dbuser_wiki ,password:DBUser.Wiki ,pgbouncer:true ,roles:[dbrole_admin] ,comment:admin user for wiki.js service }pg_services:# extra services in addition to pg_default_services, array of service definition# standby service will route {ip|name}:5435 to sync replica's pgbouncer (5435->6432 standby)- name:standby # required, service name, the actual svc name will be prefixed with `pg_cluster`, e.g: pg-meta-standbyport:5435# required, service exposed port (work as kubernetes service node port mode)ip:"*"# optional, service bind ip address, `*` for all ip by defaultselector:"[]"# required, service member selector, use JMESPath to filter inventorydest:default # optional, destination port, default|postgres|pgbouncer|<port_number>, 'default' by defaultcheck:/sync # optional, health check url path, / by defaultbackup:"[? pg_role == `primary`]"# backup server selectormaxconn:3000# optional, max allowed front-end connectionbalance:roundrobin # optional, haproxy load balance algorithm (roundrobin by default, other: leastconn)options:'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'pg_hba_rules:- {user:dbuser_view , db:all ,addr:infra ,auth:pwd ,title:'allow grafana dashboard access cmdb from infra nodes'}pg_vip_enabled:truepg_vip_address:10.10.10.2/24pg_vip_interface:eth1node_crontab:# make a full backup 1 am everyday- '00 01 * * * postgres /pg/bin/pg-backup full'
pg-meta:# 3 instance postgres cluster `pg-meta`hosts:10.10.10.10:{pg_seq:1, pg_role:primary }10.10.10.11:{pg_seq:2, pg_role:replica }10.10.10.12:{pg_seq:3, pg_role:replica , pg_offline_query:true}vars:pg_cluster:pg-metapg_conf:crit.ymlpg_users:- {name:dbuser_meta , password:DBUser.Meta , pgbouncer:true , roles:[ dbrole_admin ] , comment:pigsty admin user }- {name:dbuser_view , password:DBUser.Viewer , pgbouncer:true , roles:[ dbrole_readonly ] , comment:read-only viewer for meta database }pg_databases:- {name:meta ,baseline:cmdb.sql ,comment:pigsty meta database ,schemas:[pigsty] ,extensions:[{name:postgis, schema:public}, {name: timescaledb}]}pg_default_service_dest:postgrespg_services:- {name:standby ,src_ip:"*",port:5435 , dest:default ,selector:"[]", backup:"[? pg_role == `primary`]"}pg_vip_enabled:truepg_vip_address:10.10.10.2/24pg_vip_interface:eth1pg_listen:'${ip},${vip},${lo}'patroni_ssl_enabled:truepgbouncer_sslmode:requirepgbackrest_method:miniopg_libs:'timescaledb, $libdir/passwordcheck, pg_stat_statements, auto_explain'# add passwordcheck extension to enforce strong passwordpg_default_roles:# default roles and users in postgres cluster- {name:dbrole_readonly ,login:false ,comment:role for global read-only access }- {name:dbrole_offline ,login:false ,comment:role for restricted read-only access }- {name:dbrole_readwrite ,login:false ,roles:[dbrole_readonly] ,comment:role for global read-write access }- {name:dbrole_admin ,login:false ,roles:[pg_monitor, dbrole_readwrite] ,comment:role for object creation }- {name:postgres ,superuser:true ,expire_in:7300 ,comment:system superuser }- {name:replicator ,replication:true ,expire_in:7300 ,roles:[pg_monitor, dbrole_readonly] ,comment:system replicator }- {name:dbuser_dba ,superuser:true ,expire_in:7300 ,roles:[dbrole_admin] ,pgbouncer:true ,pool_mode:session, pool_connlimit:16 , comment:pgsql admin user }- {name:dbuser_monitor ,roles:[pg_monitor] ,expire_in:7300 ,pgbouncer:true ,parameters:{log_min_duration_statement:1000 } ,pool_mode:session ,pool_connlimit:8 ,comment:pgsql monitor user }pg_default_hba_rules:# postgres host-based auth rules by default- {user:'${dbsu}',db:all ,addr:local ,auth:ident ,title:'dbsu access via local os user ident'}- {user:'${dbsu}',db:replication ,addr:local ,auth:ident ,title:'dbsu replication from local os ident'}- {user:'${repl}',db:replication ,addr:localhost ,auth:ssl ,title:'replicator replication from localhost'}- {user:'${repl}',db:replication ,addr:intra ,auth:ssl ,title:'replicator replication from intranet'}- {user:'${repl}',db:postgres ,addr:intra ,auth:ssl ,title:'replicator postgres db from intranet'}- {user:'${monitor}',db:all ,addr:localhost ,auth:pwd ,title:'monitor from localhost with password'}- {user:'${monitor}',db:all ,addr:infra ,auth:ssl ,title:'monitor from infra host with password'}- {user:'${admin}',db:all ,addr:infra ,auth:ssl ,title:'admin @ infra nodes with pwd & ssl'}- {user:'${admin}',db:all ,addr:world ,auth:cert ,title:'admin @ everywhere with ssl & cert'}- {user:'+dbrole_readonly',db:all ,addr:localhost ,auth:ssl ,title:'pgbouncer read/write via local socket'}- {user:'+dbrole_readonly',db:all ,addr:intra ,auth:ssl ,title:'read/write biz user via password'}- {user:'+dbrole_offline' ,db:all ,addr:intra ,auth:ssl ,title:'allow etl offline tasks from intranet'}pgb_default_hba_rules:# pgbouncer host-based authentication rules- {user:'${dbsu}',db:pgbouncer ,addr:local ,auth:peer ,title:'dbsu local admin access with os ident'}- {user:'all' ,db:all ,addr:localhost ,auth:pwd ,title:'allow all user local access with pwd'}- {user:'${monitor}',db:pgbouncer ,addr:intra ,auth:ssl ,title:'monitor access via intranet with pwd'}- {user:'${monitor}',db:all ,addr:world ,auth:deny ,title:'reject all other monitor access addr'}- {user:'${admin}',db:all ,addr:intra ,auth:ssl ,title:'admin access via intranet with pwd'}- {user:'${admin}',db:all ,addr:world ,auth:deny ,title:'reject all other admin access addr'}- {user:'all' ,db:all ,addr:intra ,auth:ssl ,title:'allow all user intra access with pwd'}# OPTIONAL delayed cluster for pg-metapg-meta-delay:# delayed instance for pg-meta (1 hour ago)hosts:{10.10.10.13:{pg_seq:1, pg_role:primary, pg_upstream:10.10.10.10, pg_delay:1h } }vars:{pg_cluster:pg-meta-delay }
Citus分布式集群
下面是一个四节点的 Citus 分布式集群的声明式配置：
all:children:pg-citus0:# citus coordinator, pg_group = 0hosts:{10.10.10.10:{pg_seq:1, pg_role:primary } }vars:{pg_cluster:pg-citus0 , pg_group:0}pg-citus1:# citus data node 1hosts:{10.10.10.11:{pg_seq:1, pg_role:primary } }vars:{pg_cluster:pg-citus1 , pg_group:1}pg-citus2:# citus data node 2hosts:{10.10.10.12:{pg_seq:1, pg_role:primary } }vars:{pg_cluster:pg-citus2 , pg_group:2}pg-citus3:# citus data node 3, with an extra replicahosts:10.10.10.13:{pg_seq:1, pg_role:primary }10.10.10.14:{pg_seq:2, pg_role:replica }vars:{pg_cluster:pg-citus3 , pg_group:3}vars:# global parameters for all citus clusterspg_mode:citus # pgsql cluster mode: cituspg_shard:pg-citus # citus shard name: pg-cituspatroni_citus_db:meta # citus distributed database namepg_dbsu_password:DBUser.Postgres# all dbsu password access for citus clusterpg_users:[{name:dbuser_meta ,password:DBUser.Meta ,pgbouncer:true ,roles:[dbrole_admin ] } ]pg_databases:[{name:meta ,extensions:[{name:citus }, { name: postgis }, { name: timescaledb } ] } ]pg_hba_rules:- {user:'all' ,db:all ,addr:127.0.0.1/32 ,auth:ssl ,title:'all user ssl access from localhost'}- {user:'all' ,db:all ,addr:intra ,auth:ssl ,title:'all user ssl access from intranet'}
Redis集群
下面给出了 Redis 主从集群、哨兵集群、以及 Redis Cluster 的声明配置样例
redis-ms:# redis classic primary & replicahosts:{10.10.10.10:{redis_node:1 , redis_instances:{6379:{}, 6380:{replica_of:'10.10.10.10 6379'}}}}vars:{redis_cluster:redis-ms ,redis_password:'redis.ms' ,redis_max_memory:64MB }redis-meta:# redis sentinel x 3hosts:{10.10.10.11:{redis_node:1 , redis_instances:{26379:{} ,26380:{} ,26381:{}}}}vars:redis_cluster:redis-metaredis_password:'redis.meta'redis_mode:sentinelredis_max_memory:16MBredis_sentinel_monitor:# primary list for redis sentinel, use cls as name, primary ip:port- {name:redis-ms, host:10.10.10.10, port:6379 ,password:redis.ms, quorum:2}redis-test:# redis native cluster: 3m x 3shosts:10.10.10.12:{redis_node:1 ,redis_instances:{6379:{} ,6380:{} ,6381:{}}}10.10.10.13:{redis_node:2 ,redis_instances:{6379:{} ,6380:{} ,6381:{}}}vars:{redis_cluster:redis-test ,redis_password:'redis.test' ,redis_mode:cluster, redis_max_memory:32MB }
ETCD集群
下面给出了一个三节点的 Etcd 集群声明式配置样例：
etcd:# dcs service for postgres/patroni ha consensushosts:# 1 node for testing, 3 or 5 for production10.10.10.10:{etcd_seq:1}# etcd_seq required10.10.10.11:{etcd_seq:2}# assign from 1 ~ n10.10.10.12:{etcd_seq:3}# odd number pleasevars:# cluster level parameter override roles/etcdetcd_cluster:etcd # mark etcd cluster name etcdetcd_safeguard:false# safeguard against purgingetcd_clean:true# purge etcd during init process
- GRANT USAGE ON SCHEMAS TO dbrole_readonly- GRANT SELECT ON TABLES TO dbrole_readonly- GRANT SELECT ON SEQUENCES TO dbrole_readonly- GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly- GRANT USAGE ON SCHEMAS TO dbrole_offline- GRANT SELECT ON TABLES TO dbrole_offline- GRANT SELECT ON SEQUENCES TO dbrole_offline- GRANT EXECUTE ON FUNCTIONS TO dbrole_offline- GRANT INSERT ON TABLES TO dbrole_readwrite- GRANT UPDATE ON TABLES TO dbrole_readwrite- GRANT DELETE ON TABLES TO dbrole_readwrite- GRANT USAGE ON SEQUENCES TO dbrole_readwrite- GRANT UPDATE ON SEQUENCES TO dbrole_readwrite- GRANT TRUNCATE ON TABLES TO dbrole_admin- GRANT REFERENCES ON TABLES TO dbrole_admin- GRANT TRIGGER ON TABLES TO dbrole_admin- GRANT CREATE ON SCHEMAS TO dbrole_admin
{%forprivinpg_default_privileges%}ALTERDEFAULTPRIVILEGESFORROLE{{pg_dbsu}}{{priv}};{%endfor%}{%forprivinpg_default_privileges%}ALTERDEFAULTPRIVILEGESFORROLE{{pg_admin_username}}{{priv}};{%endfor%}-- 对于其他业务管理员而言，它们应当在执行 DDL 前执行 SET ROLE dbrole_admin，从而使用对应的默认权限配置。
{%forprivinpg_default_privileges%}ALTERDEFAULTPRIVILEGESFORROLE"dbrole_admin"{{priv}};{%endfor%}
