This the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Tutorials

How to setup pigsty in real-world environment

本章节主要针对Pigsty的高可用集群供给部分进行说明，如需了解监控系统，请参考界面一章。

无论是沙箱环境还是实际生产环境，Pigsty都采用同样的三步走部署流程：准备机器，修改配置，执行剧本。 本教程将详细介绍基于实际环境配置与定制Pigsty的方法。

  1. 准备资源

    • 所有机器都可以通过SSH免密登陆
    • 所有机器登陆的用户都可以免密码进行sudo
    • 选择1（或更多）台机器作为中央控制管理机（元节点）
    • 在元节点上安装ansible
    • 在元节点上克隆本项目
    • 下载离线软件安装包至files目录（可选）

  2. 修改配置

    • 根据环境情况，调整基础设施相关配置
    • 按照业务需求，声明所需的数据库集群规格
    • 可选特殊需求，定制数据库集群模板
    • 检查配置文件

  3. 执行剧本

    • 检查配置清单，选择执行对象，执行范围。
    • 执行infra.yml初始化基础设施，通常基础设施只需要初始化一次即可。
    • 执行initdb.yml初始化数据库集群，并通过监控系统验收数据库集群

因为Pigsty采用声明式的接口设计，因此部署的真正挑战在于第二部分 —— 配置修改。 当您根据实际环境完成配置参数调整后，简单地执行对应的预置剧本，即可将系统调整至期望的状态。

1 - Prepare

How to prepare resource for Pigsty?

在部署Pigsty前，您需要进行资源准备，包括以下几件事：

  • 资源规划：使用什么规格的机器创建多大规模的集群
  • 所有机器都可以通过SSH免密登陆
  • 所有机器登陆的用户都可以免密码进行sudo
  • 选择1（或更多）台机器作为中央控制管理机（元节点）
  • 在元节点上安装ansible
  • 在元节点上克隆本项目
  • 下载离线软件安装包至files目录（可选）

资源规划

在部署Pigsty前，您有一些重要的技术决定需要提前定好。

元节点的数量

在每套环境中，Pigsty最少需要一个元节点（或曰：中控机），作为该节点将作为整个环境的控制中心。完成各种工作：保存状态，管理配置，发起管理，收集指标，等等。

整个环境的基础设施组件，Nginx（本地yum源），Grafana，Prometheus，Alertmanager，NTP，DNS Nameserver，DCS都将部署在元节点上。

元节点将用于部署元数据库 Consul 或 Etcd，因此通常建议在生产环境使用3个元节点。能够保证较为合理的可用性。当然您可以使用已有的外部DCS集群。

除DCS外，所有基础设施都将以对等副本的方式部署在所有元节点上。元节点的数量要求最少1个，推荐3个，建议不超过5个。

元节点的要求

元节点将通过Ansible管理控制环境中的其他机器，因此需要您具有登陆其他机器并执行sudo命令的权限。

SSH访问将复用机器上的配置，您需要确保：

  • 可以通过ssh免密访问环境内的所有机器（包括元节点资深）
  • ssh免密访问的用户可以免密执行sudo权限。
  • 中控机安装有ansible

访问中控机

如果您希望使用图形界面，则需确保能从本地环境直接或间接访问中控机的80端口。

中控机运行有Nginx，监听80端口对外提供服务。Prometheus, Grafana, Altermanager, Consul UI会被Nginx代理至对应服务。

互联网访问

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

所有依赖的软件总大小约1GB左右，下载速度取决于您的网络情况。尽管Pigsty已经尽量使用镜像源以加速下载，但少量包的下载仍可能受到防火墙的阻挠，可能出现非常慢的情况。您可以通过proxy_env配置项设置下载代理以完成首次下载，如果您遇到了长时间下载不成功的情况，强烈建议您使用“HTTP代理”进行下载，或使根据您的实际网络环境使用自定义的Yum镜像。

这里特别需要注意的是中国大陆的互联网条件“较差”，PostgreSQL官方源的访问速度非常慢，建议使用离线安装的方式。

离线安装

中控机本身并不要求互联网访问，尽管如果有肯定会非常方便，但出于安全考虑，我们建议在生产环境中使用离线安装的方式进行部署。

或直接下载预先打包好的离线安装包。例如最新的离线安装包地址为： https://github.com/Vonng/pigsty/releases/download/v0.5.0/pkg.tgz

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

详情请参考离线安装一节。

接下来

完成资源准备后，您可以开始着手修改Pigsty的配置文件

1.1 - Vagrant Provision

How to pull up vm quickly based on vagrant and virtualbox

有时候为了测试“数据库集群”这样的东西，您需要若干台虚拟机。

尽管现在的云服务已经非常方便也非常便宜，但再方便也肯定也没有本地的虚拟机用起来方便。

vagrant就是用来解决这个问题的。您不需要了解它的原理，只需要知道它能简单快捷地按照您的要求在您的笔记本、PC或Mac上拉起若干台虚拟机即可。

您所需要完成的工作，就是喂给Vagrant一个配置文件清单。

样例

下面这个文件位于https://github.com/Vonng/pigsty/blob/master/vagrant/Vagrantfile，是Pigsty沙箱所使用的Vagrantfile，这里定义了四台虚拟机，包括一台2核/4GB的中控机元节点，和3台1核/1GB的数据库节点。

您只需要进入项目下的vagrant目录，执行vagrant up，即可拉起所有的四台虚拟机。

如果您的笔记本配置不足，可以考虑减少数据库节点的数量（N=3），减为0即使用单个元节点的最低配置。此外您还可以修改每台机器的CPU核数和内存资源等。

注意这里使用了IMAGE_NAME = "centos/7"，第一次执行时，默认会从官方下载好已经安装好的最小的centos 7.8虚拟机镜像。确保您拥有合适的代理网络访问权限！

IMAGE_NAME = "centos/7"
N=3  # 数据库机器节点数量，可修改为0

Vagrant.configure("2") do |config|
    config.vm.box = IMAGE_NAME
    config.vm.box_check_update = false
    config.ssh.insert_key = false

    # 元节点
    config.vm.define "meta", primary: true do |meta|  # 元节点默认的ssh别名为`meta`
        meta.vm.hostname = "meta"
        meta.vm.network "private_network", ip: "10.10.10.10"
        meta.vm.provider "virtualbox" do |v|
            v.linked_clone = true
            v.customize [
                    "modifyvm", :id,
                    "--memory", 4096, "--cpus", "2",   # 元节点的内存与CPU核数：默认为2核/4GB
                    "--nictype1", "virtio", "--nictype2", "virtio",
                    "--hwv·irtex", "on", "--ioapic", "on", "--rtcuseutc", "on", "--vtxvpid", "on", "--largepages", "on"
                ]
        end
        meta.vm.provision "shell", path: "provision.sh"
    end

    # 初始化N个数据库节点
    (1..N).each do |i|
        config.vm.define "node-#{i}" do |node|  # 数据库节点默认的ssh别名分别为`node-{1,2,3}`
            node.vm.box = IMAGE_NAME
            node.vm.network "private_network", ip: "10.10.10.#{i + 10}"
            node.vm.hostname = "node-#{i}"
            node.vm.provider "virtualbox" do |v|
                v.linked_clone = true
                v.customize [
                        "modifyvm", :id,
                        "--memory", 2048, "--cpus", "1", # 数据库节点的内存与CPU核数：默认为1核/2GB
                        "--nictype1", "virtio", "--nictype2", "virtio",
                        "--hwvirtex", "on", "--ioapic", "on", "--rtcuseutc", "on", "--vtxvpid", "on", "--largepages", "on"
                    ]
            end
            node.vm.provision "shell", path: "provision.sh"
        end
    end
end

快捷方式

Pigsty已经提供了对Vagrant命令的包装，您可以在项目的Makefile中看到虚拟机管理的相关命令

