PG Kernels

• 1: PostgreSQL
• 2: Supabase
• 3: Babelfish
• 4: Percona
• 5: openHalo
• 6: OrioleDB
• 7: Cloudberry
• 8: AgensGraph
• 9: pgEdge
• 10: DocumentDB
• 11: Citus
• 12: IvorySQL
• 13: PolarDB PG
• 14: PolarDB Oracle
• 15: PostgresML
• 16: Greenplum
• 17: Neon

In Pigsty, you can replace the native PostgreSQL kernel with different PostgreSQL “flavors” to unlock specialized capabilities.

Pigsty supports multiple PostgreSQL kernels and compatibility branches so you can get compatibility layers, multi-master replication, graph queries, MPP warehousing, transparent encryption, and more inside one operational framework.

One thing to keep in mind is that not every kernel has the same delivery depth in Pigsty: PostgreSQL, Citus, Babelfish, IvorySQL, PolarDB, AgensGraph, and pgEdge already have relatively clear templates and configuration paths; Cloudberry and Greenplum, by contrast, are more often managed through gpsql mode, and their MPP initialization plus scale-out operations are still better handled with upstream tooling.

Kernel	Key Feature	Description
PostgreSQL	Native kernel, full extension set	Vanilla PostgreSQL with 575 extensions
Supabase	Backend as a Service	PostgreSQL-based BaaS, Firebase alternative
Citus	Horizontal scaling	Distributed PostgreSQL via native extension
Babelfish	SQL Server compatible	SQL Server wire-protocol compatibility (PG17/18)
IvorySQL	Oracle compatible	Oracle syntax and PL/SQL compatibility
OpenHalo	MySQL compatible	MySQL wire-protocol compatibility
Percona	Transparent data encryption	Percona distribution with pg_tde
DocumentDB	MongoDB migration	DocumentDB + FerretDB wire compatibility
OrioleDB	OLTP optimization	Zheap, no bloat, S3 storage
PolarDB	Aurora-style RAC	RAC, China-local compliance scenario
Cloudberry	Open-source MPP warehouse	Cloudberry integrated through gpsql mode
AgensGraph	Property graph + Cypher	Graph query capability inside PostgreSQL
pgEdge	Spock multi-master replication	Distributed PostgreSQL for edge scenarios

Versions

Kernel	Debian / Ubuntu	EL
PostgreSQL / Citus	PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg26.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit	PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1 20251022 (Red Hat 14.3.1-4), 64-bit
IvorySQL	PostgreSQL 18.4 (IvorySQL 5.4) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.5.0, 64-bit	PostgreSQL 18.4 (IvorySQL 5.4) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.5.0, 64-bit
Babelfish	Babelfish 17.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit	Babelfish 17.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1 20251022 (Red Hat 14.3.1-4), 64-bit
PolarDB	PostgreSQL 17.10 (PolarDB 17.10.1.0 build accf02e2) on x86_64-linux-gnu	PostgreSQL 17.10 (PolarDB 17.10.1.0 build accf02e2) on x86_64-linux-gnu
Percona	PostgreSQL 18.4 - Percona Server for PostgreSQL 18.4.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit	PostgreSQL 18.4 - Percona Server for PostgreSQL 18.4.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1 20250617 (Red Hat 14.3.1-2), 64-bit
OrioleDB	OrioleDB 18.4 (OrioleDB 1.8-beta16) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit	OrioleDB 18.4 (OrioleDB 1.8-beta16) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1 20251022 (Red Hat 14.3.1-4), 64-bit
OpenHalo	openHalo 14.18 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit	openHalo 14.18 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1 20251022 (Red Hat 14.3.1-4), 64-bit
DocumentDB	PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg26.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit	PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1 20251022 (Red Hat 14.3.1-4), 64-bit
AgensGraph	PostgreSQL 17.10 (AgensGraph 2.17.0) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit	PostgreSQL 17.10 (AgensGraph 2.17.0) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1 20251022 (Red Hat 14.3.1-4), 64-bit
pgEdge	PostgreSQL 18.4 (pgEdge 5.0.10) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit	PostgreSQL 18.4 (pgEdge 5.0.10) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.3.1 20251022 (Red Hat 14.3.1-4), 64-bit
Cloudberry		PostgreSQL 14.4 (Apache Cloudberry 2.0.0-incubating build 1) on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-11), 64-bit

1 - PostgreSQL

PostgreSQL is the world’s most advanced and popular open-source database.

Pigsty installs PostgreSQL 18 by default, supports PostgreSQL 14 ~ 18, and provides 575 PG extensions.

Quick Start

Install Pigsty using the pgsql configuration template.

./configure -c pgsql     # Use postgres kernel
./deploy.yml             # Deploy the Pigsty core chain with native PostgreSQL

Most configuration templates use PostgreSQL kernel by default, for example:

• meta : Default, postgres with core extensions (vector, postgis, timescale)
• rich : postgres with all extensions installed
• slim : postgres only, no monitoring infrastructure
• ha/full : 4-node sandbox for HA demonstration
• pgsql : minimal postgres kernel configuration example

Configuration

Vanilla PostgreSQL kernel requires no special adjustments:

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  }
    pg_databases:
      - { name: meta, baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [ vector ]}
    pg_hba_rules:
      - { user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes' }
    pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ] # Full backup at 1 AM daily
    pg_packages: [ pgsql-main, pgsql-common ]   # pg kernel and common utilities
    #pg_extensions: [ pg18-time ,pg18-gis ,pg18-rag ,pg18-fts ,pg18-olap ,pg18-feat ,pg18-lang ,pg18-type ,pg18-util ,pg18-func ,pg18-admin ,pg18-stat ,pg18-sec ,pg18-fdw ,pg18-sim ,pg18-etl]

Version Selection

To use a different PostgreSQL major version, you can configure it using the -v parameter:

./configure -c pgsql            # Default is postgresql 18, no need to specify explicitly
./configure -c pgsql -v 18      # Explicitly use postgresql 18
./configure -c pgsql -v 17      # Use postgresql 17
./configure -c pgsql -v 16      # Use postgresql 16
./configure -c pgsql -v 15      # Use postgresql 15
./configure -c pgsql -v 14      # Use postgresql 14

If a PostgreSQL cluster is already installed, you need to uninstall it before installing a new version:

./pgsql-rm.yml -l pg-meta # Uninstall cluster pg-meta

Extension Ecosystem

Pigsty provides a rich extension ecosystem for PostgreSQL. See the Extension Catalog for details.

2 - Supabase

Supabase — Build in a weekend, Scale to millions

Supabase is an open-source Firebase alternative that wraps PostgreSQL and provides authentication, out-of-the-box APIs, edge functions, real-time subscriptions, object storage, and vector embedding capabilities. This is a low-code all-in-one backend platform that lets you skip most backend development work, requiring only database design and frontend knowledge to quickly ship products!

Supabase’s motto is: “Build in a weekend, Scale to millions”. Indeed, Supabase is extremely cost-effective at small to micro scales (4c8g), like a cyber bodhisattva. — But when you really scale to millions of users — you should seriously consider self-hosting Supabase — whether for functionality, performance, or cost considerations.

