Model of Patroni Passive Failure
Failover path triggered by node crash causing leader lease expiration and cluster election
Categories: Concept
E-R Model of Infra Cluster
Entity-Relationship model for INFRA infrastructure nodes in Pigsty, component composition, and naming conventions.
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.
Concepts
Understand Pigsty’s core concepts, architecture design, and principles. Master high availability, backup recovery, security compliance, and other key capabilities.
Architecture
Pigsty’s modular architecture—declarative composition, on-demand customization, flexible deployment.
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.
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 440 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?
Percona
Percona Postgres distribution with TDE transparent encryption support
OpenHalo
MySQL compatible Postgres 14 fork
OrioleDB
Next-generation OLTP engine for PostgreSQL
PG Kernels
How to use other PostgreSQL kernel forks in Pigsty? Such as Citus, Babelfish, IvorySQL, PolarDB, etc.
Citus
Deploy native high-availability Citus horizontally sharded clusters with Pigsty, seamlessly scaling PostgreSQL across multiple shards and accelerating OLTP/OLAP queries.
Babelfish
Create Microsoft SQL Server compatible PostgreSQL clusters using WiltonDB and Babelfish! (Wire protocol level compatibility)
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)
PostgresML
How to deploy PostgresML with Pigsty: ML, training, inference, Embedding, RAG inside DB.
Greenplum
Deploy/Monitor Greenplum clusters with Pigsty, build Massively Parallel Processing (MPP) PostgreSQL data warehouse clusters!
Cloudberry
Deploy/Monitor Cloudberry clusters with Pigsty, an MPP data warehouse cluster forked from Greenplum!
Neon
Use Neon’s open-source Serverless PostgreSQL kernel to build flexible, scale-to-zero, forkable PG services.