PGSQL

Citus is a PostgreSQL extension that turns PostgreSQL into a distributed database. It scales horizontally across nodes to process large data volumes and query workloads. Patroni v3.0+ provides native high-availability support for Citus. This …

In a typical 3-node HA deployment, simultaneous failure of two nodes removes the majority. The system usually cannot complete automatic failover and needs manual intervention. First, assess the two failed servers. If they can restart quickly, …

Neon separates storage from compute. It provides automatic scaling, scale-to-zero operation, and database branching. Neon official website: https://neon.tech/ Neon binaries are currently too large for the open-source package set. This support remains …

Pigsty supports deployment of Greenplum clusters and the derivative distribution YMatrixDB. You can also add existing Greenplum deployments to Pigsty monitoring. Overview Only the professional/enterprise editions provide Greenplum / YMatrix cluster …

Use Pigsty to create PolarDB for Oracle clusters with “domestic innovation qualification” credentials. The Security and Reliability Evaluation Results Announcement (No. 1, 2023) lists PolarDB v2.0 in Appendix 3, Centralized Database. It identifies …

Overview Use Pigsty and PolarDB to create PostgreSQL clusters with “domestic innovation qualification” credentials. PolarDB for PostgreSQL now uses PostgreSQL 17 as its base. The Pigsty polar template, default path, and extension notes now use PG17. …

IvorySQL is an open-source PostgreSQL kernel fork that aims to provide Oracle compatibility. Overview The Pigsty PGSQL repository provides IvorySQL 5.4 packages compatible with PostgreSQL 18.4. Packages cover the currently supported EL, Debian, and …

Pigsty natively supports Citus. This is a distributed horizontal scaling extension based on the native PostgreSQL kernel. Installation Citus is a PostgreSQL extension plugin. Use the standard extension installation procedure to install and enable it …

Pigsty provides supplementary extension repositories. They supply extension packages in addition to those in the official PGDG repository. YUM Repository Use this repository with EL 8/9/10 and compatible systems, such as RHEL, Rocky, AlmaLinux, and …

Why can’t my current user use the pg admin alias? Since Pigsty v4.0, only members of admin on administration nodes can manage global Patroni and PostgreSQL clusters with pg. The node.yml playbook creates the administration user dba with this …

During PostgreSQL cluster initialization, Pigsty installs and enables some core extensions by default. Default Installed Extensions Pigsty installs these extensions by default through pg_packages: Extension Description pg_repack Handle table bloat …

Extension removal has two levels: drop extension objects from the database, and uninstall packages from the operating system. Drop Extension Objects Use DROP EXTENSION to remove extensions from the database: -- Drop extension DROP EXTENSION pgvector; …

Update extensions at two levels: packages in the operating system, and extension objects in the database. Update Packages Use package managers to update extension packages: # EL systems sudo yum update pgvector_18* # Debian/Ubuntu systems sudo apt …

After you install extension packages, run CREATE EXTENSION in the database to use their features. View Available Extensions After you install extension packages, run these queries to show the available extensions: -- View all available extensions …

Some extensions require you to preload dynamic libraries or configure parameters before use. This section gives the configuration procedures. Preload Extensions After installation, you can enable most extensions with CREATE EXTENSION. Some extensions …

Pigsty uses the operating system’s package manager (yum/apt) to install extension packages. Related Parameters Use these two parameters to specify which extensions to install: Parameter Purpose Default Behavior pg_packages Global common packages …

Before you install extensions, make sure that their packages are available from the local repository or an upstream repository. Default Behavior By default, Pigsty downloads the base PostgreSQL 18 kernel packages to the local software repository. The …

Pigsty uses package aliases to simplify extension installation and management. Package Alias Mechanism Extensions have different names at different layers: Layer Example pgvector Example postgis Extension Name vector postgis, postgis_topology, … …

Pigsty includes 600 precompiled PostgreSQL extensions that are ready to use. What are Extensions PostgreSQL extensions add database functionality without changes to the core code. An extension typically consists of three parts: Control file …

To use extensions in Pigsty, complete four steps: Download, Install, Config, and Create. Download: Download extension packages to the local repository. The default local repository only guarantees the base kernel and pgsql-main package set. Install: …

In Pigsty, you can replace the native PostgreSQL kernel with a variant that provides specialized features. Pigsty supports multiple PostgreSQL kernels and compatibility branches within one operational framework. These kernels provide compatibility …

