数据库

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: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: postgis, schema: public}, {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 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 }

- name: meta                      # 必选，`name` 是数据库定义的唯一必选字段
  state: create                   # 可选，数据库状态：create（创建，默认）、absent（删除）、recreate（重建）
  baseline: cmdb.sql              # 可选，数据库 sql 的基线定义文件路径（ansible 搜索路径中的相对路径，如 files/）
  pgbouncer: true                 # 可选，是否将此数据库添加到 pgbouncer 数据库列表？默认为 true
  schemas: [pigsty]               # 可选，要创建的附加模式，由模式名称字符串组成的数组
  extensions:                     # 可选，要安装的附加扩展： 扩展对象的数组
    - { name: postgis , schema: public }  # 可以指定将扩展安装到某个模式中，也可以不指定（不指定则安装到 search_path 首位模式中）
    - { name: timescaledb }               # 例如有的扩展会创建并使用固定的模式，就不需要指定模式。
  comment: pigsty meta database   # 可选，数据库的说明与备注信息
  owner: postgres                 # 可选，数据库所有者，不指定则为当前用户
  template: template1             # 可选，要使用的模板，默认为 template1，目标必须是一个模板数据库
  strategy: FILE_COPY             # 可选，克隆策略：FILE_COPY 或 WAL_LOG（PG15+），不指定使用 PG 默认
  encoding: UTF8                  # 可选，不指定则继承模板/集群配置（UTF8）
  locale: C                       # 可选，不指定则继承模板/集群配置（C）
  lc_collate: C                   # 可选，不指定则继承模板/集群配置（C）
  lc_ctype: C                     # 可选，不指定则继承模板/集群配置（C）
  locale_provider: libc           # 可选，本地化提供者：libc、icu、builtin（PG15+）
  icu_locale: en-US               # 可选，ICU 本地化规则（PG15+）
  icu_rules: ''                   # 可选，ICU 排序规则（PG16+）
  builtin_locale: C.UTF-8         # 可选，内置本地化提供者规则（PG17+）
  tablespace: pg_default          # 可选，默认表空间，默认为 'pg_default'
  is_template: false              # 可选，是否标记为模板数据库，允许任何有 CREATEDB 权限的用户克隆
  allowconn: true                 # 可选，是否允许连接，默认为 true。显式设置 false 将完全禁止连接到此数据库
  revokeconn: false               # 可选，撤销公共连接权限。默认为 false，设置为 true 时，属主和管理员之外用户的 CONNECT 权限会被回收
  register_datasource: true       # 可选，是否将此数据库注册到 grafana 数据源？默认为 true，显式设置为 false 会跳过注册
  connlimit: -1                   # 可选，数据库连接限制，默认为 -1 ，不限制，设置为正整数则会限制连接数。
  parameters:                     # 可选，数据库级参数，通过 ALTER DATABASE SET 设置
    work_mem: '64MB'
    statement_timeout: '30s'
  pool_auth_user: dbuser_meta     # 可选，连接到此 pgbouncer 数据库的所有连接都将使用此用户进行验证（启用 pgbouncer_auth_query 才有用）
  pool_mode: transaction          # 可选，数据库级别的 pgbouncer 池化模式，默认为 transaction
  pool_size: 64                   # 可选，数据库级别的 pgbouncer 默认池子大小，默认为 64
  pool_reserve: 32                # 可选，数据库级别的 pgbouncer 池子保留空间，默认为 32，当默认池子不够用时，最多再申请这么多条突发连接。
  pool_size_min: 0                # 可选，数据库级别的 pgbouncer 池的最小大小，默认为 0
  pool_connlimit: 100             # 可选，数据库级别的最大数据库连接数，默认为 100

- name: myapp              # 简单命名
- name: my_application     # 下划线分隔
- name: app_v2             # 包含版本号

- name: myapp                # state 默认为 create
- name: olddb
  state: absent              # 删除数据库
- name: testdb
  state: recreate            # 重建数据库

ALTER DATABASE "myapp" OWNER TO "new_owner";
GRANT ALL PRIVILEGES ON DATABASE "myapp" TO "new_owner";

COMMENT ON DATABASE "myapp" IS '我的应用主数据库';

- name: myapp
  comment: 我的应用主数据库

- name: myapp_icu
  template: template0        # 使用 ICU 时必须指定 template0
  locale_provider: icu
  icu_locale: zh-Hans

- name: cloned_db
  template: source_db
  strategy: WAL_LOG          # 使用 WAL 日志方式克隆

- name: legacy_db
  template: template0        # 指定非默认编码时使用 template0
  encoding: LATIN1

