Module: PGSQL

默认配置模板的参数优化策略说明

了解在 Pigsty 中，预置的四种 Patroni 场景化模板所采用的不同参数优化策略

OLTP Template

PostgreSQL config template optimized for online transaction processing workloads

User Management

Creating PostgreSQL users/roles, managing connection pool roles, refreshing expiration times, user password rotation

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

TINY Template

PostgreSQL config template optimized for micro instances and resource-constrained environments

High Availability

Pigsty uses Patroni to implement PostgreSQL high availability, ensuring automatic failover when the primary becomes unavailable.

Tradeoffs

How to balance RTO and RPO parameters to achieve an HA solution that fits your business needs?

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.

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

PGSQL Cluster Model

Entity-Relationship model for PostgreSQL clusters in Pigsty, including E-R diagram, entity definitions, and naming conventions.

Failure Model

What failures can high availability solve, and what can’t it solve?

HA Cluster

In-depth introduction to PostgreSQL high availability cluster architecture design, component interaction, failure scenarios, and recovery mechanisms in Pigsty.

Configuration

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

Cluster & Instances

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

User/Role refers to logical objects created by the SQL command CREATE USER/ROLE within a database cluster.

Database

Database refers to logical objects created by the SQL command CREATE DATABASE within a database cluster.

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

Dashboards

Pigsty provides many out-of-the-box Grafana monitoring dashboards for PostgreSQL

Metrics List

Complete list and explanation of monitoring metrics provided by the Pigsty PGSQL module

Database Management

Database management: create, modify, delete, rebuild databases, and clone databases using templates

HBA Management

PostgreSQL and Pgbouncer HBA rule management operations: refresh, reload, verify, and troubleshoot.

SOP

Common PostgreSQL administration procedures in Pigsty for maintaining production database clusters.

Cluster Management

Standard operation guide for creating/destroying PostgreSQL clusters and scaling existing clusters.

Parameter Tuning

Tuning Postgres Parameters

Accidental Deletion

Handling accidental data deletion, table deletion, and database deletion

Clone Replicas

How to clone databases, database instances, and database clusters?

Maintenance

Common system maintenance tasks

Playbook

How to manage PostgreSQL clusters with Ansible playbooks

Version Upgrade

How to upgrade (or downgrade) PostgreSQL minor version kernel, and how to perform major version upgrades

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

Citus: 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

Config 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.