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

Return to the regular view of this page.

Deployment

How to deploy pigsty to your production environment?

Whether it is a sandbox environment or a real production environment, Pigsty uses the same three-step deployment process: prepare resources, modify configuration, execute script

Pigsty requires some preparation before deployment: configure the node with the correct permissions configuration, download and install the relevant software. After configuration, users should [modify the configuration](… /config/) according to their needs. /config/). and execute the script to adjust the system to the state described in the configuration.

If users wish to use Pigsty to monitor an existing database cluster or wish to deploy only the Pigsty monitoring system part, please refer to monitoring deployment only .

preparation

  • [node provisioning](prepare/#node provisioning)
  • [meta-node provisioning](prepare/#meta-node provisioning)
  • [software provisioning](prepare/#software provisioning)

modify configuration

  • [identity information](config/#identity information)
  • [Customize database cluster](config/#Customize database cluster)
  • [Custom Business Users](config/#Custom Business Users)
  • [Custom Business Database](config/#Custom Business Database)

Execution script

  • [infrastructure initialization](playbook/#infrastructure initialization)
  • [Database Cluster Initialization](playbook/#Database Cluster Initialization)
  • [Database Cluster Scaling](playbook/#Database Cluster Scaling)
  • [Managing business users and databases](playbook/#Managing business users and databases)
  • [Managed Services](playbook/#Managed Services)
  • [**Monitor-Only Deployment **](playbook/#Monitoring Deployment Only)

1 - Prepare

How to prepare resource for pigsty deployment?

Node provisioning

Before deploying Pigsty, users need to prepare machine node resources, including at least one meta node, with any number of database nodes.

[database nodes](… /… /concept/arch/#database nodes) can use any SSH reachable nodes: physical machines, virtual machines, containers, etc., but currently Pigsty only supports CentOS 7 operating system.

Pigsty recommends using physical and virtual machines for deployment. When using a local sandbox environment, Pigsty is based on Vagrant and Virtualbox to quickly pull up local VM resources, please refer to Vagrant tutorial for more details.

Metanode provisioning

Pigsty requires [meta-node](. /… /concept/arch/# meta-node) as the control center for the entire environment and to provide [infrastructure](. /… /concept/arch/#infrastructure) services. The number of meta-nodes requires a minimum of 1, recommends 3, and suggests no more than 5. If deploying DCS to a meta-node, it is recommended that 3 meta-nodes be used in a production environment to fully ensure the availability of DCS services.

Users should ensure that they can login to the metanode and can [passwordless SSH login](users/#configure SSH passwordless access) other nodes from the metanode and [passwordless](users#configure passwordless SUDO) execute the sudo command.

Users should ensure that they have direct or indirect access to port 80 of the meta node to access the user interface provided by Pigsty.

Software Placement

The user should [download this project](software/# download pigsty source code) and [offline package](software/# download offline package) on the metanode (optional).

When pulling up Pigsty using the local sandbox, the user will also need to additionally install on the host.

1.1 - Vagrant

How to install, config, use vagrant

Often, in order to test a system such as a “database cluster”, users need to prepare several virtual machines in advance. Although cloud services are already very convenient, local virtual machine access is usually easier, more responsive and less expensive than cloud virtual machine access. Local VM configuration is relatively cumbersome, and Vagrant can solve this problem.

Pigsty users don’t need to understand how vagrant works, they just need to know that vagrant can simply and quickly pull up several virtual machines on a laptop, PC or Mac according to the user’s needs. All the user needs to accomplish is to express their virtual machine requirements in the form of a vagrant configuration file.

Vagrant Installation

Visit official website

https://www.vagrantup.com/downloads

Download Vagrant

最新版本为2.2.14

Vagrant Install

点击 vagrant.pkg 执行安装,安装过程需要输入密码。https://www.virtualbox.org/

Vagrantfile

https://github.com/Vonng/pigsty/blob/master/vagrant/Vagrantfile 提供了一个Vagrantfile样例。

这是Pigsty沙箱所使用的Vagrantfile,定义了四台虚拟机,包括一台2核/4GB的中控机/元节点,和3台 1核/1GB 的数据库节点

vagrant 二进制程序根据 Vagrantfile 中的定义,默认调用 Virtualbox 完成本地虚拟机的创建工作。

进入Pigsty根目录下的vagrant目录,执行vagrant up,即可拉起所有的四台虚拟机。

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

定制Vagrantfile

如果用户的机器配置不足,则可以考虑使用更小的N值,减少数据库节点的数量。如果只希望运行单个元节点,将其修改为0即可。

用户还可以修改每台机器的CPU核数和内存资源等,如配置文件中的注释所述,详情参阅Vagrant与Pigsty文档。

沙箱环境默认使用IMAGE_NAME = "centos/7",首次执行时会从vagrant官方下载centos 7.8 virtualbox 镜像,确保宿主机拥有合适的网络访问权限(科学上网)!

快捷方式

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

更多信息,请参考Makefile

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

How to install Virtualbox

It’s quite simple installing virtualbox on MacOS. And similar on other operating systems.

前往Virtualbox官网

https://www.virtualbox.org/

下载Virtualbox

最新版本为6.1.18

安装Virtualbox

点击 VirtualBox.pkg 执行安装,安装过程需要输入密码并重启。

如果安装失败,请检查您的 系统偏好设置 - 安全性与隐私 - 通用 - 允许以下位置的App中点击“允许”按钮。

就这?

没错,您已经成功安装完Oracle Virtualbox了!

1.3 - Ansible

How to install vagrant

Ansible is a popular and simple automated IT tool that is widely used for operations management and software deployment.

Ansible is the execution vehicle for Pigsty scripts, so if you don’t need to customize this project, users don’t need to know much about Ansible details, just think of it as an advanced Shell or Python interpreter.

How to install

Ansible can be installed via the package manager

brew install ansible # macos
yum  install ansible # linux

Check installed version

$ echo $(ansible --version)
ansible 2.10.3

Ansible 2.9+ is recommended.

How to use

Pigsty项目根目录下提供了一系列Ansible剧本,在其开头的Hashbang中调用ansible-playbook来执行自己。

#!/usr/bin/env ansible-playbook

因此,您通常不需要关心Ansible如何使用,安装完成后,直接使用下面的方式执行Ansible剧本即可。

./pgsql.yml

离线安装Ansible

Pigsty依赖Ansible进行环境初始化。但如果元节点本身没有安装Ansible,也没有互联网访问怎么办?

离线安装包中本身带有 Ansible,可以直接通过本地文件Yum源的方式使用,假设用户已经将离线安装包解压至默认位置:/www/pigsty

那么将以下Repo文件写入/etc/yum.repos.d/pigsty-local.repo 中,就可以直接使用该源。

[pigsty-local]
name=Local Yum Repo pigsty
baseurl=file:///www/pigsty
skip_if_unavailable = 1
enabled = 1
priority = 1
gpgcheck = 0

执行以下命令,在元节点上离线安装Ansible

yum clean all
yum makecache
yum install ansible

1.4 - Admin User

How to config ssh nopass and nopass sudo?

Pigsty requires an administrative user that can SSH password-free to other nodes from the meta-node and execute the sudo command password-free.

Admin user

Pigsty recommends that the creation of the administrative user, privilege configuration and key distribution be done during the Provisioning phase of the VM as part of the delivered content.

The default user for sandbox environments, vagrant, is already configured with password-free login and password-free sudo by default, and you can use vagrant to login to all database nodes from the host or sandbox meta-node. For production environments, i.e. when the machine is delivered, there should already be such a user configured with unencrypted remote SSH login and unencrypted sudo.

If not, the user will need to create it himself. If the user has root privileges, they can also perform the initialization directly with root identity, and Pigsty can complete the creation of the administrative user during the initialization process. The relevant configuration parameters include.

node_admin_setup

Whether to create an admin user on each node (password-free sudo with ssh), which will be created by default.

Pigsty by default creates an admin user named admin (uid=88) that can SSH-free access to other nodes in the environment from the meta-node and perform password-free sudo.

node_admin_uid

The uid of the administrator user, default is 88

node_admin_username

Name of the admin user, default is admin

node_admin_ssh_exchange

Does the SSH key for the admin user get exchanged between the machines currently executing the command?

The exchange is performed by default, so that the administrator can quickly jump between machines.

node_admin_pks

Key written to admin ~/.ssh/authorized_keys

Users with the corresponding private keys can log in as administrators.

By default, Pigsty will create the administrator user admin with uid=88 and exchange that user’s key cluster-wide.

node_admin_pks given in the public key will be installed to the authorized_keys of the admin account, and the user with the corresponding private key can directly log in remotely without encryption.

Configure SSH nopass access

On the meta node, assume the username of the user executing the command is vagrant.

Generate the key

Execute the following command as user vagrant to generate a public-private key pair for vagrant to use for login.

ssh-keygegn
  • Default public key: ~/.ssh/id_rsa.pub
  • Default private key: ~/.ssh/id_rsa

Install the key

Add the public key to the corresponding user on the machine you need to log in to: /home/vagrant/.ssh/authorized_keys

If you already have direct password access to the remote machine, you can copy the public key directly via ssh-copy-id.

# Enter the password to complete the public key copy
ssh-copy-id <ip>
# Embed the password directly into the command to avoid interactive password entry
sshpass -p <password> ssh-copy-id <ip>

Then you can log in to the remote machine via password-free SSH for that user.

Configure nopass SUDO

Assuming the username is vagrant, add the following entry via the visudo command, or by creating the /etc/sudoers.d/vagrant file.

%vagrant ALL=(ALL) NOPASSWD: ALL

Then the vagrant user can execute all commands without sudo

1.5 - Prepare Software

How to prepare software resource for pigsty deployment

Users need to download the Pigsty project to the meta-node (in a sandbox environment, you can also use the host to initiate control)

Download Pigsty source code

Users can clone the project directly from Github using git, or download the latest version of the Pigsty source package from the Github Release page at.

git clone https://github.com/Vonng/pigsty
git clone git@github.com:Vonng/pigsty.git

You can also download the latest version of Pigsty from the Pigsty CDN: pigsty.tar.gz

http://pigsty-1304147732.cos.accelerate.myqcloud.com/latest/pigsty.tar.gz

Download the offline installer

Pigsty comes with a sandbox environment, and the offline installer for the sandbox environment is placed in the files directory by default, which can be downloaded from [Github Release](https://github. com/Vonng/pigsty/releases) page.

cd <pigsty>/files/
wget https://github.com/Vonng/pigsty/releases/download/v0.6.0/pkg.tgz 

Pigsty’s official CDN also provides the latest version of pkg.tgz for download, just execute the following command.

make downlaod
curl http://pigsty-1304147732.cos.accelerate.myqcloud.com/pkg.tgz -o files/pkg.tgz

For details on how to use the offline installation package, please refer to the [offline installation](. /offline/) section.

Monitor-only mode resources

If you want to use a monitoring-only deployment, it is usually recommended to deploy the monitoring agent using a copy of the monitoring component binary, so you need to download and place the Linux Binary in the files directory beforehand.

files
   ^---- pg_exporter (linux amd64 binary)
   ^---- node_exporter (linux amd64 binary)

The self-contained script files/download-exporter.sh will automatically download the latest versions of node_exporter and pg_exporter from the Internet.

1.6 - Offline Installation

How to perform offline installation

Pigsty is a complex software system. To ensure the stability of the system, Pigsty downloads all dependent packages from the Internet during the initialization process and creates local repository (local Yum source).

The total size of all dependent software is about 1GB, and the download speed depends on the user’s network. Although Pigsty has tried to use mirror sources as much as possible to speed up the download, the download of a small number of packages may still be blocked by firewalls and may appear very slow. The user can download the packages via proxy_env configuration entry to set up a download proxy to complete the first download.

If you are using a different operating system than CentOS 7.8, it is usually recommended that users use the full online download and installation process. and cache the downloaded software after the first initialization is complete, see [Making an offline installer](#Making an offline installer).

If you wish to skip the long download process, or if the execution control meta-node does not have Internet access, consider downloading a pre-packaged offline installer.

Contents of the offline installer package

To quickly pull up Pigsty, it is recommended to use the offline download package and upload method to complete the installation.

The offline installer includes all packages from the local Yum repository. By default, Pigsty is installed at [infrastructure initialization](. /../playbook/infra/) when the local Yum repository is created.

{{ repo_home }}
  |---- {{ repo_name }}.repo
  ^---- {{ repo_name}}/repo_complete
  ^---- {{ repo_name}}/**************.rpm

By default, {{ repo_home }} is the root directory of the Nginx static file server, which defaults to /www, and repo_name is a custom local source name, which defaults to pigsty.

By default, the /www/pigsty directory contains all RPM packages, and the offline installer is actually a zip archive of the /www/pigsty directory.

The principle of the offline installation package is that Pigsty checks if the local Yum source-related files already exist during the execution of the infrastructure initialization. If they already exist, the process of downloading the package and its dependencies is skipped.

The token file used for the check is {{ repo_home }}/{{ repo_name }}/repo_complete, by default /www/pigsty/repo_complete, if this token file exists, (usually set by Pigsty after the local source is created), then the local source has been created and can be used directly. Otherwise, Pigsty will perform the usual download logic. Once the download is complete, you can archive a compressed copy of the directory for accelerating the initialization of other environments.

沙箱环境

下载离线安装包

Pigsty自带了一个沙箱环境,沙箱环境的离线安装包默认放置于files目录中,可以从Github Release页面下载。

cd <pigsty>/files/
wget https://github.com/Vonng/pigsty/releases/download/v0.6.0/pkg.tgz 

Pigsty的官方CDN也提供最新版本的pkg.tgz下载,只需要执行以下命令即可。

make downlaod
curl http://pigsty-1304147732.cos.accelerate.myqcloud.com/pkg.tgz -o files/pkg.tgz

上传离线安装包

使用Pigsty沙箱时,下载离线安装至本地files目录后,则可以直接使用 Makefile 提供的快捷指令make upload上传离线安装包至元节点上。

使用 make upload,也会将本地的离线安装包(Yum缓存)拷贝至元节点上。

# upload rpm cache to meta controller
upload:
	ssh -t meta "sudo rm -rf /tmp/pkg.tgz"
	scp -r files/pkg.tgz meta:/tmp/pkg.tgz
	ssh -t meta "sudo mkdir -p /www/pigsty/; sudo rm -rf /www/pigsty/*; sudo tar -xf /tmp/pkg.tgz --strip-component=1 -C /www/pigsty/"

制作离线安装包

使用 Pigsty 沙箱时,可以通过 make cache 将沙箱中元节点的缓存制为离线安装包,并拷贝到本地。

# cache rpm packages from meta controller
cache:
	rm -rf pkg/* && mkdir -p pkg;
	ssh -t meta "sudo tar -zcf /tmp/pkg.tgz -C /www pigsty; sudo chmod a+r /tmp/pkg.tgz"
	scp -r meta:/tmp/pkg.tgz files/pkg.tgz
	ssh -t meta "sudo rm -rf /tmp/pkg.tgz"

在生产环境离线安装包

在生产环境使用离线安装包前,您必须确保生产环境的操作系统与制作该离线安装包的机器操作系统一致。Pigsty提供的离线安装包默认使用CentOS 7.8。

使用不同操作系统版本的离线安装包可能会出错,也可能不会,我们强烈建议不要这么做。

如果需要在其他版本的操作系统(例如CentOS7.3,7.7等)上运行Pigsty,建议用户在安装有同版本操作系统的沙箱中完整执行一遍初始化流程,不使用离线安装包,而是直接从上游源下载的方式进行初始化。对于没有网络访问的生产环境元节点而言,制作离线软件包是至关重要的。

常规初始化完成后,用户可以通过make cache或手工执行相关命令,将特定操作系统的软件缓存打为离线安装包。供生产环境使用。

从初始化完成的本地元节点构建离线安装包:

tar -zcf /tmp/pkg.tgz -C /www pigsty     # 制作离线软件包

在生产环境使用离线安装包与沙箱环境类似,用户需要将pkg.tgz复制到元节点上,然后将离线安装包解压至目标地址。

这里以默认的 /www/pigsty 为例,将压缩包中的所有内容(RPM包,repo_complete标记文件,repodata 源的元数据库等)解压至目标目录/www/pigsty中,可以使用以下命令。

mkdir -p /www/pigsty/
sudo rm -rf /www/pigsty/*
sudo tar -xf /tmp/pkg.tgz --strip-component=1 -C /www/pigsty/

2 - Configure

How to configure pigsty according to your needs

Users can use the following configuration items to configure the infrastructure and database cluster.

In general, most parameters can be used directly with default values.

The infrastructure section requires very little modification, and the only modification usually involved is a textual substitution of the IP address of the meta-node.

In contrast, users need to focus on the definition and configuration of database clusters. The database cluster will be deployed on database nodes and the user must provide the [identity information](identity/#identity parameter) of the database cluster with the [connection information](identity/#connection information) of the database nodes. The identity information (e.g., cluster name, instance number) is used to describe the entities in the database cluster, while the connection information (e.g., IP address) is used to access the database node. Also, the user should define the default business user and business database together with the cluster creation.

In addition, users can also customize the default access control model, template database, external exposed services by modifying the parameters. /../concept/provision/service/# service definition).

Database customization

In Pigsty, database initialization is divided into five parts.

1. [Install database software](. /../config/6-pg-install/)

What version to install, which plugins to install, what user to use

Usually the parameters in this section can be used directly without modifying anything (adjustments need to be made when the PG version is upgraded).

2. [provisioning database cluster] (../../config/7-pg-provision/)

Where to create the directory, what purpose to create the cluster, which IP ports to listen on, what connection pooling mode to use

In this section, identity information is a mandatory parameter, other than that there are very few default parameters to change.

The default parameters are rarely changed other than by pg_conf you can use the default database cluster templates (Normal Transactional OLTP / Normal Analytical OLAP / Core Financial CRIT / Micro Virtual Machine TINY). If you wish to create custom templates, you can clone the default configuration in roles/postgres/templates and adopt it with your own modifications, see [Patroni template customization](. /../reference/patroni).

3. Customization of database templates

Which roles, users, databases, schemas to create, which extensions to enable, how to set permissions and whitelist

Needs to be focused, as this is where the business declares its desired database. Users can customize through database templates at.

4. [pull up database monitoring](. /../config/9-monitor)

Deploy Pigsty monitoring system components

Normally no adjustment is needed, but in monitor deployment only mode it needs to be focused on. /monly) mode needs to be focused on and tuned.

5. [Expose database services](. /../config/10-services)

Expose database services externally via HAproxy/VIP

The configuration here does not need to be adjusted unless the user wishes to define additional services.

Configuration item reference

Most parameters are provided with reasonable default values, please refer to [configuration items](. /../config) manual to modify as needed.

| No | English | major class | function | | :–: | :—————————————–: | :———: | ——————— —————– | | 1 | connect | Infrastructure | Proxy server configuration, connection information for managed objects | | 2 | repo | Infrastructure | Customize local Yum sources, install packages offline | | 3 | node | Infrastructure | Configuring infrastructure on a normal node | | 4 | meta | meta | Infrastructure | | 5 | dcs | Infrastructure | Configure DCS services (consul/etcd) on all nodes | | 6 | [pg-install](. /../config/6-pg-install) | Database-clustering | Installing PostgreSQL database | | 7 | pg-provision | database-clustering | pulling up a PostgreSQL database cluster | | 8 | [pg-template](. /../config/8-pg-template) | Database-template | Customize PostgreSQL database content | | 9 | monitor | monitor | Database-Affiliate | | 10 | service | service | Database-affiliated |

2.1 - Assign Identity

How to configure and assign identity to pgsql clusters and instances

Pigsty基于 身份标识(Identity) 管理数据库对象。

身份参数

身份参数是定义数据库集群时必须提供的信息,包括:

名称 属性 说明 例子
pg_cluster 必选,集群级别 集群名 pg-test
pg_role 必选,实例级别 实例角色 primary, replica
pg_seq 必选,实例级别 实例序号 1, 2, 3,...
pg_shard 可选,集群级别 分片集群名 test
pg_sindex 可选,集群级别 分片集群号 1

身份参数的内容遵循 Pigsty命名原则 。其中 pg_clusterpg_rolepg_seq 属于核心身份参数,是定义数据库集群所需的最小必须参数集。核心身份参数必须显式指定,手工分配。

  • pg_cluster 标识了集群的名称,在集群层面进行配置,作为集群资源的顶层命名空间。
  • pg_role在实例层面进行配置,标识了实例在集群中扮演的角色。可选值包括:
    • primary:集群中的唯一主库,集群领导者,提供写入服务。
    • replica:集群中的普通从库,承接常规生产只读流量。
    • offline:集群中的离线从库,承接ETL/SAGA/个人用户/交互式/分析型查询。
    • standby:集群中的同步从库,采用同步复制,没有复制延迟。
    • delayed:集群中的延迟从库,显式指定复制延迟,用于执行回溯查询与数据抢救。
  • pg_seq 用于在集群内标识实例,通常采用从0或1开始递增的整数,一旦分配不再更改。
  • pg_shard 用于标识集群所属的上层 分片集簇,只有当集群是水平分片集簇的一员时需要设置。
  • pg_sindex 用于标识集群的分片集簇编号,只有当集群是水平分片集簇的一员时需要设置。
  • pg_instance衍生身份参数,用于唯一命名标识一个数据库实例,其规则为

    {{ pg_cluster }}-{{ pg_seq }} 因为pg_seq是集群内唯一的,因此该标识符全局唯一。

定义数据库集群

以下配置文件定义了一个名为pg-test的集群。集群中包含三个实例:pg-test-1pg-test-2pg-test-3,分别为主库,从库,离线库。该配置是一个集群定义所需的最小配置

  pg-test:
    vars: { pg_cluster: pg-test }
    hosts:
      10.10.10.11: {pg_seq: 1, pg_role: primary}
      10.10.10.12: {pg_seq: 2, pg_role: replica}
      10.10.10.13: {pg_seq: 3, pg_role: offline}

pg_clusterpg_rolepg_seq 属于 身份参数

除了IP地址外,这三个参数是定义一套新的数据库集群的最小必须参数集,如下面的配置所示。

其他参数都可以继承自全局配置或默认配置,但身份参数必须显式指定手工分配

  • pg_cluster 标识了集群的名称,在集群层面进行配置。
  • pg_role 在实例层面进行配置,标识了实例的角色,只有primary角色会进行特殊处理,如果不填,默认为replica角色,此外,还有特殊的delayedoffline角色。
  • pg_seq 用于在集群内标识实例,通常采用从0或1开始递增的整数,一旦分配不再更改。
  • {{ pg_cluster }}-{{ pg_seq }} 被用于唯一标识实例,即pg_instance
  • {{ pg_cluster }}-{{ pg_role }} 用于标识集群内的服务,即pg_service

定义水平分片数据库集簇

pg_shardpg_sindex 用于定义特殊的分片数据库集簇,是可选的身份参数。

假设用户有一个水平分片的 分片数据库集簇(Shard) ,名称为test。这个集簇由四个独立的集群组成:pg-test1, pg-test2pg-test3pg-test-4。则用户可以将 pg_shard: test 的身份绑定至每一个数据库集群,将pg_sindex: 1|2|3|4 分别绑定至每一个数据库集群上。如下所示:

pg-test1:
  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 1}
  hosts: {10.10.10.10: {pg_seq: 1, pg_role: primary}}
pg-test2:
  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 2}
  hosts: {10.10.10.11: {pg_seq: 1, pg_role: primary}}
pg-test3:
  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 3}
  hosts: {10.10.10.12: {pg_seq: 1, pg_role: primary}}
pg-test4:
  vars: {pg_cluster: pg-test1, pg_shard: test, pg_sindex: 4}
  hosts: {10.10.10.13: {pg_seq: 1, pg_role: primary}}

数据库节点与数据库实例

数据库集群需要部署在数据库节点上,Pigsty使用数据库节点与数据库实例一一对应的部署模式。

数据库节点使用IP地址作为标识符,数据库实例使用形如pg-test-1的标识符。 数据库节点(Node)数据库实例(Instance) 的标识符可以相互对应,相互转换。

连接信息

如果说身份参数是数据库集群的标识,那么连接信息就是数据库节点的标识

例如在 定义数据库集群 的例子中,数据库集群pg_cluster = pg-testpg_seq = 1 的数据库实例(pg-test-1)部署在IP地址为10.10.10.11 的数据库节点上。这里的IP地址10.10.10.11就是连接信息

Pigsty使用IP地址作为数据库节点的唯一标识,该IP地址必须是数据库实例监听并对外提供服务的IP地址

这一点非常重要,即使您是通过跳板机或SSH代理访问该数据库节点,也应当在配置时保证这一点。

其他连接方式

如果您的目标机器藏在SSH跳板机之后,或者无法通过ssh ip的方式直接方案,则可以考虑使用Ansible提供的连接参数

例如下面的例子中,ansible_host 通过SSH别名的方式告知Pigsty通过ssh node-1 的方式而不是ssh 10.10.10.11的方式访问目标数据库节点。

  pg-test:
    vars: { pg_cluster: pg-test }
    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: offline, ansible_host: node-3}

通过这种方式,用户可以自由指定数据库节点的连接方式,并将连接配置保存在管理用户的~/.ssh/config中。

接下来

完成身份参数配置后,用户可以对数据库集群进行进一步定制。

2.2 - Customize business users

Cusomize business users and roles in pigsty

可以通过 pg_users 定制集群特定的业务用户。该配置项通常用于在数据库集群层面定义业务用户,与 pg_default_roles 采用相同的形式。

样例

一个完整的用户定义由一个JSON/YAML对象构成,如下所示:

# complete example of user/role definition for production user
- name: dbuser_meta               # example production user have read-write access
  password: DBUser.Meta           # example user's password, can be encrypted
  login: true                     # can login, true by default (should be false for role)
  superuser: false                # is superuser? false by default
  createdb: false                 # can create database? false by default
  createrole: false               # can create role? false by default
  inherit: true                   # can this role use inherited privileges?
  replication: false              # can this role do replication? false by default
  bypassrls: false                # can this role bypass row level security? false by default
  connlimit: -1                   # connection limit, -1 disable limit
  expire_at: '2030-12-31'         # 'timestamp' when this role is expired
  expire_in: 365                  # now + n days when this role is expired (OVERWRITE expire_at)
  roles: [dbrole_readwrite]       # dborole_admin|dbrole_readwrite|dbrole_readonly
  pgbouncer: true                 # add this user to pgbouncer? false by default (true for production user)
  parameters:                     # user's default search path
  	search_path: public
  comment: test user

说明

一个用户对象由以下键值构成,只有用户名是必选项,其他参数均为可选,不添加相应键则会使用默认值。

  • name(string) : 用户名称,必选项

  • password(string) : 用户的密码,可以是以md5, sha开头的密文密码。

  • login(bool) :用户是否可以登录,默认为真;如果这里是业务角色,应当将其设置为假。

  • superuser(bool) : 用户是否具有超级用户权限,默认为假

  • createdb(bool) : 用户是否具有创建数据库的权限,默认没有

  • createrole(bool) : 用户是否具有创建新角色的权限,默认没有。

  • inherit(bool) : 用户是否继承其角色的权限?默认继承

  • replication(bool) : 用户是否具有复制权限?默认没有

  • bypassrls(bool) : 用户是否可以绕过行级安全策略?默认不行

  • connlimit(number) : 是否限制用户的连接数量?留空或-1不限,默认不限

  • expire_at(date) : 用户过期时间,默认不过期

  • expire_in(number) : 自创建n天后用户将过期,如果设置将覆盖expire_at

  • roles(string[]) : 用户所属的角色/用户组

  • pgbouncer(bool) : 是否将用户加入连接池用户列表中?默认不加入,通过连接池访问的生产用户应当显式设置此项为真,交互式个人用户/ETL用户应当设置未假或留空。

  • parameters(dict) : 针对用户修改配置参数,k-v结构

  • comment(string) : 用户备注说明信息

Pigsty建议采用dbuser_dbrole_ 的前缀区分用户角色,用户的login选项应当设置为true以允许登录,角色的login选项应当设置为false以拒绝登录。

pg_userspg_default_roles 都是 user 对象构成的数组,两者会依照定义顺序依次创建,因此后创建的用户可以属于先前创建的角色。

实现

pg_default_roles 中的用户会渲染为集群主库上的单个SQL文件:

/pg/tmp/pg-init-roles.sql

pg_users 中的用户会渲染为集群主库上的SQL文件,每个用户一个:

/pg/tmp/pg-db-{{ database.name }}.sql

并依次执行。一个实际渲染的例子如下所示:

----------------------------------------------------------------------
-- File      :   pg-user-dbuser_meta.sql
-- Path      :   /pg/tmp/pg-user-dbuser_meta.sql
-- Time      :   2021-03-22 22:52
-- Note      :   managed by ansible, DO NOT CHANGE
-- Desc      :   creation sql script for user dbuser_meta
----------------------------------------------------------------------

--==================================================================--
--                            EXECUTION                             --
--==================================================================--
-- run as dbsu (postgres by default)
-- createuser -w -p 5432 'dbuser_meta';
-- psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbuser_meta.sql

--==================================================================--
--                           CREATE USER                            --
--==================================================================--
CREATE USER "dbuser_meta" ;

--==================================================================--
--                           ALTER USER                             --
--==================================================================--
-- options
ALTER USER "dbuser_meta" ;

-- password
ALTER USER "dbuser_meta" PASSWORD 'DBUser.Meta';

-- expire
-- expire at 2022-03-22 in 365 days since 2021-03-22
ALTER USER "dbuser_meta" VALID UNTIL '2022-03-22';

-- conn limit
-- remove conn limit
-- ALTER USER "dbuser_meta" CONNECTION LIMIT -1;

-- parameters
ALTER USER "dbuser_meta" SET search_path = public;

-- comment
COMMENT ON ROLE "dbuser_meta" IS 'test user';


--==================================================================--
--                           GRANT ROLE                             --
--==================================================================--
GRANT "dbrole_readwrite" TO "dbuser_meta";


--==================================================================--
--                          PGBOUNCER USER                          --
--==================================================================--
-- user will not be added to pgbouncer user list by default,
-- unless pgbouncer is explicitly set to 'true', which means production user

-- User 'dbuser_meta' will be added to /etc/pgbouncer/userlist.txt via
-- /pg/bin/pgbouncer-create-user 'dbuser_meta' 'DBUser.Meta'


--==================================================================--

连接池

Pgbouncer有自己的用户定义文件,通常是PG用户的一个子集。

在Pigsty中,Pgbouncer的用户定义文件位于:/etc/pgbouncer/userlist.txt

$ cat userlist.txt
"postgres" ""
"dbuser_monitor" "md57bbcca538453edba8be026725c530b05"

只有在该文件中出现的用户,才可以通过PGbouncer访问数据库。

只有pgbouncer选项显式配置为true的用户,会被添加至连接池用户列表中。

修改该配置文件需要reload Pgbouncer方可生效。

导出

以下SQL查询可以使用JSON格式导出数据库中的用户(但需要少量修正)

SELECT row_to_json(u) FROM
    (SELECT r.rolname AS name,
            a.rolpassword AS password,
            r.rolcanlogin AS login,
            r.rolsuper AS superuser,
            r.rolcreatedb AS createdb,
            r.rolcreaterole AS createrole,
            r.rolinherit AS inherit,
            r.rolreplication AS replication,
            r.rolbypassrls AS bypassrls,
            r.rolconnlimit AS connlimit,
            r.rolvaliduntil AS expire_at,
            setconfig AS parameters,
            ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as roles,
            pg_catalog.shobj_description(r.oid, 'pg_authid') AS comment
     FROM pg_catalog.pg_roles r
              LEFT JOIN pg_db_role_setting rs ON r.oid = rs.setrole
              LEFT JOIN pg_authid a ON r.oid = a.oid
     WHERE r.rolname !~ '^pg_'
     ORDER BY 1) u;

创建

请尽可能通过声明的方式创建业务用户与业务数据库,而不是在数据库中手工创建。因为业务用户与业务数据库需要同时在数据库与连接池中进行变更。详情请参考:创建业务用户

在运行中的数据库集群中创建新的业务用户,首先应在集群级配置中添加新用户的定义,例如在pg-test.vars.pg_users加入新的用户对象。然后可以使用pgsql-createuser剧本创建用户:

例如,在pg-test 集群中创建或修改名为dbuser_test的用户,可以执行以下命令。

./pgsql-createuser.yml -l <pg_cluster>  -e pg_user=dbuser_test

如果dbuser_test的定义不存在,则会在检查阶段报错。

2.3 - Customize database

Configure business database

可以通过 pg_databases 定制集群特定的业务数据库。

样例

一个完整的数据库定义由一个JSON/YAML对象构成,如下所示:

- name: meta                      # name is the only required field for a database
  owner: postgres                 # optional, database owner
  template: template1             # optional, template1 by default
  encoding: UTF8                # optional, UTF8 by default , must same as template database, leave blank to set to db default
  locale: C                     # optional, C by default , must same as template database, leave blank to set to db default
  lc_collate: C                 # optional, C by default , must same as template database, leave blank to set to db default
  lc_ctype: C                   # optional, C by default , must same as template database, leave blank to set to db default
  allowconn: true                 # optional, true by default, false disable connect at all
  revokeconn: false               # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
  tablespace: pg_default          # optional, 'pg_default' is the default tablespace
  connlimit: -1                   # optional, connection limit, -1 or none disable limit (default)
  schemas: [public,monitor]       # create additional schema
  extensions:                     # optional, extension name and where to create
    - {name: postgis, schema: public}
  parameters:                     # optional, extra parameters with ALTER DATABASE
    enable_partitionwise_join: true
  pgbouncer: true                 # optional, add this database to pgbouncer list? true by default
  comment: pigsty meta database   # optional, comment string for database

说明

一个数据库对象由以下键值构成,只有数据库名是必选项,其他参数均为可选,不添加相应键则会使用默认值。

  • name(string) : 数据库名称,必选项

  • owner(string) :数据库的属主,必须为已存在的用户(用户先于数据库创建)。

  • template(string):创建数据库时所使用的模板,默认为template1

  • encoding(enum):数据库使用的字符集编码,默认为UTF8,必须与实例和模板数据库保持一致。

  • locale(enum):数据库使用的本地化规则,默认与实例和模板数据库保持一致,建议不要修改。

  • lc_collate(enum):数据库使用的本地化字符串排序规则,默认为与实例和模板数据库保持一致,建议不要修改。

  • lc_ctype(enum):数据库使用的本地化规则,默认与实例和模板数据库保持一致,建议不要修改。

  • allowconn(bool):是否允许连接至数据库,默认允许。

  • revokeconn(bool):是否回收PUBLIC默认连接至数据库的权限?默认不回收,建议在多DB实例上开启。

  • tablespace(string):数据库的默认表空间,默认为pg_default

  • connlimit(number) : 是否限制数据库的连接数量?留空或-1不限,默认不限

  • schemas(string[]):需要在该数据库中额外创建的模式(默认会创建monitor模式)

  • extensions(extension[]):数据库中额外安装的扩展,每个扩展包括nameschema两个字段。

    例如{name: postgis, schema: public} 指示Pigsty在该数据库的public模式下安装PostGIS扩展

  • pgbouncer(bool) : 是否将数据库加入连接池DB列表中?默认加入

  • parameters(dict) : 针对数据库额外修改配置参数,k-v结构

  • comment(string) : 数据库备注说明信息

实现

pg_databases 是数据库定义对象构成的数组,会依次渲染为主库上的SQL文件:

/pg/tmp/pg-db-{{ database.name }}.sql

并依次执行。一个实际渲染的例子如下所示:

----------------------------------------------------------------------
-- File      :   pg-db-meta.sql
-- Path      :   /pg/tmp/pg-db-meta.sql
-- Time      :   2021-03-22 22:52
-- Note      :   managed by ansible, DO NOT CHANGE
-- Desc      :   creation sql script for database meta
----------------------------------------------------------------------


--==================================================================--
--                            EXECUTION                             --
--==================================================================--
-- run as dbsu (postgres by default)
-- createdb -w -p 5432 'meta';
-- psql meta -p 5432 -AXtwqf /pg/tmp/pg-db-meta.sql

--==================================================================--
--                         CREATE DATABASE                          --
--==================================================================--
-- create database with following commands
-- CREATE DATABASE "meta" ;
-- following commands are executed within database "meta"


--==================================================================--
--                         ALTER DATABASE                           --
--==================================================================--
-- owner

-- tablespace

-- allow connection
ALTER DATABASE "meta" ALLOW_CONNECTIONS True;

-- connection limit
ALTER DATABASE "meta" CONNECTION LIMIT -1;

-- parameters
ALTER DATABASE "meta" SET enable_partitionwise_join = True;

-- comment
COMMENT ON DATABASE "meta" IS 'pigsty meta database';


--==================================================================--
--                       REVOKE/GRANT CONNECT                       --
--==================================================================--

--==================================================================--
--                       REVOKE/GRANT CREATE                        --
--==================================================================--
-- revoke create (schema) privilege from public
REVOKE CREATE ON DATABASE "meta" FROM PUBLIC;

-- only admin role have create privilege
GRANT CREATE ON DATABASE "meta" TO "dbrole_admin";

-- revoke public schema creation
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- admin can create objects in public schema
GRANT CREATE ON SCHEMA public TO "dbrole_admin";


--==================================================================--
--                          CREATE SCHEMAS                          --
--==================================================================--
-- create schemas


--==================================================================--
--                        CREATE EXTENSIONS                        --
--==================================================================--
-- create extensions
CREATE EXTENSION IF NOT EXISTS "postgis" WITH SCHEMA "public";


--==================================================================--
--                        PGBOUNCER DATABASE                        --
--==================================================================--
-- database will be added to pgbouncer database list by default,
-- unless pgbouncer is explicitly set to 'false', means hidden database

-- Database 'meta' will be added to /etc/pgbouncer/database.txt via
-- /pg/bin/pgbouncer-create-db 'meta'


--==================================================================--

连接池

Pgbouncer有自己的数据库定义文件,通常是PG数据库的一个子集。

在Pigsty中,Pgbouncer的数据库定义文件位于:/etc/pgbouncer/database.txt

$ cat database.txt
meta = host=/var/run/postgresql

只有在该文件中出现的数据库,才可以通过PGbouncer访问。pgbouncer选项显式配置为false的数据库不会被添加至连接池DB列表中。修改该配置文件需要reload Pgbouncer方可生效。

导出

以下SQL查询可以以JSON格式导出当前数据库的定义(需少量修正)

psql  -AXtw  <<-EOF
SELECT jsonb_pretty(row_to_json(final)::JSONB)
FROM (SELECT datname               AS name,
             datdba::RegRole::Text AS owner,
             encoding,
             datcollate            AS lc_collate,
             datctype              AS lc_ctype,
             datallowconn          AS allowconn,
             datconnlimit          AS connlimit,
             (SELECT json_agg(nspname) AS schemas FROM pg_namespace WHERE nspname !~ '^pg_' AND nspname NOT IN ('information_schema', 'monitor', 'repack')),
             (SELECT json_agg(row_to_json(ex)) AS extensions FROM (SELECT extname, extnamespace::RegNamespace AS schema FROM pg_extension WHERE extnamespace::RegNamespace::TEXT NOT IN ('information_schema', 'monitor', 'repack', 'pg_catalog')) ex),
             (SELECT json_object_agg(substring(cfg, 0 , strpos(cfg, '=')), substring(cfg, strpos(cfg, '=')+1)) AS value  FROM
                 (SELECT unnest(setconfig) AS cfg FROM pg_db_role_setting s JOIN pg_database d ON d.oid = s.setdatabase WHERE d.datname = current_database()) cf
             )
      FROM pg_database WHERE datname = current_database()
     ) final;
EOF

创建

请尽可能通过声明的方式创建业务数据库,而不是在数据库中手工创建。因为业务用户与业务数据库需要同时在数据库与连接池中进行变更。

在运行中的数据库集群中创建新的业务数据库,首先应当在集群级配置中添加新数据库的定义,例如在pg-test.vars.pg_databases加入新的数据库对象。然后可以使用pgsql-createdb剧本创建数据库:

例如,在pg-test 集群中创建或修改名为test的数据库,可以执行以下命令。

./pgsql-createdb.yml -l <pg_cluster>  -e pg_database=test

如果数据库test的定义不存在,则会在检查阶段报错。

2.4 - Customize Template

Customize template and other content in database clusters

相关参数

用户可以使用 PG模板 配置项,对集群中的模板数据库 template1 进行定制。

通过这种方式确保任何在该数据库集群中新创建的数据库都带有相同的默认配置:模式,扩展,默认权限。

名称 类型 层级 说明
pg_init string G/C 自定义PG初始化脚本
pg_replication_username string G PG复制用户
pg_replication_password string G PG复制用户的密码
pg_monitor_username string G PG监控用户
pg_monitor_password string G PG监控用户密码
pg_admin_username string G PG管理用户
pg_admin_password string G PG管理用户密码
pg_default_roles role[] G 默认创建的角色与用户
pg_default_privilegs string[] G 数据库默认权限配置
pg_default_schemas string[] G 默认创建的模式
pg_default_extensions extension[] G 默认安装的扩展
pg_hba_rules rule[] G 全局HBA规则
pg_hba_rules_extra rule[] C/I 集群/实例特定的HBA规则
pgbouncer_hba_rules rule[] G/C Pgbouncer全局HBA规则
pgbouncer_hba_rules_extra rule[] G/C Pgbounce特定HBA规则
^---/pg/bin/pg-init
          |
          ^---(1)--- /pg/tmp/pg-init-roles.sql
          ^---(2)--- /pg/tmp/pg-init-template.sql
          ^---(3)--- <other customize logic in pg-init>

# 业务用户与数据库并不是在模版定制中创建的
^-------------(4)--- /pg/tmp/pg-user-{{ user.name }}.sql
^-------------(5)--- /pg/tmp/pg-db-{{ db.name }}.sql

pg-init

pg-init是用于自定义初始化模板的Shell脚本路径,该脚本将以postgres用户身份,仅在主库上执行,执行时数据库集群主库已经被拉起,可以执行任意Shell命令,或通过psql执行任意SQL命令。

如果不指定该配置项,Pigsty会使用默认的pg-init Shell脚本,如下所示。

#!/usr/bin/env bash
set -uo pipefail


#==================================================================#
#                          Default Roles                           #
#==================================================================#
psql postgres -qAXwtf /pg/tmp/pg-init-roles.sql


#==================================================================#
#                          System Template                         #
#==================================================================#
# system default template
psql template1 -qAXwtf /pg/tmp/pg-init-template.sql

# make postgres same as templated database (optional)
psql postgres  -qAXwtf /pg/tmp/pg-init-template.sql



#==================================================================#
#                          Customize Logic                         #
#==================================================================#
# add your template logic here

如果用户需要执行复杂的定制逻辑,可在该脚本的基础上进行追加。注意pg-init 用于定制数据库集群,通常这是通过修改 模板数据库 实现的。在该脚本执行时,数据库集群已经启动,但业务用户与业务数据库尚未创建。因此模板数据库的修改会反映在默认定义的业务数据库中。

pg-init-roles.sql

pg_default_roles 中可以自定义全局统一的角色体系。其中的定义会被渲染为/pg/tmp/pg-init-roles.sqlpg-test集群中的渲染样例如下所示:

```sql ---------------------------------------------------------------------- -- File : pg-init-roles.sql -- Path : /pg/tmp/pg-init-roles -- Time : 2021-03-16 21:24 -- Note : managed by ansible, DO NOT CHANGE -- Desc : creation sql script for default roles ----------------------------------------------------------------------

–###################################################################– – dbrole_readonly – –###################################################################– – run as dbsu (postgres by default) – createuser -w -p 5432 –no-login’dbrole_readonly'; – psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbrole_readonly.sql

–==================================================================– – CREATE USER – –==================================================================– CREATE USER “dbrole_readonly” NOLOGIN;

–==================================================================– – ALTER USER – –==================================================================– – options ALTER USER “dbrole_readonly” NOLOGIN;

– password

– expire

– conn limit

– parameters

– comment COMMENT ON ROLE “dbrole_readonly” IS ‘role for global readonly access’;

–==================================================================– – GRANT ROLE – –==================================================================–

–==================================================================– – PGBOUNCER USER – –==================================================================– – user will not be added to pgbouncer user list by default, – unless pgbouncer is explicitly set to ‘true’, which means production user

– User ‘dbrole_readonly’ will NOT be added to /etc/pgbouncer/userlist.txt

–==================================================================–

–###################################################################– – dbrole_readwrite – –###################################################################– – run as dbsu (postgres by default) – createuser -w -p 5432 –no-login’dbrole_readwrite'; – psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbrole_readwrite.sql

–==================================================================– – CREATE USER – –==================================================================– CREATE USER “dbrole_readwrite” NOLOGIN;

–==================================================================– – ALTER USER – –==================================================================– – options ALTER USER “dbrole_readwrite” NOLOGIN;

– password

– expire

– conn limit

– parameters

– comment COMMENT ON ROLE “dbrole_readwrite” IS ‘role for global read-write access’;

–==================================================================– – GRANT ROLE – –==================================================================– GRANT “dbrole_readonly” TO “dbrole_readwrite”;

–==================================================================– – PGBOUNCER USER – –==================================================================– – user will not be added to pgbouncer user list by default, – unless pgbouncer is explicitly set to ‘true’, which means production user

– User ‘dbrole_readwrite’ will NOT be added to /etc/pgbouncer/userlist.txt

–==================================================================–

–###################################################################– – dbrole_offline – –###################################################################– – run as dbsu (postgres by default) – createuser -w -p 5432 –no-login’dbrole_offline'; – psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbrole_offline.sql

–==================================================================– – CREATE USER – –==================================================================– CREATE USER “dbrole_offline” NOLOGIN;

–==================================================================– – ALTER USER – –==================================================================– – options ALTER USER “dbrole_offline” NOLOGIN;

– password

– expire

– conn limit

– parameters

– comment COMMENT ON ROLE “dbrole_offline” IS ‘role for restricted read-only access (offline instance)';

–==================================================================– – GRANT ROLE – –==================================================================–

–==================================================================– – PGBOUNCER USER – –==================================================================– – user will not be added to pgbouncer user list by default, – unless pgbouncer is explicitly set to ‘true’, which means production user

– User ‘dbrole_offline’ will NOT be added to /etc/pgbouncer/userlist.txt

–==================================================================–

–###################################################################– – dbrole_admin – –###################################################################– – run as dbsu (postgres by default) – createuser -w -p 5432 –no-login’dbrole_admin’; – psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbrole_admin.sql

–==================================================================– – CREATE USER – –==================================================================– CREATE USER “dbrole_admin” NOLOGIN BYPASSRLS;

–==================================================================– – ALTER USER – –==================================================================– – options ALTER USER “dbrole_admin” NOLOGIN BYPASSRLS;

– password

– expire

– conn limit

– parameters

– comment COMMENT ON ROLE “dbrole_admin” IS ‘role for object creation’;

–==================================================================– – GRANT ROLE – –==================================================================– GRANT “dbrole_readwrite” TO “dbrole_admin”; GRANT “pg_monitor” TO “dbrole_admin”; GRANT “pg_signal_backend” TO “dbrole_admin”;

–==================================================================– – PGBOUNCER USER – –==================================================================– – user will not be added to pgbouncer user list by default, – unless pgbouncer is explicitly set to ‘true’, which means production user

– User ‘dbrole_admin’ will NOT be added to /etc/pgbouncer/userlist.txt

–==================================================================–

–###################################################################– – postgres – –###################################################################– – run as dbsu (postgres by default) – createuser -w -p 5432 –superuser’postgres'; – psql -p 5432 -AXtwqf /pg/tmp/pg-user-postgres.sql

–==================================================================– – CREATE USER – –==================================================================– CREATE USER “postgres” SUPERUSER;

–==================================================================– – ALTER USER – –==================================================================– – options ALTER USER “postgres” SUPERUSER;

– password

– expire

– conn limit

– parameters

– comment COMMENT ON ROLE “postgres” IS ‘system superuser’;

–==================================================================– – GRANT ROLE – –==================================================================–

–==================================================================– – PGBOUNCER USER – –==================================================================– – user will not be added to pgbouncer user list by default, – unless pgbouncer is explicitly set to ‘true’, which means production user

– User ‘postgres’ will NOT be added to /etc/pgbouncer/userlist.txt

–==================================================================–

–###################################################################– – replicator – –###################################################################– – run as dbsu (postgres by default) – createuser -w -p 5432 –replication’replicator'; – psql -p 5432 -AXtwqf /pg/tmp/pg-user-replicator.sql

–==================================================================– – CREATE USER – –==================================================================– CREATE USER “replicator” REPLICATION BYPASSRLS;

–==================================================================– – ALTER USER – –==================================================================– – options ALTER USER “replicator” REPLICATION BYPASSRLS;

– password

– expire

– conn limit

– parameters

– comment COMMENT ON ROLE “replicator” IS ‘system replicator’;

–==================================================================– – GRANT ROLE – –==================================================================– GRANT “pg_monitor” TO “replicator”; GRANT “dbrole_readonly” TO “replicator”;

–==================================================================– – PGBOUNCER USER – –==================================================================– – user will not be added to pgbouncer user list by default, – unless pgbouncer is explicitly set to ‘true’, which means production user

– User ‘replicator’ will NOT be added to /etc/pgbouncer/userlist.txt

–==================================================================–

–###################################################################– – dbuser_monitor – –###################################################################– – run as dbsu (postgres by default) – createuser -w -p 5432 ‘dbuser_monitor’; – psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbuser_monitor.sql

–==================================================================– – CREATE USER – –==================================================================– CREATE USER “dbuser_monitor” ;

–==================================================================– – ALTER USER – –==================================================================– – options ALTER USER “dbuser_monitor” ;

– password

– expire

– conn limit ALTER USER “dbuser_monitor” CONNECTION LIMIT 16;

– parameters

– comment COMMENT ON ROLE “dbuser_monitor” IS ‘system monitor user’;

–==================================================================– – GRANT ROLE – –==================================================================– GRANT “pg_monitor” TO “dbuser_monitor”; GRANT “dbrole_readonly” TO “dbuser_monitor”;

–==================================================================– – PGBOUNCER USER – –==================================================================– – user will not be added to pgbouncer user list by default, – unless pgbouncer is explicitly set to ‘true’, which means production user

– User ‘dbuser_monitor’ will NOT be added to /etc/pgbouncer/userlist.txt

–==================================================================–

–###################################################################– – dbuser_admin – –###################################################################– – run as dbsu (postgres by default) – createuser -w -p 5432 –superuser’dbuser_admin'; – psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbuser_admin.sql

–==================================================================– – CREATE USER – –==================================================================– CREATE USER “dbuser_admin” SUPERUSER BYPASSRLS;

–==================================================================– – ALTER USER – –==================================================================– – options ALTER USER “dbuser_admin” SUPERUSER BYPASSRLS;

– password

– expire

– conn limit

– parameters

– comment COMMENT ON ROLE “dbuser_admin” IS ‘system admin user’;

–==================================================================– – GRANT ROLE – –==================================================================– GRANT “dbrole_admin” TO “dbuser_admin”;

–==================================================================– – PGBOUNCER USER – –==================================================================– – user will not be added to pgbouncer user list by default, – unless pgbouncer is explicitly set to ‘true’, which means production user

– User ‘dbuser_admin’ will NOT be added to /etc/pgbouncer/userlist.txt

–==================================================================–

–###################################################################– – dbuser_stats – –###################################################################– – run as dbsu (postgres by default) – createuser -w -p 5432 ‘dbuser_stats’; – psql -p 5432 -AXtwqf /pg/tmp/pg-user-dbuser_stats.sql

–==================================================================– – CREATE USER – –==================================================================– CREATE USER “dbuser_stats” ;

–==================================================================– – ALTER USER – –==================================================================– – options ALTER USER “dbuser_stats” ;

– password ALTER USER “dbuser_stats” PASSWORD ‘DBUser.Stats’;

– expire

– conn limit

– parameters

– comment COMMENT ON ROLE “dbuser_stats” IS ‘business offline user for offline queries and ETL’;

–==================================================================– – GRANT ROLE – –==================================================================– GRANT “dbrole_offline” TO “dbuser_stats”;

–==================================================================– – PGBOUNCER USER – –==================================================================– – user will not be added to pgbouncer user list by default, – unless pgbouncer is explicitly set to ‘true’, which means production user

– User ‘dbuser_stats’ will NOT be added to /etc/pgbouncer/userlist.txt

–==================================================================–

–==================================================================– – PASSWORD OVERWRITE – –==================================================================– ALTER ROLE “replicator” PASSWORD ‘DBUser.Replicator’; ALTER ROLE “dbuser_monitor” PASSWORD ‘DBUser.Monitor’; ALTER ROLE “dbuser_admin” PASSWORD ‘DBUser.Admin’; –==================================================================–


</details>





## pg-init-template.sql

[`pg-init-template.sql`](https://github.com/Vonng/pigsty/blob/master/roles/postgres/templates/pg-init-template.sql) 是用于初始化 `template1` 数据的脚本模板。PG模板中的变量,大抵都是通过该SQL模板渲染为最终执行的SQL命令。该模板会被渲染至集群主库的`/pg/tmp/pg-init-template.sql`并执行。

Pigsty强烈建议通过提供自定义的`pg-init`脚本完成复杂的定制。如无必要,尽量不要改动`pg-init-template.sql`中的原有逻辑。

​```sql
--==================================================================--
--                           Executions                             --
--==================================================================--
-- psql template1 -AXtwqf /pg/tmp/pg-init-template.sql
-- this sql scripts is responsible for post-init procedure
-- it will
--    * create system users such as replicator, monitor user, admin user
--    * create system default roles
--    * create schema, extensions in template1 & postgres
--    * create monitor views in template1 & postgres


--==================================================================--
--                          Default Privileges                      --
--==================================================================--
{% for priv in pg_default_privileges %}
ALTER DEFAULT PRIVILEGES FOR ROLE {{ pg_dbsu }} {{ priv }};
{% endfor %}

{% for priv in pg_default_privileges %}
ALTER DEFAULT PRIVILEGES FOR ROLE {{ pg_admin_username }} {{ priv }};
{% endfor %}

-- for additional business admin, they can SET ROLE to dbrole_admin
{% for priv in pg_default_privileges %}
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" {{ priv }};
{% endfor %}

--==================================================================--
--                              Schemas                             --
--==================================================================--
{% for schema_name in pg_default_schemas %}
CREATE SCHEMA IF NOT EXISTS "{{ schema_name }}";
{% endfor %}

-- revoke public creation
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

--==================================================================--
--                             Extensions                           --
--==================================================================--
{% for extension in pg_default_extensions %}
CREATE EXTENSION IF NOT EXISTS "{{ extension.name }}"{% if 'schema' in extension %} WITH SCHEMA "{{ extension.schema }}"{% endif %};
{% endfor %}

默认的模板初始化逻辑还会创建监控模式,扩展与相关视图。

```sql --==================================================================-- -- Monitor Views -- --==================================================================--

– cleanse

CREATE SCHEMA IF NOT EXISTS monitor; GRANT USAGE ON SCHEMA monitor TO “{{ pg_monitor_username }}"; GRANT USAGE ON SCHEMA monitor TO “{{ pg_admin_username }}"; GRANT USAGE ON SCHEMA monitor TO “{{ pg_replication_username }}";

DROP VIEW IF EXISTS monitor.pg_table_bloat_human; DROP VIEW IF EXISTS monitor.pg_index_bloat_human; DROP VIEW IF EXISTS monitor.pg_table_bloat; DROP VIEW IF EXISTS monitor.pg_index_bloat; DROP VIEW IF EXISTS monitor.pg_session; DROP VIEW IF EXISTS monitor.pg_kill; DROP VIEW IF EXISTS monitor.pg_cancel; DROP VIEW IF EXISTS monitor.pg_seq_scan;


– Table bloat estimate

CREATE OR REPLACE VIEW monitor.pg_table_bloat AS SELECT CURRENT_CATALOG AS datname, nspname, relname , bs * tblpages AS size, CASE WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff)/tblpages::FLOAT ELSE 0 END AS ratio FROM ( SELECT ceil( reltuples / ( (bs-page_hdr)fillfactor/(tpl_size100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff, tblpages, fillfactor, bs, tblid, nspname, relname, is_na FROM ( SELECT ( 4 + tpl_hdr_size + tpl_data_size + (2 * ma) - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END - CASE WHEN ceil(tpl_data_size)::INT % ma = 0 THEN ma ELSE ceil(tpl_data_size)::INT % ma END ) AS tpl_size, (heappages + toastpages) AS tblpages, heappages, toastpages, reltuples, toasttuples, bs, page_hdr, tblid, nspname, relname, fillfactor, is_na FROM ( SELECT tbl.oid AS tblid, ns.nspname , tbl.relname, tbl.reltuples, tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages, coalesce(toast.reltuples, 0) AS toasttuples, coalesce(substring(array_to_string(tbl.reloptions, ' ‘) FROM ‘fillfactor=([0-9]+)')::smallint, 100) AS fillfactor, current_setting(‘block_size’)::numeric AS bs, CASE WHEN version()~‘mingw32’ OR version()~‘64-bit|x86_64|ppc64|ia64|amd64’ THEN 8 ELSE 4 END AS ma, 24 AS page_hdr, 23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END + CASE WHEN bool_or(att.attname = ‘oid’ and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size, sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size, bool_or(att.atttypid = ‘pg_catalog.name’::regtype) OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON att.attrelid = tbl.oid JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid WHERE NOT att.attisdropped AND tbl.relkind = ‘r’ AND nspname NOT IN (‘pg_catalog’,‘information_schema’) GROUP BY 1,2,3,4,5,6,7,8,9,10 ) AS s ) AS s2 ) AS s3 WHERE NOT is_na; COMMENT ON VIEW monitor.pg_table_bloat IS ‘postgres table bloat estimate’;


– Index bloat estimate

CREATE OR REPLACE VIEW monitor.pg_index_bloat AS SELECT CURRENT_CATALOG AS datname, nspname, idxname AS relname, relpages::BIGINT * bs AS size, COALESCE((relpages - ( reltuples * (6 + ma - (CASE WHEN index_tuple_hdr % ma = 0 THEN ma ELSE index_tuple_hdr % ma END) + nulldatawidth + ma - (CASE WHEN nulldatawidth % ma = 0 THEN ma ELSE nulldatawidth % ma END)) / (bs - pagehdr)::FLOAT + 1 )), 0) / relpages::FLOAT AS ratio FROM ( SELECT nspname, idxname, reltuples, relpages, current_setting(‘block_size’)::INTEGER AS bs, (CASE WHEN version() ~ ‘mingw32’ OR version() ~ ‘64-bit|x86_64|ppc64|ia64|amd64’ THEN 8 ELSE 4 END) AS ma, 24 AS pagehdr, (CASE WHEN max(COALESCE(pg_stats.null_frac, 0)) = 0 THEN 2 ELSE 6 END) AS index_tuple_hdr, sum((1.0 - COALESCE(pg_stats.null_frac, 0.0)) * COALESCE(pg_stats.avg_width, 1024))::INTEGER AS nulldatawidth FROM pg_attribute JOIN ( SELECT pg_namespace.nspname, ic.relname AS idxname, ic.reltuples, ic.relpages, pg_index.indrelid, pg_index.indexrelid, tc.relname AS tablename, regexp_split_to_table(pg_index.indkey::TEXT, ' ‘) :: INTEGER AS attnum, pg_index.indexrelid AS index_oid FROM pg_index JOIN pg_class ic ON pg_index.indexrelid = ic.oid JOIN pg_class tc ON pg_index.indrelid = tc.oid JOIN pg_namespace ON pg_namespace.oid = ic.relnamespace JOIN pg_am ON ic.relam = pg_am.oid WHERE pg_am.amname = ‘btree’ AND ic.relpages > 0 AND nspname NOT IN (‘pg_catalog’, ‘information_schema’) ) ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname AND ((pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE)) OR (pg_stats.tablename = ind_atts.idxname AND pg_stats.attname = pg_attribute.attname)) WHERE pg_attribute.attnum > 0 GROUP BY 1, 2, 3, 4, 5, 6 ) est LIMIT 512; COMMENT ON VIEW monitor.pg_index_bloat IS ‘postgres index bloat estimate (btree-only)';


– table bloat pretty

CREATE OR REPLACE VIEW monitor.pg_table_bloat_human AS SELECT nspname || ‘.’ || relname AS name, pg_size_pretty(size) AS size, pg_size_pretty((size * ratio)::BIGINT) AS wasted, round(100 * ratio::NUMERIC, 2) as ratio FROM monitor.pg_table_bloat ORDER BY wasted DESC NULLS LAST; COMMENT ON VIEW monitor.pg_table_bloat_human IS ‘postgres table bloat pretty’;


– index bloat pretty

CREATE OR REPLACE VIEW monitor.pg_index_bloat_human AS SELECT nspname || ‘.’ || relname AS name, pg_size_pretty(size) AS size, pg_size_pretty((size * ratio)::BIGINT) AS wasted, round(100 * ratio::NUMERIC, 2) as ratio FROM monitor.pg_index_bloat; COMMENT ON VIEW monitor.pg_index_bloat_human IS ‘postgres index bloat pretty’;


– pg session

CREATE OR REPLACE VIEW monitor.pg_session AS SELECT coalesce(datname, ‘all’) AS datname, numbackends, active, idle, ixact, max_duration, max_tx_duration, max_conn_duration FROM ( SELECT datname, count() AS numbackends, count() FILTER ( WHERE state = ‘active’ ) AS active, count() FILTER ( WHERE state = ‘idle’ ) AS idle, count() FILTER ( WHERE state = ‘idle in transaction’ OR state = ‘idle in transaction (aborted)’ ) AS ixact, max(extract(epoch from now() - state_change)) FILTER ( WHERE state = ‘active’ ) AS max_duration, max(extract(epoch from now() - xact_start)) AS max_tx_duration, max(extract(epoch from now() - backend_start)) AS max_conn_duration FROM pg_stat_activity WHERE backend_type = ‘client backend’ AND pid <> pg_backend_pid() GROUP BY ROLLUP (1) ORDER BY 1 NULLS FIRST ) t; COMMENT ON VIEW monitor.pg_session IS ‘postgres session stats’;


– pg kill

CREATE OR REPLACE VIEW monitor.pg_kill AS SELECT pid, pg_terminate_backend(pid) AS killed, datname AS dat, usename AS usr, application_name AS app, client_addr AS addr, state, extract(epoch from now() - state_change) AS query_time, extract(epoch from now() - xact_start) AS xact_time, extract(epoch from now() - backend_start) AS conn_time, substring(query, 1, 40) AS query FROM pg_stat_activity WHERE backend_type = ‘client backend’ AND pid <> pg_backend_pid(); COMMENT ON VIEW monitor.pg_kill IS ‘kill all backend session’;


– quick cancel view

DROP VIEW IF EXISTS monitor.pg_cancel; CREATE OR REPLACE VIEW monitor.pg_cancel AS SELECT pid, pg_cancel_backend(pid) AS cancel, datname AS dat, usename AS usr, application_name AS app, client_addr AS addr, state, extract(epoch from now() - state_change) AS query_time, extract(epoch from now() - xact_start) AS xact_time, extract(epoch from now() - backend_start) AS conn_time, substring(query, 1, 40) FROM pg_stat_activity WHERE state = ‘active’ AND backend_type = ‘client backend’ and pid <> pg_backend_pid(); COMMENT ON VIEW monitor.pg_cancel IS ‘cancel backend queries’;


– seq scan

DROP VIEW IF EXISTS monitor.pg_seq_scan; CREATE OR REPLACE VIEW monitor.pg_seq_scan AS SELECT schemaname AS nspname, relname, seq_scan, seq_tup_read, seq_tup_read / seq_scan AS seq_tup_avg, idx_scan, n_live_tup + n_dead_tup AS tuples, n_live_tup / (n_live_tup + n_dead_tup) AS dead_ratio FROM pg_stat_user_tables WHERE seq_scan > 0 and (n_live_tup + n_dead_tup) > 0 ORDER BY seq_tup_read DESC LIMIT 50; COMMENT ON VIEW monitor.pg_seq_scan IS ‘table that have seq scan’;

{% if pg_version >= 13 %}

– pg_shmem auxiliary function – PG 13 ONLY!

CREATE OR REPLACE FUNCTION monitor.pg_shmem() RETURNS SETOF pg_shmem_allocations AS $$ SELECT * FROM pg_shmem_allocations;$$ LANGUAGE SQL SECURITY DEFINER; COMMENT ON FUNCTION monitor.pg_shmem() IS ‘security wrapper for pg_shmem’; {% endif %}

–==================================================================– – Customize Logic – –==================================================================– – This script will be execute on primary instance among a newly created – postgres cluster. it will be executed as dbsu on template1 database – put your own customize logic here – make sure they are idempotent


</details>



一个实际的渲染样例(`pg-test`)如下所示:

<details>


​```sql
----------------------------------------------------------------------
-- File      :   pg-init-template.sql
-- Ctime     :   2018-10-30
-- Mtime     :   2021-02-27
-- Desc      :   init postgres cluster template
-- Path      :   /pg/tmp/pg-init-template.sql
-- Author    :   Vonng(fengruohang@outlook.com)
-- Copyright (C) 2018-2021 Ruohang Feng
----------------------------------------------------------------------


--==================================================================--
--                           Executions                             --
--==================================================================--
-- psql template1 -AXtwqf /pg/tmp/pg-init-template.sql
-- this sql scripts is responsible for post-init procedure
-- it will
--    * create system users such as replicator, monitor user, admin user
--    * create system default roles
--    * create schema, extensions in template1 & postgres
--    * create monitor views in template1 & postgres


--==================================================================--
--                          Default Privileges                      --
--==================================================================--
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT USAGE                         ON SCHEMAS   TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT                        ON TABLES    TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT                        ON SEQUENCES TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT EXECUTE                       ON FUNCTIONS TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT USAGE                         ON SCHEMAS   TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT                        ON TABLES    TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT                        ON SEQUENCES TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT EXECUTE                       ON FUNCTIONS TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT INSERT, UPDATE, DELETE        ON TABLES    TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT USAGE,  UPDATE                ON SEQUENCES TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES    TO dbrole_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT CREATE                        ON SCHEMAS   TO dbrole_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT USAGE                         ON SCHEMAS   TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT SELECT                        ON TABLES    TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT SELECT                        ON SEQUENCES TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT EXECUTE                       ON FUNCTIONS TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT USAGE                         ON SCHEMAS   TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT SELECT                        ON TABLES    TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT SELECT                        ON SEQUENCES TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT EXECUTE                       ON FUNCTIONS TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT INSERT, UPDATE, DELETE        ON TABLES    TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT USAGE,  UPDATE                ON SEQUENCES TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES    TO dbrole_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE dbuser_admin GRANT CREATE                        ON SCHEMAS   TO dbrole_admin;

-- for additional business admin, they can SET ROLE to dbrole_admin
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT USAGE                         ON SCHEMAS   TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT SELECT                        ON TABLES    TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT SELECT                        ON SEQUENCES TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT EXECUTE                       ON FUNCTIONS TO dbrole_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT USAGE                         ON SCHEMAS   TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT SELECT                        ON TABLES    TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT SELECT                        ON SEQUENCES TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT EXECUTE                       ON FUNCTIONS TO dbrole_offline;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT INSERT, UPDATE, DELETE        ON TABLES    TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT USAGE,  UPDATE                ON SEQUENCES TO dbrole_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES    TO dbrole_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" GRANT CREATE                        ON SCHEMAS   TO dbrole_admin;

--==================================================================--
--                              Schemas                             --
--==================================================================--
CREATE SCHEMA IF NOT EXISTS "monitor";

-- revoke public creation
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

--==================================================================--
--                             Extensions                           --
--==================================================================--
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pgstattuple" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_qualstats" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_buffercache" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pageinspect" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_prewarm" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_visibility" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_freespacemap" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "pg_repack" WITH SCHEMA "monitor";
CREATE EXTENSION IF NOT EXISTS "postgres_fdw";
CREATE EXTENSION IF NOT EXISTS "file_fdw";
CREATE EXTENSION IF NOT EXISTS "btree_gist";
CREATE EXTENSION IF NOT EXISTS "btree_gin";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";
CREATE EXTENSION IF NOT EXISTS "intagg";
CREATE EXTENSION IF NOT EXISTS "intarray";



--==================================================================--
--                            Monitor Views                         --
--==================================================================--

----------------------------------------------------------------------
-- cleanse
----------------------------------------------------------------------
CREATE SCHEMA IF NOT EXISTS monitor;
GRANT USAGE ON SCHEMA monitor TO "dbuser_monitor";
GRANT USAGE ON SCHEMA monitor TO "dbuser_admin";
GRANT USAGE ON SCHEMA monitor TO "replicator";

DROP VIEW IF EXISTS monitor.pg_table_bloat_human;
DROP VIEW IF EXISTS monitor.pg_index_bloat_human;
DROP VIEW IF EXISTS monitor.pg_table_bloat;
DROP VIEW IF EXISTS monitor.pg_index_bloat;
DROP VIEW IF EXISTS monitor.pg_session;
DROP VIEW IF EXISTS monitor.pg_kill;
DROP VIEW IF EXISTS monitor.pg_cancel;
DROP VIEW IF EXISTS monitor.pg_seq_scan;


----------------------------------------------------------------------
-- Table bloat estimate
----------------------------------------------------------------------
CREATE OR REPLACE VIEW monitor.pg_table_bloat AS
    SELECT CURRENT_CATALOG AS datname, nspname, relname , bs * tblpages AS size,
           CASE WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff)/tblpages::FLOAT ELSE 0 END AS ratio
    FROM (
             SELECT ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
                    tblpages, fillfactor, bs, tblid, nspname, relname, is_na
             FROM (
                      SELECT
                          ( 4 + tpl_hdr_size + tpl_data_size + (2 * ma)
                              - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END
                              - CASE WHEN ceil(tpl_data_size)::INT % ma = 0 THEN ma ELSE ceil(tpl_data_size)::INT % ma END
                              ) AS tpl_size, (heappages + toastpages) AS tblpages, heappages,
                          toastpages, reltuples, toasttuples, bs, page_hdr, tblid, nspname, relname, fillfactor, is_na
                      FROM (
                               SELECT
                                   tbl.oid AS tblid, ns.nspname , tbl.relname, tbl.reltuples,
                                   tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
                                   coalesce(toast.reltuples, 0) AS toasttuples,
                                   coalesce(substring(array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
                                   current_setting('block_size')::numeric AS bs,
                                   CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
                                   24 AS page_hdr,
                                   23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END
                                       + CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
                                   sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size,
                                   bool_or(att.atttypid = 'pg_catalog.name'::regtype)
                                       OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na
                               FROM pg_attribute AS att
                                        JOIN pg_class AS tbl ON att.attrelid = tbl.oid
                                        JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
                                        LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
                                        LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
                               WHERE NOT att.attisdropped AND tbl.relkind = 'r' AND nspname NOT IN ('pg_catalog','information_schema')
                               GROUP BY 1,2,3,4,5,6,7,8,9,10
                           ) AS s
                  ) AS s2
         ) AS s3
    WHERE NOT is_na;
COMMENT ON VIEW monitor.pg_table_bloat IS 'postgres table bloat estimate';

----------------------------------------------------------------------
-- Index bloat estimate
----------------------------------------------------------------------
CREATE OR REPLACE VIEW monitor.pg_index_bloat AS
    SELECT CURRENT_CATALOG AS datname, nspname, idxname AS relname, relpages::BIGINT * bs AS size,
           COALESCE((relpages - ( reltuples * (6 + ma - (CASE WHEN index_tuple_hdr % ma = 0 THEN ma ELSE index_tuple_hdr % ma END)
                                                   + nulldatawidth + ma - (CASE WHEN nulldatawidth % ma = 0 THEN ma ELSE nulldatawidth % ma END))
                                      / (bs - pagehdr)::FLOAT  + 1 )), 0) / relpages::FLOAT AS ratio
    FROM (
             SELECT nspname,
                    idxname,
                    reltuples,
                    relpages,
                    current_setting('block_size')::INTEGER                                                               AS bs,
                    (CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END)  AS ma,
                    24                                                                                                   AS pagehdr,
                    (CASE WHEN max(COALESCE(pg_stats.null_frac, 0)) = 0 THEN 2 ELSE 6 END)                               AS index_tuple_hdr,
                    sum((1.0 - COALESCE(pg_stats.null_frac, 0.0)) *
                        COALESCE(pg_stats.avg_width, 1024))::INTEGER                                                     AS nulldatawidth
             FROM pg_attribute
                      JOIN (
                 SELECT pg_namespace.nspname,
                        ic.relname                                                   AS idxname,
                        ic.reltuples,
                        ic.relpages,
                        pg_index.indrelid,
                        pg_index.indexrelid,
                        tc.relname                                                   AS tablename,
                        regexp_split_to_table(pg_index.indkey::TEXT, ' ') :: INTEGER AS attnum,
                        pg_index.indexrelid                                          AS index_oid
                 FROM pg_index
                          JOIN pg_class ic ON pg_index.indexrelid = ic.oid
                          JOIN pg_class tc ON pg_index.indrelid = tc.oid
                          JOIN pg_namespace ON pg_namespace.oid = ic.relnamespace
                          JOIN pg_am ON ic.relam = pg_am.oid
                 WHERE pg_am.amname = 'btree' AND ic.relpages > 0 AND nspname NOT IN ('pg_catalog', 'information_schema')
             ) ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum
                      JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname
                 AND ((pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE))
                     OR (pg_stats.tablename = ind_atts.idxname AND pg_stats.attname = pg_attribute.attname))
             WHERE pg_attribute.attnum > 0
             GROUP BY 1, 2, 3, 4, 5, 6
         ) est
    LIMIT 512;
COMMENT ON VIEW monitor.pg_index_bloat IS 'postgres index bloat estimate (btree-only)';

----------------------------------------------------------------------
-- table bloat pretty
----------------------------------------------------------------------
CREATE OR REPLACE VIEW monitor.pg_table_bloat_human AS
SELECT nspname || '.' || relname AS name,
       pg_size_pretty(size)      AS size,
       pg_size_pretty((size * ratio)::BIGINT) AS wasted,
       round(100 * ratio::NUMERIC, 2)  as ratio
FROM monitor.pg_table_bloat ORDER BY wasted DESC NULLS LAST;
COMMENT ON VIEW monitor.pg_table_bloat_human IS 'postgres table bloat pretty';

----------------------------------------------------------------------
-- index bloat pretty
----------------------------------------------------------------------
CREATE OR REPLACE VIEW monitor.pg_index_bloat_human AS
SELECT nspname || '.' || relname              AS name,
       pg_size_pretty(size)                   AS size,
       pg_size_pretty((size * ratio)::BIGINT) AS wasted,
       round(100 * ratio::NUMERIC, 2)         as ratio
FROM monitor.pg_index_bloat;
COMMENT ON VIEW monitor.pg_index_bloat_human IS 'postgres index bloat pretty';


----------------------------------------------------------------------
-- pg session
----------------------------------------------------------------------
CREATE OR REPLACE VIEW monitor.pg_session AS
SELECT coalesce(datname, 'all') AS datname,
       numbackends,
       active,
       idle,
       ixact,
       max_duration,
       max_tx_duration,
       max_conn_duration
FROM (
         SELECT datname,
                count(*)                                         AS numbackends,
                count(*) FILTER ( WHERE state = 'active' )       AS active,
                count(*) FILTER ( WHERE state = 'idle' )         AS idle,
                count(*) FILTER ( WHERE state = 'idle in transaction'
                    OR state = 'idle in transaction (aborted)' ) AS ixact,
                max(extract(epoch from now() - state_change))
                FILTER ( WHERE state = 'active' )                AS max_duration,
                max(extract(epoch from now() - xact_start))      AS max_tx_duration,
                max(extract(epoch from now() - backend_start))   AS max_conn_duration
         FROM pg_stat_activity
         WHERE backend_type = 'client backend'
           AND pid <> pg_backend_pid()
         GROUP BY ROLLUP (1)
         ORDER BY 1 NULLS FIRST
     ) t;
COMMENT ON VIEW monitor.pg_session IS 'postgres session stats';


----------------------------------------------------------------------
-- pg kill
----------------------------------------------------------------------
CREATE OR REPLACE VIEW monitor.pg_kill AS
SELECT pid,
       pg_terminate_backend(pid)                 AS killed,
       datname                                   AS dat,
       usename                                   AS usr,
       application_name                          AS app,
       client_addr                               AS addr,
       state,
       extract(epoch from now() - state_change)  AS query_time,
       extract(epoch from now() - xact_start)    AS xact_time,
       extract(epoch from now() - backend_start) AS conn_time,
       substring(query, 1, 40)                   AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND pid <> pg_backend_pid();
COMMENT ON VIEW monitor.pg_kill IS 'kill all backend session';


----------------------------------------------------------------------
-- quick cancel view
----------------------------------------------------------------------
DROP VIEW IF EXISTS monitor.pg_cancel;
CREATE OR REPLACE VIEW monitor.pg_cancel AS
SELECT pid,
       pg_cancel_backend(pid)                    AS cancel,
       datname                                   AS dat,
       usename                                   AS usr,
       application_name                          AS app,
       client_addr                               AS addr,
       state,
       extract(epoch from now() - state_change)  AS query_time,
       extract(epoch from now() - xact_start)    AS xact_time,
       extract(epoch from now() - backend_start) AS conn_time,
       substring(query, 1, 40)
FROM pg_stat_activity
WHERE state = 'active'
  AND backend_type = 'client backend'
  and pid <> pg_backend_pid();
COMMENT ON VIEW monitor.pg_cancel IS 'cancel backend queries';


----------------------------------------------------------------------
-- seq scan
----------------------------------------------------------------------
DROP VIEW IF EXISTS monitor.pg_seq_scan;
CREATE OR REPLACE VIEW monitor.pg_seq_scan AS
SELECT schemaname                             AS nspname,
       relname,
       seq_scan,
       seq_tup_read,
       seq_tup_read / seq_scan                AS seq_tup_avg,
       idx_scan,
       n_live_tup + n_dead_tup                AS tuples,
       n_live_tup / (n_live_tup + n_dead_tup) AS dead_ratio
FROM pg_stat_user_tables
WHERE seq_scan > 0
  and (n_live_tup + n_dead_tup) > 0
ORDER BY seq_tup_read DESC
LIMIT 50;
COMMENT ON VIEW monitor.pg_seq_scan IS 'table that have seq scan';


----------------------------------------------------------------------
-- pg_shmem auxiliary function
-- PG 13 ONLY!
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION monitor.pg_shmem() RETURNS SETOF
    pg_shmem_allocations AS $$ SELECT * FROM pg_shmem_allocations;$$ LANGUAGE SQL SECURITY DEFINER;
COMMENT ON FUNCTION monitor.pg_shmem() IS 'security wrapper for pg_shmem';


--==================================================================--
--                          Customize Logic                         --
--==================================================================--
-- This script will be execute on primary instance among a newly created
-- postgres cluster. it will be executed as dbsu on template1 database
-- put your own customize logic here
-- make sure they are idempotent

2.5 - Customize ACL

Configure access control in Pigsty

PostgreSQL中的ACL包括两部分,用户权限体系(Privileges)Host Based Authentication (HBA)

Pigsty提供了默认访问控制系统,用户可在此基础上进一步定制,与ACL相关的配置项包括:

名称 类型 层级 说明
pg_default_roles role[] G 默认创建的角色与用户
pg_default_privilegs string[] G 数据库默认权限配置
pg_hba_rules rule[] G 全局HBA规则
pg_hba_rules_extra rule[] C/I 集群/实例特定的HBA规则
pgbouncer_hba_rules rule[] G/C Pgbouncer全局HBA规则
pgbouncer_hba_rules_extra rule[] G/C Pgbounce特定HBA规则
pg_users user[] C 业务用户
pg_databases database[] C 业务数据库

HBA规则

用户可以通过 pg_hba_rulespg_hba_rules_extra 定制 Postgres的HBA规则,通过 pgbouncer_hba_rulespgbouncer_hba_rules_extra 定制Pgbouncer的HBA规则。

一条HBA规则是一个对象,包含3个必选字段:titlerolerules

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
  • title 是这条规则的说明,会被渲染为注释信息。
  • role 是这条规则的应用范围,
  • rules 是具体的HBA规则数组,每一个元素都是一条规则五元组,请参考PG官方文档。

这样的一条规则,会被渲染至/pg/data/pg_hba.conf文件中。

#  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

规则的应用范围

规则的 role 用于控制规则安装的位置。

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

作为一个特例role: offline 的HBA规则,除了会安装至pg_role == 'offline'的实例,也会安装至pg_offline_query == true的实例上,允许离线用户访问。

规则的应用顺序

定义的HBA规则按照以下顺序生效:

特别注意

请注意,因为在实际生产应用中,通常会基于实例的角色,对HBA进行区分与细化管理。Pigsty不建议通过Patroni管理HBA配置。如果配置了Patroni中的HBA规则,数据库的HBA会在重启时被Patroni所覆盖。

3 - Playbooks

Built-in playbooks to perform various tasks

Pigsty uses a declarative interface, [configuration](. /config/) is done, just run the fixed Playbook and you’re done deploying

Basic Deployment

Sandbox Deployment

Monitor-only deployments

Day-to-day management

Pigsty also provides a number of pre-built scripts for daily operations and maintenance management.

3.1 - Infra Provision

How to pull up infrasturcture on meta node

Overview

Infrastructure initialization is done via infra.yml. The script completes the installation and deployment of infrastructure on the meta node.

``infra.ymltakes the metanode (default group namemeta`) as the deployment target.

. /infra.yml

!

Caution

❗️ The initialization of the meta node must be completed before the initialization of the database node can be executed properly

infra.yml is fixed to work on the group named meta in the configuration file

meta nodes can be reused as normal nodes, i.e. PostgreSQL databases can be defined and created on meta nodes as well.

Pigsty recommends using the default configuration and creating a cluster of pg-meta meta-databases on the meta-nodes for hosting Pigsty advanced features.

A complete execution of the initialization process can take 2 to 8 minutes, depending on the machine configuration.

Selective execution

Users can selectively execute a subset of scripts through ansible’s tagging mechanism.

For example, if you want to execute only the local source initialization part, you can do so with the following command.

. /infra.yml --tags=repo

Please refer to [task details](#task details) for specific tags

Some common subsets of tasks include.

. /infra.yml --tags=repo -e repo_rebuild=true # Force a local source to be recreated
. /infra.yml --tags=prometheus_reload # Reload Prometheus configuration
. /infra.yml --tags=nginx_haproxy # Regenerate the Nginx Haproxy index page
. /infra.yml --tags=prometheus_targets,prometheus_reload # Regenerate the Prometheus static monitoring object file and apply

script description

infra.yml does the following stuff:

  • Deploy and enable local sources
  • Complete metanode initialization
  • Complete meta-node infrastructure initialization
    • CA infrastructure
    • DNS Nameserver
    • Nginx
    • Prometheus & Alertmanger
    • Grafana
  • Copy the Pigsty ontology to the meta node
  • Complete database initialization on the meta node (optional, users can reuse the meta node through the standard database cluster initialization process)

Content

#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File      :   infra.yml
# Ctime     :   2020-04-13
# Mtime     :   2020-07-23
# Desc      :   init infrastructure on meta nodes
# Path      :   infra.yml
# Copyright (C) 2018-2021 Ruohang Feng
#==============================================================#


#------------------------------------------------------------------------------
# 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-remove.yml pgsql-createdb.yml pgsql-createuser.yml pgsql-service.yml pgsql-monitor.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            



#------------------------------------------------------------------------------
# meta node database (optional)
#------------------------------------------------------------------------------
# this play will create database clusters on meta nodes.
# it's good to reuse meta node as normal database nodes too
# but it's always better to leave it be.
#------------------------------------------------------------------------------
#- name: Pgsql Initialization
#  become: yes
#  hosts: meta
#  gather_facts: no
#  roles:
#    - role: postgres                        # init postgres
#      tags: [pgsql, postgres]
#
#    - role: monitor                         # init monitor system
#      tags: [pgsql, monitor]
#
#    - role: service                         # init haproxy
#      tags: [service]


...

任务详情

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

./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]
      nameserver : Register consul dnsmasq service	TAGS: [meta, nameserver]
      nameserver : Reload consul	TAGS: [meta, nameserver]
      nginx : Make sure nginx installed	TAGS: [meta, nginx, nginx_install]
      nginx : Create local html directory	TAGS: [meta, nginx, nginx_content]
      nginx : Create nginx config directory	TAGS: [meta, nginx, nginx_content]
      nginx : Update default nginx index page	TAGS: [meta, nginx, nginx_content]
      nginx : Copy nginx default config	TAGS: [meta, nginx, nginx_config]
      nginx : Copy nginx upstream conf	TAGS: [meta, nginx, nginx_config]
      nginx : Templating /etc/nginx/haproxy.conf	TAGS: [meta, nginx, nginx_haproxy]
      nginx : Render haproxy upstream in cluster mode	TAGS: [meta, nginx, nginx_haproxy]
      nginx : Render haproxy location in cluster mode	TAGS: [meta, nginx, nginx_haproxy]
      nginx : Templating haproxy cluster index	TAGS: [meta, nginx, nginx_haproxy]
      nginx : Templating haproxy cluster index	TAGS: [meta, nginx, nginx_haproxy]
      nginx : Restart meta nginx service	TAGS: [meta, nginx, nginx_restart]
      nginx : Wait for nginx service online	TAGS: [meta, nginx, nginx_restart]
      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 : Wipe out prometheus config dir	TAGS: [meta, prometheus, prometheus_clean]
      prometheus : Wipe out existing prometheus data	TAGS: [meta, prometheus, prometheus_clean]
      prometheus : Create postgres directory structure	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy prometheus bin scripts	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy prometheus rules scripts	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy altermanager config	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Render prometheus config	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Config /etc/prometheus opts	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]
      prometheus : Render prometheus targets in cluster mode	TAGS: [meta, prometheus, prometheus_targets]
      prometheus : Reload prometheus service	TAGS: [meta, prometheus, prometheus_reload]
      prometheus : Copy prometheus service definition	TAGS: [meta, prometheus, prometheus_register]
      prometheus : Copy alertmanager service definition	TAGS: [meta, prometheus, prometheus_register]
      prometheus : Reload consul to register prometheus	TAGS: [meta, prometheus, prometheus_register]
      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_clean, dcs]
      consul : Stop any running consul instance	TAGS: [consul_clean, dcs]
      consul : Remove existing consul dir	TAGS: [consul_clean, dcs]
      consul : Recreate consul dir	TAGS: [consul_clean, 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 - Pgsql Provision

How to define and create postgres clusters?

Playbook Overview

After completing the [infrastructure initialization](. /infra-provision/), users can pgsql.yml to complete the initialization of the database cluster.

First in [Pigsty configuration file](… /… //config/pgsql/) and then apply the changes to the real environment by executing pgsql.yml.

. /pgsql.yml # Execute the database cluster initialization operation on all the machines in the manifest (danger!)
. /pgsql.yml -l pg-test # Perform database cluster initialization on the machines under the pg-test manifest (recommended!)
. /pgsql.yml -l pg-meta,pg-test # Initialize both pg-meta and pg-test clusters at the same time
. /pgsql.yml -l 10.10.10.11 # Initialize the database instance on the machine 10.10.10.11

Caution

  • Using pgsql.yml without parameters is convenient, but it is a high-risk operation in production environments

    It is strongly recommended that you add the -l parameter to the execution to limit the range of objects for which the command can be executed.

  • Users can treat metanodes as normal nodes reuse, i.e. define and create PostgreSQL databases on metanodes.

    In the default sandbox environment, executing . /pgsql.yml will complete the initialization of pg-meta and pg-test at the same time.

  • Separately When performing initialization for a cluster slave, the user must make sure that the master library has been initialized, but not the master and its slaves at the same time.

Protection mechanism

pgsql.yml provides a protection mechanism determined by the configuration parameter pg_exists_action. When there is a running instance of PostgreSQL on the target machine before executing the script, Pigsty will act according to the configuration abort|clean|skip of pg_exists_action.

  • abort: recommended to be set as the default configuration to abort script execution if an existing instance is encountered to avoid accidental library deletion.
  • clean: recommended to be used in local sandbox environment, to clear existing database in case of existing instances.
  • skip: Execute subsequent logic directly on the existing database cluster.
  • You can use . /pgsql.yml -e pg_exists_action=clean to override the configuration file option and force wipe the existing instance

The pg_disable_purge`'' option provides double protection; if enabled, pg_exists_action'' is forced to be set to ``abort' and will not wipe out running database instances under any circumstances.

dcs_exists_action` and dcs_disable_purge` have the same effect as the above two options, but for DCS (Consul Agent) instances.

Selective execution

Users have the option to execute a subset of scripts through ansible’s tagging mechanism.

As an example, if you want to execute only the service initialization part, you can do so with the following command

./pgsql.yml --tags=service

Frequently used subsets

./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=service       # 完成负载均衡的部署,包括Haproxy与VIP
./pgsql.yml --tags=haproxy_config,haproxy_reload  # 修改Haproxy配置并应用。

Description

pgsql.yml does the following, among others.

  • Initialize the database node infrastructure (node)
  • Initialize the DCS Agent (or DCS Server if it is a meta-node) service (consul)
  • Install, deploy, and initialize PostgreSQL, Pgbouncer, Patroni (postgres)
  • Installation of PostgreSQL monitoring system (monitor)
  • Installing and deploying Haproxy and VIP, exposing services to the outside world (service)

Please refer to Tasks for the precise label about tasks

#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File      :   pgsql.yml
# Mtime     :   2020-05-12
# Mtime     :   2021-03-15
# Desc      :   initialize pigsty cluster
# Path      :   pgsql.yml
# Copyright (C) 2018-2021 Ruohang Feng
#==============================================================#


#------------------------------------------------------------------------------
# init node and database
#------------------------------------------------------------------------------
- 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: service                         # init service
      tags: [service]

...

Tasks

list all available tasks with following commands

./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_clean, dcs, infra]
      consul : Stop any running consul instance	TAGS: [consul_clean, dcs, infra]
      consul : Remove existing consul dir	TAGS: [consul_clean, dcs, infra]
      consul : Recreate consul dir	TAGS: [consul_clean, 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 : Copy patroni systemd service file	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 : Drop-In consul dependency for patroni	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 init roles sql	TAGS: [pg_init, pg_init_role, pgsql, postgres]
      postgres : Render init template sql	TAGS: [pg_init, pg_init_tmpl, pgsql, postgres]
      postgres : Render default pg-init scripts	TAGS: [pg_init, pg_init_main, pgsql, postgres]
      postgres : Execute initialization scripts	TAGS: [pg_init, pg_init_exec, pgsql, postgres]
      postgres : Check primary instance ready	TAGS: [pg_init, pg_init_exec, 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, pgbouncer_ini, pgsql, postgres]
      postgres : Copy /etc/pgbouncer/pgb_hba.conf	TAGS: [pgbouncer, pgbouncer_config, pgbouncer_hba, 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]
      include_tasks	TAGS: [pg_user, pgsql, postgres]
      include_tasks	TAGS: [pg_db, pgsql, postgres]
      postgres : Reload pgbouncer to add db and users	TAGS: [pgbouncer_reload, pgsql, postgres]
      postgres : Copy pg service definition to consul	TAGS: [pg_register, pgsql, postgres, register]
      postgres : Reload postgres 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 : Install exporter yum repo	TAGS: [exporter_install, exporter_yum_install, monitor, pgsql]
      monitor : Install node_exporter and pg_exporter	TAGS: [exporter_install, exporter_yum_install, monitor, pgsql]
      monitor : Copy node_exporter binary	TAGS: [exporter_binary_install, exporter_install, monitor, pgsql]
      monitor : Copy pg_exporter binary	TAGS: [exporter_binary_install, exporter_install, monitor, pgsql]
      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_register, pgsql]
      monitor : Reload pg-exporter consul service	TAGS: [monitor, pg_exporter_register, 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, node_exporter_register, pgsql]
      monitor : Reload pgb-exporter consul service	TAGS: [monitor, node_exporter_register, 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 to consul	TAGS: [monitor, node_exporter_register, pgsql]
      monitor : Reload node-exporter consul service	TAGS: [monitor, node_exporter_register, pgsql]
      service : Make sure haproxy is installed	TAGS: [haproxy_install, service]
      service : Create haproxy directory	TAGS: [haproxy_install, service]
      service : Copy haproxy systemd service file	TAGS: [haproxy_install, haproxy_unit, service]
      service : Fetch postgres cluster memberships	TAGS: [haproxy_config, service]
      service : Templating /etc/haproxy/haproxy.cfg	TAGS: [haproxy_config, service]
      service : Launch haproxy load balancer service	TAGS: [haproxy_launch, haproxy_restart, service]
      service : Wait for haproxy load balancer online	TAGS: [haproxy_launch, service]
      service : Reload haproxy load balancer service	TAGS: [haproxy_reload, service]
      service : Copy haproxy exporter definition	TAGS: [haproxy_register, service]
      service : Copy haproxy service definition	TAGS: [haproxy_register, service]
      service : Reload haproxy consul service	TAGS: [haproxy_register, service]
      service : Make sure vip-manager is installed	TAGS: [service, vip_l2_install]
      service : Copy vip-manager systemd service file	TAGS: [service, vip_l2_install]
      service : create vip-manager systemd drop-in dir	TAGS: [service, vip_l2_install]
      service : create vip-manager systemd drop-in file	TAGS: [service, vip_l2_install]
      service : Templating /etc/default/vip-manager.yml	TAGS: [service, vip_l2_config, vip_manager_config]
      service : Launch vip-manager	TAGS: [service, vip_l2_reload]
      service : Fetch postgres cluster memberships	TAGS: [service, vip_l4_config]
      service : Render L4 VIP configs	TAGS: [service, vip_l4_config]
      include_tasks	TAGS: [service, vip_l4_reload]

3.3 - Sandbox Provision

How to perform one-pass bootstrap for sandbox?

The regular initialization process needs to complete the initialization of the meta-node/infrastructure first, and then the initialization of the other database nodes.

To speed up the initialization of the sandbox environment, Pigsty provides a sandbox-specific initialization script sandbox.yml, which can be used in an interleaved manner to complete the initialization of both the infrastructure meta nodes and normal nodes at once using an interleaved approach. This initialization method is fast, but is not recommended for production environments.

Script overview

Users can directly call sandbox.yml or complete the one-click initialization of the sandbox environment via the ``make init`' shortcut.

. /sandbox.yml

Cautions

The specific notes for sandbox initialization are the same as infrastructure deployment and PG cluster deployment.

script description

sandbox.yml will be [infra.yml](https://github.com/Vonng/pigsty/blob/master/ infra.yml) and pgsql.yml work interwoven together as follows.

#------------------------------------------------------------------------------
# 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]
      nameserver : Register consul dnsmasq service	TAGS: [meta, nameserver]
      nameserver : Reload consul	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]
      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 : Create postgres directory structure	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy prometheus bin scripts	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy prometheus rules scripts	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Copy altermanager config	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Render prometheus config	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Config /etc/prometheus opts	TAGS: [meta, prometheus, prometheus_config]
      prometheus : Fetch prometheus static monitoring targets	TAGS: [meta, prometheus, prometheus_config, prometheus_targets]
      prometheus : Render prometheus static targets	TAGS: [meta, prometheus, prometheus_config, prometheus_targets]
      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]
      prometheus : Reload prometheus service	TAGS: [meta, prometheus, prometheus_reload]
      prometheus : Copy prometheus service definition	TAGS: [meta, prometheus, prometheus_register]
      prometheus : Copy alertmanager service definition	TAGS: [meta, prometheus, prometheus_register]
      prometheus : Reload consul to register prometheus	TAGS: [meta, prometheus, prometheus_register]
      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_clean, dcs]
      consul : Stop any running consul instance	TAGS: [consul_clean, dcs]
      consul : Remove existing consul dir	TAGS: [consul_clean, dcs]
      consul : Recreate consul dir	TAGS: [consul_clean, 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, pgbouncer_ini, postgres]
      postgres : Copy /etc/pgbouncer/pgb_hba.conf	TAGS: [pgbouncer, pgbouncer_config, pgbouncer_hba, 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 pg service definition to consul	TAGS: [pg_register, postgres, register]
      postgres : Reload postgres 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 : Copy pg_exporter binary	TAGS: [monitor, pg_exporter, pg_exporter_binary]
      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_register]
      monitor : Reload pg-exporter consul service	TAGS: [monitor, pg_exporter_register]
      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, node_exporter_register]
      monitor : Reload pgb-exporter consul service	TAGS: [monitor, node_exporter_register]
      monitor : Copy node_exporter binary	TAGS: [monitor, node_exporter, node_exporter_binary]
      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 to consul	TAGS: [monitor, node_exporter_register]
      monitor : Reload node-exporter consul service	TAGS: [monitor, node_exporter_register]
      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]

3.4 - Remove Clusters

How to destroy postgres clusters and instances

剧本概览

数据库下线:可以移除现有的数据库集群或实例,回收节点:pgsql-remove.yml

日常管理

./pgsql-remove.yml -l pg-test  # 下线在 pg-test 集群
./pgsql-remove.yml -l pg-test  -l 10.10.10.13 # 下线在 pg-test 集群中的一个实例

剧本说明

#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File      :   pgsql-remove.yml
# Mtime     :   2020-05-12
# Mtime     :   2021-03-15
# Desc      :   remove postgres & consul services
# Path      :   pgsql-remove.yml
# Copyright (C) 2018-2021 Ruohang Feng
#==============================================================#

# this playbook aims at removing postgres & consul & related service
# from # existing instances. So that the node can be recycled for
# re-initialize or other database clusters.

#------------------------------------------------------------------------------
# Remove load balancer
#------------------------------------------------------------------------------
- name: Remove load balancer
  become: yes
  hosts: all
  serial: 1
  gather_facts: no
  tags: rm-lb
  tasks:
    - name: Stop load balancer
      ignore_errors: true
      systemd: name={{ item }} state=stopped enabled=no daemon_reload=yes
      with_items:
        - vip-manager
        - haproxy
        # - keepalived


#------------------------------------------------------------------------------
# Remove pg monitor
#------------------------------------------------------------------------------
- name: Remove monitor
  become: yes
  hosts: all
  gather_facts: no
  tags: rm-monitor
  tasks:

    - name: Stop monitor service
      ignore_errors: true
      systemd: name={{ item }} state=stopped enabled=no daemon_reload=yes
      with_items:
        - pg_exporter
        - pgbouncer_exporter

    - name: Deregister exporter service
      ignore_errors: true
      file: path=/etc/consul.d/svc-{{ item }}.json state=absent
      with_items:
        - haproxy
        - pg-exporter
        - pgbouncer-exporter

    - name: Reload consul
      systemd: name=consul state=reloaded


#------------------------------------------------------------------------------
# Remove watchdog owner
#------------------------------------------------------------------------------
- name: Remove monitor
  become: yes
  hosts: all
  gather_facts: no
  tags: rm-watchdog
  tasks:
    # - watchdog owner - #
    - name: Remove patroni watchdog ownership
      ignore_errors: true
      file: path=/dev/watchdog owner=root group=root


#------------------------------------------------------------------------------
# Remove postgres service
#------------------------------------------------------------------------------
- name: Remove Postgres service
  become: yes
  hosts: all
  serial: 1
  gather_facts: no
  tags: rm-pg
  tasks:
    - name: Remove postgres replica services
      when: pg_role != 'primary'
      ignore_errors: true
      systemd: name={{ item }} state=stopped enabled=no daemon_reload=yes
      with_items:
        - patroni
        - postgres
        - pgbouncer

    # if in resume mode, postgres will not be stopped
    - name: Force stop postgres non-primary process
      become_user: "{{ pg_dbsu }}"
      when: pg_role != 'primary'
      ignore_errors: true
      shell: |
        {{ pg_bin_dir }}/pg_ctl -D {{ pg_data }} stop -m immediate
        exit 0        

    - name: Remove postgres primary services
      when: pg_role == 'primary'
      ignore_errors: true
      systemd: name={{ item }} state=stopped enabled=no daemon_reload=yes
      with_items:
        - patroni
        - postgres
        - pgbouncer

    - name: Force stop postgres primary process
      become_user: "{{ pg_dbsu }}"
      when: pg_role == 'primary'
      ignore_errors: true
      shell: |
        {{ pg_bin_dir }}/pg_ctl -D {{ pg_data }} stop -m immediate
        exit 0        

    - name: Deregister postgres services
      ignore_errors: true
      file: path=/etc/consul.d/svc-{{ item }}.json state=absent
      with_items:
        - postgres
        - pgbouncer
        - patroni


#------------------------------------------------------------------------------
# Remove postgres service
#------------------------------------------------------------------------------
- name: Remove Infrastructure
  become: yes
  hosts: all
  serial: 1
  gather_facts: no
  tags: rm-infra
  tasks:

    - name: Consul leave cluster
      ignore_errors: true
      command: /usr/bin/consul leave

    - name: Stop consul and node_exporter
      ignore_errors: true
      systemd: name={{ item }} state=stopped enabled=no daemon_reload=yes
      with_items:
        - node_exporter
        - consul

#------------------------------------------------------------------------------
# Uninstall postgres and consul
#------------------------------------------------------------------------------
- name: Uninstall Packages
  become: yes
  hosts: all
  gather_facts: no
  tags: rm-pkgs
  tasks:
    - name: Uninstall postgres and consul
      when: yum_remove is defined and yum_remove|bool
      shell: |
        yum remove -y consul
        yum remove -y postgresql{{ pg_version }}*        

...

使用样例

./pgsql-remove.yml -l pg-test 

执行结果


任务详情

默认任务如下:

playbook: ./pgsql-remove.yml

  play #1 (all): Remove load balancer	TAGS: [rm-lb]
    tasks:
      Stop load balancer	TAGS: [rm-lb]

  play #2 (all): Remove monitor	TAGS: [rm-monitor]
    tasks:
      Stop monitor service	TAGS: [rm-monitor]
      Deregister exporter service	TAGS: [rm-monitor]
      Reload consul	TAGS: [rm-monitor]

  play #3 (all): Remove monitor	TAGS: [rm-watchdog]
    tasks:
      Remove patroni watchdog ownership	TAGS: [rm-watchdog]

  play #4 (all): Remove Postgres service	TAGS: [rm-pg]
    tasks:
      Remove postgres replica services	TAGS: [rm-pg]
      Force stop postgres non-primary process	TAGS: [rm-pg]
      Remove postgres primary services	TAGS: [rm-pg]
      Force stop postgres primary process	TAGS: [rm-pg]
      Deregister postgres services	TAGS: [rm-pg]

  play #5 (all): Remove Infrastructure	TAGS: [rm-infra]
    tasks:
      Consul leave cluster	TAGS: [rm-infra]
      Stop consul and node_exporter	TAGS: [rm-infra]

  play #6 (all): Uninstall Packages	TAGS: [rm-pkgs]
    tasks:
      Uninstall postgres and consul	TAGS: [rm-pkgs]

3.5 - Monitor Only

How to deploy pigsty monitoring system without provisioning solution?

剧本概览

部署监控系统:可以在现有集群中创建新的用户或修改现有用户pgsql-monitor.yml

日常管理

# 在 pg-test 集群中部署监控
./pgsql-monitor.yml -l pg-test

剧本说明

#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File      :   pgsql-monitor.yml
# Ctime     :   2021-02-23
# Mtime     :   2021-02-27
# Desc      :   deploy monitor components only
# Path      :   pgsql-monitor.yml
# Copyright (C) 2018-2021 Ruohang Feng
#==============================================================#

# this is pgsql monitor setup playbook for MONITOR ONLY mode

# MONITOR-ONLY (monly) mode is a special deployment mode for
# integration with exterior provisioning solution or existing
# postgres clusters.
# with limited functionalities

# For monly deployment, The infra part is still the same.
# You MUST use static services discovery for prometheus
# You CAN NOT use services_registry


#------------------------------------------------------------------------------
# Deploy monitor on selected targets
#------------------------------------------------------------------------------
- name: Monitor Only Deployment
  become: yes
  hosts: all
  gather_facts: no
  tags: monitor
  roles:
    - role: monitor                         # init monitor system
  vars:
    #------------------------------------------------------------------------------
    # RECOMMEND CHANGES
    #------------------------------------------------------------------------------
    # You'd better change those options in your main config file
    # prometheus_sd_method: static          # MUST use static sd for monitor only mode
    service_registry: none                  # MUST NOT register services
    exporter_install: binary                # none|yum|binary, none by default

    # exporter_install controls how node_exporter & pg_exporter are installed
    #    none   : I've already installed manually
    #    yum    : Use yum install, `exporter_repo_url` will be added if specified
    #    binary : Copy binary to /usr/bin. You must have binary in your `files` dir

    #------------------------------------------------------------------------------
    # MONITOR PROVISION
    #------------------------------------------------------------------------------
    # - install - #
    # exporter_install: none                        # none|yum|binary, none by default
    # exporter_repo_url: ''                         # if set, repo will be added to /etc/yum.repos.d/ before yum installation

    # - collect - #
    # exporter_metrics_path: /metrics               # default metric path for pg related exporter

    # - node exporter - #
    # node_exporter_enabled: true                   # setup node_exporter on instance
    # node_exporter_port: 9100                      # default port for node exporter
    # node_exporter_options: '--no-collector.softnet --collector.systemd --collector.ntp --collector.tcpstat --collector.processes'

    # - pg exporter - #
    # pg_exporter_config: pg_exporter-demo.yaml     # default config files for pg_exporter
    # pg_exporter_enabled: true                     # setup pg_exporter on instance
    # pg_exporter_port: 9630                        # default port for pg exporter
    # pg_exporter_url: ''                           # optional, if not set, generate from reference parameters

    # - pgbouncer exporter - #
    # pgbouncer exporter require pgbouncer to work, so it is disabled by default in monitor-only mode
    # pgbouncer_exporter_enabled: false             # setup pgbouncer_exporter on instance (if you don't have pgbouncer, disable it)
    # pgbouncer_exporter_port: 9631                 # default port for pgbouncer exporter
    # pgbouncer_exporter_url: ''                    # optional, if not set, generate from reference parameters

    # - postgres variables reference - #
    # pg_dbsu: postgres
    # pg_port: 5432                                 # postgres port (5432 by default)
    # pgbouncer_port: 6432                          # pgbouncer port (6432 by default)
    # pg_localhost: /var/run/postgresql             # localhost unix socket dir for connection
    # pg_default_database: postgres                 # default database will be used as primary monitor target
    # pg_monitor_username: dbuser_monitor           # system monitor username, for postgres and pgbouncer
    # pg_monitor_password: DBUser.Monitor           # system monitor user's password
    # service_registry: consul                      # none | consul | etcd | both



#------------------------------------------------------------------------------
# update static inventory in meta node and reload
#------------------------------------------------------------------------------
- name: Update prometheus static sd files
  become: yes
  hosts: meta
  tags: prometheus
  gather_facts: no
  vars:
    #------------------------------------------------------------------------------
    # RECOMMEND CHANGES
    #------------------------------------------------------------------------------
    prometheus_sd_method: static                  # service discovery method: static|consul|etcd

  tasks:
    - include_tasks: roles/prometheus/tasks/targets.yml
    - include_tasks: roles/prometheus/tasks/reload.yml

...

使用样例

./pgsql-monitor.yml -l pg-test

执行结果

$ ./pgsql-monitor.yml -l pg-test -e pg_user=test
[WARNING]: Invalid characters were found in group names but not replaced, use -vvvv to see details

PLAY [Create user in cluster] *****************************************************************************************************************************************************

TASK [Check parameter pg_user] ****************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.12] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.13] => {
    "changed": false,
    "msg": "All assertions passed"
}

TASK [Fetch user definition] ******************************************************************************************************************************************************
ok: [10.10.10.11]
ok: [10.10.10.12]
ok: [10.10.10.13]

TASK [debug] **********************************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "msg": {
        "comment": "default test user for production usage",
        "name": "test",
        "password": "test",
        "pgbouncer": true,
        "roles": [
            "dbrole_readwrite"
        ]
    }
}
ok: [10.10.10.12] => {
    "msg": {
        "comment": "default test user for production usage",
        "name": "test",
        "password": "test",
        "pgbouncer": true,
        "roles": [
            "dbrole_readwrite"
        ]
    }
}
ok: [10.10.10.13] => {
    "msg": {
        "comment": "default test user for production usage",
        "name": "test",
        "password": "test",
        "pgbouncer": true,
        "roles": [
            "dbrole_readwrite"
        ]
    }
}

TASK [Check user definition] ******************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.12] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.13] => {
    "changed": false,
    "msg": "All assertions passed"
}

TASK [include_tasks] **************************************************************************************************************************************************************
included: /Volumes/Data/pigsty/roles/postgres/tasks/monitor.yml for 10.10.10.11, 10.10.10.12, 10.10.10.13

TASK [Render user test creation sql] **********************************************************************************************************************************************
skipping: [10.10.10.12]
skipping: [10.10.10.13]
changed: [10.10.10.11]

TASK [Execute user test creation sql on primary] **********************************************************************************************************************************
skipping: [10.10.10.12]
skipping: [10.10.10.13]
changed: [10.10.10.11]

TASK [Add user to pgbouncer] ******************************************************************************************************************************************************
changed: [10.10.10.11]
changed: [10.10.10.13]
changed: [10.10.10.12]

TASK [Reload pgbouncer to add user] ***********************************************************************************************************************************************
changed: [10.10.10.11]
changed: [10.10.10.12]
changed: [10.10.10.13]

PLAY RECAP ************************************************************************************************************************************************************************
10.10.10.11                : ok=9    changed=4    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
10.10.10.12                : ok=7    changed=2    unreachable=0    failed=0    skipped=2    rescued=0    ignored=0
10.10.10.13                : ok=7    changed=2    unreachable=0    failed=0    skipped=2    rescued=0    ignored=0

任务详情

默认任务如下:

playbook: ./pgsql-monitor.yml

  play #1 (all): Monitor Only Deployment	TAGS: [monitor]
    tasks:
      monitor : Install exporter yum repo	TAGS: [exporter_install, exporter_yum_install, monitor]
      monitor : Install node_exporter and pg_exporter	TAGS: [exporter_install, exporter_yum_install, monitor]
      monitor : Copy node_exporter binary	TAGS: [exporter_binary_install, exporter_install, monitor]
      monitor : Copy pg_exporter binary	TAGS: [exporter_binary_install, exporter_install, monitor]
      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_register]
      monitor : Reload pg-exporter consul service	TAGS: [monitor, pg_exporter_register]
      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, node_exporter_register]
      monitor : Reload pgb-exporter consul service	TAGS: [monitor, node_exporter_register]
      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 to consul	TAGS: [monitor, node_exporter_register]
      monitor : Reload node-exporter consul service	TAGS: [monitor, node_exporter_register]

  play #2 (meta): Update prometheus static sd files	TAGS: [prometheus]
    tasks:
      include_tasks	TAGS: [prometheus]
      include_tasks	TAGS: [prometheus]

3.6 - Create User

How to create new users on existing clusters?

剧本概览

创建业务用户:可以在现有集群中创建新的用户或修改现有用户pgsql-createuser.yml

日常管理

# 在 pg-test 集群创建名为 test 的用户
./pgsql-createuser.yml -l pg-test -e pg_user=test

请注意,pg_user 指定的用户,必须已经存在于集群pg_users的定义中,否则会报错。这意味着用户必须先定义用户,再创建用户。

剧本说明

#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File      :   pgsql-createuser.yml
# Ctime     :   2021-02-27
# Mtime     :   2021-02-27
# Desc      :   create user on running cluster
# Path      :   pgsql-createuser.yml
# Deps      :   templates/pg-user.sql
# Copyright (C) 2018-2021 Ruohang Feng
#==============================================================#


#=============================================================================#
# How to create user ?
#   1. define user in your configuration file! <cluster>.vars.pg_usesrs
#   2. execute this playbook with pg_user set to your new user.name
#   3. run playbook on target cluster
# It essentially does:
#   1. create sql file in /pg/tmp/pg-user-{{ user.name }}.sql
#   2. create user on primary instance with that sql
#   3. if {{ user.pgbouncer }}, add to all cluster members and reload
#=============================================================================#


- name: Create user in cluster
  become: yes
  hosts: all
  gather_facts: no
  vars:

    ##################################################################################
    # IMPORTANT: Change this or use cli-arg to specify target user in inventory  #
    ##################################################################################
    pg_user: test

  tasks:
    #------------------------------------------------------------------------------
    # pre-flight check: validate pg_user and user definition
    # ------------------------------------------------------------------------------
    - name: Preflight
      block:
        - name: Check parameter pg_user
          connection: local
          assert:
            that:
              - pg_user is defined
              - pg_user != ''
              - pg_user != 'postgres'
            fail_msg: variable 'pg_user' should be specified to create target user

        - name: Fetch user definition
          connection: local
          set_fact:
            pg_user_definition={{ pg_users | json_query(pg_user_definition_query) }}
          vars:
            pg_user_definition_query: "[?name=='{{ pg_user }}'] | [0]"

        # print user definition
        - debug:
            msg: "{{ pg_user_definition }}"

        - name: Check user definition
          assert:
            that:
              - pg_user_definition is defined
              - pg_user_definition != None
              - pg_user_definition != ''
              - pg_user_definition != {}
            fail_msg: user definition for {{ pg_user }} should exists in pg_users

    #------------------------------------------------------------------------------
    # Create user on cluster primary and add pgbouncer entry to cluster members
    #------------------------------------------------------------------------------
    # create user according to user definition
    - include_tasks: roles/postgres/tasks/createuser.yml
      vars:
        user: "{{ pg_user_definition }}"


    #------------------------------------------------------------------------------
    # Pgbouncer Reload (entire cluster)
    #------------------------------------------------------------------------------
    - name: Reload pgbouncer to add user
      when: pg_user_definition.pgbouncer is defined and pg_user_definition.pgbouncer|bool
      tags: pgbouncer_reload
      systemd: name=pgbouncer state=reloaded enabled=yes daemon_reload=yes


...

使用样例

./pgsql-createuser.yml -l pg-test -e pg_user=test

执行结果

$ ./pgsql-createuser.yml -l pg-test -e pg_user=test
[WARNING]: Invalid characters were found in group names but not replaced, use -vvvv to see details

PLAY [Create user in cluster] *****************************************************************************************************************************************************

TASK [Check parameter pg_user] ****************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.12] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.13] => {
    "changed": false,
    "msg": "All assertions passed"
}

TASK [Fetch user definition] ******************************************************************************************************************************************************
ok: [10.10.10.11]
ok: [10.10.10.12]
ok: [10.10.10.13]

TASK [debug] **********************************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "msg": {
        "comment": "default test user for production usage",
        "name": "test",
        "password": "test",
        "pgbouncer": true,
        "roles": [
            "dbrole_readwrite"
        ]
    }
}
ok: [10.10.10.12] => {
    "msg": {
        "comment": "default test user for production usage",
        "name": "test",
        "password": "test",
        "pgbouncer": true,
        "roles": [
            "dbrole_readwrite"
        ]
    }
}
ok: [10.10.10.13] => {
    "msg": {
        "comment": "default test user for production usage",
        "name": "test",
        "password": "test",
        "pgbouncer": true,
        "roles": [
            "dbrole_readwrite"
        ]
    }
}

TASK [Check user definition] ******************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.12] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.13] => {
    "changed": false,
    "msg": "All assertions passed"
}

TASK [include_tasks] **************************************************************************************************************************************************************
included: /Volumes/Data/pigsty/roles/postgres/tasks/createuser.yml for 10.10.10.11, 10.10.10.12, 10.10.10.13

TASK [Render user test creation sql] **********************************************************************************************************************************************
skipping: [10.10.10.12]
skipping: [10.10.10.13]
changed: [10.10.10.11]

TASK [Execute user test creation sql on primary] **********************************************************************************************************************************
skipping: [10.10.10.12]
skipping: [10.10.10.13]
changed: [10.10.10.11]

TASK [Add user to pgbouncer] ******************************************************************************************************************************************************
changed: [10.10.10.11]
changed: [10.10.10.13]
changed: [10.10.10.12]

TASK [Reload pgbouncer to add user] ***********************************************************************************************************************************************
changed: [10.10.10.11]
changed: [10.10.10.12]
changed: [10.10.10.13]

PLAY RECAP ************************************************************************************************************************************************************************
10.10.10.11                : ok=9    changed=4    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0
10.10.10.12                : ok=7    changed=2    unreachable=0    failed=0    skipped=2    rescued=0    ignored=0
10.10.10.13                : ok=7    changed=2    unreachable=0    failed=0    skipped=2    rescued=0    ignored=0

任务详情

默认任务如下:

playbook: ./pgsql-createuser.yml

  play #1 (all): Create user in cluster	TAGS: []
    tasks:
      Check parameter pg_user	TAGS: []
      Fetch user definition	TAGS: []
      debug	TAGS: []
      Check user definition	TAGS: []
      include_tasks	TAGS: []
      Reload pgbouncer to add user	TAGS: [pgbouncer_reload]

3.7 - Create Database

How to create new database on running clusters?

剧本概览

创建业务数据库:可以在现有集群中创建新的数据库或修改现有数据库pgsql-createdb.yml

日常管理

# 在 pg-test 集群创建名为 test 的数据库
./pgsql-createdb.yml -l pg-test -e pg_database=test

剧本说明

#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File      :   pgsql-createdb.yml
# Ctime     :   2021-02-27
# Mtime     :   2021-02-27
# Desc      :   create database on running cluster
# Deps      :   templates/pg-db.sql
# Path      :   pgsql-createdb.yml
# Copyright (C) 2018-2021 Ruohang Feng
#==============================================================#


#=============================================================================#
# How to create database ?
#   1. define database in your configuration file! <cluster>.vars.pg_databases
#   2. execute this playbook with pg_database set to your new database.name
#   3. run playbook on target cluster
# It essentially does:
#   1. create sql file in /pg/tmp/pg-db-{{ database.name }}.sql
#   2. create database on primary instance with that sql
#   3. if {{ database.pgbouncer }}, add to all cluster members and reload
#=============================================================================#

- name: Create Database In Cluster
  become: yes
  hosts: all
  gather_facts: no
  vars:

    ##################################################################################
    # IMPORTANT: Change this or use cli-arg to specify target database in inventory  #
    ##################################################################################
    pg_database: test

  tasks:
    #------------------------------------------------------------------------------
    # pre-flight check: validate pg_database and database definition
    # ------------------------------------------------------------------------------
    - name: Preflight
      block:
        - name: Check parameter pg_database
          connection: local
          assert:
            that:
              - pg_database is defined
              - pg_database != ''
              - pg_database != 'postgres'
            fail_msg: variable 'pg_database' should be specified to create target database

        - name: Fetch database definition
          connection: local
          set_fact:
            pg_database_definition={{ pg_databases | json_query(pg_database_definition_query) }}
          vars:
            pg_database_definition_query: "[?name=='{{ pg_database }}'] | [0]"

        # print database definition
        - debug:
            msg: "{{ pg_database_definition }}"

        - name: Check database definition
          assert:
            that:
              - pg_database_definition is defined
              - pg_database_definition != None
              - pg_database_definition != ''
              - pg_database_definition != {}
            fail_msg: database definition for {{ pg_database }} should exists in pg_databases

    #------------------------------------------------------------------------------
    # Create database on cluster primary and add pgbouncer entry to cluster members
    #------------------------------------------------------------------------------
    # create database according to database definition
    - include_tasks: roles/postgres/tasks/createdb.yml
      vars:
        database: "{{ pg_database_definition }}"


    #------------------------------------------------------------------------------
    # Pgbouncer Reload (entire cluster)
    #------------------------------------------------------------------------------
    - name: Reload pgbouncer to add database
      when: pg_database_definition.pgbouncer is not defined or pg_database_definition.pgbouncer|bool
      tags: pgbouncer_reload
      systemd: name=pgbouncer state=reloaded enabled=yes daemon_reload=yes


...

使用样例

./pgsql-createdb.yml -l pg-test -e pg_database=test

执行结果

$ ./pgsql-createdb.yml -l pg-test -e pg_database=test
[WARNING]: Invalid characters were found in group names but not replaced, use -vvvv to see details

PLAY [Create Database In Cluster] *************************************************************************************************************************************************

TASK [Check parameter pg_database] ************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.12] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.13] => {
    "changed": false,
    "msg": "All assertions passed"
}

TASK [Fetch database definition] **************************************************************************************************************************************************
ok: [10.10.10.11]
ok: [10.10.10.12]
ok: [10.10.10.13]

TASK [debug] **********************************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "msg": {
        "name": "test"
    }
}
ok: [10.10.10.12] => {
    "msg": {
        "name": "test"
    }
}
ok: [10.10.10.13] => {
    "msg": {
        "name": "test"
    }
}

TASK [Check database definition] **************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.12] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.13] => {
    "changed": false,
    "msg": "All assertions passed"
}

TASK [include_tasks] **************************************************************************************************************************************************************
included: /Volumes/Data/pigsty/roles/postgres/tasks/createdb.yml for 10.10.10.11, 10.10.10.12, 10.10.10.13

TASK [debug] **********************************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "msg": {
        "name": "test"
    }
}
skipping: [10.10.10.12]
skipping: [10.10.10.13]

TASK [Render database test creation sql] ******************************************************************************************************************************************
skipping: [10.10.10.12]
skipping: [10.10.10.13]
changed: [10.10.10.11]

TASK [Render database test baseline sql] ******************************************************************************************************************************************
skipping: [10.10.10.11]
skipping: [10.10.10.12]
skipping: [10.10.10.13]

TASK [Execute database test creation command] *************************************************************************************************************************************
skipping: [10.10.10.12]
skipping: [10.10.10.13]
changed: [10.10.10.11]

TASK [Execute database test creation sql] *****************************************************************************************************************************************
skipping: [10.10.10.12]
skipping: [10.10.10.13]
changed: [10.10.10.11]

TASK [Execute database test creation sql] *****************************************************************************************************************************************
skipping: [10.10.10.11]
skipping: [10.10.10.12]
skipping: [10.10.10.13]

TASK [Add pgbouncer busniess database] ********************************************************************************************************************************************
changed: [10.10.10.11]
changed: [10.10.10.13]
changed: [10.10.10.12]

TASK [Reload pgbouncer to add database] *******************************************************************************************************************************************
changed: [10.10.10.11]
changed: [10.10.10.13]
changed: [10.10.10.12]

PLAY RECAP ************************************************************************************************************************************************************************
10.10.10.11                : ok=11   changed=5    unreachable=0    failed=0    skipped=2    rescued=0    ignored=0
10.10.10.12                : ok=7    changed=2    unreachable=0    failed=0    skipped=6    rescued=0    ignored=0
10.10.10.13                : ok=7    changed=2    unreachable=0    failed=0    skipped=6    rescued=0    ignored=0

任务详情

默认任务如下:

playbook: ./pgsql-createdb.yml

  play #1 (all): Create Database In Cluster	TAGS: []
    tasks:
      Check parameter pg_database	TAGS: []
      Fetch database definition	TAGS: []
      debug	TAGS: []
      Check database definition	TAGS: []
      include_tasks	TAGS: []
      Reload pgbouncer to add database	TAGS: [pgbouncer_reload]

3.8 - Service Provision

Create or update service on cluster

剧本概览

创建业务数据库:可以在现有集群中创建新的数据库或修改现有数据库pgsql-service.yml

日常管理

# 在 pg-test 集群创建所有服务
./pgsql-service.yml -l pg-test 

剧本说明

#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File      :   pgsql-service.yml
# Ctime     :   2021-03-12
# Mtime     :   2021-03-12
# Desc      :   reload service for postgres clusters
# Path      :   pgsql-service.yml
# Copyright (C) 2018-2021 Ruohang Feng
#==============================================================#

# PLEASE USE COMPLETE INVENTORY (at least contains a complete cluster definition!)

#------------------------------------------------------------------------------
# haproxy reload
#   will not reload if haproxy_reload=false
#------------------------------------------------------------------------------
- name: Reload haproxy
  become: yes
  hosts: all
  gather_facts: no
  tags: haproxy
  tasks:
    - include_tasks: roles/service/tasks/haproxy_config.yml
      when: haproxy_enabled
    - include_tasks: roles/service/tasks/haproxy_reload.yml
      when: haproxy_enabled and haproxy_reload|bool


#------------------------------------------------------------------------------
# l2-vip reload
#   will only config without reload if vip_reload=false
#------------------------------------------------------------------------------
- name: Reload l2 VIP
  become: yes
  hosts: all
  gather_facts: no
  tags: vip_l2
  tasks:
    - include_tasks: roles/service/tasks/vip_l2_config.yml
      when: vip_mode == 'l2'
    - include_tasks: roles/service/tasks/vip_l2_reload.yml
      when: vip_mode == 'l2' and vip_reload|bool


#------------------------------------------------------------------------------
# l4-vip reload
#   will not reload if vip_reload=false
#------------------------------------------------------------------------------
- name: Reload l4 VIP
  become: yes
  hosts: all
  gather_facts: no
  tags: vip_l4
  tasks:
    - include_tasks: roles/service/tasks/vip_l4_config.yml
    - include_tasks: roles/service/tasks/vip_l4_reload.yml

...

使用样例

./pgsql-service.yml -l pg-test 

执行结果

$ ./pgsql-service.yml -l pg-test
[WARNING]: Invalid characters were found in group names but not replaced, use -vvvv to see details

PLAY [Reload haproxy] *************************************************************************************************************************************************************

TASK [include_tasks] **************************************************************************************************************************************************************
included: /Volumes/Data/pigsty/roles/service/tasks/haproxy_config.yml for 10.10.10.11, 10.10.10.12, 10.10.10.13

TASK [Fetch postgres cluster memberships] *****************************************************************************************************************************************
ok: [10.10.10.11]
ok: [10.10.10.12]
ok: [10.10.10.13]

TASK [Templating /etc/haproxy/haproxy.cfg] ****************************************************************************************************************************************
ok: [10.10.10.11]
ok: [10.10.10.12]
ok: [10.10.10.13]

TASK [include_tasks] **************************************************************************************************************************************************************
included: /Volumes/Data/pigsty/roles/service/tasks/haproxy_reload.yml for 10.10.10.11, 10.10.10.12, 10.10.10.13

TASK [Reload haproxy load balancer service] ***************************************************************************************************************************************
changed: [10.10.10.13]
changed: [10.10.10.12]
changed: [10.10.10.11]

PLAY [Reload l2 VIP] **************************************************************************************************************************************************************

TASK [include_tasks] **************************************************************************************************************************************************************
included: /Volumes/Data/pigsty/roles/service/tasks/vip_l2_config.yml for 10.10.10.11, 10.10.10.12, 10.10.10.13

TASK [Templating /etc/default/vip-manager.yml] ************************************************************************************************************************************
ok: [10.10.10.11]
ok: [10.10.10.13]
ok: [10.10.10.12]

TASK [include_tasks] **************************************************************************************************************************************************************
included: /Volumes/Data/pigsty/roles/service/tasks/vip_l2_reload.yml for 10.10.10.11, 10.10.10.12, 10.10.10.13

TASK [Launch vip-manager] *********************************************************************************************************************************************************
changed: [10.10.10.11]
changed: [10.10.10.13]
changed: [10.10.10.12]

PLAY [Reload l4 VIP] **************************************************************************************************************************************************************

TASK [include_tasks] **************************************************************************************************************************************************************
skipping: [10.10.10.11]
skipping: [10.10.10.12]
skipping: [10.10.10.13]

TASK [include_tasks] **************************************************************************************************************************************************************
skipping: [10.10.10.11]
skipping: [10.10.10.12]
skipping: [10.10.10.13]

PLAY RECAP ************************************************************************************************************************************************************************
10.10.10.11                : ok=9    changed=2    unreachable=0    failed=0    skipped=2    rescued=0    ignored=0
10.10.10.12                : ok=9    changed=2    unreachable=0    failed=0    skipped=2    rescued=0    ignored=0
10.10.10.13                : ok=9    changed=2    unreachable=0    failed=0    skipped=2    rescued=0    ignored=0

任务详情

默认任务如下:

playbook: ./pgsql-service.yml

  play #1 (all): Reload haproxy	TAGS: [haproxy]
    tasks:
      include_tasks	TAGS: [haproxy]
      include_tasks	TAGS: [haproxy]

  play #2 (all): Reload l2 VIP	TAGS: [vip_l2]
    tasks:
      include_tasks	TAGS: [vip_l2]
      include_tasks	TAGS: [vip_l2]

  play #3 (all): Reload l4 VIP	TAGS: [vip_l4]
    tasks:
      include_tasks	TAGS: [vip_l4]
      include_tasks	TAGS: [vip_l4]

4 - Example

Real examples bout deploying pigsty

这里给出几个典型的部署样例,仅供参考。

4.1 - Vagrant Sandbox

Deploy pigsty on your local sandbox

概述

这个配置文件,是Pigsty自带的沙箱环境所使用的配置文件。

Github原地址为:https://github.com/Vonng/pigsty/blob/master/pigsty.yml

该配置文件可作为一个标准的学习样例,例如使用相同规格的虚拟机环境部署时,通常只需要在这份配置文件的基础上进行极少量修改就可以直接使用:例如,将10.10.10.10替换为您的元节点IP,将10.10.10.*替换为数据库节点的IP,修改或移除 ansible_host 系列连接参数以提供正确的连接信息。就可以将Pigsty部署到一组虚拟机上了。

配置文件

---
######################################################################
# File      :   pigsty.yml
# Path      :   pigsty.yml
# Desc      :   Pigsty Configuration file
# Note      :   follow ansible inventory file format
# Ctime     :   2020-05-22
# Mtime     :   2021-03-16
# Copyright (C) 2018-2021 Ruohang Feng
######################################################################


######################################################################
#               Development Environment Inventory                    #
######################################################################
all: # top-level namespace, match all hosts


  #==================================================================#
  #                           Clusters                               #
  #==================================================================#
  # postgres database clusters are defined as kv pair in `all.children`
  # where the key is cluster name and the value is the object consist
  # of cluster members (hosts) and ad-hoc variables (vars)
  # meta node are defined in special group "meta" with `meta_node=true`

  children:

    #-----------------------------
    # meta controller
    #-----------------------------
    meta:       # special group 'meta' defines the main controller machine
      vars:
        meta_node: true                     # mark node as meta controller
        ansible_group_priority: 99          # meta group is top priority

      # nodes in meta group
      hosts: {10.10.10.10: {ansible_host: meta}}

    #-----------------------------
    # cluster: pg-meta
    #-----------------------------
    pg-meta:
      # - cluster members - #
      hosts:
        10.10.10.10: {pg_seq: 1, pg_role: primary, ansible_host: meta}

      # - cluster configs - #
      vars:
        pg_cluster: pg-meta                 # 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
        patroni_mode: pause                 # enter maintenance mode, {default|pause|remove}
        patroni_watchdog_mode: off          # disable watchdog (require|automatic|off)
        pg_lc_ctype: en_US.UTF8             # enabled pg_trgm i18n char support

        pg_users:
          # complete example of user/role definition for production user
          - name: dbuser_meta               # example production user have read-write access
            password: DBUser.Meta           # example user's password, can be encrypted
            login: true                     # can login, true by default (should be false for role)
            superuser: false                # is superuser? false by default
            createdb: false                 # can create database? false by default
            createrole: false               # can create role? false by default
            inherit: true                   # can this role use inherited privileges?
            replication: false              # can this role do replication? false by default
            bypassrls: false                # can this role bypass row level security? false by default
            connlimit: -1                   # connection limit, -1 disable limit
            expire_at: '2030-12-31'         # 'timestamp' when this role is expired
            expire_in: 365                  # now + n days when this role is expired (OVERWRITE expire_at)
            roles: [dbrole_readwrite]       # dborole_admin|dbrole_readwrite|dbrole_readonly
            pgbouncer: true                 # add this user to pgbouncer? false by default (true for production user)
            parameters:                     # user's default search path
              search_path: public
            comment: test user

          # simple example for personal user definition
          - name: dbuser_vonng2              # personal user example which only have limited access to offline instance
            password: DBUser.Vonng          # or instance with explict mark `pg_offline_query = true`
            roles: [dbrole_offline]         # personal/stats/ETL user should be grant with dbrole_offline
            expire_in: 365                  # expire in 365 days since creation
            pgbouncer: false                # personal user should NOT be allowed to login with pgbouncer
            comment: example personal user for interactive queries

        pg_databases:
          - name: meta                      # name is the only required field for a database
            # owner: postgres                 # optional, database owner
            # template: template1             # optional, template1 by default
            # encoding: UTF8                # optional, UTF8 by default , must same as template database, leave blank to set to db default
            # locale: C                     # optional, C by default , must same as template database, leave blank to set to db default
            # lc_collate: C                 # optional, C by default , must same as template database, leave blank to set to db default
            # lc_ctype: C                   # optional, C by default , must same as template database, leave blank to set to db default
            allowconn: true                 # optional, true by default, false disable connect at all
            revokeconn: false               # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
            # tablespace: pg_default          # optional, 'pg_default' is the default tablespace
            connlimit: -1                   # optional, connection limit, -1 or none disable limit (default)
            extensions:                     # optional, extension name and where to create
              - {name: postgis, schema: public}
            parameters:                     # optional, extra parameters with ALTER DATABASE
              enable_partitionwise_join: true
            pgbouncer: true                 # optional, add this database to pgbouncer list? true by default
            comment: pigsty meta database   # optional, comment string for database

        pg_default_database: meta           # default database will be used as primary monitor target

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


    #-----------------------------
    # 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: offline, 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

        # business users, adjust on your own needs
        pg_users:
          - name: test                      # example production user have read-write access
            password: test                  # example user's password
            roles: [dbrole_readwrite]       # dborole_admin|dbrole_readwrite|dbrole_readonly|dbrole_offline
            pgbouncer: true                 # production user that access via pgbouncer
            comment: default test user for production usage

        pg_databases:                       # create a business database 'test'
          - name: test                      # use the simplest form

        pg_default_database: test           # default database will be used as primary monitor target

        # proxy settings
        vip_mode: l2                        # 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


  #==================================================================#
  #                           Globals                                #
  #==================================================================#
  vars:

    #------------------------------------------------------------------------------
    # CONNECTION PARAMETERS
    #------------------------------------------------------------------------------
    # this section defines connection parameters

    # ansible_user: vagrant                       # admin user with ssh access and sudo privilege

    proxy_env: # global proxy env when downloading packages
      no_proxy: "localhost,127.0.0.1,10.0.0.0/8,192.168.0.0/16,*.pigsty,*.aliyun.com,mirrors.aliyuncs.com,mirrors.tuna.tsinghua.edu.cn,mirrors.zju.edu.cn"
      # http_proxy: ''
      # https_proxy: ''
      # all_proxy: ''


    #------------------------------------------------------------------------------
    # REPO PROVISION
    #------------------------------------------------------------------------------
    # this section defines how to build a local repo

    # - repo basic - #
    repo_enabled: true                            # build local yum repo on meta nodes?
    repo_name: pigsty                             # local repo name
    repo_address: yum.pigsty                      # repo external address (ip:port or url)
    repo_port: 80                                 # listen address, must same as repo_address
    repo_home: /www                               # default repo dir location
    repo_rebuild: false                           # force re-download packages
    repo_remove: true                             # remove existing repos

    # - where to download - #
    repo_upstreams:
      - 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

      # consider using ZJU PostgreSQL mirror in mainland china
      - 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
        baseurl: http://mirrors.zju.edu.cn/postgresql/repos/yum/common/redhat/rhel-$releasever-$basearch

      - name: pgdg13
        description: PostgreSQL 13 for RHEL/CentOS $releasever - $basearch
        gpgcheck: no
        # baseurl: https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-$releasever-$basearch
        baseurl: http://mirrors.zju.edu.cn/postgresql/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/

    # - 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 audit                                       # 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 timescaledb_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:
      - https://github.com/Vonng/pg_exporter/releases/download/v0.3.2/pg_exporter-0.3.2-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


    #------------------------------------------------------------------------------
    # NODE PROVISION
    #------------------------------------------------------------------------------
    # this section defines how to provision nodes
    # nodename:                                   # if defined, node's hostname will be overwritten

    # - 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
    node_local_repo_url:                          # local repo url (if method=local, make sure firewall is configured or disabled)
      - 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


    #------------------------------------------------------------------------------
    # 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" }
      - { name: haproxy,       host: h.pigsty, url: "127.0.0.1:9091" }

    # - 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
      - 10.10.10.10 h.pigsty haproxy.pigsty

    # - prometheus - #
    prometheus_data_dir: /export/prometheus/data  # prometheus data dir
    prometheus_options: '--storage.tsdb.retention=30d'
    prometheus_reload: false                      # reload prometheus instead of recreate it
    prometheus_sd_method: consul                  # service discovery method: static|consul|etcd
    prometheus_scrape_interval: 2s                # global scrape & evaluation interval
    prometheus_scrape_timeout: 1s                 # scrape timeout
    prometheus_sd_interval: 2s                    # service discovery refresh interval

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



    #------------------------------------------------------------------------------
    # DCS PROVISION
    #------------------------------------------------------------------------------
    service_registry: consul                      # where to register services: none | consul | etcd | both
    dcs_type: consul                              # consul | etcd | both
    dcs_name: pigsty                              # consul dc name | etcd initial cluster token
    dcs_servers:                                  # dcs server dict in name:ip format
      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: clean                      # abort|skip|clean if dcs server already exists
    dcs_disable_purge: false                      # set to true to disable purge functionality for good (force dcs_exists_action = abort)
    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)


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



    #------------------------------------------------------------------------------
    # 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
    pg_disable_purge: false                       # set to true to disable pg purge functionality for good (force pg_exists_action = abort)

    # - 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)
    pg_localhost: /var/run/postgresql             # localhost unix socket dir for connection

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

    # - localization - #
    pg_encoding: UTF8                             # default to UTF8
    pg_locale: C                                  # default to C
    pg_lc_collate: C                              # default to C
    pg_lc_ctype: en_US.UTF8                       # default to en_US.UTF8

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


    #------------------------------------------------------------------------------
    # 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 - #
    # chekc http://pigsty.cc/zh/docs/concepts/provision/acl/ for more detail
    pg_default_roles:

      # common production readonly user
      - name: dbrole_readonly                 # production read-only roles
        login: false
        comment: role for global readonly access

      # common production read-write user
      - name: dbrole_readwrite                # production read-write roles
        login: false
        roles: [dbrole_readonly]             # read-write includes read-only access
        comment: role for global read-write access

      # offline have same privileges as readonly, but with limited hba access on offline instance only
      # for the purpose of running slow queries, interactive queries and perform ETL tasks
      - name: dbrole_offline
        login: false
        comment: role for restricted read-only access (offline instance)

      # admin have the privileges to issue DDL changes
      - name: dbrole_admin
        login: false
        bypassrls: true
        comment: role for object creation
        roles: [dbrole_readwrite,pg_monitor,pg_signal_backend]

      # dbsu, name is designated by `pg_dbsu`. It's not recommend to set password for dbsu
      - name: postgres
        superuser: true
        comment: system superuser

      # default replication user, name is designated by `pg_replication_username`, and password is set by `pg_replication_password`
      - name: replicator
        replication: true                          # for replication user
        bypassrls: true                            # logical replication require bypassrls
        roles: [pg_monitor, dbrole_readonly]       # logical replication require select privileges
        comment: system replicator

      # default replication user, name is designated by `pg_monitor_username`, and password is set by `pg_monitor_password`
      - name: dbuser_monitor
        connlimit: 16
        comment: system monitor user
        roles: [pg_monitor, dbrole_readonly]

      # default admin user, name is designated by `pg_admin_username`, and password is set by `pg_admin_password`
      - name: dbuser_admin
        bypassrls: true
        superuser: true
        comment: system admin user
        roles: [dbrole_admin]

      # default stats user, for ETL and slow queries
      - name: dbuser_stats
        password: DBUser.Stats
        comment: business offline user for offline queries and ETL
        roles: [dbrole_offline]


    # - privileges - #
    # object created by dbsu and admin will have their privileges properly set
    pg_default_privileges:
      - GRANT USAGE                         ON SCHEMAS   TO dbrole_readonly
      - GRANT SELECT                        ON TABLES    TO dbrole_readonly
      - GRANT SELECT                        ON SEQUENCES TO dbrole_readonly
      - GRANT EXECUTE                       ON FUNCTIONS TO dbrole_readonly
      - GRANT USAGE                         ON SCHEMAS   TO dbrole_offline
      - GRANT SELECT                        ON TABLES    TO dbrole_offline
      - GRANT SELECT                        ON SEQUENCES TO dbrole_offline
      - GRANT EXECUTE                       ON FUNCTIONS TO dbrole_offline
      - GRANT INSERT, 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

    # - schemas - #
    pg_default_schemas: [monitor]                 # default schemas to be created

    # - extension - #
    pg_default_extensions:                        # default extensions to be created
      - { 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

    # - hba - #
    pg_offline_query: false                       # set to true to enable offline query on instance
    pg_reload: true                               # reload postgres after hba changes
    pg_hba_rules:                                 # postgres host-based authentication 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 (local production user via pgbouncer)
        role: common
        rules:
          - local   all     +dbrole_readonly                                md5
          - host    all     +dbrole_readonly           127.0.0.1/32         md5

      - title: allow offline query (ETL,SAGA,Interactive) on offline instance
        role: offline
        rules:
          - host    all     +dbrole_offline               10.0.0.0/8        md5
          - host    all     +dbrole_offline               172.16.0.0/12     md5
          - host    all     +dbrole_offline               192.168.0.0/16    md5

    pg_hba_rules_extra: []                        # extra hba rules (for cluster/instance overwrite)

    pgbouncer_hba_rules:                          # pgbouncer host-based authentication 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: []                 # extra pgbouncer hba rules (for cluster/instance overwrite)
    # pg_users: []                                # business users
    # pg_databases: []                            # business databases

    #------------------------------------------------------------------------------
    # MONITOR PROVISION
    #------------------------------------------------------------------------------
    # - install - #
    exporter_install: none                        # none|yum|binary, none by default
    exporter_repo_url: ''                         # if set, repo will be added to /etc/yum.repos.d/ before yum installation

    # - collect - #
    exporter_metrics_path: /metrics               # default metric path for pg related exporter

    # - node exporter - #
    node_exporter_enabled: true                   # setup node_exporter on instance
    node_exporter_port: 9100                      # default port for node exporter
    node_exporter_options: '--no-collector.softnet --collector.systemd --collector.ntp --collector.tcpstat --collector.processes'

    # - pg exporter - #
    pg_exporter_config: pg_exporter-demo.yaml     # default config files for pg_exporter
    pg_exporter_enabled: true                     # setup pg_exporter on instance
    pg_exporter_port: 9630                        # default port for pg exporter
    pg_exporter_url: ''                           # optional, if not set, generate from reference parameters

    # - pgbouncer exporter - #
    pgbouncer_exporter_enabled: true              # setup pgbouncer_exporter on instance (if you don't have pgbouncer, disable it)
    pgbouncer_exporter_port: 9631                 # default port for pgbouncer exporter
    pgbouncer_exporter_url: ''                    # optional, if not set, generate from reference parameters


    #------------------------------------------------------------------------------
    # SERVICE PROVISION
    #------------------------------------------------------------------------------
    pg_weight: 100              # default load balance weight (instance level)

    # - service - #
    pg_services:                                  # how to expose postgres service in cluster?
      # primary service will route {ip|name}:5433 to primary pgbouncer (5433->6432 rw)
      - name: primary           # service name {{ pg_cluster }}_primary
        src_ip: "*"
        src_port: 5433
        dst_port: pgbouncer     # 5433 route to pgbouncer
        check_url: /primary     # primary health check, success when instance is primary
        selector: "[]"          # select all instance as primary service candidate

      # replica service will route {ip|name}:5434 to replica pgbouncer (5434->6432 ro)
      - name: replica           # service name {{ pg_cluster }}_replica
        src_ip: "*"
        src_port: 5434
        dst_port: pgbouncer
        check_url: /read-only   # read-only health check. (including primary)
        selector: "[]"          # select all instance as replica service candidate
        selector_backup: "[? pg_role == `primary`]"   # primary are used as backup server in replica service

      # default service will route {ip|name}:5436 to primary postgres (5436->5432 primary)
      - name: default           # service's actual name is {{ pg_cluster }}-{{ service.name }}
        src_ip: "*"             # service bind ip address, * for all, vip for cluster virtual ip address
        src_port: 5436          # bind port, mandatory
        dst_port: postgres      # target port: postgres|pgbouncer|port_number , pgbouncer(6432) by default
        check_method: http      # health check method: only http is available for now
        check_port: patroni     # health check port:  patroni|pg_exporter|port_number , patroni by default
        check_url: /primary     # health check url path, / as default
        check_code: 200         # health check http code, 200 as default
        selector: "[]"          # instance selector
        haproxy:                # haproxy specific fields
          maxconn: 3000         # default front-end connection
          balance: roundrobin   # load balance algorithm (roundrobin by default)
          default_server_options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'

      # offline service will route {ip|name}:5438 to offline postgres (5438->5432 offline)
      - name: offline           # service name {{ pg_cluster }}_replica
        src_ip: "*"
        src_port: 5438
        dst_port: postgres
        check_url: /replica     # offline MUST be a replica
        selector: "[? pg_role == `offline` || pg_offline_query ]"         # instances with pg_role == 'offline' or instance marked with 'pg_offline_query == true'
        selector_backup: "[? pg_role == `replica` && !pg_offline_query]"  # replica are used as backup server in offline service

    pg_services_extra: []        # extra services to be added

    # - haproxy - #
    haproxy_enabled: true                         # enable haproxy among every cluster members
    haproxy_reload: true                          # reload haproxy after config
    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_exporter_port: 9101                   # default admin/exporter port
    haproxy_client_timeout: 3h                    # client side connection timeout
    haproxy_server_timeout: 3h                    # server side connection timeout

    # - vip - #
    vip_mode: none                                # none | l2 | l4
    vip_reload: true                              # whether reload service after config
    # vip_address: 127.0.0.1                      # virtual ip address ip (l2 or l4)
    # vip_cidrmask: 24                            # virtual ip address cidr mask (l2 only)
    # vip_interface: eth0                         # virtual ip network interface (l2 only)

...

4.2 - QCloud VPC Deployment

Deployment with cloud provider VPS

本样例将基于腾讯云VPC部署Pigsty

资源准备

申请虚拟机

买几台虚拟机,如下图所示,其中11这一台作为元节点,带有公网IP,数据库节点3台,普通1核1G即可。

配置SSH远程登录

现在假设我们的管理用户名为vonng,就是我啦!现在首先配置我在元节点上到其他三台节点的ssh免密码访问。

# vonng@172.21.0.11           # meta
ssh-copy-id root@172.21.0.3   # pg-test-1
ssh-copy-id root@172.21.0.4   # pg-test-2
ssh-copy-id root@172.21.0.16  # pg-test-3
scp ~/.ssh/id_rsa.pub root@172.21.0.3:/tmp/
scp ~/.ssh/id_rsa.pub root@172.21.0.4:/tmp/
scp ~/.ssh/id_rsa.pub root@172.21.0.16:/tmp/
ssh root@172.21.0.3 'useradd vonng; mkdir -m 700 -p /home/vonng/.ssh; mv /tmp/id_rsa.pub /home/vonng/.ssh/authorized_keys; chown -R vonng /home/vonng; chmod 0600 /home/vonng/.ssh/authorized_keys;'
ssh root@172.21.0.4 'useradd vonng; mkdir -m 700 -p /home/vonng/.ssh; mv /tmp/id_rsa.pub /home/vonng/.ssh/authorized_keys; chown -R vonng /home/vonng; chmod 0600 /home/vonng/.ssh/authorized_keys;'
ssh root@172.21.0.16 'useradd vonng; mkdir -m 700 -p /home/vonng/.ssh; mv /tmp/id_rsa.pub /home/vonng/.ssh/authorized_keys; chown -R vonng /home/vonng; chmod 0600 /home/vonng/.ssh/authorized_keys;'

然后配置该用户免密码执行sudo的权限:

ssh root@172.21.0.3  "echo '%vonng ALL=(ALL) NOPASSWD: ALL' > /etc/sudoers.d/vonng"
ssh root@172.21.0.4  "echo '%vonng ALL=(ALL) NOPASSWD: ALL' > /etc/sudoers.d/vonng"
ssh root@172.21.0.16 "echo '%vonng ALL=(ALL) NOPASSWD: ALL' > /etc/sudoers.d/vonng"

# 校验配置是否成功
ssh 172.21.0.3 'sudo ls'
ssh 172.21.0.4 'sudo ls'
ssh 172.21.0.16 'sudo ls'

下载项目

# 从Github克隆代码
git clone https://github.com/Vonng/pigsty

# 如果您不能访问Github,也可以使用Pigsty CDN下载代码包
curl http://pigsty-1304147732.cos.accelerate.myqcloud.com/latest/pigsty.tar.gz -o pigsty.tgz && tar -xf pigsty.tgz && cd pigsty 

下载离线安装包

# 从Github Release页面下载
# https://github.com/Vonng/pigsty

# 如果您不能访问Github,也可以使用Pigsty CDN下载离线软件包
curl http://pigsty-1304147732.cos.accelerate.myqcloud.com/latest/pkg.tgz -o files/pkg.tgz

# 将离线安装包解压至元节点指定位置 (也许要sudo)
mv -rf /www/pigsty /www/pigsty-backup && mkdir -p /www/pigsty
tar -xf files/pkg.tgz --strip-component=1 -C /www/pigsty/

调整配置

我们可以基于Pigsty沙箱的配置文件进行调整。因为都是普通低配虚拟机,因此不需要任何实质配置修改,只需要修改连接参数与节点信息即可。简单的说,只要改IP地址就可以了!

现在将沙箱中的IP地址全部替换为云环境中的实际IP地址。(如果使用了L2 VIP,VIP也需要替换为合理的地址)

说明 沙箱IP 虚拟机IP
元节点 10.10.10.10 172.21.0.11
数据库节点1 10.10.10.11 172.21.0.3
数据库节点2 10.10.10.12 172.21.0.4
数据库节点3 10.10.10.13 172.21.0.16
pg-meta VIP 10.10.10.2 172.21.0.8
pg-test VIP 10.10.10.3 172.21.0.9

编辑配置文件:pigsty.yml,如果都是规格差不多的虚拟机,通常您只需要修改IP地址即可。特别需要注意的是在沙箱中我们是通过SSH Alias来连接的(诸如meta, node-1之类),记得移除所有ansible_host配置,我们将直接使用IP地址连接目标节点。

cat pigsty.yml | \
	sed 's/10.10.10.10/172.21.0.11/g' |\
	sed 's/10.10.10.11/172.21.0.3/g' |\
	sed 's/10.10.10.12/172.21.0.4/g' |\
	sed 's/10.10.10.13/172.21.0.16/g' |\
	sed 's/10.10.10.2/172.21.0.8/g' |\
	sed 's/10.10.10.3/172.21.0.9/g' |\
	sed 's/10.10.10.3/172.21.0.9/g' |\
	sed 's/, ansible_host: meta//g' |\
	sed 's/ansible_host: meta//g' |\
	sed 's/, ansible_host: node-[123]//g' |\
	sed 's/vip_interface: eth1/vip_interface: eth0/g' |\
	sed 's/vip_cidrmask: 8/vip_cidrmask: 24/g' > pigsty2.yml
mv pigsty.yml pigsty-backup.yml; mv pigsty2.yml pigsty.yml

就这?

是的,配置文件已经修改完了!我们可以看看到底修改了什么东西

$ diff pigsty.yml pigsty-backup.yml
38c38
<       hosts: {172.21.0.11: {}}
---
>       hosts: {10.10.10.10: {ansible_host: meta}}
46c46
<         172.21.0.11: {pg_seq: 1, pg_role: primary}
---
>         10.10.10.10: {pg_seq: 1, pg_role: primary, ansible_host: meta}
109,111c109,111
<         vip_address: 172.21.0.8             # virtual ip address
<         vip_cidrmask: 24                     # cidr network mask length
<         vip_interface: eth0                 # interface to add virtual ip
---
>         vip_address: 10.10.10.2             # virtual ip address
>         vip_cidrmask: 8                     # cidr network mask length
>         vip_interface: eth1                 # interface to add virtual ip
120,122c120,122
<         172.21.0.3: {pg_seq: 1, pg_role: primary}
<         172.21.0.4: {pg_seq: 2, pg_role: replica}
<         172.21.0.16: {pg_seq: 3, pg_role: offline}
---
>         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: offline, ansible_host: node-3}
147,149c147,149
<         vip_address: 172.21.0.9             # virtual ip address
<         vip_cidrmask: 24                     # cidr network mask length
<         vip_interface: eth0                 # interface to add virtual ip
---
>         vip_address: 10.10.10.3             # virtual ip address
>         vip_cidrmask: 8                     # cidr network mask length
>         vip_interface: eth1                 # interface to add virtual ip
326c326
<       - 172.21.0.11 yum.pigsty
---
>       - 10.10.10.10 yum.pigsty
329c329
<       - 172.21.0.11
---
>       - 10.10.10.10
393c393
<       - server 172.21.0.11 iburst
---
>       - server 10.10.10.10 iburst
417,430c417,430
<       - 172.21.0.8  pg-meta                       # sandbox vip for pg-meta
<       - 172.21.0.9  pg-test                       # sandbox vip for pg-test
<       - 172.21.0.11 meta-1                        # sandbox node meta-1 (node-0)
<       - 172.21.0.3 node-1                        # sandbox node node-1
<       - 172.21.0.4 node-2                        # sandbox node node-2
<       - 172.21.0.16 node-3                        # sandbox node node-3
<       - 172.21.0.11 pigsty
<       - 172.21.0.11 y.pigsty yum.pigsty
<       - 172.21.0.11 c.pigsty consul.pigsty
<       - 172.21.0.11 g.pigsty grafana.pigsty
<       - 172.21.0.11 p.pigsty prometheus.pigsty
<       - 172.21.0.11 a.pigsty alertmanager.pigsty
<       - 172.21.0.11 n.pigsty ntp.pigsty
<       - 172.21.0.11 h.pigsty haproxy.pigsty
---
>       - 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
>       - 10.10.10.10 h.pigsty haproxy.pigsty
442c442
<     grafana_url: http://admin:admin@172.21.0.11:3000 # grafana url
---
>     grafana_url: http://admin:admin@10.10.10.10:3000 # grafana url
478,480c478,480
<       meta-1: 172.21.0.11                         # you could use existing dcs cluster
<       # meta-2: 172.21.0.3                       # host which have their IP listed here will be init as server
<       # meta-3: 172.21.0.4                       # 3 or 5 dcs nodes are recommend for production environment
---
>       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
692c692
<           - host    all     all                         172.21.0.11/32      md5
---
>           - host    all     all                         10.10.10.10/32      md5

执行剧本

您可以使用同样的 沙箱初始化 来完成 基础设施和数据库集群的初始化。

其输出结果除了IP地址,与沙箱并无区别。参考输出

访问Demo

现在,您可以通过公网IP访问元节点上的服务了!请注意做好信息安全工作。

与沙箱环境不同的是,如果您需要从公网访问Pigsty管理界面,需要自己把定义的域名写入/etc/hosts中,或者使用真正申请的域名。

否则就只能通过IP端口直连的方式访问,例如: http://<meta_node_public_ip>:3000

Nginx监听的域名可以通过可以通过 nginx_upstream 选项。

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

4.3 - Production

Production deployment based on high-end hardware

本样例将基于一个真实生产环境作为样例。

该环境包括了200台高规格 x86 物理机:Dell R740 64核CPU / 400GB内存 / 4TB PCI-E SSD / 双万兆网卡

资源准备

调整配置

执行剧本

访问服务

4.4 - Aliyun MyBase

How to deploy pigsty montoring system for “Aliyun MyBase for PostgreSQL”

Pigsty内置了数据库供给方案,但也可以单纯作为监控系统与外部供给方案集成,例如阿里云MyBase for PostgreSQL。

与外部系统集成时,用户只需要部署一个元节点,用于设置监控基础设施。同时在监控目标机器上,需要安装Node Exporter与PG Exporter采集指标。

Pigsty提供了静态服务发现机制与Exporter二进制部署模式,以减少对外部系统的侵入。

下面将以一个实际例子介绍如何使用Pigsty监控阿里云MyBase。

资源申请

部署监控基础设施

部署监控Exporter

管理实例身份

更新实例列表

5 - Monly Deployment

How to deploy pigsty monitor system only, and intergrated with existing postgres instances

如果用户只希望使用Pigsty的监控系统部分,比如希望使用Pigsty监控系统监控已有的PostgreSQL实例,那么可以使用 仅监控部署(monitor only) 模式。

仅监控模式的部署流程与标准模式大体上保持一致,但省略了很多步骤

  • 元节点上完成基础设施初始化的部分,与标准流程一致
  • 修改配置文件,在仅监控模式中,通常只需要修改监控系统部分的参数。
  • 使用专用的剧本在数据库节点上完成仅监控部署./pgsql-monitor.yml

部署说明

监控用户

Pigsty在 PG供给 的阶段会创建监控用户,仅监控模式跳过了这些步骤,因此用户需要自行创建用于监控的用户。

用户需要自行在目标数据库集群上创建监控用户,并创建重要的监控模式与扩展(只有pg_stat_statements是必选项)。在待监控数据库实例上执行以下SQL以创建监控用户。

-- 创建监控用户
CREATE USER "dbuser_monitor" ;
ALTER ROLE "dbuser_monitor" PASSWORD 'DBUser.Monitor';
ALTER USER "dbuser_monitor" CONNECTION LIMIT 16;
GRANT "pg_monitor" TO "dbuser_monitor";
GRANT "dbrole_readonly" TO "dbuser_monitor";

-- 创建监控模式与扩展
CREATE SCHEMA IF NOT EXISTS monitor;
GRANT USAGE ON SCHEMA monitor TO "dbuser_monitor";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "monitor";

-- 额外的监控函数,用于监控共享内存指标,只有PG13及以上版本才需要。
CREATE OR REPLACE FUNCTION monitor.pg_shmem() RETURNS SETOF
    pg_shmem_allocations AS $$ SELECT * FROM pg_shmem_allocations;$$ LANGUAGE SQL SECURITY DEFINER;
COMMENT ON FUNCTION monitor.pg_shmem() IS 'security wrapper for pg_shmem';

监控连接串

默认情况下,Pigsty会尝试使用以下规则生成数据库与连接池的连接串。

PG_EXPORTER_URL='postgres://{{ pg_monitor_username }}:{{ pg_monitor_password }}@:{{ pg_port }}/{{ pg_default_database }}?host={{ pg_localhost }}&sslmode=disable'
PGBOUNCER_EXPORTER_URL='postgres://{{ pg_monitor_username }}:{{ pg_monitor_password }}@:{{ pgbouncer_port }}/pgbouncer?host={{ pg_localhost }}&sslmode=disable'

如果用户使用的监控角色连接串无法通过该规则生成,则可以使用以下参数直接配置数据库与连接池的连接信息:

作为样例,沙箱环境中元节点连接至数据库的连接串为:

PG_EXPORTER_URL='postgres://dbuser_monitor:DBUser.Monitor@:5432/meta?host=/var/run/postgresql&sslmode=disable'

懒人方案

如果不怎么关心安全性与权限,也可以直接使用dbsu ident认证的方式,例如postgres用户进行监控。

pg_exporter 默认以 dbsu 的用户执行,如果允许dbsu通过本地ident认证免密访问数据库(Pigsty默认配置),则可以直接使用超级用户监控数据库。Pigsty非常不推荐这种部署方式,但它确实很方便,既不用创建新用户,也不用配置权限。

PG_EXPORTER_URL='postgres:///postgres?host=/var/run/postgresql&sslmode=disable'

相关参数

使用仅监控部署时,只会用到Pigsty参数的一个子集。

基础设施部分

基础设施与元节点仍然与常规部署保持一致,除了以下两个参数必须强制使用指定的配置选项。

service_registry: none            # 须关闭服务注册,因为目标环境可能没有DCS基础设施。
prometheus_sd_method: static      # 须使用静态文件服务发现,因为目标实例可能并没有使用服务发现与服务注册

目标节点部分

目标节点的身份参数仍然为必选项,除此之外,通常只有监控系统参数需要调整。

---
#------------------------------------------------------------------------------
# MONITOR PROVISION
#------------------------------------------------------------------------------
# - install - #
exporter_install: none                        # none|yum|binary, none by default
exporter_repo_url: ''                         # if set, repo will be added to /etc/yum.repos.d/ before yum installation

# - collect - #
exporter_metrics_path: /metrics               # default metric path for pg related exporter

# - node exporter - #
node_exporter_enabled: true                   # setup node_exporter on instance
node_exporter_port: 9100                      # default port for node exporter
node_exporter_options: '--no-collector.softnet --collector.systemd --collector.ntp --collector.tcpstat --collector.processes'

# - pg exporter - #
pg_exporter_config: pg_exporter-demo.yaml     # default config files for pg_exporter
pg_exporter_enabled: true                     # setup pg_exporter on instance
pg_exporter_port: 9630                        # default port for pg exporter
pg_exporter_url: ''                           # optional, if not set, generate from reference parameters

# - pgbouncer exporter - #
pgbouncer_exporter_enabled: true              # setup pgbouncer_exporter on instance (if you don't have pgbouncer, disable it)
pgbouncer_exporter_port: 9631                 # default port for pgbouncer exporter
pgbouncer_exporter_url: ''                    # optional, if not set, generate from reference parameters

# - postgres variables reference - #
pg_dbsu: postgres
pg_port: 5432                                 # postgres port (5432 by default)
pgbouncer_port: 6432                          # pgbouncer port (6432 by default)
pg_localhost: /var/run/postgresql             # localhost unix socket dir for connection
pg_default_database: postgres                 # default database will be used as primary monitor target
pg_monitor_username: dbuser_monitor           # system monitor username, for postgres and pgbouncer
pg_monitor_password: DBUser.Monitor           # system monitor user's password
service_registry: consul                      # none | consul | etcd | both
...

通常来说,需要调整的参数包括:

exporter_install: binary          # none|yum|binary 建议使用拷贝二进制的方式安装Exporter
pgbouncer_exporter_enabled: false # 如果目标实例没有关联的Pgbouncer实例,则需关闭Pgbouncer监控
pg_exporter_url: ''               # 连接至 Postgres  的URL,如果不采用默认的URL拼合规则,则可使用此参数
pgbouncer_exporter_url: ''        # 连接至 Pgbouncer 的URL,如果不采用默认的URL拼合规则,则可使用此参数

局限性

Pigsty监控系统 与 Pigsty供给方案 配合紧密,原装的总是最好的。尽管Pigsty并不推荐拆分使用,但这样做确实是可行的,只是存在一些局限性。

指标缺失

Pigsty会集成多种来源的指标,包括机器节点,数据库,Pgbouncer连接池,Haproxy负载均衡器。如果用户自己的供给方案中缺少这些组件,则相应指标也会发生缺失。

通常Node与PG的监控指标总是存在,而PGbouncer与Haproxy的缺失通常会导致100~200个不等的指标损失。

特别是,Pgbouncer监控指标中包含极其重要的PG QPS,TPS,RT,而这些指标是无法从PostgreSQL本身获取的。

工作假设

Pigsty监控系统 如果要与外部供给方案配合,监控已有数据库集群,需要一些工作假设

  • 数据库采用独占式部署,与节点存在一一对应关系。只有这样,节点指标才能有意义地与数据库指标关联。
  • 目标节点可以被Ansible管理(NOPASS SSH与NOPASS SUDO),一些云厂商RDS产品并不允许这样做。
  • 数据库需要创建可用于访问监控指标的监控用户,安装必须的监控模式与扩展,并合理配置其访问控制权限。

服务发现

外部供给方案通常拥有自己的身份管理机制,因此Pigsty不会越俎代庖地部署DCS用于服务发现。这意味着用户只能采用 静态配置文件 的方式管理监控对象的身份,通常这并不是一个问题。

在Pigsty沙箱中,当实例的角色身份发生变化时,系统会通过回调函数与反熵过程及时修正实例的角色信息,如将primary修改为replica,将其他角色修改为primary

pg_up{cls="pg-meta", ins="pg-meta-1", instance="10.10.10.10:9630", ip="10.10.10.10", job="pg", role="primary", svc="pg-meta-primary"}

但与外部供给方案集成时,除非用户显式通知或回调 监控系统,根据最新角色定义生成配置文件,否则监控系统无法意识到主从发生了切换。上面的样例监控指标中,rolesvc标签会因为不及时的角色调整受到影响,这意味着Service级别的监控数据准确性会受到影响(即pg:svc:*系列指标,例如服务的QPS)。但其他层次的监控指标与图表不受主从切换影响,因此影响不大,且有其他办法解决。

管理权限

Pigsty的监控指标依赖 node_exporterpg_exporter 获取。

尽管pg_exporter可以采用exporter拉取远程数据库实例信息的方式部署,但node_exporter必须部署在数据库所属的节点上。

这意味着,用户必须拥有数据库所在机器的SSH登陆与sudo权限才能完成部署。换句话说,目标节点必须可以被Ansible纳入管理,而云厂商RDS通常不会给出此类权限。