Categories: Concept

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. Patroni v3.0+ provides native high-availability support for Citus, …

If a classic 3-node HA deployment experiences simultaneous failure of two nodes (majority), the system typically cannot complete automatic failover and requires manual intervention. First, assess the status of the other two servers. If they can be …

In Pigsty, you can replace the native PostgreSQL kernel with different PostgreSQL “flavors” to unlock specialized capabilities. Pigsty supports multiple PostgreSQL kernels and compatibility branches so you can get compatibility layers, multi-master …

Supabase — Build in a weekend, Scale to millions Supabase is an open-source Firebase alternative that wraps PostgreSQL and provides authentication, out-of-the-box APIs, edge functions, real-time subscriptions, object storage, and vector embedding …

Neon adopts a storage and compute separation architecture, providing seamless autoscaling, scale to zero, and unique database branching capabilities. Neon official website: https://neon.tech/ Neon binaries are currently too large to include in the …

Pigsty supports deploying Greenplum clusters and its derivative distribution YMatrixDB, and provides the capability to integrate existing Greenplum deployments into Pigsty monitoring. Overview Greenplum / YMatrix cluster deployment capabilities are …

Pigsty allows you to create PolarDB for Oracle clusters with “domestic innovation qualification” credentials using PolarDB! According to the Security and Reliability Evaluation Results Announcement (No. 1, 2023), Appendix 3, Centralized Database. …

Overview Pigsty allows you to create PostgreSQL clusters with “domestic innovation qualification” credentials using PolarDB! PolarDB for PostgreSQL now uses PostgreSQL 17 as its base. The polar template, default path, and extension notes in Pigsty …

IvorySQL is an open-source PostgreSQL kernel fork that aims to provide “Oracle compatibility” based on PG. Overview The Pigsty PGSQL repository directly provides IvorySQL 5.4 packages compatible with PostgreSQL 18.4 across the currently supported EL, …

Pigsty natively supports Citus. This is a distributed horizontal scaling extension based on the native PostgreSQL kernel. Installation Citus is a PostgreSQL extension plugin that can be installed and enabled on a native PostgreSQL cluster following …

DocumentDB is an open-source PostgreSQL document database extension maintained by Microsoft. FerretDB is a stateless protocol translation proxy built on top of it. Together, they expose a MongoDB wire-compatible endpoint from a standard PostgreSQL …

pgEdge is a distributed PostgreSQL distribution for edge scenarios, built on Spock multi-master logical replication. Overview Pigsty integrates pgEdge through pg_mode: pgedge and delivers it through the standard PostgreSQL cluster workflow: pgedge: a …

AgensGraph is a property graph database kernel built on PostgreSQL, supporting openCypher queries and mixed Cypher/SQL workflows. Overview Pigsty integrates AgensGraph through pg_mode: agens while preserving most of the standard PostgreSQL …

