Cluster / Instance
Choose the appropriate instance and cluster types based on your requirements to configure PostgreSQL database clusters that meet your needs.
Module: PGSQL
Managing PostgreSQL Clusters
Create/destroy PostgreSQL clusters, scale existing clusters, and clone clusters.
Model of Patroni Passive Failure
Failover path triggered by node crash causing leader lease expiration and cluster election
Parameter Optimization Policy
Learn the parameter optimization strategies Pigsty uses for the 4 different PostgreSQL workload scenarios.
RPO Trade-offs
Trade-off analysis for RPO (Recovery Point Objective), finding the optimal balance between availability and data loss.
Model of Patroni Active Failure
PostgreSQL primary process crashes while Patroni stays alive and attempts restart, triggering failover after timeout
Kernel Version
How to choose the appropriate PostgreSQL kernel and major version.
OLTP Template
PostgreSQL config template optimized for online transaction processing workloads
E-R Model of PostgreSQL Cluster
Entity-Relationship model for PostgreSQL clusters in Pigsty, including E-R diagram, entity definitions, and naming conventions.
Managing PostgreSQL Users
User management - create, modify, delete users, manage role membership, connection pool config
Managing PostgreSQL Databases
Database management - create, modify, delete, rebuild, and clone databases using templates
Failure Model
Detailed analysis of worst-case, best-case, and average RTO calculation logic and results across three classic failure detection/recovery paths
OLAP Template
PostgreSQL config template optimized for online analytical processing workloads
Package Alias
Pigsty provides a package alias translation mechanism that shields the differences in binary package details across operating systems, making installation easier.
CRIT Template
PostgreSQL config template optimized for critical/financial workloads with data safety and audit compliance
Patroni HA Management
Manage PostgreSQL cluster HA with Patroni, including config changes, status check, switchover, restart, and reinit replica.
User/Role
How to define and customize PostgreSQL users and roles through configuration?
Database
How to define and customize PostgreSQL databases through configuration?
Pgbouncer Connection Pooling
Manage Pgbouncer connection pool, including pause, resume, disable, enable, reconnect, kill, and reload operations.
TINY Template
PostgreSQL config template optimized for micro instances and resource-constrained environments
HBA Rules
Detailed explanation of PostgreSQL and Pgbouncer Host-Based Authentication (HBA) rules configuration in Pigsty.
Access Control
Default role system and privilege model provided by Pigsty
Managing PostgreSQL Component Services
Use systemctl to manage PostgreSQL cluster component services - start, stop, restart, reload, and status check.
Parameters
Configure PostgreSQL parameters at cluster, instance, database, and user levels
Manage PostgreSQL Cron Jobs
Configure crontab to schedule PostgreSQL backups, vacuum freeze, and bloat maintenance tasks
Managing PostgreSQL Extensions
Extension management - download, install, configure, enable, update, and remove extensions
Upgrading PostgreSQL Major/Minor Versions
Version upgrade - minor version rolling upgrade, major version migration, extension upgrade
High Availability
Pigsty uses Patroni to implement PostgreSQL high availability, ensuring automatic failover when the primary becomes unavailable.
PGSQL Arch
PostgreSQL module component interactions and data flow.
Point-in-Time Recovery
Pigsty uses pgBackRest to implement PostgreSQL point-in-time recovery, allowing users to roll back to any point in time within the backup policy window.
RTO Trade-offs
Trade-off analysis for RTO (Recovery Time Objective), finding the optimal balance between recovery speed and false failover risk.
How PITR Works
PITR mechanism: base backup, WAL archive, recovery window, and transaction boundaries
PITR Architecture
Pigsty PITR architecture: pgBackRest, repositories, and execution flow
PITR Tradeoffs
PITR strategy tradeoffs: repository choice, space planning, and recommendations
PITR Scenarios
Typical PITR scenarios: data deletion, DDL drops, batch errors, branch restore, and site disasters
Service Access
Pigsty uses HAProxy to provide service access, with optional pgBouncer for connection pooling, and optional L2 VIP and DNS access.
Security and Compliance
Authentication, authorization, encryption, audit, and compliance baseline for database and infrastructure security.
Seven-Layer Security Model
Pigsty defense-in-depth model with layered security baselines from physical to user.
Authentication
HBA rules, password policy, and certificate auth - who can connect and how to prove identity.
Access Control
Pigsty provides an out-of-the-box role and privilege model that enforces least privilege.
Encrypted Communication and Local CA
Pigsty includes a self-signed CA to issue TLS certificates and encrypt network traffic.
Data Security
Data integrity, backup and recovery, encryption and audit.
Compliance Checklist
Map Pigsty security capabilities and evidence preparation using SOC2 and MLPS Level 3.
Getting Started with PostgreSQL
Get started with PostgreSQL—connect using CLI and graphical clients
Extensions
This page lists PostgreSQL extensions supported by Pigsty and their availability overview.
Module: PGSQL
Deploy and manage world’s most advanced open-source relational database — PostgreSQL, customizable and production-ready!
Core Concepts
Configuration
Choose the appropriate instance and cluster types based on your requirements to configure PostgreSQL database clusters that meet your needs.
Service/Access
Split read and write operations, route traffic correctly, and reliably deliver PostgreSQL cluster capabilities.
Access Control
Default role system and privilege model provided by Pigsty
Service / Access
Separate read and write operations, route traffic correctly, and deliver PostgreSQL cluster capabilities reliably.
Administration
Administration
Standard Operating Procedures (SOP) for database administration tasks
User / Role
Users/roles refer to logical objects within a database cluster created using the SQL commands CREATE USER/ROLE.
Database
Database refers to the logical object created using the SQL command CREATE DATABASE within a database cluster.
Authentication / HBA
Detailed explanation of Host-Based Authentication (HBA) in Pigsty.
Access Control
Default role system and privilege model provided by Pigsty
Troubleshooting
Common failures and analysis troubleshooting approaches
Data Migration
How to migrate an existing PostgreSQL cluster to a new Pigsty-managed PostgreSQL cluster with minimal downtime?
Tutorials
Step-by-step guides for common PostgreSQL tasks and scenarios.
Reference
Monitoring
Overview of Pigsty’s monitoring system architecture and how to monitor existing PostgreSQL instances
Dashboard
Pigsty provides numerous out-of-the-box Grafana monitoring dashboards for PostgreSQL
Dashboards
Pigsty provides many out-of-the-box Grafana monitoring dashboards for PostgreSQL
Metrics
Complete monitoring metrics reference for the Pigsty PGSQL module
Metrics List
Complete list and explanation of monitoring metrics provided by the Pigsty PGSQL module
Accidental Deletion
Handling accidental data deletion, table deletion, and database deletion
Playbook
How to manage PostgreSQL clusters with Ansible playbooks
Extensions
Harness the synergistic power of PostgreSQL extensions
HA Drill: Handling 2-of-3 Node Failure
HA scenario response plan: When two of three nodes fail and auto-failover doesn’t work, how to recover from the emergency state?
PostgreSQL
Vanilla PostgreSQL kernel with 510 extensions
Bind a L2 VIP to PostgreSQL Primary with VIP-Manager
Deploy HA Citus Cluster
How to deploy a Citus high-availability distributed cluster?
Babelfish
Use Babelfish (PG17) in Pigsty to provide SQL Server protocol/T-SQL compatibility; mainstream platform coverage is complete from v4.2.0.
Percona
Percona Postgres distribution with TDE transparent encryption support
openHalo
MySQL-compatible Postgres 14 branch
OrioleDB
Next-generation OLTP engine for PostgreSQL
Cloudberry
Use the Cloudberry open-source MPP data warehouse kernel in Pigsty and manage nodes, monitoring, and configuration through gpsql mode.
AgensGraph
Use the AgensGraph (PG16) graph database kernel in Pigsty to get property graph and Cypher/SQL hybrid query capabilities within the PostgreSQL ecosystem.
pgEdge
Use the pgEdge (PG18) kernel in Pigsty to build distributed PostgreSQL for edge scenarios on top of Spock multi-master logical replication.
Param Templates
Use Pigsty’s built-in Patroni config templates or customize your own
PG Kernels
How to use PostgreSQL kernel forks in Pigsty, such as Citus, Babelfish, IvorySQL, PolarDB, and more.
Quick Start
Four-step process overview for using extensions
Introduction
Core concepts of PostgreSQL extensions and the Pigsty extension ecosystem
Packages
Extension package aliases and category naming conventions
Download
Download extension packages from software repositories to local
Install
Install extension packages on cluster nodes
Config
Preload extension libraries and configure extension parameters
Create
Create and enable extensions in databases
Update
Upgrade PostgreSQL extension versions
Remove
Uninstall PostgreSQL extensions
Default Extensions
PostgreSQL extensions installed by default in Pigsty
FAQ
Frequently asked questions about PostgreSQL
Repository
Pigsty extension software repository configuration
Misc
Miscellaneous Topics
Citus
Deploy native high-availability Citus horizontally sharded clusters with Pigsty, seamlessly scaling PostgreSQL across multiple shards and accelerating OLTP/OLAP queries.
IvorySQL
Use HighGo’s open-source IvorySQL kernel to achieve Oracle syntax/PLSQL compatibility based on PostgreSQL clusters.
PolarDB PG
Using Alibaba Cloud’s open-source PolarDB for PostgreSQL kernel to provide domestic innovation qualification support, with Oracle RAC-like user experience.
PolarDB Oracle
Using Alibaba Cloud’s commercial PolarDB for Oracle kernel (closed source, PG14, only available in special enterprise edition customization)
Greenplum
Deploy/Monitor Greenplum clusters with Pigsty, build Massively Parallel Processing (MPP) PostgreSQL data warehouse clusters!
Neon
Use Neon’s open-source Serverless PostgreSQL kernel to build flexible, scale-to-zero, forkable PG services.