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

部署

如何供给Pigsty的高可用数据库集群

1: 准备资源

1.1: Vagrant供给
1.2: 离线安装

2: 修改配置

2.1: 配置说明
2.2: 连接参数
2.3: 本地源参数
2.4: 节点参数
2.5: 元节点参数
2.6: 元数据库参数
2.7: PG安装参数
2.8: PG供给参数
2.9: PG模板
2.10: 监控系统参数
2.11: 负载均衡参数

3: 执行剧本

3.1: 基础设施初始化
3.2: 拉起数据库集群
3.3: 沙箱初始化

4: 定制

4.1: 定制Patroni
4.2: 定制业务数据库
4.3: 访问控制模型
4.4: 定制模板数据库

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

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

准备资源
- 所有机器都可以通过SSH免密登陆
- 所有机器登陆的用户都可以免密码进行sudo
- 选择1（或更多）台机器作为中央控制管理机（元节点）
- 在元节点上安装ansible
- 在元节点上克隆本项目
- 下载离线软件安装包至files目录（可选）
修改配置
- 根据环境情况，调整基础设施相关配置
- 按照业务需求，声明所需的数据库集群规格
- 可选特殊需求，定制数据库集群模板
- 检查配置文件
执行剧本
- 检查配置清单，选择执行对象，执行范围。
- 执行infra.yml初始化基础设施，通常基础设施只需要初始化一次即可。
- 执行initdb.yml初始化数据库集群，并通过监控系统验收数据库集群

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

1 - 准备资源

如何完成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供给

如何基于Vagrant与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 - 离线安装

如何离线安装Pigsty

离线安装包（可选）

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 - 修改配置

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

修改配置

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

2.1 - 配置说明

Pigsty配置文件的结构

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

配置使用

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

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

整体结构

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

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

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

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

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

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

集群定义

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

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

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

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

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

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

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

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

变量合并

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

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

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

inventory file or script group vars 2

inventory group_vars/all 3

playbook group_vars/all 3