Pigsty provides you with a complete one-click self-hosting solution for Supabase. Self-hosted Supabase enjoys full PostgreSQL monitoring, IaC, PITR, and high availability, and compared to Supabase cloud services, it provides up to 575 out-of-the-box PostgreSQL extensions and can more fully utilize the performance and cost advantages of modern hardware.

For the complete self-hosting tutorial, please refer to: Supabase Self-Hosting Guide

Quick Start

Pigsty’s default supabase.yml configuration template defines a single-node Supabase.

First, use Pigsty’s standard installation process to install the Silo and PostgreSQL instances required for Supabase:

 curl -fsSL https://repo.pigsty.io/get | bash
./bootstrap          # Environment check, install dependencies
./configure -c supabase  # Important: modify passwords and other key info in config!
./deploy.yml         # Install Pigsty, deploy PGSQL and MINIO!

Before deploying Supabase, please modify the Supabase parameters in the pigsty.yml config file according to your actual situation (mainly passwords!)

Then, run docker.yml and app.yml to complete the remaining work and deploy Supabase containers:

./docker.yml       # Install Docker module
./app.yml          # Start Supabase stateless components!

For users in China, please configure appropriate Docker mirror sites or proxy servers to bypass GFW to pull DockerHub images. For professional subscriptions, we provide the ability to offline install Pigsty and Supabase without internet access.

Pigsty exposes web services through Nginx on the admin node/INFRA node by default. You can add DNS resolution for supa.pigsty pointing to this node locally, then access https://supa.pigsty through a browser to enter the Supabase Studio management interface.

Default username and password: supabase / pigsty

3 - Babelfish

Babelfish is a PostgreSQL-based SQL Server compatibility layer, open-sourced by AWS.

Overview

Pigsty lets you deploy Babelfish in mssql mode and provide, on top of PostgreSQL:

• SQL Server wire protocol compatibility (TDS, 1433)
• T-SQL compatibility
• Unified integration with Pigsty capabilities (HA, backup, monitoring, IaC)

In Pigsty v4, Babelfish supports PostgreSQL 17/18. The default template uses pg_version: 17, and Babelfish is part of Pigsty’s standard delivery path with support for all Linux platforms.

Current Behavior

Compared with older Babelfish/PG15 docs, current behavior is:

• Supported kernel majors are PG17/18; the template defaults to PG17 (pg_version: 17)
• Default package group: babelfish + pgsql-common + sqlcmd
• Mainstream platform coverage:
  • OS: el8, el9, el10, d12, d13, u22, u24, u26
  • Arch: x86_64, aarch64
• mssql template no longer requires an extra mssql repo module (defaults to node,infra,pgsql)

Older docs may still contain deprecated naming. Pigsty now consistently uses Babelfish and babelfish aliases.

Quick Start

Use the built-in Pigsty template:

./configure -c mssql [-v 17/18]
./deploy.yml

After deployment, connect directly with SQL Server clients:

sqlcmd -S <ip>,1433 -U dbuser_mssql -P DBUser.MSSQL -d mssql

Key Configuration

Core parameters in the mssql template:

pg_mode: mssql
pg_version: 17  # optional: 18
pg_packages: [ babelfish, pgsql-common, sqlcmd ]
pg_libs: 'babelfishpg_tds, pg_stat_statements, auto_explain'

pg_databases:
  - name: mssql
    baseline: mssql.sql
    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 }
    parameters: { 'babelfishpg_tsql.migration_mode': 'multi-db' }

pg_hba_rules:
  - { user: dbuser_mssql, db: mssql, addr: intra, auth: md5, order: 525 }

pg_default_services:
  - { name: primary, port: 5433, dest: 1433 }
  - { name: replica, port: 5434, dest: 1433 }

Connectivity and Ports

Babelfish clusters expose two protocol endpoints:

• PostgreSQL protocol: 5432
• SQL Server protocol (TDS): 1433

With Pigsty service abstraction you can also use:

• 5433: fixed route to primary 1433
• 5434: route to readable node 1433

# Primary write access
sqlcmd -S <any-node-ip>,5433 -U dbuser_mssql -P DBUser.MSSQL

# Read replica query
sqlcmd -S <any-node-ip>,5434 -U dbuser_mssql -P DBUser.MSSQL

Notes

• Babelfish auth rules must use md5 instead of default scram-sha-256.
• Default migration mode is multi-db; switch with babelfishpg_tsql.migration_mode if needed.
• Not all native PostgreSQL extensions are directly usable on Babelfish kernels; validate package availability and compatibility first.
• Tighten HBA and network exposure for production; do not keep demo-level open rules.

Related Docs

• mssql config template
• PGSQL kernel mode config
• Official SQL Server sqlcmd docs

Available Extensions

The Babelfish kernel has 55 available extensions. After removing bundled PG Contrib extensions, the following extra extensions remain:

4 - Percona

Percona Postgres is a patched Postgres kernel with pg_tde (Transparent Data Encryption) extension.

Starting with v4.4.0, Pigsty packages Percona PostgreSQL under the private /usr/pgtde-$v; v4.5.0 keeps this layout prefix (/usr/pgtde-18 for PostgreSQL 18). The pgtde package alias installs both the kernel package and its contrib package, including pg_tde, PostGIS, pgvector, wal2json, pg_repack, pgaudit, and pg_stat_monitor.

• Performance Test for Percona Transparent Data Encryption (TDE)

Quick Start

Use Pigsty’s standard installation process with the pgtde configuration template.

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty;
./configure -c pgtde     # Use percona postgres kernel
./deploy.yml             # Deploy the Pigsty core chain with Percona PostgreSQL

Configuration

The following parameters need to be adjusted to deploy a Percona cluster:

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_mode: pgtde
    pg_cluster: pg-meta
    pg_users:
      - { name: dbuser_meta ,password: DBUser.Meta   ,pgbouncer: true ,roles: [dbrole_admin   ] ,comment: pgsql 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 } ]
    pg_hba_rules:
      - { user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes' }
    pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ] # Full backup at 1 AM daily

    # Percona PostgreSQL TDE kernel settings
    pg_packages: [ pgtde, pgsql-common ]
    pg_libs: 'pg_tde, pgaudit, pg_stat_statements, pg_stat_monitor, auto_explain'

The pgtde packages are delivered by Pigsty’s pgsql repository module. The legacy percona module is not required by this template.

Available Extensions

The Percona Postgres kernel has 73 available extensions. After removing bundled PG Contrib extensions, the following extra extensions remain:

Key Features

• Transparent Data Encryption: Provides data-at-rest encryption using the pg_tde extension
• PostgreSQL 18 Compatible: Based on the Percona PostgreSQL 18 package set
• Enterprise Extensions: Includes enterprise-grade features like pgaudit, pg_stat_monitor
• Complete Ecosystem: Supports popular extensions like pgvector, PostGIS

Note: Currently in stable stage - thoroughly evaluate before production use.

5 - openHalo

OpenHalo is an open-source PostgreSQL kernel that provides MySQL wire-protocol compatibility.

