Concepts

Pigsty is a portable, extensible, open-source PostgreSQL distribution. It uses declarative configuration and automation to build production database services in local environments. Its tools, scripts, and operating practices support PostgreSQL services at enterprise RDS levels.

Pigsty means PostgreSQL In Great STYle. It also means Postgres, Infras, Graphics, Service, Toolbox, it’s all Yours. It provides self-hosted PostgreSQL with graphical monitoring. Find source code on GitHub and information in the official documentation. Try the Web UI in the online demo.

Why Pigsty? What Can It Do?

PostgreSQL provides the database engine. Production service also requires high availability, backup and recovery, monitoring and alerts, access control, tuning, extensions, connection pools, and load balancing.

Pigsty automates this operational work.

Pigsty provides:

• Ready-to-Use PostgreSQL Distribution

  Pigsty integrates 600 extensions for distributed, time-series, geographic, spatial, graph, vector, search, and other multi-modal workloads. It provides production database services for PostgreSQL 14–18 on EL, Debian, and Ubuntu.

• Self-Healing High Availability Architecture

  The high availability architecture uses Patroni, Etcd, and HAProxy for automatic hardware failover and traffic routing. Primary failure recovery has RTO < 45s and RPO ≈ 0. Cluster maintenance and upgrades can proceed in sequence without application coordination.

• Complete Point-in-Time Recovery Capability

  pgBackRest and an optional Silo object-storage cluster provide point-in-time recovery (PITR). Recover quickly to a selected time after software defects or accidental data deletion.

• Flexible Service Access and Traffic Management

  HAProxy, Pgbouncer, and VIP provide service access with read/write separation, connection pools, and automatic routing. These components provide stable, high-performance database access with transaction pooling.

• Monitoring and Diagnostics

  VictoriaMetrics and Grafana provide monitoring practices with more than three thousand metric types. Views range from global dashboards to CRUD operations on individual objects.

• Declarative Configuration Management

  Pigsty follows Infrastructure as Code. Describe the required environment in declarative configuration. The system applies the changes needed to reach that state.

• Modular Architecture Design

  The modular architecture supports different deployments. Combine the core PostgreSQL module with optional Redis, MINIO (Silo), and Etcd modules. Pigsty also supports PG-compatible kernels and operating modes.

• Solid Security Best Practices

  Security features include a self-signed CA, encrypted communication, AES-encrypted backups, and SCRAM-SHA-256 password hashes. Pigsty also supplies an ACL model and HBA rules that use least privilege.

• Simple and Easy Deployment

  Prepackaged dependencies support one-step installation without Internet access. Local sandboxes can run on VMs with 1 core and 2GB RAM, with the same functions as production environments. Use Vagrant for local sandboxes or Terraform for cloud deployments.

What Pigsty Is Not

Pigsty is not a complete PaaS (Platform as a Service) system.

• Supply your own hardware resources. Pigsty runs on physical servers, VMs, or cloud instances that you provide. It does not create or manage them. Terraform templates help prepare cloud resources.

• Pigsty runs on the operating system. It does not require Kubernetes or Docker, but can coexist with them. Its Docker module runs stateless applications.

• Pigsty focuses on PostgreSQL. It also supports components such as Redis, Etcd, and Silo, but PostgreSQL remains its core.

• Pigsty avoids vendor lock-in. It uses open-source components, preserves the PostgreSQL kernel, and adds no proprietary protocols. You can continue to use managed PostgreSQL clusters after removing Pigsty.

You can choose how to build and operate your database services:

• Override any default parameter or configuration template.
• Use the declarative API or manage components directly with Ansible, Patroni, pgBackRest, and other tools.
• Manage the full database lifecycle, or use monitoring alone for existing instances and RDS.

Pigsty operates at the database service layer. It combines existing tools to deliver PostgreSQL services on your hardware.

Evolution of PostgreSQL Deployment

The following comparison explains the PostgreSQL deployment approaches that Pigsty addresses.

Manual Deployment Era

Traditional deployment requires DBAs to install PostgreSQL, configure replication and monitoring, and handle failures manually. This has several costs:

• Repeated work: Each instance needs manual operations, which can introduce errors.
• Inconsistent configuration: Different DBA practices make maintenance harder.
• Longer recovery: Manual failure handling takes time and can introduce human errors.
• Limited monitoring: Separate monitoring systems make detection and diagnosis harder.

Managed Database Era

Cloud providers offer managed databases (RDS) to reduce this work. These services also have constraints:

• Cost: Service fees typically range from several times to dozens of times the hardware cost.
• Vendor lock-in: Dependence on one cloud platform complicates migration.
• Feature limits: Advanced features, extensions, and parameter changes can be restricted.
• Data control: Cloud storage reduces direct control over data.

Local RDS Era

Pigsty provides a third option: local database services with capabilities comparable to or greater than cloud RDS.

This approach combines:

• Automation: One-step deployment, automatic configuration, and automatic failure recovery provide convenience comparable to cloud RDS.
• Control: Run on your infrastructure and retain control of your data.
• Cost: Operate enterprise database services at costs close to the hardware cost.
• Features: Use PostgreSQL capabilities and ecosystem extensions without imposed restrictions.
• Portability: Open-source components allow migration without vendor lock-in.

This approach is particularly suitable for:

• Private and hybrid clouds: Enterprises needing to run databases in local environments.
• Cost-sensitive users: Organizations looking to reduce database TCO.
• High-security scenarios: Critical data requiring complete autonomy and control.
• PostgreSQL power users: Scenarios requiring advanced features and rich extensions.
• Development and testing: Quickly setting up databases locally that match production environments.

What’s Next

Continue with these topics:

• Read System Architecture for the modular design.
• Read Cluster Model for database cluster organization.
• Read High Availability for automatic failure recovery.
• Read Point-in-Time Recovery for recovery after data deletion.
• Read Service Access for stable database connections.
• Read Infrastructure as Code for declarative configuration.
• Follow Quick Start to deploy your first environment in minutes.