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 a variant that provides specialized features.

Pigsty supports multiple PostgreSQL kernels and compatibility branches within one operational framework. These kernels provide compatibility layers, multi-master replication, graph queries, MPP warehousing, transparent encryption, and other features.

The level of Pigsty integration differs between kernels. PostgreSQL, Citus, Babelfish, IvorySQL, PolarDB, AgensGraph, and pgEdge have defined templates and configuration procedures. Cloudberry and Greenplum usually use gpsql mode. For their MPP initialization and scale-out operations, upstream tools remain the preferred method.

Kernel	Key Feature	Description
PostgreSQL	Native kernel, full extension set	Vanilla PostgreSQL with 600 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 a widely used open-source database.

Pigsty installs PostgreSQL 18 by default. It supports PostgreSQL 14 ~ 18 and provides 600 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 the PostgreSQL kernel by default. Examples include:

• 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

The native PostgreSQL kernel requires no special configuration changes:

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 select a different PostgreSQL major version, use -v:

./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, uninstall it before you install a new version:

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

Extension Ecosystem

Pigsty provides PostgreSQL extensions. See the Extension Catalog for details.

2 - Supabase

Supabase — Build in a weekend, Scale to millions

Supabase is an open-source Firebase alternative built on PostgreSQL. It provides authentication, ready-to-use APIs, edge functions, real-time subscriptions, object storage, and vector embeddings. The low-code backend platform can replace much of the backend development work. Database design and frontend knowledge can be sufficient to build a product.

Supabase’s motto is: “Build in a weekend, Scale to millions”. Its cloud service can be cost-effective for small deployments, such as 4c8g. For millions of users, consider self-hosting to meet functionality, performance, and cost requirements.

Pigsty provides an automated solution to self-host the complete Supabase stack. It includes PostgreSQL monitoring, IaC, PITR, and high availability. Compared with Supabase cloud services, this solution provides up to 600 ready-to-use PostgreSQL extensions. It can also make fuller use of the performance and cost advantages of modern hardware.

For the complete procedure, see: Supabase Self-Hosting Guide

Quick Start

Pigsty’s default supabase.yml configuration template defines a single-node Supabase. The current application combination tracks self-hosted/v0.8.2 (commit 564eab8), with Envoy and external Pigsty-managed PostgreSQL 18 as the defaults. The template still supports PostgreSQL 15–18. See the self-hosting guide for complete versions and the upgrade procedure for existing deployments.

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; cd ~/pigsty
./configure -c supabase  # Important: modify passwords and other key info in config!
./deploy.yml         # Install Pigsty, PostgreSQL, and Silo

Before you deploy Supabase, adjust the Supabase parameters in pigsty.yml for your environment. In particular, change the passwords.

Then run docker.yml and app.yml to deploy the Supabase containers:

./docker.yml -l supabase  # Install Docker on the supabase group
./app.yml -l supabase     # Start stateless containers on the supabase group

In China, configure Docker mirrors or proxy servers to bypass GFW restrictions when you pull DockerHub images. Professional subscriptions include offline installation of Pigsty and Supabase without internet access.

By default, Pigsty exposes web services through Nginx on the administration node/INFRA node. Add local DNS resolution that points supa.pigsty to this node. Then open https://supa.pigsty in a browser to access the Supabase Studio management interface.

Default username and password: supabase / pigsty

demo/supabase.cast — /demo/supabase.cast

Configuration Details

./configure -c supabase generates ~/pigsty/pigsty.yml. Before running ./deploy.yml, review and update at least the passwords, keys, and domains in this file.

For complete configuration guidance, see the Supabase Self-Hosting Guide.

3 - Babelfish

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

Overview

Deploy Babelfish in mssql mode to add these features to 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. Babelfish is part of Pigsty’s standard delivery process and supports all Linux platforms.

Current Behavior

The current behavior differs from older Babelfish/PG15 documentation as follows:

• 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

Through Pigsty services, you can also use these ports:

• 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.
• Some native PostgreSQL extensions cannot run directly on Babelfish kernels. Verify package availability and compatibility first.
• For production, restrict HBA rules and network access. Do not keep the open rules from the demonstration configuration.

Related Docs

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

Available Extensions

The Babelfish kernel has 55 available extensions. The following table lists the extra extensions, excluding bundled PG Contrib extensions:

4 - Percona

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

From v4.4.0, Pigsty installs Percona PostgreSQL under the private prefix /usr/pgtde-$v. Version v4.5.0 keeps this layout, with /usr/pgtde-18 for PostgreSQL 18. The pgtde package alias installs both the kernel package and its contrib package. These packages include 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