openHalo is based on PostgreSQL 14.18 and provides wire-level compatibility with MySQL 5.7.32-log / 8.0. Pigsty delivers it through pg_mode: mysql and the openhalo package alias.

Pigsty supports OpenHalo deployment on all supported Linux platforms.

• RPM build spec: github.com/pgsty/rpm/rpmbuild/specs/openhalodb.spec
• DEB build spec: github.com/pgsty/deb/debbuild/openhalodb

Quick Start

Use Pigsty’s standard installation flow with the mysql template.

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty;
./configure -c mysql    # Use MySQL (openHalo) template
./deploy.yml            # Install (change passwords in pigsty.yml before production use)

Cluster Config

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: postgres, extensions: [ aux_mysql ]} # mysql-compatible database
      - {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_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ] # full backup at 1 AM daily

    # OpenHalo specific settings
    pg_mode: mysql
    pg_version: 14
    pg_packages: [ openhalo, pgsql-common ]

OpenHalo provides a dedicated extension, aux_mysql, which includes functions and types needed for MySQL compatibility. Enable it in the postgres database to get full compatibility behavior.

• aux_mysql 1.5: MySQL Supplementary Extension
• /usr/halo-14/share/postgresql/extension/aux_mysql.control
• $libdir/mysm, mysm.so

Usage

For MySQL access, connections still use the postgres database. The MySQL “database” concept maps to PostgreSQL “schema”. So use mysql maps to the mysql schema in the postgres database.

MySQL usernames/passwords are the same PostgreSQL credentials.

Client Access

OpenHalo listens on port 3306 for MySQL wire protocol clients.

Pigsty’s conf/mysql installs a MySQL client by default.

mysql -h 127.0.0.1 -u dbuser_dba

At present, OpenHalo upstream reports Navicat works normally on this port, while IntelliJ DataGrip may fail.

Compatibility Parameters

Pigsty defaults database_compat_mode to mysql. You can further tune compatibility behavior with settings like:

mysql.listener_on = true                        # enable MySQL listener; restart required
mysql.port = 3306                               # second_port for MySQL mode; restart required
mysql.halo_mysql_version = '5.7.32-log'         # restart required
mysql.ci_collation = true                       # restart required
mysql.explicit_defaults_for_timestamp = false   # restart required
mysql.auto_rollback_tx_on_error = false         # restart required

Patch Notes

The OpenHalo kernel packaged by Pigsty is based on HaloTech-Co-Ltd/openHalo with small adjustments:

• Restore default database name from halo0root to postgres
• Remove 1.0. prefix in the default version string, keeping 14.18
• Adjust default config to enable MySQL compatibility and listen on 3306

Pigsty does not provide warranty coverage for OpenHalo kernel behavior. Kernel-specific issues should be addressed with the upstream vendor.

Warning: This kernel is currently in beta1 stage; evaluate risks carefully before production use.

Available Extensions

The OpenHalo kernel has 59 available extensions. After removing bundled PG Contrib extensions, the following extra extensions remain:

6 - OrioleDB

OrioleDB is a PostgreSQL storage engine extension that claims to provide 4x OLTP performance, no xid wraparound and table bloat issues, and “cloud-native” (data stored in S3) capabilities.

Pigsty ships OrioleDB as a patched PostgreSQL kernel plus the OrioleDB extension.

You can run OrioleDB as an RDS using Pigsty. Current packages support PostgreSQL 16, 17, and 18 on supported Linux platforms. pg_mode still uses oriole for the /usr/oriole-$v install path, while the orioledb package alias resolves to versioned kernel packages such as orioledb-16, orioledb-17, and orioledb-18. The current Pigsty package line is OrioleDB 1.8 beta16.

Quick Start

Follow Pigsty’s standard installation process using the oriole configuration template.

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty;
./configure -c oriole    # Use OrioleDB configuration template
./deploy.yml             # Install Pigsty with OrioleDB

For production deployment, ensure you modify the password parameters in the pigsty.yml configuration before running the install playbook.

Configuration

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], extensions: [orioledb]}
    pg_hba_rules:
      - {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
    pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ] # Full backup at 1 AM daily

    # OrioleDB specific settings
    pg_mode: oriole                                         # oriole compatibility mode
    pg_version: 18                                          # OrioleDB supports PG 16, 17, and 18
    pg_packages: [ orioledb, pgsql-common ]                 # Install OrioleDB kernel
    pg_libs: 'orioledb, pg_stat_statements, auto_explain'   # Load OrioleDB extension

Usage

To use OrioleDB, install the orioledb package alias. Pigsty resolves it to the selected PG16, PG17, or PG18 OrioleDB kernel package according to pg_version.

Initialize TPC-B-like tables with pgbench using 100 warehouses:

pgbench -is 100 meta
pgbench -nv -P1 -c10 -S -T1000 meta
pgbench -nv -P1 -c50 -S -T1000 meta
pgbench -nv -P1 -c10    -T1000 meta
pgbench -nv -P1 -c50    -T1000 meta

Next, you can rebuild these tables using the orioledb storage engine and observe the performance difference:

-- Create OrioleDB tables
CREATE TABLE pgbench_accounts_o (LIKE pgbench_accounts INCLUDING ALL) USING orioledb;
CREATE TABLE pgbench_branches_o (LIKE pgbench_branches INCLUDING ALL) USING orioledb;
CREATE TABLE pgbench_history_o (LIKE pgbench_history INCLUDING ALL) USING orioledb;
CREATE TABLE pgbench_tellers_o (LIKE pgbench_tellers INCLUDING ALL) USING orioledb;

-- Copy data from regular tables to OrioleDB tables
INSERT INTO pgbench_accounts_o SELECT * FROM pgbench_accounts;
INSERT INTO pgbench_branches_o SELECT * FROM pgbench_branches;
INSERT INTO pgbench_history_o SELECT  * FROM pgbench_history;
INSERT INTO pgbench_tellers_o SELECT * FROM pgbench_tellers;

-- Drop original tables and rename OrioleDB tables
DROP TABLE pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers;
ALTER TABLE pgbench_accounts_o RENAME TO pgbench_accounts;
ALTER TABLE pgbench_branches_o RENAME TO pgbench_branches;
ALTER TABLE pgbench_history_o RENAME TO pgbench_history;
ALTER TABLE pgbench_tellers_o RENAME TO pgbench_tellers;

Key Features

• No XID Wraparound: Eliminates transaction ID wraparound maintenance
• No Table Bloat: Advanced storage management prevents table bloat
• Cloud Storage: Native support for S3-compatible object storage
• OLTP Optimized: Designed for transactional workloads
• Improved Performance: Better space utilization and query performance

Note: Currently in Beta stage - thoroughly evaluate before production use.

Available Extensions

The OrioleDB kernel has 53 available extensions. After removing bundled PG Contrib extensions, the following extra extensions remain:

7 - Cloudberry

Cloudberry is an open-source MPP data warehouse kernel derived from the Greenplum ecosystem, suitable for large-scale parallel analytics workloads.

Overview

In Pigsty, Cloudberry uses gpsql mode and shares the same identity model, monitoring logic, and directory conventions as Greenplum / MatrixDB.

