Skip to content

This is the multi-page printable view of this section. .

Return to the regular view of this page.

Kernel

Replace vanilla PostgreSQL with exotic kernel forks

Pigsty supports various PostgreSQL kernels and compatible forks, enabling you to simulate different database systems while leveraging PostgreSQL’s ecosystem. Each kernel provides unique capabilities and compatibility layers.

Database Kernels

PostgreSQL

Vanilla Postgres with 437 Extensions

Citus

Native Distributive Extension

Babelfish

SQL Server wire-compatible

IvorySQL

Oracle grammar & PL/SQL compatible

OpenHalo

MySQL wire-compatibility

Percona

Transparent Data Encryption

OrioleDB

OLTP-optimized cloud-native storage engine

PolarDB PG

Aurora-like RAC with china domestic compliance

Supabase

Backend as a Service, self-hosting Firebase

FerretDB

Mongo Wire-Compatibility over PostgreSQL

Choose the Right Kernel

Note

Flexible Kernel: Choose the right kernel for your specific use case - whether you need MSSQL compatibility, Oracle features, or horizontal scaling capabilities.

Kernel Key Feature Description
PostgreSQL Original Flavor Vanilla PostgreSQL with 437 extensions
Citus Horizontal Scaling Distributive PostgreSQL via native extension
WiltonDB SQL Server Migration SQL Server wire-compatibility
IvorySQL Oracle Migration Oracle Grammar and PL/SQL compatible
OpenHalo MySQL Migration MySQL wire-protocol compatibility
Percona Transparent Data Encryption Percona Distribution with pg_tde
FerretDB MongoDB Migration MongoDB wire-protocol compatibility
OrioleDB OLTP Optimization Zheap, No bloat, S3 Storage
PolarDB Aurora flavor RAC RAC, China domestic compliance
Supabase Backend as Service BaaS based on PostgreSQL, Firebase alternative
Cloudberry (WIP) MPP DW & Analytics Massively parallel processing database warehouse

Citus (Distributive)

Citus Native Distributive

Citus transforms PostgreSQL into a distributed database system, enabling horizontal scaling across multiple nodes. Deploy native HA Citus clusters with Pigsty for better throughput and performance.

Key Features

  • Distributed Tables: Automatically shard tables across worker nodes
  • Distributed Queries: Execute queries across the entire cluster
  • High Availability: Built-in replication and failover capabilities
  • Real-time Analytics: Handle both transactional and analytical workloads
  • Postgres Compatibility: Maintain full PostgreSQL feature compatibility

Use Cases

  • Multi-tenant SaaS applications requiring horizontal scaling
  • Real-time analytics on large datasets
  • High-throughput OLTP workloads
  • Applications need to scale beyond single-node limitations
Note

Planning Required: Proper shard key selection is crucial for optimal performance and avoiding cross-shard queries.

Babelfish (MSSQL)

Babelfish SQL Server Wire Compatible

Note

SQL Server Compatible

Note

Beta

Create SQL Server-compatible PostgreSQL clusters with WiltonDB and Babelfish, providing wire protocol-level compatibility with Microsoft SQL Server.

Key Features

  • T-SQL Support: Execute T-SQL queries natively
  • Wire Protocol Compatibility: Connect using SQL Server drivers and tools
  • Stored Procedures: Support for T-SQL stored procedures and functions
  • Data Types: Compatible with SQL Server data types and behaviors
  • Migration Tools: Simplified migration from SQL Server environments

Use Cases

  • Migrating legacy SQL Server applications to PostgreSQL
  • Multi-database environments requiring SQL Server compatibility
  • Cost reduction while maintaining application compatibility
  • Cloud migration from SQL Server to open-source alternatives
Note

Migration Path: Ideal for organizations looking to reduce licensing costs while maintaining existing SQL Server application compatibility.

IvorySQL (Oracle)

Babelfish Oracle Grammar Compatible

Run Oracle-compatible PostgreSQL clusters with the IvorySQL kernel, open-sourced by HighGo, providing Oracle syntax and feature compatibility.

Key Features

  • PL/SQL Support: Execute PL/SQL code with minimal modifications
  • Oracle Syntax: Support for Oracle-specific SQL syntax and functions
  • Package Support: Oracle-style package and procedure definitions
  • Data Types: Oracle-compatible data types and behaviors
  • Migration Tools: Utilities for Oracle to PostgreSQL migration

Use Cases

  • Oracle database migration projects
  • Organizations seeking Oracle feature compatibility
  • Cost optimization while preserving Oracle functionality
  • Development environments requiring Oracle compatibility
Note

Enterprise Focus: Particularly valuable for enterprises with significant Oracle investments looking for migration paths.

OpenHalo (MySQL)

OpenHalo MySQL Wire-Compatible

The OpenHalo kernel provides MySQL-compatible PostgreSQL functionality, accessible using standard MySQL clients and protocols.

Key Features

  • MySQL Protocol: Wire-level compatibility with MySQL protocol
  • Client Compatibility: Use existing MySQL drivers and tools
  • SQL Dialect: Support for MySQL-specific SQL syntax
  • Migration Support: Simplified migration from MySQL environments
  • Ecosystem Integration: Leverage PostgreSQL’s advanced features with MySQL compatibility

Use Cases

  • MySQL application migration to PostgreSQL
  • Multi-database environments requiring MySQL compatibility
  • Leveraging PostgreSQL features while maintaining MySQL interface
  • Gradual migration strategies from MySQL to PostgreSQL
Note

Early Stage: Currently experimental - evaluate thoroughly before production use.

OrioleDB (OLTP)

OrioleDB OLTP Optimized Cloud Native

A PostgreSQL storage engine optimized for OLTP workloads, eliminating transaction ID wraparound issues and table bloat while supporting cloud storage.

Compatible with PostgreSQL 17, Available on all support platforms.

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 Optimization: Specifically designed for transactional workloads
  • Improved Performance: Better space utilization and query performance

Use Cases

  • High-frequency transactional applications
  • Cloud-native deployments requiring object storage
  • Applications suffering from PostgreSQL maintenance overhead
  • Systems requiring consistent performance without vacuum cycles
Note

Early Stage: Currently in Beta - evaluate thoroughly before production use.

PolarDB PG (RAC)

PolarDB Aurora Flavor RAC

Replace vanilla PostgreSQL with PolarDB PG, an open-source Aurora-like solution similar to Oracle RAC with shared storage architecture.

Key Features

  • Shared Storage: Multiple compute nodes sharing the same storage layer
  • Read Scaling: Add read replicas without storage duplication
  • Fast Recovery: Rapid recovery through shared storage architecture
  • Cost Efficiency: Reduced storage costs through sharing
  • High Availability: Built-in failover and disaster recovery

Use Cases

  • Applications requiring extreme read scalability
  • Cost-sensitive deployments needing high availability
  • Cloud environments with shared storage infrastructure
  • Workloads with variable read/write patterns
Note

Cloud Architecture: Designed for cloud environments with disaggregated compute and storage.

Supabase (Firebase)

Supabase Backend as a Service

Self-host Supabase with existing managed HA PostgreSQL clusters, launching the stateless components with docker-compose for a complete Firebase alternative.

Key Features

  • Real-time APIs: Auto-generated REST and GraphQL APIs
  • Real-time Subscriptions: WebSocket-based real-time data sync
  • Authentication: Built-in user authentication and authorization
  • Storage: File storage with CDN capabilities
  • Edge Functions: Serverless functions for custom logic

Use Cases

  • Rapid application development with backend-as-a-service
  • Real-time applications requiring instant data sync
  • JAMstack applications needing serverless backend
  • Mobile and web apps require authentication and storage
Note

Full Stack: Provides a complete backend solution with PostgreSQL as the foundation.

Cloudberry (MPP)

Cloudberry MPP Data Warehouse

Install and monitor Greenplum / Cloudberry / YMatrix MPP clusters with Pigsty for large-scale analytical processing and data warehousing.

Key Features

  • Massively Parallel Processing: Distribute queries across multiple nodes
  • Columnar Storage: Optimized storage for analytical workloads
  • Advanced Analytics: Built-in machine learning and statistical functions
  • Petabyte Scale: Handle massive datasets with linear scalability
  • Standard SQL: Full SQL compliance with PostgreSQL compatibility

Use Cases

  • Data warehousing and business intelligence
  • Large-scale analytics and reporting
  • Machine learning on big datasets
  • ETL processing for enterprise data platforms
Note

Enterprise Analytics: Designed for enterprise-scale analytical workloads requiring massive parallel processing capabilities.

1 - PostgreSQL

The vanilla PostgreSQL kernel with 437 extensions

PostgreSQL is the most advanced & popular open source database in the world.

Pigsty supports PostgreSQL 13 ~ 18, and provides 437 extensions alone with it.

Get Started

install Pigsty’s with the pgsql config template.

curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty;
./configure -c pgsql     # use vanilla postgres kernel
./install.yml            # setup everything with pigsty

