# 执行一系列命令
yum install postgresql
systemctl start postgresql
createdb mydb
createuser myuser

# 声明期望状态
pg_databases:
  - name: mydb
pg_users:
  - name: myuser

# 第一次执行：创建集群
./pgsql.yml -l pg-test

# 第二次执行：检查并调整到期望状态（无变化则不操作）
./pgsql.yml -l pg-test

# 结果相同：pg-test 集群处于期望状态

all:
  vars:
    pg_version: 16              # 全局默认：PG 16

  children:
    pg-oltp:
      vars:
        pg_conf: oltp.yml       # 集群级别：OLTP 模板
      hosts:
        10.10.10.11:
          pg_seq: 1
          pg_role: primary
        10.10.10.12:
          pg_seq: 2
          pg_role: replica
          pg_conf: tiny.yml     # 实例级别：覆盖为 tiny 模板

./pgsql.yml     # PostgreSQL 模块剧本
./infra.yml     # 基础设施模块剧本
./node.yml      # 节点模块剧本
./etcd.yml      # ETCD 模块剧本

# 初始化 Git 仓库
cd ~/pigsty
git init
git add pigsty.yml
git commit -m "Initial configuration"

# 记录每次变更
git add pigsty.yml
git commit -m "Add pg-test cluster"

# 语法检查
ansible-playbook pgsql.yml --syntax-check

# 干运行（不实际执行）
ansible-playbook pgsql.yml --check

# 差异对比
ansible-playbook pgsql.yml --diff

---
# 全局配置
all:
  vars:
    # 全局变量...

  children:
    # 基础设施集群
    infra:
      hosts: { ... }
      vars: { ... }

    # ETCD 集群
    etcd:
      hosts: { ... }
      vars: { ... }

    # PostgreSQL 集群
    pg-meta:
      hosts: { ... }
      vars: { ... }

    pg-test:
      hosts: { ... }
      vars: { ... }
...

all:
  vars:
    # 版本与区域
    version: v3.3.0
    region: default

    # 基础设施
    admin_ip: 10.10.10.10

    # PostgreSQL 默认配置
    pg_version: 17
    pg_conf: oltp.yml

pg-test:
  vars:
    pg_cluster: pg-test
    pg_version: 16              # 覆盖全局版本
    pg_conf: crit.yml           # 使用关键业务模板
    pg_databases:
      - name: testdb
    pg_users:
      - name: testuser

pg-test:
  hosts:
    10.10.10.11:
      pg_seq: 1
      pg_role: primary
    10.10.10.12:
      pg_seq: 2
      pg_role: replica
      pg_conf: tiny.yml         # 此实例使用 tiny 模板

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_databases:
      - name: testdb
        owner: testuser
        extensions:
          - postgis
          - pg_stat_statements

    # 用户定义
    pg_users:
      - name: testuser
        password: DBUser.Test
        roles: [dbrole_readwrite]

    # 连接池
    pg_default_services:
      - { name: primary, port: 5433, dest: primary }
      - { name: replica, port: 5434, dest: replica }

all:
  vars:
    pg_version: 17              # 全局默认 PG 17

  children:
    pg-legacy:
      vars:
        pg_version: 15          # 此集群使用 PG 15
      hosts:
        10.10.10.21:
          pg_seq: 1
          pg_role: primary
          pg_version: 14        # 此实例使用 PG 14（特殊需求）

# 方式一：使用软链接
ln -sf conf/prod.yml pigsty.yml

# 方式二：使用 -i 参数
./pgsql.yml -i conf/prod.yml -l pg-test

# YAML 语法检查
python3 -c "import yaml; yaml.safe_load(open('pigsty.yml'))"

# Ansible 语法检查
ansible-playbook pgsql.yml --syntax-check

# 查看特定主机的变量
ansible -i pigsty.yml 10.10.10.11 -m debug -a "var=hostvars[inventory_hostname]"

# 查看集群变量
ansible -i pigsty.yml pg-test -m debug -a "var=group_vars"

# 检查模式（不实际执行）
./pgsql.yml -l pg-test --check

# 差异对比
./pgsql.yml -l pg-test --diff

# 集群名：模块-用途
pg-meta          # PostgreSQL 元数据库
pg-order         # 订单服务数据库
redis-cache      # Redis 缓存集群

# 实例名：集群名-序号
pg-meta-1        # 集群 pg-meta 的第 1 个实例
pg-order-2       # 集群 pg-order 的第 2 个实例

# 初始化 Git
cd ~/pigsty
git init
git add pigsty.yml
git commit -m "Initial configuration"

# 每次变更都提交
git add pigsty.yml
git commit -m "Add pg-order cluster"

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 }

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

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

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

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

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

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

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