• Kernel package: cloudberry
• Mode identifier: pg_mode: gpsql
• Role flag: gp_role: master | segment
• Current repo version: Cloudberry 2.1.0
• Current main package version: DEB 2.1.0-2PIGSTY, RPM 2.1.0-3PIGSTY
• Default binary directory: /usr/cloudberry

The important boundary is this: Pigsty currently focuses on package delivery, node management, monitoring onboarding, access control, and configuration orchestration for Cloudberry. For MPP cluster initialization, scale-out, rebalance, and other upstream-specific operational actions, you should still use the official Cloudberry toolchain.

The current Pigsty repository provides cloudberry, cloudberry-backup, and cloudberry-pxf packages for both DEB and RPM platforms.

Installation

There is no standalone cloudberry one-click template yet. The more common workflow is:

1. Enroll the target nodes into Pigsty.
2. Install the cloudberry kernel package.
3. Describe the coordinator / segment topology with gpsql mode.
4. Use Pigsty to unify monitoring, accounts, access control, and backup integration.

If you only need to install the kernel package on a node:

./node.yml -t node_repo    -e '{"node_repo_modules":"local,node,pgsql"}'
./node.yml -t node_install -e '{"node_packages":["cloudberry"]}'

If you are onboarding an existing Cloudberry cluster, it is usually better to keep the original initialization workflow and add Pigsty inventory plus monitoring configuration incrementally.

Configuration

Cloudberry uses gpsql mode rather than a dedicated cloudberry mode. Compared with vanilla PostgreSQL, you at least need to care about the extra identity parameters pg_shard and gp_role; if you want to label shard groups explicitly, you can also add pg_group.

Here is a minimal readable topology example:

all:
  children:
    cb-mdw:
      hosts:
        10.10.10.10: { pg_seq: 1, pg_role: primary }
      vars:
        pg_cluster: cb-mdw
        pg_mode: gpsql
        pg_shard: cb
        gp_role: master
        pg_packages: [ cloudberry, pgsql-common ]

    cb-sdw:
      hosts:
        10.10.10.11:
          nodename: cb-sdw-1
          pg_instances:
            6000: { pg_cluster: cb-seg1, pg_seq: 1, pg_role: primary, pg_exporter_port: 9633 }
        10.10.10.12:
          nodename: cb-sdw-2
          pg_instances:
            6000: { pg_cluster: cb-seg2, pg_seq: 1, pg_role: primary, pg_exporter_port: 9633 }
      vars:
        pg_cluster: cb-sdw
        pg_mode: gpsql
        pg_shard: cb
        gp_role: segment
        pg_preflight_skip: true
        pg_packages: [ cloudberry, pgsql-common ]
        pg_exporter_config: pg_exporter_basic.yml
        pg_exporter_params: 'options=-c%20gp_role%3Dutility&sslmode=disable'

Two details are easy to miss:

• gp_role: master is for the coordinator / master node, and business access usually lands there.
• gp_role: segment nodes usually need pg_exporter to connect in utility mode for monitoring.

Client Access

For application and BI access, Cloudberry still exposes the PostgreSQL wire protocol, so most PostgreSQL-compatible clients, drivers, and BI tools can connect without special handling.

But keep the following in mind:

• Applications and analytics queries should connect to the master / coordinator, not directly to segment nodes.
• Segment nodes are better treated as data/compute shards and monitoring targets.
• If you want a unified access endpoint, you can still use Pigsty’s HAProxy / PgBouncer / DNS service abstractions.

Extensions and Ecosystem

Cloudberry comes from the PostgreSQL ecosystem, but it is not simply “vanilla PostgreSQL plus a few extensions”. For the extension packages already available in Pigsty, it is better to think in two categories:

• Pure SQL objects or components with weak ABI coupling are usually easier to adapt.
• Extensions that depend on PGXS or the kernel C ABI often need separate validation or even recompilation against the Cloudberry version and toolchain.

If your workload depends on postgis, vector extensions, FDWs, auditing, or custom C extensions, validate them on the target Cloudberry version first rather than copying a vanilla PostgreSQL extension list unchanged.

Notes

• Cloudberry currently has no dedicated Pigsty template, so you should model it manually with gpsql mode.
• The current delivery focus is packages, configuration, and monitoring; it does not replace the official Cloudberry MPP initialization and scale-out toolchain.
• Because this is an MPP distributed kernel, vanilla PostgreSQL operational assumptions do not automatically transfer to every Patroni / PgBouncer / PgBackRest node role.
• If you need horizontal PostgreSQL scaling rather than a full MPP warehouse, Citus is usually the better first choice.

Related Docs

• PGSQL kernel overview
• PGSQL kernel mode configuration
• Greenplum / GPSQL docs

8 - AgensGraph

AgensGraph is a property graph database kernel built on PostgreSQL, supporting openCypher queries and mixed Cypher/SQL workflows.

Overview

Pigsty integrates AgensGraph through pg_mode: agens while preserving most of the standard PostgreSQL operational model.

• Kernel package: agensgraph
• Mode identifier: pg_mode: agens
• Current template version: AgensGraph 2.17.0
• Current version string: PostgreSQL 17.10 (AgensGraph 2.17.0)
• Built-in template: agens
• Typical use cases: graph relationship analysis, path queries, knowledge graphs, and risk/association analysis layered onto relational data

From the client side, AgensGraph still speaks the PostgreSQL wire protocol, so normal PostgreSQL clients, drivers, and connection pools can connect directly. The real difference from vanilla PostgreSQL is not how you connect, but that the database now contains graph objects, Cypher syntax, and the agtype data type.

Installation

Use the built-in Pigsty template:

./configure -c agens
./deploy.yml

The agens template automatically enables pg_mode: agens and installs the agensgraph kernel package. After deployment, verify the kernel version:

psql -d meta -c "SELECT version();"

Configuration

Key configuration for AgensGraph in Pigsty:

all:
  vars:
    node_repo_modules: node,infra,pgsql
    pg_version: 17

  children:
    pg-meta:
      vars:
        pg_mode: agens
        pg_packages: [ agensgraph, pgsql-common ]

AgensGraph does not require a special preload stack like pgEdge or Babelfish, so most standard Pigsty patterns for HA, backup, monitoring, access control, and IaC remain unchanged. If your workload is dominated by graph traversal and complex path queries, focus on work_mem, shared_buffers, and planner cost settings instead of assuming default OLTP habits will fit.

Usage

After connecting to the database, the usual first step is to create a graph and set graph_path:

CREATE GRAPH g;
SET graph_path = g;

Create labels, vertices, and edges:

CREATE VLABEL person;
CREATE ELABEL knows;

CREATE (:person {name: 'Jack'});
CREATE (:person {name: 'Emily'})-[:knows]->(:person {name: 'Tom'});

Run graph queries and updates:

MATCH (:person {name: 'Emily'})-[:knows]->(v:person)
RETURN v.name;

MATCH (v:person {name: 'Jack'})
SET v.age = '24';

To call Cypher from within SQL, use the cypher() function:

SELECT *
FROM cypher('g', $$ MATCH (v:person) RETURN v.name $$) AS (name agtype);

In real projects, the more common pattern is to mix “relational tables + graph labels + Cypher queries”: transactions, privileges, and backup workflows still follow PostgreSQL, while graph analysis logic lives in AgensGraph graph objects and the cypher() interface.

Notes

• AgensGraph is currently fixed to the PG17-compatible line, so do not assume PG18 extension availability will carry over.
• The default agens template is single-node for quick validation; production deployments should extend to an HA topology.
• Not all third-party PostgreSQL extensions are guaranteed to work on the AgensGraph kernel; verify compatibility first.
• Graph objects and relational objects can coexist in the same database, but in production it is usually better to define clear database or naming conventions so they do not become tangled together.
• Tune memory and cost parameters based on your graph model scale; do not blindly use defaults.
• For compatibility or semantic issues with the AgensGraph kernel, consult the official manual and upstream issues first.

Related Docs

• Pigsty config template: conf/agens
• PGSQL kernel mode config
• AgensGraph repository: https://github.com/skaiworldwide-oss/agensgraph
• AgensGraph official docs: https://tech.skaiworldwide.com/docs/en/agensgraph/latest/
• AgensGraph Quick Guide: https://tech.skaiworldwide.com/docs/en/agensgraph/17/quick_guide/index.html
• AgensGraph 2.17.0 Release Notes: https://tech.skaiworldwide.com/docs/en/agensgraph/latest/release_notes/agensgraph_release_notes_2_17_0.html

Available Extensions

The AgensGraph kernel has 60 available extensions. After removing bundled PG Contrib extensions, the following extra extensions remain:

9 - pgEdge

pgEdge is a distributed PostgreSQL distribution for edge scenarios, built on Spock multi-master logical replication.

Overview

Pigsty integrates pgEdge through pg_mode: pgedge and delivers it through the standard PostgreSQL cluster workflow:

• pgedge: a PG15, PG16, PG17, and PG18 compatible kernel; the template defaults to PG18
• spock: Active-active multi-master logical replication
• snowflake: Distributed unique sequences
• lolor: Large object logical replication compatibility layer

The current Pigsty repository ships versioned pgEdge kernel packages for pgedge-15, pgedge-16, pgedge-17, and pgedge-18; the template defaults to pg_version: 18. The spock, snowflake, and lolor control files, SQL files, and shared libraries are bundled in the pgedge-$v kernel package, so they are no longer listed as separate pg_extensions packages to install. From the client side, pgEdge is still PostgreSQL wire compatible, so psql, JDBC/ODBC, DBeaver, and similar tools work as usual.

The delivery model in Pigsty is: validate the kernel on a single node first, then expand to a multi-node replication topology. The template handles the kernel, extensions, monitoring, backup, and access control out of the box, but the actual multi-master topology still needs to be designed around your workload consistency and conflict strategy.

Installation

Use the built-in Pigsty template:

./configure -c pgedge
./deploy.yml

The template pre-installs spock, snowflake, and lolor in the meta database. After deployment, verify the kernel and extensions:

psql -d meta -c "SELECT version();"
psql -d meta -c "SELECT extname, extversion FROM pg_extension WHERE extname IN ('spock','snowflake','lolor') ORDER BY 1;"

For the full template and parameters, see: pgedge config template.

Configuration

Key parameters in the pgedge template (matching conf/pgedge.yml):

pg_mode: pgedge
pg_version: 18
pg_packages: [ pgedge, pgsql-common ]
pg_libs: 'spock, lolor, pg_stat_statements, auto_explain'
pg_databases:
  - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [spock, snowflake, lolor] }

If you plan to grow into a multi-node multi-master topology, it is better to configure logical replication capacity and snowflake.node explicitly:

pg_parameters:
  wal_level: logical
  max_replication_slots: 16
  max_wal_senders: 16
  'snowflake.node': 1

snowflake.node must be unique on every writable node, otherwise distributed IDs will collide.

Usage

The common workflow in Pigsty is still: validate the kernel on a single node first, then expand into a multi-node Spock replication topology.

If you need these capabilities in a business database as well, create the extensions first:

CREATE EXTENSION IF NOT EXISTS spock;
CREATE EXTENSION IF NOT EXISTS snowflake;
CREATE EXTENSION IF NOT EXISTS lolor;

Then use the Spock SQL API or the pgEdge CLI to create nodes, replication sets, and subscriptions. If your schema already uses serial or identity, plan the snowflake sequence migration before enabling multi-master writes, otherwise cross-node primary key collisions are likely.

Notes

• Replication in pgEdge is organized per database, not as an instance-wide “turn everything into multi-master” switch.
• Replicated tables should have a PRIMARY KEY or an appropriate REPLICA IDENTITY.
• UNLOGGED and TEMPORARY tables do not participate in Spock logical replication.
• Spock configuration and operations typically require superuser privileges, so production deployments should define privilege boundaries clearly.
• If your workload depends on large object replication, use lolor explicitly rather than assuming native large objects will replicate correctly.
• Cross-region multi-master is not a checkbox feature. Network latency, conflict handling, and the write model all need to be evaluated first.

Related Docs

• PGSQL Kernel Overview
• pgedge config template
• PGSQL kernel mode config
• spock extension
• snowflake extension
• lolor extension
• pgEdge official docs
• Spock Limitations
• Snowflake Sequences

Available Extensions

The pgEdge kernel has 63 available extensions. After removing bundled PG Contrib extensions, the following extra extensions remain:

10 - DocumentDB

DocumentDB is an open-source PostgreSQL document database extension maintained by Microsoft. FerretDB is a stateless protocol translation proxy built on top of it. Together, they expose a MongoDB wire-compatible endpoint from a standard PostgreSQL kernel: applications using MongoDB drivers can connect directly, while requests are translated into PostgreSQL operations.

Unlike other kernel variants, this is not a standalone PostgreSQL fork. The data layer runs native PostgreSQL 16–18 and is managed by the standard PGSQL module. Persistence, transactions, high availability, backup, monitoring, and access control remain PostgreSQL responsibilities; FerretDB is deployed as a Pigsty Docker APP and handles only protocol translation.

Pigsty is a FerretDB community partner, provides binary packages for FerretDB and the DocumentDB extensions, and delivers the complete stack out of the box through the mongo configuration template.

Quick Start

Use Pigsty’s standard installation flow with the mongo configuration template:

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty;
./configure -c mongo    # Use the Mongo (DocumentDB + FerretDB) configuration template
./deploy.yml            # Install; change passwords in pigsty.yml first for production
./docker.yml -l pg-meta # Install Docker on the pg-meta node
./app.yml -l pg-meta    # Deploy the FerretDB Docker APP

FerretDB listens on the local loopback address at port 27017 by default. Connect with mongosh or any MongoDB-compatible client:

mongosh 'mongodb://mongod:[email protected]:27017/'

Configuration

Source: pigsty/conf/mongo.yml. See the Mongo configuration template documentation for the complete template.