To deploy a Percona cluster, adjust these parameters:

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'

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

Available Extensions

The Percona Postgres kernel has 73 available extensions. The following table lists the extra extensions, excluding bundled PG Contrib extensions:

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 ]

The OpenHalo extension aux_mysql provides functions and types for MySQL compatibility. Enable it in the postgres database for full compatibility behavior.

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

Usage

MySQL connections use the postgres database. A MySQL database maps to a PostgreSQL schema. Thus, use mysql selects the mysql schema in the postgres database.

Use the PostgreSQL usernames and passwords for MySQL access.

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

OpenHalo upstream currently reports that Navicat works normally on this port. IntelliJ DataGrip may fail.

Compatibility Parameters

Pigsty sets database_compat_mode to mysql by default. To adjust compatibility behavior, use settings such as:

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

Pigsty packages the OpenHalo kernel from HaloTech-Co-Ltd/openHalo with these 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. Contact the upstream vendor about kernel-specific issues.

Warning: This kernel is currently in beta1. Evaluate its risks carefully before production use.

Available Extensions

The OpenHalo kernel has 59 available extensions. The following table lists the extra extensions, excluding bundled PG Contrib extensions:

6 - OrioleDB

OrioleDB is a PostgreSQL storage engine extension. It claims to provide 4x OLTP performance and to eliminate xid wraparound and table bloat issues. It also claims to provide cloud-native storage, with data stored in S3.

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

You can run OrioleDB as an RDS with Pigsty. Current packages support PostgreSQL 16, 17, and 18 on supported Linux platforms. Set pg_mode to oriole to use the /usr/oriole-$v installation path. 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, change the password parameters in pigsty.yml before you run 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 uses pg_version to select the PG16, PG17, or PG18 OrioleDB kernel package.

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, rebuild these tables with the orioledb storage engine and compare performance:

-- 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. The following table lists the extra extensions, excluding bundled PG Contrib extensions:

7 - Cloudberry

Cloudberry is an open-source MPP data warehouse kernel derived from the Greenplum ecosystem. It supports 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

Pigsty currently provides package delivery, node management, monitoring integration, access control, and configuration management for Cloudberry. For MPP cluster initialization, scale-out, rebalance, and other upstream operations, you should use the official Cloudberry tools.

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

Installation

There is no standalone cloudberry deployment template yet. The usual procedure 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"]}'

For an existing Cloudberry cluster, usually keep the original initialization procedure. Add the Pigsty inventory and monitoring configuration incrementally.

Configuration

Cloudberry uses gpsql mode. There is no dedicated cloudberry mode. In addition to standard PostgreSQL identity parameters, you need pg_shard and gp_role. To label shard groups explicitly, you can also add pg_group.

This example shows a minimal topology:

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'

Apply these role settings:

• Use gp_role: master for the coordinator / master node. Applications usually connect to this node.
• gp_role: segment nodes usually need pg_exporter to connect in utility mode for monitoring.

Client Access

Cloudberry uses the PostgreSQL wire protocol for application and BI access. Most PostgreSQL-compatible clients, drivers, and BI tools can connect without special configuration.

Use these access guidelines:

• 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 differs from native PostgreSQL with added extensions. For extension packages in Pigsty, consider these 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. They may also need recompilation with the Cloudberry version and toolchain.

If your workload requires postgis, vector extensions, FDWs, auditing, or custom C extensions, first validate them on the target Cloudberry version. Do not copy a native PostgreSQL extension list without this check.

Notes

• Cloudberry currently has no dedicated Pigsty template. You should define its configuration manually with gpsql mode.
• Pigsty currently provides packages, configuration, and monitoring. Use the official Cloudberry tools for MPP initialization and scale-out.
• Cloudberry is an MPP distributed kernel. Native PostgreSQL operating assumptions may not apply 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. It supports 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

AgensGraph uses the PostgreSQL wire protocol. Standard PostgreSQL clients, drivers, and connection pools can connect directly. AgensGraph adds graph objects, Cypher syntax, and the agtype data type to the database.

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, unlike pgEdge or Babelfish. Most standard Pigsty procedures for HA, backup, monitoring, access control, and IaC remain applicable. If graph traversal and complex path queries dominate the workload, examine work_mem, shared_buffers, and planner cost settings. Do not assume that the default OLTP settings are suitable.

Usage

After you connect to the database, usually create a graph first 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);

Projects commonly combine relational tables, graph labels, and Cypher queries. Transactions, privileges, and backup procedures still follow PostgreSQL. Graph analysis uses AgensGraph graph objects and the cypher() interface.

Notes

