| 1-node | 2-node | 3-node | 4-node | 5+node |
|---|---|---|---|---|
meta |
dual |
trio |
full |
prod |
rich |
slim |
safe |
mssql |
oss |
pitr |
pg16 |
infra |
polar |
pro |
demo |
supa |
minio |
ivory |
citus |
This is the multi-page printable view of this section. Click here to print.
配置模板
开箱即用的配置模板，针对具体场景的配置示例，以及配置文件的详细解释。
1 - 配置总览
开箱即用的配置模板，针对具体场景的配置示例，以及配置文件的详细解释。
| 1-node | 2-node | 3-node | 4-node | 5+node |
|---|---|---|---|---|
meta |
dual |
trio |
full |
prod |
rich |
slim |
safe |
mssql |
oss |
demo |
supa |
infra |
polar |
pro |
pitr |
pg16 |
minio |
ivory |
citus |
单节点
meta: 默认使用的单节点安装配置模板，带有较完善的关键配置参数说明
rich: 下载所有可用PG扩展与Docker，并预置了一系列供软件备用的数据库
pitr: 单节点，使用远程对象存储进行持续备份与PITR的配置样例
demo: Pigsty Demo 站点使用的配置文件，使用公开域名对外服务，并使用证书
双节点
dual: 双节点配置模板，搭建基于主从复制的有限高可用 PostgreSQL 集群，允许宕机特定一台。
slim：精简安装，不构建本地软件源，不部署基础设施，仅依赖 etcd 的高可用 PG 集群。
pg16: 使用 PostgreSQL 16 替代当前最新的默认版本 PG 17，安装两节点PG集群。
supa：在本地主从 PostgreSQL 集群上使用 Docker Compose 拉起 supabase
三节点
trio：三节点配置模板，标准 HA 架构，允许三坏一
safe：安全加固的三节点配置模板，采用高标准的安全最佳实践
infra：安装一套三节点的可观测基础设施，并用其监控其他 RDS PG 实例
minio：安装一套三节点的高可用 MinIO 集群，提供 S3 兼容的对象存储服务
四节点
full：四节点标准沙箱演示环境，带有两套 PG集群，MinIO，Etcd，Redis，FerretDB 集群样例
mssql：使用 WiltonDB / Babelfish 的 Microsoft SQL Server 兼容内核替代 PostgreSQL
polar：使用阿里云 PolarDB for PostgreSQL 内核替代原生 PostgreSQL
ivory：使用瀚高的 IvorySQL （Oracle兼容内核）替代原生 PostgreSQL
2 - 单节点：meta
核心配置文件，Pigsty默认使用的单节点安装配置模板，带有较完善的关键配置参数说明
meta 配置模板是 Pigsty 默认使用的模板，它的目标是在当前单节点上完成 Pigsty 核心功能 —— PostgreSQL 的部署。
为了实现最好的兼容性，meta 模板仅下载安装包含 最小必需 软件集合，以便在所有操作系统发行版与芯片架构上实现这一目标。
配置概览
- 配置名称：
meta - 节点数量： 单节点
- 配置说明：Pigsty 默认使用的单节点安装配置模板，带有较完善的关键配置参数说明
- 适用系统：
el8,el9,d12,u22,u24 - 适用架构：
x86_64,aarch64 - 相关配置：
rich，pitr，demo - Vagrant：单节点
pigsty/vagrant/spec/meta.rb
使用方式：此配置模板为 Pigsty 默认配置模板，因此在 configure 时无需显式指定 -c meta 参数：
./configure [-i <primary_ip>]
配置内容
源文件地址：pigsty/conf/meta.yml
all:
#==============================================================#
# Clusters, Nodes, and Modules
#==============================================================#
children:
#----------------------------------#
# infra: monitor, alert, repo, etc..
#----------------------------------#
infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }
#----------------------------------#
# etcd cluster for HA postgres DCS
#----------------------------------#
etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
#----------------------------------#
# minio (OPTIONAL backup repo)
#----------------------------------#
#minio: { hosts: { 10.10.10.10: { minio_seq: 1 } }, vars: { minio_cluster: minio } }
#----------------------------------#
# pgsql (singleton on current node)
#----------------------------------#
# this is an example single-node postgres cluster with postgis & timescaledb installed, with one biz database & two biz users
pg-meta:
hosts:
10.10.10.10: { pg_seq: 1, pg_role: primary } # <---- primary instance with read-write capability
#x.xx.xx.xx: { pg_seq: 2, pg_role: replica } # <---- read only replica for read-only online traffic
#x.xx.xx.xy: { pg_seq: 3, pg_role: offline } # <---- offline instance of ETL & interactive queries
vars:
pg_cluster: pg-meta # required identity parameter, usually same as group name
# define business databases here: https://pigsty.io/docs/pgsql/db/
pg_databases: # define business databases on this cluster, array of database definition
- name: meta # REQUIRED, `name` is the only mandatory field of a database definition
baseline: cmdb.sql # optional, database sql baseline path, (relative path among ansible search path, e.g: files/)
schemas: [ pigsty ] # optional, additional schemas to be created, array of schema names
extensions: # optional, additional extensions to be installed: array of `{name[,schema]}`
- { name: vector } # install pgvector extension on this database by default
comment: pigsty meta database # optional, comment string for this database
#pgbouncer: true # optional, add this database to pgbouncer database list? true by default
#owner: postgres # optional, database owner, postgres by default
#template: template1 # optional, which template to use, template1 by default
#encoding: UTF8 # optional, database encoding, UTF8 by default. (MUST same as template database)
#locale: C # optional, database locale, C by default. (MUST same as template database)
#lc_collate: C # optional, database collate, C by default. (MUST same as template database)
#lc_ctype: C # optional, database ctype, C by default. (MUST same as template database)
#tablespace: pg_default # optional, default tablespace, 'pg_default' by default.
#allowconn: true # optional, allow connection, true by default. false will disable connect at all
#revokeconn: false # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)
#register_datasource: true # optional, register this database to grafana datasources? true by default
#connlimit: -1 # optional, database connection limit, default -1 disable limit
#pool_auth_user: dbuser_meta # optional, all connection to this pgbouncer database will be authenticated by this user
#pool_mode: transaction # optional, pgbouncer pool mode at database level, default transaction
#pool_size: 64 # optional, pgbouncer pool size at database level, default 64
#pool_size_reserve: 32 # optional, pgbouncer pool size reserve at database level, default 32
#pool_size_min: 0 # optional, pgbouncer pool size min at database level, default 0
#pool_max_db_conn: 100 # optional, max database connections at database level, default 100
#- { name: grafana ,owner: dbuser_grafana ,revokeconn: true ,comment: grafana primary database } # define another database
# define business users here: https://pigsty.io/docs/pgsql/user/
pg_users: # define business users/roles on this cluster, array of user definition
- name: dbuser_meta # REQUIRED, `name` is the only mandatory field of a user definition
password: DBUser.Meta # optional, password, can be a scram-sha-256 hash string or plain text
login: true # optional, can log in, true by default (new biz ROLE should be false)
superuser: false # optional, is superuser? false by default
createdb: false # optional, can create database? false by default
createrole: false # optional, can create role? false by default
inherit: true # optional, can this role use inherited privileges? true by default
replication: false # optional, can this role do replication? false by default
bypassrls: false # optional, can this role bypass row level security? false by default
pgbouncer: true # optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)
connlimit: -1 # optional, user connection limit, default -1 disable limit
expire_in: 3650 # optional, now + n days when this role is expired (OVERWRITE expire_at)
expire_at: '2030-12-31' # optional, YYYY-MM-DD 'timestamp' when this role is expired (OVERWRITTEN by expire_in)
comment: pigsty admin user # optional, comment string for this user/role
roles: [dbrole_admin] # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}
parameters: {} # optional, role level parameters with `ALTER ROLE SET`
pool_mode: transaction # optional, pgbouncer pool mode at user level, transaction by default
pool_connlimit: -1 # optional, max database connections at user level, default -1 disable limit
- { name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly], comment: read-only viewer for meta database }
# define pg extensions: https://pigsty.io/docs/pgext/ , and available alias: https://ext.pigsty.io/#/list
pg_libs: 'pg_stat_statements, auto_explain' # add timescaledb to shared_preload_libraries
pg_extensions: [ pgvector ]
# define HBA rules here: https://pigsty.io/docs/pgsql/hba/#define-hba
pg_hba_rules: # example hba rules
- {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
#pg_vip_enabled: true # define a L2 VIP which bind to cluster primary instance
#pg_vip_address: 10.10.10.2/24 # L2 VIP Address and netmask
#pg_vip_interface: eth1 # L2 VIP Network interface, overwrite on host vars if member have different network interface names
node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am
#----------------------------------#
# example cluster (3-node pg-test)
#----------------------------------#
#pg-test: # define the new 3-node cluster pg-test
# hosts:
# 10.10.10.11: { pg_seq: 1, pg_role: primary } # primary instance, leader of cluster
# 10.10.10.12: { pg_seq: 2, pg_role: replica } # replica instance, follower of leader
# 10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # replica with offline access
# vars:
# pg_cluster: pg-test # define pgsql cluster name
# pg_users: [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
# pg_databases: [{ name: test }] # create a database and user named 'test'
# node_tune: tiny
# pg_conf: tiny.yml
# pg_vip_enabled: true
# pg_vip_address: 10.10.10.3/24
# pg_vip_interface: eth1
# node_crontab: # make a full backup on monday 1am, and an incremental backup during weekdays
# - '00 01 * * 1 postgres /pg/bin/pg-backup full'
# - '00 01 * * 2,3,4,5,6,7 postgres /pg/bin/pg-backup'
#==============================================================#
# Global Parameters
#==============================================================#
vars:
#----------------------------------#
# Meta Data
#----------------------------------#
version: v3.1.0 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: default # upstream mirror region: default|china|europe
node_tune: tiny # node tuning specs: oltp,olap,tiny,crit
pg_conf: tiny.yml # pgsql tuning specs: {oltp,olap,tiny,crit}.yml
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.*,*.myqcloud.com,*.tsinghua.edu.cn"
# http_proxy: # set your proxy here: e.g http://user:[email protected]
# https_proxy: # set your proxy here: e.g http://user:[email protected]
# all_proxy: # set your proxy here: e.g http://user:[email protected]
infra_portal: # domain names and upstream servers
home : { domain: h.pigsty }
grafana : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
prometheus : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
blackbox : { endpoint: "${admin_ip}:9115" }
loki : { endpoint: "${admin_ip}:3100" }
#minio : { domain: sss.pigsty ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
#----------------------------------#
# MinIO Related Options
#----------------------------------#
#pgbackrest_method: minio # if you want to use minio as backup repo instead of 'local' fs, uncomment this
#minio_users: # and configure `pgbackrest_repo` & `minio_users` accordingly
# - { access_key: dba , secret_key: S3User.DBA, policy: consoleAdmin }
# - { access_key: pgbackrest , secret_key: S3User.Backup, policy: readwrite }
#pgbackrest_repo: # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
# minio: ... # optional minio repo for pgbackrest ...
# s3_key: pgbackrest # minio user access key for pgbackrest
# s3_key_secret: S3User.Backup # minio user secret key for pgbackrest
# cipher_pass: pgBackRest # AES encryption password, default is 'pgBackRest'
# if you want to use minio as backup repo instead of 'local' fs, uncomment this, and configure `pgbackrest_repo`
#pgbackrest_method: minio
#node_etc_hosts: [ '10.10.10.10 h.pigsty a.pigsty p.pigsty g.pigsty sss.pigsty' ]
#----------------------------------#
# Credential: CHANGE THESE PASSWORDS
#----------------------------------#
#grafana_admin_username: admin
grafana_admin_password: pigsty
#pg_admin_username: dbuser_dba
pg_admin_password: DBUser.DBA
#pg_monitor_username: dbuser_monitor
pg_monitor_password: DBUser.Monitor
#pg_replication_username: replicator
pg_replication_password: DBUser.Replicator
#patroni_username: postgres
patroni_password: Patroni.API
#haproxy_admin_username: admin
haproxy_admin_password: pigsty
#----------------------------------#
# Safe Guard
#----------------------------------#
# you can enable these flags after bootstrap, to prevent purging running etcd / pgsql instances
etcd_safeguard: false # prevent purging running etcd instance?
pg_safeguard: false # prevent purging running postgres instance? false by default
#----------------------------------#
# Repo, Node, Packages
#----------------------------------#
# if you wish to customize your own repo, change these settings:
repo_modules: infra,node,pgsql # install upstream repo during repo bootstrap
repo_remove: true # remove existing repo on admin node during repo bootstrap
node_repo_modules: local # install the local module in repo_upstream for all nodes
node_repo_remove: true # remove existing node repo for node managed by pigsty
repo_packages: [ # default packages to be downloaded (if `repo_packages` is not explicitly set)
node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-common #,docker
]
repo_extra_packages: [ # default postgres packages to be downloaded
pgsql-main
#,pgsql-core,pgsql-time,pgsql-gis,pgsql-rag,pgsql-fts,pgsql-olap,pgsql-feat,pgsql-lang,pgsql-type,pgsql-func,pgsql-admin,pgsql-stat,pgsql-sec,pgsql-fdw,pgsql-sim,pgsql-etl,
#,pg17-core,pg17-time,pg17-gis,pg17-rag,pg17-fts,pg17-olap,pg17-feat,pg17-lang,pg17-type,pg17-func,pg17-admin,pg17-stat,pg17-sec,pg17-fdw,pg17-sim,pg17-etl,
#,pg16-core,pg16-time,pg16-gis,pg16-rag,pg16-fts,pg16-olap,pg16-feat,pg16-lang,pg16-type,pg16-func,pg16-admin,pg16-stat,pg16-sec,pg16-fdw,pg16-sim,pg16-etl,
]
注意事项
请注意，为了在所有操作系统发行版与芯片架构上实现这一目标，meta 模板中仅下载安装包含 最小必需 软件集合。
这一变更体现在 repo_packages 与 repo_extra_packages 中：
docker默认不会被下载。- 除了
pg_repack,wal2json,pgvector之外的 PG 扩展默认不会被下载 - 属于
pgsql-utility但不属于pgsql-common部分的pg_activity pg_timetable pgFormatter pg_filedump pgxnclient timescaledb-tools pgcopydb pgloader不会被下载。
3 - 单节点：rich
在单节点基础上下载所有可用PG扩展与Docker，并预置了一系列供软件备用的数据库以便开箱即用
配置模板 rich 针对各类使用 PostgreSQL 数据库的业务软件而特别设计。
如果你希望在单机上通过 Docker 运行一些使用 PG 作为底层数据库的 业务软件，如 Odoo, Gitea, Wiki.js 等，可以考虑使用此模板。
配置概览
- 配置名称：
rich - 节点数量： 单节点
- 配置说明：在
meta基础上下载所有可用PG扩展与Docker，使用 MinIO 存储PG备份，并预置了一系列供软件备用的数据库以便开箱即用 - 配置内容：
pigsty/conf/rich.yml - 适用系统：
el8,el9,d12,u22,u24 - 适用架构：
x86_64 - 相关配置：
meta - Vagrant：
pigsty/vagrant/spec/meta.rb
此模板使用单节点部署，它在 meta 配置模板的基础上进行了以下增强：
- 在构建本地软件仓库时，下载 Docker 软件包（
docker-ce,docker-compose-plugin）。 - 在构建本地软件仓库时，下载 PostgreSQL 17 在当前
x86_64操作系统发行版中所有可用的扩展。 - 使用可选的单节点 MinIO 替代本地文件系统存储 PostgreSQL 备份。
- 预置了一系列供 Docker 软件模板开箱即用的的 PG 业务数据库与业务用户
- 添加了两个微型 Redis 独立主从实例
启用方式：在 configure 过程中使用 -c rich 参数：
./configure -c rich [-i <primary_ip>]
配置内容
源文件地址：pigsty/conf/rich.yml
all:
children:
# infra cluster for proxy, monitor, alert, etc..
infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }
# etcd cluster for ha postgres
etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
# minio cluster, s3 compatible object storage
minio: { hosts: { 10.10.10.10: { minio_seq: 1 } }, vars: { minio_cluster: minio } }
# postgres example cluster: pg-meta
pg-meta:
hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
vars:
pg_cluster: pg-meta
pg_users:
- {name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [dbrole_admin] ,comment: pigsty admin user }
- {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
- {name: dbuser_grafana ,password: DBUser.Grafana ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for grafana database }
- {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for bytebase database }
- {name: dbuser_kong ,password: DBUser.Kong ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for kong api gateway }
- {name: dbuser_gitea ,password: DBUser.Gitea ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for gitea service }
- {name: dbuser_wiki ,password: DBUser.Wiki ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for wiki.js service }
- {name: dbuser_noco ,password: DBUser.Noco ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for nocodb service }
- {name: dbuser_odoo ,password: DBUser.Odoo ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for odoo service ,createdb: true} #,superuser: true}
pg_databases:
- {name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: vector},{name: postgis},{name: timescaledb}]}
- {name: grafana ,owner: dbuser_grafana ,revokeconn: true ,comment: grafana primary database }
- {name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
- {name: kong ,owner: dbuser_kong ,revokeconn: true ,comment: kong api gateway database }
- {name: gitea ,owner: dbuser_gitea ,revokeconn: true ,comment: gitea meta database }
- {name: wiki ,owner: dbuser_wiki ,revokeconn: true ,comment: wiki meta database }
- {name: noco ,owner: dbuser_noco ,revokeconn: true ,comment: nocodb database }
- {name: odoo ,owner: dbuser_odoo ,revokeconn: true ,comment: odoo main database }
pg_hba_rules:
- {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
pg_libs: 'timescaledb,pg_stat_statements, auto_explain' # add timescaledb to shared_preload_libraries
pg_extensions:
postgis, pgvector, timescaledb
node_crontab: # make one full backup 1 am everyday
- '00 01 * * * postgres /pg/bin/pg-backup full'
redis-ms: # redis classic primary & replica
hosts: { 10.10.10.10: { redis_node: 1 , redis_instances: { 6379: { }, 6380: { replica_of: '10.10.10.10 6379' } } } }
vars: { redis_cluster: redis-ms ,redis_password: 'redis.ms' ,redis_max_memory: 64MB }
# To install & enable docker: ./docker.yml -l docker
docker:
hosts: { 10.10.10.10: { infra_seq: 1 } }
vars:
docker_enabled: true
#docker_registry_mirrors: ['https://docker.xxxxx.io'] # add your docker mirror/proxy if needed
vars: # global variables
version: v3.1.0 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: default # upstream mirror region: default|china|europe
node_tune: tiny # node tuning specs: oltp,olap,tiny,crit
pg_conf: tiny.yml # pgsql tuning specs: {oltp,olap,tiny,crit}.yml
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.*,*.myqcloud.com,*.tsinghua.edu.cn"
# http_proxy: # set your proxy here: e.g http://user:[email protected]
# https_proxy: # set your proxy here: e.g http://user:[email protected]
# all_proxy: # set your proxy here: e.g http://user:[email protected]
infra_portal: # domain names and upstream servers
home : { domain: h.pigsty }
grafana : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
prometheus : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
blackbox : { endpoint: "${admin_ip}:9115" }
loki : { endpoint: "${admin_ip}:3100" }
minio : { domain: sss.pigsty ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
postgrest : { domain: api.pigsty ,endpoint: "127.0.0.1:8884" }
pgadmin : { domain: adm.pigsty ,endpoint: "127.0.0.1:8885" }
pgweb : { domain: cli.pigsty ,endpoint: "127.0.0.1:8886" }
bytebase : { domain: ddl.pigsty ,endpoint: "127.0.0.1:8887" }
jupyter : { domain: lab.pigsty ,endpoint: "127.0.0.1:8888", websocket: true }
gitea : { domain: git.pigsty ,endpoint: "127.0.0.1:8889" }
wiki : { domain: wiki.pigsty ,endpoint: "127.0.0.1:9002" }
noco : { domain: noco.pigsty ,endpoint: "127.0.0.1:9003" }
supa : { domain: supa.pigsty ,endpoint: "10.10.10.10:8000", websocket: true }
dify : { domain: dify.pigsty ,endpoint: "10.10.10.10:8001", websocket: true }
odoo : { domain: odoo.pigsty, endpoint: "127.0.0.1:8069" , websocket: true }
nginx_navbar: # application nav links on home page
- { name: PgAdmin4 , url : 'http://adm.pigsty' , comment: 'PgAdmin4 for PostgreSQL' }
- { name: PGWeb , url : 'http://cli.pigsty' , comment: 'PGWEB Browser Client' }
- { name: ByteBase , url : 'http://ddl.pigsty' , comment: 'ByteBase Schema Migrator' }
- { name: PostgREST , url : 'http://api.pigsty' , comment: 'Kong API Gateway' }
- { name: Gitea , url : 'http://git.pigsty' , comment: 'Gitea Git Service' }
- { name: Minio , url : 'http://sss.pigsty' , comment: 'Minio Object Storage' }
- { name: Wiki , url : 'http://wiki.pigsty' , comment: 'Local Wikipedia' }
- { name: Noco , url : 'http://noco.pigsty' , comment: 'Nocodb Example' }
- { name: Odoo , url : 'http://odoo.pigsty' , comment: 'Odoo - the OpenERP' }
- { name: Explain , url : '/pigsty/pev.html' , comment: 'pgsql explain visualizer' }
- { name: Package , url : '/pigsty' , comment: 'local yum repo packages' }
- { name: PG Logs , url : '/logs' , comment: 'postgres raw csv logs' }
- { name: Schemas , url : '/schema' , comment: 'schemaspy summary report' }
- { name: Reports , url : '/report' , comment: 'pgbadger summary report' }
node_etc_hosts: [ "${admin_ip} sss.pigsty" ]
dns_records: [ "${admin_ip} api.pigsty adm.pigsty cli.pigsty ddl.pigsty lab.pigsty git.pigsty sss.pigsty wiki.pigsty noco.pigsty supa.pigsty dify.pigsty odoo.pigsty" ]
pgbackrest_method: minio # use minio as backup repo instead of 'local'
# download docker and pg17 extensions
repo_modules: infra,node,pgsql,docker
repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility, docker ]
repo_extra_packages: [ # download pg17 kernel & all available extensions (x86_64)
pg17-core,pg17-time,pg17-gis,pg17-rag,pg17-fts,pg17-olap,pg17-feat,pg17-lang,pg17-type,pg17-func,pg17-admin,pg17-stat,pg17-sec,pg17-fdw,pg17-sim,pg17-etl
]
注意事项
请注意，并非所有扩展插件都在 aarch64 (arm64) 架构上可用，因此当使用 ARM 架构时，请按需谨慎添加您所需的扩展。
- 要替换扩展，请参考扩展别名列表： https://ext.pigsty.io，替换
pg17-core,pg17-time,...等一系列通配软件包。
4 - 单节点：demo
Pigsty公开Demo使用的配置模板，展示了如何对外暴露网站，配置SSL证书，以及安装所有扩展插件
配置模板 demo 是 Pigsty 公开 Demo 使用的样例配置文件。
如果您希望在一台云服务器上搭建自己的网站，可以参考此配置模板。它展示了如何对外暴露网站，配置 SSL 证书，以及安装所有扩展插件。
配置概览
- 配置名称：
demo - 节点数量： 单节点
- 配置说明：在
meta基础上下载所有可用PG扩展与Docker，使用 MinIO 存储PG备份，并预置了一系列供软件备用的数据库以便开箱即用 - 配置内容：
pigsty/conf/demo.yml - 适用系统：
el8,el9,d12,u22,u24 - 适用架构：
x86_64 - 相关配置：
meta，rich - Vagrant：
pigsty/vagrant/spec/meta.rb
此模板使用单节点部署，它在 meta 配置模板的基础上进行了以下增强：
- 在构建本地软件仓库时，下载 Docker 软件包（
docker-ce,docker-compose-plugin）。 - 在构建本地软件仓库时，下载 PostgreSQL 17 在当前
x86_64操作系统发行版中所有可用的扩展。 - 在默认的
pg-meta集群中，安装所有下载了的 PostgreSQL 17 扩展插件。 - 显式指定了节点的时区，并使用中国地区的 NTP 服务器。
- 部署了 MinIO 但没有使用，演示环境节约存储浪费。
- 预置了一系列供 Docker 软件模板开箱即用的的 PG 业务数据库与业务用户
- 添加了三个微型 Redis 独立主从实例
- 添加了一个基于 FerretDB 的 Mongo 兼容集群
- 添加了一个 Kafka 样例集群。
启用方式：在 configure 过程中使用 -c demo 参数：
./configure -c demo [-i <primary_ip>]
配置内容
源文件地址：pigsty/conf/demo.yml
all:
children:
# infra cluster for proxy, monitor, alert, etc..
infra:
hosts: { 10.10.10.10: { infra_seq: 1 } }
vars:
nodename: pigsty.cc # overwrite the default hostname
node_id_from_pg: false # do not use the pg identity as hostname
docker_enabled: true # enable docker on this node
docker_registry_mirrors: ["https://mirror.ccs.tencentyun.com"] # use tencent cloud docker mirror
# ./pgsql-monitor.yml -l infra # monitor 'external' PostgreSQL instance
pg_exporters: # treat local postgres as RDS for demonstration purpose
20001: { pg_cluster: pg-foo, pg_seq: 1, pg_host: 10.10.10.10 }
#20002: { pg_cluster: pg-bar, pg_seq: 1, pg_host: 10.10.10.11 , pg_port: 5432 }
#20003: { pg_cluster: pg-bar, pg_seq: 2, pg_host: 10.10.10.12 , pg_exporter_url: 'postgres://dbuser_monitor:[email protected]:5432/postgres?sslmode=disable' }
#20004: { pg_cluster: pg-bar, pg_seq: 3, pg_host: 10.10.10.13 , pg_monitor_username: dbuser_monitor, pg_monitor_password: DBUser.Monitor }
# etcd cluster for ha postgres
etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
# minio cluster, s3 compatible object storage
minio: { hosts: { 10.10.10.10: { minio_seq: 1 } }, vars: { minio_cluster: minio } }
# postgres example cluster: pg-meta
pg-meta:
hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
vars:
pg_cluster: pg-meta
pg_users:
- {name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [dbrole_admin] ,comment: pigsty admin user }
- {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
- {name: dbuser_grafana ,password: DBUser.Grafana ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for grafana database }
- {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for bytebase database }
- {name: dbuser_kong ,password: DBUser.Kong ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for kong api gateway }
- {name: dbuser_gitea ,password: DBUser.Gitea ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for gitea service }
- {name: dbuser_wiki ,password: DBUser.Wiki ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for wiki.js service }
- {name: dbuser_noco ,password: DBUser.Noco ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for nocodb service }
- {name: dbuser_odoo ,password: DBUser.Odoo ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for odoo service ,createdb: true} #,superuser: true}
pg_databases:
- {name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: vector},{name: postgis},{name: timescaledb}]}
- {name: grafana ,owner: dbuser_grafana ,revokeconn: true ,comment: grafana primary database }
- {name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
- {name: kong ,owner: dbuser_kong ,revokeconn: true ,comment: kong api gateway database }
- {name: gitea ,owner: dbuser_gitea ,revokeconn: true ,comment: gitea meta database }
- {name: wiki ,owner: dbuser_wiki ,revokeconn: true ,comment: wiki meta database }
- {name: noco ,owner: dbuser_noco ,revokeconn: true ,comment: nocodb database }
- {name: odoo ,owner: dbuser_odoo ,revokeconn: true ,comment: odoo main database }
pg_hba_rules:
- {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
pg_libs: 'timescaledb,pg_stat_statements, auto_explain' # add timescaledb to shared_preload_libraries
pg_extensions: # extensions to be installed on this cluster
- timescaledb periods temporal_tables emaj table_version pg_cron pg_later pg_background #timescaledb_toolkit #pg_timeseries
- postgis pgrouting pointcloud q3c geoip pg_polyline pg_geohash #pg_h3 #ogr_fdw #mobilitydb
- pgvector pgvectorscale pg_vectorize pg_similarity smlar pg_summarize pg_tiktoken pg4ml #pgml
- pg_search pg_bigm zhparser hunspell
- pg_analytics pg_duckdb duckdb_fdw pg_parquet pg_fkpart plproxy #citus #hydra #pg_mooncake #pg_partman #pg_strom
- hll rum pg_graphql pg_jsonschema jsquery pg_hint_plan hypopg index_advisor pg_plan_filter imgsmlr pg_ivm pgmq pgq pg_cardano #age #rdkit
- pg_tle plv8 pllua pldebugger plpgsql_check plprofiler plsh pljava #plprql #plr #pgtap #faker #dbt2
- prefix semver pgunit md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_currency pgmp numeral pg_rational pguint pg_uint128 ip4r pg_uri pgemailaddr acl timestamp9 chkpass #debversion #pg_rrule
- topn pg_gzip pg_zstd pg_http pg_net pg_smtp_client pg_html5_email_address pgsql_tweaks pg_extra_time pg_timeit count_distinct extra_window_functions first_last_agg tdigest aggs_for_vecs aggs_for_arrays pg_arraymath quantile lower_quantile
- pg_idkit pg_uuidv7 permuteseq pg_hashids sequential_uuids pg_math pg_random pg_base36 pg_base62 pg_base58 floatvec pg_financial pgjwt pg_hashlib shacrypt cryptint pg_ecdsa pgpcre icu_ext pgqr envvar pg_protobuf url_encode
- pg_repack pg_squeeze pg_dirtyread pgfincore ddlx pg_prioritize pg_readonly safeupdate pg_permissions pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_savior table_log pg_fio #pgdd #pg_checksums #pgautofailover #pgpool #pgagent
- pg_profile pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_track_settings pg_wait_sampling system_stats pg_meta pgnodemx pg_sqlog bgw_replstatus pgmeminfo toastinfo pg_explain_ui pg_relusage pagevis #pg_store_plans #pg_top #powa
- passwordcheck supautils pgsodium pg_vault pg_session_jwt anonymizer pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor sslutils noset #pg_tde
- wrappers multicorn mysql_fdw tds_fdw sqlite_fdw pgbouncer_fdw redis_fdw pg_redis_pubsub hdfs_fdw firebird_fdw aws_s3 log_fdw #odbc_fdw #jdbc_fdw #oracle_fdw #db2_fdw #mongo_fdw #kafka_fdw
- orafce pgtt session_variable pg_statement_rollback pg_dbms_metadata pg_dbms_lock pgmemcache #pg_dbms_job #wiltondb
- pglogical pglogical_ticker pgl_ddl_deploy pg_failover_slots wal2json decoder_raw mimeo pg_fact_loader #wal2mongo #decoderbufs #repmgr #pg_bulkload
redis-ms: # redis classic primary & replica
hosts: { 10.10.10.10: { redis_node: 1 , redis_instances: { 6379: { }, 6380: { replica_of: '10.10.10.10 6379' }, 6381: { replica_of: '10.10.10.10 6379' } } } }
vars: { redis_cluster: redis-ms ,redis_password: 'redis.ms' ,redis_max_memory: 64MB }
# ./mongo.yml -l pg-mongo
pg-mongo:
hosts: { 10.10.10.10: { mongo_seq: 1 } }
vars:
mongo_cluster: pg-mongo
mongo_pgurl: 'postgres://dbuser_meta:[email protected]:5432/grafana'
# ./kafka.yml -l kf-main
kf-main:
hosts: { 10.10.10.10: { kafka_seq: 1, kafka_role: controller } }
vars:
kafka_cluster: kf-main
kafka_peer_port: 29093 # 9093 is occupied by alertmanager
vars: # global variables
version: v3.1.0 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: china # upstream mirror region: default|china|europe
infra_portal: # domain names and upstream servers
home : { domain: home.pigsty.cc }
cc : { domain: pigsty.cc ,path: "/www/pigsty.cc" ,cert: /etc/cert/pigsty.cc.crt ,key: /etc/cert/pigsty.cc.key }
grafana : { domain: demo.pigsty.cc ,endpoint: "${admin_ip}:3000" ,websocket: true ,cert: /etc/cert/demo.pigsty.cc.crt ,key: /etc/cert/demo.pigsty.cc.key }
prometheus : { domain: p.pigsty.cc ,endpoint: "${admin_ip}:9090" }
alertmanager : { domain: a.pigsty.cc ,endpoint: "${admin_ip}:9093" }
blackbox : { endpoint: "${admin_ip}:9115" }
loki : { endpoint: "${admin_ip}:3100" }
minio : { domain: sss.pigsty.cc ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
postgrest : { domain: api.pigsty.cc ,endpoint: "127.0.0.1:8884" }
pgadmin : { domain: adm.pigsty.cc ,endpoint: "127.0.0.1:8885" }
pgweb : { domain: cli.pigsty.cc ,endpoint: "127.0.0.1:8886" }
bytebase : { domain: ddl.pigsty.cc ,endpoint: "127.0.0.1:8887" }
jupyter : { domain: lab.pigsty.cc ,endpoint: "127.0.0.1:8888", websocket: true }
gitea : { domain: git.pigsty.cc ,endpoint: "127.0.0.1:8889" }
wiki : { domain: wiki.pigsty.cc ,endpoint: "127.0.0.1:9002" }
noco : { domain: noco.pigsty.cc ,endpoint: "127.0.0.1:9003" }
supa : { domain: supa.pigsty.cc ,endpoint: "10.10.10.10:8000", websocket: true }
dify : { domain: dify.pigsty.cc ,endpoint: "10.10.10.10:8001", websocket: true }
odoo : { domain: odoo.pigsty.cc, endpoint: "127.0.0.1:8069" , websocket: true }
# scp -r ~/pgsty/cc/cert/* pj:/etc/cert/ # copy https certs
# scp -r ~/dev/pigsty.cc/public pj:/www/pigsty.cc # copy pigsty.cc website
nginx_navbar: # application nav links on home page
- { name: PgAdmin4 , url: 'http://adm.pigsty.cc' , comment: 'PgAdmin4 for PostgreSQL' }
- { name: PGWeb , url: 'http://cli.pigsty.cc' , comment: 'PGWEB Browser Client' }
- { name: Jupyter , url: 'http://lab.pigsty.cc' , comment: 'Jupyter Notebook WebUI' }
- { name: ByteBase , url: 'http://ddl.pigsty.cc' , comment: 'ByteBase Schema Migrator' }
- { name: PostgREST , url: 'http://api.pigsty.cc' , comment: 'Kong API Gateway' }
- { name: Gitea , url: 'http://git.pigsty.cc' , comment: 'Gitea Git Service' }
- { name: Minio , url: 'http://sss.pigsty.cc' , comment: 'Minio Object Storage' }
- { name: Wiki , url: 'http://wiki.pigsty.cc' , comment: 'Local Wikipedia' }
- { name: Nocodb , url: 'http://noco.pigsty.cc' , comment: 'Nocodb Example' }
- { name: Odoo , url: 'http://odoo.pigsty.cc' , comment: 'Odoo - the OpenERP' }
- { name: Dify , url: 'http://dify.pigsty.cc' , comment: 'Dify - the LLM OPS' }
- { name: Explain , url: '/pigsty/pev.html' , comment: 'postgres explain visualizer' }
- { name: Package , url: '/pigsty' , comment: 'local yum repo packages' }
- { name: PG Logs , url: '/logs' , comment: 'postgres raw csv logs' }
- { name: Schemas , url: '/schema' , comment: 'schemaspy summary report' }
- { name: Reports , url: '/report' , comment: 'pgbadger summary report' }
- { name: ISD , url: '${grafana}/d/isd-overview' , comment: 'noaa isd data visualization' }
- { name: Covid , url: '${grafana}/d/covid-overview' , comment: 'covid data visualization' }
- { name: Worktime , url: '${grafana}/d/worktime-overview' , comment: 'worktime query' }
- { name: DBTrend , url: '${grafana}/d/dbeng-trending' , comment: 'DB Engine Trending Graph' }
node_etc_hosts: [ "${admin_ip} sss.pigsty" ]
node_timezone: Asia/Hong_Kong
node_ntp_servers:
- pool cn.pool.ntp.org iburst
- pool ${admin_ip} iburst # assume non-admin nodes does not have internet access
pgbackrest_enabled: false # do not take backups since this is disposable demo env
# download docker and pg17 extensions
repo_modules: infra,node,pgsql,docker
repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility, docker ]
repo_extra_packages: [ # download pg17 kernel & all available extensions (x86_64)
pg17-core,pg17-time,pg17-gis,pg17-rag,pg17-fts,pg17-olap,pg17-feat,pg17-lang,pg17-type,pg17-func,pg17-admin,pg17-stat,pg17-sec,pg17-fdw,pg17-sim,pg17-etl
]
注意事项
请注意，并非所有扩展插件都在 aarch64 (arm64) 架构上可用，因此当使用 ARM 架构时，请按需谨慎添加您所需的扩展。
- 要替换扩展，请参考扩展别名列表： https://ext.pigsty.io，替换
pg17-core,pg17-time,...等一系列通配软件包。
5 - 双节点：dual
双节点配置模板，有限高可用部署，允许宕机特定一台服务器。
此模板使用双节点部署，实现一主一备的 “半-高可用” 部署，如果您只有两台服务器，这是一个不错的选择。
配置概览
- 配置名称：
dual - 节点数量： 双节点
- 配置说明：两节点模版，有限高可用部署，允许特定一台服务器宕机。
- 适用系统：
el8,el9,d12,u22,u24 - 适用架构：
x86_64,aarch64 - 相关配置：
- Vagrant：双节点
pigsty/vagrant/spec/dual.rb
配置说明
启用方式：在 configure 过程中使用 -c dual 参数：
./configure -c dual [-i <primary_ip>]
配置生成完成后，您还需要将 10.10.10.11 占位节点 IP 地址修改为您的从库节点 IP 地址。
配置内容
源文件地址：pigsty/conf/dual.yml
all:
children:
# infra cluster for proxy, monitor, alert, etc..
infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }
# etcd cluster for ha postgres
etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
# minio cluster, optional backup repo for pgbackrest
#minio: { hosts: { 10.10.10.10: { minio_seq: 1 } }, vars: { minio_cluster: minio } }
# postgres cluster 'pg-dual' with single primary instance
pg-dual:
hosts:
10.10.10.10: { pg_seq: 1, pg_role: replica }
10.10.10.11: { pg_seq: 2, pg_role: primary } # <----- use this as primary by default
vars:
pg_cluster: pg-dual
pg_databases: [ { name: dual ,baseline: cmdb.sql ,comment: pigsty dual database ,schemas: [ pigsty ] ,extensions: [ { name: vector }] } ]
pg_users:
- { name: dbuser_dual ,password: DBUser.Meta ,pgbouncer: true ,roles: [ dbrole_admin ] ,comment: pigsty admin user }
- { name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [ dbrole_readonly ] ,comment: read-only viewer for dual database }
node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am
pg_vip_enabled: true
pg_vip_address: 10.10.10.2/24
pg_vip_interface: eth1
vars: # global parameters
version: v3.1.0 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: default # upstream mirror region: default,china,europe
infra_portal: # domain names and upstream servers
home : { domain: h.pigsty }
grafana : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
prometheus : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
blackbox : { endpoint: "${admin_ip}:9115" }
loki : { endpoint: "${admin_ip}:3100" }
#minio : { domain: sss.pigsty ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
# consider using local fs or external s3 service for cold backup storage in dual node configuration
#pgbackrest_method: minio
...
注意事项
通常在生产环境中，完整的高可用部署至少需要三个节点，以确保在任何一台服务器宕机时，集群仍然可以正常运行。 因为高可用故障检测 DCS（etcd） / Patroni需要多数节点的参与，而双节点无法满足这一要求。
但是，有时候只有两台服务器可用，这种情况下，dual 模板是一个可行的选择，假设您有两台服务器：
- 节点A，10.10.10.10 ，默认为管理节点，运行 Infra 基础设施，单节点 etcd，以及 PGSQL 的从库。
- 节点B，10.10.10.11 ，只做为 PGSQL 的主库。
在这种情况下，两节点模版允许 B 节点出现故障，并在故障发生后自动切换到 A 节点。 然而当 A 节点出现故障时（整个节点宕机），则需要人工介入。 不过，如果 A 节点不是整个节点宕机离线，而仅仅是 etcd 或 PostgreSQL 本身的问题，整套系统仍然可以继续正常运行。
此模板使用了一个 L2 VIP 实现高可用接入，如果您的网络条件不允许使用 L2 VIP （例如，在受限制的云环境，或跨交换机广播域），您可以考虑使用 DNS 解析或其他接入方式替代。
6 - 双节点：slim
双节点衍生配置模板，不使用 Infra 模块，不构建本地软件源，直接从互联网安装 PostgreSQL
此模板使用双节点部署模板，提供精简安装能力，您可以在不安装 Infra 模块的前提下，直接从互联网安装 PostgreSQL。
当您需要一个最简单的可用数据库实例，不希望部署监控与依赖项时，可以考虑 精简安装 模式。
配置概览
- 配置名称：
slim - 节点数量： 双节点
- 配置说明：精简安装配置模板
- 适用系统：
el8,el9,d12,u22,u24 - 适用架构：
x86_64,aarch64 - 相关配置：
dual - Vagrant：双节点
pigsty/vagrant/spec/dual.rb
配置说明
启用方式：在 configure 过程中使用 -c slim 参数：
./configure -c slim [-i <primary_ip>]
配置生成完成后，您还需要将 10.10.10.11 占位节点 IP 地址修改为您的从库节点 IP 地址。
配置内容
源文件地址：pigsty/conf/slim.yml
all:
children:
# actually not used
infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }
# etcd cluster for ha postgres, still required in minimal installation
etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
# postgres cluster 'pg-meta' with 2 instances
pg-meta:
hosts:
10.10.10.10: { pg_seq: 1, pg_role: primary }
10.10.10.11: { pg_seq: 2, pg_role: replica }
vars:
pg_cluster: pg-meta
pg_databases: [ { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [ pigsty ] ,extensions: [ { name: vector }] } ]
pg_users:
- { name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [ dbrole_admin ] ,comment: pigsty admin user }
- { name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [ dbrole_readonly ] ,comment: read-only viewer for meta database }
node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am
vars: # global parameters
version: v3.1.0 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: default # upstream mirror region: default,china,europe
node_tune: tiny # use tiny template for NODE in demo environment
pg_conf: tiny.yml # use tiny template for PGSQL in demo environment
# slim installation setup
nginx_enabled: false # nginx not exists
dns_enabled: false # dnsmasq not exists
prometheus_enabled: false # prometheus not exists
grafana_enabled: false # grafana not exists
pg_exporter_enabled: false # disable pg_exporter
pgbouncer_exporter_enabled: false
pg_vip_enabled: false
#----------------------------------#
# Repo, Node, Packages
#----------------------------------#
node_repo_modules: node,infra,pgsql # use node_repo_modules instead of repo_modules
node_repo_remove: true # remove existing node repo for node managed by pigsty?
repo_packages: [ # default packages to be downloaded (if `repo_packages` is not explicitly set)
node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-common #,docker
]
repo_extra_packages: [ # default postgres packages to be downloaded
pgsql-main
#,pgsql-core,pgsql-time,pgsql-gis,pgsql-rag,pgsql-fts,pgsql-olap,pgsql-feat,pgsql-lang,pgsql-type,pgsql-func,pgsql-admin,pgsql-stat,pgsql-sec,pgsql-fdw,pgsql-sim,pgsql-etl,
#,pg17-core,pg17-time,pg17-gis,pg17-rag,pg17-fts,pg17-olap,pg17-feat,pg17-lang,pg17-type,pg17-func,pg17-admin,pg17-stat,pg17-sec,pg17-fdw,pg17-sim,pg17-etl,
#,pg16-core,pg16-time,pg16-gis,pg16-rag,pg16-fts,pg16-olap,pg16-feat,pg16-lang,pg16-type,pg16-func,pg16-admin,pg16-stat,pg16-sec,pg16-fdw,pg16-sim,pg16-etl,
]
注意事项
因为缺少 Infra 模块提供的监控基础设施，精简安装模式不提供数据库监控能力。
7 - 三节点：trio
三节点配置模板，标准 HA 架构，允许三台服务器中的一台出现故障。
三节点是实现真正意义上高可用的最小规格，在这种情况下，DCS（etcd）可以容忍一台服务器的宕机。
在此配置中，使用了三节点的标准 HA 架构， INFRA，ETCD，PGSQL 三个核心模块均使用三节点部署，允许其中一台出现宕机。
配置概览
- 配置名称：
trio - 节点数量： 三节点 /
pigsty/vagrant/spec/trio.rb - 配置说明：三节点配置模板，标准 HA 架构，允许三台服务器中的任意一台出现宕机故障。
- 适用系统：
el8,el9,d12,u22,u24 - 适用架构：
x86_64 - 相关配置：
safe
启用方式：在 configure 过程中使用 -c trio 参数：
./configure -c trio
备注：这是一个三节点模版，您需要在生成配置后修改其他两个节点的 IP 地址
配置内容
源文件地址：pigsty/conf/trio.yml
all:
#==============================================================#
# Clusters, Nodes, and Modules
#==============================================================#
children:
#----------------------------------#
# infra: monitor, alert, repo, etc..
#----------------------------------#
infra: # infra cluster for proxy, monitor, alert, etc
hosts: # 1 for common usage, 3 nodes for production
10.10.10.10: { infra_seq: 1 } # identity required
10.10.10.11: { infra_seq: 2, repo_enabled: false }
10.10.10.12: { infra_seq: 3, repo_enabled: false }
vars:
patroni_watchdog_mode: off # do not fencing infra
docker_enabled: true # install with ./docker.yml
etcd: # dcs service for postgres/patroni ha consensus
hosts: # 1 node for testing, 3 or 5 for production
10.10.10.10: { etcd_seq: 1 } # etcd_seq required
10.10.10.11: { etcd_seq: 2 } # assign from 1 ~ n
10.10.10.12: { etcd_seq: 3 } # odd number please
vars: # cluster level parameter override roles/etcd
etcd_cluster: etcd # mark etcd cluster name etcd
etcd_safeguard: false # safeguard against purging
etcd_clean: true # purge etcd during init process
minio: # minio cluster, s3 compatible object storage
hosts: { 10.10.10.10: { minio_seq: 1 } }
vars: { minio_cluster: minio }
pg-meta: # 3 instance postgres cluster `pg-meta`
hosts:
10.10.10.10: { pg_seq: 1, pg_role: primary }
10.10.10.11: { pg_seq: 2, pg_role: replica }
10.10.10.12: { pg_seq: 3, pg_role: replica , pg_offline_query: true }
vars:
pg_cluster: pg-meta
pg_users:
- { name: dbuser_meta , password: DBUser.Meta ,pgbouncer: true ,roles: [ dbrole_admin ] ,comment: pigsty admin user }
- { name: dbuser_view , password: DBUser.View ,pgbouncer: true ,roles: [ dbrole_readonly ] ,comment: read-only viewer for meta database }
pg_databases:
- { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [ pigsty ] ,extensions: [ { name: vector } ] }
pg_vip_enabled: true
pg_vip_address: 10.10.10.2/24
pg_vip_interface: eth1
#==============================================================#
# Global Parameters
#==============================================================#
vars:
#----------------------------------#
# Meta Data
#----------------------------------#
version: v3.1.0 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: default # upstream mirror region: default|china|europe
node_tune: tiny # node tuning specs: oltp,olap,tiny,crit
pg_conf: tiny.yml # pgsql tuning specs: {oltp,olap,tiny,crit}.yml
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.*,*.myqcloud.com,*.tsinghua.edu.cn"
# http_proxy: # set your proxy here: e.g http://user:[email protected]
# https_proxy: # set your proxy here: e.g http://user:[email protected]
# all_proxy: # set your proxy here: e.g http://user:[email protected]
infra_portal: # domain names and upstream servers
home : { domain: h.pigsty }
grafana : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
prometheus : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
blackbox : { endpoint: "${admin_ip}:9115" }
loki : { endpoint: "${admin_ip}:3100" }
#minio : { domain: sss.pigsty ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
#----------------------------------#
# Repo, Node, Packages
#----------------------------------#
# if you wish to customize your own repo, change these settings:
repo_modules: infra,node,pgsql # install upstream repo during repo bootstrap
repo_remove: true # remove existing repo on admin node during repo bootstrap
node_repo_modules: local # install the local module in repo_upstream for all nodes
node_repo_remove: true # remove existing node repo for node managed by pigsty
repo_packages: [ # default packages to be downloaded (if `repo_packages` is not explicitly set)
node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-common #,docker
]
repo_extra_packages: [ # default postgres packages to be downloaded
pgsql-main
#,pgsql-core,pgsql-time,pgsql-gis,pgsql-rag,pgsql-fts,pgsql-olap,pgsql-feat,pgsql-lang,pgsql-type,pgsql-func,pgsql-admin,pgsql-stat,pgsql-sec,pgsql-fdw,pgsql-sim,pgsql-etl,
#,pg17-core,pg17-time,pg17-gis,pg17-rag,pg17-fts,pg17-olap,pg17-feat,pg17-lang,pg17-type,pg17-func,pg17-admin,pg17-stat,pg17-sec,pg17-fdw,pg17-sim,pg17-etl,
#,pg16-core,pg16-time,pg16-gis,pg16-rag,pg16-fts,pg16-olap,pg16-feat,pg16-lang,pg16-type,pg16-func,pg16-admin,pg16-stat,pg16-sec,pg16-fdw,pg16-sim,pg16-etl,
]
注意事项
8 - 四节点：full
四节点标准沙箱演示环境，带有两套 PG集群，MinIO，Etcd，Redis，FerretDB 集群样例
full 配置模板是 Pigsty 推荐的沙箱环境模板，它使用四个节点，部署两套 PostgreSQL，可以用于测试，演示 Pigsty 各方面的能力。
Pigsty 大部分教程和示例都是基于此模板置备的 沙箱环境。
配置概览
- 配置名称：
full - 节点数量： 四节点
- 配置说明：四节点标准沙箱演示环境，带有两套 PG集群，MinIO，Etcd，Redis 等集群样例
- 适用系统：
el8,el9,d12,u22,u24 - 适用架构：
x86_64,aarch64 - 相关配置：
rich，pitr，demo - Vagrant：四节点
pigsty/vagrant/spec/full.rb
启用方式：在 configure 过程中使用 -c full 参数：
./configure -c full
备注：这是一个四节点模版，您需要在生成配置后修改其他三个节点的 IP 地址（可选）
配置内容
源文件地址：pigsty/conf/full.yml
all:
children:
#----------------------------------#
# infra: monitor, alert, repo, etc..
#----------------------------------#
infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }
#----------------------------------#
# etcd cluster for HA postgres DCS
#----------------------------------#
etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
#----------------------------------#
# pgsql (singleton on current node)
#----------------------------------#
pg-meta:
hosts:
10.10.10.10: { pg_seq: 1, pg_role: primary }
vars:
pg_cluster: pg-meta
pg_databases: [ { name: meta ,baseline: cmdb.sql ,comment: polardb database ,schemas: [ pigsty ] }]
pg_users:
- { name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [ dbrole_admin ] ,comment: pigsty admin user }
- { name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [ dbrole_readonly ] ,comment: read-only viewer for meta database }
#----------------------------------#
# pgsql cluster: pg-test (3 nodes) #
#----------------------------------#
# pg-test ---> 10.10.10.3 ---> 10.10.10.1{1,2,3}
pg-test: # define the new 3-node 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: replica, pg_offline_query: true }
vars:
pg_cluster: pg-test # define pgsql cluster name
pg_users: [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
pg_databases: [{ name: test }]
pg_vip_enabled: true
pg_vip_address: 10.10.10.3/24
pg_vip_interface: eth1
vars:
#----------------------------------#
# Meta Data
#----------------------------------#
version: v3.1.0 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: default # upstream mirror region: default,china,europe
infra_portal: # domain names and upstream servers
home : { domain: h.pigsty }
grafana : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
prometheus : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
blackbox : { endpoint: "${admin_ip}:9115" }
loki : { endpoint: "${admin_ip}:3100" }
#----------------------------------#
# Ivory SQL Configuration
#----------------------------------#
pg_mode: ivory # IvorySQL Oracle Compatible Mode
pg_packages: [ 'ivorysql patroni pgbouncer pgbackrest pg_exporter pgbadger vip-manager' ]
pg_libs: 'liboracle_parser, pg_stat_statements, auto_explain'
pgbackrest_enabled: false # got checksum error when using oracle compatible mode
repo_modules: node,pgsql,infra,ivory
repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility ] #docker
repo_extra_packages: [ ivorysql ] # replace default postgresql kernel with ivroysql packages
注意事项
使用 IvorySQL Oracle 兼容模式时，pgBackRest 无法识别 pg_controldata 格式，因此 PITR 能力暂时不可用。
9 - 四节点：mssql
使用 WiltonDB / Babelfish 的 Microsoft SQL Server 兼容内核替代 PostgreSQL
mssql 配置模板基于 full 模板，使用 WiltonDB / Babelfish 数据库内核替代原生 PostgreSQL，提供 Microsoft SQL Server 线缆协议与语法兼容能力。
完整教程请参考：Babelfish (MSSQL) 内核使用说明
配置概览
- 配置名称：
mssql - 节点数量： 四节点
- 配置说明：Babelfish/WiltonDB 四节点配置模板，提供 Microsoft SQL Server 兼容能力
- 适用系统：
el7,el8,el9,u20,u22,u24 - 适用架构：
x86_64,aarch64 - 相关配置：
full - Vagrant：四节点
pigsty/vagrant/spec/full.rb
启用方式：在 configure 过程中使用 -c mssql 参数：
./configure -c mssql
备注：这是一个四节点模版，您需要在生成配置后修改其他三个节点的 IP 地址（可选）
配置内容
源文件地址：pigsty/conf/mssql.yml
all:
children:
#----------------------------------#
# infra: monitor, alert, repo, etc..
#----------------------------------#
infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }
#----------------------------------#
# etcd cluster for HA postgres DCS
#----------------------------------#
etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
#----------------------------------#
# pgsql (singleton on current node)
#----------------------------------#
# this is an example single-node postgres cluster
pg-meta:
hosts:
10.10.10.10: { pg_seq: 1, pg_role: primary } # <---- primary instance with read-write capability
vars:
pg_cluster: pg-meta
pg_users: # create MSSQL superuser
- {name: dbuser_mssql ,password: DBUser.MSSQL ,superuser: true, pgbouncer: true ,roles: [dbrole_admin], comment: superuser & owner for babelfish }
pg_databases:
- name: mssql
baseline: mssql.sql # init babelfish database & user
extensions:
- { name: uuid-ossp }
- { name: babelfishpg_common }
- { name: babelfishpg_tsql }
- { name: babelfishpg_tds }
- { name: babelfishpg_money }
- { name: pg_hint_plan }
- { name: system_stats }
- { name: tds_fdw }
owner: dbuser_mssql
parameters: { 'babelfishpg_tsql.migration_mode' : 'multi-db' }
comment: babelfish cluster, a MSSQL compatible pg cluster
#----------------------------------#
# pgsql (3-node pgsql/mssql 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: replica, pg_offline_query: true }
vars:
pg_cluster: pg-test
pg_users: # create MSSQL superuser
- {name: dbuser_mssql ,password: DBUser.MSSQL ,superuser: true, pgbouncer: true ,roles: [dbrole_admin], comment: superuser & owner for babelfish }
pg_primary_db: mssql # use `mssql` as the primary sql server database
pg_databases:
- name: mssql
baseline: mssql.sql # init babelfish database & user
extensions:
- { name: uuid-ossp }
- { name: babelfishpg_common }
- { name: babelfishpg_tsql }
- { name: babelfishpg_tds }
- { name: babelfishpg_money }
- { name: pg_hint_plan }
- { name: system_stats }
- { name: tds_fdw }
owner: dbuser_mssql
parameters: { 'babelfishpg_tsql.migration_mode' : 'single-db' }
comment: babelfish cluster, a MSSQL compatible pg cluster
vars:
#----------------------------------#
# Meta Data
#----------------------------------#
version: v3.1.0 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: default # upstream mirror region: default,china,europe
infra_portal: # domain names and upstream servers
home : { domain: h.pigsty }
grafana : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
prometheus : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
blackbox : { endpoint: "${admin_ip}:9115" }
loki : { endpoint: "${admin_ip}:3100" }
#----------------------------------#
# NODE, PGSQL, MSSQL
#----------------------------------#
pg_version: 15 # The current WiltonDB major version is 15
pg_packages: # install forked version of postgresql with babelfishpg support
- wiltondb patroni pgbouncer pgbackrest pg_exporter pgbadger vip-manager
pg_extensions: [ ] # do not install any vanilla postgresql extensions
pg_mode: mssql # Microsoft SQL Server Compatible Mode
pg_libs: 'babelfishpg_tds, pg_stat_statements, auto_explain' # add timescaledb to shared_preload_libraries
pg_default_hba_rules: # overwrite default HBA rules for babelfish cluster
- { user: '${dbsu}' ,db: all ,addr: local ,auth: ident ,title: 'dbsu access via local os user ident' }
- { user: '${dbsu}' ,db: replication ,addr: local ,auth: ident ,title: 'dbsu replication from local os ident' }
- { user: '${repl}' ,db: replication ,addr: localhost ,auth: pwd ,title: 'replicator replication from localhost' }
- { user: '${repl}' ,db: replication ,addr: intra ,auth: pwd ,title: 'replicator replication from intranet' }
- { user: '${repl}' ,db: postgres ,addr: intra ,auth: pwd ,title: 'replicator postgres db from intranet' }
- { user: '${monitor}' ,db: all ,addr: localhost ,auth: pwd ,title: 'monitor from localhost with password' }
- { user: '${monitor}' ,db: all ,addr: infra ,auth: pwd ,title: 'monitor from infra host with password' }
- { user: '${admin}' ,db: all ,addr: infra ,auth: ssl ,title: 'admin @ infra nodes with pwd & ssl' }
- { user: '${admin}' ,db: all ,addr: world ,auth: ssl ,title: 'admin @ everywhere with ssl & pwd' }
- { user: dbuser_mssql ,db: mssql ,addr: intra ,auth: md5 ,title: 'allow mssql dbsu intranet access' } # <--- use md5 auth method for mssql user
- { user: '+dbrole_readonly',db: all ,addr: localhost ,auth: pwd ,title: 'pgbouncer read/write via local socket' }
- { user: '+dbrole_readonly',db: all ,addr: intra ,auth: pwd ,title: 'read/write biz user via password' }
- { user: '+dbrole_offline' ,db: all ,addr: intra ,auth: pwd ,title: 'allow etl offline tasks from intranet' }
pg_default_services: # route primary & replica service to mssql port 1433
- { name: primary ,port: 5433 ,dest: 1433 ,check: /primary ,selector: "[]" }
- { name: replica ,port: 5434 ,dest: 1433 ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" }
- { name: default ,port: 5436 ,dest: postgres ,check: /primary ,selector: "[]" }
- { name: offline ,port: 5438 ,dest: postgres ,check: /replica ,selector: "[? pg_role == `offline` || pg_offline_query ]" , backup: "[? pg_role == `replica` && !pg_offline_query]" }
# download wiltondb instead of postgresql kernel
repo_modules: node,pgsql,infra,mssql
repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility ]
repo_extra_packages: [ wiltondb, sqlcmd ] # replace pgsql kernel with wiltondb/babelfish
注意事项
请注意，WiltonDB 仅在 EL 7/8/9 与 Ubuntu 20.04/22.04/24.04 系统上可用，目前尚不提供 Debian 系操作系统支持。
请注意，WiltonDB 目前 LTS 版本基于 PostgreSQL 15 进行。
10 - 四节点：polar
使用 WiltonDB / Babelfish 的 Microsoft SQL Server 兼容内核替代 PostgreSQL
使用 PolarDB for PostgreSQL 数据库内核替代原生 PostgreSQL，提供 “云原生” Aurora 风味的 PostgreSQL 体验
完整教程请参考：PolarDB for PostgreSQL (POLAR) 内核使用说明
配置概览
- 配置名称：
polar - 节点数量： 四节点
- 配置说明：使用阿里云 PolarDB for PostgreSQL 内核替代原生 PostgreSQL
- 适用系统：
el7,el8,el9,u20,u22,u24 - 适用架构：
x86_64 - 相关配置：
full - Vagrant：四节点
pigsty/vagrant/spec/full.rb
启用方式：在 configure 过程中使用 -c polar 参数：
./configure -c polar
备注：这是一个四节点模版，您需要在生成配置后修改其他三个节点的 IP 地址（可选）
配置内容
源文件地址：pigsty/conf/polar.yml
all:
children:
# infra cluster for proxy, monitor, alert, etc..
infra: { hosts: { 10.10.10.10: { infra_seq: 1 } }}
# etcd cluster for ha postgres
etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
# postgres example cluster: pg-meta
pg-meta:
hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
vars:
pg_cluster: pg-meta
pg_users:
- {name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [dbrole_admin] ,comment: pigsty admin user }
- {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
pg_databases:
- {name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty]}
pg_hba_rules:
- {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
pg_vip_enabled: true
pg_vip_address: 10.10.10.2/24
pg_vip_interface: eth1
# pgsql 3 node ha cluster: pg-test
pg-test:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary } # primary instance, leader of cluster
10.10.10.12: { pg_seq: 2, pg_role: replica } # replica instance, follower of leader
10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # replica with offline access
vars:
pg_cluster: pg-test # define pgsql cluster name
pg_users: [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
pg_databases: [{ name: test }]
pg_vip_enabled: true
pg_vip_address: 10.10.10.3/24
pg_vip_interface: eth1
vars: # global variables
version: v3.0.4 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: default # upstream mirror region: default|china|europe
node_tune: tiny # use tiny template for NODE in demo environment
pg_conf: tiny.yml # use tiny template for PGSQL in demo environment
infra_portal: # domain names and upstream servers
home : { domain: h.pigsty }
grafana : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
prometheus : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
blackbox : { endpoint: "${admin_ip}:9115" }
loki : { endpoint: "${admin_ip}:3100" }
#----------------------------------#
# NODE, PGSQL, PolarDB
#----------------------------------#
# THIS SPEC REQUIRE AN AVAILABLE POLARDB KERNEL IN THE LOCAL REPO!
pg_version: 15
pg_packages: [ 'polardb patroni pgbouncer pgbackrest pg_exporter pgbadger vip-manager' ]
pg_extensions: [ ] # do not install any vanilla postgresql extensions
pg_mode: polar # polardb compatible mode
pg_exporter_exclude_database: 'template0,template1,postgres,polardb_admin'
pg_default_roles: # default roles and users in postgres cluster
- { name: dbrole_readonly ,login: false ,comment: role for global read-only access }
- { name: dbrole_offline ,login: false ,comment: role for restricted read-only access }
- { name: dbrole_readwrite ,login: false ,roles: [dbrole_readonly] ,comment: role for global read-write access }
- { name: dbrole_admin ,login: false ,roles: [pg_monitor, dbrole_readwrite] ,comment: role for object creation }
- { name: postgres ,superuser: true ,comment: system superuser }
- { name: replicator ,superuser: true ,replication: true ,roles: [pg_monitor, dbrole_readonly] ,comment: system replicator } # <- superuser is required for replication
- { name: dbuser_dba ,superuser: true ,roles: [dbrole_admin] ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 ,comment: pgsql admin user }
- { name: dbuser_monitor ,roles: [pg_monitor] ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user }
repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility ]
repo_extra_packages: [ polardb ] # replace vanilla postgres kernel with polardb kernel
注意事项
请注意，PolarDB 目前不提供 ARM 架构软件包，目前仅在 EL 7/8/9 与 Ubuntu 20.04/22.04/24.04 系统上可用，目前尚不提供 Debian 系操作系统支持。
请注意，PolarDB 当前最新版本基于 PostgreSQL 15 分叉。
11 - 四节点：ivory
使用 WiltonDB / Babelfish 的 Microsoft SQL Server 兼容内核替代 PostgreSQL
ivory 配置模板基于 full 模板，使用使用瀚高的 IvorySQL （Oracle兼容内核）替代原生 PostgreSQL 内核
完整教程请参考：IvorySQL (Oracle兼容) 内核使用说明
配置概览
- 配置名称：
ivory - 节点数量： 四节点
- 配置说明：Babelfish/WiltonDB 四节点配置模板，提供 Microsoft SQL Server 兼容能力
- 适用系统：
el7,el8,el9,u20,u22,u24 - 适用架构：
x86_64,aarch64（el7除外） - 相关配置：
full - Vagrant：四节点
pigsty/vagrant/spec/full.rb
启用方式：在 configure 过程中使用 -c ivory 参数：
./configure -c ivory
备注：这是一个四节点模版，您需要在生成配置后修改其他三个节点的 IP 地址（可选）
配置内容
源文件地址：pigsty/conf/ivory.yml
all:
children:
#----------------------------------#
# infra: monitor, alert, repo, etc..
#----------------------------------#
infra: { hosts: { 10.10.10.10: { infra_seq: 1 } } }
#----------------------------------#
# etcd cluster for HA postgres DCS
#----------------------------------#
etcd: { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
#----------------------------------#
# pgsql (singleton on current node)
#----------------------------------#
# this is an example single-node postgres cluster
pg-meta:
hosts:
10.10.10.10: { pg_seq: 1, pg_role: primary } # <---- primary instance with read-write capability
vars:
pg_cluster: pg-meta
pg_users: # create MSSQL superuser
- {name: dbuser_ivory ,password: DBUser.MSSQL ,superuser: true, pgbouncer: true ,roles: [dbrole_admin], comment: superuser & owner for babelfish }
pg_databases:
- name: ivory
baseline: ivory.sql # init babelfish database & user
extensions:
- { name: uuid-ossp }
- { name: babelfishpg_common }
- { name: babelfishpg_tsql }
- { name: babelfishpg_tds }
- { name: babelfishpg_money }
- { name: pg_hint_plan }
- { name: system_stats }
- { name: tds_fdw }
owner: dbuser_ivory
parameters: { 'babelfishpg_tsql.migration_mode' : 'multi-db' }
comment: babelfish cluster, a MSSQL compatible pg cluster
#----------------------------------#
# pgsql (3-node pgsql/ivory 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: replica, pg_offline_query: true }
vars:
pg_cluster: pg-test
pg_users: # create MSSQL superuser
- {name: dbuser_ivory ,password: DBUser.MSSQL ,superuser: true, pgbouncer: true ,roles: [dbrole_admin], comment: superuser & owner for babelfish }
pg_primary_db: ivory # use `ivory` as the primary sql server database
pg_databases:
- name: ivory
baseline: ivory.sql # init babelfish database & user
extensions:
- { name: uuid-ossp }
- { name: babelfishpg_common }
- { name: babelfishpg_tsql }
- { name: babelfishpg_tds }
- { name: babelfishpg_money }
- { name: pg_hint_plan }
- { name: system_stats }
- { name: tds_fdw }
owner: dbuser_ivory
parameters: { 'babelfishpg_tsql.migration_mode' : 'single-db' }
comment: babelfish cluster, a MSSQL compatible pg cluster
vars:
#----------------------------------#
# Meta Data
#----------------------------------#
version: v3.1.0 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: default # upstream mirror region: default,china,europe
infra_portal: # domain names and upstream servers
home : { domain: h.pigsty }
grafana : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
prometheus : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
blackbox : { endpoint: "${admin_ip}:9115" }
loki : { endpoint: "${admin_ip}:3100" }
#----------------------------------#
# NODE, PGSQL, MSSQL
#----------------------------------#
pg_version: 15 # The current WiltonDB major version is 15
pg_packages: # install forked version of postgresql with babelfishpg support
- wiltondb patroni pgbouncer pgbackrest pg_exporter pgbadger vip-manager
pg_extensions: [ ] # do not install any vanilla postgresql extensions
pg_mode: ivory # Microsoft SQL Server Compatible Mode
pg_libs: 'babelfishpg_tds, pg_stat_statements, auto_explain' # add timescaledb to shared_preload_libraries
pg_default_hba_rules: # overwrite default HBA rules for babelfish cluster
- { user: '${dbsu}' ,db: all ,addr: local ,auth: ident ,title: 'dbsu access via local os user ident' }
- { user: '${dbsu}' ,db: replication ,addr: local ,auth: ident ,title: 'dbsu replication from local os ident' }
- { user: '${repl}' ,db: replication ,addr: localhost ,auth: pwd ,title: 'replicator replication from localhost' }
- { user: '${repl}' ,db: replication ,addr: intra ,auth: pwd ,title: 'replicator replication from intranet' }
- { user: '${repl}' ,db: postgres ,addr: intra ,auth: pwd ,title: 'replicator postgres db from intranet' }
- { user: '${monitor}' ,db: all ,addr: localhost ,auth: pwd ,title: 'monitor from localhost with password' }
- { user: '${monitor}' ,db: all ,addr: infra ,auth: pwd ,title: 'monitor from infra host with password' }
- { user: '${admin}' ,db: all ,addr: infra ,auth: ssl ,title: 'admin @ infra nodes with pwd & ssl' }
- { user: '${admin}' ,db: all ,addr: world ,auth: ssl ,title: 'admin @ everywhere with ssl & pwd' }
- { user: dbuser_ivory ,db: ivory ,addr: intra ,auth: md5 ,title: 'allow ivory dbsu intranet access' } # <--- use md5 auth method for ivory user
- { user: '+dbrole_readonly',db: all ,addr: localhost ,auth: pwd ,title: 'pgbouncer read/write via local socket' }
- { user: '+dbrole_readonly',db: all ,addr: intra ,auth: pwd ,title: 'read/write biz user via password' }
- { user: '+dbrole_offline' ,db: all ,addr: intra ,auth: pwd ,title: 'allow etl offline tasks from intranet' }
pg_default_services: # route primary & replica service to ivory port 1433
- { name: primary ,port: 5433 ,dest: 1433 ,check: /primary ,selector: "[]" }
- { name: replica ,port: 5434 ,dest: 1433 ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" }
- { name: default ,port: 5436 ,dest: postgres ,check: /primary ,selector: "[]" }
- { name: offline ,port: 5438 ,dest: postgres ,check: /replica ,selector: "[? pg_role == `offline` || pg_offline_query ]" , backup: "[? pg_role == `replica` && !pg_offline_query]" }
# download wiltondb instead of postgresql kernel
repo_modules: node,pgsql,infra,ivory
repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility ]
repo_extra_packages: [ wiltondb, sqlcmd ] # replace pgsql kernel with wiltondb/babelfish
注意事项
请注意，IvorySQL 仅在 EL 7/8/9 上可用，目前 EL7 因为过保，官方已经不再提供支持。
IvorySQL 提供 x86_64 (amd64) 与 aarch64（arm64） 的 RPM 包，但 EL7 不提供 aarch64 架构的支持。
请注意，IvorySQL 目前版本兼容 PostgreSQL 16 大版本。
12 - 五节点：oss
在 Pigsty 支持的五大操作系统发行版上，批量构建离线软件包。
oss 配置模板是 Pigsty 本地构建离线软件包使用的配置模板，仅能在本地开发时使用。
配置概览
- 配置名称：
oss - 节点数量： 五节点
- 配置说明：在 Pigsty 支持的五大操作系统发行版上，批量构建离线软件包。
- 适用系统：
el8,el9,d12,u22,u24（一次性） - 适用架构：
x86_64 - Vagrant：四节点
pigsty/vagrant/spec/oss.rb
启用方式：直接将 oss.yml 配置文件替换 pigsty.yml 配置文件：
cp conf/oss.yml pigsty.yml
备注：这是一个固定IP地址的构建模板
配置内容
源文件地址：pigsty/conf/oss.yml
all:
vars:
version: v3.1.0
admin_ip: 10.10.10.8
region: china
etcd_clean: true
proxy_env:
no_proxy: "localhost,127.0.0.1,10.0.0.0/8,192.168.0.0/16,*.pigsty,*.aliyun.com,mirrors.*,*.myqcloud.com,*.tsinghua.edu.cn"
pg_version: 17
repo_modules: infra,node,pgsql,docker #pg16,pg15,pg14,pg13,pg12,kube,mssql,ivory
repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility, docker ]
repo_url_packages: # extra packages from url
- { name: "pev.html" ,url: "http://10.10.10.1/etc/pev-1.12.1.html" }
- { name: "chart.tgz" ,url: "http://10.10.10.1/etc/chart-1.0.0.tgz" }
- { name: "plugins.tgz" ,url: "http://10.10.10.1/etc/plugins-11.3.0.tgz" }
children:
infra:
hosts:
10.10.10.8: { infra_seq: 1, admin_ip: 10.10.10.8 }
10.10.10.9: { infra_seq: 2, admin_ip: 10.10.10.9 }
10.10.10.12: { infra_seq: 3, admin_ip: 10.10.10.12 }
10.10.10.22: { infra_seq: 4, admin_ip: 10.10.10.22 }
10.10.10.24: { infra_seq: 5, admin_ip: 10.10.10.24 }
vars: { node_conf: oltp }
etcd: { hosts: { 10.10.10.8: { etcd_seq: 1 }}, vars: { etcd_cluster: etcd } }
rpm: # overwrite rpm building upstream with local repo to accelerate building
hosts:
10.10.10.8: { }
10.10.10.9: { }
vars:
repo_upstream:
- { name: pigsty-local ,description: 'Pigsty Local' ,module: local ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://${admin_ip}/pigsty' }} # used by intranet nodes
- { name: pigsty-infra ,description: 'Pigsty INFRA' ,module: infra ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://10.10.10.1/yum/infra/$basearch' ,china: 'http://10.10.10.1/yum/infra/$basearch' }}
- { name: pigsty-pgsql ,description: 'Pigsty PGSQL' ,module: pgsql ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://10.10.10.1/yum/pgsql/el$releasever.$basearch' ,china: 'http://10.10.10.1/yum/pgsql/el$releasever.$basearch' }}
- { name: nginx ,description: 'Nginx Repo' ,module: infra ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://nginx.org/packages/rhel/$releasever/$basearch/' }}
- { name: baseos ,description: 'EL 8+ BaseOS' ,module: node ,releases: [ 8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://dl.rockylinux.org/pub/rocky/$releasever/BaseOS/$basearch/os/' ,china: 'https://mirrors.aliyun.com/rockylinux/$releasever/BaseOS/$basearch/os/' ,europe: 'https://mirrors.xtom.de/rocky/$releasever/BaseOS/$basearch/os/' }}
- { name: appstream ,description: 'EL 8+ AppStream' ,module: node ,releases: [ 8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://dl.rockylinux.org/pub/rocky/$releasever/AppStream/$basearch/os/' ,china: 'https://mirrors.aliyun.com/rockylinux/$releasever/AppStream/$basearch/os/' ,europe: 'https://mirrors.xtom.de/rocky/$releasever/AppStream/$basearch/os/' }}
- { name: extras ,description: 'EL 8+ Extras' ,module: node ,releases: [ 8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://dl.rockylinux.org/pub/rocky/$releasever/extras/$basearch/os/' ,china: 'https://mirrors.aliyun.com/rockylinux/$releasever/extras/$basearch/os/' ,europe: 'https://mirrors.xtom.de/rocky/$releasever/extras/$basearch/os/' }}
- { name: powertools ,description: 'EL 8 PowerTools' ,module: node ,releases: [ 8 ] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://dl.rockylinux.org/pub/rocky/$releasever/PowerTools/$basearch/os/' ,china: 'https://mirrors.aliyun.com/rockylinux/$releasever/PowerTools/$basearch/os/' ,europe: 'https://mirrors.xtom.de/rocky/$releasever/PowerTools/$basearch/os/' }}
- { name: crb ,description: 'EL 9 CRB' ,module: node ,releases: [ 9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://dl.rockylinux.org/pub/rocky/$releasever/CRB/$basearch/os/' ,china: 'https://mirrors.aliyun.com/rockylinux/$releasever/CRB/$basearch/os/' ,europe: 'https://mirrors.xtom.de/rocky/$releasever/CRB/$basearch/os/' }}
- { name: epel ,description: 'EL 8+ EPEL' ,module: node ,releases: [ 8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://download.fedoraproject.org/pub/epel/$releasever/Everything/$basearch/' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/epel/$releasever/Everything/$basearch/' ,europe: 'https://mirrors.xtom.de/epel/$releasever/Everything/$basearch/' }}
- { name: pgdg-common ,description: 'PostgreSQL Common' ,module: pgsql ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/common/redhat/rhel-$releasever-$basearch' , europe: 'https://mirrors.xtom.de/postgresql/repos/yum/common/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg-extras ,description: 'PostgreSQL Extra' ,module: pgsql ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/common/pgdg-rhel$releasever-extras/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/common/pgdg-rhel$releasever-extras/redhat/rhel-$releasever-$basearch' , europe: 'https://mirrors.xtom.de/postgresql/repos/yum/common/pgdg-rhel$releasever-extras/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg-el8fix ,description: 'PostgreSQL EL8FIX' ,module: pgsql ,releases: [ 8 ] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/common/pgdg-centos8-sysupdates/redhat/rhel-8-x86_64/' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/common/pgdg-centos8-sysupdates/redhat/rhel-8-x86_64/' , europe: 'https://mirrors.xtom.de/postgresql/repos/yum/common/pgdg-centos8-sysupdates/redhat/rhel-8-x86_64/' } }
- { name: pgdg-el9fix ,description: 'PostgreSQL EL9FIX' ,module: pgsql ,releases: [ 9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/common/pgdg-rocky9-sysupdates/redhat/rhel-9-x86_64/' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/common/pgdg-rocky9-sysupdates/redhat/rhel-9-x86_64/' , europe: 'https://mirrors.xtom.de/postgresql/repos/yum/common/pgdg-rocky9-sysupdates/redhat/rhel-9-x86_64/' }}
- { name: pgdg17 ,description: 'PostgreSQL 17' ,module: pgsql ,releases: [ 8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/17/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/17/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/17/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg17-nonfree ,description: 'PostgreSQL 17+' ,module: pgsql ,releases: [ 8,9] ,arch: [x86_64 ] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/non-free/17/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/non-free/17/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/non-free/17/redhat/rhel-$releasever-$basearch' }}
- { name: timescaledb ,description: 'TimescaleDB' ,module: pgsql ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://packagecloud.io/timescale/timescaledb/el/$releasever/$basearch' }}
# these repos are not used by default, but can be enabled by setting repo_modules
- { name: pgdg12 ,description: 'PostgreSQL 12' ,module: pg12 ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/12/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/12/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg12-nonfree ,description: 'PostgreSQL 12+' ,module: pg12 ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/non-free/12/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/non-free/12/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/non-free/12/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg13 ,description: 'PostgreSQL 13' ,module: pg13 ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/13/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/13/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg13-nonfree ,description: 'PostgreSQL 13+' ,module: pg13 ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/non-free/13/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/non-free/13/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/non-free/13/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg14 ,description: 'PostgreSQL 14' ,module: pg14 ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/14/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/14/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg14-nonfree ,description: 'PostgreSQL 14+' ,module: pg14 ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/non-free/14/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/non-free/14/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/non-free/14/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg15 ,description: 'PostgreSQL 15' ,module: pg15 ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/15/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/15/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg15-nonfree ,description: 'PostgreSQL 15+' ,module: pg15 ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/non-free/15/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/non-free/15/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/non-free/15/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg16 ,description: 'PostgreSQL 16' ,module: pg16 ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/16/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/16/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/16/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg16-nonfree ,description: 'PostgreSQL 16+' ,module: pg16 ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/non-free/16/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/non-free/16/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/non-free/16/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg17 ,description: 'PostgreSQL 17' ,module: pg17 ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/17/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/17/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/17/redhat/rhel-$releasever-$basearch' }}
- { name: pgdg17-nonfree ,description: 'PostgreSQL 17+' ,module: pg17 ,releases: [7,8,9] ,arch: [x86_64 ] ,baseurl: { default: 'https://download.postgresql.org/pub/repos/yum/non-free/17/redhat/rhel-$releasever-$basearch' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/non-free/17/redhat/rhel-$releasever-$basearch' ,europe: 'https://mirrors.xtom.de/postgresql/repos/yum/non-free/17/redhat/rhel-$releasever-$basearch' }}
- { name: docker-ce ,description: 'Docker CE' ,module: docker ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.docker.com/linux/centos/$releasever/$basearch/stable' ,china: 'https://mirrors.aliyun.com/docker-ce/linux/centos/$releasever/$basearch/stable' ,europe: 'https://mirrors.xtom.de/docker-ce/linux/centos/$releasever/$basearch/stable' }}
- { name: kubernetes ,description: 'Kubernetes' ,module: kube ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://pkgs.k8s.io/core:/stable:/v1.31/rpm/', china: 'https://mirrors.aliyun.com/kubernetes-new/core/stable/v1.31/rpm/' }}
- { name: wiltondb ,description: 'WiltonDB' ,module: mssql ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.copr.fedorainfracloud.org/results/wiltondb/wiltondb/epel-$releasever-$basearch/' }}
- { name: ivorysql ,description: 'IvorySQL' ,module: ivory ,releases: [7,8,9] ,arch: [x86_64 ] ,baseurl: { default: 'https://yum.highgo.ca/dists/ivorysql-rpms/3/redhat/rhel-$releasever-$basearch', china: 'http://10.10.10.1/yum/ivory/el$releasever.$basearch' }}
- { name: mysql ,description: 'MySQL' ,module: mysql ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://repo.mysql.com/yum/mysql-8.0-community/el/$releasever/$basearch/', china: 'https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-8.0-community-el7-$basearch/' }}
- { name: grafana ,description: 'Grafana' ,module: grafana ,releases: [7,8,9] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://rpm.grafana.com' }}
deb: # overwrite deb building upstream with local repo to accelerate building
hosts:
10.10.10.12: { }
10.10.10.22: { }
10.10.10.24: { }
vars:
repo_upstream:
- { name: pigsty-local ,description: 'Pigsty Local' ,module: local ,releases: [11,12,20,22,24] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://${admin_ip}/pigsty ./' }}
- { name: pigsty-pgsql ,description: 'Pigsty PgSQL' ,module: pgsql ,releases: [11,12,20,22,24] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://10.10.10.1/apt/pgsql/${distro_codename} ${distro_codename} main', china: 'http://10.10.10.1/apt/pgsql/${distro_codename} ${distro_codename} main' }}
- { name: pigsty-infra ,description: 'Pigsty Infra' ,module: infra ,releases: [11,12,20,22,24] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://10.10.10.1/apt/infra/ generic main' ,china: 'http://10.10.10.1/apt/infra/ generic main' }}
- { name: nginx ,description: 'Nginx' ,module: infra ,releases: [11,12,20,22,24] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://nginx.org/packages/${distro_name} ${distro_codename} nginx' }}
- { name: base ,description: 'Debian Basic' ,module: node ,releases: [11,12 ] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://deb.debian.org/debian/ ${distro_codename} main non-free-firmware' ,china: 'https://mirrors.aliyun.com/debian/ ${distro_codename} main restricted universe multiverse' }}
- { name: updates ,description: 'Debian Updates' ,module: node ,releases: [11,12 ] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://deb.debian.org/debian/ ${distro_codename}-updates main non-free-firmware' ,china: 'https://mirrors.aliyun.com/debian/ ${distro_codename}-updates main restricted universe multiverse' }}
- { name: security ,description: 'Debian Security' ,module: node ,releases: [11,12 ] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://security.debian.org/debian-security ${distro_codename}-security main non-free-firmware' }}
- { name: base ,description: 'Ubuntu Basic' ,module: node ,releases: [ 20,22,24] ,arch: [x86_64 ] ,baseurl: { default: 'https://mirrors.edge.kernel.org/ubuntu/ ${distro_codename} main universe multiverse restricted' ,china: 'https://mirrors.aliyun.com/ubuntu/ ${distro_codename} main restricted universe multiverse' }}
- { name: updates ,description: 'Ubuntu Updates' ,module: node ,releases: [ 20,22,24] ,arch: [x86_64 ] ,baseurl: { default: 'https://mirrors.edge.kernel.org/ubuntu/ ${distro_codename}-backports main restricted universe multiverse' ,china: 'https://mirrors.aliyun.com/ubuntu/ ${distro_codename}-updates main restricted universe multiverse' }}
- { name: backports ,description: 'Ubuntu Backports' ,module: node ,releases: [ 20,22,24] ,arch: [x86_64 ] ,baseurl: { default: 'https://mirrors.edge.kernel.org/ubuntu/ ${distro_codename}-security main restricted universe multiverse' ,china: 'https://mirrors.aliyun.com/ubuntu/ ${distro_codename}-backports main restricted universe multiverse' }}
- { name: security ,description: 'Ubuntu Security' ,module: node ,releases: [ 20,22,24] ,arch: [x86_64 ] ,baseurl: { default: 'https://mirrors.edge.kernel.org/ubuntu/ ${distro_codename}-updates main restricted universe multiverse' ,china: 'https://mirrors.aliyun.com/ubuntu/ ${distro_codename}-security main restricted universe multiverse' }}
- { name: base ,description: 'Ubuntu Basic' ,module: node ,releases: [ 20,22,24] ,arch: [ aarch64] ,baseurl: { default: 'http://ports.ubuntu.com/ubuntu-ports/ ${distro_codename} main universe multiverse restricted' ,china: 'https://mirrors.aliyun.com/ubuntu-ports/ ${distro_codename} main restricted universe multiverse' }}
- { name: updates ,description: 'Ubuntu Updates' ,module: node ,releases: [ 20,22,24] ,arch: [ aarch64] ,baseurl: { default: 'http://ports.ubuntu.com/ubuntu-ports/ ${distro_codename}-backports main restricted universe multiverse' ,china: 'https://mirrors.aliyun.com/ubuntu-ports/ ${distro_codename}-updates main restricted universe multiverse' }}
- { name: backports ,description: 'Ubuntu Backports' ,module: node ,releases: [ 20,22,24] ,arch: [ aarch64] ,baseurl: { default: 'http://ports.ubuntu.com/ubuntu-ports/ ${distro_codename}-security main restricted universe multiverse' ,china: 'https://mirrors.aliyun.com/ubuntu-ports/ ${distro_codename}-backports main restricted universe multiverse' }}
- { name: security ,description: 'Ubuntu Security' ,module: node ,releases: [ 20,22,24] ,arch: [ aarch64] ,baseurl: { default: 'http://ports.ubuntu.com/ubuntu-ports/ ${distro_codename}-updates main restricted universe multiverse' ,china: 'https://mirrors.aliyun.com/ubuntu-ports/ ${distro_codename}-security main restricted universe multiverse' }}
- { name: pgdg ,description: 'PGDG' ,module: pgsql ,releases: [11,12,20,22,24] ,arch: [x86_64, aarch64] ,baseurl: { default: 'http://apt.postgresql.org/pub/repos/apt/ ${distro_codename}-pgdg main' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/ ${distro_codename}-pgdg main' }}
- { name: citus ,description: 'Citus' ,module: pgsql ,releases: [11,12,20,22 ] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://packagecloud.io/citusdata/community/${distro_name}/ ${distro_codename} main' } }
- { name: timescaledb ,description: 'Timescaledb' ,module: pgsql ,releases: [11,12,20,22,24] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://packagecloud.io/timescale/timescaledb/${distro_name}/ ${distro_codename} main' }}
# these repos are not used by default, but can be enabled by setting repo_modules
- { name: grafana ,description: 'Grafana' ,module: grafana ,releases: [11,12,20,22,24] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://apt.grafana.com stable main' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/grafana/apt/ stable main' }}
- { name: pgml ,description: 'PostgresML' ,module: pgml ,releases: [ 22 ] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://apt.postgresml.org ${distro_codename} main' }}
- { name: wiltondb ,description: 'WiltonDB' ,module: mssql ,releases: [ 20,22,24] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://ppa.launchpadcontent.net/wiltondb/wiltondb/ubuntu/ ${distro_codename} main', china: 'http://10.10.10.1/apt/mssql/${distro_codename} ${distro_codename} main' }}
- { name: mysql ,description: 'MySQL' ,module: mysql ,releases: [11,12,20,22,24] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://repo.mysql.com/apt/${distro_name} ${distro_codename} mysql-8.0 mysql-tools', china: 'https://mirrors.tuna.tsinghua.edu.cn/mysql/apt/${distro_name} ${distro_codename} mysql-8.0 mysql-tools' }}
- { name: docker-ce ,description: 'Docker' ,module: docker ,releases: [11,12,20,22,24] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://download.docker.com/linux/${distro_name} ${distro_codename} stable' ,china: 'https://mirrors.tuna.tsinghua.edu.cn/docker-ce/linux//${distro_name} ${distro_codename} stable' }}
- { name: kubernetes ,description: 'Kubernetes' ,module: kube ,releases: [11,12,20,22,24] ,arch: [x86_64, aarch64] ,baseurl: { default: 'https://pkgs.k8s.io/core:/stable:/v1.31/deb/ /', china: 'https://mirrors.aliyun.com/kubernetes-new/core/stable/v1.31/deb/ /' }}
#----------------------------------#
# build el8 packages on Rocky 8.9
#----------------------------------#
el8:
hosts: { 10.10.10.8: { pg_seq: 1, pg_role: primary } }
vars:
pg_cluster: pg-el8
#repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility ]
repo_extra_packages: [pgsql-core,pgsql-time,pgsql-gis,pgsql-rag,pgsql-fts,pgsql-olap,pgsql-feat,pgsql-lang,pgsql-type,pgsql-func,pgsql-admin,pgsql-stat,pgsql-sec,pgsql-fdw,pgsql-sim,pgsql-etl]
pg_extensions:
- timescaledb periods temporal_tables emaj table_version pg_cron pg_later pg_background #timescaledb_toolkit #pg_timeseries
- postgis pgrouting pointcloud q3c geoip pg_polyline pg_geohash #pg_h3 #ogr_fdw #mobilitydb
- pgvector pgvectorscale pg_vectorize pg_similarity smlar pg_summarize pg_tiktoken pg4ml #pgml
- pg_search pg_bigm zhparser hunspell
- pg_analytics pg_duckdb duckdb_fdw pg_parquet pg_fkpart plproxy #citus #hydra #pg_mooncake #pg_partman #pg_strom
- hll rum pg_graphql pg_jsonschema jsquery pg_hint_plan hypopg index_advisor pg_plan_filter imgsmlr pg_ivm pgmq pgq pg_cardano #age #rdkit
- pg_tle plv8 pllua pldebugger plpgsql_check plprofiler plsh #plprql #pljava #plr #pgtap #faker #dbt2
- prefix semver pgunit md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_currency pgmp numeral pg_rational pguint pg_uint128 ip4r pg_uri pgemailaddr acl timestamp9 chkpass #debversion #pg_rrule
- topn pg_gzip pg_zstd pg_http pg_net pg_smtp_client pg_html5_email_address pgsql_tweaks pg_extra_time pg_timeit count_distinct extra_window_functions first_last_agg tdigest aggs_for_vecs aggs_for_arrays pg_arraymath quantile lower_quantile
- pg_idkit pg_uuidv7 permuteseq pg_hashids sequential_uuids pg_math pg_random pg_base36 pg_base62 pg_base58 floatvec pg_financial pgjwt pg_hashlib shacrypt cryptint pg_ecdsa pgpcre icu_ext pgqr envvar pg_protobuf url_encode
- pg_repack pg_squeeze pg_dirtyread pgfincore ddlx pg_prioritize pg_readonly safeupdate pg_permissions pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_savior table_log pg_fio #pgdd #pg_checksums #pgautofailover #pgpool #pgagent
- pg_profile pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_track_settings pg_wait_sampling system_stats pg_meta pgnodemx pg_sqlog bgw_replstatus pgmeminfo toastinfo pg_explain_ui pg_relusage pagevis #pg_store_plans #pg_top #powa
- passwordcheck supautils pgsodium pg_vault pg_session_jwt anonymizer pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor sslutils noset #pg_tde
- wrappers multicorn mysql_fdw tds_fdw sqlite_fdw pgbouncer_fdw redis_fdw pg_redis_pubsub hdfs_fdw firebird_fdw aws_s3 log_fdw #odbc_fdw #jdbc_fdw #oracle_fdw #db2_fdw #mongo_fdw #kafka_fdw
- orafce pgtt session_variable pg_statement_rollback pg_dbms_metadata pg_dbms_lock pgmemcache #pg_dbms_job #wiltondb
- pglogical pglogical_ticker pgl_ddl_deploy pg_failover_slots wal2json decoder_raw mimeo pg_fact_loader #wal2mongo #decoderbufs #repmgr #pg_bulkload
#----------------------------------#
# build el9 packages on Rocky 9.3
#----------------------------------#
el9:
hosts: { 10.10.10.9: { pg_seq: 1, pg_role: primary } }
vars:
pg_cluster: pg-el9
#repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility ]
repo_extra_packages: [pgsql-core,pgsql-time,pgsql-gis,pgsql-rag,pgsql-fts,pgsql-olap,pgsql-feat,pgsql-lang,pgsql-type,pgsql-func,pgsql-admin,pgsql-stat,pgsql-sec,pgsql-fdw,pgsql-sim,pgsql-etl]
pg_extensions:
- timescaledb periods temporal_tables emaj table_version pg_cron pg_later pg_background #timescaledb_toolkit #pg_timeseries
- postgis pgrouting pointcloud q3c geoip pg_polyline pg_geohash #pg_h3 #ogr_fdw #mobilitydb
- pgvector pgvectorscale pg_vectorize pg_similarity smlar pg_summarize pg_tiktoken pg4ml #pgml
- pg_search pg_bigm zhparser hunspell
- pg_analytics pg_duckdb duckdb_fdw pg_parquet pg_fkpart plproxy #citus #hydra #pg_mooncake #pg_partman #pg_strom
- hll rum pg_graphql pg_jsonschema jsquery pg_hint_plan hypopg index_advisor pg_plan_filter imgsmlr pg_ivm pgmq pgq pg_cardano #age #rdkit
- pg_tle plv8 pllua pldebugger plpgsql_check plprofiler plsh pljava #plprql #plr #pgtap #faker #dbt2
- prefix semver pgunit md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_currency pgmp numeral pg_rational pguint pg_uint128 ip4r pg_uri pgemailaddr acl timestamp9 chkpass #debversion #pg_rrule
- topn pg_gzip pg_zstd pg_http pg_net pg_smtp_client pg_html5_email_address pgsql_tweaks pg_extra_time pg_timeit count_distinct extra_window_functions first_last_agg tdigest aggs_for_vecs aggs_for_arrays pg_arraymath quantile lower_quantile
- pg_idkit pg_uuidv7 permuteseq pg_hashids sequential_uuids pg_math pg_random pg_base36 pg_base62 pg_base58 floatvec pg_financial pgjwt pg_hashlib shacrypt cryptint pg_ecdsa pgpcre icu_ext pgqr envvar pg_protobuf url_encode
- pg_repack pg_squeeze pg_dirtyread pgfincore ddlx pg_prioritize pg_readonly safeupdate pg_permissions pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_savior table_log pg_fio #pgdd #pg_checksums #pgautofailover #pgpool #pgagent
- pg_profile pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_track_settings pg_wait_sampling system_stats pg_meta pgnodemx pg_sqlog bgw_replstatus pgmeminfo toastinfo pg_explain_ui pg_relusage pagevis #pg_store_plans #pg_top #powa
- passwordcheck supautils pgsodium pg_vault pg_session_jwt anonymizer pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor sslutils noset #pg_tde
- wrappers multicorn mysql_fdw tds_fdw sqlite_fdw pgbouncer_fdw redis_fdw pg_redis_pubsub hdfs_fdw firebird_fdw aws_s3 log_fdw #odbc_fdw #jdbc_fdw #oracle_fdw #db2_fdw #mongo_fdw #kafka_fdw
- orafce pgtt session_variable pg_statement_rollback pg_dbms_metadata pg_dbms_lock pgmemcache #pg_dbms_job #wiltondb
- pglogical pglogical_ticker pgl_ddl_deploy pg_failover_slots wal2json decoder_raw mimeo pg_fact_loader #wal2mongo #decoderbufs #repmgr #pg_bulkload
#----------------------------------#
# build d12 packages on Debian 12
#----------------------------------#
d12:
hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } }
vars:
pg_cluster: pg-d12
#repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility ]
repo_extra_packages: [pgsql-core,pgsql-time,pgsql-gis,pgsql-rag,pgsql-fts,pgsql-olap,pgsql-feat,pgsql-lang,pgsql-type,pgsql-func,pgsql-admin,pgsql-stat,pgsql-sec,pgsql-fdw,pgsql-sim,pgsql-etl]
pg_extensions:
- timescaledb periods temporal_tables emaj table_version pg_cron pg_later pg_background #timescaledb_toolkit #pg_timeseries
- postgis pgrouting pointcloud pg_h3 q3c ogr_fdw geoip pg_polyline pg_geohash mobilitydb
- pgvector pgvectorscale pg_vectorize pg_similarity smlar pg_summarize pg_tiktoken pg4ml #pgml
- pg_search pg_bigm zhparser hunspell
- pg_analytics pg_duckdb duckdb_fdw pg_parquet pg_fkpart pg_partman plproxy #citus #hydra #pg_mooncake #pg_strom
- hll rum pg_graphql pg_jsonschema jsquery pg_hint_plan hypopg index_advisor pg_plan_filter imgsmlr pg_ivm pgmq pgq pg_cardano #age #rdkit
- pg_tle plv8 pllua pldebugger plpgsql_check plprofiler plsh pljava #plprql #plr #pgtap #faker #dbt2
- prefix semver pgunit md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_currency pgmp numeral pg_rational pguint pg_uint128 ip4r pg_uri pgemailaddr acl debversion pg_rrule timestamp9 chkpass
- pg_gzip pg_zstd pg_http pg_net pg_smtp_client pg_html5_email_address pgsql_tweaks pg_extra_time pg_timeit count_distinct extra_window_functions first_last_agg tdigest aggs_for_vecs aggs_for_arrays pg_arraymath quantile lower_quantile
- pg_idkit pg_uuidv7 permuteseq pg_hashids sequential_uuids pg_math pg_random pg_base36 pg_base62 pg_base58 floatvec pg_financial pgjwt pg_hashlib shacrypt cryptint pg_ecdsa pgpcre icu_ext pgqr envvar pg_protobuf url_encode #topn
- pg_repack pg_squeeze pg_dirtyread pgfincore ddlx pg_prioritize pg_checksums pg_readonly safeupdate pg_permissions pgautofailover pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_savior table_log pg_fio #pgdd #pgpool #pgagent
- pg_profile pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_track_settings pg_wait_sampling system_stats pg_meta pgnodemx pg_sqlog bgw_replstatus pgmeminfo toastinfo pg_explain_ui pg_relusage pagevis #pg_store_plans #pg_top #powa
- passwordcheck supautils pgsodium pg_vault pg_session_jwt anonymizer pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor sslutils noset #pg_tde
- wrappers mysql_fdw tds_fdw redis_fdw pg_redis_pubsub firebird_fdw aws_s3 log_fdw #multicorn #odbc_fdw #jdbc_fdw #oracle_fdw #db2_fdw #sqlite_fdw #pgbouncer_fdw #mongo_fdw #kafka_fdw #hdfs_fdw
- orafce pgtt session_variable pg_statement_rollback pgmemcache #pg_dbms_metadata #pg_dbms_lock #pg_dbms_job
- pglogical pglogical_ticker pgl_ddl_deploy pg_failover_slots wal2json decoder_raw mimeo pg_fact_loader #wal2mongo #decoderbufs #repmgr #pg_bulkload
#----------------------------------#
# build u22 packages on Ubuntu 22.04
#----------------------------------#
u22:
hosts: { 10.10.10.22: { pg_seq: 1, pg_role: primary } }
vars:
pg_cluster: pg-u22
#repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility ]
repo_extra_packages: [pgsql-core,pgsql-time,pgsql-gis,pgsql-rag,pgsql-fts,pgsql-olap,pgsql-feat,pgsql-lang,pgsql-type,pgsql-func,pgsql-admin,pgsql-stat,pgsql-sec,pgsql-fdw,pgsql-sim,pgsql-etl]
pg_extensions:
- timescaledb periods temporal_tables emaj table_version pg_cron pg_later pg_background #timescaledb_toolkit #pg_timeseries
- postgis pgrouting pointcloud pg_h3 q3c ogr_fdw geoip pg_polyline pg_geohash mobilitydb
- pgvector pgvectorscale pg_vectorize pg_similarity smlar pg_summarize pg_tiktoken pg4ml #pgml
- pg_search pg_bigm zhparser hunspell
- pg_analytics pg_duckdb duckdb_fdw pg_parquet pg_fkpart pg_partman plproxy #citus #hydra #pg_mooncake #pg_strom
- hll rum pg_graphql pg_jsonschema jsquery pg_hint_plan hypopg index_advisor pg_plan_filter imgsmlr pg_ivm pgmq pgq pg_cardano #age #rdkit
- pg_tle plv8 pllua pldebugger plpgsql_check plprofiler plsh pljava #plprql #plr #pgtap #faker #dbt2
- prefix semver pgunit md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_currency pgmp numeral pg_rational pguint pg_uint128 ip4r pg_uri pgemailaddr acl debversion pg_rrule timestamp9 chkpass
- pg_gzip pg_zstd pg_http pg_net pg_smtp_client pg_html5_email_address pgsql_tweaks pg_extra_time pg_timeit count_distinct extra_window_functions first_last_agg tdigest aggs_for_vecs aggs_for_arrays pg_arraymath quantile lower_quantile
- pg_idkit pg_uuidv7 permuteseq pg_hashids sequential_uuids pg_math pg_random pg_base36 pg_base62 pg_base58 floatvec pg_financial pgjwt pg_hashlib shacrypt cryptint pg_ecdsa pgpcre icu_ext pgqr envvar pg_protobuf url_encode #topn
- pg_repack pg_squeeze pg_dirtyread pgfincore ddlx pg_prioritize pg_checksums pg_readonly safeupdate pg_permissions pgautofailover pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_savior table_log pg_fio #pgdd #pgpool #pgagent
- pg_profile pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_track_settings pg_wait_sampling system_stats pg_meta pgnodemx pg_sqlog bgw_replstatus pgmeminfo toastinfo pg_explain_ui pg_relusage pagevis #pg_store_plans #pg_top #powa
- passwordcheck supautils pgsodium pg_vault pg_session_jwt anonymizer pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor sslutils noset #pg_tde
- wrappers mysql_fdw tds_fdw redis_fdw pg_redis_pubsub firebird_fdw aws_s3 log_fdw #multicorn #odbc_fdw #jdbc_fdw #oracle_fdw #db2_fdw #sqlite_fdw #pgbouncer_fdw #mongo_fdw #kafka_fdw #hdfs_fdw
- orafce pgtt session_variable pg_statement_rollback pgmemcache #pg_dbms_metadata #pg_dbms_lock #pg_dbms_job #wiltondb
- pglogical pglogical_ticker pgl_ddl_deploy pg_failover_slots wal2json decoder_raw mimeo pg_fact_loader #wal2mongo #decoderbufs #repmgr #pg_bulkload
#----------------------------------#
# build u24 packages on Ubuntu 24.04
#----------------------------------#
u24:
hosts: { 10.10.10.24: { pg_seq: 1, pg_role: primary } }
vars:
pg_cluster: pg-u24
#repo_packages: [ node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility ]
repo_extra_packages: [pgsql-core,pgsql-time,pgsql-gis,pgsql-rag,pgsql-fts,pgsql-olap,pgsql-feat,pgsql-lang,pgsql-type,pgsql-func,pgsql-admin,pgsql-stat,pgsql-sec,pgsql-fdw,pgsql-sim,pgsql-etl]
pg_extensions:
- timescaledb periods temporal_tables emaj table_version pg_cron pg_later pg_background #timescaledb_toolkit #pg_timeseries
- postgis pgrouting pointcloud pg_h3 q3c ogr_fdw geoip pg_polyline pg_geohash mobilitydb
- pgvector pgvectorscale pg_vectorize pg_similarity smlar pg_summarize pg_tiktoken pg4ml #pgml
- pg_search pg_bigm zhparser hunspell
- pg_analytics pg_duckdb duckdb_fdw pg_parquet pg_fkpart pg_partman plproxy #citus #hydra #pg_mooncake #pg_strom
- hll rum pg_graphql pg_jsonschema jsquery pg_hint_plan hypopg index_advisor pg_plan_filter imgsmlr pg_ivm pgmq pgq pg_cardano #age #rdkit
- pg_tle plv8 pllua pldebugger plpgsql_check plprofiler plsh pljava #plprql #plr #pgtap #faker #dbt2
- prefix semver pgunit md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_currency pgmp numeral pg_rational pguint pg_uint128 ip4r pg_uri pgemailaddr acl debversion pg_rrule timestamp9 chkpass
- pg_gzip pg_zstd pg_http pg_net pg_smtp_client pg_html5_email_address pgsql_tweaks pg_extra_time pg_timeit count_distinct extra_window_functions first_last_agg tdigest aggs_for_vecs aggs_for_arrays pg_arraymath quantile lower_quantile
- pg_idkit pg_uuidv7 permuteseq pg_hashids sequential_uuids pg_math pg_random pg_base36 pg_base62 pg_base58 floatvec pg_financial pgjwt pg_hashlib shacrypt cryptint pg_ecdsa pgpcre icu_ext pgqr envvar pg_protobuf url_encode #topn
- pg_repack pg_squeeze pg_dirtyread pgfincore ddlx pg_prioritize pg_checksums pg_readonly safeupdate pg_permissions pgautofailover pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_savior table_log pg_fio #pgdd #pgpool #pgagent
- pg_profile pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_track_settings pg_wait_sampling system_stats pg_meta pgnodemx pg_sqlog bgw_replstatus pgmeminfo toastinfo pg_explain_ui pg_relusage pagevis #pg_store_plans #pg_top #powa
- passwordcheck supautils pgsodium pg_vault pg_session_jwt anonymizer pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor sslutils noset #pg_tde
- wrappers mysql_fdw tds_fdw redis_fdw pg_redis_pubsub firebird_fdw aws_s3 log_fdw #multicorn #odbc_fdw #jdbc_fdw #oracle_fdw #db2_fdw #sqlite_fdw #pgbouncer_fdw #mongo_fdw #kafka_fdw #hdfs_fdw
- orafce pgtt session_variable pg_statement_rollback pgmemcache #pg_dbms_metadata #pg_dbms_lock #pg_dbms_job #wiltondb
- pglogical pglogical_ticker pgl_ddl_deploy pg_failover_slots wal2json decoder_raw mimeo pg_fact_loader #wal2mongo #decoderbufs #repmgr #pg_bulkload
注意事项
构建需要使用到 http://10.10.10.1 的本地源，需要提前配置好本地源，否则构建会失败。您可以将其替换为 https://repo.pigsty.cc 或 https://repo.pigsty.io 完成构建。
13 - 生产仿真：prod
由 43 个节点组成，生产环境仿真配置，需要强大的宿主机方可运行。
配置概览
- 配置名称：
prod - 节点数量： 43节点
- 配置说明：43 节点的生产环境仿真配置，需要强大的宿主机方可运行。
- 适用系统：
el8,el9,d12,u22,u24 - 适用架构：
x86_64 - Vagrant：四节点
pigsty/vagrant/spec/prod.rb
cp -f conf/prod.yml pigsty.yml
备注：这是一个四节点模版，您需要在生成配置后修改其他三个节点的 IP 地址（可选）
配置内容
源文件地址：pigsty/conf/full.yml
all:
children:
#==========================================================#
# infra: 2 nodes
#==========================================================#
# ./infra.yml -l infra
# ./docker.yml -l infra (optional)
infra:
hosts:
10.10.10.10: { infra_seq: 1 ,nodename: infra-1 }
10.10.10.11: { infra_seq: 2 ,nodename: infra-2 }
vars:
node_id_from_pg: false
docker_enabled: true
node_cluster: infra
node_conf: oltp
pg_exporters: # bin/pgmon-add pg-meta2/pg-test2/pg-src2/pg-dst2
20001: {pg_cluster: pg-meta2 ,pg_seq: 1 ,pg_host: 10.10.10.10, pg_databases: [{ name: meta }]}
20002: {pg_cluster: pg-meta2 ,pg_seq: 2 ,pg_host: 10.10.10.11, pg_databases: [{ name: meta }]}
20003: {pg_cluster: pg-test2 ,pg_seq: 1 ,pg_host: 10.10.10.41, pg_databases: [{ name: test }]}
20004: {pg_cluster: pg-test2 ,pg_seq: 2 ,pg_host: 10.10.10.42, pg_databases: [{ name: test }]}
20005: {pg_cluster: pg-test2 ,pg_seq: 3 ,pg_host: 10.10.10.43, pg_databases: [{ name: test }]}
20006: {pg_cluster: pg-test2 ,pg_seq: 4 ,pg_host: 10.10.10.44, pg_databases: [{ name: test }]}
20007: {pg_cluster: pg-src2 ,pg_seq: 1 ,pg_host: 10.10.10.45, pg_databases: [{ name: src }]}
20008: {pg_cluster: pg-src2 ,pg_seq: 2 ,pg_host: 10.10.10.46, pg_databases: [{ name: src }]}
20009: {pg_cluster: pg-src2 ,pg_seq: 3 ,pg_host: 10.10.10.47, pg_databases: [{ name: src }]}
20010: {pg_cluster: pg-dst2 ,pg_seq: 3 ,pg_host: 10.10.10.48, pg_databases: [{ name: dst }]}
20011: {pg_cluster: pg-dst2 ,pg_seq: 4 ,pg_host: 10.10.10.49, pg_databases: [{ name: dst }]}
#==========================================================#
# nodes: 43 nodes
#==========================================================#
# ./node.yml
nodes:
hosts:
10.10.10.10 : { nodename: meta-1 ,cpu: 8 ,mem: 32768 }
10.10.10.11 : { nodename: meta-2 ,cpu: 8 ,mem: 32768 }
10.10.10.12 : { nodename: pg-v12-1 ,cpu: 2 ,mem: 4096 }
10.10.10.13 : { nodename: pg-v13-1 ,cpu: 2 ,mem: 4096 }
10.10.10.14 : { nodename: pg-v14-1 ,cpu: 2 ,mem: 4096 }
10.10.10.15 : { nodename: pg-v15-1 ,cpu: 2 ,mem: 4096 }
10.10.10.16 : { nodename: pg-v16-1 ,cpu: 2 ,mem: 4096 }
10.10.10.21 : { nodename: etcd-1 ,cpu: 2 ,mem: 4096 }
10.10.10.22 : { nodename: etcd-2 ,cpu: 2 ,mem: 4096 }
10.10.10.23 : { nodename: etcd-3 ,cpu: 2 ,mem: 4096 }
10.10.10.24 : { nodename: etcd-4 ,cpu: 2 ,mem: 4096 }
10.10.10.25 : { nodename: etcd-5 ,cpu: 2 ,mem: 4096 }
10.10.10.26 : { nodename: minio-1 ,cpu: 2 ,mem: 4096 }
10.10.10.27 : { nodename: minio-2 ,cpu: 2 ,mem: 4096 }
10.10.10.28 : { nodename: minio-3 ,cpu: 2 ,mem: 4096 }
10.10.10.29 : { nodename: proxy-1 ,cpu: 2 ,mem: 4096 }
10.10.10.30 : { nodename: proxy-2 ,cpu: 2 ,mem: 4096 }
10.10.10.41 : { nodename: pg-test-1 ,cpu: 1 ,mem: 2048 }
10.10.10.42 : { nodename: pg-test-2 ,cpu: 1 ,mem: 2048 }
10.10.10.43 : { nodename: pg-test-3 ,cpu: 1 ,mem: 2048 }
10.10.10.44 : { nodename: pg-test-4 ,cpu: 1 ,mem: 2048 }
10.10.10.45 : { nodename: pg-src-1 ,cpu: 1 ,mem: 2048 }
10.10.10.46 : { nodename: pg-src-2 ,cpu: 1 ,mem: 2048 }
10.10.10.47 : { nodename: pg-src-3 ,cpu: 1 ,mem: 2048 }
10.10.10.48 : { nodename: pg-dst-1 ,cpu: 1 ,mem: 2048 }
10.10.10.49 : { nodename: pg-dst-2 ,cpu: 1 ,mem: 2048 }
10.10.10.50 : { nodename: pg-citus0-1 ,cpu: 1 ,mem: 2048 }
10.10.10.51 : { nodename: pg-citus0-2 ,cpu: 1 ,mem: 2048 }
10.10.10.52 : { nodename: pg-citus1-1 ,cpu: 1 ,mem: 2048 }
10.10.10.53 : { nodename: pg-citus1-2 ,cpu: 1 ,mem: 2048 }
10.10.10.54 : { nodename: pg-citus2-1 ,cpu: 1 ,mem: 2048 }
10.10.10.55 : { nodename: pg-citus2-2 ,cpu: 1 ,mem: 2048 }
10.10.10.56 : { nodename: pg-citus3-1 ,cpu: 1 ,mem: 2048 }
10.10.10.57 : { nodename: pg-citus3-2 ,cpu: 1 ,mem: 2048 }
10.10.10.58 : { nodename: pg-citus4-1 ,cpu: 1 ,mem: 2048 }
10.10.10.59 : { nodename: pg-citus4-2 ,cpu: 1 ,mem: 2048 }
10.10.10.81 : { nodename: redis-test-1 ,cpu: 1 ,mem: 2048 }
10.10.10.82 : { nodename: redis-test-2 ,cpu: 1 ,mem: 2048 }
10.10.10.83 : { nodename: redis-test-3 ,cpu: 1 ,mem: 2048 }
10.10.10.84 : { nodename: redis-test-4 ,cpu: 1 ,mem: 2048 }
10.10.10.85 : { nodename: redis-ms-1 ,cpu: 1 ,mem: 2048 }
10.10.10.86 : { nodename: redis-ms-2 ,cpu: 1 ,mem: 2048 }
10.10.10.88 : { nodename: test ,cpu: 4 ,mem: 8192 }
#==========================================================#
# pg-meta: reuse infra node as meta cmdb
#==========================================================#
# ./pgsql.yml -l pg-meta
pg-meta:
hosts:
10.10.10.10: { pg_seq: 1 , pg_role: primary }
10.10.10.11: { pg_seq: 2 , pg_role: replica }
vars:
pg_cluster: pg-meta
pg_vip_enabled: true
pg_vip_address: 10.10.10.2/24
pg_vip_interface: eth1
pg_users:
- {name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [dbrole_admin] ,comment: pigsty admin user }
- {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
- {name: dbuser_grafana ,password: DBUser.Grafana ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for grafana database }
- {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for bytebase database }
- {name: dbuser_kong ,password: DBUser.Kong ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for kong api gateway }
- {name: dbuser_gitea ,password: DBUser.Gitea ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for gitea service }
- {name: dbuser_wiki ,password: DBUser.Wiki ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for wiki.js service }
- {name: dbuser_noco ,password: DBUser.Noco ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for nocodb service }
pg_databases:
- { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: vector}]}
- { name: grafana ,owner: dbuser_grafana ,revokeconn: true ,comment: grafana primary database }
- { name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
- { name: kong ,owner: dbuser_kong ,revokeconn: true ,comment: kong the api gateway database }
- { name: gitea ,owner: dbuser_gitea ,revokeconn: true ,comment: gitea meta database }
- { name: wiki ,owner: dbuser_wiki ,revokeconn: true ,comment: wiki meta database }
- { name: noco ,owner: dbuser_noco ,revokeconn: true ,comment: nocodb database }
pg_hba_rules:
- { user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes' }
pg_libs: 'pg_stat_statements, auto_explain' # add timescaledb to shared_preload_libraries
node_crontab: # make a full backup on monday 1am, and an incremental backup during weekdays
- '00 01 * * 1 postgres /pg/bin/pg-backup full'
- '00 01 * * 2,3,4,5,6,7 postgres /pg/bin/pg-backup'
#==========================================================#
# pg-v12 - v16 (no longer supported in OSS ver since 2.6)
#==========================================================#
# ./pgsql.yml -l pg-v*
pg-v12:
hosts: { 10.10.10.12: { pg_seq: 1 ,pg_role: primary } }
vars:
pg_cluster: pg-v12
#pg_version: 12
pg_extensions: []
pg_users: [ { name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] } ]
pg_databases: [ { name: v12 } ]
pg_service_provider: proxy # use load balancer on group `proxy` with port 10012
pg_default_services: [{ name: primary ,port: 10012 ,dest: postgres ,check: /primary ,selector: "[]" }]
pg-v13:
hosts: { 10.10.10.13: { pg_seq: 1 ,pg_role: primary } }
vars:
pg_cluster: pg-v13
#pg_version: 13
pg_extensions: []
pg_users: [ { name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] } ]
pg_databases: [ { name: v13 } ]
pg_service_provider: proxy # use load balancer on group `proxy` with port 10013
pg_default_services: [{ name: primary ,port: 10013 ,dest: postgres ,check: /primary ,selector: "[]" }]
pg-v14:
hosts: { 10.10.10.14: { pg_seq: 1 ,pg_role: primary } }
vars:
pg_cluster: pg-v14
#pg_version: 14
pg_extensions: []
pg_users: [ { name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] } ]
pg_databases: [ { name: v14 } ]
pg_service_provider: proxy # use load balancer on group `proxy` with port 10014
pg_default_services: [{ name: primary ,port: 10014 ,dest: postgres ,check: /primary ,selector: "[]" }]
pg-v15:
hosts: { 10.10.10.15: { pg_seq: 1 ,pg_role: primary } }
vars:
pg_cluster: pg-v15
#pg_version: 15
pg_extensions: []
pg_users: [ { name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] } ]
pg_databases: [ { name: v15 } ]
pg_service_provider: proxy # use load balancer on group `proxy` with port 10013
pg_default_services: [{ name: primary ,port: 10015 ,dest: postgres ,check: /primary ,selector: "[]" }]
pg-v16: # beware that PostgreSQL 16 is not available on el7
hosts: { 10.10.10.16: { pg_seq: 1 ,pg_role: primary } }
vars:
pg_cluster: pg-v16
#pg_version: 16
pg_extensions: []
pg_users: [ { name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] } ]
pg_databases: [ { name: v16 } ]
pg_service_provider: proxy # use load balancer on group `proxy` with port 10013
pg_default_services: [{ name: primary ,port: 10016 ,dest: postgres ,check: /primary ,selector: "[]" }]
#==========================================================#
# etcd: 5 nodes used as dedicated minio cluster
#==========================================================#
# ./etcd.yml -l etcd;
etcd: # the 5 node etcd dcs cluster
hosts:
10.10.10.21: { etcd_seq: 1 , nodename: etcd-1 }
10.10.10.22: { etcd_seq: 2 , nodename: etcd-2 }
10.10.10.23: { etcd_seq: 3 , nodename: etcd-3 }
10.10.10.24: { etcd_seq: 4 , nodename: etcd-4 }
10.10.10.25: { etcd_seq: 5 , nodename: etcd-5 }
vars:
etcd_cluster: etcd
node_cluster: etcd
#==========================================================#
# minio: 3 nodes used as dedicated minio cluster
#==========================================================#
# ./minio.yml -l minio;
minio: # access service via sss.pigsty:9002
hosts:
10.10.10.26: { minio_seq: 1 , nodename: minio-1 }
10.10.10.27: { minio_seq: 2 , nodename: minio-2 }
10.10.10.28: { minio_seq: 3 , nodename: minio-3 }
vars:
minio_cluster: minio
node_cluster: minio
minio_data: '/data' # it should be a raw device for a multi-node deployment!
minio_node: '${minio_cluster}-${minio_seq}.pigsty' # minio node name pattern
vip_enabled: true
vip_vrid: 127
vip_address: 10.10.10.100
vip_interface: eth1
haproxy_services: # expose minio service via haproxy node port 9002
- name: minio # [REQUIRED] service name, unique
port: 9002 # [REQUIRED] service port, unique
options: # [OPTIONAL] minio health check
- option httpchk
- option http-keep-alive
- http-check send meth OPTIONS uri /minio/health/live
- http-check expect status 200
servers:
- { name: minio-1 ,ip: 10.10.10.26 ,port: 9000 ,options: 'check-ssl ca-file /etc/pki/ca.crt check port 9000' }
- { name: minio-2 ,ip: 10.10.10.27 ,port: 9000 ,options: 'check-ssl ca-file /etc/pki/ca.crt check port 9000' }
- { name: minio-3 ,ip: 10.10.10.28 ,port: 9000 ,options: 'check-ssl ca-file /etc/pki/ca.crt check port 9000' }
#==========================================================#
# proxy: 2 nodes used as dedicated haproxy server
#==========================================================#
# ./node.yml -l proxy
proxy:
hosts:
10.10.10.29: { nodename: proxy-1 }
10.10.10.30: { nodename: proxy-2 }
vars:
node_cluster: proxy
vip_enabled: true
vip_vrid: 128
vip_address: 10.10.10.99
vip_interface: eth1
#==========================================================#
# pg-test: dedicate 4 node testing cluster
#==========================================================#
# ./pgsql.yml -l pg-test
pg-test:
hosts:
10.10.10.41: { pg_seq: 1 ,pg_role: primary }
10.10.10.42: { pg_seq: 2 ,pg_role: replica }
10.10.10.43: { pg_seq: 3 ,pg_role: replica }
10.10.10.44: { pg_seq: 4 ,pg_role: replica }
vars:
pg_cluster: pg-test
pg_vip_enabled: true
pg_vip_address: 10.10.10.3/24
pg_vip_interface: eth1
pg_users: [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
pg_databases: [{ name: test }]
#==========================================================#
# pg-src: dedicate 3 node testing cluster
#==========================================================#
# ./pgsql.yml -l pg-src
pg-src:
hosts:
10.10.10.45: { pg_seq: 1 ,pg_role: primary }
10.10.10.46: { pg_seq: 2 ,pg_role: replica }
10.10.10.47: { pg_seq: 3 ,pg_role: replica }
vars:
pg_cluster: pg-src
#pg_version: 14
pg_vip_enabled: true
pg_vip_address: 10.10.10.4/24
pg_vip_interface: eth1
pg_users: [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
pg_databases: [{ name: src }]
#==========================================================#
# pg-dst: dedicate 2 node testing cluster
#==========================================================#
# ./pgsql.yml -l pg-dst
pg-dst:
hosts:
10.10.10.48: { pg_seq: 1 ,pg_role: primary } # 8C 8G
10.10.10.49: { pg_seq: 2 ,pg_role: replica } # 1C 2G
vars:
pg_cluster: pg-dst
pg_vip_enabled: true
pg_vip_address: 10.10.10.5/24
pg_vip_interface: eth1
node_hugepage_ratio: 0.3
pg_users: [ { name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] } ]
pg_databases: [ { name: dst } ]
#==========================================================#
# pg-citus: 10 node citus cluster (5 x primary-replica pair)
#==========================================================#
pg-citus: # citus group
hosts:
10.10.10.50: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.60/24 ,pg_seq: 0, pg_role: primary }
10.10.10.51: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.60/24 ,pg_seq: 1, pg_role: replica }
10.10.10.52: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.61/24 ,pg_seq: 0, pg_role: primary }
10.10.10.53: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.61/24 ,pg_seq: 1, pg_role: replica }
10.10.10.54: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.62/24 ,pg_seq: 0, pg_role: primary }
10.10.10.55: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.62/24 ,pg_seq: 1, pg_role: replica }
10.10.10.56: { pg_group: 3, pg_cluster: pg-citus3 ,pg_vip_address: 10.10.10.63/24 ,pg_seq: 0, pg_role: primary }
10.10.10.57: { pg_group: 3, pg_cluster: pg-citus3 ,pg_vip_address: 10.10.10.63/24 ,pg_seq: 1, pg_role: replica }
10.10.10.58: { pg_group: 4, pg_cluster: pg-citus4 ,pg_vip_address: 10.10.10.64/24 ,pg_seq: 0, pg_role: primary }
10.10.10.59: { pg_group: 4, pg_cluster: pg-citus4 ,pg_vip_address: 10.10.10.64/24 ,pg_seq: 1, pg_role: replica }
vars:
pg_mode: citus # pgsql cluster mode: citus
pg_shard: pg-citus # citus shard name: pg-citus
pg_primary_db: test # primary database used by citus
pg_dbsu_password: DBUser.Postgres # all dbsu password access for citus cluster
pg_vip_enabled: true
pg_vip_interface: eth1
pg_extensions: [ 'citus postgis pgvector' ]
pg_libs: 'citus, timescaledb, pg_stat_statements, auto_explain' # citus will be added by patroni automatically
pg_users: [ { name: test ,password: test ,pgbouncer: true ,roles: [ dbrole_admin ] } ]
pg_databases: [ { name: test ,owner: test ,extensions: [ { name: citus }, { name: vector } ] } ]
pg_hba_rules:
- { user: 'all' ,db: all ,addr: 10.10.10.0/24 ,auth: trust ,title: 'trust citus cluster members' }
- { user: 'all' ,db: all ,addr: 127.0.0.1/32 ,auth: ssl ,title: 'all user ssl access from localhost' }
- { user: 'all' ,db: all ,addr: intra ,auth: ssl ,title: 'all user ssl access from intranet' }
#==========================================================#
# redis-meta: reuse the 5 etcd nodes as redis sentinel
#==========================================================#
# ./redis.yml -l redis-meta
redis-meta:
hosts:
10.10.10.21: { redis_node: 1 , redis_instances: { 26379: {} } }
10.10.10.22: { redis_node: 2 , redis_instances: { 26379: {} } }
10.10.10.23: { redis_node: 3 , redis_instances: { 26379: {} } }
10.10.10.24: { redis_node: 4 , redis_instances: { 26379: {} } }
10.10.10.25: { redis_node: 5 , redis_instances: { 26379: {} } }
vars:
redis_cluster: redis-meta
redis_password: 'redis.meta'
redis_mode: sentinel
redis_max_memory: 256MB
redis_sentinel_monitor: # primary list for redis sentinel, use cls as name, primary ip:port
- { name: redis-src, host: 10.10.10.45, port: 6379 ,password: redis.src, quorum: 1 }
- { name: redis-dst, host: 10.10.10.48, port: 6379 ,password: redis.dst, quorum: 1 }
#==========================================================#
# redis-src: reuse pg-src 3 nodes for redis
#==========================================================#
# ./redis.yml -l redis-src
redis-src:
hosts:
10.10.10.45: { redis_node: 1 , redis_instances: {6379: { } }}
10.10.10.46: { redis_node: 2 , redis_instances: {6379: { replica_of: '10.10.10.45 6379' }, 6380: { replica_of: '10.10.10.46 6379' } }}
10.10.10.47: { redis_node: 3 , redis_instances: {6379: { replica_of: '10.10.10.45 6379' }, 6380: { replica_of: '10.10.10.47 6379' } }}
vars:
redis_cluster: redis-src
redis_password: 'redis.src'
redis_max_memory: 64MB
#==========================================================#
# redis-dst: reuse pg-dst 2 nodes for redis
#==========================================================#
# ./redis.yml -l redis-dst
redis-dst:
hosts:
10.10.10.48: { redis_node: 1 , redis_instances: {6379: { } }}
10.10.10.49: { redis_node: 2 , redis_instances: {6379: { replica_of: '10.10.10.48 6379' } }}
vars:
redis_cluster: redis-dst
redis_password: 'redis.dst'
redis_max_memory: 64MB
#==========================================================#
# redis-test: redis native cluster in 6 nodes, 24 instances
#==========================================================#
# ./node.yml -l redis-test; ./redis.yml -l redis-test
redis-test:
hosts:
10.10.10.81: { redis_node: 1 ,redis_instances: { 6379: {} ,6380: {} ,6381: {} } }
10.10.10.82: { redis_node: 2 ,redis_instances: { 6379: {} ,6380: {} ,6381: {} } }
10.10.10.83: { redis_node: 3 ,redis_instances: { 6379: {} ,6380: {} ,6381: {} } }
10.10.10.84: { redis_node: 4 ,redis_instances: { 6379: {} ,6380: {} ,6381: {} } }
10.10.10.85: { redis_node: 5 ,redis_instances: { 6379: {} ,6380: {} ,6381: {} } }
10.10.10.86: { redis_node: 6 ,redis_instances: { 6379: {} ,6380: {} ,6381: {} } }
vars:
redis_cluster: redis-test
redis_password: 'redis.test'
redis_mode: cluster
redis_max_memory: 512MB
node_cluster: redis-test
#==========================================================#
# ferret: reuse pg-src as mongo (ferretdb)
#==========================================================#
# ./mongo.yml -l ferret
ferret:
hosts:
10.10.10.45: { mongo_seq: 1 }
10.10.10.46: { mongo_seq: 2 }
10.10.10.47: { mongo_seq: 3 }
vars:
mongo_cluster: ferret
mongo_pgurl: 'postgres://test:[email protected]:5432/src'
#mongo_pgurl: 'postgres://test:[email protected]:5436/test'
#==========================================================#
# test: running cli tools and test miscellaneous stuff
#==========================================================#
test:
hosts: { 10.10.10.88: { nodename: test } }
vars:
node_cluster: test
node_packages: [ 'etcd,logcli,mcli,redis' ]
#============================================================#
# Global Variables
#============================================================#
vars:
#==========================================================#
# INFRA
#==========================================================#
version: v3.0.4 # pigsty version string
admin_ip: 10.10.10.10 # admin node ip address
region: default # upstream mirror region: default|china|europe
infra_portal: # domain names and upstream servers
home : { domain: h.pigsty }
grafana : { domain: g.pigsty ,endpoint: "10.10.10.10:3000" , websocket: true }
prometheus : { domain: p.pigsty ,endpoint: "10.10.10.10:9090" }
alertmanager : { domain: a.pigsty ,endpoint: "10.10.10.10:9093" }
blackbox : { endpoint: "10.10.10.10:9115" }
loki : { endpoint: "10.10.10.10:3100" }
minio : { domain: sss.pigsty ,endpoint: "10.10.10.26:9001" ,scheme: https ,websocket: true }
postgrest : { domain: api.pigsty ,endpoint: "127.0.0.1:8884" }
pgadmin : { domain: adm.pigsty ,endpoint: "127.0.0.1:8885" }
pgweb : { domain: cli.pigsty ,endpoint: "127.0.0.1:8886" }
bytebase : { domain: ddl.pigsty ,endpoint: "127.0.0.1:8887" }
jupyter : { domain: lab.pigsty ,endpoint: "127.0.0.1:8888" , websocket: true }
supa : { domain: supa.pigsty ,endpoint: "10.10.10.10:8000", websocket: true }
nginx_navbar: []
dns_records: # dynamic dns records resolved by dnsmasq
- 10.10.10.1 h.pigsty a.pigsty p.pigsty g.pigsty
#==========================================================#
# NODE
#==========================================================#
node_conf: tiny # use small vm tune template
node_timezone: Asia/Hong_Kong # use Asia/Hong_Kong Timezone
node_dns_servers: # DNS servers in /etc/resolv.conf
- 10.10.10.10
- 10.10.10.11
node_etc_hosts:
- 10.10.10.10 h.pigsty a.pigsty p.pigsty g.pigsty
- 10.10.10.26 sss.pigsty
node_ntp_servers: # NTP servers in /etc/chrony.conf
- pool cn.pool.ntp.org iburst
- pool 10.10.10.10 iburst
node_admin_ssh_exchange: false # exchange admin ssh key among node cluster
#==========================================================#
# PGSQL
#==========================================================#
pg_conf: tiny.yml
pgbackrest_method: minio # USE THE HA MINIO THROUGH A LOAD BALANCER
pg_dbsu_ssh_exchange: false # do not exchange dbsu ssh key among pgsql cluster
pgbackrest_repo: # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
local: # default pgbackrest repo with local posix fs
path: /pg/backup # local backup directory, `/pg/backup` by default
retention_full_type: count # retention full backups by count
retention_full: 2 # keep 2, at most 3 full backup when using local fs repo
minio:
type: s3
s3_endpoint: sss.pigsty # s3_endpoint could be any load balancer: 10.10.10.1{0,1,2}, or domain names point to any of the 3 nodes
s3_region: us-east-1 # you could use external domain name: sss.pigsty , which resolve to any members (`minio_domain`)
s3_bucket: pgsql # instance & nodename can be used : minio-1.pigsty minio-1.pigsty minio-1.pigsty minio-1 minio-2 minio-3
s3_key: pgbackrest # Better using a new password for MinIO pgbackrest user
s3_key_secret: S3User.Backup
s3_uri_style: path
path: /pgbackrest
storage_port: 9002 # Use the load balancer port 9002 instead of default 9000 (direct access)
storage_ca_file: /etc/pki/ca.crt
bundle: y
cipher_type: aes-256-cbc # Better using a new cipher password for your production environment
cipher_pass: pgBackRest.${pg_cluster}
retention_full_type: time
retention_full: 14
...