当前是用于打印的多页视图打印.

修改配置

如何完成Pigsty配置文件的修改

1: 配置说明
2: 连接参数
3: 本地源参数
4: 节点参数
5: 元节点参数
6: 元数据库参数
7: PG安装参数
8: PG供给参数
9: PG模板
10: 监控系统参数
11: 负载均衡参数

修改配置

了解配置文件的格式
了解Ansible变量的合并方式
配置本地Yum源
配置基础设施服务
配置DCS
配置Postgres
配置监控系统
配置流量代理与负载均衡器

1 - 配置说明

Pigsty配置文件的结构

Pigsty采用遵循Ansible规则的单一YAML配置文件。

配置使用

Pigsty的配置文件默认位于conf/all.yml，配置文件需要与Ansible配合使用。

您可以在当前目录的ansible.cfg中指定默认配置文件路径。或者在命令行执行剧本时通过-i conf/your-config.yml的方式，手工指定自定义配置文件路径。详情请参考执行剧本一节。

整体结构

这里展示了YAML配置文件的顶层结构，一个最外层的all对象，其中all.children定义了环境中的数据库集群，而all.vars定义了整个环境中的全局配置变量。

---
# top-level namespace, match all hosts
all:
  #==================================================================#
  #                           Clusters                               #
  #==================================================================#
  # top-level groups, one group per database cluster (and special group 'meta')
  children:

    #-----------------------------
    # meta controller
    #-----------------------------
    meta: <2 keys>

    #-----------------------------
    # cluster: pg-meta
    #-----------------------------
    pg-meta: <2 keys>

    #-----------------------------
    # cluster: pg-test
    #-----------------------------
    pg-test: <2 keys>

  #==================================================================#
  #                           Globals                                #
  #==================================================================#
  vars: <123 keys>
...

集群定义

下沉至某一个具体的集群，例如all.children.pg-test，则是单个数据库集群的定义部分。

这里hosts中定义了数据库集群的成员，包括序号，角色，以及ansible连接方式（可选）

这里ansible_host指示ansible通过SSH Alias的方式，而不是IP直连的方式访问连接远程节点，对于需要跳板机代理的机器非常实用。

集群内部的vars中定义的变量会覆盖全局变量，因此您可以有的放矢的针对不同集群进行不同的配置与定制。

#-----------------------------
# cluster: pg-test
#-----------------------------
pg-test: # define cluster named 'pg-test'
  # - cluster members - #
  hosts:
    10.10.10.11: {pg_seq: 1, pg_role: primary, ansible_host: node-1}
    10.10.10.12: {pg_seq: 2, pg_role: replica, ansible_host: node-2}
    10.10.10.13: {pg_seq: 3, pg_role: replica, ansible_host: node-3}

  # - cluster configs - #
  vars:
    # basic settings
    pg_cluster: pg-test                 # define actual cluster name
    pg_version: 13                      # define installed pgsql version
    node_tune: tiny                     # tune node into oltp|olap|crit|tiny mode
    pg_conf: tiny.yml                   # tune pgsql into oltp/olap/crit/tiny mode

    pg_users:
      - username: test
        password: test
        comment: default test user
        groups: [ dbrole_readwrite ]    # dborole_admin|dbrole_readwrite|dbrole_readonly
    pg_databases:                       # create a business database 'test'
      - name: test
        extensions: [{name: postgis}]   # create extra extension postgis
        parameters:                     # overwrite database meta's default search_path
          search_path: public,monitor
    pg_default_database: test           # default database will be used as primary monitor target

    # proxy settings
    vip_enabled: true                   # enable/disable vip (require members in same LAN)
    vip_address: 10.10.10.3             # virtual ip address
    vip_cidrmask: 8                     # cidr network mask length
    vip_interface: eth1                 # interface to add virtual ip

变量合并

Pigsty中的变量遵循Ansible的合并规则，所有变量最后会按优先级顺序覆盖式合并，最终压平到主机层次。参见Ansible变量优先级

command line values (for example, -u my_user, these are not variables)

role defaults (defined in role/defaults/main.yml) 1

inventory file or script group vars 2

inventory group_vars/all 3

playbook group_vars/all 3

