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

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.

RTO Trade-offs

Trade-off analysis for RTO (Recovery Time Objective), finding the optimal balance between recovery speed and false failover risk.

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

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.

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

Managing PostgreSQL Component Services

Use systemctl to manage PostgreSQL cluster component services - start, stop, restart, reload, and status check.

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.

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, access control, encrypted communication, audit logs—meeting SOC2 compliance requirements.

Seven-Layer Security Model

How Pigsty provides defense-in-depth across seven security layers, from physical security to user security.

Access Control

Pigsty provides standard security practices with an out-of-the-box role and permission model.

Getting Started with PostgreSQL

Get started with PostgreSQL—connect using CLI and graphical clients

Extensions

This article lists PostgreSQL extensions supported by Pigsty and their compatibility across different systems.

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.

Cluster / Instance

Choose the appropriate instance and cluster types based on your requirements to configure PostgreSQL database clusters that meet your needs.

Kernel Version

How to choose the appropriate PostgreSQL kernel and major version.

Package Alias

Pigsty provides a package alias translation mechanism that shields the differences in binary package details across operating systems, making installation easier.

User/Role

How to define and customize PostgreSQL users and roles through configuration?

Database

How to define and customize PostgreSQL databases through configuration?

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

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 440 extensions

Bind a L2 VIP to PostgreSQL Primary with VIP-Manager

Deploy HA Citus Cluster

How to deploy a Citus high-availability distributed cluster?

Percona

Percona Postgres distribution with TDE transparent encryption support

OpenHalo

MySQL compatible Postgres 14 fork

OrioleDB

Next-generation OLTP engine for PostgreSQL

Param Templates

Use Pigsty’s built-in Patroni config templates or customize your own

PG Kernels

How to use other PostgreSQL kernel forks in Pigsty? Such as Citus, Babelfish, IvorySQL, PolarDB, etc.

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.

Babelfish

Create Microsoft SQL Server compatible PostgreSQL clusters using WiltonDB and Babelfish! (Wire protocol level compatibility)

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!

Cloudberry

Deploy/Monitor Cloudberry clusters with Pigsty, an MPP data warehouse cluster forked from Greenplum!

Neon

Use Neon’s open-source Serverless PostgreSQL kernel to build flexible, scale-to-zero, forkable PG services.