Cloudberry is an open-source MPP data warehouse kernel derived from the Greenplum ecosystem, suitable for large-scale parallel analytics workloads. Overview In Pigsty, Cloudberry uses gpsql mode and shares the same identity model, monitoring logic, …

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

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. Starting with v4.4.0, Pigsty packages Percona PostgreSQL under the private /usr/pgtde-$v; v4.5.0 keeps this layout prefix (/usr/pgtde-18 for PostgreSQL …

Babelfish is a PostgreSQL-based SQL Server compatibility layer, open-sourced by AWS. Overview Pigsty lets you deploy Babelfish in mssql mode and provide, on top of PostgreSQL: SQL Server wire protocol compatibility (TDS, 1433) T-SQL compatibility …

PostgreSQL is the world’s most advanced and popular open-source database. Pigsty installs PostgreSQL 18 by default, supports PostgreSQL 14 ~ 18, and provides 575 PG extensions. Quick Start Install Pigsty using the pgsql configuration template. …

Pigsty’s backup and restore operations ultimately execute pgBackRest commands. Using them safely requires both pgBackRest’s model and the mapping from Pigsty’s orchestration layers to native options. Core pgBackRest Concepts Stanza: the Cluster’s …

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 for building production-grade database services in local environments with declarative configuration and automation. It has a vast ecosystem providing a complete set of tools, …

Compliance is not a product you can buy. It is a state that must be demonstrated continuously through three elements: Configuration: whether security controls are enabled. Pigsty directly provides this part. Process: access approval, change …

Network boundaries, authentication, and access control reduce the likelihood of an incident. When hardware fails, credentials leak, or an operator makes a mistake, data-layer controls must limit the impact and support recovery. Data security answers …

TLS can provide three separate protections: transport encryption, server authentication, and client authentication. Each must be configured independently. Enabling server-side TLS does not mean the client verifies the server identity, nor does it …

Authentication answers “Who are you?” Authorization answers “What may you do?” Privilege failures rarely result from a lack of mechanisms—PostgreSQL GRANT and REVOKE are sufficiently precise. The usual problem is the absence of conventions that are …

PostgreSQL uses pg_hba.conf for Host-Based Authentication: who may connect, from where, to which database, and how they must prove their identity. The mechanism is powerful, but expensive to maintain manually across a cluster. Primary and replica …

Before examining individual security features, answer two more fundamental questions: Where is the root of trust? and How many defensive layers exist? The first determines what deserves the strongest protection. The second determines what remains …

The database is usually the most sensitive component in an information system: it stores the most valuable data, so attacks and failures can have the most serious consequences. Database security is not a feature that can be enabled with one switch. …

Split read and write operations, route traffic correctly, and deliver PostgreSQL cluster capabilities reliably. Service is an abstraction: it represents the form in which database clusters expose their capabilities externally, encapsulating …

Pigsty’s monitoring system has three pillars—metrics, logs, and alerting—and is available out of the box. Logs and alerts are also important inputs for audit and traceability. It can monitor clusters managed by Pigsty, existing PostgreSQL clusters, …

Pigsty allows you to use a PostgreSQL metabase as a dynamic configuration source, replacing static YAML configuration files for more powerful configuration management capabilities. Overview CMDB (Configuration Management Database) is a method of …

In Pigsty, deployment blueprint details are defined by the inventory, which is the pigsty.yml configuration file. You can customize it through declarative configuration. However, writing configuration files directly can be daunting for new users. To …

During an incident, the most expensive resource is often decision time. Pigsty can orchestrate the mechanical recovery steps, but an operator must still answer three questions: what is the target, should recovery be in place or into a clone, and how …

The value of a backup system is realized at restore time, often during an incident when every minute matters. A traditional PITR procedure requires a long sequence of coupled manual steps: pause HA, stop PostgreSQL, prepare recovery settings, restore …

A backup is an insurance policy. Its premium is storage, network traffic, and operational work; its benefit is how much data can be recovered and how quickly service can return. There is no universal free policy: more history normally needs more …

In the inventory, you can use various parameters to fine-tune Pigsty customization. These parameters cover everything from infrastructure settings to database configuration. Parameter List According to the current source and parameter reference …

Pigsty provides a configure script as a configuration wizard that automatically generates an appropriate pigsty.yml configuration file based on your current environment. This is an optional script: if you already understand how to configure Pigsty, …

The PITR principle is compact; the engineering is not. WAL archiving must not stall production writes, object-storage backups need encryption, backup jobs must follow the primary after failover, shared repositories must isolate clusters, and large …

If a database is a state machine, WAL (Write-Ahead Log) is its ordered change history. PostgreSQL records each modification in WAL before applying it to data files. Save a physical snapshot at one point, preserve all later WAL, and PostgreSQL can …

RTO (Recovery Time Objective) defines the maximum time required for the system to restore write capability when the primary fails. For critical transaction systems where availability is paramount, the shortest possible RTO is typically required, such …

If data, a table, or even a database is deleted accidentally, Point-in-Time Recovery (PITR) can return the cluster to an earlier state. This capability, once treated as specialist DBA work, is enabled by Pigsty’s standard PostgreSQL configuration. …

The PGSQL module organizes PostgreSQL in production as clusters—logical entities composed of a group of database instances associated by primary-replica relationships. Overview The PGSQL module includes the following components, working together to …

Running production-grade, highly available PostgreSQL clusters typically requires a comprehensive set of infrastructure services (foundation) for support, such as monitoring and alerting, log collection, time synchronization, DNS resolution, and …

Overview Pigsty’s PostgreSQL clusters come with out-of-the-box high availability, with core capabilities provided by Patroni, Etcd, and HAProxy. When your PostgreSQL cluster has two or more instances, you automatically have self-healing database …

Pigsty follows the IaC and GitOPS philosophy: use a declarative config inventory to describe the entire environment, and materialize it through idempotent playbooks. Users describe their desired state declaratively through parameters, and playbooks …

The largest entity concept in Pigsty is a Deployment. The main entities and relationships (E-R diagram) in a deployment are shown below: A deployment can also be understood as an Environment. For example, Production (Prod), User Acceptance Testing …

A node is an abstraction of hardware resources and operating systems. It can be a physical machine, bare metal, virtual machine, or container/pod. Any machine running a Linux OS (with systemd daemon) and standard CPU/memory/disk/network resources can …

Pigsty uses a modular architecture with a declarative interface. You can freely combine modules like building blocks as needed. Pigsty adopts a modular design that can be freely combined and used on demand (use one or all) to suit different …

The Redis module organizes Redis in production as clusters—logical entities composed of a group of Redis instances deployed on one or more nodes. Each cluster is an autonomous high-performance cache/storage unit consisting of at least one Redis …

MINIO is Pigsty’s compatibility module name for object storage. The current v4.5.0 source deploys Silo through minio_type: silo and organizes a group of object-storage instances into a cluster. Each cluster is an autonomous S3-compatible …

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 ETCD in production as clusters—logical entities composed of a group of ETCD instances associated through the Raft consensus protocol. Each cluster is an autonomous distributed key-value storage unit consisting of at least …

Patroni failures can be classified into 10 categories by failure target, and further consolidated into five categories based on detection path, which are detailed in this section. # Failure Scenario Description Final Path 1 PG process crash crash, …

The PGSQL module organizes PostgreSQL in production as clusters—logical entities composed of a group of database instances associated by primary-replica relationships. Each cluster is an autonomous business unit consisting of at least one primary …

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. For scenarios where data integrity is critical, such as financial transactions, RPO = 0 is typically required, meaning no data loss is allowed. …

The INFRA module plays a special role in Pigsty: it’s not a traditional “cluster” but rather a management hub composed of a group of infrastructure nodes, providing core services for the entire Pigsty deployment. Each INFRA node is an autonomous …

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 …