Pigsty provides four preset Patroni/PostgreSQL config templates optimized for different workloads: Template CPU Cores Use Case Characteristics /docs/pgsql/template/oltp.yml 4-128C OLTP transactions High concurrency, low latency …

Microsoft maintains DocumentDB, an open-source PostgreSQL document database extension. FerretDB is a stateless protocol translation proxy built on DocumentDB. Together, they provide a MongoDB wire-compatible endpoint for a standard PostgreSQL kernel. …

pgEdge is a distributed PostgreSQL distribution for edge deployments. It uses Spock multi-master logical replication. Overview Pigsty integrates pgEdge through pg_mode: pgedge and delivers it through the standard PostgreSQL cluster workflow: pgedge: …

AgensGraph is a property graph database kernel built on PostgreSQL. It supports openCypher queries and mixed Cypher/SQL workflows. Overview Pigsty integrates AgensGraph through pg_mode: agens while preserving most of the standard PostgreSQL …

Cloudberry is an open-source MPP data warehouse kernel derived from the Greenplum ecosystem. It supports large-scale parallel analytics workloads. Overview In Pigsty, Cloudberry uses gpsql mode and shares the same identity model, monitoring logic, …

OrioleDB is a PostgreSQL storage engine extension. It claims to provide 4x OLTP performance and to eliminate xid wraparound and table bloat issues. It also claims to provide cloud-native storage, with data stored in S3. Pigsty ships OrioleDB as a …

OpenHalo is an open-source PostgreSQL kernel that provides MySQL wire-protocol compatibility. openHalo is based on PostgreSQL 14.18 and provides wire-level compatibility with MySQL 5.7.32-log / 8.0. Pigsty delivers it through pg_mode: mysql and the …

Percona Postgres is a patched Postgres kernel with pg_tde (Transparent Data Encryption) extension. From v4.4.0, Pigsty installs Percona PostgreSQL under the private prefix /usr/pgtde-$v. Version v4.5.0 keeps this layout, with /usr/pgtde-18 for …

Babelfish is a PostgreSQL-based SQL Server compatibility layer, open-sourced by AWS. Overview Deploy Babelfish in mssql mode to add these features to PostgreSQL: SQL Server wire protocol compatibility (TDS, 1433) T-SQL compatibility Unified …

If all cluster nodes share one L2 network, you can optionally define an L2 VIP for the PostgreSQL cluster. The VIP uses Master-Backup mode. It always points to the node that runs the cluster primary instance. VIP-Manager manages the VIP. It reads the …

PostgreSQL is a widely used open-source database. Pigsty installs PostgreSQL 18 by default. It supports PostgreSQL 14 ~ 18 and provides 600 PG extensions. Quick Start Install Pigsty using the pgsql configuration template. ./configure -c pgsql # Use …

Pigsty provides 600 packaged extensions that are ready to use. The extensions cover 16 major categories, including time-series, geospatial, vector, full-text search, analytics, and feature enhancements. To use extensions in Pigsty, complete four …

Pigsty provides playbooks for cluster provisioning, scaling, user and database management, monitoring, backup, recovery, and migration. Playbook Function pgsql.yml Initialize PostgreSQL cluster or add new replicas pgsql-rm.yml Remove PostgreSQL …

Accidental Data Deletion For a small accidental DELETE, consider pg_surgery or pg_dirtyread for selective recovery in place. -- Immediately disable Auto Vacuum on this table and abort Auto Vacuum worker processes for this table ALTER TABLE …

The PGSQL module provides 638 available monitoring metrics. Metric Name Type Labels Description ALERTS Unknown category, job, level, ins, severity, ip, alertname, alertstate, instance, cls N/A ALERTS_FOR_STATE Unknown category, job, level, ins, …

Pigsty provides Grafana monitoring dashboards for PostgreSQL: Demo & Gallery. The current source provides 31 PostgreSQL-related dashboards. It includes 29 PostgreSQL / PGCAT dashboards under files/grafana/pgsql and two PGLOG dashboards under …

This page describes the Pigsty monitoring architecture, including metrics, logs, and target management. It also explains how to monitor existing PostgreSQL clusters and remote RDS services. Monitoring Overview Pigsty uses these observability …

Parameters and reference documentation

