DOCSEARCHbyAlgolia
Submit Docs

Pigsty (PostgreSQL In Great STYle)

repository·main·Indexed 26 days ago

https://github.com/pgsty/pigsty

An enterprise-grade, open-source PostgreSQL distribution for professional self-hosting. It provides a complete ecosystem including High Availability (HA), Point-in-Time Recovery (PITR), and advanced monitoring via VictoriaMetrics and Grafana. The distribution supports various PostgreSQL kernels and extensions, and includes deployment guides for integrating applications such as Bytebase, Dify, Electric sync engine, Gitea, and Hermes Agent.

Tokens
83.6K
Snippets
202
Records
489
Agent score
91%

What's inside Pigsty

  1. Overview of the repo role

    main
    The repo role builds and serves a local software repository to enable offline installations, faster deployments, and consistent package versioning across nodes. It handles downloading packages from upstream, cleaning up unwanted packages, creating APT/YUM metadata, and serving the repository via Nginx. This is particularly useful for air-gapped or secure environments.

  2. Overview of the pg_id role

    main
    The pg_id role is a prerequisite for most PostgreSQL operations in Pigsty. It runs on the control node (local connection) to derive and calculate PostgreSQL cluster identity and membership information from inventory variables. It computes instance identity, cluster membership, primary/replica relationships, and replication topology. This role is included in most PGSQL playbooks such as pgsql.yml, pgsql-rm.yml, pgsql-pitr.yml, pgsql-user.yml, and pgsql-db.yml.

  3. Overview of available Grafana dashboards

    main

    Pigsty provides 68 built-in dashboards categorized by module. Key categories include:

    • PGSQL: PostgreSQL cluster, instance, database, and query monitoring (e.g., pgsql-overview, pgsql-query, pgsql-replication).
    • PGCAT: Catalog analysis for PostgreSQL (e.g., pgcat-instance, pgcat-query).
    • Node: Host-level metrics like CPU, memory, and disk (e.g., node-overview, node-disk).
    • Infra: Infrastructure components like VictoriaMetrics, Grafana, and Nginx (e.g., infra-overview, vmetrics-instance).
    • Kafka: Kafka cluster, topic, and consumer monitoring.
    • Redis: Redis cluster and instance monitoring.
    • MySQL: MySQL fleet, cluster, and replication monitoring.
    • MinIO: S3-compatible storage monitoring.
    • MongoDB: MongoDB/FerretDB monitoring.
    • App: PostgreSQL log analysis (pglog-overview).

  4. Overview of pg_monitor role

    main

    The pg_monitor role sets up monitoring components for PostgreSQL clusters and registers them with the infrastructure stack.

    Monitoring Exporters:

    • pg_exporter: Collects PostgreSQL metrics (default port 9630).
    • pgbouncer_exporter: Collects pgBouncer metrics (default port 9631).
    • pgbackrest_exporter: Collects backup metrics (default port 9854).

    Infrastructure Registration:

    • Victoria Metrics: Registers exporters as scrape targets.
    • Vector: Configures log collection for PostgreSQL, Patroni, pgBackRest, and pgBouncer.
    • Grafana: Registers databases as datasources.

  5. Overview of Pigsty Features

    main

    Pigsty is an enterprise-grade open-source PostgreSQL distribution designed for self-hosting. Key capabilities include:

    • Extensibility: Supports 555 PostgreSQL extensions and 12 PG kernels across 16 Linux platforms.
    • Reliability: Provides self-healing High Availability (HA) clusters, pre-configured Point-in-Time Recovery (PITR), built-in ACL, and CA/SSL security.
    • Observability: Includes a SOTA monitoring stack based on VictoriaMetrics and Grafana for PostgreSQL, infrastructure, and nodes.
    • Scalability: Customizable database access services using haproxy, pgbouncer, and VIP for auto-routing and pooling.
    • Maintainability: Supports One-Cmd Deploy, Admin SOPs, Auto-Tune, IaC (Infrastructure as Code), and Vagrant/Terraform.
    • Composability: Includes modules for Redis, MinIO, Etcd, Docker, DuckDB, FerretDB, and Supabase.
    • Cloud Neutrality: Can run on bare Linux without requiring Docker or Kubernetes.

  6. Deploy JuiceFS Multi-Instance Filesystem

    main
    The juice role manages multiple JuiceFS instances on a single node. It handles package installation, shared cache directory initialization, instance lifecycle management (create/absent), and registration of metrics targets for monitoring. Each instance is managed as an independent systemd unit with its own environment file and service file.

  7. Understand Pigsty Modular Architecture

    main

    Pigsty is designed as a modular platform where you can compose different modules to build your infrastructure. The core modules include:

    • PGSQL: Self-healing PostgreSQL HA clusters using Patroni, Pgbouncer, PgBackrest, and HAProxy.
    • INFRA: Observability stack including Nginx, Local Repo, DNSMasq, VictoriaMetrics, and Grafana.
    • NODE: Node initialization (name, repo, pkg, NTP, ssh, admin, tuning, logging, and metrics).
    • ETCD: Distributed configuration store used by PostgreSQL HA agents.

    By deploying these modules on multiple nodes, you can automatically form High Availability (HA) clusters. The default deploy.yml playbook deplinstalls INFRA, NODE, ETCD, and PGSQL on the current node to create a singleton instance.

  9. Perform Point-In-Time Recovery (PITR) with pg_pitr

    main

    The pg_pitr role uses pgBackRest to restore PostgreSQL clusters to a specific point in time, transaction ID (XID), Log Sequence Number (LSN), or named restore point.

    WARNING: This is a DANGEROUS operation that will replace existing data. Always verify backups and test in non-production environments first.

  10. Understand the node_id role

    main
    The node_id role is a prerequisite for most Pigsty node operations. It gathers and calculates node identity and operating system information by running on target hosts to collect facts and processing them on the control node. It is used to derive variables for OS information, node resources, and node identity.

  11. Deploy a production-ready PostgreSQL HA cluster with the pgsql role

    main

    The pgsql role is the core component for deploying high-availability PostgreSQL clusters in Pigsty. It automates the provisioning of a complete stack including:

    • Patroni: HA orchestration and automatic failover.
    • pgBackRest: Point-in-Time Recovery (PITR) and backups.
    • pgBouncer: Connection pooling.
    • VIP Manager: L2 Virtual IP (VIP) binding.
    • HAProxy: Service exposure and load balancing.
    • DNS: Service discovery registration.

    To perform a full deployment (including identity, monitoring, and the cluster), use the pgsql.yml playbook.

    # Full deployment
./pgsql.yml -l pg-test

  12. Provision and configure Linux nodes with the node role

    main

    The node role provisions and configures Linux nodes for Pigsty workloads. It handles hostname and DNS configuration, security settings (firewall, SELinux, sudo), CA certificates, package management, kernel tuning, admin user setup, time synchronization (NTP), and optional L2 VIP via keepalived.

    Supported Platforms:

    • RHEL/Rocky 8-10
    • Ubuntu 22/24/26
    • Debian 12-13

    Available Playbooks:

    • node.yml: Performs full node provisioning.
    • node-rm.yml: Removes node components.