Model of Patroni Passive Failure
Failover path triggered by node crash causing leader lease expiration and cluster election
Categories: Concept
Features
PgBouncer features — pooling modes and SQL compatibility
E-R Model of Infra Cluster
Entity-Relationship model for INFRA infrastructure nodes in Pigsty, component composition, and naming conventions.
Introduction
Patroni introduction, quick start, and core high-availability concepts.
RPO Trade-offs
Trade-off analysis for RPO (Recovery Point Objective), finding the optimal balance between availability and data loss.
Model of Patroni Active Failure
PostgreSQL primary process crashes while Patroni stays alive and attempts restart, triggering failover after timeout
Introduction
Why do we need yet another package manager? Especially for Postgres extensions?
E-R Model of PostgreSQL Cluster
Entity-Relationship model for PostgreSQL clusters in Pigsty, including E-R diagram, entity definitions, and naming conventions.
Failure Model
Detailed analysis of worst-case, best-case, and average RTO calculation logic and results across three classic failure detection/recovery paths
E-R Model of Etcd Cluster
Entity-Relationship model for ETCD clusters in Pigsty, including E-R diagram, entity definitions, and naming conventions.
E-R Model of MinIO Cluster
Entity-Relationship model for MinIO clusters in Pigsty, including E-R diagram, entity definitions, and naming conventions.
E-R Model of Redis Cluster
Entity-Relationship model for Redis clusters in Pigsty, including E-R diagram, entity definitions, and naming conventions.
Replication modes
Asynchronous and synchronous replication modes managed by Patroni.
Community
PgBouncer community resources, tutorials, and support
Project Metrics
pgBackRest project code coverage metrics and quality statistics.
Watchdog support
Watchdog integration and fencing considerations for Patroni clusters.
DCS Failsafe Mode
DCS failsafe mode behavior, requirements, and operational caveats.
Citus support
Patroni integration details for Citus coordinator and worker groups.
Concepts
Understand Pigsty’s core concepts, architecture design, learn how high availability, backup recovery, iac, security works
Integration with other tools
Integrating Patroni with external backup and orchestration tools.
Architecture
Pigsty’s modular architecture—declarative composition, on-demand customization, flexible deployment.
Security Considerations
Security considerations for DCS, REST API, and credential handling.
Nodes
A node is an abstraction of hardware/OS resources—physical machines, bare metal, VMs, or containers/pods.
ER Model
How Pigsty abstracts different functionality into modules, and the E-R diagrams for these modules.
HA multi datacenter
Multi-datacenter high-availability patterns with Patroni replication.
Infra as Code
Pigsty uses Infrastructure as Code (IaC) philosophy to manage all components, providing declarative management for large-scale clusters.
High Availability
Pigsty uses Patroni to implement PostgreSQL high availability, ensuring automatic failover when the primary becomes unavailable.
Infrastructure
Infrastructure module architecture, components, and functionality in Pigsty.
PGSQL Arch
PostgreSQL module component interactions and data flow.
Point-in-Time Recovery
Pigsty uses pgBackRest to implement PostgreSQL point-in-time recovery, allowing users to roll back to any point in time within the backup policy window.
RTO Trade-offs
Trade-off analysis for RTO (Recovery Time Objective), finding the optimal balance between recovery speed and false failover risk.
How PITR Works
PITR mechanism: base backup, WAL archive, recovery window, and transaction boundaries
PITR Architecture
Pigsty PITR architecture: pgBackRest, repositories, and execution flow
Configure
Use the configure script to automatically generate recommended configuration files based on your environment.
Parameters
Fine-tune Pigsty customization using configuration parameters
PITR Tradeoffs
PITR strategy tradeoffs: repository choice, space planning, and recommendations
PITR Scenarios
Typical PITR scenarios: data deletion, DDL drops, batch errors, branch restore, and site disasters
Conf Templates
Use pre-made configuration templates to quickly generate configuration files adapted to your environment
Use CMDB as Config Inventory
Use PostgreSQL as a CMDB metabase to store Ansible inventory.
Monitoring System
How Pigsty’s monitoring system is architected and how monitored targets are automatically managed.
Service Access
Pigsty uses HAProxy to provide service access, with optional pgBouncer for connection pooling, and optional L2 VIP and DNS access.
Security and Compliance
Authentication, authorization, encryption, audit, and compliance baseline for database and infrastructure security.
Seven-Layer Security Model
Pigsty defense-in-depth model with layered security baselines from physical to user.
Authentication
HBA rules, password policy, and certificate auth - who can connect and how to prove identity.
Access Control
Pigsty provides an out-of-the-box role and privilege model that enforces least privilege.
Encrypted Communication and Local CA
Pigsty includes a self-signed CA to issue TLS certificates and encrypt network traffic.
Data Security
Data integrity, backup and recovery, encryption and audit.
Compliance Checklist
Map Pigsty security capabilities and evidence preparation using SOC2 and MLPS Level 3.
Backup Mechanism
Backup scripts, cron jobs, backup repository and infrastructure
HA Drill: Handling 2-of-3 Node Failure
HA scenario response plan: When two of three nodes fail and auto-failover doesn’t work, how to recover from the emergency state?
PostgreSQL
Vanilla PostgreSQL kernel with 464 extensions
Supabase
How to self-host Supabase with Pigsty, deploy an open-source Firebase alternative with a complete backend stack in one click.
Deploy HA Citus Cluster
How to deploy a Citus high-availability distributed cluster?
Babelfish
Use Babelfish (PG17) in Pigsty to provide SQL Server protocol/T-SQL compatibility; mainstream platform coverage is complete from v4.2.0.
Percona
Percona Postgres distribution with TDE transparent encryption support
openHalo
MySQL-compatible Postgres 14 branch
OrioleDB
Next-generation OLTP engine for PostgreSQL
Cloudberry
Use the Cloudberry open-source MPP data warehouse kernel in Pigsty and manage nodes, monitoring, and configuration through gpsql mode.
AgensGraph
Use the AgensGraph (PG16) graph database kernel in Pigsty to get property graph and Cypher/SQL hybrid query capabilities within the PostgreSQL ecosystem.
pgEdge
Use the pgEdge (PG17) kernel in Pigsty to build distributed PostgreSQL for edge scenarios on top of Spock multi-master logical replication.
PG Kernels
How to use PostgreSQL kernel forks in Pigsty, such as Citus, Babelfish, IvorySQL, PolarDB, and more.
Citus
Deploy native high-availability Citus horizontally sharded clusters with Pigsty, seamlessly scaling PostgreSQL across multiple shards and accelerating OLTP/OLAP queries.
IvorySQL
Use HighGo’s open-source IvorySQL kernel to achieve Oracle syntax/PLSQL compatibility based on PostgreSQL clusters.
PolarDB PG
Using Alibaba Cloud’s open-source PolarDB for PostgreSQL kernel to provide domestic innovation qualification support, with Oracle RAC-like user experience.
PolarDB Oracle
Using Alibaba Cloud’s commercial PolarDB for Oracle kernel (closed source, PG14, only available in special enterprise edition customization)
Greenplum
Deploy/Monitor Greenplum clusters with Pigsty, build Massively Parallel Processing (MPP) PostgreSQL data warehouse clusters!
Neon
Use Neon’s open-source Serverless PostgreSQL kernel to build flexible, scale-to-zero, forkable PG services.
Patroni 4.1 Documentation
Overview of Patroni high-availability documentation for PostgreSQL.
pgBouncer 1.25 Documentation
PgBouncer - Lightweight connection pooler for PostgreSQL
pgBackRest 2.58 Documentation
Reliable PostgreSQL Backup & Restore — pgBackRest documentation and reference.