This section provides step-by-step tutorials for common PostgreSQL tasks and scenarios. Citus Cluster: Deploy and manage Citus distributed clusters Disaster Drill: Emergency recovery when 2 of 3 nodes fail PG VIP: Configure L2 VIP for PostgreSQL …

Pigsty includes the pgsql-migration.yml playbook for online database migration with logical replication. The generated automation scripts can reduce application downtime to a few seconds. Logical replication requires PostgreSQL 10 or later. If the …

This document lists possible PostgreSQL and Pigsty failures. It gives standard procedures to locate, resolve, and analyze them. Disk Space Exhausted Disk space exhaustion is the most common type of failure. Symptoms If the database disk is full, …

Detailed explanation of Host-Based Authentication (HBA) in Pigsty. Authentication supports Access Control and Default Privileges. PostgreSQL supports several authentication methods. This page describes HBA, or Host-Based Authentication. HBA rules …

In this context, Database refers to the logical object created using the SQL command CREATE DATABASE within a database cluster. A PostgreSQL server can serve multiple databases at the same time. In Pigsty, define the required databases in the …

In this context, users refer to logical objects within a database cluster created using the SQL commands CREATE USER/ROLE. In PostgreSQL, users belong to the database cluster, rather than a specific database. Create business users before you create …

Database administration and operation tasks

Pigsty access-control documentation is organized by purpose: Access Control Concepts: role model, default privileges, database ACLs, and instance-isolation boundaries. Access Control Configuration: pg_default_roles, pg_users, pg_default_privileges, …

Separate read and write operations, route traffic correctly, and deliver PostgreSQL cluster capabilities reliably. A service exposes database cluster capabilities to clients. It hides the details of the underlying cluster. Services provide stable …

PostgreSQL security combines authentication, authorization, network boundaries, encrypted communication, data protection, and operating procedures. Pigsty provides configuration parameters for these mechanisms. Operators must still harden, verify, …

Split read and write operations, route traffic correctly, and reliably deliver PostgreSQL cluster capabilities. A service exposes database cluster capabilities to clients. It hides the details of the underlying cluster. Services provide stable …