The key PostgreSQL settings are the documentdb extension and its preload libraries, plus the backend superuser used by FerretDB:

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - { name: mongod ,password: DBUser.Mongo ,superuser: true ,comment: FerretDB backend user }
    pg_databases:
      - { name: postgres, extensions: [ documentdb, postgis, vector, pg_cron, rum ]}
    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'

FerretDB is deployed as a Docker APP. Its settings are ordinary overrides under apps.ferretdb.conf, and the container connects to the local primary service on port 5436 through host.docker.internal:

docker_enabled: true
app: ferretdb
apps:
  ferretdb:
    conf:
      FERRETDB_IMAGE: ghcr.io/ferretdb/ferretdb:2.7.0
      FERRETDB_POSTGRESQL_URL: 'postgres://mongod:[email protected]:5436/postgres?pool_min_conns=1&pool_max_conns=20'
      FERRETDB_BIND_ADDR: 127.0.0.1
      FERRETDB_PORT: 27017
      FERRETDB_AUTH: true
      FERRETDB_TELEMETRY: disabled

High Availability

Because FerretDB is fully stateless, its HA topology follows the standard PostgreSQL cluster pattern. The template retains a commented three-node pg-mongo example. Each node runs a FerretDB container bound to local port 27018, and HAProxy aggregates them behind the floating endpoint 10.10.10.4:27017 (mongo.pigsty).

Patroni and etcd continue to manage PostgreSQL failover. The Mongo endpoint automatically recovers after the primary switches.

Notes

• FerretDB enables authentication by default (FERRETDB_AUTH: true) but does not yet implement MongoDB authorization roles. PostgreSQL users and HBA rules remain the actual security boundary.
• Client-side MongoDB TLS is disabled by default, and the Mongo endpoint is not exposed to the network. Change FERRETDB_BIND_ADDR only when remote access is required.
• The backend cluster uses standard PostgreSQL parameters, playbooks, and dashboards; there is no independent FERRET module or mongo_* parameter group.
• Repeat an authenticated CRUD smoke test after upgrading FerretDB or DocumentDB.

11 - Citus

Pigsty natively supports Citus. This is a distributed horizontal scaling extension based on the native PostgreSQL kernel.

Installation

Citus is a PostgreSQL extension plugin that can be installed and enabled on a native PostgreSQL cluster following the standard plugin installation process.

./pgsql.yml -t pg_extension -e '{"pg_extensions":["citus"]}'

Configuration

To define a citus cluster, you need to specify the following parameters:

• pg_mode must be set to citus instead of the default pgsql
• You must define the shard name pg_shard and shard number pg_group on each shard cluster
• You must define pg_primary_db to specify the database managed by Patroni
• If you want to use postgres from pg_dbsu instead of the default pg_admin_username to execute admin commands, then pg_dbsu_password must be set to a non-empty plaintext password

Additionally, you need extra hba rules to allow SSL access from localhost and other data nodes.

You can define each Citus cluster as a separate group, like standard PostgreSQL clusters. The current complete template is conf/ha/citus.yml:

all:
  children:
    pg-citus0: # citus shard 0
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus0 , pg_group: 0 }
    pg-citus1: # citus shard 1
      hosts: { 10.10.10.11: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus1 , pg_group: 1 }
    pg-citus2: # citus shard 2
      hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus2 , pg_group: 2 }
    pg-citus3: # citus shard 3
      hosts:
        10.10.10.13: { pg_seq: 1, pg_role: primary }
        10.10.10.14: { pg_seq: 2, pg_role: replica }
      vars: { pg_cluster: pg-citus3 , pg_group: 3 }
  vars:                               # Global parameters for all Citus clusters
    pg_mode: citus                    # pgsql cluster mode must be set to: citus
    pg_shard: pg-citus                # citus horizontal shard name: pg-citus
    pg_primary_db: meta               # citus database name: meta
    pg_dbsu_password: DBUser.Postgres # If using dbsu, you need to configure a password for it
    pg_users: [ { name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [ dbrole_admin ] } ]
    pg_databases: [ { name: meta ,extensions: [ { name: citus }, { name: postgis }, { name: timescaledb } ] } ]
    pg_hba_rules:
      - { 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'  }

You can also specify identity parameters for all Citus cluster members within a single group, as shown in conf/ha/citus.yml:

#==========================================================#
# 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: auto
    pg_extensions: [ 'citus postgis timescaledb 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: postgis } ] } ]
    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'  }

Usage

You can access any node just like accessing a regular cluster:

pgbench -i postgres://test:test@pg-citus0/test
pgbench -nv -P1 -T1000 -c 2 postgres://test:test@pg-citus0/test

By default, changes you make to one Shard only occur on that cluster and are not synchronized to other Shards.

If you want to distribute writes across all Shards, you can use the API functions provided by Citus to mark tables as:

• Distributed tables (automatic partitioning, requires specifying partition key)
• Reference tables (full replication: does not require specifying partition key)

Starting from Citus 11.2, any Citus database node can play the role of coordinator, meaning any primary node can write:

psql -h pg-citus0 -d test -c "SELECT create_distributed_table('pgbench_accounts', 'aid'); SELECT truncate_local_data_after_distributing_table('public.pgbench_accounts');"
psql -h pg-citus0 -d test -c "SELECT create_reference_table('pgbench_branches')         ; SELECT truncate_local_data_after_distributing_table('public.pgbench_branches');"
psql -h pg-citus0 -d test -c "SELECT create_reference_table('pgbench_history')          ; SELECT truncate_local_data_after_distributing_table('public.pgbench_history');"
psql -h pg-citus0 -d test -c "SELECT create_reference_table('pgbench_tellers')          ; SELECT truncate_local_data_after_distributing_table('public.pgbench_tellers');"

After distributing the tables, you can also access them on other nodes:

psql -h pg-citus1 -d test -c '\dt+'

For example, a full table scan will show that the execution plan has become a distributed plan:

vagrant@meta-1:~$ psql -h pg-citus3 -d test -c 'explain select * from pgbench_accounts'
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Custom Scan (Citus Adaptive)  (cost=0.00..0.00 rows=100000 width=352)
   Task Count: 32
   Tasks Shown: One of 32
   ->  Task
         Node: host=10.10.10.52 port=5432 dbname=test
         ->  Seq Scan on pgbench_accounts_102008 pgbench_accounts  (cost=0.00..81.66 rows=3066 width=97)
(6 rows)

You can initiate writes from several different primary nodes:

pgbench -nv -P1 -T1000 -c 2 postgres://test:test@pg-citus1/test
pgbench -nv -P1 -T1000 -c 2 postgres://test:test@pg-citus2/test
pgbench -nv -P1 -T1000 -c 2 postgres://test:test@pg-citus3/test
pgbench -nv -P1 -T1000 -c 2 postgres://test:test@pg-citus4/test

When a node fails, the native high availability support provided by Patroni will promote the standby node and automatically take over.

test=# select * from  pg_dist_node;
 nodeid | groupid |  nodename   | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
--------+---------+-------------+----------+----------+-------------+----------+----------+-------------+----------------+------------------
      1 |       0 | 10.10.10.51 |     5432 | default  | t           | t        | primary  | default     | t              | f
      2 |       2 | 10.10.10.54 |     5432 | default  | t           | t        | primary  | default     | t              | t
      5 |       1 | 10.10.10.52 |     5432 | default  | t           | t        | primary  | default     | t              | t
      3 |       4 | 10.10.10.58 |     5432 | default  | t           | t        | primary  | default     | t              | t
      4 |       3 | 10.10.10.56 |     5432 | default  | t           | t        | primary  | default     | t              | t

12 - IvorySQL

IvorySQL is an open-source PostgreSQL kernel fork that aims to provide “Oracle compatibility” based on PG.

Overview

The Pigsty PGSQL repository directly provides IvorySQL 5.4 packages compatible with PostgreSQL 18.4 across the currently supported EL, Debian, Ubuntu, and dual-architecture platforms. Online installation uses Pigsty’s pgsql repository; the Professional Edition also provides offline delivery for the corresponding platforms.

Pigsty’s ivorysql package alias points to IvorySQL 5, compatible with PostgreSQL 18. Real package names are mapped by platform variables under roles/node_id/vars/; for example, EL uses ivorysql5, while Debian/Ubuntu uses ivorysql-5.

The last IvorySQL version supporting EL7 was 3.3, corresponding to PostgreSQL 16.3; the last version based on PostgreSQL 17 is IvorySQL 4.4

Installation

Install with Pigsty’s built-in ivory configuration template:

./configure -c ivory
./deploy.yml

Configuration

The following parameters need to be configured for IvorySQL database clusters:

#----------------------------------#
# Ivory SQL Configuration
#----------------------------------#
node_repo_modules: node,infra,pgsql       # use Pigsty node/infra/pgsql repos
pg_mode: ivory                    # IvorySQL Oracle Compatible Mode
pg_packages: [ ivorysql, pgsql-common ]
pg_libs: 'liboracle_parser, pg_stat_statements, auto_explain'
pg_extensions: [ ]                # do not install any vanilla postgresql extensions

When using Oracle compatibility mode, you need to dynamically load the liboracle_parser extension plugin.

Client Access

IvorySQL 5 is equivalent to PostgreSQL 18, and any client tool compatible with the PostgreSQL wire protocol can access IvorySQL clusters.

Available Extensions

The IvorySQL kernel has 95 available extensions. After removing bundled PG Contrib extensions, the following extra extensions remain:

Please note that Pigsty does not assume any warranty responsibility for using the IvorySQL kernel. Any issues or requirements encountered when using this kernel should be addressed with the original vendor.

13 - PolarDB PG

Overview

Pigsty allows you to create PostgreSQL clusters with “domestic innovation qualification” credentials using PolarDB!

PolarDB for PostgreSQL now uses PostgreSQL 17 as its base. The polar template, default path, and extension notes in Pigsty have all been updated to PG17. Any client tool compatible with the PostgreSQL wire protocol can access PolarDB clusters.

Pigsty’s PGSQL repository provides PolarDB PG open-source installation packages, but they are not downloaded to the local software repository during Pigsty installation.

Installation

Use the built-in Pigsty template:

./configure -c polar
./deploy.yml

Change Summary

Starting with Pigsty v4.4, the PolarDB PG kernel uses packages built and maintained by Pigsty. The main changes are:

Configuration

The following parameters need special configuration for PolarDB database clusters:

#----------------------------------#
# PGSQL & PolarDB
#----------------------------------#
pg_version: 17
pg_mode: polar
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 } # <- 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 }

The default installation directory for the polar kernel has moved to /usr/polar-17. One important difference is that PolarDB PG still requires the replicator replication user to be SUPERUSER, unlike vanilla PostgreSQL.

Available Extensions

The PolarDB PG kernel has 93 available extensions. After removing bundled PG Contrib extensions, the following extra extensions remain:

14 - PolarDB Oracle

Pigsty allows you to create PolarDB for Oracle clusters with “domestic innovation qualification” credentials using PolarDB!

According to the Security and Reliability Evaluation Results Announcement (No. 1, 2023), Appendix 3, Centralized Database. PolarDB v2.0 is an autonomous, controllable, secure, and reliable domestic innovation database.

PolarDB for Oracle is an Oracle-compatible version developed based on PolarDB for PostgreSQL. Both share the same kernel, distinguished by the --compatibility-mode parameter.

We collaborate with the Alibaba Cloud kernel team to provide a complete database solution based on PolarDB v2.0 kernel and Pigsty. Please contact sales for inquiries, or purchase on Alibaba Cloud Marketplace.

The PolarDB for Oracle kernel is currently only available on EL7 (CentOS 7) systems.

Extensions

Currently, the PolarDB 2.0 (Oracle compatible) kernel comes with the following 188 extension plugins:

15 - PostgresML

PostgresML is a PostgreSQL extension that supports the latest large language models (LLM), vector operations, classical machine learning, and traditional Postgres application workloads.

PostgresML (pgml) is a PostgreSQL extension written in Rust. You can run standalone Docker images, but this documentation is not a docker-compose template introduction, for reference only.

PostgresML officially supports Ubuntu 22.04, but we also maintain RPM versions for EL 8/9, if you don’t need CUDA and NVIDIA-related features.

You need internet access on database nodes to download Python dependencies from PyPI and models from HuggingFace.

Configuration

PostgresML is an extension written in Rust. Pigsty maintains prebuilt packages for PG14-17 on EL8/EL9 and Debian/Ubuntu platforms.

Creating a New Cluster

PostgresML 2.10.0 is available for PostgreSQL 14-17. The example below uses PG17; if you use PG14-16, change pg_version to the corresponding major version.

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] ,extensions: [{name: postgis, schema: public}, {name: timescaledb}]}
    pg_hba_rules:
      - {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
    pg_version: 17
    pg_libs: 'pgml, pg_stat_statements, auto_explain'
    pg_extensions: [ pgml, pgvector, wal2json, pg_repack ]

Pigsty resolves the pgml package alias to the platform-specific package name: pgml_$v on EL and postgresql-$v-pgml on Debian/Ubuntu. You also need to add pgml to pg_libs.

Enabling on an Existing Cluster

To enable pgml on an existing cluster, you can install it using Ansible’s package module:

ansible pg-meta -m package -b -a 'name=pgml_17'
# ansible el8,el9 -m package -b -a 'name=pgml_17'              # EL 8/9
# ansible u22,u24 -m package -b -a 'name=postgresql-17-pgml'   # Debian/Ubuntu

Python Dependencies

You also need to install PostgresML’s Python dependencies on cluster nodes. Official tutorial: Installation Guide

Install Python and PIP

Ensure python3, pip, and venv are installed:

# Ubuntu 22.04 (python3.10), need to install pip and venv using apt
sudo apt install -y python3 python3-pip python3-venv

For EL 8 / EL9 and compatible distributions, you can use python3.11:

# EL 8/9, can upgrade the default pip and virtualenv
sudo yum install -y python3.11 python3.11-pip       # install latest python3.11
python3.11 -m pip install --upgrade pip virtualenv  # use python3.11 on EL8 / EL9

pip config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple    # set global mirror (recommended)
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple some-package        # use for single installation

Install Dependencies

Create a Python virtual environment and use pip to install dependencies from requirements.txt and requirements-xformers.txt.

If you’re using EL 8/9, replace python3 with python3.11 in the following commands.

su - postgres;                          # create virtual environment as database superuser
mkdir -p /data/pgml; cd /data/pgml;     # create virtual environment directory
python3    -m venv /data/pgml           # create virtual environment directory (Ubuntu 22.04)
source /data/pgml/bin/activate          # activate virtual environment

# write Python dependencies and install with pip
cat > /data/pgml/requirements.txt <<EOF
accelerate==0.22.0
auto-gptq==0.4.2
bitsandbytes==0.41.1
catboost==1.2
ctransformers==0.2.27
datasets==2.14.5
deepspeed==0.10.3
huggingface-hub==0.17.1
InstructorEmbedding==1.0.1
lightgbm==4.1.0
orjson==3.9.7
pandas==2.1.0
rich==13.5.2
rouge==1.0.1
sacrebleu==2.3.1
sacremoses==0.0.53
scikit-learn==1.3.0
sentencepiece==0.1.99
sentence-transformers==2.2.2
tokenizers==0.13.3
torch==2.0.1
torchaudio==2.0.2
torchvision==0.15.2
tqdm==4.66.1
transformers==4.33.1
xgboost==2.0.0
langchain==0.0.287
einops==0.6.1
pynvml==11.5.0
EOF

# install dependencies using pip in the virtual environment
python3 -m pip install -r /data/pgml/requirements.txt
python3 -m pip install xformers==0.0.21 --no-dependencies

# additionally, 3 Python packages need to be installed globally using sudo!
sudo python3 -m pip install xgboost lightgbm scikit-learn

Enable PostgresML

After installing the pgml extension and Python dependencies on all cluster nodes, you can enable pgml on the PostgreSQL cluster.

Use the patronictl command to configure the cluster, add pgml to shared_preload_libraries, and specify your virtual environment directory in pgml.venv:

shared_preload_libraries: pgml, timescaledb, pg_stat_statements, auto_explain
pgml.venv: '/data/pgml'

Then restart the database cluster and create the extension using SQL commands:

CREATE EXTENSION vector;        -- also recommend installing pgvector!
CREATE EXTENSION pgml;          -- create PostgresML in the current database
SELECT pgml.version();          -- print PostgresML version information

If everything is normal, you should see output similar to the following:

# create extension pgml;
INFO:  Python version: 3.11.2 (main, Oct  5 2023, 16:06:03) [GCC 8.5.0 20210514 (Red Hat 8.5.0-18)]
INFO:  Scikit-learn 1.3.0, XGBoost 2.0.0, LightGBM 4.1.0, NumPy 1.26.1
CREATE EXTENSION

# SELECT pgml.version(); -- print PostgresML version information
 version
---------
 2.7.8

Done! For more details, please refer to the official PostgresML documentation: https://postgresml.org/docs/guides/use-cases/

16 - Greenplum

Pigsty supports deploying Greenplum clusters and its derivative distribution YMatrixDB, and provides the capability to integrate existing Greenplum deployments into Pigsty monitoring.

Overview

Greenplum / YMatrix cluster deployment capabilities are only available in the professional/enterprise editions and are not currently open source.

Installation

Pigsty provides installation packages for Greenplum 6 (@el7) and Greenplum 7 (@el8). Open source users can install and configure them manually.

# EL 7 Only (Greenplum6)
./node.yml -t node_install  -e '{"node_repo_modules":"pgsql","node_packages":["open-source-greenplum-db-6"]}'

# EL 8 Only (Greenplum7)
./node.yml -t node_install  -e '{"node_repo_modules":"pgsql","node_packages":["open-source-greenplum-db-7"]}'

Configuration

To define a Greenplum cluster, you need to use pg_mode = gpsql and additional identity parameters pg_shard and gp_role.

#================================================================#
#                        GPSQL Clusters                          #
#================================================================#

#----------------------------------#
# cluster: mx-mdw (gp master)
#----------------------------------#
mx-mdw:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary , nodename: mx-mdw-1 }
  vars:
    gp_role: master          # this cluster is used as greenplum master
    pg_shard: mx             # pgsql sharding name & gpsql deployment name
    pg_cluster: mx-mdw       # this master cluster name is mx-mdw
    pg_databases:
      - { name: matrixmgr , extensions: [ { name: matrixdbts } ] }
      - { name: meta }
    pg_users:
      - { name: meta , password: DBUser.Meta , pgbouncer: true }
      - { name: dbuser_monitor , password: DBUser.Monitor , roles: [ dbrole_readonly ], superuser: true }

    pgbouncer_enabled: true                # enable pgbouncer for greenplum master
    pgbouncer_exporter_enabled: false      # enable pgbouncer_exporter for greenplum master
    pg_exporter_params: 'host=127.0.0.1&sslmode=disable'  # use 127.0.0.1 as local monitor host