- name: chinese_db
  template: template0
  locale: zh_CN.UTF-8        # 中文本地化
  encoding: UTF8

- name: myapp
  template: template0
  lc_collate: en_US.UTF-8    # 英文排序规则
  lc_ctype: en_US.UTF-8

- name: fast_db
  template: template0
  locale_provider: builtin   # 使用内置提供者，最高效
  builtin_locale: C.UTF-8

- name: chinese_app
  template: template0
  locale_provider: icu
  icu_locale: zh-Hans        # 简体中文 ICU 排序
  encoding: UTF8

- name: custom_sort_db
  template: template0
  locale_provider: icu
  icu_locale: en-US
  icu_rules: '&V << w <<< W'  # 自定义 V/W 排序顺序

- name: fast_db
  template: template0
  locale_provider: builtin
  builtin_locale: C.UTF-8    # 内置 UTF-8 支持
  encoding: UTF8

- name: archive_db
  tablespace: slow_hdd       # 归档数据使用慢速存储

ALTER DATABASE "archive_db" SET TABLESPACE "slow_hdd";

- name: app_template
  is_template: true          # 标记为模板，允许普通用户克隆
  schemas: [core, api]
  extensions: [postgis, pg_trgm]

- name: archive_db
  allowconn: false           # 禁止任何连接

ALTER DATABASE "archive_db" ALLOW_CONNECTIONS false;

- name: secure_db
  owner: dbuser_secure
  revokeconn: true           # 回收公共连接权限，只有指定用户可连接

- name: limited_db
  connlimit: 50              # 最多允许 50 个并发连接

ALTER DATABASE "limited_db" CONNECTION LIMIT 50;

- name: myapp
  baseline: myapp_schema.sql  # 会查找 files/myapp_schema.sql

schemas:
  - app
  - api
  - core

schemas:
  - name: app                # 模式名（必选）
    owner: dbuser_app        # 模式属主（可选），生成 AUTHORIZATION 子句
  - name: deprecated
    state: absent            # 删除模式（使用 CASCADE）

CREATE SCHEMA IF NOT EXISTS "app" AUTHORIZATION "dbuser_app";
DROP SCHEMA IF EXISTS "deprecated" CASCADE;

extensions:
  - postgis
  - pg_trgm
  - vector

extensions:
  - name: vector             # 扩展名（必选）
    schema: public           # 安装到指定模式（可选）
    version: '0.5.1'         # 指定版本（可选）
  - name: old_extension
    state: absent            # 卸载扩展（使用 CASCADE）

CREATE EXTENSION IF NOT EXISTS "vector" WITH SCHEMA "public" VERSION '0.5.1' CASCADE;
DROP EXTENSION IF EXISTS "old_extension" CASCADE;

- name: analytics
  parameters:
    work_mem: '256MB'
    maintenance_work_mem: '512MB'
    statement_timeout: '5min'
    search_path: 'analytics,public'

parameters:
  work_mem: DEFAULT          # 重置为默认值
  statement_timeout: '30s'   # 设置新值

ALTER DATABASE "myapp" SET "work_mem" = DEFAULT;
ALTER DATABASE "myapp" SET "statement_timeout" = '30s';

- name: internal_db
  pgbouncer: false           # 不通过连接池访问

- name: session_app
  pool_mode: session         # 使用会话级池化

- name: high_load_db
  pool_size: 128             # 高负载应用使用更大的池

- name: latency_sensitive
  pool_size_min: 10          # 预热 10 个连接

- name: bursty_db
  pool_size: 64
  pool_reserve: 64           # 允许突发到 128 个连接

- name: limited_pool_db
  pool_connlimit: 50         # 连接池最多 50 个连接

- name: myapp
  pool_auth_user: dbuser_monitor  # 使用监控用户执行认证查询

- name: temp_db
  register_datasource: false  # 不注册到 Grafana

- name: myapp_icu
  template: template0        # ICU 必须使用 template0
  locale_provider: icu
  icu_locale: en-US          # ICU 本地化规则
  encoding: UTF8

- name: myapp_builtin
  template: template0
  locale_provider: builtin
  builtin_locale: C.UTF-8    # 内置本地化规则
  encoding: UTF8

meta                        = host=/var/run/postgresql mode=session
grafana                     = host=/var/run/postgresql mode=transaction
bytebase                    = host=/var/run/postgresql auth_user=dbuser_meta
kong                        = host=/var/run/postgresql pool_size=32 reserve_pool=64
gitea                       = host=/var/run/postgresql min_pool_size=10
wiki                        = host=/var/run/postgresql
noco                        = host=/var/run/postgresql
mongo                       = host=/var/run/postgresql