Concept

Citus is a PostgreSQL extension that turns PostgreSQL into a distributed database. It scales horizontally across nodes to process large data volumes and query workloads. Patroni v3.0+ provides native high-availability support for Citus. This …

In a typical 3-node HA deployment, simultaneous failure of two nodes removes the majority. The system usually cannot complete automatic failover and needs manual intervention. First, assess the two failed servers. If they can restart quickly, …

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 …

Snapshot of the pgBackRest 2.59.2 documentation: https://pgbackrest.org/ Introduction pgBackRest provides backup and restore functions for PostgreSQL. It supports large databases and workloads. pgBackRest v2.59.2 is the current stable release. …

Source: https://www.pgbouncer.org/ This manual is based on PgBouncer 1.26.0, released on 2026-09-23. See the release notes for the changes from earlier versions. pgbouncer is a PostgreSQL connection pooler. Applications connect to pgbouncer as they …

Source: https://patroni.readthedocs.io/en/latest/index.html Warning Running Patroni on memory-restricted systems with Python 3.11+ Unexpected behavior can occur with Python 3.11 or newer on systems with strict memory limits. For example, this …

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 …

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 …

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 …

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. …

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 …

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 …

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 …

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. …

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

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 …

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, …

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 …

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 …

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 …

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 …

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 …

Pigsty’s backup and restore operations execute pgBackRest commands. For safe operation, understand the pgBackRest model and how Pigsty maps its orchestration parameters to native options. Core pgBackRest Concepts Stanza: the Cluster’s Backup Identity …

The mongo configuration template is a PostgreSQL deployment mode, not an independent Pigsty module. It combines: PostgreSQL 18 managed by the standard PGSQL module The documentdb extension and its required preload libraries A stateless FerretDB proxy …

Pigsty is a portable, extensible, open-source PostgreSQL distribution. It uses declarative configuration and automation to build production database services in local environments. Its tools, scripts, and operating practices support PostgreSQL …

Compliance requires continuous evidence of three elements: Configuration: Security controls must be enabled. Pigsty directly provides configuration. Process: The organization must establish access approval, change management, recovery exercises, and …

Network boundaries, authentication, and access control reduce incident risk. Data controls must limit damage and support recovery after hardware failure, leaked credentials, or operator errors. Data security must provide integrity, recoverability, …

TLS can provide transport encryption, server authentication, and client authentication. Configure each protection separately. Enabling server TLS does not make clients verify the server or make the server require client certificates. Most TLS …

Authentication verifies identity. Authorization determines what that identity can do. PostgreSQL GRANT and REVOKE provide precise control. Privilege problems usually arise from missing default conventions. Examples include making an application …

pg_hba.conf defines PostgreSQL Host-Based Authentication. It controls who can connect, the source address, the database, and the required identity check. Manual HBA maintenance across a cluster requires care. Primary and replica rules can differ, and …

Identify the root of trust and defensive layers before selecting security features. The root of trust needs the strongest protection. Multiple layers preserve some protection if one fails. Trust Boundaries Pigsty uses Ansible for declarative …

Databases usually store the most valuable and sensitive data in an information system. Attacks and failures can therefore have severe consequences. Security requires multiple controls: who can connect, what they can do, and whether traffic is …

Split read and write operations, route traffic correctly, and deliver PostgreSQL cluster capabilities reliably. A service exposes database cluster capabilities through a stable interface that hides the underlying cluster details. Services provide …

The built-in monitoring system provides metrics, logs, and alerting. Logs and alerts also support audit and traceability. It can monitor Pigsty-managed clusters, existing PostgreSQL clusters, and external RDS services. Monitoring Targets Pigsty …

Use a PostgreSQL metabase as a dynamic configuration source instead of static YAML files. Overview A CMDB (Configuration Management Database) stores configuration in database tables. By default, Pigsty uses static pigsty.yml as the Ansible inventory. …

The inventory defines the deployment through declarative configuration. Customize its pigsty.yml file for your requirements. New users can start with ready-to-use templates for common deployment scenarios. Each template is a predefined pigsty.yml …