inventory group_vars/* 3

playbook group_vars/* 3

inventory file or script host vars 2

inventory host_vars/* 3

playbook host_vars/* 3

host facts / cached set_facts 4

play vars

play vars_prompt

play vars_files

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

block vars (only for tasks in block)

task vars (only for the task)

include_vars

set_facts / registered vars

role (and include_role) params

include params

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

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

group_vars/all

全局变量，定义于all.vars

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

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

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

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

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

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

./initdb.yml -e pg_exists_action=clean

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

配置项（变量）

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

详细列表如下：

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

2.2 - 连接参数

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

参数概览

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

参数详解

proxy_env

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

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

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

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

ansible_host

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

Ansible中关于SSH连接的参数

ansible_host

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

ansible_port

The ssh port number, if not 22

ansible_user

The default ssh user name to use.

ansible_ssh_pass

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

ansible_ssh_private_key_file

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

ansible_ssh_common_args

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

ansible_sftp_extra_args

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

ansible_scp_extra_args

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

ansible_ssh_extra_args

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

ansible_ssh_pipelining

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

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

2.3 - 本地源参数

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

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

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

参数概览

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 - 节点参数

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

参数概览

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

默认配置

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

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

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

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

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

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

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

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

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

参数详解

2.5 - 元节点参数

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

参数概览

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

默认参数

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

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

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

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

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

参数详解

2.6 - 元数据库参数

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

参数概览

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

默认参数

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

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

参数详解

2.7 - PG安装参数

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

参数概览

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

默认参数

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

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

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

参数详解

2.8 - PG供给参数

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

参数概览

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

默认参数

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

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

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

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

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

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

参数详解

2.9 - PG模板

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

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

数据库初始化模板

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

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

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

数据库初始化脚本

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

参数概览

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

默认参数

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

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

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

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

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

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

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

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

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

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


# object created by dbsu and admin will have their privileges properly set
pg_default_privilegs:
  - GRANT USAGE                         ON SCHEMAS   TO dbrole_readonly
  - GRANT SELECT                        ON TABLES    TO dbrole_readonly
  - GRANT SELECT                        ON SEQUENCES TO dbrole_readonly
  - GRANT EXECUTE                       ON FUNCTIONS TO dbrole_readonly
  - GRANT INSERT, UPDATE, DELETE        ON TABLES    TO dbrole_readwrite
  - GRANT USAGE,  UPDATE                ON SEQUENCES TO dbrole_readwrite
  - GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES    TO dbrole_admin
  - GRANT CREATE                        ON SCHEMAS   TO dbrole_admin
  - GRANT USAGE                         ON TYPES     TO dbrole_admin

# schemas
pg_default_schemas: [monitor]

# extension
pg_default_extensions:
  - { name: 'pg_stat_statements',  schema: 'monitor' }
  - { name: 'pgstattuple',         schema: 'monitor' }
  - { name: 'pg_qualstats',        schema: 'monitor' }
  - { name: 'pg_buffercache',      schema: 'monitor' }
  - { name: 'pageinspect',         schema: 'monitor' }
  - { name: 'pg_prewarm',          schema: 'monitor' }
  - { name: 'pg_visibility',       schema: 'monitor' }
  - { name: 'pg_freespacemap',     schema: 'monitor' }
  - { name: 'pg_repack',           schema: 'monitor' }
  - name: postgres_fdw
  - name: file_fdw
  - name: btree_gist
  - name: btree_gin
  - name: pg_trgm
  - name: intagg
  - name: intarray

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

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

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

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

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

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

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

参数详解

2.10 - 监控系统参数

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

参数概览

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

默认参数

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

参数详解

2.11 - 负载均衡参数

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

参数概览

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

vip_enabled
vip_address
vip_cidrmask
vip_interface

默认参数

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

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

参数详解

3 - 执行剧本

如何利用Pigsty提供的剧本完成完整的初始化。

Pigsty采用声明式接口，配置完成之后只需运行固定的 剧本（Playbook），即可完成部署

部署分为两部分：

基础设施部署：在元节点上部署Pigsty所需的基础设施
PG集群部署：在其他机器上部署所需的高可用PostgreSQL数据库集群

此外，针对本地沙箱环境，Pigsty提供了特殊的快速一键部署脚本

沙箱环境部署：采用交织式部署，一键同时完成基础设施与PG集群的部署任务。

3.1 - 基础设施初始化

如何使用剧本初始化基础设施

剧本概览

Pigsty需要首先完成元节点的基础设施部署，基础设施的部署通过infra.yml完成。

./infra.yml

注意事项

您必须完成元节点的初始化后，才能正常执行普通节点的初始化工作。

infra.yml 固定会作用于配置文件中名为 meta 的分组

您可以将元节点当成普通节点复用，即在元节点上定义并创建PostgreSQL数据库。

我们建议按照默认配置在元节点上创建一个pg-meta元数据库，但不要施加过多负载。

完整执行一遍初始化流程可能花费2～8分钟，视您的机器配置而定。

选择性执行

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

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

./infra.yml --tags=repo

具体的标签请参考任务详情

剧本说明

infra.yml 主要完成以下工作

部署本地源
完成元节点的初始化
完成Meta基础设施初始化
- CA基础设施
- DNS Nameserver
- Nginx
- Prometheus & Alertmanger
- Grafana
将Pigsty本体拷贝至元节点
在元节点上完成数据库初始化（可选）

#------------------------------------------------------------------------------
# init local yum repo (only run on meta nodes)
#------------------------------------------------------------------------------
- name: Init local repo
  become: yes
  hosts: meta
  gather_facts: no
  tags: repo
  roles:
    - repo
#------------------------------------------------------------------------------
# provision nodes
#------------------------------------------------------------------------------
- name: Provision Node
  become: yes
  hosts: meta
  gather_facts: no
  tags: node
  roles:
    - node
#------------------------------------------------------------------------------
# init meta service (only run on meta nodes)
#------------------------------------------------------------------------------
- name: Init meta service
  become: yes
  hosts: meta
  gather_facts: no
  tags: meta
  roles:
    - role: ca
      tags: ca

    - role: nameserver
      tags: nameserver

    - role: nginx
      tags: nginx

    - role: prometheus
      tags: prometheus

    - role: grafana
      tags: grafana
#------------------------------------------------------------------------------
# init dcs on nodes
#------------------------------------------------------------------------------
- name: Init dcs
  become: yes
  hosts: meta
  gather_facts: no
  roles:
    - role: consul
      tags: dcs
#------------------------------------------------------------------------------
# copy scripts to meta node
#------------------------------------------------------------------------------
- name: Copy ansible scripts
  become: yes
  hosts: meta
  gather_facts: no
  ignore_errors: yes
  tags: ansible
  tasks:
    - name: Copy ansible scritps
      when: node_admin_setup is defined and node_admin_setup|bool and node_admin_username != ''
      block:
        # create copy of this repo
        - name: Create ansible tarball
          become: no
          connection: local
          run_once: true
          command:
            cmd: tar -cf files/meta.tgz roles templates ansible.cfg infra.yml pgsql.yml pgsql-rm.yml pigsty.yml Makefile
            chdir: "{{ playbook_dir }}"

        - name: Create ansible directory
          file: path="/home/{{ node_admin_username }}/meta" state=directory owner={{ node_admin_username }}

        - name: Copy ansible tarball
          copy: src="meta.tgz" dest="/home/{{ node_admin_username }}/meta/meta.tgz" owner={{ node_admin_username }}

        - name: Extract tarball
          shell: |
            cd /home/{{ node_admin_username }}/meta/
            tar -xf meta.tgz
            chown -R {{ node_admin_username }} /home/{{ node_admin_username }}
            rm -rf meta.tgz
            chmod a+x *.yml

任务详情

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

./infra.yml --list-tasks

默认任务如下：

playbook: ./infra.yml

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

  play #2 (meta): Provision Node	TAGS: [node]
    tasks:
      node : 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]
      node : Install node basic packages	TAGS: [node, node_pkgs]
      node : Install node extra packages	TAGS: [node, node_pkgs]
      node : Install meta specific packages	TAGS: [node, node_pkgs]
      node : Install node basic packages	TAGS: [node, node_pkgs]
      node : 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]
      node : 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]
      node : 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:
      ca : Create local ca directory	TAGS: [ca, ca_dir, meta]
      ca : Copy ca cert from local files	TAGS: [ca, ca_copy, meta]
      ca : Check ca key cert exists	TAGS: [ca, ca_create, meta]
      ca : Create self-signed CA key-cert	TAGS: [ca, ca_create, meta]
      nameserver : Make sure dnsmasq package installed	TAGS: [meta, nameserver]
      nameserver : Copy dnsmasq /etc/dnsmasq.d/config	TAGS: [meta, nameserver]
      nameserver : Add dynamic dns records to meta	TAGS: [meta, nameserver]
      nameserver : Launch meta dnsmasq service	TAGS: [meta, nameserver]
      nameserver : Wait for meta dnsmasq online	TAGS: [meta, nameserver]
      nginx : Make sure nginx package installed	TAGS: [meta, nginx, nginx_install]
      nginx : Create local html directory	TAGS: [meta, nginx, nginx_dir]
      nginx : Update default nginx index page	TAGS: [meta, nginx, nginx_dir]
      nginx : Copy nginx default config	TAGS: [meta, nginx, nginx_config]
      nginx : Copy nginx upstream conf	TAGS: [meta, nginx, nginx_config]
      nginx : Fetch haproxy facts	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      debug	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      nginx : Templating /etc/nginx/haproxy.conf	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      nginx : Templating haproxy.html	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      nginx : Launch nginx server	TAGS: [meta, nginx, nginx_reload]
      nginx : Restart meta nginx service	TAGS: [meta, nginx, nginx_launch]
      nginx : Wait for nginx service online	TAGS: [meta, nginx, nginx_launch]
      nginx : Make sure nginx exporter installed	TAGS: [meta, nginx, nginx_exporter]
      nginx : Config nginx_exporter options	TAGS: [meta, nginx, nginx_exporter]
      nginx : Restart nginx_exporter service	TAGS: [meta, nginx, nginx_exporter]
      nginx : Wait for nginx exporter online	TAGS: [meta, nginx, nginx_exporter]
      nginx : Register cosnul nginx service	TAGS: [meta, nginx, nginx_register]
      nginx : Register consul nginx-exporter service	TAGS: [meta, nginx, nginx_register]
      nginx : Reload consul	TAGS: [meta, nginx, nginx_register]
      prometheus : Install prometheus and alertmanager	TAGS: [meta, prometheus, prometheus_install]
      prometheus : Wipe out prometheus config dir	TAGS: [meta, prometheus, prometheus_clean]
      prometheus : Wipe out existing prometheus data	TAGS: [meta, prometheus, prometheus_clean]
      prometheus : Recreate prometheus data dir	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy /etc/prometheus configs	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy /etc/prometheus opts	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus scrape_interval	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus evaluation_interval	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus scrape_timeout	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus pg metrics path	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Launch prometheus service	TAGS: [meta, prometheus, prometheus_launch]
      prometheus : Launch alertmanager service	TAGS: [meta, prometheus, prometheus_launch]
      prometheus : Wait for prometheus online	TAGS: [meta, prometheus, prometheus_launch]
      prometheus : Wait for alertmanager online	TAGS: [meta, prometheus, prometheus_launch]
      grafana : Make sure grafana is installed	TAGS: [grafana, grafana_install, meta]
      grafana : Check grafana plugin cache exists	TAGS: [grafana, grafana_plugin, meta]
      grafana : Provision grafana plugins via cache	TAGS: [grafana, grafana_plugin, meta]
      grafana : Download grafana plugins from web	TAGS: [grafana, grafana_plugin, meta]
      grafana : Download grafana plugins from web	TAGS: [grafana, grafana_plugin, meta]
      grafana : Create grafana plugins cache	TAGS: [grafana, grafana_plugin, meta]
      grafana : Copy /etc/grafana/grafana.ini	TAGS: [grafana, grafana_config, meta]
      grafana : 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]
      grafana : Replace grafana icon with pigsty	TAGS: [grafana, grafana_config, grafana_customize, meta]
      grafana : Launch grafana service	TAGS: [grafana, grafana_launch, meta]
      grafana : Wait for grafana online	TAGS: [grafana, grafana_launch, meta]
      grafana : Update grafana default preferences	TAGS: [grafana, grafana_provision, meta]
      grafana : Register consul grafana service	TAGS: [grafana, grafana_register, meta]
      grafana : Reload consul	TAGS: [grafana, grafana_register, meta]

  play #4 (meta): Init dcs	TAGS: []
    tasks:
      consul : Check for existing consul	TAGS: [consul_check, dcs]
      consul : Consul exists flag fact set	TAGS: [consul_check, dcs]
      consul : Abort due to consul exists	TAGS: [consul_check, dcs]
      consul : Clean existing consul instance	TAGS: [consul_check, dcs]
      consul : Stop any running consul instance	TAGS: [consul_check, dcs]
      consul : Remove existing consul dir	TAGS: [consul_check, dcs]
      consul : Recreate consul dir	TAGS: [consul_check, dcs]
      consul : Make sure consul is installed	TAGS: [consul_install, dcs]
      consul : 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]
      consul : Copy /etc/consul.d/consul.json	TAGS: [consul_config, dcs]
      consul : Copy consul agent service	TAGS: [consul_config, dcs]
      consul : Get dcs bootstrap expect quroum	TAGS: [consul_server, dcs]
      consul : Copy consul server service unit	TAGS: [consul_server, dcs]
      consul : Launch consul server service	TAGS: [consul_server, dcs]
      consul : Wait for consul server online	TAGS: [consul_server, dcs]
      consul : Launch consul agent service	TAGS: [consul_agent, dcs]
      consul : 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 - 拉起数据库集群

如何定义并拉起PostgreSQL数据库集群

剧本概览

完成了基础设施部署后，您可以通过pgsql.yml完成数据库集群的部署。

您需要首先在 Pigsty配置文件完成数据库集群的定义，然后通过执行pgsql.yml将变更具现化。

./pgsql.yml                      # 在所有清单中的机器上执行数据库集群初始化操作（危险！）
./pgsql.yml -l pg-test           # 在 pg-test 分组下的机器执行数据库集群初始化（推荐！）
./pgsql.yml -l pg-meta,pg-test   # 同时初始化pg-meta与pg-test两个集群
./pgsql.yml -l 10.10.10.11       # 初始化10.10.10.11这台机器上的数据库实例

注意事项

使用不带参数的pgsql.yml虽然很爽，但在生产环境中是一个高危操作

强烈建议您在执行时添加-l参数，限制命令执行的对象范围。
您可以将元节点当成普通节点复用，即在元节点上定义并创建PostgreSQL数据库。

默认沙箱环境中，执行./pgsql.yml会同时完成pg-meta与pg-test的初始化工作。
在从库初始化时，您必须确保主库必须已经完成初始化，或者主库与其他从库同时进行初始化亦可。

保护机制

pgsql.yml提供保护机制，由配置参数pg_exists_action决定。当执行剧本前会目标机器上有正在运行的PostgreSQL实例时，Pigsty会根据pg_exists_action的配置abort|clean|skip行动。

abort：建议设置为默认配置，如遇现存实例，中止剧本执行，避免误删库。
clean：建议在本地沙箱环境使用，如遇现存实例，清除已有数据库。
skip：直接在已有数据库集群上执行后续逻辑。
您可以通过./pgsql.yml -e pg_exists_action=clean的方式来覆盖配置文件选项，强制抹掉现有实例

选择性执行

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

例如，如果您只想执行负载均衡初始化的部分，则可以通过以下命令进行

./pgsql.yml --tags=haproxy

更多常用选择性执行的命令如下所示：

./pgsql.yml --tags=infra        # 完成基础设施的初始化，包括机器节点初始化与DCS部署
./pgsql.yml --tags=node         # 完成机器节点的初始化
./pgsql.yml --tags=dcs          # 完成DCS：consul/etcd的初始化
./pgsql.yml --tags=dcs -e dcs_exists_action # 完成consul/etcd的初始化，抹除已有的consul agent

./pgsql.yml --tags=pgsql        # 完成数据库与监控的部署
./pgsql.yml --tags=postgres     # 完成数据库部署
./pgsql.yml --tags=monitor      # 完成监控的部署
./pgsql.yml --tags=pg_biz_init,pg_biz_pgbouncer  # 修改数据库与用户定义并应用。

./pgsql.yml --tags=proxy        # 完成负载均衡器的部署，包括Haproxy与VIP
./pgsql.yml --tags=haproxy      # 完成Haproxy的部署
./pgsql.yml --tags=vip          # 完成VIP的部署
./pgsql.yml --tags=haproxy_config,haproxy_reload  # 修改Haproxy配置并应用。

剧本顶层标签

- name: Pgsql Initialization
  become: yes
  hosts: all
  gather_facts: no
  roles:
    - role: node                            # init node
      tags: [infra, node]
    - role: consul                          # init consul
      tags: [infra, dcs]
    - role: postgres                        # init postgres
      tags: [pgsql, postgres]
    - role: monitor                         # init monitor system
      tags: [pgsql, monitor]
    - role: haproxy                         # init haproxy
      tags: [proxy, haproxy]
    - role: vip                             # init haproxy
      tags: [proxy, vip]

精确到任务的标签请参考任务详情

剧本说明

infra.yml 主要完成以下工作

部署本地源
完成元节点的初始化
完成Meta基础设施初始化
- CA基础设施
- DNS Nameserver
- Nginx
- Prometheus & Alertmanger
- Grafana
将Pigsty本体拷贝至元节点
在元节点上完成数据库初始化（可选）

#------------------------------------------------------------------------------
# init local yum repo (only run on meta nodes)
#------------------------------------------------------------------------------
- name: Init local repo
  become: yes
  hosts: meta
  gather_facts: no
  tags: repo
  roles:
    - repo
#------------------------------------------------------------------------------
# provision nodes
#------------------------------------------------------------------------------
- name: Provision Node
  become: yes
  hosts: meta
  gather_facts: no
  tags: node
  roles:
    - node
#------------------------------------------------------------------------------
# init meta service (only run on meta nodes)
#------------------------------------------------------------------------------
- name: Init meta service
  become: yes
  hosts: meta
  gather_facts: no
  tags: meta
  roles:
    - role: ca
      tags: ca

    - role: nameserver
      tags: nameserver

    - role: nginx
      tags: nginx

    - role: prometheus
      tags: prometheus

    - role: grafana
      tags: grafana
#------------------------------------------------------------------------------
# init dcs on nodes
#------------------------------------------------------------------------------
- name: Init dcs
  become: yes
  hosts: meta
  gather_facts: no
  roles:
    - role: consul
      tags: dcs
#------------------------------------------------------------------------------
# copy scripts to meta node
#------------------------------------------------------------------------------
- name: Copy ansible scripts
  become: yes
  hosts: meta
  gather_facts: no
  ignore_errors: yes
  tags: ansible
  tasks:
    - name: Copy ansible scritps
      when: node_admin_setup is defined and node_admin_setup|bool and node_admin_username != ''
      block:
        # create copy of this repo
        - name: Create ansible tarball
          become: no
          connection: local
          run_once: true
          command:
            cmd: tar -cf files/meta.tgz roles templates ansible.cfg infra.yml pgsql.yml pgsql-rm.yml pigsty.yml Makefile
            chdir: "{{ playbook_dir }}"

        - name: Create ansible directory
          file: path="/home/{{ node_admin_username }}/meta" state=directory owner={{ node_admin_username }}

        - name: Copy ansible tarball
          copy: src="meta.tgz" dest="/home/{{ node_admin_username }}/meta/meta.tgz" owner={{ node_admin_username }}

        - name: Extract tarball
          shell: |
            cd /home/{{ node_admin_username }}/meta/
            tar -xf meta.tgz
            chown -R {{ node_admin_username }} /home/{{ node_admin_username }}
            rm -rf meta.tgz
            chmod a+x *.yml

任务详情

使用以下命令可以列出所有任务，以及可以使用的标签：

./pgsql.yml --list-tasks

默认任务如下：

playbook: ./pgsql.yml

  play #1 (all): Pgsql Initialization	TAGS: []
    tasks:
      node : Update node hostname	TAGS: [infra, node, node_name]
      node : Add new hostname to /etc/hosts	TAGS: [infra, node, node_name]
      node : Write static dns records	TAGS: [infra, node, node_dns]
      node : Get old nameservers	TAGS: [infra, node, node_resolv]
      node : Truncate resolv file	TAGS: [infra, node, node_resolv]
      node : Write resolv options	TAGS: [infra, node, node_resolv]
      node : Add new nameservers	TAGS: [infra, node, node_resolv]
      node : Append old nameservers	TAGS: [infra, node, node_resolv]
      node : Node configure disable firewall	TAGS: [infra, node, node_firewall]
      node : Node disable selinux by default	TAGS: [infra, node, node_firewall]
      node : Backup existing repos	TAGS: [infra, node, node_repo]
      node : Install upstream repo	TAGS: [infra, node, node_repo]
      node : Install local repo	TAGS: [infra, node, node_repo]
      node : Install node basic packages	TAGS: [infra, node, node_pkgs]
      node : Install node extra packages	TAGS: [infra, node, node_pkgs]
      node : Install meta specific packages	TAGS: [infra, node, node_pkgs]
      node : Install node basic packages	TAGS: [infra, node, node_pkgs]
      node : Install node extra packages	TAGS: [infra, node, node_pkgs]
      node : Install meta specific packages	TAGS: [infra, node, node_pkgs]
      node : Node configure disable numa	TAGS: [infra, node, node_feature]
      node : Node configure disable swap	TAGS: [infra, node, node_feature]
      node : Node configure unmount swap	TAGS: [infra, node, node_feature]
      node : Node setup static network	TAGS: [infra, node, node_feature]
      node : Node configure disable firewall	TAGS: [infra, node, node_feature]
      node : Node configure disk prefetch	TAGS: [infra, node, node_feature]
      node : Enable linux kernel modules	TAGS: [infra, node, node_kernel]
      node : Enable kernel module on reboot	TAGS: [infra, node, node_kernel]
      node : Get config parameter page count	TAGS: [infra, node, node_tuned]
      node : Get config parameter page size	TAGS: [infra, node, node_tuned]
      node : Tune shmmax and shmall via mem	TAGS: [infra, node, node_tuned]
      node : Create tuned profiles	TAGS: [infra, node, node_tuned]
      node : Render tuned profiles	TAGS: [infra, node, node_tuned]
      node : Active tuned profile	TAGS: [infra, node, node_tuned]
      node : Change additional sysctl params	TAGS: [infra, node, node_tuned]
      node : Copy default user bash profile	TAGS: [infra, node, node_profile]
      node : Setup node default pam ulimits	TAGS: [infra, node, node_ulimit]
      node : Create os user group admin	TAGS: [infra, node, node_admin]
      node : Create os user admin	TAGS: [infra, node, node_admin]
      node : Grant admin group nopass sudo	TAGS: [infra, node, node_admin]
      node : Add no host checking to ssh config	TAGS: [infra, node, node_admin]
      node : Add admin ssh no host checking	TAGS: [infra, node, node_admin]
      node : Fetch all admin public keys	TAGS: [infra, node, node_admin]
      node : Exchange all admin ssh keys	TAGS: [infra, node, node_admin]
      node : Install public keys	TAGS: [infra, node, node_admin]
      node : Install ntp package	TAGS: [infra, node, ntp_install]
      node : Install chrony package	TAGS: [infra, node, ntp_install]
      node : Setup default node timezone	TAGS: [infra, node, ntp_config]
      node : Copy the ntp.conf file	TAGS: [infra, node, ntp_config]
      node : Copy the chrony.conf template	TAGS: [infra, node, ntp_config]
      node : Launch ntpd service	TAGS: [infra, node, ntp_launch]
      node : Launch chronyd service	TAGS: [infra, node, ntp_launch]
      consul : Check for existing consul	TAGS: [consul_check, dcs, infra]
      consul : Consul exists flag fact set	TAGS: [consul_check, dcs, infra]
      consul : Abort due to consul exists	TAGS: [consul_check, dcs, infra]
      consul : Clean existing consul instance	TAGS: [consul_check, dcs, infra]
      consul : Stop any running consul instance	TAGS: [consul_check, dcs, infra]
      consul : Remove existing consul dir	TAGS: [consul_check, dcs, infra]
      consul : Recreate consul dir	TAGS: [consul_check, dcs, infra]
      consul : Make sure consul is installed	TAGS: [consul_install, dcs, infra]
      consul : Make sure consul dir exists	TAGS: [consul_config, dcs, infra]
      consul : Get dcs server node names	TAGS: [consul_config, dcs, infra]
      consul : Get dcs node name from var	TAGS: [consul_config, dcs, infra]
      consul : Get dcs node name from var	TAGS: [consul_config, dcs, infra]
      consul : Fetch hostname as dcs node name	TAGS: [consul_config, dcs, infra]
      consul : Get dcs name from hostname	TAGS: [consul_config, dcs, infra]
      consul : Copy /etc/consul.d/consul.json	TAGS: [consul_config, dcs, infra]
      consul : Copy consul agent service	TAGS: [consul_config, dcs, infra]
      consul : Get dcs bootstrap expect quroum	TAGS: [consul_server, dcs, infra]
      consul : Copy consul server service unit	TAGS: [consul_server, dcs, infra]
      consul : Launch consul server service	TAGS: [consul_server, dcs, infra]
      consul : Wait for consul server online	TAGS: [consul_server, dcs, infra]
      consul : Launch consul agent service	TAGS: [consul_agent, dcs, infra]
      consul : Wait for consul agent online	TAGS: [consul_agent, dcs, infra]
      postgres : Create os group postgres	TAGS: [instal, pg_dbsu, pgsql, postgres]
      postgres : Make sure dcs group exists	TAGS: [instal, pg_dbsu, pgsql, postgres]
      postgres : Create dbsu {{ pg_dbsu }}	TAGS: [instal, pg_dbsu, pgsql, postgres]
      postgres : Grant dbsu nopass sudo	TAGS: [instal, pg_dbsu, pgsql, postgres]
      postgres : Grant dbsu all sudo	TAGS: [instal, pg_dbsu, pgsql, postgres]
      postgres : Grant dbsu limited sudo	TAGS: [instal, pg_dbsu, pgsql, postgres]
      postgres : Config patroni watchdog support	TAGS: [instal, pg_dbsu, pgsql, postgres]
      postgres : Add dbsu ssh no host checking	TAGS: [instal, pg_dbsu, pgsql, postgres]
      postgres : Fetch dbsu public keys	TAGS: [instal, pg_dbsu, pgsql, postgres]
      postgres : Exchange dbsu ssh keys	TAGS: [instal, pg_dbsu, pgsql, postgres]
      postgres : Install offical pgdg yum repo	TAGS: [instal, pg_install, pgsql, postgres]
      postgres : Install pg packages	TAGS: [instal, pg_install, pgsql, postgres]
      postgres : Install pg extensions	TAGS: [instal, pg_install, pgsql, postgres]
      postgres : Link /usr/pgsql to current version	TAGS: [instal, pg_install, pgsql, postgres]
      postgres : Add pg bin dir to profile path	TAGS: [instal, pg_install, pgsql, postgres]
      postgres : Fix directory ownership	TAGS: [instal, pg_install, pgsql, postgres]
      postgres : Remove default postgres service	TAGS: [instal, pg_install, pgsql, postgres]
      postgres : Check necessary variables exists	TAGS: [always, pg_preflight, pgsql, postgres, preflight]
      postgres : Fetch variables via pg_cluster	TAGS: [always, pg_preflight, pgsql, postgres, preflight]
      postgres : Set cluster basic facts for hosts	TAGS: [always, pg_preflight, pgsql, postgres, preflight]
      postgres : Assert cluster primary singleton	TAGS: [always, pg_preflight, pgsql, postgres, preflight]
      postgres : Setup cluster primary ip address	TAGS: [always, pg_preflight, pgsql, postgres, preflight]
      postgres : Setup repl upstream for primary	TAGS: [always, pg_preflight, pgsql, postgres, preflight]
      postgres : Setup repl upstream for replicas	TAGS: [always, pg_preflight, pgsql, postgres, preflight]
      postgres : Debug print instance summary	TAGS: [always, pg_preflight, pgsql, postgres, preflight]
      postgres : Check for existing postgres instance	TAGS: [pg_check, pgsql, postgres, prepare]
      postgres : Set fact whether pg port is open	TAGS: [pg_check, pgsql, postgres, prepare]
      postgres : Abort due to existing postgres instance	TAGS: [pg_check, pgsql, postgres, prepare]
      postgres : Clean existing postgres instance	TAGS: [pg_check, pgsql, postgres, prepare]
      postgres : Shutdown existing postgres service	TAGS: [pg_clean, pgsql, postgres, prepare]
      postgres : Remove registerd consul service	TAGS: [pg_clean, pgsql, postgres, prepare]
      postgres : Remove postgres metadata in consul	TAGS: [pg_clean, pgsql, postgres, prepare]
      postgres : Remove existing postgres data	TAGS: [pg_clean, pgsql, postgres, prepare]
      postgres : Make sure main and backup dir exists	TAGS: [pg_dir, pgsql, postgres, prepare]
      postgres : Create postgres directory structure	TAGS: [pg_dir, pgsql, postgres, prepare]
      postgres : Create pgbouncer directory structure	TAGS: [pg_dir, pgsql, postgres, prepare]
      postgres : Create links from pgbkup to pgroot	TAGS: [pg_dir, pgsql, postgres, prepare]
      postgres : Create links from current cluster	TAGS: [pg_dir, pgsql, postgres, prepare]
      postgres : Copy pg_cluster to /pg/meta/cluster	TAGS: [pg_meta, pgsql, postgres, prepare]
      postgres : Copy pg_version to /pg/meta/version	TAGS: [pg_meta, pgsql, postgres, prepare]
      postgres : Copy pg_instance to /pg/meta/instance	TAGS: [pg_meta, pgsql, postgres, prepare]
      postgres : Copy pg_seq to /pg/meta/sequence	TAGS: [pg_meta, pgsql, postgres, prepare]
      postgres : Copy pg_role to /pg/meta/role	TAGS: [pg_meta, pgsql, postgres, prepare]
      postgres : Copy postgres scripts to /pg/bin/	TAGS: [pg_scripts, pgsql, postgres, prepare]
      postgres : Copy alias profile to /etc/profile.d	TAGS: [pg_scripts, pgsql, postgres, prepare]
      postgres : Copy psqlrc to postgres home	TAGS: [pg_scripts, pgsql, postgres, prepare]
      postgres : Setup hostname to pg instance name	TAGS: [pg_hostname, pgsql, postgres, prepare]
      postgres : Copy consul node-meta definition	TAGS: [pg_nodemeta, pgsql, postgres, prepare]
      postgres : Restart consul to load new node-meta	TAGS: [pg_nodemeta, pgsql, postgres, prepare]
      postgres : Config patroni watchdog support	TAGS: [pg_watchdog, pgsql, postgres, prepare]
      postgres : Get config parameter page count	TAGS: [pg_config, pgsql, postgres]
      postgres : Get config parameter page size	TAGS: [pg_config, pgsql, postgres]
      postgres : Tune shared buffer and work mem	TAGS: [pg_config, pgsql, postgres]
      postgres : Hanlde small size mem occasion	TAGS: [pg_config, pgsql, postgres]
      postgres : Calculate postgres mem params	TAGS: [pg_config, pgsql, postgres]
      postgres : create patroni config dir	TAGS: [pg_config, pgsql, postgres]
      postgres : use predefined patroni template	TAGS: [pg_config, pgsql, postgres]
      postgres : Render default /pg/conf/patroni.yml	TAGS: [pg_config, pgsql, postgres]
      postgres : Link /pg/conf/patroni to /pg/bin/	TAGS: [pg_config, pgsql, postgres]
      postgres : Link /pg/bin/patroni.yml to /etc/patroni/	TAGS: [pg_config, pgsql, postgres]
      postgres : Config patroni watchdog support	TAGS: [pg_config, pgsql, postgres]
      postgres : create patroni systemd drop-in dir	TAGS: [pg_config, pgsql, postgres]
      postgres : Copy postgres systemd service file	TAGS: [pg_config, pgsql, postgres]
      postgres : create patroni systemd drop-in file	TAGS: [pg_config, pgsql, postgres]
      postgres : Render default initdb scripts	TAGS: [pg_config, pgsql, postgres]
      postgres : Launch patroni on primary instance	TAGS: [pg_primary, pgsql, postgres]
      postgres : Wait for patroni primary online	TAGS: [pg_primary, pgsql, postgres]
      postgres : Wait for postgres primary online	TAGS: [pg_primary, pgsql, postgres]
      postgres : Check primary postgres service ready	TAGS: [pg_primary, pgsql, postgres]
      postgres : Check replication connectivity to primary	TAGS: [pg_primary, pgsql, postgres]
      postgres : Render default pg-init scripts	TAGS: [pg_init, pg_init_config, pgsql, postgres]
      postgres : Render template init script	TAGS: [pg_init, pg_init_config, pgsql, postgres]
      postgres : Execute initialization scripts	TAGS: [pg_init, pgsql, postgres]
      postgres : Check primary instance ready	TAGS: [pg_init, pgsql, postgres]
      postgres : Add dbsu password to pgpass if exists	TAGS: [pg_pass, pgsql, postgres]
      postgres : Add system user to pgpass	TAGS: [pg_pass, pgsql, postgres]
      postgres : Check replication connectivity to primary	TAGS: [pg_replica, pgsql, postgres]
      postgres : Launch patroni on replica instances	TAGS: [pg_replica, pgsql, postgres]
      postgres : Wait for patroni replica online	TAGS: [pg_replica, pgsql, postgres]
      postgres : Wait for postgres replica online	TAGS: [pg_replica, pgsql, postgres]
      postgres : Check replica postgres service ready	TAGS: [pg_replica, pgsql, postgres]
      postgres : Render hba rules	TAGS: [pg_hba, pgsql, postgres]
      postgres : Reload hba rules	TAGS: [pg_hba, pgsql, postgres]
      postgres : Pause patroni	TAGS: [pg_patroni, pgsql, postgres]
      postgres : Stop patroni on replica instance	TAGS: [pg_patroni, pgsql, postgres]
      postgres : Stop patroni on primary instance	TAGS: [pg_patroni, pgsql, postgres]
      postgres : Launch raw postgres on primary	TAGS: [pg_patroni, pgsql, postgres]
      postgres : Launch raw postgres on primary	TAGS: [pg_patroni, pgsql, postgres]
      postgres : Wait for postgres online	TAGS: [pg_patroni, pgsql, postgres]
      postgres : Check pgbouncer is installed	TAGS: [pgbouncer, pgbouncer_check, pgsql, postgres]
      postgres : Stop existing pgbouncer service	TAGS: [pgbouncer, pgbouncer_clean, pgsql, postgres]
      postgres : Remove existing pgbouncer dirs	TAGS: [pgbouncer, pgbouncer_clean, pgsql, postgres]
      postgres : Recreate dirs with owner postgres	TAGS: [pgbouncer, pgbouncer_clean, pgsql, postgres]
      postgres : Copy /etc/pgbouncer/pgbouncer.ini	TAGS: [pgbouncer, pgbouncer_config, pgsql, postgres]
      postgres : Copy /etc/pgbouncer/pgb_hba.conf	TAGS: [pgbouncer, pgbouncer_config, pgsql, postgres]
      postgres : Touch userlist and database list	TAGS: [pgbouncer, pgbouncer_config, pgsql, postgres]
      postgres : Add default users to pgbouncer	TAGS: [pgbouncer, pgbouncer_config, pgsql, postgres]
      postgres : Copy pgbouncer systemd service	TAGS: [pgbouncer, pgbouncer_launch, pgsql, postgres]
      postgres : Launch pgbouncer pool service	TAGS: [pgbouncer, pgbouncer_launch, pgsql, postgres]
      postgres : Wait for pgbouncer service online	TAGS: [pgbouncer, pgbouncer_launch, pgsql, postgres]
      postgres : Check pgbouncer service is ready	TAGS: [pgbouncer, pgbouncer_launch, pgsql, postgres]
      postgres : Render business init script	TAGS: [business, pg_biz_config, pg_biz_init, pgsql, postgres]
      postgres : Render database baseline sql	TAGS: [business, pg_biz_config, pg_biz_init, pgsql, postgres]
      postgres : Execute business init script	TAGS: [business, pg_biz_init, pgsql, postgres]
      postgres : Execute database baseline sql	TAGS: [business, pg_biz_init, pgsql, postgres]
      postgres : Add pgbouncer busniess users	TAGS: [business, pg_biz_pgbouncer, pgsql, postgres]
      postgres : Add pgbouncer busniess database	TAGS: [business, pg_biz_pgbouncer, pgsql, postgres]
      postgres : Restart pgbouncer	TAGS: [business, pg_biz_pgbouncer, pgsql, postgres]
      postgres : Copy postgres service definition	TAGS: [pg_register, pgsql, postgres, register]
      postgres : Reload consul service	TAGS: [pg_register, pgsql, postgres, register]
      postgres : Render grafana datasource definition	TAGS: [pg_grafana, pgsql, postgres, register]
      postgres : Register datasource to grafana	TAGS: [pg_grafana, pgsql, postgres, register]
      monitor : Create /etc/pg_exporter conf dir	TAGS: [monitor, pg_exporter, pgsql]
      monitor : Copy default pg_exporter.yaml	TAGS: [monitor, pg_exporter, pgsql]
      monitor : Config /etc/default/pg_exporter	TAGS: [monitor, pg_exporter, pgsql]
      monitor : Config pg_exporter service unit	TAGS: [monitor, pg_exporter, pgsql]
      monitor : Launch pg_exporter systemd service	TAGS: [monitor, pg_exporter, pgsql]
      monitor : Wait for pg_exporter service online	TAGS: [monitor, pg_exporter, pgsql]
      monitor : Register pg-exporter consul service	TAGS: [monitor, pg_exporter, pgsql]
      monitor : Reload pg-exporter consul service	TAGS: [monitor, pg_exporter, pgsql]
      monitor : Config pgbouncer_exporter opts	TAGS: [monitor, pgbouncer_exporter, pgsql]
      monitor : Config pgbouncer_exporter service	TAGS: [monitor, pgbouncer_exporter, pgsql]
      monitor : Launch pgbouncer_exporter service	TAGS: [monitor, pgbouncer_exporter, pgsql]
      monitor : Wait for pgbouncer_exporter online	TAGS: [monitor, pgbouncer_exporter, pgsql]
      monitor : Register pgb-exporter consul service	TAGS: [monitor, pgbouncer_exporter, pgsql]
      monitor : Reload pgb-exporter consul service	TAGS: [monitor, pgbouncer_exporter, pgsql]
      monitor : Copy node_exporter systemd service	TAGS: [monitor, node_exporter, pgsql]
      monitor : Config default node_exporter options	TAGS: [monitor, node_exporter, pgsql]
      monitor : Launch node_exporter service unit	TAGS: [monitor, node_exporter, pgsql]
      monitor : Wait for node_exporter online	TAGS: [monitor, node_exporter, pgsql]
      monitor : Register node-exporter service	TAGS: [monitor, node_exporter, pgsql]
      monitor : Reload node-exporter consul service	TAGS: [monitor, node_exporter, pgsql]
      haproxy : Make sure haproxy is installed	TAGS: [haproxy, haproxy_install, proxy]
      haproxy : Create haproxy directory	TAGS: [haproxy, haproxy_install, proxy]
      haproxy : Copy haproxy systemd service file	TAGS: [haproxy, haproxy_install, haproxy_unit, proxy]
      haproxy : Fetch postgres cluster memberships	TAGS: [haproxy, haproxy_config, proxy]
      haproxy : Templating /etc/haproxy/haproxy.cfg	TAGS: [haproxy, haproxy_config, proxy]
      haproxy : Launch haproxy load balancer service	TAGS: [haproxy, haproxy_launch, haproxy_restart, proxy]
      haproxy : Wait for haproxy load balancer online	TAGS: [haproxy, haproxy_launch, proxy]
      haproxy : Reload haproxy load balancer service	TAGS: [haproxy, haproxy_reload, proxy]
      haproxy : Copy haproxy service definition	TAGS: [haproxy, haproxy_register, proxy]
      haproxy : Reload haproxy consul service	TAGS: [haproxy, haproxy_register, proxy]
      vip : Templating /etc/default/vip-manager.yml	TAGS: [proxy, vip]
      vip : create vip-manager. systemd drop-in dir	TAGS: [proxy, vip]
      vip : create vip-manager systemd drop-in file	TAGS: [proxy, vip]
      vip : Launch vip-manager	TAGS: [proxy, vip]

3.3 - 沙箱初始化

如何使用快速部署沙箱环境

常规初始化需要先完成元节点的初始化，再完成其他节点的初始化。

为了加快沙箱环境的初始化速度，Pigsty提供了专用于沙箱的初始化剧本，可以采用交织的方式一次性同时完成基础设施元节点和普通节点的初始化。这种方式确实很快，但我不建议在生产环境这样用。

剧本概览

您可以直接调用sandbox.yml或通过make init的快捷方式完成沙箱环境的一键初始化。

./sandbox.yml

注意事项

沙箱的具体注意事项与基础设施部署和 PG集群部署保持一致

剧本说明

sandbox.yml 主要是将infra.yml与pgsql.yml的工作交织在一起。

#------------------------------------------------------------------------------
# init local yum repo on meta node
#------------------------------------------------------------------------------
- name: Init local repo
  become: yes
  hosts: meta
  gather_facts: no
  tags: repo
  roles:
    - repo
#------------------------------------------------------------------------------
# provision all nodes
#------------------------------------------------------------------------------
# node provision depends on existing repo on meta node
- name: Provision Node
  become: yes
  hosts: all
  gather_facts: no
  tags: node
  roles:
    - node
#------------------------------------------------------------------------------
# init meta service on meta node
#------------------------------------------------------------------------------
# meta provision depends on node provision. You'll have to provision node on meta node
# then provision meta infrastructure on meta node
- name: Init meta service
  become: yes
  hosts: meta
  gather_facts: no
  tags: meta
  roles:
    - role: ca
      tags: ca
    - role: nameserver
      tags: nameserver
    - role: nginx
      tags: nginx
    - role: prometheus
      tags: prometheus
    - role: grafana
      tags: grafana
#------------------------------------------------------------------------------
# init dcs on nodes
#------------------------------------------------------------------------------
# typically you'll have to bootstrap dcs on meta node first (or use external dcs)
# but pigsty allows you to setup server and agent at the same time.
- name: Init dcs
  become: yes
  hosts: all            # provision all nodes or just meta nodes
  gather_facts: no
  roles:
    - role: consul
      tags: dcs
#------------------------------------------------------------------------------
# create or recreate postgres database clusters
#------------------------------------------------------------------------------
- name: Init database cluster
  become: yes
  hosts: all
  gather_facts: false

  roles:
    - role: postgres                        # init postgres
      tags: postgres

    - role: monitor                         # init monitor system
      tags: monitor

    - role: haproxy                         # init haproxy
      tags: haproxy

    - role: vip                             # init vip-manager
      tags: vip

任务详情

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

./sandbox.yml --list-tasks

默认任务如下：

playbook: ./sandbox.yml

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

  play #2 (all): Provision Node	TAGS: [node]
    tasks:
      node : 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]
      node : Install node basic packages	TAGS: [node, node_pkgs]
      node : Install node extra packages	TAGS: [node, node_pkgs]
      node : Install meta specific packages	TAGS: [node, node_pkgs]
      node : Install node basic packages	TAGS: [node, node_pkgs]
      node : 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]
      node : 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]
      node : 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:
      ca : Create local ca directory	TAGS: [ca, ca_dir, meta]
      ca : Copy ca cert from local files	TAGS: [ca, ca_copy, meta]
      ca : Check ca key cert exists	TAGS: [ca, ca_create, meta]
      ca : Create self-signed CA key-cert	TAGS: [ca, ca_create, meta]
      nameserver : Make sure dnsmasq package installed	TAGS: [meta, nameserver]
      nameserver : Copy dnsmasq /etc/dnsmasq.d/config	TAGS: [meta, nameserver]
      nameserver : Add dynamic dns records to meta	TAGS: [meta, nameserver]
      nameserver : Launch meta dnsmasq service	TAGS: [meta, nameserver]
      nameserver : Wait for meta dnsmasq online	TAGS: [meta, nameserver]
      nginx : Make sure nginx package installed	TAGS: [meta, nginx, nginx_install]
      nginx : Create local html directory	TAGS: [meta, nginx, nginx_dir]
      nginx : Update default nginx index page	TAGS: [meta, nginx, nginx_dir]
      nginx : Copy nginx default config	TAGS: [meta, nginx, nginx_config]
      nginx : Copy nginx upstream conf	TAGS: [meta, nginx, nginx_config]
      nginx : Fetch haproxy facts	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      debug	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      nginx : Templating /etc/nginx/haproxy.conf	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      nginx : Templating haproxy.html	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      nginx : Launch nginx server	TAGS: [meta, nginx, nginx_reload]
      nginx : Restart meta nginx service	TAGS: [meta, nginx, nginx_launch]
      nginx : Wait for nginx service online	TAGS: [meta, nginx, nginx_launch]
      nginx : Make sure nginx exporter installed	TAGS: [meta, nginx, nginx_exporter]
      nginx : Config nginx_exporter options	TAGS: [meta, nginx, nginx_exporter]
      nginx : Restart nginx_exporter service	TAGS: [meta, nginx, nginx_exporter]
      nginx : Wait for nginx exporter online	TAGS: [meta, nginx, nginx_exporter]
      nginx : Register cosnul nginx service	TAGS: [meta, nginx, nginx_register]
      nginx : Register consul nginx-exporter service	TAGS: [meta, nginx, nginx_register]
      nginx : Reload consul	TAGS: [meta, nginx, nginx_register]
      prometheus : Install prometheus and alertmanager	TAGS: [meta, prometheus, prometheus_install]
      prometheus : Wipe out prometheus config dir	TAGS: [meta, prometheus, prometheus_clean]
      prometheus : Wipe out existing prometheus data	TAGS: [meta, prometheus, prometheus_clean]
      prometheus : Recreate prometheus data dir	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy /etc/prometheus configs	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy /etc/prometheus opts	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus scrape_interval	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus evaluation_interval	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus scrape_timeout	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus pg metrics path	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Launch prometheus service	TAGS: [meta, prometheus, prometheus_launch]
      prometheus : Launch alertmanager service	TAGS: [meta, prometheus, prometheus_launch]
      prometheus : Wait for prometheus online	TAGS: [meta, prometheus, prometheus_launch]
      prometheus : Wait for alertmanager online	TAGS: [meta, prometheus, prometheus_launch]
      grafana : Make sure grafana is installed	TAGS: [grafana, grafana_install, meta]
      grafana : Check grafana plugin cache exists	TAGS: [grafana, grafana_plugin, meta]
      grafana : Provision grafana plugins via cache	TAGS: [grafana, grafana_plugin, meta]
      grafana : Download grafana plugins from web	TAGS: [grafana, grafana_plugin, meta]
      grafana : Download grafana plugins from web	TAGS: [grafana, grafana_plugin, meta]
      grafana : Create grafana plugins cache	TAGS: [grafana, grafana_plugin, meta]
      grafana : Copy /etc/grafana/grafana.ini	TAGS: [grafana, grafana_config, meta]
      grafana : 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]
      grafana : Replace grafana icon with pigsty	TAGS: [grafana, grafana_config, grafana_customize, meta]
      grafana : Launch grafana service	TAGS: [grafana, grafana_launch, meta]
      grafana : Wait for grafana online	TAGS: [grafana, grafana_launch, meta]
      grafana : Update grafana default preferences	TAGS: [grafana, grafana_provision, meta]
      grafana : Register consul grafana service	TAGS: [grafana, grafana_register, meta]
      grafana : Reload consul	TAGS: [grafana, grafana_register, meta]

  play #4 (all): Init dcs	TAGS: []
    tasks:
      consul : Check for existing consul	TAGS: [consul_check, dcs]
      consul : Consul exists flag fact set	TAGS: [consul_check, dcs]
      consul : Abort due to consul exists	TAGS: [consul_check, dcs]
      consul : Clean existing consul instance	TAGS: [consul_check, dcs]
      consul : Stop any running consul instance	TAGS: [consul_check, dcs]
      consul : Remove existing consul dir	TAGS: [consul_check, dcs]
      consul : Recreate consul dir	TAGS: [consul_check, dcs]
      consul : Make sure consul is installed	TAGS: [consul_install, dcs]
      consul : 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]
      consul : Copy /etc/consul.d/consul.json	TAGS: [consul_config, dcs]
      consul : Copy consul agent service	TAGS: [consul_config, dcs]
      consul : Get dcs bootstrap expect quroum	TAGS: [consul_server, dcs]
      consul : Copy consul server service unit	TAGS: [consul_server, dcs]
      consul : Launch consul server service	TAGS: [consul_server, dcs]
      consul : Wait for consul server online	TAGS: [consul_server, dcs]
      consul : Launch consul agent service	TAGS: [consul_agent, dcs]
      consul : Wait for consul agent online	TAGS: [consul_agent, dcs]

  play #5 (all): Init database cluster	TAGS: []
    tasks:
      postgres : 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]
      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]
      postgres : Check for existing postgres instance	TAGS: [pg_check, postgres, prepare]
      postgres : Set fact whether pg port is open	TAGS: [pg_check, postgres, prepare]
      postgres : Abort due to existing postgres instance	TAGS: [pg_check, postgres, prepare]
      postgres : Clean existing postgres instance	TAGS: [pg_check, postgres, prepare]
      postgres : Shutdown existing postgres service	TAGS: [pg_clean, postgres, prepare]
      postgres : Remove registerd consul service	TAGS: [pg_clean, postgres, prepare]
      postgres : Remove postgres metadata in consul	TAGS: [pg_clean, postgres, prepare]
      postgres : Remove existing postgres data	TAGS: [pg_clean, postgres, prepare]
      postgres : Make sure main and backup dir exists	TAGS: [pg_dir, postgres, prepare]
      postgres : 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]
      postgres : Copy postgres scripts to /pg/bin/	TAGS: [pg_scripts, postgres, prepare]
      postgres : Copy alias profile to /etc/profile.d	TAGS: [pg_scripts, postgres, prepare]
      postgres : 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]
      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]
      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]
      postgres : Wait for postgres primary online	TAGS: [pg_primary, postgres]
      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]
      postgres : Wait for postgres replica online	TAGS: [pg_replica, postgres]
      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]
      postgres : Launch raw postgres on primary	TAGS: [pg_patroni, postgres]
      postgres : Launch raw postgres on primary	TAGS: [pg_patroni, postgres]
      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]
      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]
      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]
      haproxy : Make sure haproxy is installed	TAGS: [haproxy, haproxy_install]
      haproxy : Create haproxy directory	TAGS: [haproxy, haproxy_install]
      haproxy : Copy haproxy systemd service file	TAGS: [haproxy, haproxy_install, haproxy_unit]
      haproxy : Fetch postgres cluster memberships	TAGS: [haproxy, haproxy_config]
      haproxy : Templating /etc/haproxy/haproxy.cfg	TAGS: [haproxy, haproxy_config]
      haproxy : Launch haproxy load balancer service	TAGS: [haproxy, haproxy_launch, haproxy_restart]
      haproxy : Wait for haproxy load balancer online	TAGS: [haproxy, haproxy_launch]
      haproxy : Reload haproxy load balancer service	TAGS: [haproxy, haproxy_reload]
      haproxy : Copy haproxy service definition	TAGS: [haproxy, haproxy_register]
      haproxy : Reload haproxy consul service	TAGS: [haproxy, haproxy_register]
      vip : Templating /etc/default/vip-manager.yml	TAGS: [vip]
      vip : create vip-manager. systemd drop-in dir	TAGS: [vip]
      vip : create vip-manager systemd drop-in file	TAGS: [vip]
      vip : Launch vip-manager	TAGS: [vip]

沙箱初始化

常规初始化需要先完成元节点的初始化，再完成其他节点。

为了加快沙箱环境的初始化速度，Pigsty提供了

选择性执行

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

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

./infra.yml --tags=repo

默认任务

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

./sandbox.yml --list-tasks

默认任务如下：

playbook: ./sandbox.yml

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

  play #2 (all): Provision Node	TAGS: [node]
    tasks:
      node : 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]
      node : Install node basic packages	TAGS: [node, node_pkgs]
      node : Install node extra packages	TAGS: [node, node_pkgs]
      node : Install meta specific packages	TAGS: [node, node_pkgs]
      node : Install node basic packages	TAGS: [node, node_pkgs]
      node : 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]
      node : 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]
      node : 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:
      ca : Create local ca directory	TAGS: [ca, ca_dir, meta]
      ca : Copy ca cert from local files	TAGS: [ca, ca_copy, meta]
      ca : Check ca key cert exists	TAGS: [ca, ca_create, meta]
      ca : Create self-signed CA key-cert	TAGS: [ca, ca_create, meta]
      nginx : Make sure nginx package installed	TAGS: [meta, nginx, nginx_install]
      nginx : Create local html directory	TAGS: [meta, nginx, nginx_dir]
      nginx : Update default nginx index page	TAGS: [meta, nginx, nginx_dir]
      nginx : Copy nginx default config	TAGS: [meta, nginx, nginx_config]
      nginx : Copy nginx upstream conf	TAGS: [meta, nginx, nginx_config]
      nginx : Fetch haproxy facts	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      debug	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      nginx : Templating /etc/nginx/haproxy.conf	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      nginx : Templating haproxy.html	TAGS: [meta, nginx, nginx_config, nginx_haproxy]
      nginx : Launch nginx server	TAGS: [meta, nginx, nginx_reload]
      nginx : Restart meta nginx service	TAGS: [meta, nginx, nginx_launch]
      nginx : Wait for nginx service online	TAGS: [meta, nginx, nginx_launch]
      nginx : Make sure nginx exporter installed	TAGS: [meta, nginx, nginx_exporter]
      nginx : Config nginx_exporter options	TAGS: [meta, nginx, nginx_exporter]
      nginx : Restart nginx_exporter service	TAGS: [meta, nginx, nginx_exporter]
      nginx : Wait for nginx exporter online	TAGS: [meta, nginx, nginx_exporter]
      nginx : Register cosnul nginx service	TAGS: [meta, nginx, nginx_register]
      nginx : Register consul nginx-exporter service	TAGS: [meta, nginx, nginx_register]
      nginx : Reload consul	TAGS: [meta, nginx, nginx_register]
      prometheus : Install prometheus and alertmanager	TAGS: [meta, prometheus, prometheus_install]
      prometheus : Wipe out prometheus config dir	TAGS: [meta, prometheus, prometheus_clean]
      prometheus : Wipe out existing prometheus data	TAGS: [meta, prometheus, prometheus_clean]
      prometheus : Recreate prometheus data dir	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy /etc/prometheus configs	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy /etc/prometheus opts	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus scrape_interval	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus evaluation_interval	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus scrape_timeout	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Overwrite prometheus pg metrics path	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Launch prometheus service	TAGS: [meta, prometheus, prometheus_launch]
      prometheus : Launch alertmanager service	TAGS: [meta, prometheus, prometheus_launch]
      prometheus : Wait for prometheus online	TAGS: [meta, prometheus, prometheus_launch]
      prometheus : Wait for alertmanager online	TAGS: [meta, prometheus, prometheus_launch]
      grafana : Make sure grafana is installed	TAGS: [grafana, grafana_install, meta]
      grafana : Check grafana plugin cache exists	TAGS: [grafana, grafana_plugin, meta]
      grafana : Provision grafana plugins via cache	TAGS: [grafana, grafana_plugin, meta]
      grafana : Download grafana plugins from web	TAGS: [grafana, grafana_plugin, meta]
      grafana : Download grafana plugins from web	TAGS: [grafana, grafana_plugin, meta]
      grafana : Create grafana plugins cache	TAGS: [grafana, grafana_plugin, meta]
      grafana : Copy /etc/grafana/grafana.ini	TAGS: [grafana, grafana_config, meta]
      grafana : 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]
      grafana : Replace grafana icon with pigsty	TAGS: [grafana, grafana_config, grafana_customize, meta]
      grafana : Launch grafana service	TAGS: [grafana, grafana_launch, meta]
      grafana : Wait for grafana online	TAGS: [grafana, grafana_launch, meta]
      grafana : Update grafana default preferences	TAGS: [grafana, grafana_provision, meta]
      grafana : Register consul grafana service	TAGS: [grafana, grafana_register, meta]
      grafana : Reload consul	TAGS: [grafana, grafana_register, meta]

  play #4 (all): Init dcs	TAGS: []
    tasks:
      consul : Check for existing consul	TAGS: [consul_check, dcs]
      consul : Consul exists flag fact set	TAGS: [consul_check, dcs]
      consul : Abort due to consul exists	TAGS: [consul_check, dcs]
      consul : Clean existing consul instance	TAGS: [consul_check, dcs]
      consul : Stop any running consul instance	TAGS: [consul_check, dcs]
      consul : Remove existing consul dir	TAGS: [consul_check, dcs]
      consul : Recreate consul dir	TAGS: [consul_check, dcs]
      consul : Make sure consul is installed	TAGS: [consul_install, dcs]
      consul : 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]
      consul : Copy /etc/consul.d/consul.json	TAGS: [consul_config, dcs]
      consul : Copy consul agent service	TAGS: [consul_config, dcs]
      consul : Get dcs bootstrap expect quroum	TAGS: [consul_server, dcs]
      consul : Copy consul server service unit	TAGS: [consul_server, dcs]
      consul : Launch consul server service	TAGS: [consul_server, dcs]
      consul : Wait for consul server online	TAGS: [consul_server, dcs]
      consul : Launch consul agent service	TAGS: [consul_agent, dcs]
      consul : Wait for consul agent online	TAGS: [consul_agent, dcs]

  play #5 (all): Init database cluster	TAGS: []
    tasks:
      postgres : 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]
      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]
      postgres : Check for existing postgres instance	TAGS: [pg_check, postgres, prepare]
      postgres : Set fact whether pg port is open	TAGS: [pg_check, postgres, prepare]
      postgres : Abort due to existing postgres instance	TAGS: [pg_check, postgres, prepare]
      postgres : Clean existing postgres instance	TAGS: [pg_check, postgres, prepare]
      postgres : Shutdown existing postgres service	TAGS: [pg_clean, postgres, prepare]
      postgres : Remove registerd consul service	TAGS: [pg_clean, postgres, prepare]
      postgres : Remove postgres metadata in consul	TAGS: [pg_clean, postgres, prepare]
      postgres : Remove existing postgres data	TAGS: [pg_clean, postgres, prepare]
      postgres : Make sure main and backup dir exists	TAGS: [pg_dir, postgres, prepare]
      postgres : 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]
      postgres : Copy postgres scripts to /pg/bin/	TAGS: [pg_scripts, postgres, prepare]
      postgres : Copy alias profile to /etc/profile.d	TAGS: [pg_scripts, postgres, prepare]
      postgres : 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]
      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]
      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]
      postgres : Wait for postgres primary online	TAGS: [pg_primary, postgres]
      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]
      postgres : Wait for postgres replica online	TAGS: [pg_replica, postgres]
      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]
      postgres : Launch raw postgres on primary	TAGS: [pg_patroni, postgres]
      postgres : Launch raw postgres on primary	TAGS: [pg_patroni, postgres]
      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]
      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]
      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]
      haproxy : Make sure haproxy is installed	TAGS: [haproxy, haproxy_install]
      haproxy : Create haproxy directory	TAGS: [haproxy, haproxy_install]
      haproxy : Copy haproxy systemd service file	TAGS: [haproxy, haproxy_install, haproxy_unit]
      haproxy : Fetch postgres cluster memberships	TAGS: [haproxy, haproxy_config]
      haproxy : Templating /etc/haproxy/haproxy.cfg	TAGS: [haproxy, haproxy_config]
      haproxy : Launch haproxy load balancer service	TAGS: [haproxy, haproxy_launch, haproxy_restart]
      haproxy : Wait for haproxy load balancer online	TAGS: [haproxy, haproxy_launch]
      haproxy : Reload haproxy load balancer service	TAGS: [haproxy, haproxy_reload]
      haproxy : Copy haproxy service definition	TAGS: [haproxy, haproxy_register]
      haproxy : Reload haproxy consul service	TAGS: [haproxy, haproxy_register]
      vip : Templating /etc/default/vip-manager.yml	TAGS: [vip]
      vip : create vip-manager. systemd drop-in dir	TAGS: [vip]
      vip : create vip-manager systemd drop-in file	TAGS: [vip]
      vip : Launch vip-manager	TAGS: [vip]

4 - 定制

如何定制Pigsty

定制模板

定制初始化脚本
定制操作系统优化方案
定制数据库模板
定制访问控制系统
定制Patroni模板
定制自定义基线脚本

4.1 - 定制Patroni

介绍Pigsty提供的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.2 - 定制业务数据库

定制业务数据库

定制参数

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

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

4.3 - 访问控制模型

介绍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

其他

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.4 - 定制模板数据库

您可以使用以下参数，对集群中的模板数据库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: []