#----------------------------------#
# cluster: mx-sdw (gp master)
#----------------------------------#
mx-sdw:
  hosts:
    10.10.10.11:
      nodename: mx-sdw-1        # greenplum segment node
      pg_instances:             # greenplum segment instances
        6000: { pg_cluster: mx-seg1, pg_seq: 1, pg_role: primary , pg_exporter_port: 9633 }
        6001: { pg_cluster: mx-seg2, pg_seq: 2, pg_role: replica , pg_exporter_port: 9634 }
    10.10.10.12:
      nodename: mx-sdw-2
      pg_instances:
        6000: { pg_cluster: mx-seg2, pg_seq: 1, pg_role: primary , pg_exporter_port: 9633  }
        6001: { pg_cluster: mx-seg3, pg_seq: 2, pg_role: replica , pg_exporter_port: 9634  }
    10.10.10.13:
      nodename: mx-sdw-3
      pg_instances:
        6000: { pg_cluster: mx-seg3, pg_seq: 1, pg_role: primary , pg_exporter_port: 9633 }
        6001: { pg_cluster: mx-seg1, pg_seq: 2, pg_role: replica , pg_exporter_port: 9634 }
  vars:
    gp_role: segment               # these are nodes for gp segments
    pg_shard: mx                   # pgsql sharding name & gpsql deployment name
    pg_cluster: mx-sdw             # these segment clusters name is mx-sdw
    pg_preflight_skip: true        # skip preflight check (since pg_seq & pg_role & pg_cluster not exists)
    pg_exporter_config: pg_exporter_basic.yml                             # use basic config to avoid segment server crash
    pg_exporter_params: 'options=-c%20gp_role%3Dutility&sslmode=disable'  # use gp_role = utility to connect to segments

Additionally, PG Exporter requires extra connection parameters to connect to Greenplum Segment instances for metric collection.

17 - Neon

Neon adopts a storage and compute separation architecture, providing seamless autoscaling, scale to zero, and unique database branching capabilities.

Neon official website: https://neon.tech/

Neon binaries are currently too large to include in the open-source package set. This support path remains in pilot stage; contact Pigsty sales if you need it.