Most of the config template is use the PostgreSQL kernel by default, such as:

  • meta : DEFAULT, postgres with core extensions (vector, postgis, timescale)
  • rich : postgres with all extensions installed
  • slim : postgres only without monitor infra
  • full : the 4-node sandbox for HA demonstration
  • pgsql : the minimal postgres kernel config example (THIS CONFIG)

Configure

Nothing special needs to be tuned for vanilla PostgreSQL kernel:

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' }
    node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am
    pg_packages: [ pgsql-main, pgsql-common ]   # pg kernel and common utils
    #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]

To use a different PostgreSQL major version, you can configure with -v parameter:

./configure -c pgsql            # the default is 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
./configure -c pgsql -v 13      # use postgresql 13

If PostgreSQL cluster is already installed, you’ll need to uninstall it before installing the new version

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

PostgreSQL beta

To use PostgreSQL beta version (19 not released), you’ll also need to add the beta repo to your node_repo_modules (or repo_modules if you are building a local repo)

The configure will do that for you if you are use the -v 19 argument:

./configure -c pgsql -v 19     # use the postgresql 19 kernel (beta not released yet)
./install.yml                  # setup everything with pigsty

Beware Pigsty is not build extensions for PostgreSQL 19 yet, so only those existing extensions in the PGDG repo are currently available. We will start building them after the PostgreSQL 19 is released.

Multi-Node

To setup a multi-node PostgreSQL cluster, you can check the PGSQL: Configure for details:

This is the example 3-node pg-test cluster in the full config template:

2 - Citus

Native Distributive Extension for PostgreSQL Sharding

Citus is a PostgreSQL extension that transforms PostgreSQL into a distributed database, enabling horizontal scaling across multiple nodes to handle large amounts of data and queries.

Since Patroni v3.0, native support for Citus high availability has been provided, simplifying the setup of Citus clusters. Pigsty also offers native support for this.

Pigsty v3.7.0 pins the Citus template to PostgreSQL 17; Citus packages are not available for PostgreSQL 18 in this release.

Citus Cluster

Pigsty natively supports Citus. Refer to conf/citus.yml.

This example uses a four-node sandbox with a Citus cluster named pg-citus, consisting of a two-node coordinator cluster pg-citus0 and two worker clusters pg-citus1 and pg-citus2.

pg-citus:
  hosts:
    10.10.10.10: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.2/24 ,pg_seq: 1, pg_role: primary }
    10.10.10.11: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.2/24 ,pg_seq: 2, pg_role: replica }
    10.10.10.12: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.3/24 ,pg_seq: 1, pg_role: primary }
    10.10.10.13: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.4/24 ,pg_seq: 1, pg_role: primary }
  vars:
    pg_mode: citus                            # pgsql cluster mode: citus
    pg_version: 17                            # Citus is not available for PG18 in v3.7.0
    pg_shard: pg-citus                        # Citus shard name: pg-citus
    pg_primary_db: citus                      # primary database used by Citus
    pg_vip_enabled: true                      # enable VIP for Citus cluster
    pg_vip_interface: eth1                    # VIP interface for all members
    pg_dbsu_password: DBUser.Postgres         # all DBSU passwords for Citus cluster
    pg_extensions: [ citus, postgis, pgvector, topn, pg_cron, hll ]  # install these extensions
    pg_libs: 'citus, pg_cron, pg_stat_statements' # Citus will be added automatically by Patroni
    pg_users: [{ name: dbuser_citus ,password: DBUser.Citus ,pgbouncer: true ,roles: [ dbrole_admin ]    }]
    pg_databases: [{ name: citus ,owner: dbuser_citus ,extensions: [ citus, vector, topn, pg_cron, hll ] }]
    pg_parameters:
      cron.database_name: citus
      citus.node_conninfo: 'sslmode=require sslrootcert=/pg/cert/ca.crt sslmode=verify-full'
    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'  }

Compared to a standard PostgreSQL cluster, Citus cluster configuration has some specific requirements. First, ensure that the Citus extension is downloaded, installed, loaded, and enabled. This involves the following four parameters:

  • repo_packages: Must include the citus extension, or you need to use a PostgreSQL offline package with the Citus extension.
  • pg_extensions: Must include the citus extension, meaning you need to install the citus extension on each node.
  • pg_libs: Must include the citus extension, and it must be first in the list, but now Patroni will automatically handle this.
  • pg_databases: Define a primary database with the citus extension installed.

Additionally, ensure the configuration for the Citus cluster is correct:

  • pg_mode: Must be set to citus to inform Patroni to use the Citus mode.
  • pg_primary_db: Specify the primary database name, which must have the citus extension (named citus here).
  • pg_shard: Specify a unified name as a prefix for all horizontal shard PG clusters (e.g., pg-citus).
  • pg_group: Specify a shard number, starting from zero for the coordinator cluster and incrementing for worker clusters.
  • pg_cluster: Must match the combination of [pg_shard] and [pg_group].
  • pg_dbsu_password: Set a non-empty plain-text password for proper Citus functionality.
  • pg_parameters: It is recommended to set the citus.node_conninfo parameter, which enforces SSL access and requires node-to-node client certificate verification.

Once configured, deploy the Citus cluster just like a regular PostgreSQL cluster using pgsql.yml.

Managing Citus Clusters

After defining the Citus cluster, use the same playbook pgsql.yml to deploy the Citus cluster:

./pgsql.yml -l pg-citus    # Deploy Citus cluster pg-citus

Any DBSU user (postgres) can use patronictl (alias: pg) to list the status of the Citus cluster:

$ pg list
+ Citus cluster: pg-citus ----------+---------+-----------+----+-----------+--------------------+
| Group | Member      | Host        | Role    | State     | TL | Lag in MB | Tags               |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     0 | pg-citus0-1 | 10.10.10.10 | Leader  | running   |  1 |           | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 20C.40G.125G |
|       |             |             |         |           |    |           | version: '17'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     1 | pg-citus1-1 | 10.10.10.11 | Leader  | running   |  1 |           | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 10C.20G.125G |
|       |             |             |         |           |    |           | version: '17'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     2 | pg-citus2-1 | 10.10.10.12 | Leader  | running   |  1 |           | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 10C.20G.125G |
|       |             |             |         |           |    |           | version: '17'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     2 | pg-citus2-2 | 10.10.10.13 | Replica | streaming |  1 |         0 | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 10C.20G.125G |
|       |             |             |         |           |    |           | version: '17'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+

Each horizontal shard cluster can be treated as a separate PGSQL cluster, managed with the pg (patronictl) command. Note that when using pg to manage the Citus cluster, the --group parameter must be used to specify the cluster shard number:

pg list pg-citus --group 0   # Use --group 0 to specify the shard number

Citus has a system table called pg_dist_node to record node information, which Patroni automatically maintains.

PGURL=postgres://postgres:[email protected]/citus

psql $PGURL -c 'SELECT * FROM pg_dist_node;'       # View node information

Additionally, you can view user authentication information (restricted to superusers):

$ psql $PGURL -c 'SELECT * FROM pg_dist_authinfo;'   # View node authentication info (superuser only)

You can then access the Citus cluster with regular business users (e.g., dbuser_citus with DDL permissions):

psql postgres://dbuser_citus:[email protected]/citus -c 'SELECT * FROM pg_dist_node;'

Using the Citus Cluster

When using a Citus cluster, we highly recommend reading the Citus Official Documentation to understand its architecture and core concepts.

Key to this is understanding the five types of tables in Citus, their characteristics, and use cases:

  • Distributed Table
  • Reference Table
  • Local Table
  • Local Management Table
  • Schema Table

On the coordinator node, you can create distributed and reference tables and query them from any data node. Since version 11.2, any Citus database node can act as a coordinator.

We can use pgbench to create some tables, distributing the main table (pgbench_accounts) across the nodes, and using other smaller tables as reference tables:

PGURL=postgres://dbuser_citus:[email protected]/citus
pgbench -i $PGURL

psql $PGURL <<-EOF
SELECT create_distributed_table('pgbench_accounts', 'aid'); SELECT truncate_local_data_after_distributing_table('public.pgbench_accounts');
SELECT create_reference_table('pgbench_branches')         ; SELECT truncate_local_data_after_distributing_table('public.pgbench_branches');
SELECT create_reference_table('pgbench_history')          ; SELECT truncate_local_data_after_distributing_table('public.pgbench_history');
SELECT create_reference_table('pgbench_tellers')          ; SELECT truncate_local_data_after_distributing_table('public.pgbench_tellers');
EOF

Run read-write bench:

pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus      # 直连协调者 5432 端口
pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]:6432/citus # 通过连接池，减少客户端连接数压力，可以有效提高整体吞吐。
pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus      # 任意 primary 节点都可以作为 coordinator
pgbench --select-only -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus # 可以发起只读查询

Production Deployment

Production citus deployment usually requires physical replication for both coordinator and each worker cluster.