• AgensGraph currently uses the PG17-compatible line. Do not assume that PG18 extensions are available.
• The default agens template uses one node for quick validation. Production deployments should use an HA topology.
• Third-party PostgreSQL extensions may be incompatible with AgensGraph. Verify compatibility first.
• Graph objects and relational objects can coexist in one database. For production, clear database or naming conventions usually help you distinguish them.
• Tune memory and cost parameters for the size of your graph model. Check that the defaults are suitable before you use them.
• 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. The following table lists the extra extensions, excluding bundled PG Contrib extensions:

9 - pgEdge

pgEdge is a distributed PostgreSQL distribution for edge deployments. It uses 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 supplies the versioned kernel packages pgedge-15, pgedge-16, pgedge-17, and pgedge-18. The template defaults to pg_version: 18. The pgedge-$v kernel package includes the control files, SQL files, and shared libraries for spock, snowflake, and lolor. Thus, these extensions are no longer separate packages in pg_extensions. pgEdge supports the PostgreSQL wire protocol, so psql, JDBC/ODBC, DBeaver, and similar tools work as usual.

First validate the kernel on one node. Then expand to a replication topology with multiple nodes. The template configures the kernel, extensions, monitoring, backup, and access control. Design the multi-master topology for your workload consistency requirements and conflict strategy.

Installation

Use the built-in Pigsty template:

./configure -c pgedge
./deploy.yml

The template 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] }

For a future multi-node multi-master topology, we recommend explicit settings for logical replication capacity and snowflake.node:

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

First validate the kernel on one node. Then expand to a Spock replication topology with multiple nodes.

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 uses serial or identity, plan the snowflake sequence migration before you enable multi-master writes. Otherwise, primary key collisions between nodes are likely.

Notes

• Configure pgEdge replication for each database. There is no instance-wide switch to enable multi-master replication for all databases.
• 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 usually require superuser privileges. Production deployments should define privilege boundaries clearly.
• If your workload requires large object replication, use lolor explicitly. Do not assume that native large objects will replicate correctly.
• Before you configure multi-master replication across regions, evaluate network latency, conflict handling, and the write model.

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. The following table lists the extra extensions, excluding bundled PG Contrib extensions:

10 - DocumentDB

Microsoft maintains DocumentDB, an open-source PostgreSQL document database extension. FerretDB is a stateless protocol translation proxy built on DocumentDB. Together, they provide a MongoDB wire-compatible endpoint for a standard PostgreSQL kernel. Applications can connect directly with MongoDB drivers. FerretDB translates their requests into PostgreSQL operations.

This stack is not a standalone PostgreSQL fork. The data layer uses native PostgreSQL 16–18, which the standard PGSQL module manages. PostgreSQL provides persistence, transactions, high availability, backup, monitoring, and access control. FerretDB runs as a Pigsty Docker APP and handles only protocol translation.

Pigsty is a FerretDB community partner. It provides binary packages for FerretDB and the DocumentDB extensions. Use the mongo configuration template to deploy the complete stack.

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

By default, FerretDB listens on the local loopback address at port 27017. 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 runs as a Docker APP. Set its configuration overrides under apps.ferretdb.conf. 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

FerretDB is fully stateless, so its HA topology follows the standard PostgreSQL cluster pattern. The template includes a commented three-node pg-mongo example. Each node runs a FerretDB container on local port 27018. HAProxy routes access to these containers through the floating endpoint 10.10.10.4:27017 (mongo.pigsty).

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

Notes

• FerretDB enables authentication by default (FERRETDB_AUTH: true). It does not yet implement MongoDB authorization roles. PostgreSQL users and HBA rules control access.
• Client-side MongoDB TLS is disabled by default. 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. Use the standard extension installation procedure to install and enable it on a native PostgreSQL cluster.

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

Configuration

To define a Citus cluster, set these 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
• You can run administration commands as postgres from pg_dbsu, instead of the default pg_admin_username. If you use this account, you must set pg_dbsu_password to a non-empty plaintext password.

You also need HBA rules that allow SSL access from localhost and other data nodes.

You can define each Citus cluster as a separate group, as with 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

Connect to any node as you would connect to 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 to one shard affect only that cluster. These changes do not synchronize to other shards.

To distribute writes across all shards, use Citus API functions to mark tables as one of these types:

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

From Citus 11.2, any Citus database node can act as coordinator. Thus, 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, this full table scan shows a distributed execution 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, Patroni promotes the standby node. The standby automatically takes over through native high availability support.

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.

Overview

The Pigsty PGSQL repository provides IvorySQL 5.4 packages compatible with PostgreSQL 18.4. Packages cover the currently supported EL, Debian, and Ubuntu platforms on both architectures. Online installation uses Pigsty’s pgsql repository. The Professional Edition also provides offline delivery for these platforms.

