pgsty/pigsty

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.

Star history