For example, in simu.yml there’s a 10-node cluster cluster:

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_version: 17                            # Citus is not available for PG18 in v3.7.0
    pg_shard: pg-citus                        # citus shard name: pg-citus
    pg_primary_db: citus                      # primary database used by citus
    pg_vip_enabled: true                      # enable vip for citus cluster
    pg_vip_interface: eth1                    # vip interface for all members
    pg_dbsu_password: DBUser.Postgres         # enable dbsu password access for citus
    pg_extensions: [ citus, postgis, pgvector, topn, pg_cron, hll ]  # install these extensions
    pg_libs: 'citus, pg_cron, pg_stat_statements' # citus will be added by patroni automatically
    pg_users: [{ name: dbuser_citus ,password: DBUser.Citus ,pgbouncer: true ,roles: [ dbrole_admin ]    }]
    pg_databases: [{ name: citus ,owner: dbuser_citus ,extensions: [ citus, vector, topn, pg_cron, hll ] }]
    pg_parameters:
      cron.database_name: citus
      citus.node_conninfo: 'sslrootcert=/pg/cert/ca.crt sslmode=verify-full'
    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'  }

We’ll cover a range of advanced topics in subsequent tutorials:

  • Read-write separation
  • Failover handling
  • Consistent backup and restore
  • Advanced monitoring and troubleshooting
  • Connection pool

3 - Babelfish

MS SQL Server Wire compatibility on PostgreSQL

Pigsty allows users to create a Microsoft SQL Server compatible PostgreSQL cluster using Babelfish and WiltonDB!

  • Babelfish: An open-source MSSQL (Microsoft SQL Server) compatibility extension Open Sourced by AWS
  • WiltonDB: A PostgreSQL kernel distribution focusing on integrating Babelfish

Babelfish is a PostgreSQL extension, but it works on a slightly modified PostgreSQL kernel Fork, WiltonDB provides compiled kernel binaries and extension binary packages on EL/Ubuntu systems.

Pigsty can replace the native PostgreSQL kernel with WiltonDB, providing an out-of-the-box MSSQL compatible cluster along with all the supported by common PostgreSQL clusters, such as HA, PITR, IaC, monitoring, etc.

WiltonDB is very similar to PostgreSQL 15, but it can not use vanilla PostgreSQL extensions directly. WiltonDB has several re-compiled extensions such as system_stats, pg_hint_plan and tds_fdw.

The cluster will listen on the default PostgreSQL port and the default MSSQL 1433 port, providing MSSQL services via the TDS WireProtocol on this port. You can connect to the MSSQL service provided by Pigsty using any MSSQL client, such as SQL Server Management Studio, or using the sqlcmd command-line tool.

Get Started

install Pigsty’s with the mssql config template.

curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty;
./configure -c mssql     # Use mssql (babelfish) template
./install.yml            # install everything with pigsty

For production deployments, make sure to modify the password parameters in the pigsty.yml config before running the install playbook.

Notes

When installing and deploying the MSSQL module, please pay special attention to the following points:

  • WiltonDB is available on EL (7/8/9) and Ubuntu (20.04/22.04) but not available on Debian systems.
  • WiltonDB is currently compiled based on PostgreSQL 15, so you need to specify pg_version: 15.
  • On EL systems, the wiltondb binary is installed by default in the /usr/bin/ directory, while on Ubuntu systems, it is installed in the /usr/lib/postgresql/15/bin/ directory, which is different from the official PostgreSQL binary location.
  • In WiltonDB compatibility mode, the HBA password authentication rule needs to use md5 instead of scram-sha-256. Therefore, you need to override Pigsty’s default HBA rule set and insert the md5 authentication rule required by SQL Server before the dbrole_readonly wildcard authentication rule.
  • WiltonDB can only be enabled for a primary database, and you should designate a user as the Babelfish superuser, allowing Babelfish to create databases and users. The default is mssql and dbuser_myssql. If you change this, you should also modify the user in files/mssql.sql.
  • The WiltonDB TDS cable protocol compatibility plugin babelfishpg_tds needs to be enabled in shared_preload_libraries.
  • After enabling the WiltonDB extension, it listens on the default MSSQL port 1433. You can override Pigsty’s default service definitions to redirect the primary and replica services to port 1433 instead of the 5432 / 6432ports.