make        # 启动集群
make new    # 销毁并创建新集群
make dns    # 将Pigsty域名记录写入本机/etc/hosts （需要sudo权限）
make ssh    # 将虚拟机SSH配置信息写入 ~/.ssh/config
make clean	# 销毁现有本地集群
make cache	# 制作离线安装包，并拷贝至宿主机本地，加速后续集群创建
make upload # 将离线安装缓存包 pkg.tgz 上传并解压至默认目录 /www/pigsty

###############################################################
# vm management
###############################################################
clean:
	cd vagrant && vagrant destroy -f --parallel; exit 0
up:
	cd vagrant && vagrant up
halt:
	cd vagrant && vagrant halt
down: halt
status:
	cd vagrant && vagrant status
suspend:
	cd vagrant && vagrant suspend
resume:
	cd vagrant && vagrant resume
provision:
	cd vagrant && vagrant provision
# sync ntp time
sync:
	echo meta node-1 node-2 node-3 | xargs -n1 -P4 -I{} ssh {} 'sudo ntpdate pool.ntp.org'; true
	# echo meta node-1 node-2 node-3 | xargs -n1 -P4 -I{} ssh {} 'sudo chronyc -a makestep'; true
# show vagrant cluster status
st: status
start: up ssh sync
stop: halt

# only init partial of cluster
meta-up:
	cd vagrant && vagrant up meta
node-up:
	cd vagrant && vagrant up node-1 node-2 node-3
node-new:
	cd vagrant && vagrant destroy -f node-1 node-2 node-3
	cd vagrant && vagrant up node-1 node-2 node-3

1.2 - Offline Installation

How to install pigsty without the Internet access

离线安装包（可选）

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

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

下载地址

预先打包（仅限CentOS 7.8）的离线安装包可以在Github Release页面找到

例如v0.5的离线安装包地址为：

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缓存离线安装包。

离线安装包是本地yum源的副本

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

如果您改了离线源的名称repo_name，您还需要注意，配置文件中{{ repo_name }}是否与离线安装包解压所得的文件夹名一致。

2 - Configuration

How to change pigsty configuration

Configuration

  • Configuration file format
  • Ansible variable precedence
  • Local yum repo
  • config infrastructure
  • config DCS
  • config postgres
  • config monitoring system
  • config proxy and load balancer

2.1 - Introduction to Configuration

Introduction to pigsty configuration