inventory group_vars/* 3

playbook group_vars/* 3

inventory file or script host vars 2

inventory host_vars/* 3

playbook host_vars/* 3

host facts / cached set_facts 4

play vars

play vars_prompt

play vars_files

role vars (defined in role/vars/main.yml)

block vars (only for tasks in block)

task vars (only for the task)

include_vars

set_facts / registered vars

role (and include_role) params

include params

extra vars (for example, -e "user=my_user")(always win precedence)

配置文件中的变量只有三个层级：

group_vars/all

全局变量，定义于all.vars

设置整个环境统一的参数（如基础设施信息），优先级最低。
group_vars/*

集群变量，定义于all.children.<cluster-name>.vars中

用于设置某个集群的特定配置，如集群名称。
host_vars/*

实例变量，定义于all.children.<cluster-name>.hosts.<host-name>

用于设置特定实例的配置，如实例标号。

您可以在执行剧本时，通过-e的命令行参数覆盖配置文件中的选项，例如：

./initdb.yml -e pg_exists_action=clean

这里-e pg_exists_action=clean就会覆盖配置文件中的选项，在初始化的过程中强制抹除已经存在的数据库实例（危险）。

配置项（变量）

Pigsty带有很多配置项变量，分为10个部分

详细列表如下：

#------------------------------------------------------------------------------
# CONNECTION PARAMETERS
#------------------------------------------------------------------------------
proxy_env
#------------------------------------------------------------------------------
# REPO PROVISION
#------------------------------------------------------------------------------
repo_enabled
repo_name
repo_address
repo_port
repo_home
repo_rebuild
repo_remove
repo_upstreams
repo_packages
repo_url_packages
#------------------------------------------------------------------------------
# NODE PROVISION
#------------------------------------------------------------------------------
node_dns_hosts
node_dns_server
node_dns_servers
node_dns_options
node_repo_method
node_repo_remove
node_local_repo_url
node_packages
node_extra_packages
node_meta_packages
node_disable_numa
node_disable_swap
node_disable_firewall
node_disable_selinux
node_static_network
node_disk_prefetch
node_kernel_modules
node_tune
node_sysctl_params
node_admin_setup
node_admin_uid
node_admin_username
node_admin_ssh_exchange
node_admin_pks
node_ntp_service
node_ntp_config
node_timezone
node_ntp_servers
#------------------------------------------------------------------------------
# META PROVISION
#------------------------------------------------------------------------------
ca_method
ca_subject
ca_homedir
ca_cert
ca_key
nginx_upstream
dns_records
prometheus_scrape_interval
prometheus_scrape_timeout
prometheus_metrics_path
prometheus_data_dir
prometheus_retention
grafana_url
grafana_admin_password
grafana_plugin
grafana_cache
grafana_customize
grafana_plugins
grafana_git_plugins
#------------------------------------------------------------------------------
# DCS PROVISION
#------------------------------------------------------------------------------
dcs_type
dcs_name
dcs_servers
dcs_exists_action
consul_data_dir
etcd_data_dir
#------------------------------------------------------------------------------
# POSTGRES INSTALLATION
#------------------------------------------------------------------------------
pg_dbsu
pg_dbsu_uid
pg_dbsu_sudo
pg_dbsu_home
pg_dbsu_ssh_exchange
pg_version
pgdg_repo
pg_add_repo
pg_bin_dir
pg_packages
pg_extensions
#------------------------------------------------------------------------------
# POSTGRES PROVISION
#------------------------------------------------------------------------------
pg_cluster         
pg_seq           
pg_role    
pg_hostname
pg_nodename
pg_exists
pg_exists_action
pg_data
pg_fs_main
pg_fs_bkup
pg_listen
pg_port
patroni_mode
pg_namespace
patroni_port
patroni_watchdog_mode
pg_conf
pgbouncer_port
pgbouncer_poolmode
pgbouncer_max_db_conn
#------------------------------------------------------------------------------
# POSTGRES TEMPLATE
#------------------------------------------------------------------------------
pg_init
pg_replication_username
pg_replication_password
pg_monitor_username
pg_monitor_password
pg_admin_username
pg_admin_password
pg_default_roles
pg_default_privilegs
pg_default_schemas
pg_default_extensions
pg_hba_rules
pg_hba_rules_extra
pgbouncer_hba_rules
pgbouncer_hba_rules_extra
#------------------------------------------------------------------------------
# MONITOR PROVISION
#------------------------------------------------------------------------------
pg_exporter_config
node_exporter_port
pg_exporter_port
pgbouncer_exporter_port
exporter_metrics_path
pg_localhost
#------------------------------------------------------------------------------
# PROXY PROVISION
#------------------------------------------------------------------------------
haproxy_enabled
haproxy_policy
haproxy_admin_username
haproxy_admin_password
haproxy_client_timeout
haproxy_server_timeout
haproxy_exporter_port
haproxy_check_port
haproxy_primary_port
haproxy_replica_port
haproxy_backend_port
vip_enabled
vip_address
vip_cidrmask
vip_interface

2 - 连接参数

Pigsty中与连接、代理有关的参数

参数概览

#------------------------------------------------------------------------------
# CONNECTION PARAMETERS
#------------------------------------------------------------------------------
proxy_env
ansible_host

参数详解

proxy_env

在某些受到“互联网封锁”的地区，有些软件的下载会受到影响。

例如，从中国大陆访问PostgreSQL的官方源，下载速度可能只有几KB每秒。但如果使用了合适的HTTP代理，则可以达到几MB每秒。

因此，如果您有代理服务器，请通过proxy_env进行配置，样例如下：

proxy_env: # global proxy env when downloading packages
  http_proxy: 'http://username:password@proxy.address.com'
  https_proxy: 'http://username:password@proxy.address.com'
  all_proxy: 'http://username:password@proxy.address.com'
  no_proxy: "localhost,127.0.0.1,10.0.0.0/8,192.168.0.0/16,*.pigsty,*.aliyun.com"

ansible_host

如果您的环境使用了跳板机，或者进行了某些定制化修改，无法通过简单的ssh <ip>方式访问，那么可以考虑使用Ansible的连接参数。

Ansible中关于SSH连接的参数

ansible_host

The name of the host to connect to, if different from the alias you wish to give to it.

ansible_port

The ssh port number, if not 22

ansible_user

The default ssh user name to use.

ansible_ssh_pass

The ssh password to use (never store this variable in plain text; always use a vault. See Variables and Vaults)

ansible_ssh_private_key_file

Private key file used by ssh. Useful if using multiple keys and you don’t want to use SSH agent.

ansible_ssh_common_args

This setting is always appended to the default command line for sftp, scp, and ssh. Useful to configure a ProxyCommand for a certain host (or group).

ansible_sftp_extra_args

This setting is always appended to the default sftp command line.

ansible_scp_extra_args

This setting is always appended to the default scp command line.

ansible_ssh_extra_args

This setting is always appended to the default ssh command line.

ansible_ssh_pipelining

Determines whether or not to use SSH pipelining. This can override the pipelining setting in ansible.cfg.

最简单的用法是将ssh alias配置为ansible_host，只要您可以通过ssh <name>的方式访问目标机器，那么将ansible_host配置为<name>即可。注意这些变量都是实例级别的变量。

3 - 本地源参数

Pigsty中关于本地Yum源的配置项

Pigsty是一个复杂的软件系统，为了确保系统的稳定，Pigsty会在初始化过程中从互联网下载所有依赖的软件包并建立本地Yum源。

所有依赖的软件总大小约1GB左右，下载速度取决于您的网络情况。尽管Pigsty已经尽量使用镜像源以加速下载，但少量包的下载仍可能受到防火墙的阻挠，可能出现非常慢的情况。您可以通过proxy_env配置项设置下载代理以完成首次下载，或直接下载预先打包好的离线安装包。

建立本地Yum源时，如果{{ repo_home }}/{{ repo_name }}目录已经存在，而且里面有repo_complete的标记文件，Pigsty会认为本地Yum源已经初始化完毕，因此跳过软件下载阶段，显著加快速度。离线安装包即是把{{ repo_home }}/{{ repo_name }}目录整个打成压缩包。

参数概览

repo_enabled: true                            # 是否启用本地源功能
repo_name: pigsty                             # 本地源名称
repo_address: yum.pigsty                      # 外部可访问的源地址 (ip:port 或 url)
repo_port: 80                                 # 源HTTP服务器监听地址
repo_home: /www                               # 默认根目录
repo_rebuild: false                           # 强制重新下载软件包
repo_remove: true                             # 移除已有的yum源
repo_upstreams: [...]                         # 上游Yum源
repo_packages: [...]                          # 需要下载的软件包
repo_url_packages: [...]                      # 通过URL下载的软件

https://github.com/Vonng/pigsty/releases/download/v0.5.0/pkg.tgz

将该软件包拷贝至项目根目录的files/pkg.tgz，然后执行make upload，即可将离线软件包上传至元节点的目标位置。

make upload

为了快速拉起Pigsty，建议使用离线下载软件包并上传的方式完成安装。默认的离线软件包基于CentOS 7.8，用于生产环境时，我们强烈建议您依据生产环境的实际情况完成一次完整的网络下载，并通过make cache缓存离线安装包。

执行初始化

完成上述操作后，执行make init即会调用ansible完成Pigsty系统的初始化。

$ make init
./infra.yml                 # provision infrastructures
./initdb.yml                # provision database clusteres

参数详解

repo_enabled

如果为true（默认情况），执行正常的本地yum源创建流程，否则跳过构建本地yum源的操作。

repo_name

本地yum源的名称，默认为pigsty，您可以改为自己喜欢的名称，例如pgsql-rhel7等。

repo_address

本地yum源对外提供服务的地址，可以是域名也可以是IP地址，默认为yum.pigsty。

如果使用域名，您必须确保在当前环境中该域名会解析到本地源所在的服务器，也就是元节点。

如果您的本地yum源没有使用标准的80端口，您需要在地址中加入端口，并与repo_port变量保持一致。

您可以通过节点参数中的静态DNS配置来为环境中的所有节点写入Pigsty本地源的域名，沙箱环境中即是采用这种方式来解析默认的yum.pigsty域名。

repo_port

本地yum源使用的HTTP端口，默认为80端口。

repo_home

本地yum源的根目录，默认为www。

该目录将作为HTTP服务器的根对外暴露。

repo_rebuild

如果为false（默认情况），什么都不发生，如果为true，那么在任何情况下都会执行Repo重建的工作。

repo_remove

在执行本地源初始化的过程中，是否移除/etc/yum.repos.d中所有已有的repo？默认为true。

原有repo文件会备份至/etc/yum.repos.d/backup中。

因为操作系统已有的源内容不可控，建议强制移除并通过repo_upstreams进行显式配置。

repo_upstream

所有添加到/etc/yum.repos.d中的Yum源，Pigsty将从这些源中下载软件。

Pigsty默认使用阿里云的CentOS7镜像源，清华大学Grafana镜像源，PackageCloud的Prometheus源，PostgreSQL官方源，以及SCLo，Harbottle，Nginx, Haproxy等软件源。

- name: base
  description: CentOS-$releasever - Base - Aliyun Mirror
  baseurl:
    - http://mirrors.aliyun.com/centos/$releasever/os/$basearch/
    - http://mirrors.aliyuncs.com/centos/$releasever/os/$basearch/
    - http://mirrors.cloud.aliyuncs.com/centos/$releasever/os/$basearch/
  gpgcheck: no
  failovermethod: priority

- name: updates
  description: CentOS-$releasever - Updates - Aliyun Mirror
  baseurl:
    - http://mirrors.aliyun.com/centos/$releasever/updates/$basearch/
    - http://mirrors.aliyuncs.com/centos/$releasever/updates/$basearch/
    - http://mirrors.cloud.aliyuncs.com/centos/$releasever/updates/$basearch/
  gpgcheck: no
  failovermethod: priority

- name: extras
  description: CentOS-$releasever - Extras - Aliyun Mirror
  baseurl:
    - http://mirrors.aliyun.com/centos/$releasever/extras/$basearch/
    - http://mirrors.aliyuncs.com/centos/$releasever/extras/$basearch/
    - http://mirrors.cloud.aliyuncs.com/centos/$releasever/extras/$basearch/
  gpgcheck: no
  failovermethod: priority

- name: epel
  description: CentOS $releasever - EPEL - Aliyun Mirror
  baseurl: http://mirrors.aliyun.com/epel/$releasever/$basearch
  gpgcheck: no
  failovermethod: priority

- name: grafana
  description: Grafana - TsingHua Mirror
  gpgcheck: no
  baseurl: https://mirrors.tuna.tsinghua.edu.cn/grafana/yum/rpm

- name: prometheus
  description: Prometheus and exporters
  gpgcheck: no
  baseurl: https://packagecloud.io/prometheus-rpm/release/el/$releasever/$basearch

- name: pgdg-common
  description: PostgreSQL common RPMs for RHEL/CentOS $releasever - $basearch
  gpgcheck: no
  baseurl: https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-$releasever-$basearch

- name: pgdg13
  description: PostgreSQL 13 for RHEL/CentOS $releasever - $basearch - Updates testing
  gpgcheck: no
  baseurl: https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-$releasever-$basearch

- name: centos-sclo
  description: CentOS-$releasever - SCLo
  gpgcheck: no
  mirrorlist: http://mirrorlist.centos.org?arch=$basearch&release=7&repo=sclo-sclo

- name: centos-sclo-rh
  description: CentOS-$releasever - SCLo rh
  gpgcheck: no
  mirrorlist: http://mirrorlist.centos.org?arch=$basearch&release=7&repo=sclo-rh

- name: nginx
  description: Nginx Official Yum Repo
  skip_if_unavailable: true
  gpgcheck: no
  baseurl: http://nginx.org/packages/centos/$releasever/$basearch/

- name: haproxy
  description: Copr repo for haproxy
  skip_if_unavailable: true
  gpgcheck: no
  baseurl: https://download.copr.fedorainfracloud.org/results/roidelapluie/haproxy/epel-$releasever-$basearch/

# for latest consul & kubernetes
- name: harbottle
  description: Copr repo for main owned by harbottle
  skip_if_unavailable: true
  gpgcheck: no
  baseurl: https://download.copr.fedorainfracloud.org/results/harbottle/main/epel-$releasever-$basearch/

repo_packages

需要下载的rpm安装包列表，默认下载的软件包如下所示：

# - what to download - #
repo_packages:
  # repo bootstrap packages
  - epel-release nginx wget yum-utils yum createrepo                                      # bootstrap packages

  # node basic packages
  - ntp chrony uuid lz4 nc pv jq vim-enhanced make patch bash lsof wget unzip git tuned   # basic system util
  - readline zlib openssl libyaml libxml2 libxslt perl-ExtUtils-Embed ca-certificates     # basic pg dependency
  - numactl grubby sysstat dstat iotop bind-utils net-tools tcpdump socat ipvsadm telnet  # system utils

  # dcs & monitor packages
  - grafana prometheus2 pushgateway alertmanager                                          # monitor and ui
  - node_exporter postgres_exporter nginx_exporter blackbox_exporter                      # exporter
  - consul consul_exporter consul-template etcd                                           # dcs

  # python3 dependencies
  - ansible python python-pip python-psycopg2                                             # ansible & python
  - python3 python3-psycopg2 python36-requests python3-etcd python3-consul                # python3
  - python36-urllib3 python36-idna python36-pyOpenSSL python36-cryptography               # python3 patroni extra deps

  # proxy and load balancer
  - haproxy keepalived dnsmasq                                                            # proxy and dns

  # postgres common Packages
  - patroni patroni-consul patroni-etcd pgbouncer pg_cli pgbadger pg_activity               # major components
  - pgcenter boxinfo check_postgres emaj pgbconsole pg_bloat_check pgquarrel                # other common utils
  - barman barman-cli pgloader pgFormatter pitrery pspg pgxnclient PyGreSQL pgadmin4 tail_n_mail

  # postgres 13 packages
  - postgresql13* postgis31* citus_13 pgrouting_13                                          # postgres 13 and postgis 31
  - pg_repack13 pg_squeeze13                                                                # maintenance extensions
  - pg_qualstats13 pg_stat_kcache13 system_stats_13 bgw_replstatus13                        # stats extensions
  - plr13 plsh13 plpgsql_check_13 plproxy13 plr13 plsh13 plpgsql_check_13 pldebugger13      # PL extensions                                      # pl extensions
  - hdfs_fdw_13 mongo_fdw13 mysql_fdw_13 ogr_fdw13 redis_fdw_13 pgbouncer_fdw13             # FDW extensions
  - wal2json13 count_distinct13 ddlx_13 geoip13 orafce13                                    # MISC extensions
  - rum_13 hypopg_13 ip4r13 jsquery_13 logerrors_13 periods_13 pg_auto_failover_13 pg_catcheck13
  - pg_fkpart13 pg_jobmon13 pg_partman13 pg_prioritize_13 pg_track_settings13 pgaudit15_13
  - pgcryptokey13 pgexportdoc13 pgimportdoc13 pgmemcache-13 pgmp13 pgq-13
  - pguint13 pguri13 prefix13  safeupdate_13 semver13  table_version13 tdigest13

repo_url_packages

采用URL直接下载，而非yum下载的软件包。您可以将自定义的软件包连接添加到这里。

Pigsty默认会通过URL下载三款软件：

pg_exporter（必须，监控系统核心组件）
vip-manager（可选，启用VIP时必须）
polysh（可选，多机管理便捷工具）

repo_url_packages:
  - https://github.com/Vonng/pg_exporter/releases/download/v0.3.1/pg_exporter-0.3.1-1.el7.x86_64.rpm
  - https://github.com/cybertec-postgresql/vip-manager/releases/download/v0.6/vip-manager_0.6-1_amd64.rpm
  - http://guichaz.free.fr/polysh/files/polysh-0.4-1.noarch.rpm

4 - 节点参数

Pigsty中关于机器与操作系统、基础设施的配置参数

参数概览

#------------------------------------------------------------------------------
# NODE PROVISION
#------------------------------------------------------------------------------
node_dns_hosts
node_dns_server
node_dns_servers
node_dns_options
node_repo_method
node_repo_remove
node_local_repo_url
node_packages
node_extra_packages
node_meta_packages
node_disable_numa
node_disable_swap
node_disable_firewall
node_disable_selinux
node_static_network
node_disk_prefetch
node_kernel_modules
node_tune
node_sysctl_params
node_admin_setup
node_admin_uid
node_admin_username
node_admin_ssh_exchange
node_admin_pks
node_ntp_service
node_ntp_config
node_timezone
node_ntp_servers

默认配置

#------------------------------------------------------------------------------
# NODE PROVISION
#------------------------------------------------------------------------------
# this section defines how to provision nodes

# - node dns - #
node_dns_hosts: # static dns records in /etc/hosts
  - 10.10.10.10 yum.pigsty
node_dns_server: add                          # add (default) | none (skip) | overwrite (remove old settings)
node_dns_servers: # dynamic nameserver in /etc/resolv.conf
  - 10.10.10.10
node_dns_options: # dns resolv options
  - options single-request-reopen timeout:1 rotate
  - domain service.consul

# - node repo - #
node_repo_method: local                       # none|local|public (use local repo for production env)
node_repo_remove: true                        # whether remove existing repo
# local repo url (if method=local, make sure firewall is configured or disabled)
node_local_repo_url:
  - http://yum.pigsty/pigsty.repo

# - node packages - #
node_packages: # common packages for all nodes
  - wget,yum-utils,ntp,chrony,tuned,uuid,lz4,vim-minimal,make,patch,bash,lsof,wget,unzip,git,readline,zlib,openssl
  - numactl,grubby,sysstat,dstat,iotop,bind-utils,net-tools,tcpdump,socat,ipvsadm,telnet,tuned,pv,jq
  - python3,python3-psycopg2,python36-requests,python3-etcd,python3-consul
  - python36-urllib3,python36-idna,python36-pyOpenSSL,python36-cryptography
  - node_exporter,consul,consul-template,etcd,haproxy,keepalived,vip-manager
node_extra_packages: # extra packages for all nodes
  - patroni,patroni-consul,patroni-etcd,pgbouncer,pgbadger,pg_activity
node_meta_packages: # packages for meta nodes only
  - grafana,prometheus2,alertmanager,nginx_exporter,blackbox_exporter,pushgateway
  - dnsmasq,nginx,ansible,pgbadger,polysh

# - node features - #
node_disable_numa: false                      # disable numa, important for production database, reboot required
node_disable_swap: false                      # disable swap, important for production database
node_disable_firewall: true                   # disable firewall (required if using kubernetes)
node_disable_selinux: true                    # disable selinux  (required if using kubernetes)
node_static_network: true                     # keep dns resolver settings after reboot
node_disk_prefetch: false                     # setup disk prefetch on HDD to increase performance

# - node kernel modules - #
node_kernel_modules:
  - softdog
  - br_netfilter
  - ip_vs
  - ip_vs_rr
  - ip_vs_rr
  - ip_vs_wrr
  - ip_vs_sh
  - nf_conntrack_ipv4

# - node tuned - #
node_tune: tiny                               # install and activate tuned profile: none|oltp|olap|crit|tiny
node_sysctl_params: # set additional sysctl parameters, k:v format
  net.bridge.bridge-nf-call-iptables: 1       # for kubernetes

# - node user - #
node_admin_setup: true                        # setup an default admin user ?
node_admin_uid: 88                            # uid and gid for admin user
node_admin_username: admin                    # default admin user
node_admin_ssh_exchange: true                 # exchange ssh key among cluster ?
node_admin_pks: # public key list that will be installed
  - 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAAAgQC7IMAMNavYtWwzAJajKqwdn3ar5BhvcwCnBTxxEkXhGlCO2vfgosSAQMEflfgvkiI5nM1HIFQ8KINlx1XLO7SdL5KdInG5LIJjAFh0pujS4kNCT9a5IGvSq1BrzGqhbEcwWYdju1ZPYBcJm/MG+JD0dYCh8vfrYB/cYMD0SOmNkQ== vagrant@pigsty.com'

# - node ntp - #
node_ntp_service: ntp                         # ntp or chrony
node_ntp_config: true                         # overwrite existing ntp config?
node_timezone: Asia/Shanghai                  # default node timezone
node_ntp_servers: # default NTP servers
  - pool cn.pool.ntp.org iburst
  - pool pool.ntp.org iburst
  - pool time.pool.aliyun.com iburst
  - server 10.10.10.10 iburst

参数详解

5 - 元节点参数

Pigsty中关于元节点的相关配置参数：CA，DNS，Nginx，Prometheus，Grafana

参数概览

#------------------------------------------------------------------------------
# META PROVISION
#------------------------------------------------------------------------------
ca_method
ca_subject
ca_homedir
ca_cert
ca_key
nginx_upstream
dns_records
prometheus_scrape_interval
prometheus_scrape_timeout
prometheus_metrics_path
prometheus_data_dir
prometheus_retention
grafana_url
grafana_admin_password
grafana_plugin
grafana_cache
grafana_customize
grafana_plugins
grafana_git_plugins

默认参数

#------------------------------------------------------------------------------
# META PROVISION
#------------------------------------------------------------------------------
# - ca - #
ca_method: create                             # create|copy|recreate
ca_subject: "/CN=root-ca"                     # self-signed CA subject
ca_homedir: /ca                               # ca cert directory
ca_cert: ca.crt                               # ca public key/cert
ca_key: ca.key                                # ca private key

# - nginx - #
nginx_upstream:
  - {name: home,           host: pigsty,   url: "127.0.0.1:3000"}
  - { name: consul,        host: c.pigsty, url: "127.0.0.1:8500" }
  - { name: grafana,       host: g.pigsty, url: "127.0.0.1:3000" }
  - { name: prometheus,    host: p.pigsty, url: "127.0.0.1:9090" }
  - { name: alertmanager,  host: a.pigsty, url: "127.0.0.1:9093" }

# - nameserver - #
dns_records: # dynamic dns record resolved by dnsmasq
  - 10.10.10.2  pg-meta                       # sandbox vip for pg-meta
  - 10.10.10.3  pg-test                       # sandbox vip for pg-test
  - 10.10.10.10 meta-1                        # sandbox node meta-1 (node-0)
  - 10.10.10.11 node-1                        # sandbox node node-1
  - 10.10.10.12 node-2                        # sandbox node node-2
  - 10.10.10.13 node-3                        # sandbox node node-3
  - 10.10.10.10 pigsty
  - 10.10.10.10 y.pigsty yum.pigsty
  - 10.10.10.10 c.pigsty consul.pigsty
  - 10.10.10.10 g.pigsty grafana.pigsty
  - 10.10.10.10 p.pigsty prometheus.pigsty
  - 10.10.10.10 a.pigsty alertmanager.pigsty
  - 10.10.10.10 n.pigsty ntp.pigsty

# - prometheus - #
prometheus_scrape_interval: 2s                # global scrape & evaluation interval (2s for dev, 15s for prod)
prometheus_scrape_timeout: 1s                 # global scrape timeout (1s for dev, 1s for prod)
prometheus_metrics_path: /metrics             # default metrics path (only affect job 'pg')
prometheus_data_dir: /export/prometheus/data  # prometheus data dir
prometheus_retention: 30d                     # how long to keep

# - grafana - #
grafana_url: http://admin:admin@10.10.10.10:3000 # grafana url
grafana_admin_password: admin                  # default grafana admin user password
grafana_plugin: install                        # none|install|reinstall
grafana_cache: /www/pigsty/grafana/plugins.tar.gz # path to grafana plugins tarball
grafana_customize: true                        # customize grafana resources
grafana_plugins: # default grafana plugins list
  - redis-datasource
  - simpod-json-datasource
  - fifemon-graphql-datasource
  - sbueringer-consul-datasource
  - camptocamp-prometheus-alertmanager-datasource
  - ryantxu-ajax-panel
  - marcusolsson-hourly-heatmap-panel
  - michaeldmoore-multistat-panel
  - marcusolsson-treemap-panel
  - pr0ps-trackmap-panel
  - dalvany-image-panel
  - magnesium-wordcloud-panel
  - cloudspout-button-panel
  - speakyourcode-button-panel
  - jdbranham-diagram-panel
  - grafana-piechart-panel
  - snuids-radar-panel
  - digrich-bubblechart-panel
grafana_git_plugins:
  - https://github.com/Vonng/grafana-echarts

参数详解

6 - 元数据库参数

Pigsty中关于元数据库（Consul/Etcd）的配置参数

参数概览

#------------------------------------------------------------------------------
# DCS PROVISION
#------------------------------------------------------------------------------
dcs_type
dcs_name
dcs_servers
dcs_exists_action
consul_data_dir
etcd_data_dir

默认参数

#------------------------------------------------------------------------------
# DCS PROVISION
#------------------------------------------------------------------------------
dcs_type: consul                              # consul | etcd | both
dcs_name: pigsty                              # consul dc name | etcd initial cluster token
# dcs server dict in name:ip format
dcs_servers:
  meta-1: 10.10.10.10                         # you could use existing dcs cluster
  # meta-2: 10.10.10.11                       # host which have their IP listed here will be init as server
  # meta-3: 10.10.10.12                       # 3 or 5 dcs nodes are recommend for production environment

dcs_exists_action: skip                       # abort|skip|clean if dcs server already exists
consul_data_dir: /var/lib/consul              # consul data dir (/var/lib/consul by default)
etcd_data_dir: /var/lib/etcd                  # etcd data dir (/var/lib/consul by default)

参数详解

7 - PG安装参数

Pigsty中关于Postgres安装的相关参数

参数概览

#------------------------------------------------------------------------------
# POSTGRES INSTALLATION
#------------------------------------------------------------------------------
pg_dbsu
pg_dbsu_uid
pg_dbsu_sudo
pg_dbsu_home
pg_dbsu_ssh_exchange
pg_version
pgdg_repo
pg_add_repo
pg_bin_dir
pg_packages
pg_extensions

默认参数

#------------------------------------------------------------------------------
# POSTGRES INSTALLATION
#------------------------------------------------------------------------------
# - dbsu - #
pg_dbsu: postgres                             # os user for database, postgres by default (change it is not recommended!)
pg_dbsu_uid: 26                               # os dbsu uid and gid, 26 for default postgres users and groups
pg_dbsu_sudo: limit                           # none|limit|all|nopass (Privilege for dbsu, limit is recommended)
pg_dbsu_home: /var/lib/pgsql                  # postgresql binary
pg_dbsu_ssh_exchange: false                   # exchange ssh key among same cluster

# - postgres packages - #
pg_version: 13                                # default postgresql version
pgdg_repo: false                              # use official pgdg yum repo (disable if you have local mirror)
pg_add_repo: false                            # add postgres related repo before install (useful if you want a simple install)
pg_bin_dir: /usr/pgsql/bin                    # postgres binary dir
pg_packages:
  - postgresql${pg_version}*
  - postgis31_${pg_version}*
  - pgbouncer patroni pg_exporter pgbadger
  - patroni patroni-consul patroni-etcd pgbouncer pgbadger pg_activity
  - python3 python3-psycopg2 python36-requests python3-etcd python3-consul
  - python36-urllib3 python36-idna python36-pyOpenSSL python36-cryptography

pg_extensions:
  - pg_repack${pg_version} pg_qualstats${pg_version} pg_stat_kcache${pg_version} wal2json${pg_version}
  # - ogr_fdw${pg_version} mysql_fdw_${pg_version} redis_fdw_${pg_version} mongo_fdw${pg_version} hdfs_fdw_${pg_version}
  # - count_distinct${version}  ddlx_${version}  geoip${version}  orafce${version}                                   # popular features
  # - hypopg_${version}  ip4r${version}  jsquery_${version}  logerrors_${version}  periods_${version}  pg_auto_failover_${version}  pg_catcheck${version}
  # - pg_fkpart${version}  pg_jobmon${version}  pg_partman${version}  pg_prioritize_${version}  pg_track_settings${version}  pgaudit15_${version}
  # - pgcryptokey${version}  pgexportdoc${version}  pgimportdoc${version}  pgmemcache-${version}  pgmp${version}  pgq-${version}  pgquarrel pgrouting_${version}
  # - pguint${version}  pguri${version}  prefix${version}   safeupdate_${version}  semver${version}   table_version${version}  tdigest${version}

参数详解

8 - PG供给参数

Pigsty中关于如何拉起一套数据库集群的定义参数

参数概览

#------------------------------------------------------------------------------
# POSTGRES PROVISION
#------------------------------------------------------------------------------
pg_cluster         
pg_seq           
pg_role    
pg_hostname
pg_nodename
pg_exists
pg_exists_action
pg_data
pg_fs_main
pg_fs_bkup
pg_listen
pg_port
patroni_mode
pg_namespace
patroni_port
patroni_watchdog_mode
pg_conf
pgbouncer_port
pgbouncer_poolmode
pgbouncer_max_db_conn

默认参数

#------------------------------------------------------------------------------
# POSTGRES PROVISION
#------------------------------------------------------------------------------
# - identity - #
# pg_cluster:                                 # [REQUIRED] cluster name (validated during pg_preflight)
# pg_seq: 0                                   # [REQUIRED] instance seq (validated during pg_preflight)
# pg_role: replica                            # [REQUIRED] service role (validated during pg_preflight)
pg_hostname: false                            # overwrite node hostname with pg instance name
pg_nodename: true                             # overwrite consul nodename with pg instance name

# - retention - #
# pg_exists_action, available options: abort|clean|skip
#  - abort: abort entire play's execution (default)
#  - clean: remove existing cluster (dangerous)
#  - skip: end current play for this host
# pg_exists: false                            # auxiliary flag variable (DO NOT SET THIS)
pg_exists_action: clean

# - storage - #
pg_data: /pg/data                             # postgres data directory
pg_fs_main: /export                           # data disk mount point     /pg -> {{ pg_fs_main }}/postgres/{{ pg_instance }}
pg_fs_bkup: /var/backups                      # backup disk mount point   /pg/* -> {{ pg_fs_bkup }}/postgres/{{ pg_instance }}/*

# - connection - #
pg_listen: '0.0.0.0'                          # postgres listen address, '0.0.0.0' by default (all ipv4 addr)
pg_port: 5432                                 # postgres port (5432 by default)

# - patroni - #
# patroni_mode, available options: default|pause|remove
#   - default: default ha mode
#   - pause:   into maintenance mode
#   - remove:  remove patroni after bootstrap
patroni_mode: default                         # pause|default|remove
pg_namespace: /pg                             # top level key namespace in dcs
patroni_port: 8008                            # default patroni port
patroni_watchdog_mode: automatic              # watchdog mode: off|automatic|required
pg_conf: tiny.yml                             # user provided patroni config template path

# - pgbouncer - #
pgbouncer_port: 6432                          # default pgbouncer port
pgbouncer_poolmode: transaction               # default pooling mode: transaction pooling
pgbouncer_max_db_conn: 100                    # important! do not set this larger than postgres max conn or conn limit

参数详解

9 - PG模板

Pigsty中关于定制Postgres的相关参数

在Pigsty中提供两种定制化的方式

数据库初始化模板

初始化模板是用于初始化数据库集群的定义文件，默认位于roles/postgres/templates/patroni.yml，采用patroni.yml 配置文件格式在templates/目录中，有四种预定义好的初始化模板：

oltp.yml 常规OLTP模板，默认配置
olap.yml OLAP模板，提高并行度，针对吞吐量优化，针对长时间运行的查询进行优化。
crit.yml 核心业务模板，基于OLTP模板针对安全性，数据完整性进行优化，采用同步复制，启用数据校验和。
tiny.yml 微型数据库模板，针对低资源场景进行优化，例如运行于虚拟机中的演示数据库集群。

用户也可以基于上述模板进行定制与修改，并通过pg_conf参数使用相应的模板。

数据库初始化脚本

当数据库初始化完毕后，用户通常希望对数据库进行自定义的定制脚本，例如创建统一的默认角色，用户，创建默认的模式，配置默认权限等。

参数概览

#------------------------------------------------------------------------------
# POSTGRES TEMPLATE
#------------------------------------------------------------------------------
pg_init
pg_replication_username
pg_replication_password
pg_monitor_username
pg_monitor_password
pg_admin_username
pg_admin_password
pg_default_roles
pg_default_privilegs
pg_default_schemas
pg_default_extensions
pg_hba_rules
pg_hba_rules_extra
pgbouncer_hba_rules
pgbouncer_hba_rules_extra

默认参数

#------------------------------------------------------------------------------
# POSTGRES TEMPLATE
#------------------------------------------------------------------------------
# - template - #
pg_init: pg-init                              # init script for cluster template

# - system roles - #
pg_replication_username: replicator           # system replication user
pg_replication_password: DBUser.Replicator    # system replication password
pg_monitor_username: dbuser_monitor           # system monitor user
pg_monitor_password: DBUser.Monitor           # system monitor password
pg_admin_username: dbuser_admin               # system admin user
pg_admin_password: DBUser.Admin               # system admin password

# - default roles - #
pg_default_roles:
  - username: dbrole_readonly                 # sample user:
    options: NOLOGIN                          # role can not login
    comment: role for readonly access         # comment string

  - username: dbrole_readwrite                # sample user: one object for each user
    options: NOLOGIN
    comment: role for read-write access
    groups: [ dbrole_readonly ]               # read-write includes read-only access

  - username: dbrole_admin                    # sample user: one object for each user
    options: NOLOGIN BYPASSRLS                # admin can bypass row level security
    comment: role for object creation
    groups: [dbrole_readwrite,pg_monitor,pg_signal_backend]

  # NOTE: replicator, monitor, admin password are overwritten by separated config entry
  - username: postgres                        # reset dbsu password to NULL (if dbsu is not postgres)
    options: SUPERUSER LOGIN
    comment: system superuser

  - username: replicator
    options: REPLICATION LOGIN
    groups: [pg_monitor, dbrole_readonly]
    comment: system replicator

  - username: dbuser_monitor
    options: LOGIN CONNECTION LIMIT 10
    comment: system monitor user
    groups: [pg_monitor, dbrole_readonly]

  - username: dbuser_admin
    options: LOGIN BYPASSRLS
    comment: system admin user
    groups: [dbrole_admin]

  - username: dbuser_stats
    password: DBUser.Stats
    options: LOGIN
    comment: business read-only user for statistics
    groups: [dbrole_readonly]


# object created by dbsu and admin will have their privileges properly set
pg_default_privilegs:
  - 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 INSERT, UPDATE, DELETE        ON TABLES    TO dbrole_readwrite
  - GRANT USAGE,  UPDATE                ON SEQUENCES TO dbrole_readwrite
  - GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES    TO dbrole_admin
  - GRANT CREATE                        ON SCHEMAS   TO dbrole_admin
  - GRANT USAGE                         ON TYPES     TO dbrole_admin

# schemas
pg_default_schemas: [monitor]

# extension
pg_default_extensions:
  - { name: 'pg_stat_statements',  schema: 'monitor' }
  - { name: 'pgstattuple',         schema: 'monitor' }
  - { name: 'pg_qualstats',        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: 'pg_repack',           schema: 'monitor' }
  - name: postgres_fdw
  - name: file_fdw
  - name: btree_gist
  - name: btree_gin
  - name: pg_trgm
  - name: intagg
  - name: intarray

# postgres host-based authentication rules
pg_hba_rules:
  - title: allow meta node password access
    role: common
    rules:
      - host    all     all                         10.10.10.10/32      md5

  - title: allow intranet admin password access
    role: common
    rules:
      - host    all     +dbrole_admin               10.0.0.0/8          md5
      - host    all     +dbrole_admin               172.16.0.0/12       md5
      - host    all     +dbrole_admin               192.168.0.0/16      md5

  - title: allow intranet password access
    role: common
    rules:
      - host    all             all                 10.0.0.0/8          md5
      - host    all             all                 172.16.0.0/12       md5
      - host    all             all                 192.168.0.0/16      md5

  - title: allow local read-write access (local production user via pgbouncer)
    role: common
    rules:
      - local   all     +dbrole_readwrite                               md5
      - host    all     +dbrole_readwrite           127.0.0.1/32        md5

  - title: allow read-only user (stats, personal) password directly access
    role: replica
    rules:
      - local   all     +dbrole_readonly                               md5
      - host    all     +dbrole_readonly           127.0.0.1/32        md5
pg_hba_rules_extra: []

# pgbouncer host-based authentication rules
pgbouncer_hba_rules:
  - title: local password access
    role: common
    rules:
      - local  all          all                                     md5
      - host   all          all                     127.0.0.1/32    md5

  - title: intranet password access
    role: common
    rules:
      - host   all          all                     10.0.0.0/8      md5
      - host   all          all                     172.16.0.0/12   md5
      - host   all          all                     192.168.0.0/16  md5
pgbouncer_hba_rules_extra: []

参数详解

10 - 监控系统参数

Pigsty中与监控系统有关的参数

参数概览

#------------------------------------------------------------------------------
# MONITOR PROVISION
#------------------------------------------------------------------------------
pg_exporter_config
node_exporter_port
pg_exporter_port
pgbouncer_exporter_port
exporter_metrics_path
pg_localhost

默认参数

#------------------------------------------------------------------------------
# MONITOR PROVISION
#------------------------------------------------------------------------------
# - monitor options -
pg_exporter_config: pg_exporter-demo.yaml     # default config files for pg_exporter
node_exporter_port: 9100                      # default port for node exporter
pg_exporter_port: 9630                        # default port for pg exporter
pgbouncer_exporter_port: 9631                 # default port for pgbouncer exporter
exporter_metrics_path: /metrics               # default metric path for pg related exporter
pg_localhost: /var/run/postgresql             # localhost unix socket path

参数详解

11 - 负载均衡参数

Pigsty中关于流量代理与负载均衡相关的参数

参数概览

#------------------------------------------------------------------------------
# PROXY PROVISION
#------------------------------------------------------------------------------
haproxy_enabled
haproxy_policy
haproxy_admin_auth_enabled
haproxy_admin_username
haproxy_admin_password
haproxy_client_timeout
haproxy_server_timeout
haproxy_exporter_port
haproxy_check_port
haproxy_primary_port
haproxy_replica_port
haproxy_backend_port
haproxy_weight
haproxy_weight_fallback

vip_enabled
vip_address
vip_cidrmask
vip_interface

默认参数

#------------------------------------------------------------------------------
# PROXY PROVISION
#------------------------------------------------------------------------------
# - haproxy - #
haproxy_enabled: true                         # enable haproxy among every cluster members
haproxy_policy: roundrobin                    # roundrobin, leastconn
haproxy_admin_auth_enabled: false             # enable authentication for haproxy admin?
haproxy_admin_username: admin                 # default haproxy admin username
haproxy_admin_password: admin                 # default haproxy admin password
haproxy_client_timeout: 3h                    # client side connection timeout
haproxy_server_timeout: 3h                    # server side connection timeout
haproxy_exporter_port: 9101                   # default admin/exporter port
haproxy_check_port: 8008                      # default health check port (patroni 8008 by default)
haproxy_primary_port: 5433                    # default primary port 5433
haproxy_replica_port: 5434                    # default replica port 5434
haproxy_backend_port: 6432                    # default target port: pgbouncer:6432 postgres:5432
haproxy_weight: 100                           # default weight for load balancer
haproxy_weight_fallback: 1                    # assign a small weight for primary in replica service
                                              # (in case of singleton replica failure, primary can still take read-only  )

# - vip - #
# vip_enabled: true                             # level2 vip requires primary/standby under same switch
# vip_address: 127.0.0.1                      # virtual ip address ip/cidr
# vip_cidrmask: 32                            # virtual ip address cidr mask
# vip_interface: eth0                         # virtual ip network interface