Module: PGSQL

Citus is a PostgreSQL extension that transforms PostgreSQL into a distributed database, enabling horizontal scaling across multiple nodes to handle large amounts of data and queries. Patroni v3.0+ provides native high-availability support for Citus, …

If a classic 3-node HA deployment experiences simultaneous failure of two nodes (majority), the system typically cannot complete automatic failover and requires manual intervention. First, assess the status of the other two servers. If they can be …

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 …

PGSQL is Pigsty’s core module. Ansible inventory declares PostgreSQL clusters; Patroni and etcd provide HA orchestration; pgBackRest provides backup and PITR; HAProxy, VIP, DNS, PgBouncer, and the observability stack expose and monitor database …

In Pigsty, you can replace the native PostgreSQL kernel with different PostgreSQL “flavors” to unlock specialized capabilities. Pigsty supports multiple PostgreSQL kernels and compatibility branches so you can get compatibility layers, multi-master …

Neon adopts a storage and compute separation architecture, providing seamless autoscaling, scale to zero, and unique database branching capabilities. Neon official website: https://neon.tech/ Neon binaries are currently too large to include in the …

Pigsty supports deploying Greenplum clusters and its derivative distribution YMatrixDB, and provides the capability to integrate existing Greenplum deployments into Pigsty monitoring. Overview Greenplum / YMatrix cluster deployment capabilities are …

Pigsty allows you to create PolarDB for Oracle clusters with “domestic innovation qualification” credentials using PolarDB! According to the Security and Reliability Evaluation Results Announcement (No. 1, 2023), Appendix 3, Centralized Database. …

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

IvorySQL is an open-source PostgreSQL kernel fork that aims to provide “Oracle compatibility” based on PG. Overview The Pigsty PGSQL repository directly provides IvorySQL 5.4 packages compatible with PostgreSQL 18.4 across the currently supported EL, …

Pigsty natively supports Citus. This is a distributed horizontal scaling extension based on the native PostgreSQL kernel. Installation Citus is a PostgreSQL extension plugin that can be installed and enabled on a native PostgreSQL cluster following …

Pigsty provides supplementary extension repositories, offering additional extension packages on top of the PGDG official repository. YUM Repository Applicable to EL 8/9/10 and compatible systems (RHEL, Rocky, AlmaLinux, CentOS, etc.). Add Repository …

Why can’t my current user use the pg admin alias? Starting from Pigsty v4.0, permissions to manage global Patroni / PostgreSQL clusters using the pg admin alias have been tightened to the admin group (admin) on admin nodes. The admin user (dba) …

Pigsty installs and enables some core extensions by default when initializing PostgreSQL clusters. Default Installed Extensions Extensions installed by default via pg_packages: Extension Description pg_repack Handle table bloat online, important …

Removing extensions involves two levels: dropping extension objects (database level) and uninstalling packages (operating system level). Drop Extension Objects Use DROP EXTENSION to remove extensions from the database: -- Drop extension DROP …

Extension updates involve two levels: package updates (operating system level) and extension object updates (database level). Update Packages Use package managers to update extension packages: # EL systems sudo yum update pgvector_18* # Debian/Ubuntu …

After installing extension packages, you need to execute CREATE EXTENSION in the database to use extension features. View Available Extensions After installing extension packages, you can view available extensions: -- View all available extensions …

Some extensions require preloading dynamic libraries or configuring parameters before use. This section describes how to configure extensions. Preload Extensions Most extensions can be enabled directly with CREATE EXTENSION after installation, but …

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

Before installing extensions, ensure that extension packages are downloaded to the local repository or available from upstream. Default Behavior Pigsty downloads the base PostgreSQL 18 kernel packages to the local software repository by default. The …

Pigsty uses a package alias mechanism to simplify extension installation and management. Package Alias Mechanism Managing extensions involves multiple layers of name mapping: Layer Example pgvector Example postgis Extension Name vector postgis, …

