pgsty/pigsty
Pigsty
Pigsty is a full-stack orchestration suite for deploying, monitoring, and managing high-availability PostgreSQL clusters and their supporting infrastructure. It functions as a cluster management platform and high-availability suite that automates failover, manages virtual IPs, and ensures data consistency through distributed consensus.
The project distinguishes itself by providing a comprehensive database infrastructure-as-code framework and a dedicated observability stack. It incorporates a backup and recovery manager supporting point-in-time recovery via S3-compatible object storage, alongside compatibility layers that allow PostgreSQL to emulate the wire protocols of Oracle, MySQL, and MongoDB.
Its broader capabilities cover database security hardening through role-based access control and traffic encryption, performance tuning for specific workloads, and advanced traffic management via connection pooling and load balancing. The platform also supports the deployment of integrated components such as Redis, Kafka, and vector search for retrieval-augmented generation tasks.
The system uses idempotent playbooks for infrastructure automation and provides a graphical user interface for cluster administration and web-based database exploration.
Features
- Database High Availability - Creates self-healing high-availability PostgreSQL clusters with automated failover, backup, and point-in-time recovery.
- Database Cluster Deployments - Orchestrates the deployment of high-availability PostgreSQL clusters with automated failover and distributed coordination.
- Backup and Recovery - Manages physical and logical backups with point-in-time recovery using S3-compatible object storage.
- Pool Configuration - Defines and automatically applies connection pool modes, sizes, and authentication settings to the pooler.
- Connection Pools - Manages a lightweight pool of database connections to reduce overhead and improve system scalability.
- Connection Pool Managers - Controls database connection pools by managing the lifecycle and active state of client connections.
- Database Configurations - Configures database-level parameters that apply to all sessions connecting to a specific database.
- Database Performance Tuning - Applies pre-defined tuning templates optimized for OLTP or OLAP workloads to maximize throughput.
- Database Version Migrations - Implements rolling minor version updates and major version migrations to keep database software current.
- Distributed Key-Value Stores - Organizes distributed key-value storage into logical clusters using the Raft consensus protocol for consistent state management.
- High Availability Configurations - Establishes high-availability configurations with data redundancy and failover mechanisms using multi-node patterns.
- PostgreSQL Cluster Management Platforms - Offers a full-stack orchestration suite for deploying, monitoring, and managing high-availability PostgreSQL clusters.
- Cluster Lifecycle Management - Manages the operational lifecycle of database clusters, including creation, instance addition, and controlled switchovers.
- Read Replicas - Offloads database read traffic to multiple replica instances to increase query capacity and reduce primary node load.
- Write Consistency Enforcement - Configures synchronous standby and quorum commit modes to guarantee data durability by requiring replica confirmation.
- Web-Based Database Clients - Ships a web-based interface to browse and explore PostgreSQL database contents from any cross-platform client.
- WAL Archiving - Continuously archives write-ahead logs to S3-compatible storage and manages archive pruning for point-in-time recovery.
- Point-in-Time Recovery - Reconstructs a cluster to a specific timestamp or transaction ID using base backups and archived write-ahead logs.
- WAL and Snapshot Archiving - Streams write-ahead logs and physical snapshots to object storage for point-in-time recovery and disaster resilience.
- Cluster Configuration Management - Manages cluster-wide parameters and high-availability settings with support for granular overrides.
- Self-Healing Database Clusters - Creates self-healing PostgreSQL clusters featuring automatic failover, health-check routing, and point-in-time recovery.
- Infrastructure as Code Frameworks - Provides a declarative infrastructure-as-code framework using idempotent playbooks for provisioning database nodes and poolers.
- Infrastructure Orchestration - Coordinates the execution of infrastructure-as-code playbooks to materialize the desired state of database clusters.
- Infrastructure Automation - Uses idempotent playbooks to automate provisioning and administrative tasks for database clusters.
- Idempotent Infrastructure Playbooks - Uses idempotent playbooks to define and provision the desired state of database clusters across different environments.
- Database Cluster Orchestrators - Employs a remote orchestration tool to control and manage all cluster nodes from a centralized administration node.
- Observability Stacks - Ships a coordinated observability stack utilizing Prometheus and Grafana for centralized metrics, logs, and traces.
- Traffic Load Balancers - Routes client requests across cluster nodes using load balancers to abstract the underlying cluster topology.
- Cluster State Coordinators - Provides a fault-tolerant key-value store to maintain consistent cluster topology and metadata.
- Virtual IP Management - Binds a virtual IP address to the current primary node to provide a consistent entry point for clients.
- Declarative User Provisioning - Manages database users and role memberships through declarative configurations, including automated password updates.
- Role-Based Access Control - Enforces least-privilege access through a predefined system of roles and host-based authentication rules.
- Deployment Security Hardening - Deploys pre-configured security bundles including SSL enforcement and password policies to meet compliance requirements.
- Database Server Hardening - Implements database server hardening including RBAC, TLS encryption, and system-level security configurations.
- Declarative Configuration - Defines databases, users, roles, and schemas declaratively using structured templates and SQL baselines.
- Split-Brain Prevention - Implements watchdog timers to automatically shut down hanging nodes, preventing split-brain scenarios during failover.
- Cluster Health Monitoring - Displays health, roles, replication lag, and timelines of all cluster members in a summary list.
- Cluster Management Interfaces - Provides a graphical user interface for administering database servers and managing cluster configurations.
- Cluster Monitoring Dashboards - Provides graphical dashboards to visualize global key metrics, alert events, and health status across database instances.
- Metrics Visualizers - Displays monitoring data through graphical dashboards to track the health and performance of database clusters.
- Database Performance Metrics - Gathers performance data on sessions, transactions, and replication lag via exporters for monitoring dashboards.
- Observability Stack Deployments - Deploys a coordinated set of metrics exporters and log collectors alongside the database for system visibility.
- PostgreSQL Performance Monitoring - Collects internal performance statistics from PostgreSQL and other data stores to expose them for external monitoring.
- Observability Stacks - Deploys a coordinated observability stack for collecting time-series metrics, structured logs, and distributed traces.
- Accidental Data Loss Protections - Implements zero-data-loss tuning and delayed replicas to recover from accidental deletions or corruption.
- Infrastructure Metadata Schemas - Stores cluster metadata and variable definitions in a relational database for dynamic configuration via SQL.
- Cron-Driven Backup Schedules - Schedules full and incremental backups using flexible cron expressions to ensure data recoverability.
- Backup and Restore Monitoring - Provides visibility into data protection reliability by exporting metrics on backup timing, size, and status.
- Standby Cluster Cloning - Creates standby clusters that continuously sync from upstream sources to ensure disaster recovery readiness.
- Backup Storage Backends - Supports database backup storage across local filesystems and S3-compatible object stores like MinIO, AWS S3, and GCS.
- Online Bloat Removal - Implements online removal of table and index bloat using size and ratio thresholds to reclaim disk space.
- Branch-Based Object Restoration - Creates temporary historical clusters to export and migrate specific dropped tables back to production.
- Connection Pool Observability - Tracks metrics for load balancers and connection poolers to optimize traffic routing and session management.
- Logical Backups - Dumps database contents into a portable format for data migration between major versions or flexible exports.
- Forensic Branching - Spawns read-only branch clusters at various time points to perform audits or forensics without impacting production.
- Physical - Creates full, incremental, or differential physical snapshots of the database cluster across various storage backends.
- Database Compatibility Layers - Implements compatibility layers that allow PostgreSQL to emulate the wire protocols of other database systems.
- Database Integrity Verification - Detects silent corruption using internal data checksums and recovers damaged blocks from available replicas.
- Database Maintenance - Provides utilities for reorganizing tables, reclaiming unused space, and verifying relation integrity.
- Database Schema Migrations - Tracks and versions database schema migrations through a dedicated interface to coordinate structural updates.
- Zero-Downtime Migrations - Moves existing PostgreSQL data to managed environments using logical replication to minimize service downtime.
- Database Performance Optimizers - Provides pre-defined optimization templates and custom parameter overlays to enhance database performance for specific workloads.
- Database Protocol Compatibility - Implements a wire-protocol compatible API allowing MongoDB clients to interact with a relational PostgreSQL backend.
- Audit Logs - Tracks DDL changes, slow queries, and connection logs through built-in logging and dedicated audit extensions.
- Database Wire Protocol Emulation - Provides compatibility layers enabling PostgreSQL to emulate the wire protocols of Oracle, MySQL, and MongoDB.
- Deleted Row Recovery - Retrieves data from tables after a delete operation by reading dead tuples before they are reclaimed.
- Full Text Search - Implements full-text search capabilities including multi-language tokenization, BM25 ranking, and fuzzy string matching.
- SQL Geospatial Analysis - Provides spatial data types and SQL functions for storing and analyzing geographic and geometric information.
- Columnar Query Accelerators - Accelerates OLAP queries through columnar storage, distributed processing, and GPU acceleration.
- Graph Data Models - Supports property-graph modeling with vertices and edges and integrates Cypher query syntax with SQL.
- Configuration Synchronization - Automatically synchronizes Pgbouncer user and database lists based on the current cluster configuration.
- Multi-Master Replication - Supports multi-master regional distribution of PostgreSQL instances to enable local writes and logical replication.
- Graceful Primary Switchovers - Provides a mechanism for planned primary switchovers, ensuring full synchronization before demoting the current primary.
- Oracle Syntax Emulations - The project provides PL/SQL compatibility and Oracle-specific data types to simplify migrations to PostgreSQL.
- PostgreSQL Extensions - Manages the installation, configuration, and removal of PostgreSQL extension packages across cluster nodes.
- Cascading Replication - Configures replica bridge instances that relay WAL data to other replicas to reduce primary load.
- Delayed Replication Configurations - Maintains a delayed standby cluster that trails the primary by a set duration to enable recovery from accidental changes.
- Replica Reinitialization - Implements a recovery mechanism that wipes corrupted replicas and re-initializes them from a fresh base backup.
- Traffic Routing - Provides dedicated service ports to separate traffic for read-write primary access and read-only replicas.
- Redis Cluster Deployments - Ships automated deployment of Redis in standalone, primary-replica, sentinel, or cluster modes.
- Redis Deployment Tools - Automates the deployment of Redis in standalone, Sentinel, or native cluster modes for high availability.
- SQL Server Protocol Emulations - The project supports T-SQL syntax and the TDS wire protocol so that SQL Server client tools can connect to a PostgreSQL backend.
- Storage-Compute Architectures - Deploys a database architecture that decouples storage from compute to enable independent scaling.
- Transaction ID Wraparound Prevention - Executes vacuum freeze operations on aging tables to prevent critical database shutdown due to transaction ID wraparound.
- Vector Similarity Search - Provides vector storage and similarity search capabilities to support retrieval-augmented generation (RAG) tasks.
- Offline Package Caching - Caches complete sets of database kernels and extension packages to enable installations in air-gapped environments.
- Air-Gapped Deployment Strategies - Provides comprehensive methods for installing the database stack in isolated network environments without internet access.
- Database-as-a-Service Tooling - Provides the necessary infrastructure and tooling to offer PostgreSQL as a managed service.
- Cloud Provisioning - Automates the provisioning of virtual machine environments and cloud resources across multiple providers using IaC templates.
- Cluster Expansion - Enables adding new nodes and initializing additional database or cache clusters to expand an existing environment.
- Cluster Scaling Orchestrators - Allows dynamically adjusting database cluster capacity by adding or removing replica nodes through inventory updates.
- Data Suite Orchestration - Provisions a production-grade environment containing integrated PostgreSQL, Redis, Kafka, and MongoDB-compatible components.
- Object Storage Deployment - Provides automated installation and configuration of distributed S3-compatible object storage clusters.
- High-Availability Object Storage - Deploys an S3-compatible object storage cluster using erasure coding across multiple nodes and disks.
- Distributed Database Deployment - Automates the provisioning of high-availability distributed Citus clusters with coordinator and worker nodes.
- Database Platform Deployments - Installs and configures the complete Supabase stack on physical, virtual, or cloud servers.
- Service Configuration Reloading - Triggers configuration reloads for PostgreSQL parameters without interrupting active database connections.
- Database-Backed Inventories - Stores cluster inventory and variables in a relational database to enable dynamic configuration management.
- Traffic Management Gateways - Routes database and infrastructure traffic through unified gateways like HAProxy and Nginx to centralize traffic control.
- Network-Based Access Controls - Applies layered authentication rules based on the client network location to enforce stronger credentials for remote access.
- Certificate Issuance Utilities - Generates and manages security certificates using a self-signed Certificate Authority for secure internal communication.
- Communication Encryption - Encrypts internal traffic between coordinator and worker nodes using SSL certificates and strict verification.
- Certificate-Based Authentication - Implements identity verification using digital certificates to replace or enhance traditional password-based authentication.
- Component Transport Security - Secures communication between clients and infrastructure components, including the REST API, using SSL/TLS encryption.
- Host Hardening - Hardens the host environment using SELinux enforcement and restricted sudo privileges to isolate the database.
- Host-Based Access Restrictors - Defines host-based authentication rules to restrict which client sources can connect to the database.
- Hierarchical Configuration Overrides - Applies database settings through a priority hierarchy from global defaults down to specific instance or user levels.
- Rolling Updates - Implements rolling restart strategies for PostgreSQL instances to apply configuration changes with minimal service disruption.
- Database Fork Deployments - Installs alternative PostgreSQL forks to enable capabilities such as horizontal sharding or graph data modeling.
- Database Service Endpoints - Creates custom network endpoints with specific routing and health checks to control client access to clusters.
- Alert Thresholds - Defines and monitors threshold-based alert rules and recording rules to notify operators of system anomalies.
- Log Aggregation - Collects logs from PostgreSQL, Pgbouncer, and Patroni using Vector and stores them in a centralized service.
- Failover Event Tracking - Tracks the timeline of cluster leader changes, including the reason and timestamp for each switchover.
- System Tuning - Tunes server host states including hostname, timezone, SSH, and sudo configurations via declarative modules.