Decisions can consume much of the available incident response time. Pigsty automates recovery steps, but the operator must decide three things. Select the target, choose in-place or clone recovery, and define how to validate the result. Read and …

A backup must support recovery when an incident occurs. Traditional PITR requires connected manual steps: pause HA, stop PostgreSQL, prepare recovery settings, restore the backup, and replay WAL. Then validate the target, rebuild metadata, and start …

Backups require storage, network traffic, and operational work. They determine how much data you can recover and how quickly service returns. More history usually requires more capacity. A shorter RTO usually requires newer backups and tested …

Use inventory parameters to customize infrastructure settings and database configuration. Parameter List The current source and reference pages list 373 public parameters across 10 official modules. See Reference - Parameter List for the complete …

The configure configuration wizard generates pigsty.yml for your current environment. This script is optional. If you know how to configure Pigsty, edit pigsty.yml directly and skip the wizard. Quick Start Enter the Pigsty source directory and run …

The PITR principle is simple, but implementation must address several constraints. WAL archiving must not block production writes. Object storage backups need encryption. Backup jobs must follow the primary after failover, and shared repositories …

A database is a state machine. WAL (Write-Ahead Log) records its changes in order. PostgreSQL writes each change to WAL before applying it to data files. Keep a physical snapshot and all later WAL to replay the database to a selected consistent …

RTO (Recovery Time Objective) defines the maximum time required for the system to restore write capability when the primary fails. Critical transaction systems typically need the shortest practical RTO, often below one minute. Shorter RTO increases …

Point-in-Time Recovery (PITR) can restore an earlier cluster state after accidental deletion of data, a table, or a database. Pigsty enables PITR in its standard PostgreSQL configuration. This work once required specialist DBA knowledge. Replication …

PGSQL organizes PostgreSQL as clusters. Each cluster is a logical group of database instances connected through primary-replica relationships. Overview The PGSQL module combines these components for production HA clusters: Component Type Description …

Production HA PostgreSQL needs monitoring, alerts, logs, time synchronization, DNS, and software repositories. The INFRA module provides these services. It is optional but strongly recommended. Overview The following single-node deployment diagram …

Overview Pigsty PostgreSQL clusters include HA through Patroni, Etcd, and HAProxy. A cluster with two or more instances gains automatic HA without extra configuration. A surviving instance can provide the complete database service. Clients can …

Pigsty uses IaC and GitOps. Describe the environment in a declarative inventory. Apply it with idempotent playbooks. Declare the target state with parameters. Playbooks can repeatedly adjust nodes to that state. This resembles Kubernetes CRDs and …

Source: https://patroni.readthedocs.io/en/latest/ha_multi_dc.html A PostgreSQL cluster across multiple data centers uses replication for high availability. Replication can be synchronous or asynchronous. See replication modes. Both modes require …

A deployment is the largest entity in Pigsty. This entity-relationship diagram shows its main entities and their relationships: A deployment is also an environment. Examples include Production (Prod), User Acceptance Testing (UAT), Staging, Testing, …

A node represents hardware resources and an operating system. It can be a physical server, virtual machine, container, or pod. A node needs a Linux OS, systemd, and standard CPU, memory, disk, and network resources. Install modules on nodes. The …

Source: https://patroni.readthedocs.io/en/latest/security.html Protect two Patroni cluster interfaces from unauthorized access: the distributed configuration storage (DCS) and the Patroni REST API. Protecting DCS Patroni and patronictl store data in …

Pigsty uses a modular architecture and declarative interface. Combine modules to meet your requirements. Use one or more modules from the modular design. Describe the environment with a configuration inventory and parameters. Apply it with Ansible …

Source: https://patroni.readthedocs.io/en/latest/tools_integration.html Patroni can integrate with other tools in your stack. These examples show possible integrations. The list is not complete. Barman Patroni provides patroni_barman. This …

Source: https://patroni.readthedocs.io/en/latest/citus.html Patroni automates deployment of Multi-Node Citus clusters. TL;DR Apply these rules: Install the Citus PostgreSQL extension on every node. The minimum supported version is 10.0. We recommend …