The following parameters need to be configured for the MSSQL database cluster:

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - {name: dbuser_mssql ,password: DBUser.MSSQL ,superuser: true, pgbouncer: true ,roles: [dbrole_admin], comment: superuser & owner for babelfish  }
    pg_databases:
      - name: mssql
        baseline: mssql.sql
        extensions: [uuid-ossp, babelfishpg_common, babelfishpg_tsql, babelfishpg_tds, babelfishpg_money, pg_hint_plan, system_stats, tds_fdw]
        owner: dbuser_mssql
        parameters: { 'babelfishpg_tsql.migration_mode' : 'multi-db' }
        comment: babelfish cluster, a MSSQL compatible pg cluster
    node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am

    # Babelfish / WiltonDB Ad Hoc Settings
    pg_mode: mssql                     # Microsoft SQL Server Compatible Mode
    pg_version: 15
    pg_packages: [ wiltondb, pgsql-common, sqlcmd ]
    pg_libs: 'babelfishpg_tds, pg_stat_statements, auto_explain' # add timescaledb to shared_preload_libraries
    pg_default_hba_rules: # overwrite default HBA rules for babelfish cluster
      - { user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu access via local os user ident' }
      - { user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu replication from local os ident' }
      - { user: '${repl}'    ,db: replication ,addr: localhost ,auth: pwd   ,title: 'replicator replication from localhost' }
      - { user: '${repl}'    ,db: replication ,addr: intra     ,auth: pwd   ,title: 'replicator replication from intranet' }
      - { user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: pwd   ,title: 'replicator postgres db from intranet' }
      - { user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor from localhost with password' }
      - { user: '${monitor}' ,db: all         ,addr: infra     ,auth: pwd   ,title: 'monitor from infra host with password' }
      - { user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin @ infra nodes with pwd & ssl' }
      - { user: '${admin}'   ,db: all         ,addr: world     ,auth: ssl   ,title: 'admin @ everywhere with ssl & pwd' }
      - { user: dbuser_mssql ,db: mssql       ,addr: intra     ,auth: md5   ,title: 'allow mssql dbsu intranet access' } # <--- use md5 auth method for mssql user
      - { user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: pwd   ,title: 'pgbouncer read/write via local socket' }
      - { user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: pwd   ,title: 'read/write biz user via password' }
      - { user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: pwd   ,title: 'allow etl offline tasks from intranet' }
    pg_default_services: # route primary & replica service to mssql port 1433
      - { name: primary ,port: 5433 ,dest: 1433  ,check: /primary   ,selector: "[]" }
      - { name: replica ,port: 5434 ,dest: 1433  ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" }
      - { name: default ,port: 5436 ,dest: postgres ,check: /primary   ,selector: "[]" }
      - { name: offline ,port: 5438 ,dest: postgres ,check: /replica   ,selector: "[? pg_role == `offline` || pg_offline_query ]" , backup: "[? pg_role == `replica` && !pg_offline_query]" }

You can define business databases & users in the pg_databases and pg_users section:

#----------------------------------#
# pgsql (singleton on current node)
#----------------------------------#
# this is an example single-node postgres cluster with postgis & timescaledb installed, with one biz database & two biz users
pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary } # <---- primary instance with read-write capability
  vars:
    pg_cluster: pg-test
    pg_users:                           # create MSSQL superuser
      - {name: dbuser_mssql ,password: DBUser.MSSQL ,superuser: true, pgbouncer: true ,roles: [dbrole_admin], comment: superuser & owner for babelfish  }
    pg_primary_db: mssql                # use `mssql` as the primary sql server database
    pg_databases:
      - name: mssql
        baseline: mssql.sql             # init babelfish database & user
        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            }
        owner: dbuser_mssql
        parameters: { 'babelfishpg_tsql.migration_mode' : 'multi-db' }
        comment: babelfish cluster, a MSSQL compatible pg cluster

Client Access

You can use any SQL Server compatible client tool to access this database cluster.

Microsoft provides sqlcmd as the official command-line tool.

Besides, they have a go version cli tool: go-sqlcmd

Install go-sqlcmd:

curl -LO https://github.com/microsoft/go-sqlcmd/releases/download/v1.4.0/sqlcmd-v1.4.0-linux-amd64.tar.bz2
tar xjvf sqlcmd-v1.4.0-linux-amd64.tar.bz2
sudo mv sqlcmd* /usr/bin/

Get started with go-sqlcmd

$ sqlcmd -S 10.10.10.10,1433 -U dbuser_mssql -P DBUser.MSSQL
1> select @@version
2> go
version
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Babelfish for PostgreSQL with SQL Server Compatibility - 12.0.2000.8
Oct 22 2023 17:48:32
Copyright (c) Amazon Web Services
PostgreSQL 15.4 (EL 1:15.4.wiltondb3.3_2-2.el8) on x86_64-redhat-linux-gnu (Babelfish 3.3.0)

(1 row affected)

You can route service traffic to MSSQL 1433 port instead of 5433/5434:

# route 5433 on all members to 1433 on primary
sqlcmd -S 10.10.10.11,5433 -U dbuser_mssql -P DBUser.MSSQL

# route 5434 on all members to 1433 on replicas
sqlcmd -S 10.10.10.11,5434 -U dbuser_mssql -P DBUser.MSSQL

Install

If you have the Internet access, you can add the WiltonDB repository to the node and install it as a node package directly:

node_repo_modules: local,node,pgsql,mssql
node_packages: [ wiltondb ]

Install wiltondb with the following command:

./node.yml -t node_repo,node_pkg

It’s OK to install vanilla PostgreSQL and WiltonDB on the same node, but you can only run one of them at a time, and this is not recommended for production environments.

Extensions

Most of the PGSQL module’s extensions (non-SQL class) cannot be used directly on the WiltonDB core of the MSSQL module and need to be recompiled.

WiltonDB currently comes with the following extension plugins:

Name Version Comment
dblink 1.2 connect to other PostgreSQL databases from within a database
adminpack 2.1 administrative functions for PostgreSQL
dict_int 1.0 text search dictionary template for integers
intagg 1.1 integer aggregator and enumerator (obsolete)
dict_xsyn 1.0 text search dictionary template for extended synonym processing
amcheck 1.3 functions for verifying relation integrity
autoinc 1.0 functions for autoincrementing fields
bloom 1.0 bloom access method - signature file based index
fuzzystrmatch 1.1 determine similarities and distance between strings
intarray 1.5 functions, operators, and index support for 1-D arrays of integers
btree_gin 1.3 support for indexing common datatypes in GIN
btree_gist 1.7 support for indexing common datatypes in GiST
hstore 1.8 data type for storing sets of (key, value) pairs
hstore_plperl 1.0 transform between hstore and plperl
isn 1.2 data types for international product numbering standards
hstore_plperlu 1.0 transform between hstore and plperlu
jsonb_plperl 1.0 transform between jsonb and plperl
citext 1.6 data type for case-insensitive character strings
jsonb_plperlu 1.0 transform between jsonb and plperlu
jsonb_plpython3u 1.0 transform between jsonb and plpython3u
cube 1.5 data type for multidimensional cubes
hstore_plpython3u 1.0 transform between hstore and plpython3u
earthdistance 1.1 calculate great-circle distances on the surface of the Earth
lo 1.1 Large Object maintenance
file_fdw 1.0 foreign-data wrapper for flat file access
insert_username 1.0 functions for tracking who changed a table
ltree 1.2 data type for hierarchical tree-like structures
ltree_plpython3u 1.0 transform between ltree and plpython3u
pg_walinspect 1.0 functions to inspect contents of PostgreSQL Write-Ahead Log
moddatetime 1.0 functions for tracking last modification time
old_snapshot 1.0 utilities in support of old_snapshot_threshold
pgcrypto 1.3 cryptographic functions
pgrowlocks 1.2 show row-level locking information
pageinspect 1.11 inspect the contents of database pages at a low level
pg_surgery 1.0 extension to perform surgery on a damaged relation
seg 1.4 data type for representing line segments or floating-point intervals
pgstattuple 1.5 show tuple-level statistics
pg_buffercache 1.3 examine the shared buffer cache
pg_freespacemap 1.2 examine the free space map (FSM)
postgres_fdw 1.1 foreign-data wrapper for remote PostgreSQL servers
pg_prewarm 1.2 prewarm relation data
tcn 1.0 Triggered change notifications
pg_trgm 1.6 text similarity measurement and index searching based on trigrams
xml2 1.1 XPath querying and XSLT
refint 1.0 functions for implementing referential integrity (obsolete)
pg_visibility 1.2 examine the visibility map (VM) and page-level visibility info
pg_stat_statements 1.10 track planning and execution statistics of all SQL statements executed
sslinfo 1.2 information about SSL certificates
tablefunc 1.0 functions that manipulate whole tables, including crosstab
tsm_system_rows 1.0 TABLESAMPLE method which accepts number of rows as a limit
tsm_system_time 1.0 TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent 1.1 text search dictionary that removes accents
uuid-ossp 1.1 generate universally unique identifiers (UUIDs)
plpgsql 1.0 PL/pgSQL procedural language
babelfishpg_money 1.1.0 babelfishpg_money
system_stats 2.0 EnterpriseDB system statistics for PostgreSQL
tds_fdw 2.0.3 Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
babelfishpg_common 3.3.3 Transact SQL Datatype Support
babelfishpg_tds 1.0.0 TDS protocol extension
pg_hint_plan 1.5.1
babelfishpg_tsql 3.3.1 Transact SQL compatibility

4 - IvorySQL

PostgreSQL fork with oracle (grammar) compatibility

IvorySQL is an open-source “Oracle-compatible” PostgreSQL kernel, developed by HighGo, licensed under Apache 2.0.

The Oracle compatibility here refers to compatibility at the PL/SQL, syntax, built-in functions, data types, system views, MERGE, and GUC parameter levels. It’s not a wire protocol compatibility like Babelfish, openHalo, or FerretDB that allows using the original client drivers. Users still need to use PostgreSQL client tools to access IvorySQL, but can use Oracle-compatible syntax.

Currently, IvorySQL’s latest version 5.0 maintains compatibility with PostgreSQL’s latest minor version 18.0, and provides binary RPM/DEB packages for mainstream Linux distributions. Pigsty offers the option to replace the native PostgreSQL with the IvorySQL kernel in PG RDS.

Quick Start

Use the standard procedure to install Pigsty with the ivory configuration template:

curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty;
./configure -c ivory     # Use IvorySQL configuration template
./install.yml            # Run installation playbook

For production deployments, you should edit the auto-generated pigsty.yml configuration file to modify parameters like passwords before executing ./install.yml for deployment.

The latest IvorySQL 5.0 is equivalent to PostgreSQL 18.0 Any client tool compatible with PostgreSQL’s wire protocol can access IvorySQL clusters.

By default, you can use a PostgreSQL client to access through the alternative 1521 port, which enables Oracle compatibility mode by default.

Configuration Instructions

To use the IvorySQL kernel in Pigsty, modify the following four configuration parameters:

It’s that simple — just add these four lines to the global variables in the configuration file, and Pigsty will replace the native PostgreSQL kernel with IvorySQL:

pg_mode: ivory                           # IvorySQL compatibility mode, uses IvorySQL binaries
pg_packages: [ ivorysql, pgsql-common ]  # Install ivorysql, replacing pgsql-main kernel
pg_libs: 'liboracle_parser, pg_stat_statements, auto_explain'  # Load Oracle compatibility extensions
repo_extra_packages: [ ivorysql ]        # Download ivorysql packages

IvorySQL also provides a series of new GUC parameters that can be specified in pg_parameters.

Extensions

Most of the PGSQL modules’ extension (non-SQL classes) cannot be used directly on the IvorySQL kernel. If you need to use them, you need to recompile and install from source code for the new kernel.

Caveats

  • The IvorySQL software package is located in the pigsty-infra repository, not in pigsty-pgsql or pigsty-ivory repositories.
  • Pigsty does not assume any warranty for using the IvorySQL kernel, and any issues or requests should be addressed to the manufacturer.

5 - Percona

Percona Postgres Distribution with TDE support

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

It is compatible with PostgreSQL 18.1, and available on all supported platforms in Pigsty.

Get Started

install Pigsty’s with the pgtde config template.

curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty;
./configure -c pgtde     # use percona postgres kernel
./install.yml            # setup everything with pigsty

Configure

The following parameters need to be tuned to deploy a percona cluster:

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 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' }
    node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am

    # Percona PostgreSQL TDE Ad Hoc Settings
    pg_packages: [ percona-main, pgsql-common ]  # install percona postgres packages
    pg_libs: 'pg_tde, pgaudit, pg_stat_statements, pg_stat_monitor, auto_explain'

Extensions

Percona Postgres has 80 available extensions, including pg_tde, pgvector, postgis, pgaudit, set_user, pg_stat_monitor,….

name version comment
hstore_plperlu 1.0 transform between hstore and plperlu
jsonb_plperl 1.0 transform between jsonb and plperl
intagg 1.1 integer aggregator and enumerator (obsolete)
pltcl 1.0 PL/Tcl procedural language
isn 1.3 data types for international product numbering standards
pgstattuple 1.5 show tuple-level statistics
postgis_topology-3 3.5.4 PostGIS topology spatial types and functions
postgis_raster 3.5.4 PostGIS raster types and functions
tsm_system_rows 1.0 TABLESAMPLE method which accepts number of rows as a limit
lo 1.2 Large Object maintenance
hstore_plperl 1.0 transform between hstore and plperl
ltree 1.3 data type for hierarchical tree-like structures
postgis_raster-3 3.5.4 PostGIS raster types and functions
postgis_topology 3.5.4 PostGIS topology spatial types and functions
pgrowlocks 1.2 show row-level locking information
address_standardizer_data_us-3 3.5.4 Address Standardizer US dataset example
uuid-ossp 1.1 generate universally unique identifiers (UUIDs)
postgis-3 3.5.4 PostGIS geometry and geography spatial types and functions
hstore_plpython3u 1.0 transform between hstore and plpython3u
postgis 3.5.4 PostGIS geometry and geography spatial types and functions
set_user 4.2.0 similar to SET ROLE but with added logging
postgis_tiger_geocoder-3 3.5.4 PostGIS tiger geocoder and reverse geocoder
jsonb_plperlu 1.0 transform between jsonb and plperlu
pg_surgery 1.0 extension to perform surgery on a damaged relation
xml2 1.2 XPath querying and XSLT
pg_stat_monitor 2.3 The pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL contrib module pg_stat_statements. pg_stat_monitor provides aggregated statistics, client information, plan details including plan, and histogram information.
pg_tde 2.1 pg_tde access method
plpgsql 1.0 PL/pgSQL procedural language
address_standardizer-3 3.5.4 Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
tablefunc 1.0 functions that manipulate whole tables, including crosstab
hstore 1.8 data type for storing sets of (key, value) pairs
vector 0.8.1 vector data type and ivfflat and hnsw access methods
postgis_tiger_geocoder 3.5.4 PostGIS tiger geocoder and reverse geocoder
dblink 1.2 connect to other PostgreSQL databases from within a database
pltclu 1.0 PL/TclU untrusted procedural language
pg_trgm 1.6 text similarity measurement and index searching based on trigrams
sslinfo 1.2 information about SSL certificates
pg_stat_statements 1.12 track planning and execution statistics of all SQL statements executed
bool_plperlu 1.0 transform between bool and plperlu
cube 1.5 data type for multidimensional cubes
ltree_plpython3u 1.0 transform between ltree and plpython3u
amcheck 1.5 functions for verifying relation integrity
postgis_sfcgal 3.5.4 PostGIS SFCGAL functions
plpython3u 1.0 PL/Python3U untrusted procedural language
tsm_system_time 1.0 TABLESAMPLE method which accepts time in milliseconds as a limit
intarray 1.5 functions, operators, and index support for 1-D arrays of integers
btree_gist 1.8 support for indexing common datatypes in GiST
plperlu 1.0 PL/PerlU untrusted procedural language
fuzzystrmatch 1.2 determine similarities and distance between strings
bool_plperl 1.0 transform between bool and plperl
btree_gin 1.3 support for indexing common datatypes in GIN
pg_prewarm 1.2 prewarm relation data
pg_repack 1.5.3 Reorganize tables in PostgreSQL databases with minimal locks
citext 1.8 data type for case-insensitive character strings
pgcrypto 1.4 cryptographic functions
moddatetime 1.0 functions for tracking last modification time
plperl 1.0 PL/Perl procedural language
seg 1.4 data type for representing line segments or floating-point intervals
earthdistance 1.2 calculate great-circle distances on the surface of the Earth
unaccent 1.1 text search dictionary that removes accents
postgres_fdw 1.2 foreign-data wrapper for remote PostgreSQL servers
pg_logicalinspect 1.0 functions to inspect logical decoding components
tcn 1.0 Triggered change notifications
bloom 1.0 bloom access method - signature file based index
dict_int 1.0 text search dictionary template for integers
autoinc 1.0 functions for autoincrementing fields
address_standardizer_data_us 3.5.4 Address Standardizer US dataset example
postgis_sfcgal-3 3.5.4 PostGIS SFCGAL functions
jsonb_plpython3u 1.0 transform between jsonb and plpython3u
file_fdw 1.0 foreign-data wrapper for flat file access
pgaudit 18.0 provides auditing functionality
dict_xsyn 1.0 text search dictionary template for extended synonym processing
pg_walinspect 1.1 functions to inspect contents of PostgreSQL Write-Ahead Log
pg_buffercache 1.6 examine the shared buffer cache
refint 1.0 functions for implementing referential integrity (obsolete)
pg_freespacemap 1.3 examine the free space map (FSM)
insert_username 1.0 functions for tracking who changed a table
address_standardizer 3.5.4 Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
pg_visibility 1.2 examine the visibility map (VM) and page-level visibility info
pageinspect 1.13 inspect the contents of database pages at a low level

6 - PolarDB

PolarDB for PostgreSQL, with aurora flavor RAC

PolarDB is an aurora RAC flavor “cloud native” database system developed & open-sourced by Aliyun.

The latest version is v15.15.5.0, compatible with PostgreSQL 15, and available on all linux distributions supported by Pigsty.

Get Started

install Pigsty’s with the polar config template.

curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty;
./configure -c polar     # Use polar (PolarDB) template
./install.yml            # Run Deployment Playbook

Configure

The following parameters need to be tuned to deploy a PolarDB cluster:

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]}
    pg_hba_rules:
      - {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
    node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am

    # PolarDB Ad Hoc Settings
    pg_version: 15                            # PolarDB PG is based on PG 15
    pg_mode: polar                            # PolarDB PG Compatible mode
    pg_packages: [ polardb, pgsql-common ]    # Replace PG kernel with PolarDB kernel
    pg_exporter_exclude_database: 'template0,template1,postgres,polardb_admin'
    pg_default_roles:                         # PolarDB require replicator as superuser
      - { 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 }

Client Access

PolarDB for PostgreSQL is essentially equivalent to PostgreSQL 15, and any client tools compatible with the PostgreSQL wire protocol can access the PolarDB cluster.

Extensions

Most of the PGSQL module’s extension (non pure-SQL) cannot be used directly on the PolarDB kernel. If you need to use them, you need to recompile and install from source code for the new kernel.

Currently, the PolarDB kernel comes with the following 61 extension plugins. In addition to Contrib extensions, the additional extensions provided include:

name Version comment
adminpack 2.1 administrative functions for PostgreSQL
amcheck 1.3 functions for verifying relation integrity
autoinc 1.0 functions for autoincrementing fields
bloom 1.0 bloom access method - signature file based index
bool_plperl 1.0 transform between bool and plperl
bool_plperlu 1.0 transform between bool and plperlu
btree_gin 1.3 support for indexing common datatypes in GIN
btree_gist 1.7 support for indexing common datatypes in GiST
citext 1.6 data type for case-insensitive character strings
cube 1.5 data type for multidimensional cubes
dblink 1.2 connect to other PostgreSQL databases from within a database
dict_int 1.0 text search dictionary template for integers
dict_xsyn 1.0 text search dictionary template for extended synonym processing
earthdistance 1.1 calculate great-circle distances on the surface of the Earth
file_fdw 1.0 foreign-data wrapper for flat file access
fuzzystrmatch 1.1 determine similarities and distance between strings
hll 2.18 type for storing hyperloglog data
hstore 1.8 data type for storing sets of (key, value) pairs
hstore_plperl 1.0 transform between hstore and plperl
hstore_plperlu 1.0 transform between hstore and plperlu
hstore_plpython3u 1.0 transform between hstore and plpython3u
hypopg 1.3.1 Hypothetical indexes for PostgreSQL
insert_username 1.0 functions for tracking who changed a table
intagg 1.1 integer aggregator and enumerator (obsolete)
intarray 1.5 functions, operators, and index support for 1-D arrays of integers
isn 1.2 data types for international product numbering standards
jsonb_plperl 1.0 transform between jsonb and plperl
jsonb_plperlu 1.0 transform between jsonb and plperlu
jsonb_plpython3u 1.0 transform between jsonb and plpython3u
lo 1.1 Large Object maintenance
log_fdw 1.4 foreign-data wrapper for Postgres log file access
ltree 1.2 data type for hierarchical tree-like structures
ltree_plpython3u 1.0 transform between ltree and plpython3u
moddatetime 1.0 functions for tracking last modification time
old_snapshot 1.0 utilities in support of old_snapshot_threshold
pageinspect 1.11 inspect the contents of database pages at a low level
pase 0.0.1 ant ai similarity search
pg_bigm 1.2 text similarity measurement and index searching based on bigrams
pg_buffercache 1.4 examine the shared buffer cache
pg_freespacemap 1.2 examine the free space map (FSM)
pg_jieba 1.1.0 a parser for full-text search of Chinese
pg_prewarm 1.2 prewarm relation data
pg_repack 1.5.1-1 Reorganize tables in PostgreSQL databases with minimal locks
pg_stat_statements 1.10 track planning and execution statistics of all SQL statements executed
pg_surgery 1.0 extension to perform surgery on a damaged relation
pg_trgm 1.6 text similarity measurement and index searching based on trigrams
pg_visibility 1.2 examine the visibility map (VM) and page-level visibility info
pg_walinspect 1.0 functions to inspect contents of PostgreSQL Write-Ahead Log
pgcrypto 1.3 cryptographic functions
pgrowlocks 1.2 show row-level locking information
pgstattuple 1.5 show tuple-level statistics
plperl 1.0 PL/Perl procedural language
plperlu 1.0 PL/PerlU untrusted procedural language
plpgsql 1.0 PL/pgSQL procedural language
plpython3u 1.0 PL/Python3U untrusted procedural language
pltcl 1.0 PL/Tcl procedural language
pltclu 1.0 PL/TclU untrusted procedural language
polar_audit 1.0 provides auditing functionality
polar_feature_utils 1.0 PolarDB feature utilization
polar_io_stat 1.0 polar io stat in multi dimension
polar_login_history 1.0 record user login information
polar_masking 1.0.0 provides data masking for polardb
polar_monitor 1.0 monitor functions for PolarDB
polar_monitor_preload 1.0 examine the polardb information
polar_parameter_manager 1.1 Extension to select parameters for manger.
polar_password_policy 1.0 create password policies and check user passwords based on the policies
polar_proxy_utils 1.0 Extension to provide operations about proxy.
polar_resource_manager 1.0 a background process that forcibly frees user session process memory
polar_smgrperf 1.0 smgr perf test extension
polar_sql_mapping 1.0 Record error sqls and mapping them to correct one
polar_stat_env 1.0 env stat functions for PolarDB
polar_vfs 1.0 polar virtual file system for different storage
polar_worker 1.0 polar_worker
postgres_fdw 1.1 foreign-data wrapper for remote PostgreSQL servers
refint 1.0 functions for implementing referential integrity (obsolete)
roaringbitmap 0.5 support for Roaring Bitmaps
seg 1.4 data type for representing line segments or floating-point intervals
sslinfo 1.2 information about SSL certificates
tablefunc 1.0 functions that manipulate whole tables, including crosstab
tcn 1.0 Triggered change notifications
tsm_system_rows 1.0 TABLESAMPLE method which accepts number of rows as a limit
tsm_system_time 1.0 TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent 1.1 text search dictionary that removes accents
uuid-ossp 1.1 generate universally unique identifiers (UUIDs)
vector 0.6.2 vector data type and ivfflat and hnsw access methods
xml2 1.1 XPath querying and XSLT

PolarDB for Oracle

There’s 2nd fork of PolarDB, which is PolarDB for Oracle, which is not open source.

Pigsty Pro has support for Running PolarDB for Oracle as RDS.

7 - OrioleDB

Next Gen OLTP engine for PostgreSQL

OrioleDB is a PostgreSQL storage engine extension that claims to deliver 4x OLTP performance without the xid wraparound & table bloat, and “cloud native” (data on s3) capabilities.

The latest version of OrioleDB is based on a Patched PostgreSQL 17.0 with an additional extension

You can run OrioleDB as RDS with pigsty, it is compatible with PG 17 and available on all supported Linux platforms. The latest version is beta12 over patchset 17_11.

Get Started

Follow the Pigsty standard installation and use the oriole config template.

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

For production deployments, make sure to modify the password parameters in the pigsty.yml config 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'}
    node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am

    # OrioleDB Ad Hoc Settings
    pg_mode: oriole                                         # oriole compatible mode
    pg_packages: [ orioledb, pgsql-common ]                 # install OrioleDB kernel
    pg_libs: 'orioledb, pg_stat_statements, auto_explain'   # Load OrioleDB Extension

Usage

To use OrioleDB, you need to install the orioledb_17 and oriolepg_17 packages (currently only available as RPMs).

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

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 differences:

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

8 - OpenHalo

MySQL Compatible Postgres 14 Fork

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

OpenHalo is based on PostgreSQL 14.10 kernel version and provides wire protocol compatibility with MySQL 5.7.32-log / 8.0 version.

Pigsty provides deployment support for OpenHalo on all supported Linux platforms.

Get Started

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

curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty;
./configure -c mysql    # Use MySQL (openHalo) configuration template
./install.yml           # Install, for production deployment please modify passwords in pigsty.yml first

For production deployment, please ensure to modify the password parameters in the pigsty.yml configuration file before running the installation 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: postgres, extensions: [aux_mysql]} # the 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'}
    node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am

    # OpenHalo Ad Hoc Setting
    pg_mode: mysql                    # MySQL Compatible Mode by HaloDB
    pg_version: 14                    # The current HaloDB is compatible with PG Major Version 14
    pg_packages: [ openhalodb, pgsql-common ]  # install openhalodb instead of postgresql kernel

Usage

When accessing MySQL, the actual connection uses the postgres database. Please note that the concept of “database” in MySQL actually corresponds to “Schema” in PostgreSQL. Therefore, use mysql actually uses the mysql Schema within the postgres database.

The usernames and passwords used for MySQL are the same as those in PostgreSQL. You can manage users and permissions using the standard PostgreSQL approach.

Client Access

OpenHalo provides MySQL wire protocol compatibility, listening on port 3306 by default, allowing direct connections from MySQL clients and drivers.

Pigsty’s conf/mysql configuration installs the mysql client tool by default.

You can access MySQL using the following command:

mysql -h 127.0.0.1 -u dbuser_dba

Currently, OpenHalo officially ensures that Navicat can access this MySQL port normally, but Intellij IDEA’s DataGrip access will result in errors.

Modification

The OpenHalo kernel installed by Pigsty is based on the HaloTech-Co-Ltd/openHalo kernel with minor modifications:

  • Changed the default database name from halo0root back to postgres
  • Removed the 1.0. prefix from the default version number, reverting to 14.10
  • Modified the default configuration file to enable MySQL compatibility and listen on port 3306 by default

Please note that Pigsty does not provide any warranty for using the OpenHalo kernel. Any issues or requirements encountered while using this kernel should be addressed with the original vendor.

9 - Cloudberry

Cloudberry and Greenplum, the MPP data warehouse

You can deploy and monitor Cloudberry clusters, which is a Greenplum fork.

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

Wait for 2.0 GA

We are waiting for the official release of Apache Cloudberry 2.0, so do not use it in production now

Install

To install cloudberry, you’ll have to enable the gpsql repo module:

./node.yml -t node_install  -e '{"node_repo_modules":"node,pgsql,gpsql","node_packages":["cloudberrydb"]}'

Configure

Set pg_mode = gpsql and the extra 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

10 - Supabase

Self-hosting BaaS upon PostgreSQL

See the maintained self-hosting tutorial: Supabase

Supabase is great, but having your own Supabase is even better. Pigsty helps you build enterprise-grade Supabase on your own servers (physical/virtual machines/cloud servers) with one-click deployment — more extensions, better performance, deeper control, and much more cost-effective.

Pigsty is one of the three 3rd party self-hosting tutorials listed in the official Supabase docs

Quick Start

Prepare a Linux server, follow the Pigsty standard installation process, select the supabase configuration template, and execute the following commands:

curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty
./configure -c supabase    # Use supabase configuration (please change credentials in pigsty.yml)
vi pigsty.yml              # Edit domain, passwords, keys...
./install.yml              # Install pigsty
./docker.yml               # Install docker compose components
./app.yml                  # Start supabase stateless components with docker (may be slow)

After installation, visit port 8000 in your browser to access Supa Studio, username supabase, password pigsty.

Table of Contents

What is Supabase?

Supabase is a BaaS (Backend as Service), an open-source Firebase alternative, and the most popular database + backend solution in the AI Agent era. Supabase wraps PostgreSQL and provides authentication, messaging, edge functions, object storage, and automatically generates REST API and GraphQL API based on PostgreSQL database schemas.

Supabase aims to provide developers with a one-stop backend solution, reducing the complexity of developing and maintaining backend infrastructure. It allows developers to eliminate most backend development work — developers only need to understand database design and frontend to quickly deliver applications! Developers can quickly complete a full application with just frontend development and database schema design using Vibe Coding.

Currently, Supabase is the most popular open-source project in the PostgreSQL open-source ecosystem, with 80,000 stars on GitHub. Supabase also provides “generous” free cloud service quotas for small entrepreneurs — 500 MB of free space, which is sufficient for storing user tables, view counts, and similar data.

Why Self-Host?

Since Supabase cloud service is so attractive, why self-host?

The most intuitive reason is what we mentioned in “Are Cloud Databases an Intelligence Tax?”: when your data/computing scale exceeds the cloud computing applicable spectrum (Supabase: 4C/8G/500MB free storage), costs can easily explode. Moreover, currently, sufficiently reliable local enterprise-grade NVMe SSDs have a three to four order of magnitude advantage in cost-effectiveness compared to cloud storage, and self-hosting can better leverage this advantage.

Another important reason is functionality — Supabase cloud service functionality is limited. Many powerful PostgreSQL extensions cannot be provided as cloud services due to multi-tenant security challenges and licensing issues. Therefore, although extensions are PostgreSQL’s core feature, only 64 extensions are available on Supabase cloud service. Self-built Supabase with Pigsty provides up to 437 ready-to-use PostgreSQL extensions.

Additionally, autonomy and avoiding vendor lock-in are important reasons for self-hosting — although Supabase aims to provide an open-source alternative to Google Firebase without vendor lock-in, the threshold for self-building enterprise-grade Supabase to high standards is actually quite high. Supabase includes a series of PostgreSQL extension plugins developed and maintained by them, and plans to replace the native PostgreSQL kernel with the acquired OrioleDB, but these kernels and extensions are not provided in the official PGDG repository.

This is actually a form of implicit vendor lock-in, preventing users from self-building using methods other than the supabase/postgres Docker image. Pigsty provides an open-source, transparent, and universal solution to solve this problem. We package all 10 missing extensions developed and used by Supabase into ready-to-use RPM/DEB packages, ensuring they are available on all mainstream Linux operating system distributions:

Extension Description
pg_graphql Provides GraphQL support within PostgreSQL (RUST), Rust extension, provided by PIGSTY
pg_jsonschema Provides JSON Schema validation capability, Rust extension, provided by PIGSTY
wrappers Supabase’s external data source wrapper bundle, Rust extension, provided by PIGSTY
index_advisor Query index advisor, SQL extension, provided by PIGSTY
pg_net Extension for asynchronous non-blocking HTTP/HTTPS requests with SQL (supabase), C extension, provided by PIGSTY
vault Extension for storing encrypted credentials in Vault (supabase), C extension, provided by PIGSTY
pgjwt PostgreSQL implementation of JSON Web Token API (supabase), SQL extension, provided by PIGSTY
pgsodium Table data encryption storage TDE, extension, provided by PIGSTY
supautils Used to ensure database cluster security in cloud environments, C extension, provided by PIGSTY
pg_plan_filter Filter and block specific query statements using execution plan costs, C extension, provided by PIGSTY

Meanwhile, we install most extensions by default in Supabase self-hosting deployment. You can refer to the available extension list to enable them as needed.

Additionally, Pigsty handles the automatic setup of underlying high availability PostgreSQL database clusters, high availability MinIO object storage clusters, and even Docker container infrastructure deployment and Nginx reverse proxy, domain configuration and HTTPS certificate issuance. You can deploy any number of stateless Supabase container clusters using Docker Compose and store state in external Pigsty self-hosted database services.

In this self-hosting deployment architecture, you gain the freedom to use different kernels (PostgreSQL 15-18, OrioleDB), the freedom to install 437 extensions, the freedom to scale Supabase/Postgres/MinIO, the freedom from database operational chores, and the freedom from vendor lock-in to run locally indefinitely. Compared to the cost of using cloud services, the price is just preparing servers and typing a few more commands.

Single Node Quick Start

Let’s start with single-node Supabase deployment. We’ll introduce multi-node high availability deployment methods later.

Prepare a fresh Linux server, use the supabase configuration template provided by Pigsty to execute the standard installation process, then additionally run docker.yml and app.yml to deploy the stateless Supabase containers (default ports 8000/8433).

curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty
./configure -c supabase    # Use supabase configuration (please change credentials in pigsty.yml)
vi pigsty.yml              # Edit domain, passwords, keys...
./install.yml              # Install pigsty
./docker.yml               # Install docker compose components
./app.yml                  # Start supabase stateless components with docker

Before deploying Supabase, please modify the parameters (domain and passwords) in the automatically generated pigsty.yml configuration file according to your actual situation. If it’s just local development testing, you can skip this for now. We’ll introduce how to further customize through configuration file modifications later.

asciicast

If configured correctly, after about ten minutes, you can access the Supabase Studio graphical management interface locally via http://<your_ip_address>:8000. The default username and password are: supabase and pigsty.

DockerHub blocked in mainland China

In mainland China, Pigsty uses DockerHub mirror sites provided by 1Panel and 1ms to download Supabase-related images by default, which may be slow. You can also configure proxy and mirror sites yourself, or manually pull images with cd /opt/supabase; docker compose pull. We also provide Supabase self-hosting expert consulting services including complete offline installation solutions.

Using Supabase object storage requires HTTPS/domain

If you need to use object storage functionality, you need to access Supabase via domain and HTTPS, otherwise errors will occur.

Please change passwords for production deployment!

For serious production deployments, must change all default passwords!

Key Technical Decisions for Self-Hosting

Here are some key technical decisions involved in self-hosting Supabase for your reference:

Using the default single-node deployment, Supabase cannot enjoy PostgreSQL/MinIO high availability capabilities. Nevertheless, single-node deployment still has significant advantages compared to the official pure Docker Compose solution: for example, out-of-the-box monitoring systems, the ability to freely install extensions, component scaling capabilities, and providing fallback database point-in-time recovery capabilities.

If you only have one server or choose to self-host on cloud servers, Pigsty recommends using external S3 instead of local MinIO as object storage to store PostgreSQL backups and support Supabase Storage services. Such deployment can provide a fallback-level RTO (hour-level recovery time)/RPO (MB-level data loss) disaster recovery level under single-machine deployment conditions during failures.

In serious production deployments, Pigsty recommends using at least 3-4 node deployment strategies to ensure both MinIO and PostgreSQL use multi-node deployments that meet enterprise-grade high availability requirements. In this case, you need to prepare more nodes and disks accordingly and adjust cluster configurations in the pigsty.yml configuration manifest, as well as access information in supabase cluster configuration to use high availability access points.

Some Supabase functionality requires sending emails, so SMTP services are needed. Unless purely for internal networks, for serious production deployments, using SMTP cloud services is recommended. Self-built email servers easily have their emails marked as spam and rejected.

If your service is directly exposed to the public network, we strongly recommend using real domains and HTTPS certificates and accessing through Nginx Portal.

Next, we’ll discuss some advanced topics in sequence: how to further improve Supabase security, availability, and performance based on single-node deployment.

Advanced Topic: Security Hardening

Pigsty Base Components

For serious production deployments, we strongly recommend changing Pigsty default passwords. Because these default values are public and well-known, going to production without changing passwords is like streaking:

The above passwords are for Pigsty component modules and are strongly recommended to be set before installation and deployment.

Supabase Keys

In addition to Pigsty component passwords, you also need to modify Supabase keys, including:

Please refer to the Supabase tutorial: Securing your services instructions:

  • Generate a JWT_SECRET longer than 40 characters and use the tools in the tutorial to sign ANON_KEY and SERVICE_ROLE_KEY JWTs.
  • Use the tools provided in the tutorial to generate an ANON_KEY JWT based on JWT_SECRET and expiration time attributes. This is the credential for anonymous users.
  • Use the tools provided in the tutorial to generate a SERVICE_ROLE_KEY based on JWT_SECRET and expiration time attributes. This is the credential for higher-privilege service roles.
  • Setup PG_META_CRYPTO_KEY with a random string at least 32 char long for securing connection strings between Studio and postgres-meta
  • If your PostgreSQL business user uses a password different from the default, please modify the POSTGRES_PASSWORD value accordingly
  • If your object storage uses a password different from the default, please modify the S3_ACCESS_KEY and S3_SECRET_KEY values accordingly

After modifying Supabase credentials, you can restart Docker Compose containers to apply the new configuration:

./app.yml -t app_config,app_launch
cd /opt/supabase; make up

Advanced Topic: Domain Integration

If you’re using Supabase on localhost or within a LAN, you can choose IP:Port direct connection to Kong’s exposed HTTP port 8000 to access Supabase.

You can use an internal static DNS domain, but for serious production deployments, we recommend using real domain + HTTPS to access Supabase. In this case, your server should have a public IP address, you should own a domain, use DNS resolution services provided by cloud/DNS/CDN providers to point it to the installation node’s public IP (optional fallback: local /etc/hosts static resolution).

A simple approach is to batch replace the placeholder domain (supa.pigsty) with your actual domain, say supa.pigsty.cc:

sed -ie 's/supa.pigsty/supa.pigsty/g' ~/pigsty/pigsty.yml

If you haven’t configured it beforehand, reload Nginx and Supabase configurations:

make nginx      # Reload nginx configuration
make cert       # Apply for free HTTPS certificate with certbot
./app.yml       # Reload Supabase configuration

The modified configuration should look like the following snippet:

all:
  vars:
    infra_portal:
      supa :
        domain: supa.pigsty.cc        # Replace with your domain!
        endpoint: "10.10.10.10:8000"
        websocket: true
        certbot: supa.pigsty.cc       # Certificate name, usually same as domain

  children:
    supabase:
      vars:
          supabase:                                       # the definition of supabase app
            conf:                                         # override /opt/supabase/.env
              SITE_URL: https://supa.pigsty                # <------- Change This to your external domain name
              API_EXTERNAL_URL: https://supa.pigsty        # <------- Otherwise the storage api may not work!
              SUPABASE_PUBLIC_URL: https://supa.pigsty     # <------- DO NOT FORGET TO PUT IT IN infra_portal!

Complete domain/HTTPS configuration can refer to the Certificate Management tutorial. You can also use Pigsty’s built-in local static resolution and self-signed HTTPS certificates as fallback.

asciicast

Advanced Topic: External Object Storage

You can use S3 or S3-compatible services as object storage for PostgreSQL backups and Supabase usage. Here we use Alibaba Cloud OSS object storage as an example.

Pigsty provides a terraform/spec/aliyun-meta-s3.tf template that can be used to deploy a server and an OSS bucket on Alibaba Cloud.

First, modify the S3-related configuration in all.children.supa.vars.apps.[supabase].conf, pointing it to the Alibaba Cloud OSS bucket:

# if using s3/minio as file storage
S3_BUCKET: data                       # Replace with S3-compatible service connection information
S3_ENDPOINT: https://sss.pigsty:9000  # Replace with S3-compatible service connection information
S3_ACCESS_KEY: s3user_data            # Replace with S3-compatible service connection information
S3_SECRET_KEY: S3User.Data            # Replace with S3-compatible service connection information
S3_FORCE_PATH_STYLE: true             # Replace with S3-compatible service connection information
S3_REGION: stub                       # Replace with S3-compatible service connection information
S3_PROTOCOL: https                    # Replace with S3-compatible service connection information

Reload Supabase configuration with the following command:

./app.yml -t app_config,app_launch

You can also use S3 as PostgreSQL backup repository by adding an aliyun backup repository definition in all.vars.pgbackrest_repo:

all:
  vars:
    pgbackrest_method: aliyun          # pgbackrest backup method: local,minio,[other user-defined repositories...], in this example backup is stored to MinIO
    pgbackrest_repo:                   # pgbackrest backup repository: https://pgbackrest.org/configuration.html#section-repository
      aliyun:                          # Define a new backup repository aliyun
        type: s3                       # Alibaba Cloud OSS is S3-compatible object storage
        s3_endpoint: oss-cn-beijing-internal.aliyuncs.com
        s3_region: oss-cn-beijing
        s3_bucket: pigsty-oss
        s3_key: xxxxxxxxxxxxxx
        s3_key_secret: xxxxxxxx
        s3_uri_style: host
        path: /pgbackrest
        bundle: y                         # bundle small files into a single file
        bundle_limit: 20MiB               # Limit for file bundles, 20MiB for object storage
        bundle_size: 128MiB               # Target size for file bundles, 128MiB for object storage
        cipher_type: aes-256-cbc          # enable AES encryption for remote backup repo
        cipher_pass: pgBackRest.MyPass    # Set an encryption password, pgBackrest backup repository encryption password
        retention_full_type: time         # retention full backup by time on minio repo
        retention_full: 14                # keep full backup for the last 14 days

Then specify using the aliyun backup repository in all.vars.pgbackrest_method and reset pgBackrest backup:

./pgsql.yml -t pgbackrest

Pigsty will switch the backup repository to external object storage. More backup configurations can refer to PostgreSQL Backup documentation.

Advanced Topic: Using SMTP

You can use SMTP to send emails by modifying the supabase application configuration and adding SMTP information:

all:
  children:
    supabase:        # supa group
      vars:          # supa group vars
        apps:        # supa group app list
          supabase:  # the supabase app
            conf:    # the supabase app conf entries
              SMTP_HOST: smtpdm.aliyun.com:80
              SMTP_PORT: 80
              SMTP_USER: [email protected]
              SMTP_PASS: your_email_user_password
              SMTP_SENDER_NAME: MySupabase
              SMTP_ADMIN_EMAIL: [email protected]
              ENABLE_ANONYMOUS_USERS: false

Don’t forget to use app.yml to reload the configuration

Advanced Topic: True High Availability

After these configurations, you have an enterprise-grade Supabase (basic single-machine version) with public domain, HTTPS certificate, SMTP, PITR backup, monitoring, IaC, and 400+ extensions. For high availability configuration, please refer to other parts of Pigsty documentation. If you’re too lazy to read and learn, we provide hands-on Supabase self-hosting expert consulting services — ¥2000 to save you from the hassle of tinkering and downloading.

Single-node RTO/RPO relies on external object storage services for fallback. If your node fails, backups are retained in external S3 storage, and you can redeploy Supabase on a new node and restore from backup. Such deployment can provide a minimum standard RTO (hour-level recovery time)/RPO (MB-level data loss) fallback disaster recovery level during failures.

To achieve RTO < 30s with zero data loss failover, you need to use multi-node high availability deployment, which involves:

  • ETCD: DCS needs three or more nodes to tolerate one node failure.
  • PGSQL: PostgreSQL synchronous commit mode without data loss, recommend using at least three nodes.
  • INFRA: Monitoring infrastructure failure has less impact, recommend using dual replicas in production
  • Supabase stateless containers themselves can also be multi-node replicas to achieve high availability.

In this case, you also need to modify PostgreSQL and MinIO access points to use DNS/L2 VIP/HAProxy and other high availability access points For these parts, you only need to refer to the documentation of each module in Pigsty for configuration and deployment. We recommend referring to the configurations in conf/ha/trio.yml and conf/ha/safe.yml to upgrade cluster scale to three nodes or more.

11 - FerretDB

Mongo Wire Compatible PostgreSQL

FerretDB is an open-source MongoDB wire protocol compatible middleware that allows you to use PostgreSQL as a drop-in replacement for MongoDB. It enables applications that rely on MongoDB’s wire protocol to work seamlessly with PostgreSQL, providing a bridge between the two databases.

To enable FerretDB, you’ll need the FerretDB patched documentdb extension, which is also available in the Pigsty repository. The latest combo is FerretDB 2.7 and DocumentDB 0.107.0.

Get Started

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

./configure -c mongo    # Use FerretDB / DocumentDB config template
./install.yml           # Install, for production deployment please modify passwords in pigsty.yml first

For production deployment, please ensure to modify the password parameters in the pigsty.yml configuration file before running the installation playbook.

Configuration 

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  ,pgbouncer: true ,roles: [dbrole_admin   ] ,comment: ferretdb super user ,superuser: true }
      - { 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, owner: mongod ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [ documentdb, postgis, vector, pg_cron, rum ]}
    pg_hba_rules:
      - { user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes' }
      - { user: mongod      , db: all ,addr: world ,auth: pwd ,title: 'mongodb password access from everywhere' }
    node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am

    # DocumentDB Settings
    pg_extensions: [ documentdb, citus, postgis, pgvector, pg_cron, rum ]
    pg_libs: 'pg_documentdb, pg_documentdb_core, pg_cron, pg_stat_statements, auto_explain'  # add timescaledb to shared_preload_libraries
    pg_parameters: { cron.database_name: meta }

Usage

Check the FERRET docs for the details.

Install Client Tools

You can use MongoDB’s command-line tool MongoSH to access FerretDB.

Use the pig command to add MongoDB repository, then install mongosh using yum or apt:

pig repo add mongo -u
yum install mongodb-mongosh
apt install mongodb-mongosh

Connect to FerretDB

You can access FerretDB using MongoDB connection strings with any MongoDB driver in any language. Here’s an example using the mongosh CLI tool:

$ mongosh
Current Mongosh Log ID:	67ba8c1fe551f042bf51e943
Connecting to:		mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.4.0
Using MongoDB:		7.0.77
Using Mongosh:		2.4.0

For mongosh info see: https://www.mongodb.com/docs/mongodb-shell/

test>

Authentication

You can log in with different users. See FerretDB: Authentication for details.

mongosh 'mongodb://dbuser_meta:[email protected]:27017/meta'      # Business admin user
mongosh 'mongodb://dbuser_view:[email protected]:27017/meta'    # Read-only user

Quick Start

You can connect to FerretDB and use it as if it were a MongoDB cluster.

$ mongosh 'mongodb://dbuser_meta:[email protected]:27017/meta'

MongoDB commands are translated to SQL and executed in the underlying PostgreSQL:

use test                            // CREATE SCHEMA test;
db.dropDatabase();                  // DROP SCHEMA test;
db.createCollection('posts');       // CREATE TABLE posts(_data JSONB,...)
db.posts.insertOne({                // INSERT INTO posts VALUES(...);
    title: 'Post One',body: 'Body of post one',category: 'News',tags: ['news', 'events'],
    user: {name: 'John Doe',status: 'author'},date: Date()}
);
db.posts.find().limit(2).pretty();  // SELECT * FROM posts LIMIT 2;
db.posts.createIndex({ title: 1 })  // CREATE INDEX ON posts(_data->>'title');

If you’re not familiar with MongoDB, here’s a quick tutorial that works with FerretDB: Perform CRUD Operations with MongoDB Shell

To generate sample workload, you can use this simple test script with mongosh:

cat > benchmark.js <<'EOF'
const coll = "testColl";
const numDocs = 1000;

for (let i = 0; i < numDocs; i++) {  // insert
  db.getCollection(coll).insertOne({ num: i, name: "MongoDB Benchmark Test" });
}

for (let i = 0; i < numDocs; i++) {  // select
  db.getCollection(coll).find({ num: i });
}

for (let i = 0; i < numDocs; i++) {  // update
  db.getCollection(coll).updateOne({ num: i }, { $set: { name: "Updated" } });
}

for (let i = 0; i < numDocs; i++) {  // delete
  db.getCollection(coll).deleteOne({ num: i });
}
EOF

mongosh 'mongodb://dbuser_meta:[email protected]:27017' benchmark.js

You can check FerretDB’s supported MongoDB commands and known differences. For basic usage, these differences are usually not significant.