Pigsty follows 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变量优先级

  1. command line values (for example, -u my_user, these are not variables)
  2. role defaults (defined in role/defaults/main.yml) 1
  3. inventory file or script group vars 2
  4. inventory group_vars/all 3
  5. playbook group_vars/all 3
  6. inventory group_vars/* 3
  7. playbook group_vars/* 3
  8. inventory file or script host vars 2
  9. inventory host_vars/* 3
  10. playbook host_vars/* 3
  11. host facts / cached set_facts 4
  12. play vars
  13. play vars_prompt
  14. play vars_files
  15. role vars (defined in role/vars/main.yml)
  16. block vars (only for tasks in block)
  17. task vars (only for the task)
  18. include_vars
  19. set_facts / registered vars
  20. role (and include_role) params
  21. include params
  22. 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.2 - Connection

Connection parameters in Pigsty

Overview

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

Detail

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>即可。注意这些变量都是实例级别的变量。

2.3 - Yum Repo

Parameters about local yum repo

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

2.4 - Node

Parameters about node & infrastructure

参数概览

#------------------------------------------------------------------------------
# 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

参数详解

2.5 - Meta Node

Parameters about meta node：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

参数详解

2.6 - DCS

Parameters about distributive configuration storage (consul/etcd)

Overview

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

Detail

#------------------------------------------------------------------------------
# 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)

参数详解

2.7 - PG Install

Parameters about postgres installation

参数概览

#------------------------------------------------------------------------------
# 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}

参数详解

2.8 - PG Provision

Parameters about postgres provision

Overview

#------------------------------------------------------------------------------
# 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

Detail

#------------------------------------------------------------------------------
# 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

参数详解

2.9 - PG Template

Parameters about postgres template and customization

在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: []

参数详解

2.10 - Monitoring

Parameters about monitoring system

Overview

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

Detail

#------------------------------------------------------------------------------
# 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

参数详解

2.11 - Proxy

Parameters about haproxy, vip and load balancer

Overview

#------------------------------------------------------------------------------
# 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

Detail

#------------------------------------------------------------------------------
# PROXY PROVISION
#------------------------------------------------------------------------------
# - haproxy - #
haproxy_enabled: true                         # enable haproxy among every cluster members
haproxy_policy: leastconn                     # roundrobin, leastconn
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

# - 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

参数详解

3 - Playbooks

How to initialize infra and database with ansible playbooks

初始化

Pigsty采用声明式的接口，当配置完成之后，您只需要运行固定的Playbook，即可完成初始化。

初始化操作分为两部分：

同时，目标也分为两种：

  • 元节点
  • 普通节点

操作需要满足以下两条约束

  • 必须在初始化普通的节点基础设施前，完成元节点的基础设施初始化。
  • 初始化数据库前，必须完成基础设施的初始化

操作顺序

步骤\对象 元节点 普通节点
基础设施 1 2
数据库 3 4 
# 基础设施初始化
./infra.yml -l meta            # 初始化元节点的基础设施（通常只需在环境初始化时执行一次）
./infra.yml -l <cluster-name>  # 初始化普通节点上的基础设施（每次新增集群时执行）

# 数据库集群初始化
./initdb.yml -l <cluster>      # 初始化普通节点上的数据库集群（也可以在元节点上初始化数据库）

其他常见操作

  • 操作时序
  • 使用参数控制执行对象
  • 使用标签控制执行内容
  • 抹除现有集群
  • 向集群添加新实例
  • 从集群中移除实例

3.1 - Infra Provision

How to provision infrastructure

选择性执行

您可以通过ansible的标签机制，可以选择执行剧本的一个子集。

例如，如果您只想执行本地源初始化的部分，则可以通过以下命令进行

./infra.yml --tags=repo

默认任务

使用以下命令可以列出所有基础设施初始化会执行的任务，以及可以使用的标签：

./infra.yml --list-tasks

默认任务如下：

playbook: ./infra.yml

  play #1 (meta): Init local repo	TAGS: [repo]
    tasks:
      Create local repo directory	TAGS: [repo, repo_dir]
      Backup & remove existing repos	TAGS: [repo, repo_upstream]
      Add required upstream repos	TAGS: [repo, repo_upstream]
      Check repo pkgs cache exists	TAGS: [repo, repo_prepare]
      Set fact whether repo_exists	TAGS: [repo, repo_prepare]
      Move upstream repo to backup	TAGS: [repo, repo_prepare]
      Add local file system repos	TAGS: [repo, repo_prepare]
      repo : Remake yum cache if not exists	TAGS: [repo, repo_prepare]
      Install repo bootstrap packages	TAGS: [repo, repo_boot]
      Render repo nginx server files	TAGS: [repo, repo_nginx]
      Disable selinux for repo server	TAGS: [repo, repo_nginx]
      Launch repo nginx server	TAGS: [repo, repo_nginx]
      Waits repo server online	TAGS: [repo, repo_nginx]
      repo : Download web url packages	TAGS: [repo, repo_download]
      Download repo packages	TAGS: [repo, repo_download]
      Download repo pkg deps	TAGS: [repo, repo_download]
      Create local repo index	TAGS: [repo, repo_download]
      repo : Copy bootstrap scripts	TAGS: [repo, repo_download, repo_script]
      Mark repo cache as valid	TAGS: [repo, repo_download]

  play #2 (all): Provision Node	TAGS: [node]
    tasks:
      Update node hostname	TAGS: [node, node_name]
      node : Add new hostname to /etc/hosts	TAGS: [node, node_name]
      node : Write static dns records	TAGS: [node, node_dns]
      node : Get old nameservers	TAGS: [node, node_resolv]
      node : Truncate resolv file	TAGS: [node, node_resolv]
      node : Write resolv options	TAGS: [node, node_resolv]
      node : Add new nameservers	TAGS: [node, node_resolv]
      node : Append old nameservers	TAGS: [node, node_resolv]
      node : Node configure disable firewall	TAGS: [node, node_firewall]
      node : Node disable selinux by default	TAGS: [node, node_firewall]
      node : Backup existing repos	TAGS: [node, node_repo]
      node : Install upstream repo	TAGS: [node, node_repo]
      node : Install local repo	TAGS: [node, node_repo]
      Install node basic packages	TAGS: [node, node_pkgs]
      Install node extra packages	TAGS: [node, node_pkgs]
      node : Install meta specific packages	TAGS: [node, node_pkgs]
      Install node basic packages	TAGS: [node, node_pkgs]
      Install node extra packages	TAGS: [node, node_pkgs]
      node : Install meta specific packages	TAGS: [node, node_pkgs]
      node : Node configure disable numa	TAGS: [node, node_feature]
      node : Node configure disable swap	TAGS: [node, node_feature]
      node : Node configure unmount swap	TAGS: [node, node_feature]
      node : Node setup static network	TAGS: [node, node_feature]
      node : Node configure disable firewall	TAGS: [node, node_feature]
      node : Node configure disk prefetch	TAGS: [node, node_feature]
      node : Enable linux kernel modules	TAGS: [node, node_kernel]
      node : Enable kernel module on reboot	TAGS: [node, node_kernel]
      node : Get config parameter page count	TAGS: [node, node_tuned]
      node : Get config parameter page size	TAGS: [node, node_tuned]
      node : Tune shmmax and shmall via mem	TAGS: [node, node_tuned]
      node : Create tuned profiles	TAGS: [node, node_tuned]
      node : Render tuned profiles	TAGS: [node, node_tuned]
      node : Active tuned profile	TAGS: [node, node_tuned]
      node : Change additional sysctl params	TAGS: [node, node_tuned]
      node : Copy default user bash profile	TAGS: [node, node_profile]
      Setup node default pam ulimits	TAGS: [node, node_ulimit]
      node : Create os user group admin	TAGS: [node, node_admin]
      node : Create os user admin	TAGS: [node, node_admin]
      node : Grant admin group nopass sudo	TAGS: [node, node_admin]
      node : Add no host checking to ssh config	TAGS: [node, node_admin]
      node : Add admin ssh no host checking	TAGS: [node, node_admin]
      node : Fetch all admin public keys	TAGS: [node, node_admin]
      node : Exchange all admin ssh keys	TAGS: [node, node_admin]
      node : Install public keys	TAGS: [node, node_admin]
      node : Install ntp package	TAGS: [node, ntp_install]
      node : Install chrony package	TAGS: [node, ntp_install]
      Setup default node timezone	TAGS: [node, ntp_config]
      node : Copy the ntp.conf file	TAGS: [node, ntp_config]
      node : Copy the chrony.conf template	TAGS: [node, ntp_config]
      node : Launch ntpd service	TAGS: [node, ntp_launch]
      node : Launch chronyd service	TAGS: [node, ntp_launch]

  play #3 (meta): Init meta service	TAGS: [meta]
    tasks:
      Create local ca directory	TAGS: [ca, ca_dir, meta]
      Copy ca cert from local files	TAGS: [ca, ca_copy, meta]
      Check ca key cert exists	TAGS: [ca, ca_create, meta]
      ca : Create self-signed CA key-cert	TAGS: [ca, ca_create, meta]
      Make sure nginx package installed	TAGS: [meta, nginx]
      Copy nginx default config	TAGS: [meta, nginx]
      Copy nginx upstream conf	TAGS: [meta, nginx]
      nginx : Create local html directory	TAGS: [meta, nginx]
      Update default nginx index page	TAGS: [meta, nginx]
      Restart meta nginx service	TAGS: [meta, nginx]
      Wait for nginx service online	TAGS: [meta, nginx]
      Make sure nginx exporter installed	TAGS: [meta, nginx, nginx_exporter]
      Config nginx_exporter options	TAGS: [meta, nginx, nginx_exporter]
      Restart nginx_exporter service	TAGS: [meta, nginx, nginx_exporter]
      Wait for nginx exporter online	TAGS: [meta, nginx, nginx_exporter]
      Install prometheus and alertmanager	TAGS: [meta, prometheus, prometheus_install]
      Wipe out prometheus config dir	TAGS: [meta, prometheus, prometheus_clean]
      Wipe out existing prometheus data	TAGS: [meta, prometheus, prometheus_clean]
      Recreate prometheus data dir	TAGS: [meta, prometheus, prometheus_config]
      Copy /etc/prometheus configs	TAGS: [meta, prometheus, prometheus_config]
      Copy /etc/prometheus opts	TAGS: [meta, prometheus, prometheus_config]
      Overwrite prometheus scrape_interval	TAGS: [meta, prometheus, prometheus_config]
      Overwrite prometheus evaluation_interval	TAGS: [meta, prometheus, prometheus_config]
      Overwrite prometheus scrape_timeout	TAGS: [meta, prometheus, prometheus_config]
      Overwrite prometheus pg metrics path	TAGS: [meta, prometheus, prometheus_config]
      Launch prometheus service	TAGS: [meta, prometheus, prometheus_launch]
      prometheus : Launch alertmanager service	TAGS: [meta, prometheus, prometheus_launch]
      Wait for prometheus online	TAGS: [meta, prometheus, prometheus_launch]
      prometheus : Wait for alertmanager online	TAGS: [meta, prometheus, prometheus_launch]
      Make sure grafana is installed	TAGS: [grafana, grafana_install, meta]
      Check grafana plugin cache exists	TAGS: [grafana, grafana_plugin, meta]
      Provision grafana plugins via cache	TAGS: [grafana, grafana_plugin, meta]
      Download grafana plugins from web	TAGS: [grafana, grafana_plugin, meta]
      Download grafana plugins from web	TAGS: [grafana, grafana_plugin, meta]
      Create grafana plugins cache	TAGS: [grafana, grafana_plugin, meta]
      Copy /etc/grafana/grafana.ini	TAGS: [grafana, grafana_config, meta]
      Remove grafana provision dir	TAGS: [grafana, grafana_config, meta]
      grafana : Copy provisioning content	TAGS: [grafana, grafana_config, meta]
      grafana : Copy pigsty dashboards	TAGS: [grafana, grafana_config, meta]
      grafana : Copy pigsty icon image	TAGS: [grafana, grafana_config, meta]
      Replace grafana icon with pigsty	TAGS: [grafana, grafana_config, grafana_customize, meta]
      Launch grafana service	TAGS: [grafana, grafana_launch, meta]
      Wait for grafana online	TAGS: [grafana, grafana_launch, meta]
      Update grafana default preferences	TAGS: [grafana, grafana_provision, meta]
      Register consul grafana service	TAGS: [grafana, grafana_register, meta]
      grafana : Reload consul	TAGS: [grafana, grafana_register, meta]

  play #4 (all): Init dcs	TAGS: []
    tasks:
      Check for existing consul	TAGS: [consul_check, dcs]
      consul : Consul exists flag fact set	TAGS: [consul_check, dcs]
      Abort due to consul exists	TAGS: [consul_check, dcs]
      Clean existing consul instance	TAGS: [consul_check, dcs]
      Stop any running consul instance	TAGS: [consul_check, dcs]
      Remove existing consul dir	TAGS: [consul_check, dcs]
      Recreate consul dir	TAGS: [consul_check, dcs]
      Make sure consul is installed	TAGS: [consul_install, dcs]
      Make sure consul dir exists	TAGS: [consul_config, dcs]
      consul : Get dcs server node names	TAGS: [consul_config, dcs]
      consul : Get dcs node name from var	TAGS: [consul_config, dcs]
      consul : Get dcs node name from var	TAGS: [consul_config, dcs]
      consul : Fetch hostname as dcs node name	TAGS: [consul_config, dcs]
      consul : Get dcs name from hostname	TAGS: [consul_config, dcs]
      Copy /etc/consul.d/consul.json	TAGS: [consul_config, dcs]
      Copy consul agent service	TAGS: [consul_config, dcs]
      consul : Get dcs bootstrap expect quroum	TAGS: [consul_server, dcs]
      Copy consul server service unit	TAGS: [consul_server, dcs]
      Launch consul server service	TAGS: [consul_server, dcs]
      Wait for consul server online	TAGS: [consul_server, dcs]
      Launch consul agent service	TAGS: [consul_agent, dcs]
      Wait for consul agent online	TAGS: [consul_agent, dcs]

  play #5 (meta): Copy ansible scripts	TAGS: [ansible]
    tasks:
      Create ansible tarball	TAGS: [ansible]
      Create ansible directory	TAGS: [ansible]
      Copy ansible tarball	TAGS: [ansible]
      Extract tarball	TAGS: [ansible]

3.2 - PgSQL Provision

How to provision database clusters

选择性执行

指定执行任务

您可以通过ansible的标签机制，可以选择执行剧本的一个子集。

例如，如果您只想执行初始化用户权限系统和模板的部分，则可以通过以下命令进行

./initdb.yml --tags=pg_init

指定执行对象

另外，通过limit子句，您可以选择需要执行的对象：

./initdb.yml -l pg-test

该剧本将会在pg-test集群的所有成员上执行，而不会影响环境中的其他集群。

建议在生产环境中操作时，总是指定-l参数，避免误操作（比如把整个环境中的数据库全抹干净了）

默认任务

使用以下命令可以列出所有基础设施初始化会执行的任务，以及可以使用的标签：

./initdb.yml --list-tasks

默认任务如下：

playbook: ./initdb.yml

  play #1 (all): Init database cluster	TAGS: []
    tasks:
      Create os group postgres	TAGS: [instal, pg_dbsu, postgres]
      postgres : Make sure dcs group exists	TAGS: [instal, pg_dbsu, postgres]
      postgres : Create dbsu {{ pg_dbsu }}	TAGS: [instal, pg_dbsu, postgres]
      postgres : Grant dbsu nopass sudo	TAGS: [instal, pg_dbsu, postgres]
      postgres : Grant dbsu all sudo	TAGS: [instal, pg_dbsu, postgres]
      postgres : Grant dbsu limited sudo	TAGS: [instal, pg_dbsu, postgres]
      postgres : Config patroni watchdog support	TAGS: [instal, pg_dbsu, postgres]
      postgres : Add dbsu ssh no host checking	TAGS: [instal, pg_dbsu, postgres]
      postgres : Fetch dbsu public keys	TAGS: [instal, pg_dbsu, postgres]
      postgres : Exchange dbsu ssh keys	TAGS: [instal, pg_dbsu, postgres]
      postgres : Install offical pgdg yum repo	TAGS: [instal, pg_install, postgres]
      postgres : Install pg packages	TAGS: [instal, pg_install, postgres]
      postgres : Install pg extensions	TAGS: [instal, pg_install, postgres]
      postgres : Link /usr/pgsql to current version	TAGS: [instal, pg_install, postgres]
      postgres : Add pg bin dir to profile path	TAGS: [instal, pg_install, postgres]
      postgres : Fix directory ownership	TAGS: [instal, pg_install, postgres]
      Remove default postgres service	TAGS: [instal, pg_install, postgres]
      postgres : Check necessary variables exists	TAGS: [always, pg_preflight, postgres, preflight]
      postgres : Fetch variables via pg_cluster	TAGS: [always, pg_preflight, postgres, preflight]
      postgres : Set cluster basic facts for hosts	TAGS: [always, pg_preflight, postgres, preflight]
      postgres : Assert cluster primary singleton	TAGS: [always, pg_preflight, postgres, preflight]
      postgres : Setup cluster primary ip address	TAGS: [always, pg_preflight, postgres, preflight]
      postgres : Setup repl upstream for primary	TAGS: [always, pg_preflight, postgres, preflight]
      postgres : Setup repl upstream for replicas	TAGS: [always, pg_preflight, postgres, preflight]
      postgres : Debug print instance summary	TAGS: [always, pg_preflight, postgres, preflight]
      Check for existing postgres instance	TAGS: [pg_check, postgres, prepare]
      postgres : Set fact whether pg port is open	TAGS: [pg_check, postgres, prepare]
      Abort due to existing postgres instance	TAGS: [pg_check, postgres, prepare]
      Clean existing postgres instance	TAGS: [pg_check, postgres, prepare]
      Shutdown existing postgres service	TAGS: [pg_clean, postgres, prepare]
      postgres : Remove registerd consul service	TAGS: [pg_clean, postgres, prepare]
      Remove postgres metadata in consul	TAGS: [pg_clean, postgres, prepare]
      Remove existing postgres data	TAGS: [pg_clean, postgres, prepare]
      postgres : Make sure main and backup dir exists	TAGS: [pg_dir, postgres, prepare]
      Create postgres directory structure	TAGS: [pg_dir, postgres, prepare]
      postgres : Create pgbouncer directory structure	TAGS: [pg_dir, postgres, prepare]
      postgres : Create links from pgbkup to pgroot	TAGS: [pg_dir, postgres, prepare]
      postgres : Create links from current cluster	TAGS: [pg_dir, postgres, prepare]
      postgres : Copy pg_cluster to /pg/meta/cluster	TAGS: [pg_meta, postgres, prepare]
      postgres : Copy pg_version to /pg/meta/version	TAGS: [pg_meta, postgres, prepare]
      postgres : Copy pg_instance to /pg/meta/instance	TAGS: [pg_meta, postgres, prepare]
      postgres : Copy pg_seq to /pg/meta/sequence	TAGS: [pg_meta, postgres, prepare]
      postgres : Copy pg_role to /pg/meta/role	TAGS: [pg_meta, postgres, prepare]
      Copy postgres scripts to /pg/bin/	TAGS: [pg_scripts, postgres, prepare]
      postgres : Copy alias profile to /etc/profile.d	TAGS: [pg_scripts, postgres, prepare]
      Copy psqlrc to postgres home	TAGS: [pg_scripts, postgres, prepare]
      postgres : Setup hostname to pg instance name	TAGS: [pg_hostname, postgres, prepare]
      postgres : Copy consul node-meta definition	TAGS: [pg_nodemeta, postgres, prepare]
      postgres : Restart consul to load new node-meta	TAGS: [pg_nodemeta, postgres, prepare]
      postgres : Config patroni watchdog support	TAGS: [pg_watchdog, postgres, prepare]
      postgres : Get config parameter page count	TAGS: [pg_config, postgres]
      postgres : Get config parameter page size	TAGS: [pg_config, postgres]
      postgres : Tune shared buffer and work mem	TAGS: [pg_config, postgres]
      postgres : Hanlde small size mem occasion	TAGS: [pg_config, postgres]
      Calculate postgres mem params	TAGS: [pg_config, postgres]
      postgres : create patroni config dir	TAGS: [pg_config, postgres]
      postgres : use predefined patroni template	TAGS: [pg_config, postgres]
      postgres : Render default /pg/conf/patroni.yml	TAGS: [pg_config, postgres]
      postgres : Link /pg/conf/patroni to /pg/bin/	TAGS: [pg_config, postgres]
      postgres : Link /pg/bin/patroni.yml to /etc/patroni/	TAGS: [pg_config, postgres]
      postgres : Config patroni watchdog support	TAGS: [pg_config, postgres]
      postgres : create patroni systemd drop-in dir	TAGS: [pg_config, postgres]
      Copy postgres systemd service file	TAGS: [pg_config, postgres]
      postgres : create patroni systemd drop-in file	TAGS: [pg_config, postgres]
      postgres : Render default initdb scripts	TAGS: [pg_config, postgres]
      postgres : Launch patroni on primary instance	TAGS: [pg_primary, postgres]
      postgres : Wait for patroni primary online	TAGS: [pg_primary, postgres]
      Wait for postgres primary online	TAGS: [pg_primary, postgres]
      Check primary postgres service ready	TAGS: [pg_primary, postgres]
      postgres : Check replication connectivity to primary	TAGS: [pg_primary, postgres]
      postgres : Render default pg-init scripts	TAGS: [pg_init, pg_init_config, postgres]
      postgres : Render template init script	TAGS: [pg_init, pg_init_config, postgres]
      postgres : Execute initialization scripts	TAGS: [pg_init, postgres]
      postgres : Check primary instance ready	TAGS: [pg_init, postgres]
      postgres : Add dbsu password to pgpass if exists	TAGS: [pg_pass, postgres]
      postgres : Add system user to pgpass	TAGS: [pg_pass, postgres]
      postgres : Check replication connectivity to primary	TAGS: [pg_replica, postgres]
      postgres : Launch patroni on replica instances	TAGS: [pg_replica, postgres]
      postgres : Wait for patroni replica online	TAGS: [pg_replica, postgres]
      Wait for postgres replica online	TAGS: [pg_replica, postgres]
      Check replica postgres service ready	TAGS: [pg_replica, postgres]
      postgres : Render hba rules	TAGS: [pg_hba, postgres]
      postgres : Reload hba rules	TAGS: [pg_hba, postgres]
      postgres : Pause patroni	TAGS: [pg_patroni, postgres]
      postgres : Stop patroni on replica instance	TAGS: [pg_patroni, postgres]
      postgres : Stop patroni on primary instance	TAGS: [pg_patroni, postgres]
      Launch raw postgres on primary	TAGS: [pg_patroni, postgres]
      Launch raw postgres on primary	TAGS: [pg_patroni, postgres]
      Wait for postgres online	TAGS: [pg_patroni, postgres]
      postgres : Check pgbouncer is installed	TAGS: [pgbouncer, pgbouncer_check, postgres]
      postgres : Stop existing pgbouncer service	TAGS: [pgbouncer, pgbouncer_clean, postgres]
      postgres : Remove existing pgbouncer dirs	TAGS: [pgbouncer, pgbouncer_clean, postgres]
      Recreate dirs with owner postgres	TAGS: [pgbouncer, pgbouncer_clean, postgres]
      postgres : Copy /etc/pgbouncer/pgbouncer.ini	TAGS: [pgbouncer, pgbouncer_config, postgres]
      postgres : Copy /etc/pgbouncer/pgb_hba.conf	TAGS: [pgbouncer, pgbouncer_config, postgres]
      postgres : Touch userlist and database list	TAGS: [pgbouncer, pgbouncer_config, postgres]
      postgres : Add default users to pgbouncer	TAGS: [pgbouncer, pgbouncer_config, postgres]
      postgres : Copy pgbouncer systemd service	TAGS: [pgbouncer, pgbouncer_launch, postgres]
      postgres : Launch pgbouncer pool service	TAGS: [pgbouncer, pgbouncer_launch, postgres]
      postgres : Wait for pgbouncer service online	TAGS: [pgbouncer, pgbouncer_launch, postgres]
      postgres : Check pgbouncer service is ready	TAGS: [pgbouncer, pgbouncer_launch, postgres]
      postgres : Render business init script	TAGS: [business, pg_biz_config, pg_biz_init, postgres]
      postgres : Render database baseline sql	TAGS: [business, pg_biz_config, pg_biz_init, postgres]
      postgres : Execute business init script	TAGS: [business, pg_biz_init, postgres]
      postgres : Execute database baseline sql	TAGS: [business, pg_biz_init, postgres]
      postgres : Add pgbouncer busniess users	TAGS: [business, pg_biz_pgbouncer, postgres]
      postgres : Add pgbouncer busniess database	TAGS: [business, pg_biz_pgbouncer, postgres]
      postgres : Restart pgbouncer	TAGS: [business, pg_biz_pgbouncer, postgres]
      Copy postgres service definition	TAGS: [pg_register, postgres, register]
      postgres : Reload consul service	TAGS: [pg_register, postgres, register]
      postgres : Render grafana datasource definition	TAGS: [pg_grafana, postgres, register]
      postgres : Register datasource to grafana	TAGS: [pg_grafana, postgres, register]
      monitor : Create /etc/pg_exporter conf dir	TAGS: [monitor, pg_exporter]
      monitor : Copy default pg_exporter.yaml	TAGS: [monitor, pg_exporter]
      monitor : Config /etc/default/pg_exporter	TAGS: [monitor, pg_exporter]
      monitor : Config pg_exporter service unit	TAGS: [monitor, pg_exporter]
      monitor : Launch pg_exporter systemd service	TAGS: [monitor, pg_exporter]
      monitor : Wait for pg_exporter service online	TAGS: [monitor, pg_exporter]
      monitor : Register pg-exporter consul service	TAGS: [monitor, pg_exporter]
      monitor : Reload pg-exporter consul service	TAGS: [monitor, pg_exporter]
      monitor : Config pgbouncer_exporter opts	TAGS: [monitor, pgbouncer_exporter]
      monitor : Config pgbouncer_exporter service	TAGS: [monitor, pgbouncer_exporter]
      monitor : Launch pgbouncer_exporter service	TAGS: [monitor, pgbouncer_exporter]
      monitor : Wait for pgbouncer_exporter online	TAGS: [monitor, pgbouncer_exporter]
      monitor : Register pgb-exporter consul service	TAGS: [monitor, pgbouncer_exporter]
      monitor : Reload pgb-exporter consul service	TAGS: [monitor, pgbouncer_exporter]
      monitor : Copy node_exporter systemd service	TAGS: [monitor, node_exporter]
      monitor : Config default node_exporter options	TAGS: [monitor, node_exporter]
      monitor : Launch node_exporter service unit	TAGS: [monitor, node_exporter]
      monitor : Wait for node_exporter online	TAGS: [monitor, node_exporter]
      monitor : Register node-exporter service	TAGS: [monitor, node_exporter]
      monitor : Reload node-exporter consul service	TAGS: [monitor, node_exporter]
      proxy : Templating /etc/default/vip-manager.yml	TAGS: [proxy, vip]
      proxy : create vip-manager. systemd drop-in dir	TAGS: [proxy, vip]
      proxy : create vip-manager systemd drop-in file	TAGS: [proxy, vip]
      proxy : Launch vip-manager	TAGS: [proxy, vip]
      proxy : Set pg_instance in case of absence	TAGS: [haproxy, proxy]
      proxy : Fetch postgres cluster memberships	TAGS: [haproxy, proxy]
      Templating /etc/haproxyhaproxy.cfg	TAGS: [haproxy, proxy]
      Copy haproxy systemd service file	TAGS: [haproxy, proxy]
      Launch haproxy load balancer service	TAGS: [haproxy, proxy]
      Wait for haproxy load balancer online	TAGS: [haproxy, proxy]
      Copy haproxy service definition	TAGS: [haproxy_register, proxy]
      Reload haproxy consul service	TAGS: [haproxy_register, proxy]

4 - Customize

How to customize pigsty

Customize Template

Customize Business database

4.1 - Access Control

Learn how to customize access control model in pigsty

PostgreSQL提供了两类访问控制机制：认证（Authentication） 与 权限（Privileges）

Pigsty附带有基本的访问控制模型，足以覆盖绝大多数应用场景。

用户体系

Pigsty的默认权限系统包含四个默认用户与三类默认角色，覆盖绝大多数业务场景。 您可以通过配置文件修改默认用户的名字，但默认角色的名字不建议新手用户修改。 如需修改默认角色名称，请配套修改初始化模板中的默认权限逻辑。

默认用户

Pigsty带有四个默认用户：

  • 超级用户（postgres），数据库的拥有者与创建者，与操作系统用户一致
  • 复制用户（replicator），用于主从复制的用户。
  • 监控用户（dbuser_monitor），用于监控数据库指标的用户。
  • 管理员（dbuser_admin），执行日常管理操作与数据库变更。

默认角色

Pigsty带有三个默认角色：

  • 只读角色（dbrole_readonly）：只读
  • 读写角色（dbrole_readwrite）：读写，继承dbrole_readonly
  • 管理角色（dbrole_admin）：执行DDL变更，继承dbrole_readwrite

相关配置

以下是7个默认用户与角色的相关变量，其中三个默认用户有专门的参数配置项。

出于安全考虑，我们不建议为DBSU配置密码，故pg_dbsu没有专门的密码配置项。 但您仍然可以在pg_default_roles中为超级用户指定密码。

pg_dbsu: postgres                             # os user for database, postgres by default
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

pg_default_roles:
  - username: dbrole_readonly                 
    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]

其他

Pgbouncer的操作系统用户将与数据库超级用户保持一致。

Pigsty默认会使用Postgres管理用户作为Pgbouncer的管理用户，使用Postgres的监控用户同时作为Pgbouncer的监控用户。

权限模型

默认情况下，角色拥有的权限如下所示

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

其他业务用户默认都应当属于三种默认角色之一：只读，读写，管理员。

所有用户都可以访问所有Schema，只读用户可以读取所有表，读写用户可以对所有表进行DML操作，管理员可以执行DDL变更操作。

数据库权限

数据库有三种权限：CONNECT, CREATE, TEMP，以及特殊的属主OWNERSHIP。

默认情况下，如果数据库没有配置属主，那么超级用户将会作为数据库的默认OWNER，而所有业务用户都会具有数据库的CONNECT权限。

如果为数据库配置了属主，则数据库的owner为该用户。在这种情况下数据库的CONNECT权限会被回收，只有OWNER可以CONNECT，管理员与OWNER也可以将CONNECT权限授予其他角色。这种设置对于多租户实例尤为实用。

创建新对象

默认情况下，出于安全考虑，Pigsty会撤销PUBLIC用户在数据库下CREATE新模式的权限，同时也会撤销PUBLIC用户在public模式下创建新关系的权限。数据库超级用户与管理员不受此限制，他们总是可以在任何地方执行DDL变更。

我们非常不建议使用业务用户执行DDL变更，因为PostgreSQL的ALTER DEFAULT PRIVILEGE仅针对“由特定用户创建的对象”生效，默认情况下超级用户postgres和dbuser_admin创建的对象拥有默认的权限配置，如果您希望授予业务用户dbrole_admin，请在执行DDL变更时首先执行

SET ROLE dbrole_admin; -- dbrole_admin 创建的对象具有正确的默认权限

多租户管理

如果您希望简单的使用数据库，那么在pg_databases中将owner留空即可，所有新创建的业务用户都可以连接至该数据库。

如果您希望在单个实例中承载多个数据库（尽管非常不建议这样做），则可以为数据库配置特定的属主。 在这种情况下，默认只有该属主用户可以连接至该数据库，且该属主可以将连接权限授予其他用户。

pg_databases:
  - {name: testdb , owner: dbuser_test} # 去掉owner

在数据库中创建对象的权限与用户是否为数据库属主无关，这只取决于创建该用户时是否为该用户赋予管理员权限。

pg_users:
  - {username: test1, password: xxx , groups: [dbrole_readwrite]}  # 不能创建Schema与对象
  - {username: test2, password: xxx , groups: [dbrole_admin]}      # 可以创建Schema与对象

认证模型

HBA是Host Based Authentication的缩写，可以将其视作IP黑白名单。

HBA配置方式

在Pigsty中，所有实例的HBA都由配置文件生成而来，最终生成的HBA规则取决于实例的角色（pg_role） Pigsty的HBA由下列变量控制：

  • pg_hba_rules: 环境统一的HBA规则
  • pg_hba_rules_extra: 特定于实例或集群的HBA规则
  • pgbouncer_hba_rules: 链接池使用的HBA规则
  • pgbouncer_hba_rules_extra: 特定于实例或集群的链接池HBA规则

每个变量都是由下列样式的规则组成的数组：

- 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

基于角色的HBA

role = common的HBA规则组会安装到所有的实例上，而其他的取值，例如（role : primary）则只会安装至pg_role = primary的实例上。 因此用户可以通过角色体系定义灵活的HBA规则。

在默认配置下，主库与从库会使用以下的HBA规则：

  • 超级用户通过本地操作系统认证访问
  • 其他用户可以从本地用密码访问
  • 复制用户可以从局域网段通过密码访问
  • 监控用户可以通过本地访问
  • 所有人都可以在元节点上使用密码访问
  • 管理员可以从局域网通过密码访问
  • 所有人都可以从内网通过密码访问
  • 读写用户（生产业务账号）可以通过本地（链接池）访问 （部分访问控制转交链接池处理）
  • 在从库上：只读用户（个人）可以从本地（链接池）访问。 （意味主库上拒绝只读用户连接）
#==============================================================#
# Default HBA
#==============================================================#
# allow local su with ident"
local   all             postgres                               ident
local   replication     postgres                               ident

# allow local user password access
local   all             all                                    md5

# allow local/intranet replication with password
local   replication     replicator                              md5
host    replication     replicator         127.0.0.1/32         md5
host    all             replicator         10.0.0.0/8           md5
host    all             replicator         172.16.0.0/12        md5
host    all             replicator         192.168.0.0/16       md5
host    replication     replicator         10.0.0.0/8           md5
host    replication     replicator         172.16.0.0/12        md5
host    replication     replicator         192.168.0.0/16       md5

# allow local role monitor with password
local   all             dbuser_monitor                          md5
host    all             dbuser_monitor      127.0.0.1/32        md5


#==============================================================#
# Common HBA
#==============================================================#
#  allow meta node password access
host    all     all                         10.10.10.10/32      md5

#  allow intranet admin password access
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

#  allow intranet password access
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

#  allow local read-write access (local production user via pgbouncer)
local   all     +dbrole_readwrite                               md5
host    all     +dbrole_readwrite           127.0.0.1/32        md5


#==============================================================#
# primary HBA
#==============================================================#

#==============================================================#
# replica HBA
#==============================================================#
#  allow read-only user (stats, personal) password directly access
local   all     +dbrole_readonly                               md5
host    all     +dbrole_readonly           127.0.0.1/32        md5


#==============================================================#
# Ad Hoc HBA
#==============================================================#

4.2 - Customize Business

Customize business database

您可以使用以下参数，对集群中的模板数据库template1进行定制。

pg-init-template.sql 是用于初始化template1数据的脚本模板


#------------------------------------------------------------------------------
# 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: []

4.3 - Customize Patroni

Learn about customize options for patroni

Pigsty使用Patroni管理与初始化Postgres数据库集群。

即使您选择不使用Patroni，Pigsty依然会使用Patroni完成数据库集群的初始化工作。

因此您可以通过Patroni配置文件完成一部分PostgreSQL的定制工作。

预制模板

初始化模板是用于初始化数据库集群的定义文件，默认位于roles/postgres/templates/。

Patroni配置文件格式，在有四种预定义好的初始化模板：

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

Patroni配置文件样例

#!/usr/bin/env patroni
#==============================================================#
# File      :   patroni.yml
# Ctime     :   2020-04-08
# Mtime     :   2020-12-22
# Desc      :   patroni cluster definition for {{ pg_cluster }} (oltp)
# Path      :   /pg/bin/patroni.yml
# Real Path :   /pg/conf/{{ pg_instance }}.yml
# Link      :   /pg/bin/patroni.yml -> /pg/conf/{{ pg_instance}}.yml
# Note      :   Transactional Database Cluster Template
# Doc       :   https://patroni.readthedocs.io/en/latest/SETTINGS.html
# Copyright (C) 2018-2021 Ruohang Feng
#==============================================================#

# OLTP database are optimized for performance, rt latency
# typical spec: 64 Core | 400 GB RAM | PCI-E SSD xTB

---
#------------------------------------------------------------------------------
# identity
#------------------------------------------------------------------------------
namespace: {{ pg_namespace }}/          # namespace
scope: {{ pg_cluster }}                 # cluster name
name: {{ pg_instance }}                 # instance name

#------------------------------------------------------------------------------
# log
#------------------------------------------------------------------------------
log:
  level: INFO                           #  NOTEST|DEBUG|INFO|WARNING|ERROR|CRITICAL
  dir: /pg/log/                         #  default log file: /pg/log/patroni.log
  file_size: 100000000                  #  100MB log triggers a log rotate
  # format: '%(asctime)s %(levelname)s: %(message)s'

#------------------------------------------------------------------------------
# dcs
#------------------------------------------------------------------------------
consul:
  host: 127.0.0.1:8500
  consistency: default         # default|consistent|stale
  register_service: true
  service_check_interval: 15s
  service_tags:
    - {{ pg_cluster }}

#------------------------------------------------------------------------------
# api
#------------------------------------------------------------------------------
# how to expose patroni service
# listen on all ipv4, connect via public ip, use same credential as dbuser_monitor
restapi:
  listen: 0.0.0.0:{{ patroni_port }}
  connect_address: {{ inventory_hostname }}:{{ patroni_port }}
  authentication:
    verify_client: none                 # none|optional|required
    username: {{ pg_monitor_username }}
    password: '{{ pg_monitor_password }}'

#------------------------------------------------------------------------------
# ctl
#------------------------------------------------------------------------------
ctl:
  optional:
    insecure: true
    # cacert: '/path/to/ca/cert'
    # certfile: '/path/to/cert/file'
    # keyfile: '/path/to/key/file'

#------------------------------------------------------------------------------
# tags
#------------------------------------------------------------------------------
tags:
  nofailover: false
  clonefrom: true
  noloadbalance: false
  nosync: false
{% if pg_upstream is defined %}
  replicatefrom: {{ pg_upstream }}    # clone from another replica rather than primary
{% endif %}

#------------------------------------------------------------------------------
# watchdog
#------------------------------------------------------------------------------
# available mode: off|automatic|required
watchdog:
  mode: {{ patroni_watchdog_mode }}
  device: /dev/watchdog
  # safety_margin: 10s

#------------------------------------------------------------------------------
# bootstrap
#------------------------------------------------------------------------------
bootstrap:

  #----------------------------------------------------------------------------
  # bootstrap method
  #----------------------------------------------------------------------------
  method: initdb
  # add custom bootstrap method here

  # default bootstrap method: initdb
  initdb:
    - locale: C
    - encoding: UTF8
    # - data-checksums    # enable data-checksum


  #----------------------------------------------------------------------------
  # bootstrap users
  #---------------------------------------------------------------------------
  # additional users which need to be created after initializing new cluster
  # replication user and monitor user are required
  users:
    {{ pg_replication_username }}:
      password: '{{ pg_replication_password }}'
    {{ pg_monitor_username }}:
      password: '{{ pg_monitor_password }}'
    {{ pg_admin_username }}:
      password: '{{ pg_admin_password }}'

  # bootstrap hba, allow local and intranet password access & replication
  # will be overwritten later
  pg_hba:
    - local   all             postgres                                ident
    - local   all             all                                     md5
    - host    all             all            0.0.0.0/0                md5
    - local   replication     postgres                                ident
    - local   replication     all                                     md5
    - host    replication     all            0.0.0.0/0                md5


  #----------------------------------------------------------------------------
  # template
  #---------------------------------------------------------------------------
  # post_init: /pg/bin/pg-init

  #----------------------------------------------------------------------------
  # bootstrap config
  #---------------------------------------------------------------------------
  # this section will be written to /{{ pg_namespace }}/{{ pg_cluster }}/config
  # if will NOT take any effect after cluster bootstrap
  dcs:

{% if pg_role == 'primary' and pg_upstream is defined %}
    #----------------------------------------------------------------------------
    # standby cluster definition
    #---------------------------------------------------------------------------
    standby_cluster:
      host: {{ pg_upstream }}
      port: {{ pg_port }}
      # primary_slot_name: patroni     # must be create manually on upstream server, if specified
      create_replica_methods:
        - basebackup
{% endif %}

    #----------------------------------------------------------------------------
    # important parameters
    #---------------------------------------------------------------------------
    # constraint: ttl >: loop_wait + retry_timeout * 2

    # the number of seconds the loop will sleep. Default value: 10
    # this is patroni check loop interval
    loop_wait: 10

    # the TTL to acquire the leader lock (in seconds). Think of it as the length of time before initiation of the automatic failover process. Default value: 30
    # config this according to your network condition to avoid false-positive failover
    ttl: 30

    # timeout for DCS and PostgreSQL operation retries (in seconds). DCS or network issues shorter than this will not cause Patroni to demote the leader. Default value: 10
    retry_timeout: 10

    # the amount of time a master is allowed to recover from failures before failover is triggered (in seconds)
    # Max RTO: 2 loop wait + master_start_timeout
    master_start_timeout: 10

    # import: candidate will not be promoted if replication lag is higher than this
    # maximum RPO: 1MB
    maximum_lag_on_failover: 1048576

    # The number of seconds Patroni is allowed to wait when stopping Postgres and effective only when synchronous_mode is enabled
    master_stop_timeout: 30

    # turns on synchronous replication mode. In this mode a replica will be chosen as synchronous and only the latest leader and synchronous replica are able to participate in leader election
    # set to true for RPO mode
    synchronous_mode: false

    # prevents disabling synchronous replication if no synchronous replicas are available, blocking all client writes to the master
    synchronous_mode_strict: false


    #----------------------------------------------------------------------------
    # postgres parameters
    #---------------------------------------------------------------------------
    postgresql:
      use_slots: true
      use_pg_rewind: true
      remove_data_directory_on_rewind_failure: true


      parameters:
        #----------------------------------------------------------------------
        # IMPORTANT PARAMETERS
        #----------------------------------------------------------------------
        max_connections: 400                    # 100 -> 400
        superuser_reserved_connections: 10      # reserve 10 connection for su
        max_locks_per_transaction: 128          # 64 -> 128
        max_prepared_transactions: 0            # 0 disable 2PC
        track_commit_timestamp: on              # enabled xact timestamp
        max_worker_processes: 8                 # default 8, set to cpu core
        wal_level: logical                      # logical
        wal_log_hints: on                       # wal log hints to support rewind
        max_wal_senders: 16                     # 10 -> 16
        max_replication_slots: 16               # 10 -> 16
        wal_keep_size: 100GB                    # keep at least 100GB WAL
        password_encryption: md5                # use traditional md5 auth

        #----------------------------------------------------------------------
        # RESOURCE USAGE (except WAL)
        #----------------------------------------------------------------------
        # memory: shared_buffers and maintenance_work_mem will be dynamically set
        shared_buffers: {{ pg_shared_buffers }}
        maintenance_work_mem: {{ pg_maintenance_work_mem }}
        work_mem: 32MB                          # 4MB -> 32MB
        huge_pages: try                         # try huge pages
        temp_file_limit: 100GB                  # 0 -> 100GB
        vacuum_cost_delay: 2ms                  # wait 2ms per 10000 cost
        vacuum_cost_limit: 10000                # 10000 cost each round
        bgwriter_delay: 10ms                    # check dirty page every 10ms
        bgwriter_lru_maxpages: 800              # 100 -> 800
        bgwriter_lru_multiplier: 5.0            # 2.0 -> 5.0  more cushion buffer

        #----------------------------------------------------------------------
        # WAL
        #----------------------------------------------------------------------
        wal_buffers: 16MB                       # max to 16MB
        wal_writer_delay: 20ms                  # wait period
        wal_writer_flush_after: 1MB             # max allowed data loss
        min_wal_size: 100GB                     # at least 100GB WAL
        max_wal_size: 400GB                     # at most 400GB WAL
        commit_delay: 20                        # 200ms -> 20ms, increase speed
        commit_siblings: 10                     # 5 -> 10
        checkpoint_timeout: 60min               # checkpoint 5min -> 1h
        checkpoint_completion_target: 0.95      # 0.5 -> 0.95

        #----------------------------------------------------------------------
        # REPLICATION
        #----------------------------------------------------------------------
        # synchronous_standby_names: ''
        vacuum_defer_cleanup_age: 50000         # 0->50000 last 50000 xact changes will not be vacuumed
        promote_trigger_file: promote.signal    # default promote trigger file path
        max_standby_archive_delay: 10min        # max delay before canceling queries when reading WAL from archive;
        max_standby_streaming_delay: 3min       # max delay before canceling queries when reading streaming WAL;
        wal_receiver_status_interval: 1s        # send replies at least this often
        hot_standby_feedback: on                # send info from standby to prevent query conflicts
        wal_receiver_timeout: 60s               # time that receiver waits for
        max_logical_replication_workers: 8      # 4 -> 8
        max_sync_workers_per_subscription: 8    # 4 -> 8

        #----------------------------------------------------------------------
        # QUERY TUNING
        #----------------------------------------------------------------------
        # planner
        # enable_partitionwise_join: on
        random_page_cost: 1.1                   # 4 for HDD, 1.1 for SSD
        effective_cache_size: 320GB             # max mem - shared buffer
        default_statistics_target: 1000         # stat bucket 100 -> 1000

        #----------------------------------------------------------------------
        # REPORTING AND LOGGING
        #----------------------------------------------------------------------
        log_destination: csvlog                 # use standard csv log
        logging_collector: on                   # enable csvlog
        log_directory: log                      # default log dir: /pg/data/log
        # log_filename: 'postgresql-%a.log'     # weekly auto-recycle
        log_filename: 'postgresql-%Y-%m-%d.log' # YYYY-MM-DD full log retention
        log_checkpoints: on                     # log checkpoint info
        log_lock_waits: on                      # log lock wait info
        log_replication_commands: on            # log replication info
        log_statement: ddl                      # log ddl change
        log_min_duration_statement: 100         # log slow query (>100ms)

        #----------------------------------------------------------------------
        # STATISTICS
        #----------------------------------------------------------------------
        track_io_timing: on                     # collect io statistics
        track_functions: all                    # track all functions (none|pl|all)
        track_activity_query_size: 8192         # max query length in pg_stat_activity

        #----------------------------------------------------------------------
        # AUTOVACUUM
        #----------------------------------------------------------------------
        log_autovacuum_min_duration: 1s         # log autovacuum activity take more than 1s
        autovacuum_max_workers: 3               # default autovacuum worker 3
        autovacuum_naptime: 1min                # default autovacuum naptime 1min
        autovacuum_vacuum_scale_factor: 0.08    # fraction of table size before vacuum   20% -> 8%
        autovacuum_analyze_scale_factor: 0.04   # fraction of table size before analyze  10% -> 4%
        autovacuum_vacuum_cost_delay: -1        # default vacuum cost delay: same as vacuum_cost_delay
        autovacuum_vacuum_cost_limit: -1        # default vacuum cost limit: same as vacuum_cost_limit
        autovacuum_freeze_max_age: 100000000    # age > 1 billion triggers force vacuum

        #----------------------------------------------------------------------
        # CLIENT
        #----------------------------------------------------------------------
        deadlock_timeout: 50ms                  # 50ms for deadlock
        idle_in_transaction_session_timeout: 10min  # 10min timeout for idle in transaction

        #----------------------------------------------------------------------
        # CUSTOMIZED OPTIONS
        #----------------------------------------------------------------------
        # extensions
        shared_preload_libraries: '{{ pg_shared_libraries | default("pg_stat_statements, auto_explain") }}'

        # auto_explain
        auto_explain.log_min_duration: 1s       # auto explain query slower than 1s
        auto_explain.log_analyze: true          # explain analyze
        auto_explain.log_verbose: true          # explain verbose
        auto_explain.log_timing: true           # explain timing
        auto_explain.log_nested_statements: true

        # pg_stat_statements
        pg_stat_statements.max: 10000           # 5000 -> 10000 queries
        pg_stat_statements.track: all           # track all statements (all|top|none)
        pg_stat_statements.track_utility: off   # do not track query other than CRUD
        pg_stat_statements.track_planning: off  # do not track planning metrics


#------------------------------------------------------------------------------
# postgres
#------------------------------------------------------------------------------
postgresql:

  #----------------------------------------------------------------------------
  # how to connect to postgres
  #----------------------------------------------------------------------------
  bin_dir: {{ pg_bin_dir }}
  data_dir: {{ pg_data }}
  config_dir: {{ pg_data }}
  pgpass: {{ pg_dbsu_home }}/.pgpass
  listen: {{ pg_listen }}:{{ pg_port }}
  connect_address: {{ inventory_hostname }}:{{ pg_port }}
  use_unix_socket: true # default: /var/run/postgresql, /tmp

  #----------------------------------------------------------------------------
  # who to connect to postgres
  #----------------------------------------------------------------------------
  authentication:
    superuser:
      username: {{ pg_dbsu }}
    replication:
      username: {{ pg_replication_username }}
      password: '{{ pg_replication_password }}'
    rewind:
      username: {{ pg_replication_username }}
      password: '{{ pg_replication_password }}'

  #----------------------------------------------------------------------------
  # how to react to database operations
  #----------------------------------------------------------------------------
  # event callback script log: /pg/log/callback.log
  callbacks:
    on_start: /pg/bin/pg-failover-callback
    on_stop: /pg/bin/pg-failover-callback
    on_reload: /pg/bin/pg-failover-callback
    on_restart: /pg/bin/pg-failover-callback
    on_role_change: /pg/bin/pg-failover-callback

  # rewind policy: data checksum should be enabled before using rewind
  use_pg_rewind: true
  remove_data_directory_on_rewind_failure: true
  remove_data_directory_on_diverged_timelines: false

  #----------------------------------------------------------------------------
  # how to create replica
  #----------------------------------------------------------------------------
  # create replica method: default pg_basebackup
  create_replica_methods:
    - basebackup
  basebackup:
    - max-rate: '1000M'
    - checkpoint: fast
    - status-interva: 1s
    - verbose
    - progress

  #----------------------------------------------------------------------------
  # ad hoc parameters (overwrite with default)
  #----------------------------------------------------------------------------
  # parameters:

  #----------------------------------------------------------------------------
  # host based authentication, bootstrap only
  #----------------------------------------------------------------------------
  pg_hba:
    - local   all             postgres                                ident
    - local   all             all                                     md5
    - host    all             all            0.0.0.0/0                md5
    - local   replication     postgres                                ident
    - local   replication     all                                     md5
    - host    replication     all            0.0.0.0/0                md5

...

4.4 - Customize Template

Customize business database

Parameters

您可以使用以下参数，对集群中的业务数据库进行初始化与定制。

pg-init-business.sql 用于初始化其他业务数据库的脚本模板


#------------------------------------------------------------------------------
# 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: []