Source: https://patroni.readthedocs.io/en/latest/dcs_failsafe_mode.html The problem Patroni uses the Distributed Configuration Store (DCS) for leader elections and network partition detection. A node can run Postgres as primary only if it can update …

Source: https://patroni.readthedocs.io/en/latest/watchdog.html Multiple PostgreSQL primaries can lose transactions because their timelines diverge. This is called split-brain. Patroni must prevent PostgreSQL from accepting transaction commits after …

Source: pgBackRest project metrics, captured for the 2.59.2 documentation snapshot. Code Coverage pgBackRest aims to have complete function/branch/line coverage for the core C code in /src. Function/line coverage is complete with no exceptions. …

Source: https://www.pgbouncer.org/community.html Tutorials How to Set Up PgBouncer for Postgres Plus Standard Server Good overview of PgBouncer concepts. What is the point of bouncing? Discusses differences between pooling modes. Support Project …

Source: https://patroni.readthedocs.io/en/latest/replication_modes.html Patroni uses PostgreSQL streaming replication. See the Postgres documentation. Patroni configures asynchronous replication by default. Select a replication design for your …

The Redis module organizes production Redis into clusters. A cluster is a logical entity with Redis instances on one or more nodes. Each cluster is an autonomous, high-performance cache or storage unit with at least one Redis instance. It exposes …

MINIO is the compatibility module name for object storage. Pigsty v4.5.0 deploys Silo with minio_type: silo. It groups object storage instances into a cluster. Each cluster is an autonomous S3-compatible object store with at least one instance. It …

RTO Timeline Failure Model Phase Best Worst Average Notes Demote retry loop + retry loop/2 + retry Patroni retries after detecting partition, demotes after timeout Lease Expiration ttl - loop - retry ttl - loop - retry ttl - loop - retry Remaining …

The ETCD module organizes production ETCD into clusters. A cluster is a logical entity whose ETCD instances coordinate through the Raft consensus protocol. Each cluster is an autonomous distributed key-value store with at least one ETCD instance. It …

Patroni failures have 10 categories by failure target. Detection paths group them into five categories, described here. # Failure Scenario Description Final Path 1 PG process crash crash, OOM killed Active Detection 2 PG connection refused …

PGSQL organizes PostgreSQL as clusters. Each cluster is a logical group of database instances connected through primary-replica relationships. Each cluster is an autonomous business unit with at least one primary instance. It exposes database …

PIG is a command-line tool for installing and managing PostgreSQL and its extensions. It maps extension names to RPM or DEB packages and uses apt, dnf, or yum to install them. The catalog contains 603 extension entries. PIG selects package names for …

RTO Timeline Failure Model Item Best Worst Average Description Crash Found 0 loop loop/2 Best: PG crashes right before checkWorst: PG crashes right after check Restart Timeout 0 start start Best: PG recovers instantlyWorst: Wait full start timeout …

RPO (Recovery Point Objective) defines the maximum amount of data loss allowed when the primary fails. Critical data, such as financial transactions, typically requires RPO = 0: no data loss. Stricter RPO targets increase write latency and reduce …

Source: https://patroni.readthedocs.io/en/latest/README.html Patroni is a Python template for high availability (HA) PostgreSQL solutions. It started as a fork of Governor, a Compose project, and adds many features. For additional background info, …

The INFRA module is a management hub made of infrastructure nodes, rather than a traditional cluster. It provides core services for the deployment. Each node is an autonomous service unit that runs components such as Nginx, Grafana, and …

Source: https://www.pgbouncer.org/features.html Connection pooling modes with different connection reuse intervals: Session pooling PgBouncer assigns a server connection to a client for its entire session. The connection returns to the pool when the …

RTO Timeline Failure Model Phase Best Worst Average Description Lease Expiration ttl - loop ttl ttl - loop/2 Best: crash just before refreshWorst: crash right after refresh Replica Detect 0 loop loop / 2 Best: exactly at check pointWorst: just …