264 lines
19 KiB
YAML
264 lines
19 KiB
YAML
---
|
|
#-----------------------------------------------------------------
|
|
# PG_IDENTITY
|
|
#-----------------------------------------------------------------
|
|
pg_mode: pgsql #CLUSTER # pgsql cluster mode: pgsql,citus,mssql,mysql,ivory,polar,oracle,gpsql
|
|
# pg_cluster: #CLUSTER # pgsql cluster name, required identity parameter
|
|
# pg_role: replica #INSTANCE # pgsql role, required, could be primary,replica,offline
|
|
# pg_seq: 0 #INSTANCE # pgsql instance seq number, required identity parameter
|
|
# pg_instances: {} #INSTANCE # define multiple pg instances on node in `{port:ins_vars}` format
|
|
# pg_upstream: #INSTANCE # repl upstream ip addr for standby cluster or cascade replica
|
|
# pg_shard: #CLUSTER # pgsql shard name, optional identity for sharding clusters
|
|
# pg_group: 0 #CLUSTER # pgsql shard index number, optional identity for sharding clusters
|
|
# gp_role: master #CLUSTER # greenplum role of this cluster, could be master or segment
|
|
pg_offline_query: false #INSTANCE # set to true to enable offline queries on this instance
|
|
|
|
#-----------------------------------------------------------------
|
|
# PG_BUSINESS
|
|
#-----------------------------------------------------------------
|
|
# postgres business object definition, overwrite in group vars
|
|
pg_users: [] # postgres business users
|
|
pg_databases: [] # postgres business databases
|
|
pg_services: [] # postgres business services
|
|
pg_hba_rules: [] # business hba rules for postgres
|
|
pgb_hba_rules: [] # business hba rules for pgbouncer
|
|
pg_crontab: [] # postgres crontab entries, e.g: ['00 01 * * * /pg/bin/pg-backup full']
|
|
# global credentials, overwrite in global vars
|
|
pg_dbsu_password: '' # dbsu password, empty string means no dbsu password by default
|
|
pg_replication_username: replicator
|
|
pg_replication_password: DBUser.Replicator
|
|
pg_admin_username: dbuser_dba
|
|
pg_admin_password: DBUser.DBA
|
|
pg_monitor_username: dbuser_monitor
|
|
pg_monitor_password: DBUser.Monitor
|
|
|
|
#-----------------------------------------------------------------
|
|
# PG_INSTALL
|
|
#-----------------------------------------------------------------
|
|
pg_dbsu: postgres # os dbsu name, postgres by default, better not to change it
|
|
pg_dbsu_uid: 26 # os dbsu uid and gid, 26 for default postgres users and groups
|
|
pg_dbsu_sudo: limit # dbsu sudo privilege, none,limit,all,nopass. limit by default
|
|
pg_dbsu_home: /var/lib/pgsql # postgresql home directory, `/var/lib/pgsql` by default
|
|
pg_dbsu_ssh_exchange: true # exchange postgres dbsu ssh key among the same pgsql cluster
|
|
pg_version: 18 # postgres major version to be installed, 18 by default
|
|
pg_bin_dir: /usr/pgsql/bin # postgres binary dir, `/usr/pgsql/bin` by default
|
|
pg_log_dir: /pg/log/postgres # postgres log dir, `/pg/log/postgres` by default
|
|
pg_packages: # pg packages to be installed, alias can be used
|
|
- pgsql-main pgsql-common
|
|
pg_extensions: [] # pg extensions to be installed, alias can be used
|
|
|
|
#-----------------------------------------------------------------
|
|
# PG_BOOTSTRAP
|
|
#-----------------------------------------------------------------
|
|
pg_data: /pg/data # postgres data directory, `/pg/data` by default
|
|
pg_fs_main: /data/postgres # postgres main data directory, `/data/postgres` by default
|
|
pg_fs_backup: /data/backups # postgres backup data directory, `/data/backups` by default
|
|
pg_storage_type: SSD # storage type for pg main data, SSD,HDD, SSD by default
|
|
pg_dummy_filesize: 64MiB # size of `/pg/dummy`, hold 64MB disk space for emergency use
|
|
pg_listen: '0.0.0.0' # postgres/pgbouncer listen addresses, comma separated list
|
|
pg_port: 5432 # postgres listen port, 5432 by default
|
|
pg_localhost: /var/run/postgresql # postgres unix socket dir for localhost connection
|
|
patroni_enabled: true # if disabled, no postgres cluster will be created during init
|
|
patroni_mode: default # patroni working mode: default,pause,remove
|
|
pg_namespace: /pg # top level key namespace in etcd, used by patroni & vip
|
|
patroni_port: 8008 # patroni listen port, 8008 by default
|
|
patroni_log_dir: /pg/log/patroni # patroni log dir, `/pg/log/patroni` by default
|
|
patroni_ssl_enabled: false # secure patroni RestAPI communications with SSL?
|
|
patroni_watchdog_mode: off # patroni watchdog mode: automatic,required,off. off by default
|
|
patroni_username: postgres # patroni restapi username, `postgres` by default
|
|
patroni_password: Patroni.API # patroni restapi password, `Patroni.API` by default
|
|
pg_etcd_password: '' # etcd password for this pg cluster, '' to use pg_cluster
|
|
pg_primary_db: postgres # primary database name, used by citus,etc... ,postgres by default
|
|
pg_parameters: {} # extra parameters in postgresql.auto.conf
|
|
pg_files: [] # extra files to be copied to postgres data directory (e.g. license)
|
|
pg_conf: oltp.yml # config template: oltp,olap,crit,tiny. `oltp.yml` by default
|
|
pg_max_conn: auto # postgres max connections, `auto` will use recommended value
|
|
pg_shared_buffer_ratio: 0.25 # postgres shared buffers ratio, 0.25 by default, 0.1~0.4
|
|
pg_io_method: worker # io method for postgres, auto,fsync,worker,io_uring, worker by default
|
|
pg_rto: norm # shared rto mode for patroni & haproxy: fast,norm,safe,wide
|
|
pg_rto_plan: # [ttl, loop, retry, start, margin, inter, fastinter, downinter, rise, fall]
|
|
fast: [ 20 ,5 ,5 ,15 ,5 ,'1s' ,'0.5s' ,'1s' ,3 ,3 ] # rto < 30s
|
|
norm: [ 30 ,5 ,10 ,25 ,5 ,'2s' ,'1s' ,'2s' ,3 ,3 ] # rto < 45s
|
|
safe: [ 60 ,10 ,20 ,45 ,10 ,'3s' ,'1.5s' ,'3s' ,3 ,3 ] # rto < 90s
|
|
wide: [ 120 ,20 ,30 ,95 ,15 ,'4s' ,'2s' ,'4s' ,3 ,3 ] # rto < 150s
|
|
pg_rpo: 1048576 # recovery point objective in bytes, `1MiB` at most by default
|
|
pg_libs: 'pg_stat_statements, auto_explain' # preloaded libraries, `pg_stat_statements,auto_explain` by default
|
|
pg_delay: 0 # replication apply delay for standby cluster leader
|
|
pg_checksum: true # enable data checksum for postgres cluster?
|
|
pg_pwd_enc: scram-sha-256 # passwords encryption algorithm: fixed to scram-sha-256
|
|
pg_encoding: UTF8 # database cluster encoding, `UTF8` by default
|
|
pg_locale: C # database cluster local, `C` by default
|
|
pg_lc_collate: C # database cluster collate, `C` by default
|
|
pg_lc_ctype: C # database character type, `C` by default
|
|
#pgsodium_key: "" # pgsodium key, 64 hex digit, default to sha256(pg_cluster)
|
|
#pgsodium_getkey_script: "" # pgsodium getkey script path, pgsodium_getkey by default
|
|
|
|
#-----------------------------------------------------------------
|
|
# PG_PROVISION
|
|
#-----------------------------------------------------------------
|
|
pg_provision: true # provision postgres cluster after bootstrap
|
|
pg_init: pg-init # provision init script for cluster template, `pg-init` by default
|
|
pg_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 ,comment: system superuser }
|
|
- { name: replicator ,replication: true ,roles: [pg_monitor, dbrole_readonly] ,comment: system replicator }
|
|
- { name: dbuser_dba ,superuser: true ,roles: [dbrole_admin] ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 , comment: pgsql admin user }
|
|
- { name: dbuser_monitor ,roles: [pg_monitor, dbrole_readonly] ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user }
|
|
pg_default_privileges: # default privileges when created by admin user
|
|
- 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
|
|
pg_default_schemas: [ monitor ] # default schemas to be created
|
|
pg_default_extensions: # default extensions to be created
|
|
- { name: pg_stat_statements ,schema: monitor }
|
|
- { name: pgstattuple ,schema: monitor }
|
|
- { name: pg_buffercache ,schema: monitor }
|
|
- { name: pageinspect ,schema: monitor }
|
|
- { name: pg_prewarm ,schema: monitor }
|
|
- { name: pg_visibility ,schema: monitor }
|
|
- { name: pg_freespacemap ,schema: monitor }
|
|
- { name: postgres_fdw ,schema: public }
|
|
- { name: file_fdw ,schema: public }
|
|
- { name: btree_gist ,schema: public }
|
|
- { name: btree_gin ,schema: public }
|
|
- { name: pg_trgm ,schema: public }
|
|
- { name: intagg ,schema: public }
|
|
- { name: intarray ,schema: public }
|
|
- { name: pg_repack }
|
|
pg_reload: true # reload postgres/pgbouncer/vip after conf changes
|
|
pg_default_hba_rules: # postgres default host-based authentication rules, order by `order`
|
|
- {user: '${dbsu}' ,db: all ,addr: local ,auth: ident ,title: 'dbsu access via local os user ident' ,order: 100}
|
|
- {user: '${dbsu}' ,db: replication ,addr: local ,auth: ident ,title: 'dbsu replication from local os ident' ,order: 150}
|
|
- {user: '${repl}' ,db: replication ,addr: localhost ,auth: pwd ,title: 'replicator replication from localhost',order: 200}
|
|
- {user: '${repl}' ,db: replication ,addr: intra ,auth: pwd ,title: 'replicator replication from intranet' ,order: 250}
|
|
- {user: '${repl}' ,db: postgres ,addr: intra ,auth: pwd ,title: 'replicator postgres db from intranet' ,order: 300}
|
|
- {user: '${monitor}' ,db: all ,addr: localhost ,auth: pwd ,title: 'monitor from localhost with password' ,order: 350}
|
|
- {user: '${monitor}' ,db: all ,addr: infra ,auth: pwd ,title: 'monitor from infra host with password',order: 400}
|
|
- {user: '${admin}' ,db: all ,addr: intra ,auth: pwd ,title: 'admin @ intranet nodes with pwd' ,order: 450}
|
|
- {user: '${admin}' ,db: all ,addr: world ,auth: ssl ,title: 'admin @ everywhere with ssl & pwd' ,order: 500}
|
|
- {user: '+dbrole_readonly',db: all ,addr: localhost ,auth: pwd ,title: 'pgbouncer read/write via local socket',order: 550}
|
|
- {user: '+dbrole_readonly',db: all ,addr: intra ,auth: pwd ,title: 'read/write biz user via password' ,order: 600}
|
|
- {user: '+dbrole_offline' ,db: all ,addr: intra ,auth: pwd ,title: 'allow etl offline tasks from intranet',order: 650}
|
|
pgb_default_hba_rules: # pgbouncer default host-based authentication rules, order by `order`
|
|
- {user: '${dbsu}' ,db: pgbouncer ,addr: local ,auth: peer ,title: 'dbsu local admin access with os ident',order: 100}
|
|
- {user: 'all' ,db: all ,addr: localhost ,auth: pwd ,title: 'allow all user local access with pwd' ,order: 150}
|
|
- {user: '${monitor}' ,db: pgbouncer ,addr: intra ,auth: pwd ,title: 'monitor access via intranet with pwd' ,order: 200}
|
|
- {user: '${monitor}' ,db: all ,addr: world ,auth: deny ,title: 'reject all other monitor access addr' ,order: 250}
|
|
- {user: '${admin}' ,db: all ,addr: intra ,auth: pwd ,title: 'admin access via intranet with pwd' ,order: 300}
|
|
- {user: '${admin}' ,db: all ,addr: world ,auth: deny ,title: 'reject all other admin access addr' ,order: 350}
|
|
- {user: 'all' ,db: all ,addr: intra ,auth: pwd ,title: 'allow all user intra access with pwd' ,order: 400}
|
|
|
|
#-----------------------------------------------------------------
|
|
# PG_BACKUP
|
|
#-----------------------------------------------------------------
|
|
pgbackrest_enabled: true # enable pgbackrest on pgsql host?
|
|
pgbackrest_log_dir: /pg/log/pgbackrest # pgbackrest log dir, `/pg/log/pgbackrest` by default
|
|
pgbackrest_method: local # pgbackrest repo method: local,minio,[user-defined...]
|
|
pgbackrest_init_backup: true # take a full backup after pgbackrest is initialized?
|
|
pgbackrest_repo: # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
|
|
local: # default pgbackrest repo with local posix fs
|
|
path: /pg/backup # local backup directory, `/pg/backup` by default
|
|
retention_full_type: count # retention full backups by count
|
|
retention_full: 2 # keep 2, at most 3 full backups when using local fs repo
|
|
minio: # optional minio repo for pgbackrest
|
|
type: s3 # minio is s3-compatible, so s3 is used
|
|
s3_endpoint: sss.pigsty # minio endpoint domain name, `sss.pigsty` by default
|
|
s3_region: us-east-1 # minio region, us-east-1 by default, useless for minio
|
|
s3_bucket: pgsql # minio bucket name, `pgsql` by default
|
|
s3_key: pgbackrest # minio user access key for pgbackrest
|
|
s3_key_secret: S3User.Backup # minio user secret key for pgbackrest
|
|
s3_uri_style: path # use path style uri for minio rather than host style
|
|
path: /pgbackrest # minio backup path, default is `/pgbackrest`
|
|
storage_port: 9000 # minio port, 9000 by default
|
|
storage_ca_file: /etc/pki/ca.crt # minio ca file path, `/etc/pki/ca.crt` by default
|
|
block: y # Enable block incremental backup
|
|
bundle: y # bundle small files into a single file
|
|
bundle_limit: 20MiB # Limit for file bundles, 20MiB for object storage
|
|
bundle_size: 128MiB # Target size for file bundles, 128MiB for object storage
|
|
cipher_type: aes-256-cbc # enable AES encryption for remote backup repo
|
|
cipher_pass: pgBackRest # AES encryption password, default is 'pgBackRest'
|
|
retention_full_type: time # retention full backup by time on minio repo
|
|
retention_full: 14 # keep full backup for the last 14 days
|
|
|
|
#-----------------------------------------------------------------
|
|
# PG_ACCESS
|
|
#-----------------------------------------------------------------
|
|
pgbouncer_enabled: true # if disabled, pgbouncer will not be launched on pgsql host
|
|
pgbouncer_port: 6432 # pgbouncer listen port, 6432 by default
|
|
pgbouncer_log_dir: /pg/log/pgbouncer # pgbouncer log dir, `/pg/log/pgbouncer` by default
|
|
pgbouncer_auth_query: false # query postgres to retrieve unlisted business users?
|
|
pgbouncer_poolmode: transaction # pooling mode: transaction,session,statement, transaction by default
|
|
pgbouncer_sslmode: disable # pgbouncer client ssl mode, disable by default
|
|
pgbouncer_ignore_param: [ extra_float_digits, application_name, TimeZone, DateStyle, IntervalStyle, search_path ]
|
|
pg_weight: 100 #INSTANCE # relative load balance weight in service, 100 by default, 0-255
|
|
pg_service_provider: '' # dedicate haproxy node group name, or empty string for local nodes by default
|
|
pg_default_service_dest: pgbouncer # default service destination if svc.dest='default'
|
|
pg_default_services: # postgres default service definitions
|
|
- { name: primary ,port: 5433 ,dest: default ,check: /primary ,selector: "[]" }
|
|
- { name: replica ,port: 5434 ,dest: default ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" }
|
|
- { name: default ,port: 5436 ,dest: postgres ,check: /primary ,selector: "[]" }
|
|
- { name: offline ,port: 5438 ,dest: postgres ,check: /replica ,selector: "[? pg_role == `offline` || pg_offline_query ]" , backup: "[? pg_role == `replica` && !pg_offline_query]"}
|
|
pg_vip_enabled: false # enable a l2 vip for pgsql primary? false by default
|
|
pg_vip_address: 127.0.0.1/24 # vip address in `<ipv4>/<mask>` format, require if vip is enabled
|
|
pg_vip_interface: eth0 # vip network interface to listen, eth0 by default
|
|
pg_dns_suffix: '' # pgsql dns suffix, '' by default
|
|
pg_dns_target: auto # auto, primary, vip, none, or ad hoc ip
|
|
|
|
#-----------------------------------------------------------------
|
|
# INTERNAL
|
|
#-----------------------------------------------------------------
|
|
pg_exists: false
|
|
pg_cert_exists: false
|
|
#-----------------------------------------------------------------
|
|
# CA (Reference)
|
|
#-----------------------------------------------------------------
|
|
cert_validity: 7300d # cert validity, 20 years by default
|
|
#-----------------------------------------------------------------
|
|
# NODE_TUNE (Reference)
|
|
#-----------------------------------------------------------------
|
|
node_tune: oltp # install and activate tuned profile: none|oltp|olap|crit|tiny
|
|
#-----------------------------------------------------------------
|
|
# NODE_SEC (Reference)
|
|
#-----------------------------------------------------------------
|
|
node_firewall_intranet: # which intranet cidr considered as internal network
|
|
- 10.0.0.0/8
|
|
- 192.168.0.0/16
|
|
- 172.16.0.0/12
|
|
#-----------------------------------------------------------------
|
|
# ETCD (Reference)
|
|
#-----------------------------------------------------------------
|
|
etcd_port: 2379 # etcd client port, 2379 by default
|
|
etcd_root_password: Etcd.Root # etcd root password for RBAC
|
|
#-----------------------------------------------------------------
|
|
# PROMETHEUS (Reference)
|
|
#-----------------------------------------------------------------
|
|
exporter_metrics_path: /metrics # exporter metric path, `/metrics` by default
|
|
#-----------------------------------------------------------------
|
|
# GRAFANA (Reference)
|
|
#-----------------------------------------------------------------
|
|
grafana_enabled: true # enable grafana on this infra node?
|
|
grafana_admin_username: admin # default grafana admin username
|
|
grafana_admin_password: pigsty # default grafana admin password
|
|
#-----------------------------------------------------------------
|
|
# DNS
|
|
#-----------------------------------------------------------------
|
|
dns_enabled: true # setup dnsmasq on this infra node?
|
|
...
|