Pigsty uses configuration to control PostgreSQL behavior. This configuration combines inventory files in ~/pigsty/conf/*.yml with PGSQL parameters. After you write the configuration, you can reproduce a customized cluster in a few minutes. The …

Core concepts and architecture design

PGSQL is the core Pigsty module. An Ansible inventory declares PostgreSQL clusters. Patroni and etcd manage high availability. pgBackRest provides backup and PITR. HAProxy, VIP, DNS, PgBouncer, and the observability stack expose and monitor database …

Getting Started Path Order Question Entry 1 What modules does Pigsty include? Modular Architecture, PGSQL Architecture, PGSQL Cluster Model 2 How do I get it running first? Quick Start, Web UI, PostgreSQL Quick Start 3 How should I read the config …

The mongo configuration template is a PostgreSQL deployment mode, not an independent Pigsty module. It combines: PostgreSQL 18 managed by the standard PGSQL module The documentdb extension and its required preload libraries A stateless FerretDB proxy …

PostgreSQL is an open-source relational database. It can store and retrieve different types of data. This guide uses PG as an abbreviation for PostgreSQL. Use this guide if you know basic Linux commands and want to start with PostgreSQL in Pigsty. …

Compliance requires continuous evidence of three elements: Configuration: Security controls must be enabled. Pigsty directly provides configuration. Process: The organization must establish access approval, change management, recovery exercises, and …

Network boundaries, authentication, and access control reduce incident risk. Data controls must limit damage and support recovery after hardware failure, leaked credentials, or operator errors. Data security must provide integrity, recoverability, …

TLS can provide transport encryption, server authentication, and client authentication. Configure each protection separately. Enabling server TLS does not make clients verify the server or make the server require client certificates. Most TLS …

Authentication verifies identity. Authorization determines what that identity can do. PostgreSQL GRANT and REVOKE provide precise control. Privilege problems usually arise from missing default conventions. Examples include making an application …

pg_hba.conf defines PostgreSQL Host-Based Authentication. It controls who can connect, the source address, the database, and the required identity check. Manual HBA maintenance across a cluster requires care. Primary and replica rules can differ, and …

Identify the root of trust and defensive layers before selecting security features. The root of trust needs the strongest protection. Multiple layers preserve some protection if one fails. Trust Boundaries Pigsty uses Ansible for declarative …

Databases usually store the most valuable and sensitive data in an information system. Attacks and failures can therefore have severe consequences. Security requires multiple controls: who can connect, what they can do, and whether traffic is …

Split read and write operations, route traffic correctly, and deliver PostgreSQL cluster capabilities reliably. A service exposes database cluster capabilities through a stable interface that hides the underlying cluster details. Services provide …

Decisions can consume much of the available incident response time. Pigsty automates recovery steps, but the operator must decide three things. Select the target, choose in-place or clone recovery, and define how to validate the result. Read and …

A backup must support recovery when an incident occurs. Traditional PITR requires connected manual steps: pause HA, stop PostgreSQL, prepare recovery settings, restore the backup, and replay WAL. Then validate the target, rebuild metadata, and start …

Backups require storage, network traffic, and operational work. They determine how much data you can recover and how quickly service returns. More history usually requires more capacity. A shorter RTO usually requires newer backups and tested …

The PITR principle is simple, but implementation must address several constraints. WAL archiving must not block production writes. Object storage backups need encryption. Backup jobs must follow the primary after failover, and shared repositories …

A database is a state machine. WAL (Write-Ahead Log) records its changes in order. PostgreSQL writes each change to WAL before applying it to data files. Keep a physical snapshot and all later WAL to replay the database to a selected consistent …

RTO (Recovery Time Objective) defines the maximum time required for the system to restore write capability when the primary fails. Critical transaction systems typically need the shortest practical RTO, often below one minute. Shorter RTO increases …

Point-in-Time Recovery (PITR) can restore an earlier cluster state after accidental deletion of data, a table, or a database. Pigsty enables PITR in its standard PostgreSQL configuration. This work once required specialist DBA knowledge. Replication …

PGSQL organizes PostgreSQL as clusters. Each cluster is a logical group of database instances connected through primary-replica relationships. Overview The PGSQL module combines these components for production HA clusters: Component Type Description …

Overview Pigsty PostgreSQL clusters include HA through Patroni, Etcd, and HAProxy. A cluster with two or more instances gains automatic HA without extra configuration. A surviving instance can provide the complete database service. Clients can …

Quick Start PostgreSQL has two types of version upgrade: minor and major. Their risk and complexity differ substantially. Type Example Downtime Data Compatibility Risk Minor upgrade 17.2 → 17.3 Seconds (rolling) Fully compatible Low Major upgrade 17 …

Quick Start Pigsty provides 600 extensions. To use an extension, complete four steps: download, install, configure, and enable. pg-meta: hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: pg_cluster: pg-meta pg_extensions: [ postgis, …

Pigsty uses crontab to schedule routine backups, transaction freezing, and reorganization of bloated tables and indexes. Quick Reference Operation Quick Command Description Configure Cron Jobs ./pgsql.yml -t pg_crontab -l <cls> Apply pg_crontab …

You can set PostgreSQL parameters at several levels with different scopes and priorities. Pigsty supports four levels, from global to local: Level Scope Configuration Method Storage Location Cluster All instances in cluster Patroni DCS / Tuning …

Overview The Pigsty PGSQL module has multiple components. Each component runs as a systemd service on nodes, except pgBackRest. To maintain production PostgreSQL clusters, learn how these components work and how to manage them. Component Port Service …

Access control combines roles, object privileges, database ACLs, and HBA. This page describes configuration parameters. For design and boundaries, see Access Control Concepts. Pigsty provides a compact ACL model described by these parameters: …

Overview HBA (Host-Based Authentication) controls who can connect to a database, from which source, and with which authentication method. For the authentication model and default rules, see Authentication. Pigsty manages HBA rules through …

tiny.yml is optimized for micro instances and environments with limited resources. It is designed for CPUs with 1-3 cores. It minimizes resource use, limits memory allocation, and disables parallel queries. Pair with node_tune = tiny for OS-level …

Overview Pigsty uses Pgbouncer for PostgreSQL connection pooling. By default, it listens on port 6432 and proxies connections to local PostgreSQL on port 5432. This component is optional. If you do not need many connections, transaction pooling, or …

RTO Timeline Failure Model Phase Best Worst Average Notes Demote retry loop + retry loop/2 + retry Patroni retries after detecting partition, demotes after timeout Lease Expiration ttl - loop - retry ttl - loop - retry ttl - loop - retry Remaining …

Quick Start Pigsty manages HBA rules declaratively. First define HBA rules in the inventory. Then run bin/pgsql-hba <cls> to refresh them. pg-meta: hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: pg_cluster: pg-meta pg_hba_rules: # <--- …

In this document, “database” refers to a logical object within a database cluster created with CREATE DATABASE. A PostgreSQL cluster can serve multiple databases at the same time. Define the required databases in the Pigsty cluster configuration. …

In this document, “user” refers to a logical object within a database cluster created with CREATE USER/ROLE. In PostgreSQL, users belong to the database cluster, rather than a specific database. Create business users before you create business …

Overview Pigsty uses Patroni to manage PostgreSQL clusters. It handles configuration changes, status checks, switchovers, restarts, replica reinitialization, and other operations. To manage clusters with Patroni, use one of these identities: On an …

crit.yml is for transactional workloads with increased consistency and audit requirements. It forces data checksums and Patroni strict synchronous mode. It also adds connection logging and adjusts selected WAL, timeout, and parallel-query parameters. …

PostgreSQL package names differ between operating systems: EL systems (RHEL/Rocky/Alma/…) use formats like pgvector_18, postgis36_18* Debian/Ubuntu systems use formats like postgresql-18-pgvector, postgresql-18-postgis-3 Without aliases, you need …

olap.yml is optimized for online analytical processing (OLAP) on CPUs with 4-128 cores. It supports large queries, high parallelism, relaxed timeouts, and aggressive vacuum. Pair with node_tune = olap for OS-level tuning. Use Cases Use the OLAP …

Patroni failures have 10 categories by failure target. Detection paths group them into five categories, described here. # Failure Scenario Description Final Path 1 PG process crash crash, OOM killed Active Detection 2 PG connection refused …

Quick Start Pigsty manages databases declaratively. First define databases in the inventory. Then run bin/pgsql-db <cls> <dbname> to create or modify them. pg-meta: hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: pg_cluster: pg-meta …

Quick Start Pigsty manages users declaratively. First define users in the inventory. Then run bin/pgsql-user <cls> <username> to create or modify them. pg-meta: hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: pg_cluster: pg-meta …

PGSQL organizes PostgreSQL as clusters. Each cluster is a logical group of database instances connected through primary-replica relationships. Each cluster is an autonomous business unit with at least one primary instance. It exposes database …

oltp.yml is Pigsty’s default configuration template for online transaction processing (OLTP). It is designed for CPUs with 4-128 cores and provides high concurrency, low latency, and high throughput. Pair with node_tune = oltp for OS-level tuning. …

Choosing a “kernel” in Pigsty means determining the PostgreSQL major version, mode/distribution, packages to install, and tuning templates to load. The Pigsty v4.5 source supports PostgreSQL 14-18 and uses 18 by default. Use the following …

RTO Timeline Failure Model Item Best Worst Average Description Crash Found 0 loop loop/2 Best: PG crashes right before checkWorst: PG crashes right after check Restart Timeout 0 start start Best: PG recovers instantlyWorst: Wait full start timeout …

RPO (Recovery Point Objective) defines the maximum amount of data loss allowed when the primary fails. Critical data, such as financial transactions, typically requires RPO = 0: no data loss. Stricter RPO targets increase write latency and reduce …

Pigsty provides four parameter templates for different workloads. Select a template with pg_conf. tiny.yml: Optimized for small nodes, VMs, and demos (the template is labeled for 1-3 cores) oltp.yml: Optimized for OLTP workloads and latency-sensitive …

RTO Timeline Failure Model Phase Best Worst Average Description Lease Expiration ttl - loop ttl ttl - loop/2 Best: crash just before refreshWorst: crash right after refresh Replica Detect 0 loop loop / 2 Best: exactly at check pointWorst: just …

Quick Reference Action Command Description Create Cluster bin/pgsql-add <cls> Create a new PostgreSQL cluster Expand Cluster bin/pgsql-add <cls> <ip...> Add replica to existing cluster Shrink Cluster bin/pgsql-rm <cls> <ip...> Remove instance from …

Select instance and cluster types that meet your requirements. Pigsty supports several PostgreSQL instance and cluster types: Primary: Define a single instance cluster. Replica: Define a basic HA cluster with one primary and one replica. Offline: …