PG Provision Parameters

Parameters about how to pull up database clusters

PG provisioning, the process of creating and pulling up a set of databases on a machine that has Postgres installed.

PG provisioning consists of the following main components.

  • Cluster identity definition
  • Cleaning up existing instances
  • Creating the directory structure, copying tools and scripts
  • Rendering Patroni template configuration files
  • Pulling up master repositories using Patroni
  • Pulling up slave repositories using Patroni
  • Configuring Pgbouncer

Pigsty uses Patroni to do the bulk of the provisioning work, and even if you choose a Patroni-less mode, pulling up the database cluster is handled by Patroni and the Patroni component is removed when creation is complete.

PG provisioning provides several Patroni customization templates, specified via the pg_conf parameter.

  • oltp.yml OLTP template, default configuration, optimized for latency and performance for production models.
  • olap.yml OLAP template, improve parallelism, optimize for throughput, long queries.
  • crit.yml) Core business template, based on OLTP template optimized for RPO, security, data integrity, enable synchronous replication and data checksum.
  • tiny.yml Micro database template, optimized for low resource scenarios, such as demo database clusters running in virtual machines.

Overview

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

Defaults

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

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

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

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

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

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

Reference

Identity

pg_cluster, pg_role, pg_seq belong to identity parameters

With the exception of the IP address, these three parameters are the minimum set of mandatory parameters to define a new set of database clusters, as shown in the configuration below.

All other parameters can be inherited from the global configuration or the default configuration, but the identity parameters must be explicitly specified and manually assigned.

  • pg_cluster identifies the name of the cluster and is configured at the cluster level.
  • pg_role identifies the role of the instance, configured at the instance level. Only the primary role will be handled specially, if left unfilled, the default is the replica role, in addition to the special delayed and offline roles.
  • pg_seq is used to identify the instance within the cluster, usually as an integer incrementing from 0 or 1, and will not be changed once assigned.
  • {{ pg_cluster }}-{{ pg_seq }} is used to uniquely identify the instance, i.e. pg_instance
  • {{ pg_cluster }}-{{ pg_role }} is used to identify the services within the cluster, i.e. pg_service
pg-test:
  hosts:
    10.10.10.11: {pg_seq: 1, pg_role: replica}
    10.10.10.12: {pg_seq: 2, pg_role: primary}
    10.10.10.13: {pg_seq: 3, pg_role: replica}
  vars:
    pg_cluster: pg-test

pg_cluster

PG数据库集群的名称

身份参数，必填参数，集群级参数

pg_seq

数据库实例的序号，在集群内部唯一，用于区别与标识集群内的不同实例，从0或1开始分配。

身份参数，必填参数，实例级参数

pg_role

数据库实例的角色，默认角色包括：primary, replica。

后续可选角色包括：offline与delayed。

身份参数，必填参数，实例级参数

pg_hostname

是否将PG实例的名称pg_instance 注册为主机名，默认禁用。

pg_nodename

是否将PG实例的名称注册为Consul中的节点名称，默认启用。

pg_exists

PG实例是否存在的标记位，不可配置。

pg_exists_action

安全保险，当PostgreSQL实例已经存在时，系统应当执行的动作

  • abort: 中止整个剧本的执行（默认行为）
  • clean: 抹除现有实例并继续（极端危险）
  • skip: 忽略存在实例的目标（中止），在其他目标机器上继续执行。

如果您真的需要强制清除已经存在的数据库实例，建议先使用pgsql-rm.yml完成集群与实例的下线与销毁，在重新执行初始化。否则，则需要通过命令行参数-e pg_exists_action=clean完成覆写，强制在初始化过程中抹除已有实例。

pg_data

默认数据目录

默认为/pg/data

pg_fs_main

主数据盘目录

默认为/export

pg_fs_bkup

归档与备份盘目录

默认为/var/backups

pg_listen

监听的IP地址，默认为所有IPv4地址

pg_port

监听的端口，默认为5432

patroni_mode

Patroni的工作模式：

  • default: 启用Patroni
  • pause: 启用Patroni，但在完成初始化后自动进入维护模式
  • remove: 依然使用Patroni初始化集群，但初始化完成后移除Patroni

pg_namespace

Patroni使用的顶层Key命名空间

默认为pg

patroni_port

Patroni API服务器默认监听的端口

默认端口为8008

patroni_watchdog_mode

当发生主从切换时，Patroni会尝试在提升从库前关闭主库。如果指定超时时间内主库仍未成功关闭，Patroni会根据配置使用Linux内核功能softdog进行fencing关机。

  • off：不使用watchdog
  • automatic：如果内核启用了softdog，则启用watchdog，不强制，默认行为。
  • required：强制使用watchdog，如果系统未启用softdog则拒绝启动。

pg_conf

拉起Postgres集群所用的Patroni模板。Pigsty预制了4种模板

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

pgbouncer_port

Pgbouncer连接池默认监听的端口

默认为6432

pgbouncer_poolmode

Pgbouncer连接池默认使用的Pool模式

默认为transaction，即事务级连接池。其他可选项包括：session|statemente

pgbouncer_max_db_conn

允许连接池与单个数据库之间建立的最大连接数

默认值为100

使用事务Pooling模式时，活跃服务端连接数通常处于个位数。如果采用会话Pooling，可以适当增大此参数。

Last modified 2021-02-08: update en deploy doc (c50b13b)