PGSQL Cluster
Main dashboard for a PGSQL cluster: Demo PGSQL Cluster is the main dashboard for a single PostgreSQL cluster, providing cluster-level core metrics overview.
PIGSTY - The FLOSS PG RDS on PIGSTY
Recent content in PIGSTY - The FLOSS PG RDS on PIGSTY
Latest posts
PGSQL Database
Main dashboard for a single PGSQL database: Demo PGSQL Database is the main dashboard for a single PostgreSQL database, providing comprehensive database-level metrics.
PGSQL Instance
Main dashboard for a single PGSQL instance: Demo PGSQL Instance is the main dashboard for a single PostgreSQL instance, providing comprehensive instance-level metrics.
PGSQL Overview
Main dashboard for the PGSQL module: Demo PGSQL Overview is the main dashboard for the PostgreSQL module, providing a global overview of the entire PGSQL module.
PIGSTY
PGCAT Database
Database info from database catalog: Demo PGCAT Database shows database-level information retrieved directly from PostgreSQL system catalog.
PGRDS Cluster
RDS version of PGSQL Cluster: Demo PGRDS Cluster is the RDS version of PGSQL Cluster, focusing on PostgreSQL-native metrics without host-level metrics.
PGRDS Instance
RDS version of PGSQL Instance: Demo PGRDS Instance is the RDS version of PGSQL Instance, focusing on PostgreSQL-native metrics without host-level metrics.
PGSQL Alert
Global key metrics and alert events for PGSQL: Demo PGSQL Alert provides a global overview of core metrics and alert events for PostgreSQL clusters.
PGCAT Instance
Instance info from database catalog: Demo PGCAT Instance shows instance-level information retrieved directly from PostgreSQL system catalog.
PGSQL Activity
Session/load/QPS/TPS/locks for PGSQL cluster: Demo PGSQL Activity focuses on session activity, load, QPS, TPS, and lock status for a PostgreSQL cluster.
PGSQL Shard
Overview of horizontally sharded PGSQL clusters: Demo PGSQL Shard provides cross-shard metric comparison for horizontally sharded PGSQL clusters such as CITUS or GPSQL.
PGSQL Tables
Table/index access metrics: Demo PGSQL Tables shows table and index access metrics for all objects within a single PostgreSQL database.
PGSQL Persist
Persistence metrics for PGSQL instance: Demo PGSQL Persist focuses on persistence-related metrics: WAL generation, XID consumption, checkpoints, archiving, and I/O patterns.
PGSQL Replication
Replication, slots, and pub/sub for PGSQL cluster: Demo PGSQL Replication focuses on replication status, replication slots, and logical replication (pub/sub) for a PostgreSQL cluster.
PGSQL Table
Detailed info for a single table: Demo PGSQL Table shows detailed metrics for a single table including QPS, response time, index usage, and sequence info.
PGCAT Table
Detailed table info from catalog: Demo PGCAT Table shows detailed table information from database catalog including statistics and bloat analysis.
PGSQL Proxy
Detailed metrics for HAProxy: Demo PGSQL Proxy shows detailed metrics for a single HAProxy load balancer instance serving PostgreSQL traffic.
PGSQL Service
Service, proxy, routing, and load balancing for PGSQL cluster: Demo PGSQL Service focuses on service endpoints, proxy routing, and load balancing status for a PostgreSQL cluster.
PGSQL Databases
Database CRUD, slow queries, and table statistics: Demo PGSQL Databases focuses on database-level CRUD operations, slow queries, and table statistics across all instances in a cluster.
PGSQL Pgbouncer
Metrics overview for Pgbouncer: Demo PGSQL Pgbouncer shows connection pooling metrics for a single Pgbouncer instance.
PGSQL Query
Detailed info for a query type: Demo PGSQL Query shows detailed metrics for a specific query type including QPS and response time distribution.
PGCAT Query
Query details from database catalog: Demo PGCAT Query shows query details from database catalog including SQL text and execution statistics.
PGSQL Patroni
HA status and Patroni component status: Demo PGSQL Patroni focuses on high-availability status and Patroni component health for a PostgreSQL cluster.
PGSQL Session
Session and active/idle time metrics: Demo PGSQL Session focuses on session statistics and active/idle time distribution for a single PostgreSQL instance.
PGCAT Locks
Activity and lock wait info: Demo PGCAT Locks shows active sessions and lock wait information from database catalog.
PGSQL PITR
PITR context for point-in-time recovery: Demo PGSQL PITR provides context information for point-in-time recovery operations, showing backup status and WAL timeline.
PGSQL Xacts
Transaction, lock, TPS/QPS metrics: Demo PGSQL Xacts focuses on transaction processing, lock activity, and TPS/QPS metrics for a single PostgreSQL instance.
PGCAT Schema
Schema info from database catalog: Demo PGCAT Schema shows schema-level information from database catalog including tables, indexes, and sequences.
PGSQL Exporter
Self-monitoring metrics for exporters: Demo PGSQL Exporter shows self-monitoring metrics for the Postgres exporter and Pgbouncer exporter components.
Annotate Command (annotate)
Source: pgBackRest Command Docs: annotate Annotations included with the backup command can be added, modified, or removed afterwards using the annotate command. Command Options Backup Annotation Option ( --annotation ) Annotate backup with user-defined key/value pairs. Users can attach informative key/value pairs to the backup. This option may be used multiple times to attach multiple annotations.…
Cluster / Instance
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 instance/cluster types in Pigsty: Primary : Define a single instance cluster. Replica : Define a basic HA cluster with one primary and one replica. Offline : Define…
Managing PostgreSQL Clusters
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 cluster Remove Cluster bin/pgsql-rm <cls> Destroy entire PostgreSQL cluster Reload Service bin/pgsql-svc <cls> [ip...] Reload cluster load balancer…
Dynamic Configuration Settings
Source: https://patroni.readthedocs.io/en/latest/dynamic_configuration.html Dynamic configuration is stored in the DCS (Distributed Configuration Store) and applied on all cluster nodes. In order to change the dynamic configuration you can use either patronictl_edit_config tool or Patroni REST API . loop_wait : the number of seconds the loop will sleep. Default value: 10, minimum possible value: 1…
Model of Patroni Passive Failure
RTO Timeline Failure Model Phase Best Worst Average Description Lease Expiration ttl - loop ttl ttl - loop/2 Best: crash just before refresh Worst: crash right after refresh Replica Detect 0 loop loop / 2 Best: exactly at check point Worst: just missed check point Election Promote 0 2 1 Best: direct lock and promote Worst: API timeout + Promote HAProxy Check (rise-1) × fastinter (rise-1) ×…
Features
“ P ostgreSQL I n G reat STY le”: P ostgres, I nfras, G raphics, S ervice, T oolbox, it’s all Y ours. —— Battery-included, local-first PostgreSQL distribution, open-source RDS alternative Value Propositions Extensibility : Powerful extensions out of the box: deep integration of PostGIS , TimescaleDB , Citus , PGVector , 575 plugins, and 12 PG kernels . Reliability : Quickly…
Features
Source: https://www.pgbouncer.org/features.html Several levels of brutality when rotating connections: Session pooling Most polite method. When a client connects, a server connection will be assigned to it for the whole duration it stays connected. When the client disconnects, the server connection will be put back into pool. This mode supports all PostgreSQL features. Transaction pooling A server…
Getting Started
Here is a simple getting started tutorial to help you experience the core capabilities of the PIG package manager. Short Version curl -fsSL https://repo.pigsty.io/pig | bash # Install PIG from Cloudflare pig repo set # One-time setup for Linux, Pigsty + PGDG repos (overwrites!) pig install -v 18 -y pg18 pg_duckdb vector # Install PG 18 kernel, pg_duckdb, pgvector extensions... Installation You can…
E-R Model of Infra Cluster
The INFRA module plays a special role in Pigsty: it’s not a traditional “cluster” but rather a management hub composed of a group of infrastructure nodes , providing core services for the entire Pigsty deployment. Each INFRA node is an autonomous infrastructure service unit running core components like Nginx, Grafana, and VictoriaMetrics, collectively providing observability and…
Introduction
Source: https://patroni.readthedocs.io/en/latest/README.html Patroni is a template for high availability (HA) PostgreSQL solutions using Python. Patroni originated as a fork of Governor , the project from Compose. It includes plenty of new features. For additional background info, see: PostgreSQL HA with Kubernetes and Patroni , talk by Josh Berkus at KubeCon 2016 (video) Feb. 2016 Zalando Tech…
News
Source: https://pgbackrest.org/news.html New Distribution Tarball July 20, 2026 Starting with pgBackRest 2.59.0, every release includes a distribution tarball that makes building from source simpler. Unlike a checkout of the git repository, the tarball ships the generated source and the rendered documentation pre-built, so pgBackRest builds and installs without the code generation or documentation…
Parameter Optimization Policy
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 OLTP workloads and latency-sensitive applications (4C8GB+) (default template) olap.yml : Optimized for OLAP workloads and throughput (4C8G+) crit.yml :…
Preloading
The following 126 extensions require loading in shared_preload_libraries to function properly. You need to modify the shared_preload_libraries parameter in postgresql.conf , add the extension library, and restart the database. Extension Library Description timescaledb timescaledb Enables scalable inserts and complex queries for time-series data pg_cron pg_cron Job scheduler for PostgreSQL pg_task…
RPO Trade-offs
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. However, stricter RPO targets come at a cost: higher write latency, reduced system throughput, and the risk that replica failures may cause primary…
YAML Configuration Settings
Source: https://patroni.readthedocs.io/en/latest/yaml_configuration.html Global/Universal thread_pool_size : size of thread pool used by Patroni to execute asynchronous tasks and communicate via REST API with other members during leader race or failsafe checks. Minimal value is 5 , default value is 5 . thread_stack_size : specifies the stack size to be used for threads started by Patroni. Value…
Archive Get Command (archive-get)
Source: pgBackRest Command Docs: archive-get This command is used by PostgreSQL to restore a backup, perform PITR, or as an alternative to streaming for keeping a replica up to date. WAL segments are required for PostgreSQL recovery or to maintain a replica. When multiple repositories are configured, WAL will be fetched from the repositories in priority order (e.g. repo1 , repo2 , etc.). In…
Configuration: pgbouncer.ini
Source: https://www.pgbouncer.org/config.html Description The configuration file is in “ini” format. Section names are between [ and ] . Lines starting with ; or # are taken as comments and ignored. The characters ; and # are not recognized as special when they appear later in the line. Generic settings logfile Specifies the log file. For daemonization ( -d ), either this or syslog…
Model of Patroni Active Failure
RTO Timeline Failure Model Item Best Worst Average Description Crash Found 0 loop loop/2 Best: PG crashes right before check Worst: PG crashes right after check Restart Timeout 0 start start Best: PG recovers instantly Worst: Wait full start timeout before releasing lease Replica Detect 0 loop loop/2 Best: Right at check point Worst: Just missed check point Elect Promote 0 2 1 Best: Acquire lock…
Headless
The following 30 extensions can be used without running CREATE EXTENSION . These extensions typically exist as shared libraries (hooks) or standalone tools that take effect through configuration parameters. Extension Package Version Attr Description pg_task pg_task 2.1.29 --sL--- execute any sql command at any specific time at background plan_filter pg_plan_filter 0.0.1 --sL--- filter statements…
History
Historical Origins The Pigsty project began in 2018-2019, originating from Tantan . Tantan is an internet dating app — China’s Tinder, now acquired by Momo. Tantan was a Nordic-style startup with a Swedish engineering founding team. Tantan had excellent technical taste, using PostgreSQL and Go as its core technology stack. The entire Tantan system architecture was modeled after Instagram,…