Extensions are the soul of PostgreSQL. Pigsty includes 575 pre-compiled, out-of-the-box extension plugins, fully unleashing PostgreSQL’s potential. What are Extensions PostgreSQL extensions are a modular mechanism that allows enhancing database …

Using extensions in Pigsty requires 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: …

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 …

DocumentDB is an open-source PostgreSQL document database extension maintained by Microsoft. FerretDB is a stateless protocol translation proxy built on top of it. Together, they expose a MongoDB wire-compatible endpoint from a standard PostgreSQL …

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

AgensGraph is a property graph database kernel built on PostgreSQL, supporting 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, suitable for 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 that claims to provide 4x OLTP performance, no xid wraparound and table bloat issues, and “cloud-native” (data stored in S3) capabilities. Pigsty ships OrioleDB as a patched PostgreSQL kernel plus the …

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. Starting with v4.4.0, Pigsty packages Percona PostgreSQL under the private /usr/pgtde-$v; v4.5.0 keeps this layout prefix (/usr/pgtde-18 for PostgreSQL …

Babelfish is a PostgreSQL-based SQL Server compatibility layer, open-sourced by AWS. Overview Pigsty lets you deploy Babelfish in mssql mode and provide, on top of PostgreSQL: SQL Server wire protocol compatibility (TDS, 1433) T-SQL compatibility …

You can define an OPTIONAL L2 VIP on a PostgreSQL cluster, provided that all nodes in the cluster are in the same L2 network. This VIP works on Master-Backup mode and always points to the node where the primary instance of the database cluster is …

PostgreSQL is the world’s most advanced and popular open-source database. Pigsty installs PostgreSQL 18 by default, supports PostgreSQL 14 ~ 18, and provides 575 PG extensions. Quick Start Install Pigsty using the pgsql configuration template. …

Pigsty provides 575 packaged extensions, covering 16 major categories including time-series, geospatial, vector, full-text search, analytics, and feature enhancements, ready to use out-of-the-box. Using extensions in Pigsty involves four core steps: …

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

Accidental Data Deletion If it’s a small-scale DELETE misoperation, you can consider using the pg_surgery or pg_dirtyread extension for in-place surgical recovery. -- Immediately disable Auto Vacuum on this table and abort Auto Vacuum worker …

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 numerous out-of-the-box Grafana monitoring dashboards for PostgreSQL: Demo & Gallery. The current source provides 31 PostgreSQL-related dashboards: 29 PostgreSQL / PGCAT dashboards under files/grafana/pgsql, plus two PGLOG dashboards …

This document introduces Pigsty’s monitoring system architecture, including metrics, logs, and target management. It also covers how to monitor existing PG clusters and remote RDS services. Monitoring Overview Pigsty uses a modern observability stack …

Parameters and reference documentation

Pigsty includes a built-in playbook pgsql-migration.yml that implements online database migration based on logical replication. With pre-generated automation scripts, application downtime can be reduced to just a few seconds. However, note that …

This document lists potential failures in PostgreSQL and Pigsty, as well as SOPs for locating, handling, and analyzing issues. Disk Space Exhausted Disk space exhaustion is the most common type of failure. Symptoms When the disk space where the …

Detailed explanation of Host-Based Authentication (HBA) in Pigsty. Authentication is the foundation of Access Control and Default Privileges. PostgreSQL supports several authentication methods. Here we mainly introduce HBA: Host Based …

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 simultaneously. In Pigsty, you can 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 directly to the database cluster rather than to a specific database. Therefore, when creating …

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. Service is an abstraction: it is the form in which database clusters provide capabilities to the outside world and encapsulates the …

PostgreSQL security combines authentication, authorization, network boundaries, encrypted communication, data protection, and operational process. Pigsty provides configuration entry points for these mechanisms; operators must still harden, verify, …

Split read and write operations, route traffic correctly, and reliably deliver PostgreSQL cluster capabilities. Service is an abstraction: it is the form in which database clusters provide capabilities externally, encapsulating the details of the …

Pigsty is a “configuration-driven” PostgreSQL platform: all behaviors come from the combination of inventory files in ~/pigsty/conf/*.yml and PGSQL parameters. Once you’ve written the configuration, you can replicate a customized cluster with …

Core concepts and architecture design

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 (abbreviated as PG) is the world’s most advanced and popular open-source relational database. Use it to store and retrieve multi-modal data. This guide is for developers with basic Linux CLI experience but not very familiar with …

Compliance is not a product you can buy. It is a state that must be demonstrated continuously through three elements: Configuration: whether security controls are enabled. Pigsty directly provides this part. Process: access approval, change …

Network boundaries, authentication, and access control reduce the likelihood of an incident. When hardware fails, credentials leak, or an operator makes a mistake, data-layer controls must limit the impact and support recovery. Data security answers …

TLS can provide three separate protections: transport encryption, server authentication, and client authentication. Each must be configured independently. Enabling server-side TLS does not mean the client verifies the server identity, nor does it …

Authentication answers “Who are you?” Authorization answers “What may you do?” Privilege failures rarely result from a lack of mechanisms—PostgreSQL GRANT and REVOKE are sufficiently precise. The usual problem is the absence of conventions that are …

PostgreSQL uses pg_hba.conf for Host-Based Authentication: who may connect, from where, to which database, and how they must prove their identity. The mechanism is powerful, but expensive to maintain manually across a cluster. Primary and replica …

Before examining individual security features, answer two more fundamental questions: Where is the root of trust? and How many defensive layers exist? The first determines what deserves the strongest protection. The second determines what remains …

The database is usually the most sensitive component in an information system: it stores the most valuable data, so attacks and failures can have the most serious consequences. Database security is not a feature that can be enabled with one switch. …

Split read and write operations, route traffic correctly, and deliver PostgreSQL cluster capabilities reliably. Service is an abstraction: it represents the form in which database clusters expose their capabilities externally, encapsulating …

During an incident, the most expensive resource is often decision time. Pigsty can orchestrate the mechanical recovery steps, but an operator must still answer three questions: what is the target, should recovery be in place or into a clone, and how …

The value of a backup system is realized at restore time, often during an incident when every minute matters. A traditional PITR procedure requires a long sequence of coupled manual steps: pause HA, stop PostgreSQL, prepare recovery settings, restore …

A backup is an insurance policy. Its premium is storage, network traffic, and operational work; its benefit is how much data can be recovered and how quickly service can return. There is no universal free policy: more history normally needs more …

The PITR principle is compact; the engineering is not. WAL archiving must not stall production writes, object-storage backups need encryption, backup jobs must follow the primary after failover, shared repositories must isolate clusters, and large …

If a database is a state machine, WAL (Write-Ahead Log) is its ordered change history. PostgreSQL records each modification in WAL before applying it to data files. Save a physical snapshot at one point, preserve all later WAL, and PostgreSQL can …

RTO (Recovery Time Objective) defines the maximum time required for the system to restore write capability when the primary fails. For critical transaction systems where availability is paramount, the shortest possible RTO is typically required, such …

If data, a table, or even a database is deleted accidentally, Point-in-Time Recovery (PITR) can return the cluster to an earlier state. This capability, once treated as specialist DBA work, is enabled by Pigsty’s standard PostgreSQL configuration. …

The PGSQL module organizes PostgreSQL in production as clusters—logical entities composed of a group of database instances associated by primary-replica relationships. Overview The PGSQL module includes the following components, working together to …

Overview Pigsty’s PostgreSQL clusters come with out-of-the-box high availability, with core capabilities provided by Patroni, Etcd, and HAProxy. When your PostgreSQL cluster has two or more instances, you automatically have self-healing database …

Quick Start PostgreSQL version upgrades fall into two types: minor version upgrade and major version upgrade, with very different risk and complexity. Type Example Downtime Data Compatibility Risk Minor upgrade 17.2 → 17.3 Seconds (rolling) Fully …

Quick Start Pigsty provides 575 extensions. Using extensions involves four steps: Download, Install, Configure, Enable. pg-meta: hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: pg_cluster: pg-meta pg_extensions: [ postgis, timescaledb, …

Pigsty uses crontab to manage scheduled tasks for routine backups, freezing aging transactions, and reorganizing bloated tables and indexes. Quick Reference Operation Quick Command Description Configure Cron Jobs ./pgsql.yml -t pg_crontab -l <cls> …

PostgreSQL parameters can be configured at multiple levels with different scopes and precedence. Pigsty supports four configuration levels, from global to local: Level Scope Configuration Method Storage Location Cluster All instances in cluster …

Overview Pigsty’s PGSQL module consists of multiple components, each running as a systemd service on nodes. (pgbackrest is an exception) Understanding these components and their management is essential for maintaining production PostgreSQL clusters. …

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

Overview HBA (Host-Based Authentication) controls “who can connect to the database, from where, and how”. See Authentication for the authentication model and default rules. Pigsty manages HBA rules declaratively through pg_default_hba_rules and …

tiny.yml is optimized for micro instances and resource-constrained environments. Designed for 1-3 core CPUs with minimal resource usage, conservative memory allocation, and disabled parallel queries. Pair with node_tune = tiny for OS-level tuning. …

Overview Pigsty uses Pgbouncer as PostgreSQL connection pooling middleware, listening on port 6432 by default, proxying access to local PostgreSQL on port 5432. This is an optional component. If you don’t have massive connections or need transaction …

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 uses declarative management: first define HBA rules in the inventory, then use bin/pgsql-hba <cls> to refresh. pg-meta: hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: pg_cluster: pg-meta pg_hba_rules: # <--- Define …

In this document, “database” refers to a logical object within a database cluster created with CREATE DATABASE. A PostgreSQL cluster can serve multiple databases simultaneously. In Pigsty, you can define required databases in cluster configuration. …

In this document, “user” refers to a logical object within a database cluster created with CREATE USER/ROLE. In PostgreSQL, users belong directly to the database cluster rather than a specific database. Therefore, when creating business databases …

Overview Pigsty uses Patroni to manage PostgreSQL clusters. It handles config changes, status checks, switchover, restart, reinit replicas, and more. To use Patroni for management, you need one of the following identities: From INFRA node as admin …

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

PostgreSQL package naming conventions vary significantly across different operating systems: EL systems (RHEL/Rocky/Alma/…) use formats like pgvector_18, postgis36_18* Debian/Ubuntu systems use formats like postgresql-18-pgvector, …

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

Patroni failures can be classified into 10 categories by failure target, and further consolidated into five categories based on detection path, which are detailed in this section. # Failure Scenario Description Final Path 1 PG process crash crash, …

Quick Start Pigsty uses declarative management: first define databases in the inventory, then use bin/pgsql-db <cls> <dbname> to create or modify. pg-meta: hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: pg_cluster: pg-meta …

Quick Start Pigsty uses declarative management: first define users in the inventory, then use bin/pgsql-user <cls> <username> to create or modify. pg-meta: hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } } vars: pg_cluster: pg-meta pg_users: [{ …

The PGSQL module organizes PostgreSQL in production as clusters—logical entities composed of a group of database instances associated by primary-replica relationships. Each cluster is an autonomous business unit consisting of at least one primary …

oltp.yml is Pigsty’s default config template, optimized for online transaction processing (OLTP). Designed for 4-128 core CPUs with high concurrency, low latency, and high throughput. Pair with node_tune = oltp for OS-level tuning. Use Cases OLTP …

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 currently supports PostgreSQL 14-18 and uses 18 by default. 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. For scenarios where data integrity is critical, such as financial transactions, RPO = 0 is typically required, meaning no data loss is allowed. …

Pigsty provides four scenario-based parameter templates by default, which can be specified and used through the pg_conf parameter. tiny.yml: Optimized for small nodes, VMs, and demos (the template is labeled for 1-3 cores) oltp.yml: Optimized for …

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 …

Choose the appropriate instance and cluster types based on your requirements to configure PostgreSQL database clusters that meet your needs. You can define different types of instances and clusters. Here are several common PostgreSQL …