demo/kernel

Ten-node PostgreSQL kernel matrix demo configuration

The demo/kernel configuration template demonstrates the major PostgreSQL kernels and compatible branches supported by Pigsty in a single configuration. It is intended for feature validation and kernel difference testing, not production use.

Overview

  • Config Name: demo/kernel
  • Node Count: 10 nodes, with one node also hosting INFRA/ETCD and pg-citus
  • Description: PostgreSQL kernel matrix demo covering Citus, IvorySQL, Babelfish, PolarDB, Percona TDE, OrioleDB, OpenHalo, DocumentDB/FerretDB, AgensGraph, and pgEdge
  • OS Distro: depends on actual package support for each kernel
  • OS Arch: depends on actual package support for each kernel
  • Related: pgsql, mssql, mongo

Usage:

./configure -c demo/kernel

Note: This is a fixed-IP demo template. Adjust node addresses for your actual environment after generation.

Content

Source: pigsty/conf/demo/kernel.yml

---
#==============================================================#
# File      :   kernel.yml
# Desc      :   Pigsty 10-node kernel matrix demo
# Ctime     :   2025-03-25
# Mtime     :   2026-02-26
# Docs      :   https://pigsty.io/docs/conf
# License   :   Apache-2.0 @ https://pigsty.io/docs/about/license/
# Copyright :   2018-2026  Ruohang Feng / Vonng ([email protected])
#==============================================================#