Pigsty’s ivorysql package alias points to IvorySQL 5, which is compatible with PostgreSQL 18. Platform variables under roles/node_id/vars/ map the alias to package names. For example, EL uses ivorysql5, and Debian/Ubuntu uses ivorysql-5.

IvorySQL 3.3 was the last version to support EL7. It corresponds to PostgreSQL 16.3. IvorySQL 4.4 is the last version based on PostgreSQL 17.

Installation

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

./configure -c ivory
./deploy.yml

Configuration

Set these parameters for an IvorySQL database cluster:

#----------------------------------#
# 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

For Oracle compatibility mode, dynamically load the liboracle_parser extension plugin.

Client Access

IvorySQL 5 is equivalent to PostgreSQL 18. Any client tool that supports the PostgreSQL wire protocol can connect to IvorySQL clusters.

Available Extensions

The IvorySQL kernel has 95 available extensions. The following table lists the extra extensions, excluding bundled PG Contrib extensions:

Pigsty does not assume any warranty responsibility for use of the IvorySQL kernel. Contact the original vendor about issues or requirements for this kernel.

13 - PolarDB PG

Overview

Use Pigsty and PolarDB to create PostgreSQL clusters with “domestic innovation qualification” credentials.

PolarDB for PostgreSQL now uses PostgreSQL 17 as its base. The Pigsty polar template, default path, and extension notes now use PG17. Any client tool that supports the PostgreSQL wire protocol can connect to PolarDB clusters.

Pigsty’s PGSQL repository provides PolarDB PG open-source installation packages. Pigsty does not download these packages to the local software repository during installation.

Installation

Use the built-in Pigsty template:

./configure -c polar
./deploy.yml

Change Summary

From Pigsty v4.4, the PolarDB PG kernel uses packages that Pigsty builds and maintains. The main changes are:

Configuration

Set these parameters 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 is now /usr/polar-17. PolarDB PG still requires the replicator replication user to be SUPERUSER. Native PostgreSQL does not have this requirement.

Available Extensions

The PolarDB PG kernel has 93 available extensions. The following table lists the extra extensions, excluding bundled PG Contrib extensions:

14 - PolarDB Oracle

Use Pigsty to create PolarDB for Oracle clusters with “domestic innovation qualification” credentials.

The Security and Reliability Evaluation Results Announcement (No. 1, 2023) lists PolarDB v2.0 in Appendix 3, Centralized Database. It identifies PolarDB v2.0 as an autonomous, controllable, secure, and reliable domestic innovation database.

PolarDB for Oracle is an Oracle-compatible version based on PolarDB for PostgreSQL. Both use the same kernel. The --compatibility-mode parameter selects the mode.

We work with the Alibaba Cloud kernel team to provide a complete database solution with the PolarDB v2.0 kernel and Pigsty. For inquiries, contact sales. You can also purchase it on Alibaba Cloud Marketplace.

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

Extensions

The PolarDB 2.0 kernel in Oracle compatibility mode currently includes these 188 extension plugins:

15 - PostgresML

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

PostgresML (pgml) is written in Rust. You can run standalone Docker images. This page is reference documentation and does not describe a docker-compose template.

PostgresML officially supports Ubuntu 22.04. We also maintain RPM versions for EL 8/9, for use without 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 following example uses PG17. For PG14-16, set pg_version to the applicable 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 maps the pgml package alias to pgml_$v on EL and postgresql-$v-pgml on Debian/Ubuntu. You must also 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 must also install PostgresML’s Python dependencies on the cluster nodes. See the 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. 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

Install the pgml extension and Python dependencies on all cluster nodes first. Then enable pgml on the PostgreSQL cluster.

Use patronictl to configure the cluster. Add pgml to shared_preload_libraries. Set pgml.venv to your virtual environment directory:

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 these commands succeed, the output should be similar to this:

# 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

For more details, see the official PostgresML documentation: https://postgresml.org/docs/guides/use-cases/

16 - Greenplum

Pigsty supports deployment of Greenplum clusters and the derivative distribution YMatrixDB. You can also add existing Greenplum deployments to Pigsty monitoring.

Overview

Only the professional/enterprise editions provide Greenplum / YMatrix cluster deployment. This capability is 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, set pg_mode = gpsql. Also set the 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

PG Exporter also requires extra connection parameters to collect metrics from Greenplum Segment instances.

17 - Neon

Neon separates storage from compute. It provides automatic scaling, scale-to-zero operation, and database branching.

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

Neon binaries are currently too large for the open-source package set. This support remains in the pilot stage. If you need it, contact Pigsty sales.