配置示例

各类集群的声明式配置示例。

本节提供常见场景的配置示例，帮助您快速上手声明式配置。

单节点部署

最简单的配置，单节点运行所有组件：

all:
  children:
    infra:
      hosts: { 10.10.10.10: { infra_seq: 1 } }
    etcd:
      hosts: { 10.10.10.10: { etcd_seq: 1 } }
    pg-meta:
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-meta }

高可用集群

三节点 HA 集群

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 }
  vars:
    pg_cluster: pg-test
    pg_version: 17

    # 高可用配置
    pg_conf: crit.yml            # 关键业务模板，同步复制

    # 业务数据库
    pg_databases:
      - name: testdb
        owner: testuser

    pg_users:
      - name: testuser
        password: DBUser.Test
        roles: [dbrole_readwrite]

同步复制配置

pg-sync:
  hosts:
    10.10.10.21: { pg_seq: 1, pg_role: primary }
    10.10.10.22: { pg_seq: 2, pg_role: replica }
    10.10.10.23: { pg_seq: 3, pg_role: replica }
  vars:
    pg_cluster: pg-sync

    # 同步复制：至少一个同步从库
    synchronous_mode: true
    synchronous_node_count: 1

读写分离

使用服务定义

pg-rw:
  hosts:
    10.10.10.31: { pg_seq: 1, pg_role: primary }
    10.10.10.32: { pg_seq: 2, pg_role: replica }
    10.10.10.33: { pg_seq: 3, pg_role: replica }
  vars:
    pg_cluster: pg-rw

    # 服务定义
    pg_default_services:
      - name: primary    # 读写服务
        port: 5433
        dest: primary

      - name: replica    # 只读服务
        port: 5434
        dest: replica
        selector: "[]"   # 所有从库

      - name: offline    # 离线查询
        port: 5436
        dest: offline
        selector: "[? pg_role == `offline` || pg_offline_query ]"

离线查询实例

pg-analytics:
  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
      pg_offline_query: true    # 可用于离线查询
      pg_weight: 0              # 不承载在线流量
  vars:
    pg_cluster: pg-analytics

OLTP 与 OLAP

OLTP 集群

pg-oltp:
  hosts:
    10.10.10.51: { pg_seq: 1, pg_role: primary }
    10.10.10.52: { pg_seq: 2, pg_role: replica }
  vars:
    pg_cluster: pg-oltp
    pg_conf: oltp.yml           # OLTP 模板

    # 连接池优化
    pgbouncer_poolmode: transaction
    pg_default_hba_rules:
      - { user: all, db: all, addr: intra, auth: pwd, title: 'allow intranet access' }

OLAP 集群

pg-olap:
  hosts:
    10.10.10.61: { pg_seq: 1, pg_role: primary }
    10.10.10.62: { pg_seq: 2, pg_role: replica }
  vars:
    pg_cluster: pg-olap
    pg_conf: olap.yml           # OLAP 模板

    # 分析扩展
    pg_libs: 'pg_stat_statements, auto_explain, pg_analytics'
    pg_extensions:
      - pg_analytics
      - duckdb_fdw

扩展配置

PostGIS 地理空间

pg-gis:
  hosts:
    10.10.10.71: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-gis

    pg_databases:
      - name: gisdb
        extensions:
          - postgis
          - postgis_topology
          - postgis_raster
          - pgrouting

    pg_extensions:
      - postgis35
      - pgrouting

向量搜索

pg-vector:
  hosts:
    10.10.10.81: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-vector

    pg_databases:
      - name: vectordb
        extensions:
          - vector
          - pg_search

    pg_extensions:
      - pgvector
      - pg_search

时序数据

pg-timeseries:
  hosts:
    10.10.10.91: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-timeseries

    pg_databases:
      - name: tsdb
        extensions:
          - timescaledb

    pg_extensions:
      - timescaledb
    pg_libs: 'timescaledb, pg_stat_statements'

多集群部署

完整生产环境

all:
  vars:
    version: v3.3.0
    admin_ip: 10.10.10.10
    pg_version: 17

  children:
    # 基础设施
    infra:
      hosts:
        10.10.10.10: { infra_seq: 1 }
        10.10.10.11: { infra_seq: 2 }
      vars:
        node_cluster: infra

    # ETCD 集群
    etcd:
      hosts:
        10.10.10.10: { etcd_seq: 1 }
        10.10.10.11: { etcd_seq: 2 }
        10.10.10.12: { etcd_seq: 3 }
      vars:
        etcd_cluster: etcd

    # 元数据库
    pg-meta:
      hosts:
        10.10.10.10: { pg_seq: 1, pg_role: primary }
      vars:
        pg_cluster: pg-meta
        pg_databases:
          - name: meta

    # 用户服务数据库
    pg-user:
      hosts:
        10.10.10.21: { pg_seq: 1, pg_role: primary }
        10.10.10.22: { pg_seq: 2, pg_role: replica }
        10.10.10.23: { pg_seq: 3, pg_role: replica }
      vars:
        pg_cluster: pg-user
        pg_conf: crit.yml
        pg_databases:
          - name: userdb

    # 订单服务数据库
    pg-order:
      hosts:
        10.10.10.31: { pg_seq: 1, pg_role: primary }
        10.10.10.32: { pg_seq: 2, pg_role: replica }
      vars:
        pg_cluster: pg-order
        pg_databases:
          - name: orderdb

备份配置

本地备份

pg-local-backup:
  hosts:
    10.10.10.101: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-local-backup

    pgbackrest_enabled: true
    pgbackrest_repo:
      local:
        path: /pg/backup
        retention_full: 2
        retention_full_type: count

MinIO 备份

pg-minio-backup:
  hosts:
    10.10.10.111: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-minio-backup

    pgbackrest_enabled: true
    pgbackrest_repo:
      minio:
        type: s3
        s3_endpoint: minio.pigsty
        s3_bucket: pgsql
        s3_key: pgbackrest
        s3_key_secret: S3User.Backup
        retention_full: 7
        retention_full_type: time

安全配置

严格安全模式

pg-secure:
  hosts:
    10.10.10.121: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-secure

    # SSL 强制
    pg_ssl_enabled: true
    pg_ssl_mode: require

    # 强密码策略
    pg_pwd_enc: scram-sha-256

    # 严格 HBA
    pg_default_hba_rules:
      - { user: dbsu,      db: all, addr: local,     auth: ident, title: 'dbsu via local ident' }
      - { user: replicator, db: replication, addr: intra, auth: pwd, title: 'replication' }
      - { user: all,       db: all, addr: intra,     auth: ssl,   title: 'intranet ssl only' }

配置模板

Pigsty 内置多种配置模板，位于 conf/ 目录：

模板 说明
meta.yml 单节点元数据库
demo.yml 四节点演示环境
prod.yml 生产环境模板
full.yml 完整参数示例
dual.yml 双节点 HA
trio.yml 三节点 HA

使用模板：

# 复制模板
cp conf/prod.yml pigsty.yml

# 根据实际环境修改
vim pigsty.yml