all:
  children:
    infra: { hosts: { 10.10.10.10: { infra_seq: 1 } }, vars: { repo_enabled: false } }
    etcd:  { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }

    # 1. Vanilla PostgreSQL + Citus in one kernel template
    pg-citus:
      hosts:
        10.10.10.10: { pg_seq: 1, pg_role: primary }
      vars:
        pg_cluster: pg-citus
        pg_version: 18
        pg_packages: [ pgsql-main, pgsql-common, citus ]
        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  }
        pg_databases:
          - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [citus, postgis, timescaledb, vector] }
        pg_extensions: [ citus, postgis, timescaledb, pgvector ]
        pg_libs: 'citus, pg_stat_statements, auto_explain'

    # 2. IvorySQL kernel
    pg-ivory:
      hosts:
        10.10.10.11: { pg_seq: 1, pg_role: primary }
      vars:
        pg_mode: ivory
        pg_cluster: pg-ivory
        pg_version: 18
        pg_packages: [ ivorysql, pgsql-common ]
        pg_libs: 'liboracle_parser, pg_stat_statements, auto_explain'
        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  }
        pg_databases:
          - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] }

    # 3. Babelfish (MSSQL compatible) kernel
    pg-mssql:
      hosts:
        10.10.10.12: { pg_seq: 1, pg_role: primary }
      vars:
        pg_mode: mssql
        pg_cluster: pg-mssql
        pg_version: 17
        pg_packages: [ babelfish, pgsql-common, sqlcmd ]
        pg_users:
          - { 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
            extensions: [ uuid-ossp, babelfishpg_common, babelfishpg_tsql, babelfishpg_tds, babelfishpg_money ]
            owner: dbuser_mssql
            parameters: { 'babelfishpg_tsql.migration_mode' : 'multi-db' }
            comment: babelfish cluster, a MSSQL compatible pg cluster
        pg_libs: 'babelfishpg_tds, pg_stat_statements, auto_explain'
        pg_hba_rules:
          - { user: dbuser_mssql ,db: mssql ,addr: intra ,auth: md5 ,title: 'allow mssql dbsu intranet access'      ,order: 525 }
          - { user: all          ,db: all   ,addr: intra ,auth: md5 ,title: 'everyone intranet access with md5 pwd' ,order: 800 }
        pg_default_services:
          - { 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]" }

    # 4. PolarDB kernel
    pg-polar:
      hosts:
        10.10.10.13: { pg_seq: 1, pg_role: primary }
      vars:
        pg_mode: polar
        pg_cluster: pg-polar
        pg_version: 17
        pg_packages: [ polardb, pgsql-common ]
        pg_exporter_exclude_database: 'template0,template1,postgres,polardb_admin'
        pg_default_roles:
          - { 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 }
          - { 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 }

    # 5. percona tde kernel
    pg-tde:
      hosts:
        10.10.10.14: { pg_seq: 1, pg_role: primary }
      vars:
        node_repo_modules: node,infra,pgsql,percona
        pg_cluster: pg-tde
        pg_version: 18
        pg_packages: [ percona-main, pgsql-common ]
        pg_libs: 'pg_tde, pgaudit, pg_stat_statements, pg_stat_monitor, auto_explain'
        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  }
        pg_databases:
          - name: meta
            baseline: cmdb.sql
            comment: pigsty tde database
            schemas: [pigsty]
            extensions: [ vector, postgis, pg_tde ,pgaudit, { name: pg_stat_monitor, schema: monitor } ]

    # 6. OrioleDB kernel
    pg-oriole:
      hosts:
        10.10.10.15: { pg_seq: 1, pg_role: primary }
      vars:
        pg_mode: oriole
        pg_cluster: pg-oriole
        pg_version: 18        # OrioleDB supports PG 16, 17, and 18
        pg_packages: [ oriole, pgsql-common ]
        pg_libs: 'orioledb, pg_stat_statements, auto_explain'
        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  }
        pg_databases:
          - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [orioledb] }

    # 7. OpenHaloDB (MySQL compatible) kernel
    pg-mysql:
      hosts:
        10.10.10.16: { pg_seq: 1, pg_role: primary }
      vars:
        pg_mode: mysql
        pg_cluster: pg-mysql
        pg_version: 14
        pg_packages: [ openhalo, pgsql-common ]
        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  }
        pg_databases:
          - { name: postgres ,extensions: [aux_mysql] }
          - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] }

    # 8. DocumentDB + MongoDB (FerretDB) kernel
    pg-mongo:
      hosts:
        10.10.10.17: { pg_seq: 1, pg_role: primary ,mongo_seq: 1 }
      vars:
        pg_cluster: pg-mongo
        pg_version: 18
        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  }
        pg_databases:
          - { name: postgres ,extensions: [documentdb, postgis, vector, pg_cron, rum] }
        pg_hba_rules:
          - { user: dbuser_view ,db: all ,addr: infra     ,auth: pwd   ,title: 'allow grafana dashboard access cmdb from infra nodes' }
          - { user: postgres    ,db: all ,addr: world     ,auth: pwd   ,title: 'dbsu password access everywhere (demo only)' }
          - { user: all         ,db: all ,addr: localhost ,order: 1    ,auth: trust ,title: 'documentdb localhost trust access' }
          - { user: all         ,db: all ,addr: local     ,order: 1    ,auth: trust ,title: 'documentdb local trust access' }
          - { user: all         ,db: all ,addr: intra     ,auth: pwd   ,order: 800  ,title: 'everyone intranet access with password' }
        pg_parameters: { cron.database_name: postgres }
        pg_extensions: [ documentdb, postgis, pgvector, pg_cron, rum ]
        pg_libs: 'pg_documentdb, pg_documentdb_core, pg_documentdb_extended_rum, pg_cron, pg_stat_statements, auto_explain'
        mongo_cluster: ferret
        mongo_pgurl: 'postgres://dbuser_dba:[email protected]:5432/postgres'

    # 9. AgensGraph kernel
    pg-agens:
      hosts:
        10.10.10.18: { pg_seq: 1, pg_role: primary }
      vars:
        pg_mode: agens
        pg_cluster: pg-agens
        pg_version: 16
        pg_packages: [ agensgraph, pgsql-common ]
        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  }
        pg_databases:
          - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] }

    # 10. pgedge kernel (stock pigsty pgsql repo path)
    pg-edge:
      hosts:
        10.10.10.19: { pg_seq: 1, pg_role: primary }
      vars:
        pg_mode: pgedge
        pg_cluster: pg-edge
        pg_version: 18
        pg_packages: [ pgedge, pgsql-common ]
        pg_extensions: [ spock, snowflake, lolor ]
        pg_libs: 'spock, lolor, pg_stat_statements, auto_explain'
        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  }
        pg_databases:
          - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [spock, snowflake, lolor] }

  vars:
    version: v4.3.0                   # pigsty version string
    admin_ip: 10.10.10.10             # admin node ip address
    region: default                   # upstream mirror region: default|china|europe
    node_tune: oltp                   # node tuning specs: oltp,olap,tiny,crit
    pg_conf: oltp.yml                 # pgsql tuning specs: {oltp,olap,tiny,crit}.yml
    node_repo_modules: node,infra,pgsql
    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"
    infra_portal:
      home : { domain: i.pigsty }

    grafana_admin_password: pigsty
    grafana_view_password: DBUser.Viewer
    pg_admin_password: DBUser.DBA
    pg_monitor_password: DBUser.Monitor
    pg_replication_password: DBUser.Replicator
    patroni_password: Patroni.API
    haproxy_admin_password: pigsty
    minio_secret_key: S3User.MinIO
    etcd_root_password: Etcd.Root
...

Explanation

This template uses single-node clusters to show the minimum viable configuration for different kernels:

  • pg-citus: PostgreSQL 18 + Citus
  • pg-ivory: IvorySQL, compatible with PostgreSQL 18
  • pg-mssql: Babelfish, compatible with PostgreSQL 17
  • pg-polar: PolarDB for PostgreSQL, compatible with PostgreSQL 17
  • pg-tde: Percona PostgreSQL 18 + pg_tde
  • pg-oriole: OrioleDB, supports PostgreSQL 16, 17, and 18; the current demo config defaults to PG18
  • pg-mysql: OpenHalo, compatible with PostgreSQL 14
  • pg-mongo: DocumentDB + FerretDB, default PostgreSQL 18
  • pg-agens: AgensGraph, compatible with PostgreSQL 16
  • pg-edge: pgEdge, compatible with PostgreSQL 18

Notes:

  • Package support varies by kernel, OS, and architecture. Confirm the target repository is available before deployment.
  • This template includes permissive access rules for demo use. For production, use a dedicated kernel template and tighten HBA and password policies.

Last Modified 2026-07-01: calibrate conf docs (bc26b8c)