Skip to content

This is the multi-page printable view of this section. .

Return to the regular view of this page.

Blog

Pigsty articles, engineering design notes, and the complete release archive.
Article

Essays, tutorials, field notes, and project stories collected from VONNG.

Design

Architecture decisions and implementation notes for Pigsty engineering.

Release

Pigsty release records organized by complete x.y.z version numbers.

1 - Release

Pigsty release archive using complete x.y.z version numbers.

Every stable numbered Pigsty tag through v4.5.0 has its own x.y.z page, including patch releases. Each record reconciles the long-form Pigsty release article, the historical About / Release Note archive, and the corresponding GitHub release or tag, then links the exact source comparison. Planned versions remain drafts until an actual tag is published.

1.1 - Pigsty v2.7.0

Extension explosion, 20+ new powerful extensions, Docker apps

v2.7.0 ·

Highlights

New powerful extensions, especially Rust/pgrx-developed ones:

C/C++ extensions:

New Features

  • Allow Pigsty to run in Docker VM images
  • ARM64 packages for INFRA & PGSQL modules on Ubuntu and EL
  • New installer script with Cloudflare download, version specification, better prompts
  • PGSQL PITR dashboard for PITR observability
  • Guardrails to prevent running playbooks on unmanaged nodes
  • Per-distro config files: el7, el8, el9, debian11, debian12, ubuntu20, ubuntu22

Docker App Templates

Software Upgrades

  • PostgreSQL 16.3
  • Patroni 3.3.0
  • pgBackRest 2.51
  • VIP-Manager v2.5.0
  • HAProxy 2.9.7
  • Grafana 10.4.2
  • Prometheus 2.51
  • Loki & Promtail: 3.0.0 (Warning: breaking changes!)
  • Alertmanager 0.27.0
  • BlackBox Exporter 0.25.0
  • Node Exporter 1.8.0
  • pgBackRest Exporter 0.17.0
  • DuckDB 0.10.2
  • etcd 3.5.13
  • minio-20240510014138 / mcli-20240509170424
  • pev2 v1.8.0 -> v1.11.0
  • pgvector 0.6.1 -> 0.7.0
  • pg_tle: v1.3.4 -> v1.4.0
  • hydra: v1.1.1 -> v1.1.2
  • duckdb_fdw: v1.1.0 recompiled for libduckdb 0.10.2
  • pg_bm25 0.5.6 -> pg_search 0.7.0
  • pg_analytics: 0.5.6 -> 0.6.1
  • pg_graphql: 1.5.0 -> 1.5.4
  • pg_net 0.8.0 -> 0.9.1
  • pg_sparse (deprecated)

Bug Fixes

  • Fixed variable whitespace in pg_exporters role
  • Fixed minio_cluster not commented in global config
  • Fixed EL7 template postgis34 should be postgis33
  • Fixed EL8 python3.11-cryptography dependency renamed to python3-cryptography
  • Fixed /pg/bin/pg-role not getting OS username in non-interactive shell
  • Fixed /pg/bin/pg-pitr not prompting -X -P options correctly

API Changes

  • New node_write_etc_hosts parameter for controlling /etc/hosts writes
  • New prometheus_sd_dir parameter for Prometheus static discovery directory
  • Configure script adds -x|--proxy for writing proxy info
  • Stopped parsing Nginx log detail labels in Promtail/Loki to avoid label cardinality explosion
  • Using Alertmanager API v2 instead of v1
  • Using /pg/cert/ca.crt instead of /etc/pki/ca.crt in PGSQL module

Offline Package Checksums 

MD5 (pigsty-pkg-v2.7.0.el8.x86_64.tgz) = ec271a1d34b2b1360f78bfa635986c3a
MD5 (pigsty-pkg-v2.7.0.debian12.x86_64.tgz) = f3304bfd896b7e3234d81d8ff4b83577
MD5 (pigsty-pkg-v2.7.0.ubuntu22.x86_64.tgz) = 5b071c2a651e8d1e68fc02e7e922f2b3

Sources

1.2 - Pigsty v2.6.0

PG16 as default, ParadeDB & DuckDB extensions introduced

v2.6.0 ·

Highlights

Configuration Changes

  • Replaced node_repo_method with node_repo_modules, removed node_repo_local_urls
  • Temporarily disabled Grafana unified alerting to avoid “Database Locked” errors
  • New node_repo_modules parameter to specify upstream repos added to nodes
  • Removed node_local_repo_urls, functionality replaced by node_repo_modules & repo_upstream
  • Removed node_repo_method parameter, functionality replaced by node_repo_modules
  • Added new local source in repo_upstream, used via node_repo_modules to replace node_local_repo_urls
  • Reorganized node_default_packages, infra_packages, pg_packages, pg_extensions defaults
  • When replacing repo_upstream.baseurl, if EL8/9 PGDG minor-version-specific repos are available, use major.minor instead of major for $releasever for better minor version compatibility

Software Upgrades

  • Grafana 10.3
  • Prometheus 2.47
  • node_exporter 1.7.0
  • HAProxy 2.9.5
  • Loki / Promtail 2.9.4
  • minio-20240216110548 / mcli-20240217011557
  • etcd 3.5.11
  • Redis 7.2.4
  • Bytebase 2.13.2
  • DuckDB 0.10.0
  • FerretDB 1.19
  • Metabase: new Docker app template

PostgreSQL Extensions

  • PostgreSQL minor version upgrades: 16.2, 15.6, 14.11, 13.14, 12.18
  • PostgreSQL 16: now promoted to default major version
  • pg_exporter 0.6.1: security fix
  • Patroni 3.2.2
  • pgBadger 12.4
  • pgBackRest 2.50
  • vip-manager 2.3.0
  • PostGIS 3.4.2
  • TimescaleDB 2.14.1
  • Vector extension PGVector 0.6.0: added parallel HNSW index creation
  • New extension duckdb_fdw v1.1 for reading/writing DuckDB data
  • New extension pgsql-gzip for Gzip compression/decompression v1.0.0
  • New extension pg_sparse for efficient sparse vectors (ParadeDB) v0.5.6
  • New extension pg_bm25 for high-quality BM25 full-text search (ParadeDB) v0.5.6
  • New extension pg_analytics with SIMD + columnar storage for analytics (ParadeDB) v0.5.6
  • Upgraded AIML extension pgml to v2.8.1 with PG 16 support
  • Upgraded columnar extension hydra to v1.1.1 with PG 16 support
  • Upgraded graph extension age to v1.5.0 with PG 16 support
  • Upgraded GraphQL extension pg_graphql to v1.5.0 for Supabase support
MD5 (pigsty-v2.6.0.tgz) = 330e9bc16a2f65d57264965bf98174ff
MD5 (pigsty-pkg-v2.6.0.debian11.x86_64.tgz) = 81abcd0ced798e1198740ab13317c29a
MD5 (pigsty-pkg-v2.6.0.debian12.x86_64.tgz) = 7304f4458c9abd3a14245eaf72f4eeb4
MD5 (pigsty-pkg-v2.6.0.el7.x86_64.tgz) = f914fbb12f90dffc4e29f183753736bb
MD5 (pigsty-pkg-v2.6.0.el8.x86_64.tgz) = fc23d122d0743d1c1cb871ca686449c0
MD5 (pigsty-pkg-v2.6.0.el9.x86_64.tgz) = 9d258dbcecefd232f3a18bcce512b75e
MD5 (pigsty-pkg-v2.6.0.ubuntu20.x86_64.tgz) = 901ee668621682f99799de8932fb716c
MD5 (pigsty-pkg-v2.6.0.ubuntu22.x86_64.tgz) = 39872cf774c1fe22697c428be2fc2c22

Sources

1.3 - Pigsty v2.5.1

Routine minor update, PG16 key extension support

v2.5.1 ·

Following PostgreSQL v16.1, v15.5, 14.10, 13.13, 12.17, 11.22 routine minor version updates.

All important PostgreSQL 16 extensions are now in place (added pg_repack and timescaledb support).

  • Software updates:
    • PostgreSQL to v16.1, v15.5, 14.10, 13.13, 12.17, 11.22
    • Patroni v3.2.0
    • PgBackrest v2.49
    • Citus 12.1
    • TimescaleDB 2.13
    • Grafana v10.2.0
    • FerretDB 1.15
    • SealOS 4.3.7
    • Bytebase 2.11.1
  • Removed monitor schema prefix from PGCAT dashboard queries (allowing users to install pg_stat_statements elsewhere)
  • New wool.yml config template designed for Alibaba Cloud free 99 ECS single-node
  • Added python3-jmespath package for EL9 to fix jmespath missing after Ansible dependency update during bootstrap
MD5 (pigsty-pkg-v2.5.1.el7.x86_64.tgz) = 31ee48df1007151009c060e0edbd74de
MD5 (pigsty-pkg-v2.5.1.el8.x86_64.tgz) = a40f1b864ae8a19d9431bcd8e74fa116
MD5 (pigsty-pkg-v2.5.1.el9.x86_64.tgz) = c976cd4431fc70367124fda4e2eac0a7
MD5 (pigsty-pkg-v2.5.1.debian11.x86_64.tgz) = 7fc1b5bdd3afa267a5fc1d7cb1f3c9a7
MD5 (pigsty-pkg-v2.5.1.debian12.x86_64.tgz) = add0731dc7ed37f134d3cb5b6646624e
MD5 (pigsty-pkg-v2.5.1.ubuntu20.x86_64.tgz) = 99048d09fa75ccb8db8e22e2a3b41f28
MD5 (pigsty-pkg-v2.5.1.ubuntu22.x86_64.tgz) = 431668425f8ce19388d38e5bfa3a948c

Sources

1.4 - Pigsty v2.5.0

Ubuntu/Debian support: bullseye, bookworm, jammy, focal

v2.5.0 · 

curl https://get.pigsty.cc/latest | bash

Highlights

  • Ubuntu / Debian support: bullseye, bookworm, jammy, focal
  • CDN repo.pigsty.cc software repository providing RPM/DEB package downloads
  • Anolis OS support (compatible with EL 8.8)
  • PostgreSQL 16 replaces PostgreSQL 14 as the alternative primary supported version
  • New PGSQL Exporter / PGSQL Patroni dashboards, redesigned PGSQL Query dashboard
  • Extension updates:
    • PostGIS upgraded to 3.4 (EL8/EL9), EL7 remains on PostGIS 3.3
    • Removed pg_embedding as developer discontinued maintenance, recommend pgvector instead
    • New extension (EL): Point cloud plugin pointcloud support, natively available on Ubuntu
    • New extensions (EL): imgsmlr, pg_similarity, pg_bigm for search
    • Recompiled pg_filedump as PG version-independent package
    • Added hydra columnar storage extension, citus no longer installed by default
  • Software updates:
    • Grafana to v10.1.5
    • Prometheus to v2.47
    • Promtail/Loki to v2.9.1
    • Node Exporter to v1.6.1
    • Bytebase to v2.10.0
    • Patroni to v3.1.2
    • pgbouncer to v1.21.0
    • pg_exporter to v0.6.0
    • pgbackrest to v2.48.0
    • pgbadger to v12.2
    • pg_graphql to v1.4.0
    • pg_net to v0.7.3
    • FerretDB to v0.12.1
    • SealOS to 4.3.5
    • Supabase support to 20231013070755

Ubuntu Support Notes

Pigsty supports Ubuntu 22.04 (jammy) and 20.04 (focal) LTS versions with corresponding offline packages.

Compared to EL systems, some parameter defaults need explicit adjustment. See ubuntu.yml for details:

  • repo_upstream: Adjusted for Ubuntu/Debian package names
  • repo_packages: Adjusted for Ubuntu/Debian package names
  • node_repo_local_urls: Defaults to ['deb [trusted=yes] http://${admin_ip}/pigsty ./']
  • node_default_packages:
    • zlib -> zlib1g, readline -> libreadline-dev
    • vim-minimal -> vim-tiny, bind-utils -> dnsutils, perf -> linux-tools-generic
    • Added acl package to ensure Ansible permissions work correctly
  • infra_packages: All packages with _ replaced by -, postgresql-client-16 replaces postgresql16
  • pg_packages: Ubuntu conventionally uses - instead of _, no need to manually install patroni-etcd
  • pg_extensions: Extension names differ from EL, Ubuntu lacks passwordcheck_cracklib
  • pg_dbsu_uid: Ubuntu DEB packages don’t specify explicit UID, manual specification required, Pigsty defaults to 543

API Changes

Default value changes:

  • repo_modules now defaults to infra,node,pgsql,redis,minio, enabling all upstream sources

  • repo_upstream changed, now adds Pigsty Infra/MinIO/Redis/PGSQL modular software sources

  • repo_packages changed, removed unused karma,mtail,dellhw_exporter, removed PG14 main extensions, added PG16 main extensions, added virtualenv package

  • node_default_packages changed, now installs python3-pip by default

  • pg_libs: timescaledb removed from shared_preload_libraries, no longer auto-enabled by default

  • pg_extensions changed, Citus no longer installed by default, passwordcheck_cracklib installed by default, EL8,9 PostGIS default version upgraded to 3.4

    - pg_repack_${pg_version}* wal2json_${pg_version}* passwordcheck_cracklib_${pg_version}*
- postgis34_${pg_version}* timescaledb-2-postgresql-${pg_version}* pgvector_${pg_version}*

  • All Patroni templates remove wal_keep_size parameter by default to avoid triggering Patroni 3.1.1 bug, functionality covered by min_wal_size

MD5 (pigsty-pkg-v2.5.0.el7.x86_64.tgz) = 87e0be2edc35b18709d7722976e305b0
MD5 (pigsty-pkg-v2.5.0.el8.x86_64.tgz) = e71304d6f53ea6c0f8e2231f238e8204
MD5 (pigsty-pkg-v2.5.0.el9.x86_64.tgz) = 39728496c134e4352436d69b02226ee8
MD5 (pigsty-pkg-v2.5.0.debian11.x86_64.tgz) = e3f548a6c7961af6107ffeee3eabc9a7
MD5 (pigsty-pkg-v2.5.0.debian12.x86_64.tgz) = 1e469cc86a19702e48d7c1a37e2f14f9
MD5 (pigsty-pkg-v2.5.0.ubuntu20.x86_64.tgz) = cc3af3b7c12f98969d3c6962f7c4bd8f
MD5 (pigsty-pkg-v2.5.0.ubuntu22.x86_64.tgz) = c5b2b1a4867eee624e57aed58ac65a80

Sources

1.5 - Pigsty v2.4.1

Supabase/PostgresML support with graphql, jwt, pg_net, vault

v2.4.1 ·

Supabase & PostgresML support, and other new extensions!

Pigsty-Distro-En

Highlights

  • Supabase support: run open-source Firebase alternative with external postgres managed by Pigsty: example config
  • PostgresML support: Run LLMs, vector operations, classical Machine Learning in Postgres.
  • GraphQL support: pg_graphql reflects a GraphQL schema from the existing SQL schema.
  • Async HTTP Client support pg_net enables asynchronous (non-blocking) HTTP/HTTPS requests with SQL
  • JWT support: pgjwt is the PostgreSQL implementation of JWT (JSON Web Tokens)
  • Vault support: vault can store encrypted secrets in the Vault
  • New component pg_filedump for pg 14 & 15, low-level data recovery tool for PostgreSQL
  • New extension hydra the columnar available for PG 13 - 15. (not packaged due to conflict with citus columnar)
  • Reduce offline packages size for el9 400MB by removing proj-data*
  • Bump FerretDB version to v1.10
  • Fix patroni v3.1.1 broken issue
7e3989a98b7b0cd213e7efa09a8e8ebc  pigsty-v2.4.1.tgz
efabe7632d8994f3fb58f9838b8f9d7d  pigsty-pkg-v2.4.1.el7.x86_64.tgz
ea78957e8c8434b120d1c8c43d769b56  pigsty-pkg-v2.4.1.el8.x86_64.tgz
4ef280a7d28872814e34521978b851bb  pigsty-pkg-v2.4.1.el9.x86_64.tgz

Sources

1.6 - Pigsty v2.4.0

PG16, RDS monitoring, new extensions: FTS/graph/HTTP/embedding

v2.4.0 ·

Highlights

  • PostgreSQL 16 GA released, Pigsty provides support
  • Monitor cloud databases: RDS for PostgreSQL and PolarDB with brand-new PGRDS dashboards
  • Commercial support and consulting services officially launched. First LTS version released, providing up to 5 years of support for subscribers
  • New extension: Apache AGE, openCypher graph query engine on PostgreSQL
  • New extension: zhparser, full text search for Chinese language
  • New extension: pg_roaringbitmap, roaring bitmap for PostgreSQL
  • New extension: pg_embedding, HNSW alternative to pgvector
  • New extension: pg_tle, admin/manage stored procedure extensions
  • New extension: pgsql-http, issue HTTP requests with SQL interface
  • Additional extensions: pg_auth_mon, pg_checksums, pg_failover_slots, pg_readonly, postgresql-unit, pg_store_plans, pg_uuidv7, set_user
  • Redis improvements: Sentinel monitoring support, automatic HA configuration for primary-replica clusters

API Changes

  • New parameter: REDIS.redis_sentinel_monitor — specify list of primaries monitored by Sentinel cluster

Bug Fixes

  • Fixed missing uid when registering datasources in Grafana 10.1
MD5 (pigsty-pkg-v2.4.0.el7.x86_64.tgz) = 257443e3c171439914cbfad8e9f72b17
MD5 (pigsty-pkg-v2.4.0.el8.x86_64.tgz) = 41ad8007ffbfe7d5e8ba5c4b51ff2adc
MD5 (pigsty-pkg-v2.4.0.el9.x86_64.tgz) = 9a950aed77a6df90b0265a6fa6029250

Sources

1.7 - Pigsty v2.3.1

PGVector with HNSW, PG16 RC1, doc refresh, Chinese docs, bug fixes

v2.3.1 ·

Highlights

  • pgvector updated to 0.5 with HNSW algorithm support
  • PostgreSQL 16 RC1 support (el8/el9)
  • Added SealOS to default packages for quick Kubernetes cluster deployment

Bug Fixes

  • Fixed infra.repo.repo_pkg task: downloads could be affected by existing /www/pigsty content when repo_packages contains * wildcards
  • Changed vip_dns_suffix default from .vip to empty string, so cluster name itself becomes the default Node cluster L2 VIP
  • modprobe watchdog and chown watchdog if patroni_watchdog_mode is required
  • When pg_dbsu_sudo = limit and patroni_watchdog_mode = required, grant database dbsu sudo for:
    • /usr/bin/sudo /sbin/modprobe softdog: Ensure softdog kernel module enabled when starting Patroni service
    • /usr/bin/sudo /bin/chown {{ pg_dbsu }} /dev/watchdog: Ensure watchdog ownership correct when starting Patroni service

Documentation Updates

  • Added updated content to English documentation
  • Added simplified Chinese built-in docs, fixed Chinese docs on pigsty.cc

Software Updates

  • PostgreSQL 16 RC1 for EL8/EL9
  • PGVector 0.5.0 with HNSW index support
  • TimescaleDB 2.11.2
  • Grafana 10.1.0
  • Loki & Promtail 2.8.4
  • Redis Stack 7.2 on el7/8
  • mcli-20230829225506 / minio-20230829230735
  • FerretDB 1.9
  • SealOS 4.3.3
  • pgBadger 1.12.2
MD5 (pigsty-pkg-v2.3.1.el7.x86_64.tgz) = ce69791eb622fa87c543096cdf11f970
MD5 (pigsty-pkg-v2.3.1.el8.x86_64.tgz) = 495aba9d6d18ce1ebed6271e6c96b63a
MD5 (pigsty-pkg-v2.3.1.el9.x86_64.tgz) = 38b45582cbc337ff363144980d0d7b64

Sources

1.8 - Pigsty v2.3.0

Node VIP, FerretDB, NocoDB, MySQL stub, CVE fixes

v2.3.0 ·

Highlights

  • INFRA: Added NODE/PGSQL VIP monitoring support
  • PGSQL: Fixed PostgreSQL CVE-2023-39417 via minor upgrades: 15.4, 14.9, 13.12, 12.16, and Patroni v3.1.0
  • NODE: Allow users to bind L2 VIP to node clusters using keepalived
  • REPO: Pigsty Yum repo optimized, site-wide HTTPS by default: get.pigsty.cc and demo.pigsty.cc
  • APP: Upgraded app/bytebase to v2.6.0, app/ferretdb to v1.8; added new app template: NocoDB, open-source Airtable
  • REDIS: Upgraded to v7.2, redesigned Redis dashboards
  • MONGO: Added basic support via FerretDB 1.8
  • MYSQL: Added Prometheus/Grafana/CA stubs for future integration

API Changes

Added new parameter group NODE.NODE_VIP with 8 new parameters:

  • NODE.VIP.vip_enabled: Enable VIP on this node cluster?
  • NODE.VIP.vip_address: Node VIP address in IPv4 format, required if VIP enabled
  • NODE.VIP.vip_vrid: Required, integer 1-255, must be unique within same VLAN
  • NODE.VIP.vip_role: master/backup, defaults to backup, used as initial role
  • NODE.VIP.vip_preempt: Optional, true/false, defaults to false, enable VIP preemption
  • NODE.VIP.vip_interface: Node VIP network interface to listen on, eth0 by default
  • NODE.VIP.vip_dns_suffix: Node VIP DNS name suffix, defaults to .vip
  • NODE.VIP.vip_exporter_port: Keepalived exporter listen port, defaults to 9650
MD5 (pigsty-pkg-v2.3.0.el7.x86_64.tgz) = 81db95f1c591008725175d280ad23615
MD5 (pigsty-pkg-v2.3.0.el8.x86_64.tgz) = 6f4d169b36f6ec4aa33bfd5901c9abbe
MD5 (pigsty-pkg-v2.3.0.el9.x86_64.tgz) = 4bc9ae920e7de6dd8988ca7ee681459d

Sources

1.9 - Pigsty v2.2.0

Dashboard & provisioning overhaul, UOS compatibility

v2.2.0 ·

Highlights

  • Monitoring Dashboard Overhaul: https://demo.pigsty.cc
  • Vagrant Sandbox Redesign: libvirt support with new config templates
  • Pigsty EL Yum Repos: Consolidated scattered RPMs, simplified installation/build process
  • OS Compatibility: Added UOS-v20-1050e support
  • New Config Template: 42-node production simulation configuration
  • Unified official PGDG Citus packages (el7)

Software Upgrades

  • PostgreSQL 16 beta2
  • Citus 12 / PostGIS 3.3.3 / TimescaleDB 2.11.1 / PGVector 0.44
  • Patroni 3.0.4 / pgBackRest 2.47 / pgBouncer 1.20
  • Grafana 10.0.3 / Loki/Promtail/logcli 2.8.3
  • etcd 3.5.9 / HAProxy v2.8.1 / Redis v7.0.12
  • MinIO 20230711212934 / mcli 20230711233044

Bug Fixes

  • Fixed Docker group permission issue 29434bd
  • Made infra OS user group supplementary rather than primary
  • Fixed Redis Sentinel systemd service auto-enable state 5c96feb
  • Relaxed bootstrap & configure checks, especially when /etc/redhat-release doesn’t exist
  • Upgraded to Grafana 10, fixing Grafana 9.x CVE-2023-1410
  • Added PG 14-16 command tags and error codes to CMDB pglog schema

API Changes

New variable:

  • INFRA.NGINX.nginx_exporter_enabled: Users can now disable nginx_exporter by setting this parameter

Default value changes:

  • repo_modules: node,pgsql,infra : Redis now provided by pigsty-el repo, no longer needs redis module
  • repo_upstream:
    • Added pigsty-el: EL version-independent RPMs: grafana, minio, pg_exporter, etc.
    • Added pigsty-misc: EL version-specific RPMs: redis, prometheus stack, etc.
    • Removed citus: PGDG now has complete EL7-EL9 Citus 12 support
    • Removed remi: Redis now provided by pigsty-el repo
  • repo_packages: Consolidated package lists (see source for details)
  • repo_url_packages:
  • node_default_packages: Updated package list
  • infra_packages: Updated package list
  • PGSERVICE in .pigsty replaced with PGDATABASE=postgres, allowing users to access specific instances from admin node using just IP address

Directory structure changes:

  • bin/dns and bin/ssh moved to vagrant/ directory
MD5 (pigsty-pkg-v2.2.0.el7.x86_64.tgz) = 5fb6a449a234e36c0d895a35c76add3c
MD5 (pigsty-pkg-v2.2.0.el8.x86_64.tgz) = c7211730998d3b32671234e91f529fd0
MD5 (pigsty-pkg-v2.2.0.el9.x86_64.tgz) = 385432fe86ee0f8cbccbbc9454472fdd

Sources

1.10 - Pigsty v2.1.0

PostgreSQL 12-16beta support

v2.1.0 ·

Highlights

  • PostgreSQL 16 beta support, plus support for versions 12-15
  • Added PGVector extension support for PG 12-15 for storing AI embeddings
  • Added 6 additional default extension panel/datasource plugins for Grafana
  • Added bin/profile script for remote profiling and flame graph generation
  • Added bin/validate for validating pigsty.yml configuration file correctness
  • Added bin/repo-add for quickly adding Yum repo definitions to nodes
  • PostgreSQL 16 observability: added pg_stat_io support and related monitoring dashboards

Software Upgrades

  • PostgreSQL 15.3, 14.8, 13.11, 12.15, 11.20, and 16 beta1
  • pgBackRest 2.46 / pgbouncer 1.19
  • Redis 7.0.11
  • Grafana v9.5.3
  • Loki / Promtail / Logcli 2.8.2
  • Prometheus 2.44
  • TimescaleDB 2.11.0
  • minio-20230518000536 / mcli-20230518165900
  • Bytebase v2.2.0

Improvements

  • When adding local user public keys, all id*.pub files are now added to remote machines (e.g., keys generated with elliptic curve algorithms)

Sources

1.11 - Pigsty v2.0.2

Added pgvector support, fixed MinIO CVE

v2.0.2 ·

Highlights

Use out-of-the-box pgvector to store AI Embeddings, index, and retrieve vectors.

Changes

  • New extension pgvector for storing AI embeddings and vector similarity search
  • Fixed MinIO CVE-2023-28432, using new policy API from 20230324
  • Added dynamic reload command for DNSMASQ systemd service
  • Updated PEV version to v1.8
  • Updated Grafana version to v9.4.7
  • Updated MinIO and MCLI versions to 20230324
  • Updated Bytebase version to v1.15.0
  • Updated monitoring dashboards and fixed dead links
  • Updated Aliyun Terraform template, default to RockyLinux 9
  • Using Grafana v9.4 Provisioning API
  • Added asciinema videos for many admin tasks
  • Fixed EL8 PostgreSQL broken dependencies: removed anonymizer_15 faker_15 pgloader
MD5 (pigsty-pkg-v2.0.2.el7.x86_64.tgz) = d46440a115d741386d29d6de646acfe2
MD5 (pigsty-pkg-v2.0.2.el8.x86_64.tgz) = 5fa268b5545ac96b40c444210157e1e1
MD5 (pigsty-pkg-v2.0.2.el9.x86_64.tgz) = c8b113d57c769ee86a22579fc98e8345

Sources

1.12 - Pigsty v2.0.1

v2 bug fixes, security enhancements, Grafana upgrade

v2.0.1 ·

Security improvements and bug fixes for v2.0.0.

Improvements

  • New pig logo to comply with PostgreSQL trademark policy
  • Grafana upgraded to v9.4 with better UI and bug fixes
  • Patroni upgraded to v3.0.1 with bug fixes
  • Grafana systemd service file reverted to rpm default
  • Use slower copy instead of rsync for Grafana dashboard sync, more reliable
  • Bootstrap now restores default repo files after execution
  • Added asciinema videos for various admin tasks
  • Security enhancement mode: restricted monitoring user permissions
  • New config template: dual.yml for two-node deployment
  • Enable log_connections and log_disconnections in crit.yml template
  • Enable $lib/passwordcheck in pg_libs in crit.yml template
  • Explicitly grant pg_monitor role monitoring view permissions
  • Remove default dbrole_readonly from dbuser_monitor to restrict monitoring user permissions
  • Patroni now listens on {{ inventory_hostname }} instead of 0.0.0.0
  • pg_listen now controls postgres/pgbouncer listen address
  • ${ip}, ${lo}, ${vip} placeholders now available in pg_listen
  • Aliyun terraform image upgraded from centos 7.9 to Rocky Linux 9
  • Bytebase upgraded to v1.14.0

Bug Fixes

  • Added missing advertise address for alertmanager
  • Fixed missing pg_mode variable when creating database users with bin/pgsql-user
  • Added -a password option for Redis cluster join task in redis.yml
  • Added missing default value in infra-rm.yml.remove infra data task
  • Fixed prometheus monitoring target definition file owner to prometheus user
  • Use admin user instead of root to delete DCS metadata
  • Fixed issue caused by Grafana 9.4 bug: missing Meta datasource

Sources

1.13 - Pigsty v2.0.0

Major architecture upgrade, compatibility/security/maintainability

v2.0.0 ·

Highlights

  • Perfect integration of PostgreSQL 15, PostGIS 3.3, Citus 11.2, TimescaleDB 2.10 — distributed geospatial time-series hyper-converged database
  • Major OS compatibility improvements: supports EL7, 8, 9, plus RHEL, CentOS, Rocky, OracleLinux, AlmaLinux compatible distros
  • Security improvements: self-signed CA, global SSL network encryption, scram-sha-256 password auth, AES-encrypted backups, redesigned HBA rule system
  • Patroni upgraded to 3.0, providing native HA Citus distributed cluster support, FailSafe mode enabled by default — no fear of DCS failures causing global primary outages
  • Out-of-the-box PITR support based on pgBackRest, default support for local filesystem and dedicated MinIO/S3 cluster backups
  • New ETCD module: independently deployable, easy scaling, built-in monitoring and HA, completely replacing Consul as DCS for HA PG
  • New MINIO module: independently deployable, multi-disk multi-node support, S3 local replacement, also for centralized PostgreSQL backup repository
  • Significantly simplified configuration parameters, usable without defaults; templates auto-adjust host and PG parameters based on machine specs, HBA/service definitions more concise and universal
  • License changed from Apache License 2.0 to AGPL 3.0 due to Grafana and MinIO dependencies

Compatibility

  • Supports EL7, EL8, EL9 major versions with corresponding offline packages, default dev/test environment upgraded from EL7 to EL9
  • Supports more EL-compatible Linux distros: RHEL, CentOS, RockyLinux, AlmaLinux, OracleLinux, etc.
  • Source and offline package naming conventions changed — version, OS version, and architecture now reflected in package names
  • PGSQL: PostgreSQL 15.2, PostGIS 3.3, Citus 11.2, TimescaleDB 2.10 now work together harmoniously
  • PGSQL: Patroni upgraded to 3.0 as PGSQL HA component
    • ETCD now default DCS, replacing Consul, eliminating one Consul Agent failure point
    • vip-manager upgraded to 2.1 using ETCDv3 API, completely deprecating ETCDv2 API; same for Patroni
    • Native HA Citus distributed cluster support using fully open-source Citus 11.2
    • FailSafe mode enabled by default — no fear of DCS failures causing global primary outages
  • PGSQL: pgBackrest v2.44 introduced for out-of-the-box PostgreSQL PITR
    • Default backup repo on primary’s backup directory, rolling two-day recovery window
    • Default alternative repo is dedicated MinIO/S3 cluster, rolling two-week recovery window; local use requires enabling MinIO module
  • ETCD now an independently deployed module with complete scale-out/in solution and monitoring
  • MINIO now an independently deployed module, multi-disk multi-node support, S3 local replacement, also for centralized backup repository
  • NODE module now includes haproxy, docker, node_exporter, promtail components
    • chronyd now replaces ntpd as default NTP service on all nodes
    • HAPROXY now part of NODE rather than PGSQL-exclusive, can expose services via NodePort
    • PGSQL module can now use dedicated centralized HAPROXY cluster for unified external service
  • INFRA module now includes dnsmasq, nginx, prometheus, grafana, loki components
    • DNSMASQ server in Infra module enabled by default, added as default DNS server for all nodes
    • Added blackbox_exporter for host PING probing, pushgateway for batch job metrics
    • loki and promtail now use Grafana’s default packages with official Grafana Echarts panel plugin
    • Monitoring support for PostgreSQL 15’s new observability points, added Patroni monitoring
  • Software version upgrades
    • PostgreSQL 15.2 / PostGIS 3.3 / TimescaleDB 2.10 / Citus 11.2
    • Patroni 3.0 / Pgbouncer 1.18 / pgBackRest 2.44 / vip-manager 2.1
    • HAProxy 2.7 / Etcd 3.5 / MinIO 20230131022419 / mcli 20230128202938
    • Prometheus 2.42 / Grafana 9.3 / Loki & Promtail 2.7 / Node Exporter 1.5

Security

  • Complete local self-signed CA: pigsty-ca for issuing internal component certificates
  • User creation/password changes no longer leave traces in log files
  • Nginx enables SSL support by default (for HTTPS, trust pigsty-ca in your system or use Chrome thisisunsafe)
  • ETCD fully enables SSL encryption for client and peer communication
  • PostgreSQL SSL support added and enabled by default, management connections use SSL
  • Pgbouncer SSL support added, disabled by default for performance
  • Patroni SSL support added, management API restricted to local and admin node access with password auth
  • PostgreSQL default password auth changed from md5 to scram-sha-256
  • Pgbouncer auth query support added for dynamic connection pool user management
  • pgBackRest uses AES-256-CBC encryption by default for remote centralized backup storage
  • High-security template provided: enforces global SSL and requires admin certificate login
  • All default HBA rules now explicitly defined in config files

Maintainability

  • Existing config templates auto-adjust optimizations based on machine specs (CPU/memory/storage)
  • Postgres/Pgbouncer/Patroni/pgBackRest log directories now dynamically configurable: default /pg/log/<type>/
  • Original IP placeholder 10.10.10.10 replaced with dedicated variable ${admin_ip}, referenceable in multiple places for switching backup admin nodes
  • region can be specified to use upstream mirrors from different regions for faster package downloads
  • Finer-grained upstream source addresses now allowed based on EL version, architecture, and region
  • Terraform templates for Alibaba Cloud and AWS China provided for one-click EC2 VM provisioning
  • Multiple Vagrant sandbox templates provided: meta, full, el7/8/9, minio, build, citus
  • New dedicated playbook: pgsql-monitor.yml for monitoring existing Postgres instances or RDS
  • New dedicated playbook: pgsql-migration.yml for seamless logical replication migration to Pigsty-managed clusters
  • Series of dedicated shell utilities added, wrapping common ops operations
  • All Ansible roles optimized for simplicity, readability, and maintainability — usable without default parameters
  • Additional Pgbouncer parameters can be defined at business database/user level

API Changes

Pigsty v2.0 has extensive changes: 64 new parameters, 13 removed, 17 renamed.

New Parameters

  • INFRA.META.admin_ip: Primary meta node IP address
  • INFRA.META.region: Upstream mirror region: default|china|europe
  • INFRA.META.os_version: Enterprise Linux version: 7,8,9
  • INFRA.CA.ca_cn: CA Common Name, default pigsty-ca
  • INFRA.CA.cert_validity: Certificate validity, default 20 years
  • INFRA.REPO.repo_enabled: Build local yum repo on infra node?
  • INFRA.REPO.repo_upstream: Upstream yum repo definition list
  • INFRA.REPO.repo_home: Local yum repo home directory, usually same as nginx_home ‘/www’
  • INFRA.NGINX.nginx_ssl_port: HTTPS listen port
  • INFRA.NGINX.nginx_ssl_enabled: Enable nginx HTTPS?
  • INFRA.PROMETHEUS.alertmanager_endpoint: Alertmanager endpoint (ip|domain):port format
  • NODE.NODE_TUNE.node_hugepage_ratio: Memory hugepage ratio, default 0 (disabled)
  • NODE.HAPROXY.haproxy_service: List of haproxy services to expose
  • PGSQL.PG_ID.pg_mode: pgsql cluster mode: pgsql,citus,gpsql
  • PGSQL.PG_BUSINESS.pg_dbsu_password: dbsu password, empty string means no dbsu password
  • PGSQL.PG_INSTALL.pg_log_dir: postgres log directory, default /pg/data/log
  • PGSQL.PG_BOOTSTRAP.pg_storage_type: SSD|HDD, default SSD
  • PGSQL.PG_BOOTSTRAP.patroni_log_dir: patroni log directory, default /pg/log
  • PGSQL.PG_BOOTSTRAP.patroni_ssl_enabled: Use SSL for patroni RestAPI?
  • PGSQL.PG_BOOTSTRAP.patroni_username: patroni rest api username
  • PGSQL.PG_BOOTSTRAP.patroni_password: patroni rest api password (important: change this)
  • PGSQL.PG_BOOTSTRAP.patroni_citus_db: Citus database managed by patroni, default postgres
  • PGSQL.PG_BOOTSTRAP.pg_max_conn: postgres max connections, auto uses recommended value
  • PGSQL.PG_BOOTSTRAP.pg_shmem_ratio: postgres shared memory ratio, default 0.25, range 0.1~0.4
  • PGSQL.PG_BOOTSTRAP.pg_rto: Recovery Time Objective, failover ttl, default 30s
  • PGSQL.PG_BOOTSTRAP.pg_rpo: Recovery Point Objective, max 1MB data loss by default
  • PGSQL.PG_BOOTSTRAP.pg_pwd_enc: Password encryption algorithm: md5|scram-sha-256
  • PGSQL.PG_BOOTSTRAP.pgbouncer_log_dir: pgbouncer log directory, default /var/log/pgbouncer
  • PGSQL.PG_BOOTSTRAP.pgbouncer_auth_query: If enabled, query pg_authid for biz users instead of populating user list
  • PGSQL.PG_BOOTSTRAP.pgbouncer_sslmode: pgbouncer client SSL: disable|allow|prefer|require|verify-ca|verify-full
  • PGSQL.PG_BOOTSTRAP.pg_service_provider: Dedicated haproxy node group name, or empty for local node
  • PGSQL.PG_BOOTSTRAP.pg_default_service_dest: Default service destination if svc.dest=‘default’
  • PGSQL.PG_BACKUP.pgbackrest_enabled: Enable pgbackrest?
  • PGSQL.PG_BACKUP.pgbackrest_clean: Remove pgbackrest data during init?
  • PGSQL.PG_BACKUP.pgbackrest_log_dir: pgbackrest log directory, default /pg/log
  • PGSQL.PG_BACKUP.pgbackrest_method: pgbackrest backup repo method: local or minio
  • PGSQL.PG_BACKUP.pgbackrest_repo: pgbackrest backup repo config
  • PGSQL.PG_DNS.pg_dns_suffix: pgsql dns suffix, default empty
  • PGSQL.PG_DNS.pg_dns_target: auto, primary, vip, none, or ad hoc ip
  • ETCD.etcd_seq: etcd instance identifier, required
  • ETCD.etcd_cluster: etcd cluster and group name, default etcd
  • ETCD.etcd_safeguard: Prevent purging running etcd instances?
  • ETCD.etcd_clean: Clean existing etcd during init?
  • ETCD.etcd_data: etcd data directory, default /data/etcd
  • ETCD.etcd_port: etcd client port, default 2379
  • ETCD.etcd_peer_port: etcd peer port, default 2380
  • ETCD.etcd_init: etcd initial cluster state: new or existing
  • ETCD.etcd_election_timeout: etcd election timeout, default 1000ms
  • ETCD.etcd_heartbeat_interval: etcd heartbeat interval, default 100ms
  • MINIO.minio_seq: minio instance identifier, required
  • MINIO.minio_cluster: minio cluster name, default minio
  • MINIO.minio_clean: Clean minio during init? default false
  • MINIO.minio_user: minio OS user, default minio
  • MINIO.minio_node: minio node name pattern
  • MINIO.minio_data: minio data directory, use {x…y} for multiple drives
  • MINIO.minio_domain: minio external domain, default sss.pigsty
  • MINIO.minio_port: minio service port, default 9000
  • MINIO.minio_admin_port: minio console port, default 9001
  • MINIO.minio_access_key: root access key, default minioadmin
  • MINIO.minio_secret_key: root secret key, default minioadmin
  • MINIO.minio_extra_vars: extra environment variables for minio server
  • MINIO.minio_alias: alias for local minio deployment
  • MINIO.minio_buckets: list of minio buckets to create
  • MINIO.minio_users: list of minio users to create

Removed Parameters

  • INFRA.CA.ca_homedir: CA home directory, now fixed to /etc/pki/
  • INFRA.CA.ca_cert: CA certificate filename, now fixed to ca.key
  • INFRA.CA.ca_key: CA key filename, now fixed to ca.key
  • INFRA.REPO.repo_upstreams: Replaced by repo_upstream
  • PGSQL.PG_INSTALL.pgdg_repo: Now handled by node playbooks
  • PGSQL.PG_INSTALL.pg_add_repo: Now handled by node playbooks
  • PGSQL.PG_IDENTITY.pg_backup: Unused and conflicted with partial names
  • PGSQL.PG_IDENTITY.pg_preflight_skip: No longer used, replaced by pg_id
  • DCS.dcs_name: Removed due to etcd usage
  • DCS.dcs_servers: Replaced by ad hoc group etcd
  • DCS.dcs_registry: Removed due to etcd usage
  • DCS.dcs_safeguard: Replaced by etcd_safeguard
  • DCS.dcs_clean: Replaced by etcd_clean

Renamed Parameters

  • nginx_upstream -> infra_portal
  • repo_address -> repo_endpoint
  • pg_hostname -> node_id_from_pg
  • pg_sindex -> pg_group
  • pg_services -> pg_default_services
  • pg_services_extra -> pg_services
  • pg_hba_rules_extra -> pg_hba_rules
  • pg_hba_rules -> pg_default_hba_rules
  • pgbouncer_hba_rules_extra -> pgb_hba_rules
  • pgbouncer_hba_rules -> pgb_default_hba_rules
  • vip_mode -> pg_vip_enabled
  • vip_address -> pg_vip_address
  • vip_interface -> pg_vip_interface
  • node_packages_default -> node_default_packages
  • node_packages_meta -> infra_packages
  • node_packages_meta_pip -> infra_packages_pip
  • node_data_dir -> node_data

Special thanks to Italian user @alemacci for contributions on SSL encryption, backup, multi-OS distro adaptation, and adaptive parameter templates!

Sources

1.14 - Pigsty v1.5.1

Grafana security hotfix

v1.5.1 ·

Highlights

IMPORTANT: Fixed the issue where CREATE INDEX|REINDEX CONCURRENTLY in PG14.0-14.3 could corrupt index data.

Pigsty v1.5.1 upgrades the default PostgreSQL version to 14.4. Strongly recommend updating ASAP.

Software Upgrades

  • postgres upgraded to 14.4
  • haproxy upgraded to 2.6.0
  • grafana upgraded to 9.0.0
  • prometheus upgraded to 2.36.0
  • patroni upgraded to 2.1.4

Bug Fixes

  • Fixed TYPO in pgsql-migration.yml
  • Removed PID config item from HAProxy configuration
  • Removed i686 packages from default packages
  • Enabled all Systemd Redis Services by default
  • Enabled all Systemd Patroni Services by default

API Changes

  • grafana_database and grafana_pgurl marked as deprecated API, will be removed in future versions

New Applications

  • wiki.js: Build local Wikipedia with Postgres
  • FerretDB: Provide MongoDB API using Postgres

Sources

1.15 - Pigsty v1.5.0

Docker application support

v1.5.0 ·

Highlights

  • Complete Docker support: enabled by default on meta node with many out-of-the-box software templates: bytebase, pgadmin, pgweb, postgrest, minio, etc.
  • Infrastructure self-monitoring: Nginx, ETCD, Consul, Prometheus, Grafana, Loki self-monitoring
  • CMDB upgrade: compatibility improvements, supports Redis cluster/Greenplum cluster metadata, config file visualization
  • Service discovery improvements: Consul can auto-discover all monitoring targets and integrate with Prometheus
  • Better cold backup support: default scheduled backup tasks, pg_probackup backup tool, one-click delayed replica creation
  • ETCD can now be used as PostgreSQL/Patroni DCS service, as an alternative to Consul
  • Redis playbook/role improvements: now allows init and remove operations for individual Redis instances, not just entire Redis nodes

Monitoring System

Dashboards

  • CMDB Overview: visualize Pigsty CMDB Inventory
  • DCS Overview: view Consul and ETCD cluster monitoring metrics
  • Nginx Overview: view Pigsty Web access metrics and logs
  • Grafana Overview: Grafana self-monitoring
  • Prometheus Overview: Prometheus self-monitoring
  • INFRA Dashboard redesigned to reflect overall infrastructure status

Monitoring Architecture

  • Now allows Consul for service discovery (when all services are registered to Consul)
  • All Infra components now enable self-monitoring and register to Prometheus and Consul via infra_register role
  • Metrics collector pg_exporter updated to v0.5.0, new features: scale and default, allowing metric multiplication factors and default values
  • pg_bgwriter, pg_wal, pg_query, pg_db, pgbouncer_stat time-related metrics now uniformly scaled to seconds from milliseconds/microseconds
  • Related counter metrics in pg_table now have default value 0 instead of NaN
  • pg_class metrics collector removed by default, related metrics added to pg_table and pg_index collectors
  • pg_table_size metrics collector now enabled by default with 300-second cache time

Deployment

  • New optional package docker.tgz with common app images: Pgadmin, Pgweb, Postgrest, ByteBase, Kong, Minio, etc.
  • New ETCD role: automatically deploys ETCD service on DCS Server nodes and integrates with monitoring
  • pg_dcs_type specifies DCS service for PG high-availability: Consul (default), ETCD (alternative)
  • node_crontab parameter for configuring node scheduled tasks like database backups, VACUUM, statistics collection
  • New pg_checksum option: when enabled, database cluster enables data checksums (previously only crit template enabled by default)
  • New pg_delay option: when instance is Standby Cluster Leader, this parameter configures a delayed replica
  • New pg_probackup package, default role replicator now has backup-related function permissions
  • Redis deployment split into two parts: Redis node and Redis instance, redis_port parameter controls specific instances
  • Loki and Promtail now installed via fpm-built RPM packages
  • DCS3 config template now uses a 3-node pg-meta cluster with a single-node delayed replica

Software Upgrades

  • PostgreSQL upgraded to 14.3
  • Redis upgraded to 6.2.7
  • PG Exporter upgraded to 0.5.0
  • Consul upgraded to 1.12.0
  • vip-manager upgraded to v1.0.2
  • Grafana upgraded to v8.5.2
  • Loki & Promtail upgraded to v2.5.0, using fpm packaging

Bug Fixes

  • Fixed Loki and Promtail default config filename issues
  • Fixed Loki and Promtail environment variable expansion issues
  • Complete English documentation translation and revision; documentation JS resources now served locally, no internet access required

API Changes

New Parameters

  • node_data_dir: Main data mount path, created if doesn’t exist
  • node_crontab_overwrite: Overwrite /etc/crontab instead of appending
  • node_crontab: Node crontab content to append or overwrite
  • nameserver_enabled: Enable nameserver on this infra node?
  • prometheus_enabled: Enable prometheus on this infra node?
  • grafana_enabled: Enable grafana on this infra node?
  • loki_enabled: Enable loki on this infra node?
  • docker_enable: Enable docker on this infra node?
  • consul_enable: Enable consul server/agent?
  • etcd_enable: Enable etcd server/client?
  • pg_checksum: Enable pg cluster data checksums?
  • pg_delay: Application delay when backup cluster leader replays replication

Parameter Redesign

*_clean is now a boolean parameter for cleaning existing instances during init.

*_safeguard is also a boolean parameter to prevent cleaning running instances during any playbook execution.

  • pg_exists_action -> pg_clean
  • pg_disable_purge -> pg_safeguard
  • dcs_exists_action -> dcs_clean
  • dcs_disable_purge -> dcs_safeguard

Parameter Renames

  • node_ntp_config -> node_ntp_enabled
  • node_admin_setup -> node_admin_enabled
  • node_admin_pks -> node_admin_pk_list
  • node_dns_hosts -> node_etc_hosts_default
  • node_dns_hosts_extra -> node_etc_hosts
  • node_dns_server -> node_dns_method
  • node_local_repo_url -> node_repo_local_urls
  • node_packages -> node_packages_default
  • node_extra_packages -> node_packages
  • node_packages_meta -> node_packages_meta
  • node_meta_pip_install -> node_packages_meta_pip
  • node_sysctl_params -> node_tune_params
  • app_list -> nginx_indexes
  • grafana_plugin -> grafana_plugin_method
  • grafana_cache -> grafana_plugin_cache
  • grafana_plugins -> grafana_plugin_list
  • grafana_git_plugin_git -> grafana_plugin_git
  • haproxy_admin_auth_enabled -> haproxy_auth_enabled
  • pg_shared_libraries -> pg_libs
  • dcs_type -> pg_dcs_type

Sources

1.16 - Pigsty v1.4.1

Bug fixes & full English documentation translation

v1.4.1 ·

Bug fixes / Docker support / English documentation

Docker is now enabled by default on the meta node, allowing you to spin up various software.

Bug Fixes

  • Fixed Promtail & Loki configuration variable issues
  • Fixed Grafana legacy alerts
  • Disabled nameserver by default
  • Renamed pg-alias.sh for Patroni shortcuts
  • Disabled exemplars queries for all dashboards
  • Fixed Loki data directory issue
  • Changed autovacuum_freeze_max_age from 100000000 to 1000000000

Sources

1.17 - Pigsty v1.4.0

MatrixDB support, separated INFRA/NODES/PGSQL/REDIS modules

v1.4.0 ·

Architecture

  • Decoupled system into 4 major categories: INFRA, NODES, PGSQL, REDIS, making Pigsty clearer and more extensible
  • Single-node deployment = INFRA + NODES + PGSQL
  • PGSQL cluster deployment = NODES + PGSQL
  • Redis cluster deployment = NODES + REDIS
  • Other database deployment = NODES + xxx (e.g., MONGO, KAFKA…)

Accessibility

  • CDN for mainland China
  • Use bash -c "$(curl -fsSL http://get.pigsty.cc/latest)" to get latest source
  • New download script to download and extract packages

Monitoring Enhancements

  • Split monitoring into 5 categories: INFRA, NODES, REDIS, PGSQL, APP
  • Logging enabled by default
    • loki and promtail now enabled by default, with prebuilt loki-rpm
  • Model and labels
    • Added hidden ds prometheus datasource variable to all dashboards
    • Added ip label to all metrics, used as join key between database and node metrics
  • INFRA Monitoring
    • INFRA main dashboard: INFRA Overview
    • Added Log dashboard: Logs Instance
    • PGLOG Analysis and PGLOG Session now treated as sample Pigsty APPs
  • NODES Monitoring Application
    • Pigsty can be used standalone as host monitoring software
    • Includes 4 core dashboards: Nodes Overview & Nodes Cluster & Nodes Instance & Nodes Alert
    • New identity variables for nodes: node_cluster and nodename
  • PGSQL Monitoring Enhancements
    • New PGSQL Cluster, simplified and focused on what matters in a cluster
    • New dashboard PGSQL Databases for cluster-level object monitoring
    • PGSQL Alert dashboard now focuses solely on PGSQL alerts
    • PGSQL Shard added to PGSQL
  • Redis Monitoring Enhancements
    • Added node monitoring to all Redis dashboards

MatrixDB Support

  • MatrixDB (Greenplum 7) can be deployed via pigsty-matrix.yml playbook
  • MatrixDB monitoring dashboard: PGSQL MatrixDB
  • Added sample configuration: pigsty-mxdb.yml

Software Upgrades

  • PostgreSQL 14.2
  • PostGIS 3.2
  • TimescaleDB 2.6
  • Patroni 2.1.3 (Prometheus metrics + failover slots)
  • HAProxy 2.5.5 (fixed stats errors, more metrics)
  • PG Exporter 0.4.1 (timeout parameters, etc.)
  • Grafana 8.4.4
  • Prometheus 2.33.4
  • Greenplum 6.19.4 / MatrixDB 4.4.0
  • Loki now provided as RPM package instead of ZIP archive

Bug Fixes

  • Removed Patroni’s Consul dependency, making migration to new Consul clusters easier
  • Fixed Prometheus bin/new script default data directory path
  • Added restart seconds in vip-manager systemd service
  • Fixed typos and tasks

API Changes

New Variables

  • node_cluster: Identity variable for node cluster
  • nodename_overwrite: If set, nodename will be set to node’s hostname
  • nodename_exchange: Exchange node hostnames between play hosts (in /etc/hosts)
  • node_dns_hosts_extra: Extra static DNS records easily overridable by single instance/cluster
  • patroni_enabled: If disabled, postgres & patroni bootstrap not executed during postgres role
  • pgbouncer_enabled: If disabled, pgbouncer not started during postgres role
  • pg_exporter_params: Extra URL parameters for pg_exporter when generating monitoring target URL
  • pg_provision: Boolean variable indicating whether to execute provisioning part of postgres role
  • no_cmdb: Used for infra.yml and infra-demo.yml playbooks, won’t create CMDB on meta node

Sources

1.18 - Pigsty v1.3.1

Dashboard polish, security fixes, and software upgrades

v1.3.1 ·

Monitoring

  • PGSQL & PGCAT dashboard improvements
  • Optimized PGCAT Instance & PGCAT Database layout
  • Added key metric panels to PGSQL Instance dashboard (consistent with PGSQL Cluster)
  • Added table/index bloat panels to PGCAT Database, removed PGCAT Bloat dashboard
  • Added index information to PGCAT Database dashboard
  • Fixed broken panels in Grafana 8.3
  • Added Redis index to Nginx homepage

Deployment

  • New infra-demo.yml playbook for one-click bootstrap
  • New infra-jupyter.yml playbook for optional JupyterLab server
  • New infra-pgweb.yml playbook for optional PGWeb server
  • Added pg alias on meta node for starting PostgreSQL cluster from admin user
  • Adjusted max_locks_per_transactions in all Patroni config templates per timescaledb-tune recommendations
  • Added citus.node_conninfo: 'sslmode=prefer' to config templates for SSL-free Citus usage
  • Added all extensions (except pgrouting) from PGDG14 to package list
  • Upgraded node_exporter to v1.3.1
  • Added PostgREST v9.0.0 for generating REST APIs from PostgreSQL schemas

Bug Fixes

  • Grafana security vulnerability fix (upgraded to v8.3.1, details)
  • Fixed pg_instance & pg_service issues in register role when starting playbook mid-run
  • Fixed Nginx homepage rendering on hosts without pg_cluster variable
  • Fixed style issues when upgrading to Grafana 8.3.1

Sources

1.19 - Pigsty v1.3.0

PGCAT overhaul & PGSQL enhancement & Redis beta support

v1.3.0 ·

Redis Support

Feature Description
Redis Deployment Standalone, Sentinel, and Cluster modes
Redis Monitoring Overview, Cluster, and Instance dashboards

PGCAT Overhaul

Dashboard Description
PGCAT Instance New instance-level catalog dashboard
PGCAT Database New database-level catalog dashboard
PGCAT Table Redesigned table-level dashboard

PGSQL Enhancements

Dashboard Improvements
PGSQL Cluster Added 10 key metric panels
PGSQL Instance Added 10 key metric panels
PGSQL Service Simplified and redesigned
Cross-references Navigation links between PGCAT and PGSQL dashboards

Monitor Deployment

  • Grafana datasources auto-register during monitor-only deployment

Software Upgrades

  • PostgreSQL 13 added to default package list
  • PostgreSQL upgraded to 14.1 as default
  • Added Greenplum RPM packages and dependencies
  • Added Redis RPM and source packages
  • Added perf as default package

Sources

1.20 - Pigsty v1.2.0

Default PGSQL version upgraded to 14

v1.2.0 ·

Core Features

  • Default to PostgreSQL 14
  • Default to TimescaleDB 2.5 extension
  • TimescaleDB and PostGIS enabled by default in CMDB

Monitor-Only Mode

  • Monitor existing PostgreSQL instances via connection URL only
  • pg_exporter deployed on local meta node
  • New PGSQL Cluster Monly dashboard for remote clusters

Software Upgrades

  • Grafana upgraded to 8.2.2
  • pev2 upgraded to v0.11.9
  • Promscale upgraded to 0.6.2
  • PgWeb upgraded to 0.11.9
  • New extensions: pglogical, pg_stat_monitor, orafce

Improvements

  • Auto-detect machine specs and use appropriate node_tune and pg_conf templates
  • Reworked bloat-related views, exposing more information
  • Removed TimescaleDB and Citus internal monitoring
  • Added pgsql-audit.yml playbook for creating audit reports
  • All config templates simplified to two: auto and demo

Bug Fixes

  • pgbouncer_exporter resource owner changed to {{ pg_dbsu }} instead of postgres
  • Fixed pg_exporter duplicate metrics on pg_table/pg_index during REINDEX TABLE CONCURRENTLY

Sources

1.21 - Pigsty v1.1.1

TimescaleDB upgrade and new Patroni templates

v1.1.1 ·

  • Replaced TimescaleDB apache version with timescale version
  • Upgraded Prometheus to 2.30
  • Fixed pg_exporter config directory owner issue (changed to {{ pg_dbsu }})

Upgrade Notes

The main change in this version is TimescaleDB — using the official TimescaleDB License (TSL) version to replace the Apache License v2 version from the PGDG repository.


## Sources

- [GitHub release](https://github.com/pgsty/pigsty/releases/tag/v1.1.1)
- [Pigsty v1.1 release article](https://pigsty.io/blog/pigsty/v1.1/)
- [Source comparison: `v1.1...v1.1.1`](https://github.com/pgsty/pigsty/compare/v1.1...v1.1.1)

1.22 - Pigsty v1.1.0

Homepage, JupyterLab, PGWEB, Pev2 & pgbadger

v1.1 ·

Feature Enhancements

  • Added pg_dummy_filesize to create filesystem space placeholder
  • Major homepage redesign
  • Added JupyterLab integration
  • Added PGWeb console integration
  • Added PgBadger support
  • Added PEV2 support, execution plan visualization tool
  • Added pglog tooling

Software Upgrades

  • PostgreSQL upgraded to v13.4 (with official PG14 support)
  • pgbouncer upgraded to v1.16 (metric definitions updated)
  • Grafana upgraded to v8.1.4
  • Prometheus upgraded to v2.29
  • node_exporter upgraded to v1.2.2
  • HAProxy upgraded to v2.1.1
  • Consul upgraded to v1.10.2
  • vip-manager upgraded to v1.0.1

API Changes

  • nginx_upstream now has different structure (incompatible)
  • New config entry: app_list, navigation entries rendered to homepage
  • New config entry: docs_enabled, setup local docs on default server
  • New config entry: pev2_enabled, setup local PEV2 tool
  • New config entry: pgbadger_enabled, create log summary/report directory
  • New config entry: jupyter_enabled, enable JupyterLab server on meta node
  • New config entry: jupyter_username, specify user to run JupyterLab
  • New config entry: jupyter_password, specify default password for JupyterLab
  • New config entry: pgweb_enabled, enable PGWeb server on meta node
  • New config entry: pgweb_username, specify user to run PGWeb
  • Renamed internal flag repo_exist to repo_exists
  • repo_address default value changed to pigsty instead of yum.pigsty
  • HAProxy access point changed to http://pigsty instead of http://h.pigsty

Sources

1.23 - Pigsty v1.0.1

Bug fixes and documentation improvements

v1.0.1 ·

2021-09-14

Documentation Update

  • Chinese documentation now available
  • Machine-translated English documentation now available

Bug Fixes

  • pgsql-remove no longer removes primary instances
  • Replaced pg_instance with pg_cluster + pg_seq (Start-At-Task could fail when pg_instance undefined)
  • Removed Citus from default shared preload libraries (Citus forces max_prepared_transaction to non-zero)
  • Added ssh sudo check in configure (now uses ssh -t sudo -n ls for permission check)
  • Fixed pg-backup script typo

Optimizations

  • Removed NTP sanity check alert (duplicate of ClockSkew)
  • Removed collector.systemd to reduce overhead

Sources

1.24 - Pigsty v1.0.0

v1 GA, Monitoring System Overhaul

v1.0.0 ·

Monitoring System Overhaul

  • New dashboards on Grafana 8.0
  • New metric definitions, added PG14 support
  • Simplified labeling system: static label set (job, cls, ins)
  • New alerting rules and derived metrics
  • Monitor multiple databases simultaneously
  • Real-time log search & csvlog analysis
  • Richly-linked dashboards, click through for drill-down/roll-up

Architecture Changes

  • Added Citus and TimescaleDB to default installation
  • Added PostgreSQL 14beta2 support
  • Simplified HAProxy admin page indexing
  • Decoupled infrastructure and PGSQL by adding new role register
  • Added new roles loki and promtail for logging
  • Added new role environ to setup environment for admin user on meta node
  • Default to static service discovery for Prometheus (instead of consul)
  • Added new role remove for graceful cluster and instance removal
  • Upgraded Prometheus and Grafana provisioning logic
  • Upgraded to vip-manager 1.0, node_exporter 1.2, pg_exporter 0.4, Grafana 8.0
  • Every database on every instance auto-registers as a Grafana datasource
  • Moved Consul registration to register role, changed Consul service tags
  • Added cmdb.sql as pg-meta baseline definition (CMDB & PGLOG)

Application Framework

  • Extensible framework for new features
  • Core app: PostgreSQL monitoring system pgsql
  • Core app: PostgreSQL catalog explorer pgcat
  • Core app: PostgreSQL csvlog analyzer pglog
  • Added sample app covid for COVID-19 data visualization
  • Added sample app isd for ISD weather data visualization

Other

  • Added JupyterLab for full Python data science environment
  • Added vonng-echarts-panel to restore ECharts support
  • Added wrapper scripts createpg, createdb, createuser
  • Added CMDB dynamic inventory scripts: load_conf.py, inventory_cmdb, inventory_conf
  • Removed obsolete playbooks: pgsql-monitor, pgsql-service, node-remove, etc.

API Changes

  • New variable: node_meta_pip_install
  • New variable: grafana_admin_username
  • New variable: grafana_database
  • New variable: grafana_pgurl
  • New variable: pg_shared_libraries
  • New variable: pg_exporter_auto_discovery
  • New variable: pg_exporter_exclude_database
  • New variable: pg_exporter_include_database
  • Variable renamed: grafana_url → grafana_endpoint

Bug Fixes

  • Fixed default timezone Asia/Shanghai (CST) issue
  • Fixed nofile limits for pgbouncer & patroni
  • pgbouncer user list and database list now generated when running tag pgbouncer

Sources

1.25 - Pigsty v0.9.1

Three-step installer, PostgreSQL 13.3, and Grafana 7.5.6

v0.9.1 ·

Update package version:

  • PostgreSQL 13.3
  • Grafana 7.5.6

Add configure support.

Now install pigsty with 3 steps: download, configure and install:

curl -fsSL https://github.com/pgsty/pigsty/releases/download/v0.9.1/pigsty.tgz | gzip -d | tar -xC ~; cd ~/pigsty
./configure
make install

WoW! just like old time!

Sources

1.26 - Pigsty v0.9.0

Pigsty GUI, CLI, Logging Integration

v0.9.0 ·

New Stuff

  • One-liner install: curl -fsSL https://pigsty.cc/install | bash bootstraps everything.
  • pigsty-cli: wraps the common Ansible playbooks so you stop copy-pasting command lines. Still beta but already handy.
  • Loki + Promtail: Postgres, pgbouncer, and Patroni logs stream into Grafana with metrics extracted from log volume. infra-loki.yml and pgsql-promtail.yml wire things up.
  • Binary exporters: grab monitoring binaries with files/get_bin.sh if you don’t want to rely on repos.
  • Flight mode: once the meta node is initialized you can run bin/upgrade to switch into a dynamic inventory using data stored inside pg-meta.

Fixes

  • Cleaned up HAProxy health checks that were flooding PG and Patroni logs with connection reset noise.
  • Patroni logs now carry readable timestamps (no more millisecond fragments) and explicit time zones.
  • Monitoring queries run by dbuser_monitor log only when slower than 1s.
  • Grafana role refactor keeps the API stable, but uses CDN-hosted plugin bundles for faster installs.
  • Pgbouncer user creation now handles md5 passwords properly.
  • Hardened SQL templates for DB/user creation, fixed DNS orchestration edge cases, and tidied Makefile typos.

Knob Changes

  • node_disable_swap defaults to false; Pigsty no longer nukes swap by default.
  • node_sysctl_params stops writing kernel tunables unless you explicitly set them.
  • grafana_plugin: install now means “download from CDN if cache is missing.”
  • repo_url_packages pulls extra RPMs from the Pigsty CDN so installs inside China work out of the box.
  • proxy_env.no_proxy includes the CDN endpoints.
  • grafana_customize defaults to false; flip it on only if you have the Pigsty Pro UI bits.
  • node_admin_pk_current adds your current ~/.ssh/id_rsa.pub to the admin account.
  • Loki/Promtail knobs: loki_clean, loki_data_dir, promtail_enabled, promtail_clean, promtail_port, promtail_status_file, promtail_send_url.

Sources

1.27 - Pigsty v0.8.0

Service Provision

v0.8.0 ·

v0.8 finalizes the provisioning API. Services are completely rebuilt: instead of a hard-coded primary/replica pair you can now declare any number of services, plug in HAProxy, swap in an external load balancer, or hand off to a custom VIP controller. Everything else in the supply chain stabilizes on top of this model.

Service API

The old vip and haproxy knobs moved under the service role. pg_services (plus pg_services_extra) define each exposed endpoint—name, ports, selectors, health checks, weights, and balancer hints. Selectors are JMESPath filters over cluster members, and optional selector_backup pools handle fail-in when replicas are gone. Out of the box we ship primary, replica, default, and offline service definitions; swap dst_port to point at postgres, pgbouncer, or any number.

The HAProxy stanza keeps per-service tuning (maxconn, algorithm, timeouts) while VIP config distinguishes L2/L4 implementations so you can drop Pigsty behind an existing load balancer.

Database Interface Tweaks

Locales can now be split into lc_collate and lc_ctype so extensions like pg_trgm behave with non-C collations. The rest of the pg_databases schema stays the same—owner/template/encoding/connlimit/revokeconn/pgbouncer/comment—just with better defaults and inline comments.

Sources

1.28 - Pigsty v0.7.0

Monitor only deployment

v0.7.0 ·

Pigsty v0.7 focuses on plugging existing fleets into Pigsty’s observability stack. The new monitor-only flow lets you drop Pigsty dashboards onto databases that were provisioned elsewhere, and the declarative APIs for databases and users got a much needed redesign.

Highlights

  • Monitor-only deployment flow (monly) with its own playbook.
  • Split static Prometheus target files by cluster for easier hand-editing.
  • New helper playbooks: pgsql-createuser.yml and pgsql-createdb.yml for live clusters.
  • Database and user schema definitions now cover owner/template/locale knobs plus per-role capabilities.
  • Bug fixes for extension schema typos and pgbouncer reload.

API Changes

New options:

prometheus_sd_target: batch
exporter_install: none
exporter_repo_url: ''
node_exporter_options: '--no-collector.softnet --collector.systemd --collector.ntp --collector.tcpstat --collector.processes'
pg_exporter_url: ''
pgbouncer_exporter_url: ''

Removed option:

exporter_binary_install

Structures affected: pg_default_roles, pg_users, pg_databases. Also fixed the pg_default_privilegs typo → pg_default_privileges.

Monitor-Only Mode

When you just want Pigsty’s observability without touching the way databases were provisioned, run the monly flow. Infra still gets bootstrapped on the meta node via ./infra.yml, but database nodes skip the provisioning playbooks and only run ./pgsql-monitor.yml. Config gets much shorter—most of the time you only keep infra vars and a handful of monitoring knobs.

Database Provisioning Interface

pg_databases now exposes owner/template/encoding/locale/connlimit/allowconn knobs plus revokeconn (strip CONNECT from public) and inline comments. Use ./pgsql-createdb.yml -e pg_database=<name> to create or mutate live databases; the generated SQL lives inside /pg/tmp/pg-db-<name>.sql on the primary.

User Provisioning Interface

pg_users swapped username → name, groups → roles, and exploded options into discrete flags (login, superuser, createdb, createrole, inherit, replication, bypassrls, connlimit). Users can also get expire_at / expire_in timers plus pgbouncer defaults to false. Apply changes through ./pgsql-createuser.yml -e pg_user=<name> which renders /pg/tmp/pg-user-<name>.sql on the primary.

Sources

1.29 - Pigsty v0.6.0

Architecture Enhancement

v0.6.0 ·

Pigsty v0.6 responds to user feedback with a redesigned provisioning path plus a monitoring stack that can sit beside any managed PG fleet—even a MyBase cluster built elsewhere.

Bug Fixes

  • Patroni no longer resets PG HBA on restart.
  • Fixed copy typos and the default primary for the pg-test sandbox cluster.
  • Patched the dashboard title typo on PG Overview.

Feature Work

  • Monitoring supply chain overhaul: Prometheus can now run fully static, exporters accept service_registry toggles, and exporter_binary_install lets you drop binaries without hitting repos. Each exporter has its own *_enabled flag.
  • Prometheus static discovery is rendered straight from inventory, so you can graft Pigsty dashboards onto any PG-as-a-service footprint.
  • HAProxy provisioning adds a global console at h.pigsty, optional auth, fallback routing to the primary when all replicas die, and per-service weight tuning.
  • ACL defaults now include dbrole_offline for slow-query/ETL workloads plus HBA rules that fence those workloads to marked nodes.
  • Component refresh: PostgreSQL 13.2, Prometheus 2.25, pg_exporter 0.3.2, node_exporter 1.1, Consul 1.9.3, and a faster ZJU PG mirror.

API Changes

New knobs:

service_registry: consul
prometheus_options: '--storage.tsdb.retention=30d'
prometheus_sd_method: consul
prometheus_sd_interval: 2s
pg_offline_query: false
node_exporter_enabled: true
pg_exporter_enabled: true
pgbouncer_exporter_enabled: true
dcs_disable_purge: false
pg_disable_purge: false
haproxy_weight: 100
haproxy_weight_fallback: 1

Removed knobs:

prometheus_metrics_path
prometheus_retention

Sources

1.30 - Pigsty v0.5.2

Playbook restructuring, VIP/HAProxy controls, and configuration cleanup

[!INFO]

This historical version is represented by its Git tag and source tree because no GitHub Release was published.

Highlights

  • Made pigsty.yml the default configuration and reorganized the standard sandbox.yml, infra.yml, and pgsql.yml workflows.
  • Split VIP management from HAProxy and added administrator authentication, traffic weights, and primary fallback controls.
  • Added the nameserver step to the main playbook and refreshed the accompanying documentation.

v0.5.2 is a tagged maintenance snapshot without a separately authored GitHub Release body.

Sources

1.31 - Pigsty v0.5.1

Dashboard fixes, the ZJU mirror, and documentation refresh

[!INFO]

This historical version is represented by its Git tag and source tree because no GitHub Release was published.

Highlights

  • Fixed PostgreSQL cluster and instance dashboard issues found after v0.5.0.
  • Switched the default PostgreSQL mirror to Zhejiang University for faster regional installs.
  • Refreshed the README and consolidated the v0.5 documentation snapshot.

v0.5.1 is a tagged maintenance snapshot without a separately authored GitHub Release body.

Sources

1.32 - Pigsty v0.5.0

Database Customize Template

v0.5.0 ·

Outline

  • The official docs site (http://pigsty.cc/) is live.
  • Database templating becomes fully declarative: define users, roles, databases, ACLs, extensions, and schemas in config.
  • The default access model is refined and HBA management now comes straight from Pigsty instead of Patroni.
  • Grafana provisioning switched from shoving a sqlite file to JSON provisioning via API.
  • Added the pg-cluster-replication dashboard to the open bundle.
  • CentOS 7.8 offline bundle: pkg.tgz.

Declarative Database Layouts

Multi-tenant headaches go away once everything is described as code. The new templates let you declare users, passwords, role hierarchies, DB defaults, extensions, schemas, and default privileges in YAML so a single config file replaces piles of runbooks. A stripped example:


## Sources

- [GitHub release](https://github.com/pgsty/pigsty/releases/tag/v0.5.0)
- [Pigsty v0.5 release article](https://pigsty.io/blog/pigsty/v0.5/)
- [Historical About / Release Note](https://pigsty.io/docs/about/release/#v050)
- [Source comparison: `v0.4.0...v0.5.0`](https://github.com/pgsty/pigsty/compare/v0.4.0...v0.5.0)

1.33 - Pigsty v0.4.0

PostgreSQL 13 Support, Official Documentation

v0.4.0 ·

Pigsty v0.4 is our second public beta. The observability stack was rebuilt around Grafana 7.3, and ten curated dashboards became the default open-source payload. pg_exporter 0.3.1 drives metrics, and the alert wiring has been cleaned up for the new Grafana release.

Open-Source Dashboards

The OSS build now exposes ten high-signal Grafana panels: PG Overview, Cluster, Service, Instance, Database, Query, Table, Table Catalog, Table Detail, and Node. Even with a lean set it easily outclasses most “enterprise” PG monitoring suites.

Software Refresh

  • PostgreSQL 13.1 + Patroni 2.0.1-4, with citus added to the repo
  • pg_exporter upgraded to 0.3.1
  • Grafana jumps to 7.3; a ton of compatibility fixes landed
  • Prometheus 2.23 with the new UI enabled
  • Consul 1.9 and related components updated

Other Improvements

  • Updated Prometheus alert rules and Alertmanager info links
  • Fixed a batch of bugs and typos
  • Added a tiny backup script for quick dumps

Offline Bundle

Need an air-gapped install? Grab the CentOS 7.8 package bundle (pkg.tgz) from GitHub and deploy from local media.

Sources

1.34 - Pigsty v0.3.0

Provisioning Solution GA

v0.3.0 ·

Pigsty v0.3.0 is the very first public preview. It packages a lean observability stack plus a reproducible offline bundle so you can spin up a real PostgreSQL lab without touching the public Internet.

Observability Stack

The open build ships eight curated Grafana dashboards: PG Overview, Cluster, Service, Instance, Database, Table Overview, Table Catalog, and a bare-metal Node view. Even with a trimmed set the coverage still crushes most “enterprise” monitoring stories.

Offline Bundle

Shipyard environments can fetch the CentOS 7.8 offline bundle directly from GitHub (pkg.tgz). Drop it on the management node and you have a deterministic install no matter how broken the mirrors are.

Sources

1.35 - Pigsty v0.0.5

Offline installation, Consul role, and HAProxy fixes

[!INFO]

This historical version is represented by its Git tag and source tree because no GitHub Release was published.

Highlights

  • Added an offline installation mode for environments without Internet access.
  • Introduced a dedicated Consul role and refreshed repository bootstrap behavior.
  • Added psql startup helpers, split PostgreSQL script tasks, and fixed HAProxy connection-reset noise.

This historical record is reconstructed from the v0.0.5 tag and its source comparison with v0.0.4.

Sources

1.36 - Pigsty v0.0.4

Ansible role split, networking, and repository refactor

[!INFO]

This historical version is represented by its Git tag and source tree because no GitHub Release was published.

Highlights

  • Split the former monolithic automation into dedicated Meta, Grafana, DNS, Nginx, and Prometheus roles.
  • Added static-network handling and revised the local repository upstream policy.
  • Expanded role documentation while keeping service registration templates aligned with the new layout.

This historical record is reconstructed from the v0.0.4 tag and its source comparison with v0.0.3.

Sources

1.37 - Pigsty v0.0.3

Interface and monitoring-schema improvements

[!INFO]

This historical version is represented by its Git tag and source tree because no GitHub Release was published.

Highlights

  • Improved the early Pigsty configuration interface and added the monitoring schema.
  • Added a Kubernetes playbook and refined the PostgreSQL initialization defaults.
  • Simplified the Vagrant bootstrap path and fixed several HAProxy, Patroni, and exporter registration details.

This historical tag predates the public release series. Its record is reconstructed from the tagged source and the commit history leading to v0.0.3.

Sources

1.38 - Pigsty v0.2.0

Provisioning refactor, etcd DCS, and HAProxy 2.2 support

[!INFO]

This historical version is represented by its Git tag and source tree because no GitHub Release was published.

Highlights

  • Refactored the infrastructure, node, repository, Patroni, PgBouncer, monitoring, and PostgreSQL initialization roles.
  • Added etcd as a DCS option, production/test inventory layouts, and configurable database superuser support.
  • Added Patroni pause handling, HAProxy 2.2 compatibility, and fixes for older CentOS 7 environments.

v0.2.0 predates curated GitHub Release notes. This record is reconstructed from the tagged source and the comparison with v0.1.0.

Sources

1.39 - Pigsty v0.1.0

Test-environment validation after the initial role-based restructuring

[!INFO]

This historical version is represented by its Git tag and source tree because no GitHub Release was published.

Highlights

  • Completed the first large-scale conversion from playbooks to reusable Ansible roles.
  • Established the initial PostgreSQL primary/standby, PgBouncer, monitoring, HAProxy, Keepalived, and repository workflows.
  • Validated the reorganized project in a production-like test environment.

v0.1.0 is an early engineering milestone rather than a curated GitHub Release. The notes are reconstructed from the tag and its commit history.

Sources

2 - Article

Pigsty essays, tutorials, field notes, and project stories collected from VONNG.

This column collects every article in VONNG’s dedicated Pigsty section, plus articles in other columns whose front matter explicitly carries the Pigsty tag. Original page resources and available English translations are preserved.

2.1 - Pigsty v2.6: PostgreSQL Crashes the OLAP Party

Originally published on VONNG.

GitHub Release | Release Note

On the last day of February, Pigsty v2.6 is officially released! This version makes PostgreSQL 16 the default major version and introduces a series of new extensions, including ParadeDB and DuckDB, elevating PostgreSQL’s OLAP analytical capabilities to an entirely new level. Calling it the HTAP benchmark and database all-rounder is well-deserved.

Additionally, we’ve completely refreshed the Pigsty official website, documentation, and blog, presenting six more refined core value propositions. Globally, we’re now using the Cloudflare-powered domain pigsty.io as the default official site and repository address. The original pigsty.cc domain, website, and repos continue to serve as mirrors within China.

Finally, we’re officially launching transparently-priced Pigsty Pro and service subscriptions, providing advanced features and support options for users who need them.

Epic-Level Analytics Enhancement

TPC-H and ClickBench are authoritative analytics benchmarks. ClickBench provides horizontal comparisons of many OLAP databases, serving as quantifiable references. In this representative example, we can see relative performance of many well-known database components (lower time is better):

olap.jpg

c6a.4xlarge, 500gb gp2 / 1 billion records

This chart shows PostgreSQL and its ecosystem extensions’ performance. Native untuned PostgreSQL takes (x1000), while tuned it reaches (x47). The PG ecosystem also has three analytics-related extensions: columnar Hydra (x42), time-series TimescaleDB (x103), and distributed Citus (x262). But compared to top-tier OLAP-focused systems — Umbra, ClickHouse, Databend, SelectDB (x3~x4) — there’s still a 10x+ performance gap. However, the recent arrival of ParadeDB and DuckDB has changed this!

ParadeDB’s native PG extension pg_analytics achieves second-tier (x10) performance, only 3-4x behind top-tier OLAP databases. Considering the extra benefits — ACID, data freshness, no ETL, no extra learning curve, no separate service to maintain (not to mention it also provides ElasticSearch-quality full-text search) — this performance gap is usually acceptable.

And DuckDB (x3.2) elevates OLAP to an entirely new level — setting aside academic databases like Umbra, DuckDB may be the fastest practical analytics database. While not a PG extension itself, it’s an embeddable component, and projects like DuckDB FDW and pg_quack let PostgreSQL fully leverage DuckDB’s complete analytical performance!

duck.jpg

Appreciation from ParadeDB’s founder and DuckDB FDW’s author

New Value Propositions

Value propositions are the soul of a database distribution. In this version, we present six core values as shown:

value1.jpg

This diagram lists six core problems PostgreSQL solves: Postgres extensibility, Infrastructure reliability, Graphics observability, Service availability, Toolbox maintainability, and component composabilitY.

value2.jpg

Pigsty’s six abbreviations form the PIGSTY acronym — besides PostgreSQL in Great STYle, these six value propositions offer another interpretation:

Postgres, Infras, Graphics, Service, Toolbox, Yours.

Your graphical Postgres infrastructure service toolbox.

We’ve also redesigned the logo, from the sunglasses-wearing pig head to a hexagonal composition with colors matching key components (PG Blue, ETCD Teal, Grafana Orange, Ansible Black, Redis/MinIO Red, Nginx Green) — a condensed version of the large hexagon above. The original sunglasses pig will continue as Pigsty’s mascot.

mascot.jpg

New Website

In this version, we’ve renovated the old website using the latest Docsy documentation framework, updating substantial content. We abandoned flashy impractical designs, putting Pigsty’s value propositions and core features directly on the landing page.

web1.jpg

The real content lives in the documentation. We restructured the doc directory:

web2.jpg

After letting go of pure Markdown purism, we can use attractive styles and features in documentation:

web3.jpg

Beyond docs, we’ve organized recent articles into the Pigsty blog, divided into six columns: Cloud Computing Mudslide, Database Veteran Driver, and PostgreSQL’s Ecosystem, Development, Administration, and Kernel sections.

web4.jpg

Meanwhile, Pigsty’s software repositories now have global mirrors powered by Cloudflare R2, hosted on Cloudflare for smooth access worldwide (China users can continue using pigsty.cc).

PostgreSQL 16 Becomes Default

The last notable feature: in Pigsty v2.6, PostgreSQL 16 (16.2) officially replaces PostgreSQL 15 as the default major version.

Three months ago, we noted that PostgreSQL’s main extensions were in place, plus with the second minor release, it was production-ready.

Pigsty v2.6 coincides with PostgreSQL 16.2’s third minor release, and important extensions like Hydra, PGML, and AGE have followed to PG 16. So we’ve decided to officially upgrade the default PG major version to 16, making it the only supported major version in the open-source edition (except EL7).

Therefore, another important technical decision in this version: we’ve removed the default PG 12-15 packages and extensions from the open-source edition. This doesn’t mean Pigsty doesn’t support PG 12-15 — with minor config adjustments, you can easily use older PostgreSQL versions and extensions — but we won’t run integration tests against these versions (though they’ve been thoroughly tested in older Pigsty releases).

pg16.jpg

Similarly, we’ve narrowed the open-source support scope to EL 8 / EL 9 and Ubuntu 22.04 — the three most widely-used OS distributions. In Pigsty 2.5, we supported PG 12-16 (five major versions) times seven OS distributions, totaling 34 combinations, plus upcoming ARM support, creating significant testing pressure.

Focusing the open-source edition on one core PG major version and three mainstream OS distributions better utilizes R&D bandwidth to meet the majority of open-source users’ needs. Again, this doesn’t mean Pigsty can’t run on older systems — you can still run smoothly on EL7, Ubuntu 20.04, Debian 11/12, but we won’t provide offline packages, smoke tests, or support for these OSes.

Supporting niche/legacy OSes and outdated major versions isn’t needed by the vast majority of users but requires substantial extra effort and cost, so it’s included in our paid commercial support.

Open Source vs Pro Edition

Some open-source users have feedback: “I don’t need stuff unrelated to PostgreSQL slowing down downloads/installation and adding management complexity — Redis, MinIO, Docker, K8S, Supabase — you think they help PG, but flashy extras only slow my attack speed.”

The specific feature division isn’t finalized yet, so 2.6 may be the last fully-featured open-source Pigsty version. But the basic principle: the open-source edition will retain all core modules and PG extensions (PGSQL, INFRA, NODE, ETCD), while modules less related to PostgreSQL may become Pro edition content later.

pro.jpg

Going forward, the Pigsty open-source edition will focus on doing one thing well — providing reliable, highly-available, extensible local PostgreSQL RDS services. Practical features like Docker templates may still stay in the open-source edition.

This doesn’t mean these features disappear from open-source Pigsty — seasoned open-source veterans can still easily recreate them by modifying config files — but they won’t be default components of the open-source version.

Commercial Subscriptions

Open source is a passion project powered by love, but sustainable development requires commercial interests. In this version, we officially launch commercial Pigsty editions, providing richer support options for those who need them.

Besides additional feature modules, Pigsty Professional Subscriptions provide consulting Q&A and backstop services, supporting a broader range of operating systems and database versions:

svc.jpg

While Pigsty’s mission is providing out-of-the-box database services — even with self-healing HA for hardware failures and PITR for software/human errors — you might spin it up and go a year, two, three without issues. Statistically, that’s normal.

But database problems are usually big problems. Misusing databases also tends to become big problems. So we provide expert consulting and services for paying customers as ultimate backstop for difficult issues. (Example: we’ve rescued a burned Gitlab database with no backups). We also offer professional PostgreSQL DBA consulting services: backup, security, compliance recommendations, management and development best practices, performance evaluation and optimization, design guidance and Q&A.

sub.jpg

Often, turning the ordinary into extraordinary and achieving orders-of-magnitude improvements comes down to one sentence from an expert. This is especially true for PostgreSQL, whose soul is extensibility and whose extension ecosystem is incredibly rich. Our services ensure every dollar you spend is worthwhile and spent on what truly matters.

Looking Forward

Pigsty’s next major version is planned as v3, officially implementing the open-source/pro feature division. We’ll complete missing extension DEBs for Ubuntu/Debian systems and provide a CLI tool to wrap management operations. We may package Pigsty itself as RPM/DEB, and plan beta MYSQL monitoring/deployment support.

For monitoring, we’ll redesign PostgreSQL monitoring dashboards based on PG 16’s IO metrics, provide MySQL monitoring capability, and try using Vector as an alternative to Promtail for log collection. We already have monitoring for Alibaba Cloud RDS PG and PolarDB; we also plan AWS RDS and Aurora monitoring support in v3.0.

For infrastructure, we’re choosing to abandon “cheap” Tencent Cloud CDN, fully embracing more reliable, faster, and cheaper Cloudflare to serve global users. Tencent Cloud CDN may serve as a domestic mirror for Pro edition acceleration.

Pigsty’s product and interfaces will stabilize in v2.6 and v3.0 — it’s already doing great on product and technology fronts! Even exceeding RDS in some areas (like extension support and monitoring!). So upcoming work will shift focus to marketing and sales. Sustainable open-source operations require user and customer support. If Pigsty has helped you, please consider sponsoring us or purchasing our service subscriptions.

v2.6.0 Release Notes

Highlights

Configuration Changes

  • Replaced node_repo_method with node_repo_modules, removed node_repo_local_urls
  • Temporarily disabled Grafana unified alerting to avoid “Database Locked” errors
  • New node_repo_modules parameter to specify upstream repos added to nodes
  • Removed node_local_repo_urls, functionality replaced by node_repo_modules & repo_upstream
  • Removed node_repo_method parameter, functionality replaced by node_repo_modules
  • Added new local source in repo_upstream, used via node_repo_modules to replace node_local_repo_urls
  • Reorganized node_default_packages, infra_packages, pg_packages, pg_extensions defaults
  • When replacing repo_upstream.baseurl, if EL8/9 PGDG minor-version-specific repos are available, use major.minor instead of major for $releasever for better minor version compatibility

Software Upgrades

  • Grafana 10.3
  • Prometheus 2.47
  • node_exporter 1.7.0
  • HAProxy 2.9.5
  • Loki / Promtail 2.9.4
  • minio-20240216110548 / mcli-20240217011557
  • etcd 3.5.11
  • Redis 7.2.4
  • Bytebase 2.13.2
  • DuckDB 0.10.0
  • FerretDB 1.19
  • Metabase: new Docker app template

PostgreSQL Extensions

  • PostgreSQL minor version upgrades: 16.2, 15.6, 14.11, 13.14, 12.18
  • PostgreSQL 16: now promoted to default major version
  • pg_exporter 0.6.1: security fix
  • Patroni 3.2.2
  • pgBadger 12.4
  • pgBackRest 2.50
  • vip-manager 2.3.0
  • PostGIS 3.4.2
  • TimescaleDB 2.14.1
  • Vector extension PGVector 0.6.0: added parallel HNSW index creation
  • New extension duckdb_fdw v1.1 for reading/writing DuckDB data
  • New extension pgsql-gzip for Gzip compression/decompression v1.0.0
  • New extension pg_sparse for efficient sparse vectors (ParadeDB) v0.5.6
  • New extension pg_bm25 for high-quality BM25 full-text search (ParadeDB) v0.5.6
  • New extension pg_analytics with SIMD + columnar storage for analytics (ParadeDB) v0.5.6
  • Upgraded AIML extension pgml to v2.8.1 with PG 16 support
  • Upgraded columnar extension hydra to v1.1.1 with PG 16 support
  • Upgraded graph extension age to v1.5.0 with PG 16 support
  • Upgraded GraphQL extension pg_graphql to v1.5.0 for Supabase support
MD5 (pigsty-v2.6.0.tgz) = 330e9bc16a2f65d57264965bf98174ff
MD5 (pigsty-pkg-v2.6.0.debian11.x86_64.tgz) = 81abcd0ced798e1198740ab13317c29a
MD5 (pigsty-pkg-v2.6.0.debian12.x86_64.tgz) = 7304f4458c9abd3a14245eaf72f4eeb4
MD5 (pigsty-pkg-v2.6.0.el7.x86_64.tgz) = f914fbb12f90dffc4e29f183753736bb
MD5 (pigsty-pkg-v2.6.0.el8.x86_64.tgz) = fc23d122d0743d1c1cb871ca686449c0
MD5 (pigsty-pkg-v2.6.0.el9.x86_64.tgz) = 9d258dbcecefd232f3a18bcce512b75e
MD5 (pigsty-pkg-v2.6.0.ubuntu20.x86_64.tgz) = 901ee668621682f99799de8932fb716c
MD5 (pigsty-pkg-v2.6.0.ubuntu22.x86_64.tgz) = 39872cf774c1fe22697c428be2fc2c22

2.2 - Pigsty v2.5: Ubuntu & PG16

Originally published on VONNG.

GitHub Release | Release Note

On Programmer’s Day (10/24), Pigsty v2.5.0 is released! This version adds support for Ubuntu and Debian operating systems. Combined with existing EL7/8/9 support, we’ve achieved a grand slam of mainstream Linux distributions.

Additionally, Pigsty now officially supports self-hosted Supabase and PostgresML, plus columnar storage extension hydra, LiDAR point cloud extension pointcloud, image similarity extension imgsmlr, extended distance function package pg_similarity, and multilingual fuzzy search extension pg_bigm.

For monitoring, Pigsty has optimized the PostgreSQL dashboard experience, added new Patroni & Exporter dashboards, and redesigned the PGSQL Query dashboard based on query macro-optimization methodology.

About Pigsty

Pigsty is an out-of-the-box PostgreSQL distribution providing a local-first open-source alternative to RDS PostgreSQL. It enables users to run better enterprise-grade PostgreSQL database services at a fraction of cloud RDS costs using pure hardware. For more information, visit https://pigsty.io.

intro

Ubuntu/Debian Support

Pigsty now supports Ubuntu and Debian operating systems (referred to as Deb support). Users have been requesting Ubuntu and Debian support since the 0.x era two years ago, so this is something that felt both important and right to do.

As a database distribution that builds on bare operating systems, supporting a new OS isn’t as simple as containerized databases just packaging an image. There’s substantial adaptation work required. The first challenge is package availability — Prometheus, for example, doesn’t have an official DEB repository, so we had to maintain our own packaging and provide a repository.

APT/YUM Repo

Pigsty-maintained APT/YUM repositories

The massive differences in package management require rewriting the entire bootstrap / local repository build logic for Deb systems. Distro FHS and convention differences need case-by-case handling. You’re not just dealing with PostgreSQL kernel and 100+ extensions — there’s also etcd, minio, redis, grafana, prometheus, haproxy, and various other components. Fortunately, Pigsty has overcome these issues, giving Ubuntu/Debian the same smooth experience as EL 7-9.

One-Click Install

One-click Pigsty installation

In terms of user experience, Deb support has an almost identical feature set to EL. The only exception is that Supabase and its specialized extensions haven’t been fully ported yet. Beyond that, Deb has some unique extensions like the chemical formula extension RDKit, LiDAR point cloud extension pointcloud, and extended distance function package pg_similarity (the latter two have been backported to EL). To fully leverage PostgresML + CUDA capabilities, Ubuntu is essential.

Pigsty’s auto-configuration now detects Debian/Ubuntu systems, automatically using the corresponding config template for single-node installations. Deb templates differ from EL in only 8 parameter defaults — package names differ between distributions, so parameters like xx_packages need adjustment. The only other changes are upstream repos repo_upstream, local repo URLs node_repo_local_urls, and default pg_dbsu_uid (DEB packages don’t assign fixed UIDs).

Ubuntu Config

Declarative config file for Ubuntu systems

Users typically don’t need to adjust these parameters, so the Pigsty workflow on Deb systems is virtually identical. In fact, Pigsty’s offline package build template works exactly this way: completing full Pigsty installations on seven different operating systems at once, without any special handling.

New Extensions

Pigsty v2.5 includes several user-requested extensions. First up is PostgresML. While the previous version already supported PostgresML on EL8/EL9, AI work is almost universally done on Ubuntu — at minimum, CUDA driver installation is much easier.

So in Pigsty v2.5, you can run native PostgresML clusters on Ubuntu. No fiddling with NVIDIA Docker or anything like that — just pip install the Python dependencies and you’re ready to go. Train models with SQL, invoke models, and complete your entire AI workflow within the database!

PostgresML

The second noteworthy extension is pointcloud. Thanks to PostGIS, PostgreSQL has always been a favorite of autonomous driving/EV companies. PointCloud extends PostgreSQL and PostGIS’s power to a new frontier. LiDAR continuously scans surroundings and generates “point cloud” data. The pointcloud extension provides PcPoint & PcPatch data types and forty functions, allowing efficient storage, retrieval, and computation on ultra-high-dimensional point sets. This extension is natively available in the PGDG APT repository, and Pigsty has ported it to EL systems so all users can benefit.

pointcloud

imgsmlr is a reverse image search extension. While many AI models can now encode images into high-dimensional vectors for semantic search using pgvector, what makes imgsmlr interesting is that it requires no external dependencies and can complete all functionality within the database. In the author’s words: “My goal isn’t to provide the most advanced image search method, but to show you how to write a PostgreSQL extension for even non-typical database tasks like image processing.”

imgsmlr

It first processes PNG/JPG images using Haar wavelet transform into 16K patterns and 64-byte signature digests, then uses GiST index retrieval on digests for efficient reverse image search. Using imgsmlr to retrieve the 10 most similar images from 400 million random images takes about 600ms.

Another interesting extension, pg_similarity, is available by default in Ubuntu/Debian APT repos, and Pigsty has ported it to EL. It provides efficient C implementations of 17 text distance metric functions, greatly enriching search and ranking capabilities. A related plugin is pg_bigm, similar to PostgreSQL’s built-in pg_trgm, except it uses bigrams instead of trigrams for fuzzy search, providing better full-text search support for CJK languages.

pg_similarity

Additionally, we’ve updated Supabase support to the latest version: 20231013070755. You can self-host Supabase on EL8/EL9 systems using Pigsty’s PostgreSQL database.

Including PostgreSQL’s built-in extensions, Pigsty 2.5 supports 150+ extensions. Despite this abundance, note that they’re all optional. Pigsty provides pg_repack, wal2json, and passwordcheck_cracklib (EL) for all PostgreSQL major versions, with only the online bloat management extension pg_repack installed by default. Other extensions, if not installed, impose no extra burden on the system.

Monitoring System Updates

Pigsty v2.5 brings monitoring system adjustments, updating the long-standing pg_exporter to v0.6.0 with TLS support, fixing two dependency security issues, building ARM64 packages, and using the latest metrics definition files. Additionally, four shared buffer I/O related metrics were added to the pg_query collector, enriching the information in PGSQL Query.

First, the new PGSQL Patroni dashboard provides a complete view of cluster HA status. Very helpful for analyzing historical service health and failover causes.

Patroni Dashboard

Then there’s PGSQL Exporter, providing detailed self-monitoring metrics and logs for PG Exporter and Pgbouncer Exporter. Useful for optimizing the monitoring system itself.

Exporter Dashboard

In component navigation panels across various dashboards, you can click Patroni/Exporter indicator tiles to jump directly to component details:

Component Navigation

The PGSQL Query dashboard now has five sections: Overview, core QPS/RT metrics, time-differential metrics, call-differential metrics, and percentage metrics. Following macro-optimization methodology:

Reduce Resource Consumption: Lower saturation risk, optimize CPU/memory/IO, typically targeting total query time/IO. Uses dM/dt: metric M differentiated over time (per-second increments).

Improve User Experience: Most common optimization goal. In OLTP, typically targets reduced average query response time. Uses dM/dc: metric M differentiated over call count (per-call increments).

Balance Workload: Ensure proper proportions of resource usage/performance across query groups. Uses M%: percentage of a query class’s metric relative to totals.

The PGSQL first screen shows the most critical query performance metrics: QPS and RT — plus their 1/5/15-minute averages, jitter, and distribution ranges.

Query QPS/RT

Next are dM/dc metrics for user experience optimization, where M includes:

  • Average rows returned per query
  • Average execution time per query
  • Average WAL size per query
  • Average I/O time per query
  • Average buffer blocks read/written per query
  • Average buffer blocks accessed/dirtied per query
Query dM/dc

Then dM/dt metrics for resource consumption reduction, with similar M metrics but differentiated over time instead of call count:

Query dM/dt

The final section shows %M metrics for workload balancing. Reveals a specific query group’s proportion and relative position in the overall workload, shown in bold. Click specific queries to navigate in-place — very convenient.

Query Percent

Beyond these three dashboards, Pigsty has optimized and fixed many other panels. Many panel info sections now provide more detail: what metrics the panel shows, what problems it solves, etc. We’ve also introduced three new Grafana plugins for CSV/JSON datasources and variable panels.

Release Notes

v2.5.0 

curl https://get.pigsty.cc/latest | bash

Highlights

  • Ubuntu / Debian support: bullseye, bookworm, jammy, focal
  • CDN repo.pigsty.cc software repository providing RPM/DEB package downloads
  • Anolis OS support (compatible with EL 8.8)
  • PostgreSQL 16 replaces PostgreSQL 14 as the alternative primary supported version
  • New PGSQL Exporter / PGSQL Patroni dashboards, redesigned PGSQL Query dashboard
  • Extension updates:
    • PostGIS upgraded to 3.4 (EL8/EL9), EL7 remains on PostGIS 3.3
    • Removed pg_embedding as developer discontinued maintenance, recommend pgvector instead
    • New extension (EL): Point cloud plugin pointcloud support, natively available on Ubuntu
    • New extensions (EL): imgsmlr, pg_similarity, pg_bigm for search
    • Recompiled pg_filedump as PG version-independent package
    • Added hydra columnar storage extension, citus no longer installed by default
  • Software updates:
    • Grafana to v10.1.5
    • Prometheus to v2.47
    • Promtail/Loki to v2.9.1
    • Node Exporter to v1.6.1
    • Bytebase to v2.10.0
    • Patroni to v3.1.2
    • pgbouncer to v1.21.0
    • pg_exporter to v0.6.0
    • pgbackrest to v2.48.0
    • pgbadger to v12.2
    • pg_graphql to v1.4.0
    • pg_net to v0.7.3
    • FerretDB to v0.12.1
    • SealOS to 4.3.5
    • Supabase support to 20231013070755

Ubuntu Support Notes

Pigsty supports Ubuntu 22.04 (jammy) and 20.04 (focal) LTS versions with corresponding offline packages.

Compared to EL systems, some parameter defaults need explicit adjustment. See ubuntu.yml for details:

  • repo_upstream: Adjusted for Ubuntu/Debian package names
  • repo_packages: Adjusted for Ubuntu/Debian package names
  • node_repo_local_urls: Defaults to ['deb [trusted=yes] http://${admin_ip}/pigsty ./']
  • node_default_packages:
    • zlib -> zlib1g, readline -> libreadline-dev
    • vim-minimal -> vim-tiny, bind-utils -> dnsutils, perf -> linux-tools-generic
    • Added acl package to ensure Ansible permissions work correctly
  • infra_packages: All packages with _ replaced by -, postgresql-client-16 replaces postgresql16
  • pg_packages: Ubuntu conventionally uses - instead of _, no need to manually install patroni-etcd
  • pg_extensions: Extension names differ from EL, Ubuntu lacks passwordcheck_cracklib
  • pg_dbsu_uid: Ubuntu DEB packages don’t specify explicit UID, manual specification required, Pigsty defaults to 543

API Changes

Default value changes:

  • repo_modules now defaults to infra,node,pgsql,redis,minio, enabling all upstream sources

  • repo_upstream changed, now adds Pigsty Infra/MinIO/Redis/PGSQL modular software sources

  • repo_packages changed, removed unused karma,mtail,dellhw_exporter, removed PG14 main extensions, added PG16 main extensions, added virtualenv package

  • node_default_packages changed, now installs python3-pip by default

  • pg_libs: timescaledb removed from shared_preload_libraries, no longer auto-enabled by default

  • pg_extensions changed, Citus no longer installed by default, passwordcheck_cracklib installed by default, EL8,9 PostGIS default version upgraded to 3.4

    - pg_repack_${pg_version}* wal2json_${pg_version}* passwordcheck_cracklib_${pg_version}*
- postgis34_${pg_version}* timescaledb-2-postgresql-${pg_version}* pgvector_${pg_version}*

  • All Patroni templates remove wal_keep_size parameter by default to avoid triggering Patroni 3.1.1 bug, functionality covered by min_wal_size

MD5 (pigsty-pkg-v2.5.0.el7.x86_64.tgz) = 87e0be2edc35b18709d7722976e305b0
MD5 (pigsty-pkg-v2.5.0.el8.x86_64.tgz) = e71304d6f53ea6c0f8e2231f238e8204
MD5 (pigsty-pkg-v2.5.0.el9.x86_64.tgz) = 39728496c134e4352436d69b02226ee8
MD5 (pigsty-pkg-v2.5.0.debian11.x86_64.tgz) = e3f548a6c7961af6107ffeee3eabc9a7
MD5 (pigsty-pkg-v2.5.0.debian12.x86_64.tgz) = 1e469cc86a19702e48d7c1a37e2f14f9
MD5 (pigsty-pkg-v2.5.0.ubuntu20.x86_64.tgz) = cc3af3b7c12f98969d3c6962f7c4bd8f
MD5 (pigsty-pkg-v2.5.0.ubuntu22.x86_64.tgz) = c5b2b1a4867eee624e57aed58ac65a80

v2.5.1

Following PostgreSQL v16.1, v15.5, 14.10, 13.13, 12.17, 11.22 routine minor version updates.

All important PostgreSQL 16 extensions are now in place (added pg_repack and timescaledb support).

  • Software updates:
    • PostgreSQL to v16.1, v15.5, 14.10, 13.13, 12.17, 11.22
    • Patroni v3.2.0
    • PgBackrest v2.49
    • Citus 12.1
    • TimescaleDB 2.13
    • Grafana v10.2.0
    • FerretDB 1.15
    • SealOS 4.3.7
    • Bytebase 2.11.1
  • Removed monitor schema prefix from PGCAT dashboard queries (allowing users to install pg_stat_statements elsewhere)
  • New wool.yml config template designed for Alibaba Cloud free 99 ECS single-node
  • Added python3-jmespath package for EL9 to fix jmespath missing after Ansible dependency update during bootstrap
MD5 (pigsty-pkg-v2.5.1.el7.x86_64.tgz) = 31ee48df1007151009c060e0edbd74de
MD5 (pigsty-pkg-v2.5.1.el8.x86_64.tgz) = a40f1b864ae8a19d9431bcd8e74fa116
MD5 (pigsty-pkg-v2.5.1.el9.x86_64.tgz) = c976cd4431fc70367124fda4e2eac0a7
MD5 (pigsty-pkg-v2.5.1.debian11.x86_64.tgz) = 7fc1b5bdd3afa267a5fc1d7cb1f3c9a7
MD5 (pigsty-pkg-v2.5.1.debian12.x86_64.tgz) = add0731dc7ed37f134d3cb5b6646624e
MD5 (pigsty-pkg-v2.5.1.ubuntu20.x86_64.tgz) = 99048d09fa75ccb8db8e22e2a3b41f28
MD5 (pigsty-pkg-v2.5.1.ubuntu22.x86_64.tgz) = 431668425f8ce19388d38e5bfa3a948c

2.3 - Pigsty v2.4: Monitor Cloud RDS

Originally published on VONNG.

GitHub Release | Release Note

PostgreSQL released its new major version 16 today, bringing a series of improvements. Pigsty followed up within 1 hour of release with Pigsty v2.4, providing complete support for PostgreSQL 16 GA. Additionally, v2.4 adds enhanced support for monitoring existing PG instances, especially RDS for PostgreSQL and PolarDB. Redis monitoring has been improved based on 7.x, with automated Sentinel-based high availability configuration.

banner

Highlights

  • PostgreSQL 16 GA released, Pigsty provides support within 1 hour of release
  • Monitor cloud databases: RDS for PostgreSQL and PolarDB, with brand-new PGRDS dashboards
  • Commercial support and consulting services officially launched. First LTS version released, providing up to 5 years of support for subscribers
  • New extension: Apache AGE — graph database query capability on PostgreSQL
  • New extension: zhparser — Chinese word segmentation for full-text search
  • New extension: pg_roaringbitmap — efficient RoaringBitmap implementation
  • New extension: pg_embedding — another HNSW-based vector database plugin, alternative to pgvector
  • New extension: pg_tle — AWS’s trusted language stored procedure management/publishing/packaging extension
  • New extension: pgsql-http — send HTTP requests and handle responses using SQL interface
  • Other new extensions: pg_auth_mon, pg_checksums, pg_failover_slots, pg_readonly, postgresql-unit, pg_store_plans, pg_uuidv7, set_user
  • Redis improvements: Sentinel monitoring support, automatic HA configuration for primary-replica clusters

API Changes

  • New parameter: REDIS.redis_sentinel_monitor — specify list of primaries monitored by Sentinel cluster

PostgreSQL 16 Support

Pigsty is probably the first distribution to provide PostgreSQL 16 support — we’ve been tracking it since 16 beta1. So when PostgreSQL 16 was released, Pigsty completed GA support within an hour. You can already spin up PostgreSQL 16 high-availability clusters, though some important extensions aren’t yet available in the official PGDG repository, such as Citus and TimescaleDB. But other extensions are ready: including PostGIS 3.4, pgvector, pg_squeeze, wal2json, pg_cron, and extensions maintained and packaged by Pigsty: zhparser, roaringbitmap, pg_embedding, pgsql-http, and more.

PostgreSQL 16 brings practical new features: logical decoding and logical replication from standbys, new I/O statistics views, parallel execution of full joins, better freezing performance, new SQL/JSON standard function set, and regular expressions in HBA authentication.

Note that the official PGDG repository has decided to drop EL7 support for PostgreSQL 16, so PG16 is only available on EL8 and EL9 and compatible OS distributions.

Monitoring RDS and PolarDB

Pigsty v2.4 provides RDS monitoring support, with particular emphasis on PolarDB cloud database monitoring. When you only have a remote PostgreSQL connection string, you can use this method to integrate it into Pigsty monitoring.

PolarDB Cluster

Example: Monitoring a primary-replica PolarDB RDS cluster

Pigsty v2.4 provides RDS monitoring support with special attention to PolarDB cloud database monitoring. When you only have a remote PostgreSQL connection string, you can bring it into Pigsty monitoring. Pigsty provides two brand-new dashboards: PGRDS Cluster and PGRDS Instance, for presenting complete RDS PG metrics.

PGRDS ClusterPGRDS Dashboard 1PGRDS Dashboard 2

Commercial Support

Pigsty v2.4 is the first LTS version, providing 3 years of long-term support for enterprise subscribers. We’re also officially launching subscription and support services — contact us if interested.

https://pigsty.io/docs/support/

Support Page

Redis High Availability

In Pigsty v2.4, we provide a new parameter redis_sentinel_monitor for automatically configuring high availability for classic Redis primary-replica clusters. This parameter can only be defined on Sentinel clusters, and primaries defined in it will be automatically managed by the Sentinel cluster.

Redis Sentinel

Meanwhile, we’ve added Sentinel-related metrics and panels to Redis monitoring, adapted for Redis 7.x’s new features.

New Extensions

Pigsty v2.4 provides a series of new extensions, including important ones not yet in the official PGDG repository. For example: graph database plugin Apache AGE, Chinese full-text search plugin zhparser, HTTP plugin pgsql-http, trusted extension packaging plugin pg_tle, bitmap plugin pg_roaringbitmap, and pg_embedding as an alternative vector database implementation to PGVector, and more.

All extensions are compiled and packaged for PostgreSQL 12 through PostgreSQL 16 on EL7 through EL9, though EL7 doesn’t yet support pg_tle and pg_embedding due to compiler version issues. These RPM packages are maintained by Pigsty and hosted in Pigsty’s own Yum repository.

Extension List

For example, you can use AGE to add graph database capabilities to PostgreSQL, create Graphs, and explore graph data using Cypher query language alongside SQL — achieving Neo4j-like functionality.

Apache AGE

Or you can use the zhparser Chinese word segmentation plugin to split Chinese text and queries into keywords, using PostgreSQL’s classic full-text search capability to achieve search engine and ElasticSearch-like functionality.

zhparser

Even more impressively, you can use the pgsql-http plugin to send HTTP requests and process HTTP responses using a SQL interface. This enables deep integration and interaction between the database and external systems, opening up endless possibilities:

pgsql-http

You can also use roaringbitmap to efficiently perform counting statistics with minimal resources:

roaringbitmap

We won’t go into all the details here — we’ll publish dedicated articles introducing how to use these powerful extensions.

v2.4.0 Release Notes

Highlights

  • PostgreSQL 16 GA released, Pigsty provides support
  • Monitor cloud databases: RDS for PostgreSQL and PolarDB with brand-new PGRDS dashboards
  • Commercial support and consulting services officially launched. First LTS version released, providing up to 5 years of support for subscribers
  • New extension: Apache AGE, openCypher graph query engine on PostgreSQL
  • New extension: zhparser, full text search for Chinese language
  • New extension: pg_roaringbitmap, roaring bitmap for PostgreSQL
  • New extension: pg_embedding, HNSW alternative to pgvector
  • New extension: pg_tle, admin/manage stored procedure extensions
  • New extension: pgsql-http, issue HTTP requests with SQL interface
  • Additional extensions: pg_auth_mon, pg_checksums, pg_failover_slots, pg_readonly, postgresql-unit, pg_store_plans, pg_uuidv7, set_user
  • Redis improvements: Sentinel monitoring support, automatic HA configuration for primary-replica clusters

API Changes

  • New parameter: REDIS.redis_sentinel_monitor — specify list of primaries monitored by Sentinel cluster

Bug Fixes

  • Fixed missing uid when registering datasources in Grafana 10.1
MD5 (pigsty-pkg-v2.4.0.el7.x86_64.tgz) = 257443e3c171439914cbfad8e9f72b17
MD5 (pigsty-pkg-v2.4.0.el8.x86_64.tgz) = 41ad8007ffbfe7d5e8ba5c4b51ff2adc
MD5 (pigsty-pkg-v2.4.0.el9.x86_64.tgz) = 9a950aed77a6df90b0265a6fa6029250

2.4 - Pigsty v2.3: Richer App Ecosystem

Originally published on VONNG.

GitHub Release | Release Note

Pigsty v2.3 is here! This release further refines the monitoring system, enriches the application ecosystem, and keeps pace with PostgreSQL’s routine minor version updates (CVE fixes).

Pigsty v2.3 follows PostgreSQL’s minor version updates including 15.4, 14.9, 13.12, 12.16, and 16 beta3, addressing a CVE security vulnerability. The HA controller Patroni is also upgraded to version 3.1, fixing several bugs.

v2.3 adds support for FerretDB — a truly open-source MongoDB alternative built on PostgreSQL. Users can access it with MongoDB clients, but all data is actually stored in the underlying PostgreSQL.

v2.3 also includes NocoDB by default: an open-source Airtable alternative. It’s a database-spreadsheet hybrid that lets you quickly build collaborative applications using a low-code approach.

Pigsty v2.3 introduces the ability to bind an L2 VIP to a host node cluster using the VRRP protocol to eliminate single points of failure across the entire chain, with full monitoring support: keepalived_exporter collects metrics, and every Node VIP (keepalived) and PGSQL VIP (vip-manager) is added to blackbox_exporter’s ICMP/PING monitoring list.

For monitoring, Pigsty v2.3 builds on v2.2’s foundation with additional polish: new VIP monitoring, VIP and node PING metrics prominently placed in NODE/PGSQL monitoring, a new lock wait tree view in PGSQL monitoring, Redis monitoring style updates, MinIO monitoring adapted to new metric names, and MySQL/MongoDB monitoring stubs laying groundwork for future implementation.

MongoDB Support?

MongoDB is a popular NoSQL document database. But due to licensing issues (SSPL) and positioning concerns (Postgres distribution), Pigsty chose to use FerretDB to provide MongoDB support. FerretDB is an interesting open-source project: it lets PostgreSQL provide MongoDB capabilities.

ferretdb

MongoDB and PostgreSQL are very different database systems: MongoDB uses a document model with its own query language. But since PostgreSQL offers complete JSON/JSONB/GIN functionality, this is theoretically entirely feasible: FerretDB translates your MongoDB queries into SQL queries:

use test                            -- CREATE SCHEMA test;
db.dropDatabase()                   -- DROP DATABASE test;
db.createCollection('posts')        -- CREATE TABLE posts(_data JSONB,...)
db.posts.insert({title: 'Post One',
  body: 'Body of post one',
  category: 'News',
  tags: ['news', 'events'],
  user: {name: 'John Doe',
         status: 'author'},
  date: Date()})                    -- INSERT INTO posts VALUES(...);
db.posts.find().limit(2).pretty()   -- SELECT * FROM posts LIMIT 2;
db.posts.createIndex({ title: 1 })  -- CREATE INDEX ON posts(_data->>'title');

Defining a FerretDB cluster in Pigsty is no different from other database types — you just need to provide core identity parameters: cluster name and instance number. The key parameter is mongo_pgurl, which specifies the underlying PostgreSQL address that FerretDB uses.

ferret:
  hosts:
    10.10.10.45: { mongo_seq: 1 }
    10.10.10.46: { mongo_seq: 2 }
    10.10.10.47: { mongo_seq: 3 }
  vars:
    mongo_cluster: ferret
    mongo_pgurl: 'postgres://test:[email protected]:5436/test'

You can directly specify any PostgreSQL service address created by Pigsty. No special database configuration is needed — just ensure the user has DDL privileges.

ferretdb-monitoring

After configuration, run ./mongo.yml -l ferret to complete installation. If you prefer containers, you can also cd pigsty/app/ferretdb; make to spin up FerretDB via docker-compose. Once installed, use any MongoDB client to access FerretDB, such as MongoSH:

mongosh 'mongodb://test:[email protected]:27017/test?authMechanism=PLAIN'

For users looking to migrate from MongoDB to PostgreSQL, this is a minimal-effort compromise solution. Pigsty also offers another approach via MongoFDW: query existing MongoDB clusters using SQL from within PostgreSQL.

New App: NocoDB

Pigsty v2.3 adds built-in support for NocoDB. Use the default Docker Compose template to spin up NocoDB with one command and use the built-in PostgreSQL for storage.

NocoDB is an open-source Airtable alternative. What’s Airtable? Think Google Docs / Google Sheets, but with extremely rich APIs and hooks that enable powerful functionality.

nocodb

NocoDB transforms any relational database into a spreadsheet, running your own local cloud document software. It also lets users implement requirements via low-code approaches: for example, you can send auto-generated forms to others for filling out, with results automatically organized into real-time shared, collaborative, programmable multi-dimensional tables.

In Pigsty, spinning up NocoDB is dead simple — just one command. Modify the DATABASE_URL parameter in .env to use different databases.

cd ~/pigsty/app/nocodb; make up

Node VIP Support

Pigsty v2.3 introduces the ability to bind an L2 VIP to a host node cluster using the VRRP protocol to eliminate single points of failure across the entire chain, with complete monitoring support.

In ancient Pigsty versions (pre-0.5), keepalived-based L2 VIP was available but was later replaced by HAProxy + VIP-Manager: HAProxy works with any network, provides flexible health checks and traffic distribution, plus a simple admin interface. VIP-Manager binds an L2 VIP to the database cluster primary.

But the general L2 VIP requirement still exists. For example, if users choose HAProxy cluster access, how do you ensure HAProxy’s own reliability? While DNS-based load balancing works, VRRP clearly wins on reliability and ease of use. MinIO, ETCD, and Prometheus sometimes have similar needs.

Binding an L2 VIP to a cluster is simple: enable vip_enabled, assign a unique VirtualRouterID and VIP address within the VLAN. By default, all cluster members use BACKUP initial state in non-preemptive mode. Set vip_role and vip_preempt to change this behavior.

vip-config

L2 VIPs are automatically monitored. When the MASTER goes down, BACKUP takes over immediately.

vip-failover

Monitoring Improvements

Pigsty v2.2 completely overhauled the monitoring system based on Grafana 10. v2.3 adds more refinements on top of v2.2.

For example, the new NODE VIP dashboard displays VIP status: owning cluster/members, network RT, keepalived state, and more.

node-vip-dashboard

The image above shows live monitoring of an L2 VIP automatic failover: bound to a 3-node MinIO cluster. When the original Master (.27) goes down, (.26) takes over immediately.

The same information appears in prominent positions on NODE and PGSQL dashboards: for example, the Overview instance list now includes VIP quick navigation (purple):

node-overviewpgsql-overview

Similarly, NODE Cluster and PGSQL Cluster prominently display VIP and all member ICMP reachability status (Ping network latency).

node-cluster-pingpgsql-cluster-ping

Additionally, PGCAT adds a default 1-second refresh PGCAT Locks dashboard for intuitive observation of current database activity and lock waits.

pgcat-locks

Lock waits are organized into a wait tree, with Level and indentation indicating hierarchy. You can select different refresh rates, up to 10 times per second.

Lock Wait Tree

For Redis monitoring, related dashboards have been unified to match PGSQL and NODE styling:

redis-monitoring

Smoother Build Process

Pigsty v2.2 introduced official Yum repos; v2.3 enables site-wide HTTPS by default.

When downloading Pigsty software directly from the internet, you might encounter firewall/GFW issues. For example, default Grafana/Prometheus Yum repos can be extremely slow. Additionally, some scattered RPM packages need web URL downloads rather than repotrack.

Pigsty v2.2 solved this with an official Yum repo at http://get.pigsty.cc, configured as a default upstream source. All scattered RPMs and packages requiring VPN access are hosted there, significantly speeding up online installation/builds.

Installation

The Pigsty v2.3 installation command is:

bash -c “$(curl -fsSL https://get.pigsty.cc/latest)"

One command for a complete Pigsty installation on a fresh machine. For beta versions, replace latest with beta. For air-gapped environments, download Pigsty and offline packages:

download
https://get.pigsty.cc/v2.3.0/pigsty-v2.3.0.tgz
https://get.pigsty.cc/v2.3.0/pigsty-pkg-v2.3.0.el7.x86_64.tgz
https://get.pigsty.cc/v2.3.0/pigsty-pkg-v2.3.0.el8.x86_64.tgz
https://get.pigsty.cc/v2.3.0/pigsty-pkg-v2.3.0.el9.x86_64.tgz

That’s what Pigsty v2.3 brings to the table.

For more details, check out the official Pigsty documentation: https://pigsty.io and GitHub Release Notes: https://github.com/pgsty/pigsty/releases/tag/v2.3.0

v2.3.0 Release Notes

Highlights

  • INFRA: Added NODE/PGSQL VIP monitoring support
  • PGSQL: Fixed PostgreSQL CVE-2023-39417 via minor upgrades: 15.4, 14.9, 13.12, 12.16, and Patroni v3.1.0
  • NODE: Allow users to bind L2 VIP to node clusters using keepalived
  • REPO: Pigsty Yum repo optimized, site-wide HTTPS by default: get.pigsty.cc and demo.pigsty.cc
  • APP: Upgraded app/bytebase to v2.6.0, app/ferretdb to v1.8; added new app template: NocoDB, open-source Airtable
  • REDIS: Upgraded to v7.2, redesigned Redis dashboards
  • MONGO: Added basic support via FerretDB 1.8
  • MYSQL: Added Prometheus/Grafana/CA stubs for future integration

API Changes

Added new parameter group NODE.NODE_VIP with 8 new parameters:

  • NODE.VIP.vip_enabled: Enable VIP on this node cluster?
  • NODE.VIP.vip_address: Node VIP address in IPv4 format, required if VIP enabled
  • NODE.VIP.vip_vrid: Required, integer 1-255, must be unique within same VLAN
  • NODE.VIP.vip_role: master/backup, defaults to backup, used as initial role
  • NODE.VIP.vip_preempt: Optional, true/false, defaults to false, enable VIP preemption
  • NODE.VIP.vip_interface: Node VIP network interface to listen on, eth0 by default
  • NODE.VIP.vip_dns_suffix: Node VIP DNS name suffix, defaults to .vip
  • NODE.VIP.vip_exporter_port: Keepalived exporter listen port, defaults to 9650
MD5 (pigsty-pkg-v2.3.0.el7.x86_64.tgz) = 81db95f1c591008725175d280ad23615
MD5 (pigsty-pkg-v2.3.0.el8.x86_64.tgz) = 6f4d169b36f6ec4aa33bfd5901c9abbe
MD5 (pigsty-pkg-v2.3.0.el9.x86_64.tgz) = 4bc9ae920e7de6dd8988ca7ee681459d

v2.3.1 Release Notes

Highlights

  • pgvector updated to 0.5 with HNSW algorithm support
  • PostgreSQL 16 RC1 support (el8/el9)
  • Added SealOS to default packages for quick Kubernetes cluster deployment

Bug Fixes

  • Fixed infra.repo.repo_pkg task: downloads could be affected by existing /www/pigsty content when repo_packages contains * wildcards
  • Changed vip_dns_suffix default from .vip to empty string, so cluster name itself becomes the default Node cluster L2 VIP
  • modprobe watchdog and chown watchdog if patroni_watchdog_mode is required
  • When pg_dbsu_sudo = limit and patroni_watchdog_mode = required, grant database dbsu sudo for:
    • /usr/bin/sudo /sbin/modprobe softdog: Ensure softdog kernel module enabled when starting Patroni service
    • /usr/bin/sudo /bin/chown {{ pg_dbsu }} /dev/watchdog: Ensure watchdog ownership correct when starting Patroni service

Documentation Updates

  • Added updated content to English documentation
  • Added simplified Chinese built-in docs, fixed Chinese docs on pigsty.cc

Software Updates

  • PostgreSQL 16 RC1 for EL8/EL9
  • PGVector 0.5.0 with HNSW index support
  • TimescaleDB 2.11.2
  • Grafana 10.1.0
  • Loki & Promtail 2.8.4
  • Redis Stack 7.2 on el7/8
  • mcli-20230829225506 / minio-20230829230735
  • FerretDB 1.9
  • SealOS 4.3.3
  • pgBadger 1.12.2
MD5 (pigsty-pkg-v2.3.1.el7.x86_64.tgz) = ce69791eb622fa87c543096cdf11f970
MD5 (pigsty-pkg-v2.3.1.el8.x86_64.tgz) = 495aba9d6d18ce1ebed6271e6c96b63a
MD5 (pigsty-pkg-v2.3.1.el9.x86_64.tgz) = 38b45582cbc337ff363144980d0d7b64

2.5 - Pigsty v2.2: Monitoring System Reborn

Originally published on VONNG.

GitHub Release | Release Note

Pigsty v2.2 is here! The world’s most powerful PostgreSQL monitoring system receives an epic upgrade — completely rebuilt on Grafana v10, pushing PG observability to a whole new level with a dramatically improved user experience. Live Demo: http://demo.pigsty.cc

This release also introduces a 42-node production simulation sandbox template, adds support for Citus 12 and PG 16 beta2, provides KVM-based Vagrant templates, establishes dedicated Pigsty Yum repos for scattered/hard-to-reach RPM packages, and adds compatibility with UOS20 (a domestic Chinese Linux distribution).

Monitoring Overhaul: Visual Design

In Pigsty v2.2, the monitoring dashboards were completely rebuilt from scratch, fully leveraging Grafana v10’s new features to deliver a fresh visualization experience.

The most obvious change is color. Pigsty v2.2 adopts a brand-new color scheme. Take the PGSQL Overview dashboard as an example — the new palette uses lower saturation, resulting in a more harmonious and aesthetically pleasing visual experience compared to the previous version.

PGSQL Overview v2.0

Pigsty v2.0 used Grafana’s default high-saturation colors

PGSQL Overview v2.2

Pigsty v2.2: Failed instances shown in black, click to jump directly to the incident scene

The v2.2 monitoring dashboards use PG Blue, Nginx Green, Redis Red, Python Yellow, and Grafana Orange as the base colors. The inspiration for this color scheme came from an article about applying the color palette from Makoto Shinkai’s “Weathering With You” to scientific paper illustrations.

color-scheme

Monitoring Overhaul: Cluster Navigation

Beyond colors, v2.2 also redesigns content organization and layout. For instance, stats tiles now replace the old table-style navigation, making problematic services immediately visible on the first screen. Click any anomalous tile to jump straight to the incident.

The traditional navigation tables still exist for when you need richer information — they’ve been moved to dedicated Instances / Members sections. Let’s look at the most commonly used PGSQL Cluster dashboard:

PGSQL Cluster Stats

The first screen shows tile-based visual navigation displaying cluster component health and service availability, core metrics, load levels, and alert events. It also provides quick navigation to cluster resources — instances, connection pools, load balancers, services, and databases.

PGSQL Cluster Table

Table-style navigation in PGSQL Cluster

The detailed cluster resource tables appear in the second section for reference. Combined with the metrics and logs sections that follow, it presents a complete picture of a PostgreSQL cluster’s core state.

PGSQL Cluster Metrics

Monitoring Overhaul: Instance View

PGSQL Instance shows detailed status for a single instance and has also been redesigned in v2.2. The fundamental design principle: only non-blue/green states need attention. Through color-coded visual encoding, users can quickly identify root causes during incident analysis.

PGSQL Instance

Other instances, host nodes, ETCD, MinIO, and Redis all use similar designs. For example, here’s the Node Instance first screen:

Node Instance

Node Instance metrics remain largely unchanged, but the overview section was redesigned. MinIO Overview follows the same pattern:

MinIO Overview

ETCD Overview uses State Timeline to visualize DCS service availability. The image below shows a simulated ETCD failure scenario: instances are shut down one by one in a 5-node ETCD cluster. The cluster tolerates two node failures, but three failures render the entire ETCD service unavailable (yellow bars turn dark blue, indicating overall ETCD service unavailability).

ETCD Overview

When DCS fails, PostgreSQL clusters relying on ETCD for high availability enable FailSafeMode by default: when all cluster members are reachable and the issue is confirmed to be DCS rather than the instance itself, it prevents unnecessary primary demotion. This status is reflected in PG monitoring:

PG FailSafe

Monitoring Overhaul: Services

Another completely redesigned area is Service and Proxy monitoring. The Service dashboard now includes critical service information: SLI. Through State Timeline bars, users can intuitively see service interruptions, obtain availability metrics, and understand the status of load balancers and backend database servers.

PGSQL Service

In this example, the four HAProxy instances for the pg-test cluster were drained, put into maintenance mode, then the backend database servers were shut down. The pg-test-replica read service only becomes unavailable when all cluster instances are offline.

HAProxy Drain

This shows the monitoring dashboard for pg-test cluster’s HAProxy #1 load balancer. Every service it handles is listed, showing backend server status and calculating SLI. HAProxy’s own status and metrics are in the Node Haproxy dashboard.

HAProxy Instance

The global overview shows the overall status timeline and SLI metrics for all database services in Pigsty.

Monitoring Overhaul: Database Statistics

Besides monitoring database servers, Pigsty also monitors the logical objects they host — databases, tables, queries, indexes, and more.

PGSQL Databases shows cluster-level database statistics. For example, the pg-test cluster has 4 database instances and one database called test. This view enables horizontal comparison of database metrics across all 4 instances.

PGSQL Databases

Users can drill down into statistics within a single database instance via the PGSQL Database dashboard. This dashboard provides key metrics about the database and connection pool, but most importantly, it indexes the most active tables and queries — the two most critical in-database objects.

PGSQL DatabasePGSQL Database TablesPGSQL Database Queries

Monitoring Overhaul: System Catalog

Beyond metrics collected by pg_exporter, Pigsty uses another type of optional supplementary data — system catalogs. This is what the PGCAT dashboard series does. PGCAT Instance directly queries database system catalogs (using at most 8 read-only monitoring connections) to retrieve and present information.

For example, you can get current database activities, locate and analyze slow queries, unused indexes, and sequential scans using various metrics. You can also examine database roles, sessions, replication status, configuration changes, memory usage details, and backup/persistence specifics.

PGCAT Instance 1PGCAT Instance 2

While PGCAT Instance focuses on the database server itself, PGCAT Database focuses on object details within a single database: schemas, tables, indexes, bloat, top SQL, top tables, and more.

PGCAT Database

Each schema, table, and index can be clicked to drill down into more detailed dedicated dashboards. For example, PGCAT Schema shows detailed objects within a schema.

PGCAT Schema

Database queries are also aggregated by execution plan, making it easy to find problematic SQL and quickly locate slow queries.

PGCAT Query

Monitoring Overhaul: Tables and Queries

In Pigsty, you can examine every aspect of a table. The PGCAT Table dashboard shows table metadata, its indexes, statistics for each column, and related queries.

PGCAT Table

You can also use the PGSQL Table dashboard to view key metrics for a table across any historical time period from a metrics perspective. Click the table name to easily switch between views.

PGSQL Table

Similarly, you can get detailed information about SQL queries (grouped by identical execution plans).

PGSQL Query 1PGSQL Query 2

Pigsty includes many more topic-specific dashboards. Due to space constraints, this covers the monitoring system overview. The best way to experience it is to visit the public Pigsty demo: http://demo.pigsty.cc and explore it yourself. While it’s just a modest 4-node environment with 1-core VMs, it’s sufficient to demonstrate Pigsty’s core monitoring capabilities.

Production Simulation Sandbox

Pigsty provides a Vagrant + VirtualBox sandbox environment that runs on your laptop/Mac. There’s a minimal 1-node version and a full 4-node version for demos and learning. Now v2.2 adds a 42-node production simulation sandbox.

All production sandbox details are described in the prod.yml config file — under 500 lines. It runs easily on a single physical server, and spinning it up is no different from the 4-node version: just make prod install.

Prod Config

Pigsty v2.2 provides libvirt-based Vagrantfile templates. Simply adjust the machine inventory in the config above, and you can create all required VMs with one command. Everything runs comfortably on a used Dell R730 (48C 256G) — which costs under $400 secondhand. Of course, you can still use Pigsty’s Terraform templates to spin up VMs on cloud providers with one click.

After installation, the environment looks like this: a two-node monitoring infrastructure with primary-standby setup, a dedicated 5-node ETCD cluster, a 3-node MinIO cluster providing object storage for PG backups, and a dedicated 2-node HAProxy cluster for unified database load balancing.

Prod Infra

On top of this, there are 3 Redis clusters and 10 PostgreSQL clusters of various configurations, including a ready-to-use 5-shard Citus 12 distributed PostgreSQL cluster.

This configuration serves as a reference for medium-to-large enterprises managing large-scale database clusters — and you can spin it up completely in half an hour on a single physical server.

Smoother Build Process

When downloading Pigsty software directly from the internet, you might encounter firewall/GFW issues. For example, the default Grafana/Prometheus Yum repos can be extremely slow. Additionally, some scattered RPM packages need to be downloaded via web URLs rather than repotrack.

Pigsty v2.2 solves this problem. Pigsty now provides an official Yum repo: http://get.pigsty.cc, configured as one of the default upstream sources. All scattered RPMs and packages requiring VPN access are hosted there, significantly speeding up online installation/build processes.

Additionally, v2.2 adds support for the domestic Chinese operating system UOS 1050e uel20, meeting special requirements for certain customers. Pigsty has recompiled PG-related RPM packages for these systems.

Installation

Starting with v2.2, the Pigsty installation command is:

bash -c “$(curl -fsSL http://get.pigsty.cc/latest)"

One command to complete a full Pigsty installation on a fresh machine. To try beta versions, replace latest with beta. For air-gapped environments without internet access, you can download Pigsty and the offline packages containing all software:

http://get.pigsty.cc/v2.2.0/pigsty-v2.2.0.tgz
http://get.pigsty.cc/v2.2.0/pigsty-pkg-v2.2.0.el7.x86_64.tgz
http://get.pigsty.cc/v2.2.0/pigsty-pkg-v2.2.0.el8.x86_64.tgz
http://get.pigsty.cc/v2.2.0/pigsty-pkg-v2.2.0.el9.x86_64.tgz

That’s what Pigsty v2.2 brings to the table.

For more details, check out the official Pigsty documentation: https://pigsty.io and the GitHub Release Notes: https://github.com/pgsty/pigsty/releases/tag/v2.2.0

v2.2.0 Release Notes

Highlights

  • Monitoring Dashboard Overhaul: https://demo.pigsty.cc
  • Vagrant Sandbox Redesign: libvirt support with new config templates
  • Pigsty EL Yum Repos: Consolidated scattered RPMs, simplified installation/build process
  • OS Compatibility: Added UOS-v20-1050e support
  • New Config Template: 42-node production simulation configuration
  • Unified official PGDG Citus packages (el7)

Software Upgrades

  • PostgreSQL 16 beta2
  • Citus 12 / PostGIS 3.3.3 / TimescaleDB 2.11.1 / PGVector 0.44
  • Patroni 3.0.4 / pgBackRest 2.47 / pgBouncer 1.20
  • Grafana 10.0.3 / Loki/Promtail/logcli 2.8.3
  • etcd 3.5.9 / HAProxy v2.8.1 / Redis v7.0.12
  • MinIO 20230711212934 / mcli 20230711233044

Bug Fixes

  • Fixed Docker group permission issue 29434bd
  • Made infra OS user group supplementary rather than primary
  • Fixed Redis Sentinel systemd service auto-enable state 5c96feb
  • Relaxed bootstrap & configure checks, especially when /etc/redhat-release doesn’t exist
  • Upgraded to Grafana 10, fixing Grafana 9.x CVE-2023-1410
  • Added PG 14-16 command tags and error codes to CMDB pglog schema

API Changes

New variable:

  • INFRA.NGINX.nginx_exporter_enabled: Users can now disable nginx_exporter by setting this parameter

Default value changes:

  • repo_modules: node,pgsql,infra : Redis now provided by pigsty-el repo, no longer needs redis module
  • repo_upstream:
    • Added pigsty-el: EL version-independent RPMs: grafana, minio, pg_exporter, etc.
    • Added pigsty-misc: EL version-specific RPMs: redis, prometheus stack, etc.
    • Removed citus: PGDG now has complete EL7-EL9 Citus 12 support
    • Removed remi: Redis now provided by pigsty-el repo
  • repo_packages: Consolidated package lists (see source for details)
  • repo_url_packages:
  • node_default_packages: Updated package list
  • infra_packages: Updated package list
  • PGSERVICE in .pigsty replaced with PGDATABASE=postgres, allowing users to access specific instances from admin node using just IP address

Directory structure changes:

  • bin/dns and bin/ssh moved to vagrant/ directory
MD5 (pigsty-pkg-v2.2.0.el7.x86_64.tgz) = 5fb6a449a234e36c0d895a35c76add3c
MD5 (pigsty-pkg-v2.2.0.el8.x86_64.tgz) = c7211730998d3b32671234e91f529fd0
MD5 (pigsty-pkg-v2.2.0.el9.x86_64.tgz) = 385432fe86ee0f8cbccbbc9454472fdd

2.6 - Pigsty v2.1: Vector + Full PG Version Support!

Originally published on VONNG.

GitHub Release | Release Note

Following PostgreSQL’s summer minor version updates and the release of PG 16 Beta, Pigsty closely tracks the PG community with the v2.1 release. This update supports PostgreSQL 16 Beta1 high availability and new monitoring metrics, while also providing support for PG 12-15. Meanwhile, the AI vector extension PGVector officially joined Pigsty in v2.0.2 and is now enabled by default.

Vector Database Extension: PGVector

Vector databases have been extremely hot lately. There are many specialized vector database products on the market — commercial ones like Pinecone and Zilliz, and open-source ones like Milvus and Qdrant. Among all existing vector databases, pgvector is unique — it chose to build on the world’s most powerful open-source relational database PostgreSQL as an extension, rather than starting from scratch as another specialized “database”. After all, building a good TP database from zero is very difficult.

pgvector has an elegant, simple, and easy-to-use interface, respectable performance, and inherits PostgreSQL’s ecosystem superpowers. Previously, PGVECTOR required manual download, compilation, and installation, so I submitted an issue to get it added to the PostgreSQL Global Development Group’s official repository. Now you can simply use the PGDG repo and run yum install pgvector_15 to complete installation. In database instances with pgvector installed, just use CREATE EXTENSION vector to enable it.

But with Pigsty, you don’t even need this process. In Pigsty v2.0.2 released in late March, PGVector extension was already integrated and installed by default. You just need CREATE EXTENSION vector and it’s ready to use.

We’re also working on a better PGVector implementation with improved functionality, performance, and usability — stay tuned for future versions.

pgvector

PG16 Support and Observability

Pigsty is perhaps the fastest distribution to provide PostgreSQL 16 support — although still in Beta, with some extensions yet to catch up, you can already spin up PostgreSQL 16 high-availability clusters for testing. PostgreSQL 16 has some practical new features: logical decoding and logical replication from standbys, new statistics views for I/O, parallel execution of full joins, better freezing performance, new SQL/JSON standard function set, and regular expressions in HBA authentication.

Pigsty pays special attention to PostgreSQL 16’s observability improvements. The new pg_stat_io view lets users access important I/O statistics directly from within the database — extremely significant for performance optimization and failure analysis. Previously, users could only see limited statistics at the database/BGWriter level; for finer statistics, they had to correlate with OS-level I/O metrics. Now you can deeply analyze reads/writes/extends/fsyncs/hits/evictions across three dimensions: backend process type, relation type, and operation type.

pg-stat-io

Another valuable observability improvement: pg_stat_all_tables and pg_stat_all_indexes now record the timestamp of the last sequential scan / index scan. While Pigsty’s monitoring system could achieve this through scan statistics charts, official direct support is certainly better: users can intuitively draw conclusions like whether an index is unused and can be removed. Additionally, the n_tup_newpage_upd metric tells us how many rows on a table were moved to a new page during updates rather than updated in-place — this metric is valuable for optimizing UPDATE performance and adjusting table fill factor.

PGSQL 12-15 Support

Pigsty has supported PostgreSQL since version 10, always closely following the community’s latest major versions. But users do have needs for older versions — some external components only support up to a certain version, some are cautious about upgrading to the latest major version, and some want to create a Pigsty-managed Standby Cluster from existing lower-version clusters for migration. Regardless, support for lower PostgreSQL versions is a real user demand. So in v2.1, we added support for PG 12-14, all included in the offline packages by default.

Each major version includes not just core packages, but also important extensions for that version: geospatial extension PostGIS, time-series database extension TimescaleDB, distributed database extension citus, vector database extension PGVector, online garbage collection extension pg_repack, CDC logical decoding extensions wal2json and pglogical, scheduled task extension pg_cron, and password strength checking extension passwordcheck_cracklib — ensuring each major version enjoys PostgreSQL ecosystem’s core capabilities.

PostgreSQL 11 can actually be supported too, but due to some missing extensions and its upcoming EOL, it was excluded from this update. For users new to PostgreSQL, we always recommend starting with the latest stable major version (currently 15). If you really need versions 10 or 11, you can follow the tutorial to adjust package versions in the repository and build yourself.

Grafana Monitoring System Improvements

With Grafana upgraded to v9.5.3, the new navigation bar and panel layout give Pigsty’s monitoring system UI a fresh look. All monitoring dashboards have been fine-tuned and adapted for the new UI, and some inconsistent styling issues have been fixed.

grafana-ui

Pigsty 2.1 introduces 4 Grafana extension plugins from volkovlabs. Using Grafana + Echarts for data visualization and analysis has always been a feature highlight that Pigsty advocates and supports, but limited author bandwidth made it difficult to invest resources in this direction.

Before v2.1’s release, I was happy to see a professionally maintained Apache Echarts panel plugin — finally I can breathe easy and retire my self-maintained echarts panel. A professional startup team chose to expand in this direction, developing a series of useful extension plugins: Dynamic Text plugin for rendering SVG and text from backend data, Form plugin for form submissions, dynamic data calendar plugin, and more.

volkovlabs

Additionally, Pigsty specifically added echarts-gl extension resources in Grafana’s public/chart directory, allowing users to create cool 3D globe panels like those in Apache Echarts’ official gallery using Pigsty’s built-in Grafana without internet access.

Other Utility Improvements

Pigsty 2.1 adds 3 convenience commands: profile, validate, and repo-add.

The bin/validate command accepts a config file path as input, checking and validating Pigsty configuration file correctness. Common issues like accidentally writing the same IP in different clusters, configuration name/type errors, and the most common YAML indentation format errors can all be automatically detected and reported. After modifying configuration, users can use bin/validate to ensure their changes are valid.

The bin/repo-add command is for manually adjusting YUM repos on nodes. When users want to add new packages to local repos, they often need to use Ansible playbook subtasks, which is inconvenient. Now you can use the wrapped command-line tool: for example, bin/repo-add infra node,pgsql will add repos categorized as node and pgsql to nodes in the infra group.

The bin/profile command conveniently performs perf sampling for 1 minute on a process with a specific PID at a given IP address, generating a flame graph in Pigsty’s web server directory. Users can open and browse it directly from the web interface — this feature is especially useful for analyzing internal database failures and performance bottlenecks.

v2.1.0 Release Notes

Highlights

  • PostgreSQL 16 beta support, plus support for versions 12-15
  • Added PGVector extension support for PG 12-15 for storing AI embeddings
  • Added 6 additional default extension panel/datasource plugins for Grafana
  • Added bin/profile script for remote profiling and flame graph generation
  • Added bin/validate for validating pigsty.yml configuration file correctness
  • Added bin/repo-add for quickly adding Yum repo definitions to nodes
  • PostgreSQL 16 observability: added pg_stat_io support and related monitoring dashboards

Software Upgrades

  • PostgreSQL 15.3, 14.8, 13.11, 12.15, 11.20, and 16 beta1
  • pgBackRest 2.46 / pgbouncer 1.19
  • Redis 7.0.11
  • Grafana v9.5.3
  • Loki / Promtail / Logcli 2.8.2
  • Prometheus 2.44
  • TimescaleDB 2.11.0
  • minio-20230518000536 / mcli-20230518165900
  • Bytebase v2.2.0

Improvements

  • When adding local user public keys, all id*.pub files are now added to remote machines (e.g., keys generated with elliptic curve algorithms)

2.7 - Pigsty v2.0: Open-Source RDS PostgreSQL Alternative

Originally published on VONNG.

GitHub Release | Release Note

2023/02/28, Pigsty v2.0.0 is officially released, bringing a series of major feature updates.

PIGSTY now stands for “PostgreSQL In Great STYle” — PostgreSQL at its best. Pigsty’s positioning has also evolved from “batteries-included PostgreSQL distribution” to “Me Better Open-Source RDS PG Alternative”.

No beating around the bush — this is an ambitious goal: overthrow cloud database monopolies and disrupt RDS!

intro

2.0 New Features

Pigsty is a better, local-first, open-source RDS for PostgreSQL alternative.

features

Powerful Distribution

Unleash the full power of the world’s most advanced relational database!

PostgreSQL is a near-perfect database kernel, but it needs more tools and systems to become a good enough database service (RDS) — Pigsty helps PostgreSQL make this leap.

Pigsty deeply integrates PostgreSQL ecosystem’s three core extensions: PostGIS, TimescaleDB, and Citus, ensuring they work together to provide distributed geospatial time-series database capabilities. Pigsty also provides software needed to run enterprise-grade RDS services, packaging all dependencies into offline bundles. All components can be installed and deployed with one click without internet access, ready for production.

In Pigsty, functional components are abstracted into modules that can be freely combined for various scenarios. The INFRA module comes with a complete modern monitoring stack, while the NODE module tunes nodes to specified states and integrates them into monitoring. Installing the PGSQL module on multiple nodes automatically forms a high-availability database cluster based on primary-replica replication, and the ETCD module provides consensus and metadata storage for database HA. The optional MINIO module can serve as storage for large files like images and videos, or as a database backup repository. REDIS, which pairs excellently with PG, is also supported. More modules (like GPSQL, MYSQL, KAFKA) will be added later, and you can develop your own modules to extend Pigsty’s capabilities.

modules

Stunning Observability

Unparalleled monitoring best practices using modern open-source observability stack!

Pigsty provides monitoring best practices based on the open-source Grafana/Prometheus observability stack: Prometheus for metric collection, Grafana for visualization, Loki for log collection and querying, Alertmanager for alert notifications. PushGateway for batch job monitoring, Blackbox Exporter for service availability checks. The entire system is designed as a one-click, out-of-the-box INFRA module.

Any component managed by Pigsty is automatically integrated into monitoring, including host nodes, HAProxy load balancers, Postgres databases, Pgbouncer connection pools, ETCD metadata stores, Redis KV caches, MinIO object storage, and the entire monitoring infrastructure itself. Numerous Grafana dashboards and preset alert rules will qualitatively enhance your system observability. This system can also be reused for your application monitoring infrastructure, or to monitor existing database instances or RDS.

Whether for failure analysis or slow query optimization, capacity assessment or resource planning, Pigsty provides comprehensive data support for truly data-driven operations. In Pigsty, over three thousand metric types describe every aspect of the system, further processed, aggregated, analyzed, refined, and presented in intuitive visualizations. From global fleet overviews to CRUD details of individual objects (tables, indexes, functions) in a database instance — everything is visible. You can drill down, roll up, and navigate horizontally, browsing system current state and historical trends while predicting future evolution. See the public demo: http://demo.pigsty.cc.

observability

Battle-Tested Reliability

Out-of-the-box high availability and point-in-time recovery ensure your database is rock solid!

For table/database drops caused by software defects or human error, Pigsty provides out-of-the-box PITR point-in-time recovery, enabled by default without additional configuration. As long as storage is sufficient, base backups and WAL archiving powered by pgBackRest give you the ability to quickly return to any point in time. You can use local directories/disks, dedicated MinIO clusters, or S3 object storage for longer retention periods — your choice.

More importantly, Pigsty makes high availability and self-healing standard for PostgreSQL clusters. The self-healing architecture built on patroni, etcd, and haproxy handles hardware failures with ease: RTO < 30s for automatic primary failover, RPO = 0 in consistency-priority mode ensuring zero data loss. As long as any instance in the cluster survives, the cluster can provide full service, and clients connecting to any node in the cluster get complete service.

Pigsty includes HAProxy load balancer for automatic traffic switching, offering DNS/VIP/LVS and other access methods for clients. Failover and planned switchover are nearly imperceptible to applications except for brief blips — no need to modify connection strings and restart. Minimal maintenance windows bring great flexibility: you can perform rolling maintenance and upgrades without application coordination. Hardware failures can wait until the next day for leisurely handling — letting developers, ops, and DBAs sleep peacefully. Many large organizations and core institutions have been using Pigsty in production for extended periods. The largest deployment has 25K CPU cores and 200+ PostgreSQL instances. In this case, Pigsty experienced dozens of hardware failures and incidents over three years while maintaining 99.999%+ overall availability.

reliability

Simple and Maintainable

Infra as Code — declarative APIs encapsulate database management complexity.

Pigsty uses declarative interfaces, elevating system controllability to a new level: users tell Pigsty “what kind of database cluster I want” via configuration inventory, without worrying about how to do it. In effect, this is similar to K8S CRDs and Operators, but Pigsty works on any node’s database and infrastructure — containers, VMs, or bare metal.

Whether creating/destroying clusters, adding/removing replicas, or provisioning databases/users/services/extensions/ACL rules, you just modify the configuration inventory and run Pigsty’s idempotent playbooks — Pigsty adjusts the system to your desired state. Users don’t worry about configuration details; Pigsty automatically tunes based on machine hardware. You only need to focus on basics like cluster name, which instances go on which machines, which template to use (transaction/analytics/critical/tiny) — developers can self-service. But if you want to dive deeper, Pigsty provides rich, fine-grained control parameters to satisfy the pickiest DBA’s customization needs.

Additionally, Pigsty installation itself is one-click simple, with all dependencies pre-packaged for offline installation without internet access. Machine resources for installation can be automatically provisioned via Vagrant or Terraform templates, letting you spin up a complete Pigsty deployment on your local laptop or cloud VMs in ten-ish minutes. The local sandbox can run on 1-core 2GB micro VMs, providing identical functionality to production for development, testing, demos, and learning.

maintainability

Solid Security

Encryption and backup all in one — as long as hardware and keys are secure, you don’t need to worry about database security.

Each Pigsty deployment creates a self-signed CA for certificate issuance. All network communication can use SSL encryption. Database passwords are encrypted with compliant scram-sha-256 algorithm, remote backups use AES-256 encryption. Additionally, an out-of-the-box access control system for PGSQL addresses security needs for most scenarios.

Pigsty provides an out-of-the-box, easy-to-use, refined and flexible, easily extensible access control system for PostgreSQL, including four default roles with separation of duties: read (DQL) / write (DML) / admin (DDL) / offline (ETL), and four default users: dbsu / replicator / monitor / admin. All database templates have sensible default permissions for these roles and users, and any new database objects automatically follow this permission system. Client access is restricted by HBA rule groups designed on the principle of least privilege, with all sensitive operations logged for audit.

All network communication can use SSL encryption. Sensitive management pages and API endpoints are protected by multiple layers: username/password authentication, access restricted to management node/infrastructure node IPs/subnets, HTTPS required for network traffic. Patroni API and Pgbouncer have SSL disabled by default for performance reasons, but security switches are available when needed. Properly configured systems pass security certifications easily. With internal network deployment, properly configured security groups and firewalls, database security will no longer be your pain point.

security

Broad Application Scenarios

One-click launch massive software using PostgreSQL with preset Docker templates!

In data-intensive applications, databases are often the trickiest part. For example, the core difference between GitLab Enterprise and Community editions is the underlying PostgreSQL database monitoring and HA. If you already have a good enough local PG RDS, why pay for software’s homegrown database?

Pigsty provides the Docker module with many out-of-the-box Compose templates. You can use Pigsty-managed HA PostgreSQL (plus Redis and MinIO) as backend storage, launching these applications statelessly with one click: Gitlab, Gitea, Wiki.js, Odoo, Jira, Confluence, Habour, Mastodon, Discourse, KeyCloak, etc. If your application needs a reliable PostgreSQL database, Pigsty might be the simplest way to get one.

Pigsty also provides development toolkits tightly integrated with PostgreSQL: PGAdmin4, PGWeb, ByteBase, PostgREST, Kong, plus “upper-layer databases” using PostgreSQL as storage like EdgeDB, FerretDB, and Supabase. Even better, you can build interactive data applications using Pigsty’s built-in Grafana and Postgres in a low-code way, and even create more expressive interactive visualizations with Pigsty’s built-in ECharts panel.

applications

Open-Source Free Software

Pigsty is free software under AGPLv3, nurtured by community members who love PostgreSQL

Pigsty is completely open-source and free, allowing you to run enterprise-grade PostgreSQL database services at nearly bare-metal hardware costs without database experts. By comparison, public cloud vendors charge premiums of several to over ten times the underlying hardware resources as “service fees” for RDS.

Many users choose cloud because they can’t handle databases themselves; many use RDS because there’s no alternative. We will break cloud vendor monopolies, providing users with a cloud-neutral, better open-source RDS alternative: Pigsty closely follows the PostgreSQL upstream trunk, with no vendor lock-in, no annoying “license fees”, no node limits, and no data collection. All your core assets — data — remain “autonomous and controllable” in your own hands.

Pigsty aims to replace tedious manual database ops with database autopilot software, but no software can solve every problem. There will always be some rare edge cases requiring expert intervention. This is why we offer professional subscription services for enterprise users needing PostgreSQL support. A few thousand dollars in subscription/consulting fees is a tiny fraction of a top DBA’s annual salary, giving you complete peace of mind and putting costs where they matter. For community users, we also provide free support and daily Q&A.

opensource

2.0 Quick Start

Pigsty 2.0 installation is still one command:

curl -fsSL http://download.pigsty.cc/get | bash
install

For limited internet access, you can download the offline package for your OS from GitHub or CDN in advance. The monitoring system has a public demo: http://demo.pigsty.cc.

demo

v2.0.0 Release Notes

Highlights

  • Perfect integration of PostgreSQL 15, PostGIS 3.3, Citus 11.2, TimescaleDB 2.10 — distributed geospatial time-series hyper-converged database
  • Major OS compatibility improvements: supports EL7, 8, 9, plus RHEL, CentOS, Rocky, OracleLinux, AlmaLinux compatible distros
  • Security improvements: self-signed CA, global SSL network encryption, scram-sha-256 password auth, AES-encrypted backups, redesigned HBA rule system
  • Patroni upgraded to 3.0, providing native HA Citus distributed cluster support, FailSafe mode enabled by default — no fear of DCS failures causing global primary outages
  • Out-of-the-box PITR support based on pgBackRest, default support for local filesystem and dedicated MinIO/S3 cluster backups
  • New ETCD module: independently deployable, easy scaling, built-in monitoring and HA, completely replacing Consul as DCS for HA PG
  • New MINIO module: independently deployable, multi-disk multi-node support, S3 local replacement, also for centralized PostgreSQL backup repository
  • Significantly simplified configuration parameters, usable without defaults; templates auto-adjust host and PG parameters based on machine specs, HBA/service definitions more concise and universal
  • License changed from Apache License 2.0 to AGPL 3.0 due to Grafana and MinIO dependencies

Compatibility

  • Supports EL7, EL8, EL9 major versions with corresponding offline packages, default dev/test environment upgraded from EL7 to EL9
  • Supports more EL-compatible Linux distros: RHEL, CentOS, RockyLinux, AlmaLinux, OracleLinux, etc.
  • Source and offline package naming conventions changed — version, OS version, and architecture now reflected in package names
  • PGSQL: PostgreSQL 15.2, PostGIS 3.3, Citus 11.2, TimescaleDB 2.10 now work together harmoniously
  • PGSQL: Patroni upgraded to 3.0 as PGSQL HA component
    • ETCD now default DCS, replacing Consul, eliminating one Consul Agent failure point
    • vip-manager upgraded to 2.1 using ETCDv3 API, completely deprecating ETCDv2 API; same for Patroni
    • Native HA Citus distributed cluster support using fully open-source Citus 11.2
    • FailSafe mode enabled by default — no fear of DCS failures causing global primary outages
  • PGSQL: pgBackrest v2.44 introduced for out-of-the-box PostgreSQL PITR
    • Default backup repo on primary’s backup directory, rolling two-day recovery window
    • Default alternative repo is dedicated MinIO/S3 cluster, rolling two-week recovery window; local use requires enabling MinIO module
  • ETCD now an independently deployed module with complete scale-out/in solution and monitoring
  • MINIO now an independently deployed module, multi-disk multi-node support, S3 local replacement, also for centralized backup repository
  • NODE module now includes haproxy, docker, node_exporter, promtail components
    • chronyd now replaces ntpd as default NTP service on all nodes
    • HAPROXY now part of NODE rather than PGSQL-exclusive, can expose services via NodePort
    • PGSQL module can now use dedicated centralized HAPROXY cluster for unified external service
  • INFRA module now includes dnsmasq, nginx, prometheus, grafana, loki components
    • DNSMASQ server in Infra module enabled by default, added as default DNS server for all nodes
    • Added blackbox_exporter for host PING probing, pushgateway for batch job metrics
    • loki and promtail now use Grafana’s default packages with official Grafana Echarts panel plugin
    • Monitoring support for PostgreSQL 15’s new observability points, added Patroni monitoring
  • Software version upgrades
    • PostgreSQL 15.2 / PostGIS 3.3 / TimescaleDB 2.10 / Citus 11.2
    • Patroni 3.0 / Pgbouncer 1.18 / pgBackRest 2.44 / vip-manager 2.1
    • HAProxy 2.7 / Etcd 3.5 / MinIO 20230131022419 / mcli 20230128202938
    • Prometheus 2.42 / Grafana 9.3 / Loki & Promtail 2.7 / Node Exporter 1.5

Security

  • Complete local self-signed CA: pigsty-ca for issuing internal component certificates
  • User creation/password changes no longer leave traces in log files
  • Nginx enables SSL support by default (for HTTPS, trust pigsty-ca in your system or use Chrome thisisunsafe)
  • ETCD fully enables SSL encryption for client and peer communication
  • PostgreSQL SSL support added and enabled by default, management connections use SSL
  • Pgbouncer SSL support added, disabled by default for performance
  • Patroni SSL support added, management API restricted to local and admin node access with password auth
  • PostgreSQL default password auth changed from md5 to scram-sha-256
  • Pgbouncer auth query support added for dynamic connection pool user management
  • pgBackRest uses AES-256-CBC encryption by default for remote centralized backup storage
  • High-security template provided: enforces global SSL and requires admin certificate login
  • All default HBA rules now explicitly defined in config files

Maintainability

  • Existing config templates auto-adjust optimizations based on machine specs (CPU/memory/storage)
  • Postgres/Pgbouncer/Patroni/pgBackRest log directories now dynamically configurable: default /pg/log/<type>/
  • Original IP placeholder 10.10.10.10 replaced with dedicated variable ${admin_ip}, referenceable in multiple places for switching backup admin nodes
  • region can be specified to use upstream mirrors from different regions for faster package downloads
  • Finer-grained upstream source addresses now allowed based on EL version, architecture, and region
  • Terraform templates for Alibaba Cloud and AWS China provided for one-click EC2 VM provisioning
  • Multiple Vagrant sandbox templates provided: meta, full, el7/8/9, minio, build, citus
  • New dedicated playbook: pgsql-monitor.yml for monitoring existing Postgres instances or RDS
  • New dedicated playbook: pgsql-migration.yml for seamless logical replication migration to Pigsty-managed clusters
  • Series of dedicated shell utilities added, wrapping common ops operations
  • All Ansible roles optimized for simplicity, readability, and maintainability — usable without default parameters
  • Additional Pgbouncer parameters can be defined at business database/user level

API Changes

Pigsty v2.0 has extensive changes: 64 new parameters, 13 removed, 17 renamed.

New Parameters

  • INFRA.META.admin_ip: Primary meta node IP address
  • INFRA.META.region: Upstream mirror region: default|china|europe
  • INFRA.META.os_version: Enterprise Linux version: 7,8,9
  • INFRA.CA.ca_cn: CA Common Name, default pigsty-ca
  • INFRA.CA.cert_validity: Certificate validity, default 20 years
  • INFRA.REPO.repo_enabled: Build local yum repo on infra node?
  • INFRA.REPO.repo_upstream: Upstream yum repo definition list
  • INFRA.REPO.repo_home: Local yum repo home directory, usually same as nginx_home ‘/www’
  • INFRA.NGINX.nginx_ssl_port: HTTPS listen port
  • INFRA.NGINX.nginx_ssl_enabled: Enable nginx HTTPS?
  • INFRA.PROMETHEUS.alertmanager_endpoint: Alertmanager endpoint (ip|domain):port format
  • NODE.NODE_TUNE.node_hugepage_ratio: Memory hugepage ratio, default 0 (disabled)
  • NODE.HAPROXY.haproxy_service: List of haproxy services to expose
  • PGSQL.PG_ID.pg_mode: pgsql cluster mode: pgsql,citus,gpsql
  • PGSQL.PG_BUSINESS.pg_dbsu_password: dbsu password, empty string means no dbsu password
  • PGSQL.PG_INSTALL.pg_log_dir: postgres log directory, default /pg/data/log
  • PGSQL.PG_BOOTSTRAP.pg_storage_type: SSD|HDD, default SSD
  • PGSQL.PG_BOOTSTRAP.patroni_log_dir: patroni log directory, default /pg/log
  • PGSQL.PG_BOOTSTRAP.patroni_ssl_enabled: Use SSL for patroni RestAPI?
  • PGSQL.PG_BOOTSTRAP.patroni_username: patroni rest api username
  • PGSQL.PG_BOOTSTRAP.patroni_password: patroni rest api password (important: change this)
  • PGSQL.PG_BOOTSTRAP.patroni_citus_db: Citus database managed by patroni, default postgres
  • PGSQL.PG_BOOTSTRAP.pg_max_conn: postgres max connections, auto uses recommended value
  • PGSQL.PG_BOOTSTRAP.pg_shmem_ratio: postgres shared memory ratio, default 0.25, range 0.1~0.4
  • PGSQL.PG_BOOTSTRAP.pg_rto: Recovery Time Objective, failover ttl, default 30s
  • PGSQL.PG_BOOTSTRAP.pg_rpo: Recovery Point Objective, max 1MB data loss by default
  • PGSQL.PG_BOOTSTRAP.pg_pwd_enc: Password encryption algorithm: md5|scram-sha-256
  • PGSQL.PG_BOOTSTRAP.pgbouncer_log_dir: pgbouncer log directory, default /var/log/pgbouncer
  • PGSQL.PG_BOOTSTRAP.pgbouncer_auth_query: If enabled, query pg_authid for biz users instead of populating user list
  • PGSQL.PG_BOOTSTRAP.pgbouncer_sslmode: pgbouncer client SSL: disable|allow|prefer|require|verify-ca|verify-full
  • PGSQL.PG_BOOTSTRAP.pg_service_provider: Dedicated haproxy node group name, or empty for local node
  • PGSQL.PG_BOOTSTRAP.pg_default_service_dest: Default service destination if svc.dest=‘default’
  • PGSQL.PG_BACKUP.pgbackrest_enabled: Enable pgbackrest?
  • PGSQL.PG_BACKUP.pgbackrest_clean: Remove pgbackrest data during init?
  • PGSQL.PG_BACKUP.pgbackrest_log_dir: pgbackrest log directory, default /pg/log
  • PGSQL.PG_BACKUP.pgbackrest_method: pgbackrest backup repo method: local or minio
  • PGSQL.PG_BACKUP.pgbackrest_repo: pgbackrest backup repo config
  • PGSQL.PG_DNS.pg_dns_suffix: pgsql dns suffix, default empty
  • PGSQL.PG_DNS.pg_dns_target: auto, primary, vip, none, or ad hoc ip
  • ETCD.etcd_seq: etcd instance identifier, required
  • ETCD.etcd_cluster: etcd cluster and group name, default etcd
  • ETCD.etcd_safeguard: Prevent purging running etcd instances?
  • ETCD.etcd_clean: Clean existing etcd during init?
  • ETCD.etcd_data: etcd data directory, default /data/etcd
  • ETCD.etcd_port: etcd client port, default 2379
  • ETCD.etcd_peer_port: etcd peer port, default 2380
  • ETCD.etcd_init: etcd initial cluster state: new or existing
  • ETCD.etcd_election_timeout: etcd election timeout, default 1000ms
  • ETCD.etcd_heartbeat_interval: etcd heartbeat interval, default 100ms
  • MINIO.minio_seq: minio instance identifier, required
  • MINIO.minio_cluster: minio cluster name, default minio
  • MINIO.minio_clean: Clean minio during init? default false
  • MINIO.minio_user: minio OS user, default minio
  • MINIO.minio_node: minio node name pattern
  • MINIO.minio_data: minio data directory, use {x…y} for multiple drives
  • MINIO.minio_domain: minio external domain, default sss.pigsty
  • MINIO.minio_port: minio service port, default 9000
  • MINIO.minio_admin_port: minio console port, default 9001
  • MINIO.minio_access_key: root access key, default minioadmin
  • MINIO.minio_secret_key: root secret key, default minioadmin
  • MINIO.minio_extra_vars: extra environment variables for minio server
  • MINIO.minio_alias: alias for local minio deployment
  • MINIO.minio_buckets: list of minio buckets to create
  • MINIO.minio_users: list of minio users to create

Removed Parameters

  • INFRA.CA.ca_homedir: CA home directory, now fixed to /etc/pki/
  • INFRA.CA.ca_cert: CA certificate filename, now fixed to ca.key
  • INFRA.CA.ca_key: CA key filename, now fixed to ca.key
  • INFRA.REPO.repo_upstreams: Replaced by repo_upstream
  • PGSQL.PG_INSTALL.pgdg_repo: Now handled by node playbooks
  • PGSQL.PG_INSTALL.pg_add_repo: Now handled by node playbooks
  • PGSQL.PG_IDENTITY.pg_backup: Unused and conflicted with partial names
  • PGSQL.PG_IDENTITY.pg_preflight_skip: No longer used, replaced by pg_id
  • DCS.dcs_name: Removed due to etcd usage
  • DCS.dcs_servers: Replaced by ad hoc group etcd
  • DCS.dcs_registry: Removed due to etcd usage
  • DCS.dcs_safeguard: Replaced by etcd_safeguard
  • DCS.dcs_clean: Replaced by etcd_clean

Renamed Parameters

  • nginx_upstream -> infra_portal
  • repo_address -> repo_endpoint
  • pg_hostname -> node_id_from_pg
  • pg_sindex -> pg_group
  • pg_services -> pg_default_services
  • pg_services_extra -> pg_services
  • pg_hba_rules_extra -> pg_hba_rules
  • pg_hba_rules -> pg_default_hba_rules
  • pgbouncer_hba_rules_extra -> pgb_hba_rules
  • pgbouncer_hba_rules -> pgb_default_hba_rules
  • vip_mode -> pg_vip_enabled
  • vip_address -> pg_vip_address
  • vip_interface -> pg_vip_interface
  • node_packages_default -> node_default_packages
  • node_packages_meta -> infra_packages
  • node_packages_meta_pip -> infra_packages_pip
  • node_data_dir -> node_data

Special thanks to Italian user @alemacci for contributions on SSL encryption, backup, multi-OS distro adaptation, and adaptive parameter templates!

v2.0.1 Release Notes

Security improvements and bug fixes for v2.0.0.

Improvements

  • New pig logo to comply with PostgreSQL trademark policy
  • Grafana upgraded to v9.4 with better UI and bug fixes
  • Patroni upgraded to v3.0.1 with bug fixes
  • Grafana systemd service file reverted to rpm default
  • Use slower copy instead of rsync for Grafana dashboard sync, more reliable
  • Bootstrap now restores default repo files after execution
  • Added asciinema videos for various admin tasks
  • Security enhancement mode: restricted monitoring user permissions
  • New config template: dual.yml for two-node deployment
  • Enable log_connections and log_disconnections in crit.yml template
  • Enable $lib/passwordcheck in pg_libs in crit.yml template
  • Explicitly grant pg_monitor role monitoring view permissions
  • Remove default dbrole_readonly from dbuser_monitor to restrict monitoring user permissions
  • Patroni now listens on {{ inventory_hostname }} instead of 0.0.0.0
  • pg_listen now controls postgres/pgbouncer listen address
  • ${ip}, ${lo}, ${vip} placeholders now available in pg_listen
  • Aliyun terraform image upgraded from centos 7.9 to Rocky Linux 9
  • Bytebase upgraded to v1.14.0

Bug Fixes

  • Added missing advertise address for alertmanager
  • Fixed missing pg_mode variable when creating database users with bin/pgsql-user
  • Added -a password option for Redis cluster join task in redis.yml
  • Added missing default value in infra-rm.yml.remove infra data task
  • Fixed prometheus monitoring target definition file owner to prometheus user
  • Use admin user instead of root to delete DCS metadata
  • Fixed issue caused by Grafana 9.4 bug: missing Meta datasource

v2.0.2 Release Notes

Highlights

Use out-of-the-box pgvector to store AI Embeddings, index, and retrieve vectors.

Changes

  • New extension pgvector for storing AI embeddings and vector similarity search
  • Fixed MinIO CVE-2023-28432, using new policy API from 20230324
  • Added dynamic reload command for DNSMASQ systemd service
  • Updated PEV version to v1.8
  • Updated Grafana version to v9.4.7
  • Updated MinIO and MCLI versions to 20230324
  • Updated Bytebase version to v1.15.0
  • Updated monitoring dashboards and fixed dead links
  • Updated Aliyun Terraform template, default to RockyLinux 9
  • Using Grafana v9.4 Provisioning API
  • Added asciinema videos for many admin tasks
  • Fixed EL8 PostgreSQL broken dependencies: removed anonymizer_15 faker_15 pgloader
MD5 (pigsty-pkg-v2.0.2.el7.x86_64.tgz) = d46440a115d741386d29d6de646acfe2
MD5 (pigsty-pkg-v2.0.2.el8.x86_64.tgz) = 5fa268b5545ac96b40c444210157e1e1
MD5 (pigsty-pkg-v2.0.2.el9.x86_64.tgz) = c8b113d57c769ee86a22579fc98e8345

2.8 - Pigsty v1.5: Docker Application Support, Infrastructure Self-Monitoring

Originally published on VONNG.

GitHub Release | Release Note

Pigsty v1.5 is officially released! Complete Docker support brings a rich application ecosystem — countless database-backed software works out of the box!

Other improvements include: infrastructure self-monitoring, better cold backup support, new CMDB compatible with Redis and Greenplum, ETCD as high-availability DCS, and better log collection and visualization. GitHub Stars crossed 500!

Highlights

Feature Description
Docker Support Enabled by default on meta node, with rich out-of-the-box software templates
Infra Self-Monitoring Nginx, ETCD, Consul, Prometheus, Grafana, Loki
CMDB Upgrade Supports Redis/Greenplum cluster metadata, configuration visualization
Service Discovery Consul auto-discovers monitoring targets for Prometheus
Cold Backup Enhancement Default scheduled backups, pg_probackup, one-click delayed replica
ETCD as DCS Alternative to Consul for PostgreSQL/Patroni
Redis Improvements Supports single-instance level init and remove operations

Docker Support

The most important feature in Pigsty v1.5 is Docker support. Countless software and tools can work out of the box via Docker: out-of-the-box database + out-of-the-box applications = out-of-the-box software solutions.

Many software products need databases, but putting databases in containers remains controversial. There’s a huge gap between Docker-based toy databases and production-grade databases. Pigsty combines the best of both: stateful databases are managed by Pigsty, running on standard physical or virtual machines (like PostgreSQL and Redis); stateless applications run via Docker, with their state stored in Pigsty-managed external databases.

In Pigsty v1.4.1, Docker was added as an experimental feature; in v1.5, Docker becomes a default Pigsty component, enabled by default on the meta node. Regular nodes have it disabled by default, but you can enable Docker on all nodes via configuration.

Application Ecosystem

Docker itself is just a tool — what matters is the massive application ecosystem Docker represents!

Pigsty curated some commonly used software, especially those using PostgreSQL and Redis, providing one-click launch tutorials and shortcuts, plus an offline-ready Docker image package docker.tgz.

Code Hosting Platform: Gitea

To start a private code hosting service, use this command to launch Gitea:

cd ~/pigsty/app/gitea; make up

This command uses Docker Compose to launch the Gitea image, using Pigsty’s default CMDB pg-meta.gitea as metadata storage. Access the domain or port specified in the config file to access your code hosting service.

Database Management Platform: PgAdmin

PgAdmin4 is a classic PostgreSQL management tool with many useful features. Pigsty provides the latest PgAdmin4 6.9 support — just one command to start the image, automatically loading all managed database instances from Pigsty.

cd ~/pigsty/app/pgadmin; make up; make conf

Schema Change Tool: Bytebase

Bytebase is a schema change management tool designed for PostgreSQL, using Git workflows and ticket approval to version-control database schemas. Bytebase itself stores metadata in PostgreSQL.

cd ~/pigsty/app/bytebase; make up

Web Client: PGWEB

Sometimes users want to query small amounts of data from production databases using personal accounts — a browser-based PostgreSQL client works great. PGWEB can be deployed on the management node or a dedicated bastion host, with specific HBA rules allowing personal users to query production read-only instances.

cd ~/pigsty/app/pgweb; make up

Object Storage: MinIO

Object storage is a fundamental cloud service. For private deployments, you can use MinIO to quickly build your own object storage. It can store documents, images, videos, backups, with automatic redundancy and disaster recovery, exposing a standard S3-compatible API.

cd ~/pigsty/app/minio; make up

Building on MinIO, you can use JuiceFS to convert massive distributed storage into a filesystem for other services.

Data Analysis Environment: Jupyter

Pigsty provides a powerful data analysis tool: Jupyter Lab, allowing combined Python and SQL data processing and analysis. Jupyter Lab doesn’t run via Docker by default — it runs directly under a restricted OS user on the management node for easier database interaction.

Database Schema Reports: SchemaSPY

To generate detailed schema reports for a database:

bin/schemaspy 10.10.10.10 meta pigsty

Database Log Analysis Reports

To view database log summary information:

bin/pglog-summary 10.10.10.10

More Applications

Many well-known software applications can be launched with Pigsty + Docker:

Application Description
Gitlab Open-source code hosting platform using PG
Habour Open-source image registry using PG
Jira Open-source project management platform using PG
Confluence Open-source knowledge hosting platform using PG
Odoo Open-source ERP using PG
Mastodon Social network based on PG
Discourse Open-source forum based on PG and Redis
KeyCloak Open-source SSO single sign-on solution

Better Cold Backups

Data failures broadly fall into two categories: hardware failures/resource exhaustion (disk failure/crash) and software defects/human errors (dropping databases/tables). Physical replication addresses the former, while delayed replicas and cold backups typically address the latter. Because erroneous deletion operations are immediately replicated to replicas, hot and warm backups cannot solve errors like DROP DATABASE or DROP TABLE — you need cold backups or delayed replicas.

In Pigsty v1.5, the cold backup mechanism was improved:

  • Added scheduled tasks for daily full cold backups
  • Improved delayed replica creation — just declare it and it’s automatically created
  • For power users, pg_probackup is provided as a backup solution
  • Built-in MinIO Docker images lay the foundation for out-of-the-box offsite disaster recovery

Scheduled Tasks

Pigsty v1.5 supports configuring scheduled tasks for nodes, including both append and overwrite modes for /etc/crontab. Basic physical cold backups, log analysis, schema dumps, garbage collection, and statistics collection can all be managed in a unified, declarative way.

The most important is the default daily full backup at 1:00 AM. Combined with Pigsty’s default last-day WAL archive, you can restore the database to any state within the past day, providing a solid safety net for software defects and human-error-induced data loss.

Delayed Replicas

In Pigsty v1.5, creating a delayed replica no longer requires manually running patronictl edit-config to adjust cluster configuration — just declare it like this to create a delayed replica (cluster):

CMDB Compatibility Improvements

Pigsty has an optional CMDB, allowing you to store configuration in the default PostgreSQL database on the meta node instead of the default config file pigsty.yml.

Pigsty CMDB was first introduced in v0.8, designed only for PostgreSQL. When Pigsty started supporting Redis, Greenplum, and more database types, the original design became outdated. So in Pigsty v1.5, the CMDB was redesigned.

Just use bin/inventory_load to load the current config file into CMDB, and bin/inventory_cmdb to switch to CMDB mode. When using CMDB, you can view the visual configuration inventory directly from Grafana’s CMDB Overview panel:

You can see PostgreSQL, Redis, and Greenplum/MatrixDB cluster member information from CMDB Overview.

You can adjust configuration directly via SQL, or via the API exposed by PostgREST, for example creating new clusters or scaling.

PostgREST is a binary component that automatically generates REST APIs from PostgreSQL database schemas, bundled in Pigsty v1.5’s Docker image package.

cd ~/pigsty/app/postgrest; make up

It can also auto-generate API definitions via Swagger OpenAPI Spec, expose API documentation with Swagger Editor, and generate client stubs in different programming languages.

PostgREST isn’t just for exposing CMDB CRUD interfaces. If you already have a well-designed database schema, PostgREST can immediately build a backend REST API service without hand-coding tedious CRUD logic — complex logic can be exposed via stored procedures.

For more powerful API support, consider the Kong API gateway. It can turn any existing API into a full-featured API service, enabling various authentication mechanisms, automatic logging, tracing, rate limiting, and disaster recovery. Kong is built on Nginx + Lua (OpenResty), storing metadata in PostgreSQL and Redis:

cd ~/pigsty/app/kong; make up

Infrastructure Monitoring

In Pigsty v1.5, infrastructure self-monitoring received major improvements: INFRA now uses the same management pattern as NODES, PGSQL, and REDIS. Infrastructure registers itself via the infra_register role, adding itself to Prometheus monitoring targets. Corresponding dashboards were added to Grafana.

In Pigsty v1.5’s Home dashboard, infrastructure appears as light-green components, listed alongside NODES, REDIS, and PGSQL instances. Additionally, Infra services register to Service Registry (Consul) and can be automatically managed via service discovery.

INFRA Overview provides basic status and quick navigation for all infrastructure components

Prometheus Overview: time-series database self-monitoring

Grafana Overview: monitoring dashboard self-monitoring

Loki Overview: log collection component self-monitoring

ETCD as DCS

In Pigsty v1.5, you can use ETCD as an alternative to Consul for PostgreSQL high-availability DCS.

Compared to Consul, ETCD lacks service discovery, built-in DNS, health checks, and an out-of-the-box UI, but ETCD requires no agent, is simpler to deploy, has higher popularity thanks to the Kubernetes ecosystem, has one fewer failure point than Consul, and offers better metric observability.

Just specify pg_dcs_type: etcd to use ETCD as DCS. You can also use both Consul and ETCD simultaneously — for example, ETCD for DCS and Consul for service discovery.

Pigsty v1.5 provides an out-of-the-box monitoring dashboard for ETCD and Consul: DCS Overview

Currently, ETCD as DCS is a minimum viable implementation without CA certificates and TLS support — this will be added in a future security hardening update.

Better Log Collection and Visualization

In Pigsty v1.5, separate access logs are enabled by default for each upstream service, with all fields parsed by Loki for direct analysis. If you have a website on Pigsty, you can immediately do interactive log traffic analysis and statistics.

NGINX Overview: showing Nginx metrics and logs

v1.5.0 Release Notes

Highlights

  • Complete Docker support: enabled by default on meta node with many out-of-the-box software templates: bytebase, pgadmin, pgweb, postgrest, minio, etc.
  • Infrastructure self-monitoring: Nginx, ETCD, Consul, Prometheus, Grafana, Loki self-monitoring
  • CMDB upgrade: compatibility improvements, supports Redis cluster/Greenplum cluster metadata, config file visualization
  • Service discovery improvements: Consul can auto-discover all monitoring targets and integrate with Prometheus
  • Better cold backup support: default scheduled backup tasks, pg_probackup backup tool, one-click delayed replica creation
  • ETCD can now be used as PostgreSQL/Patroni DCS service, as an alternative to Consul
  • Redis playbook/role improvements: now allows init and remove operations for individual Redis instances, not just entire Redis nodes

Monitoring System

Dashboards

  • CMDB Overview: visualize Pigsty CMDB Inventory
  • DCS Overview: view Consul and ETCD cluster monitoring metrics
  • Nginx Overview: view Pigsty Web access metrics and logs
  • Grafana Overview: Grafana self-monitoring
  • Prometheus Overview: Prometheus self-monitoring
  • INFRA Dashboard redesigned to reflect overall infrastructure status

Monitoring Architecture

  • Now allows Consul for service discovery (when all services are registered to Consul)
  • All Infra components now enable self-monitoring and register to Prometheus and Consul via infra_register role
  • Metrics collector pg_exporter updated to v0.5.0, new features: scale and default, allowing metric multiplication factors and default values
  • pg_bgwriter, pg_wal, pg_query, pg_db, pgbouncer_stat time-related metrics now uniformly scaled to seconds from milliseconds/microseconds
  • Related counter metrics in pg_table now have default value 0 instead of NaN
  • pg_class metrics collector removed by default, related metrics added to pg_table and pg_index collectors
  • pg_table_size metrics collector now enabled by default with 300-second cache time

Deployment

  • New optional package docker.tgz with common app images: Pgadmin, Pgweb, Postgrest, ByteBase, Kong, Minio, etc.
  • New ETCD role: automatically deploys ETCD service on DCS Server nodes and integrates with monitoring
  • pg_dcs_type specifies DCS service for PG high-availability: Consul (default), ETCD (alternative)
  • node_crontab parameter for configuring node scheduled tasks like database backups, VACUUM, statistics collection
  • New pg_checksum option: when enabled, database cluster enables data checksums (previously only crit template enabled by default)
  • New pg_delay option: when instance is Standby Cluster Leader, this parameter configures a delayed replica
  • New pg_probackup package, default role replicator now has backup-related function permissions
  • Redis deployment split into two parts: Redis node and Redis instance, redis_port parameter controls specific instances
  • Loki and Promtail now installed via fpm-built RPM packages
  • DCS3 config template now uses a 3-node pg-meta cluster with a single-node delayed replica

Software Upgrades

  • PostgreSQL upgraded to 14.3
  • Redis upgraded to 6.2.7
  • PG Exporter upgraded to 0.5.0
  • Consul upgraded to 1.12.0
  • vip-manager upgraded to v1.0.2
  • Grafana upgraded to v8.5.2
  • Loki & Promtail upgraded to v2.5.0, using fpm packaging

Bug Fixes

  • Fixed Loki and Promtail default config filename issues
  • Fixed Loki and Promtail environment variable expansion issues
  • Complete English documentation translation and revision; documentation JS resources now served locally, no internet access required

API Changes

New Parameters

  • node_data_dir: Main data mount path, created if doesn’t exist
  • node_crontab_overwrite: Overwrite /etc/crontab instead of appending
  • node_crontab: Node crontab content to append or overwrite
  • nameserver_enabled: Enable nameserver on this infra node?
  • prometheus_enabled: Enable prometheus on this infra node?
  • grafana_enabled: Enable grafana on this infra node?
  • loki_enabled: Enable loki on this infra node?
  • docker_enable: Enable docker on this infra node?
  • consul_enable: Enable consul server/agent?
  • etcd_enable: Enable etcd server/client?
  • pg_checksum: Enable pg cluster data checksums?
  • pg_delay: Application delay when backup cluster leader replays replication

Parameter Redesign

*_clean is now a boolean parameter for cleaning existing instances during init.

*_safeguard is also a boolean parameter to prevent cleaning running instances during any playbook execution.

  • pg_exists_action -> pg_clean
  • pg_disable_purge -> pg_safeguard
  • dcs_exists_action -> dcs_clean
  • dcs_disable_purge -> dcs_safeguard

Parameter Renames

  • node_ntp_config -> node_ntp_enabled
  • node_admin_setup -> node_admin_enabled
  • node_admin_pks -> node_admin_pk_list
  • node_dns_hosts -> node_etc_hosts_default
  • node_dns_hosts_extra -> node_etc_hosts
  • node_dns_server -> node_dns_method
  • node_local_repo_url -> node_repo_local_urls
  • node_packages -> node_packages_default
  • node_extra_packages -> node_packages
  • node_packages_meta -> node_packages_meta
  • node_meta_pip_install -> node_packages_meta_pip
  • node_sysctl_params -> node_tune_params
  • app_list -> nginx_indexes
  • grafana_plugin -> grafana_plugin_method
  • grafana_cache -> grafana_plugin_cache
  • grafana_plugins -> grafana_plugin_list
  • grafana_git_plugin_git -> grafana_plugin_git
  • haproxy_admin_auth_enabled -> haproxy_auth_enabled
  • pg_shared_libraries -> pg_libs
  • dcs_type -> pg_dcs_type

v1.5.1 Release Notes

Highlights

IMPORTANT: Fixed the issue where CREATE INDEX|REINDEX CONCURRENTLY in PG14.0-14.3 could corrupt index data.

Pigsty v1.5.1 upgrades the default PostgreSQL version to 14.4. Strongly recommend updating ASAP.

Software Upgrades

  • postgres upgraded to 14.4
  • haproxy upgraded to 2.6.0
  • grafana upgraded to 9.0.0
  • prometheus upgraded to 2.36.0
  • patroni upgraded to 2.1.4

Bug Fixes

  • Fixed TYPO in pgsql-migration.yml
  • Removed PID config item from HAProxy configuration
  • Removed i686 packages from default packages
  • Enabled all Systemd Redis Services by default
  • Enabled all Systemd Patroni Services by default

API Changes

  • grafana_database and grafana_pgurl marked as deprecated API, will be removed in future versions

New Applications

  • wiki.js: Build local Wikipedia with Postgres
  • FerretDB: Provide MongoDB API using Postgres

2.9 - Pigsty v1.4: Modular Architecture, MatrixDB Data Warehouse Support

Originally published on VONNG.

GitHub Release | Release Note

Pigsty v1.4 is officially released! A brand new modular architecture: four built-in modules INFRA, NODES, PGSQL, REDIS can be used independently and freely combined; new MatrixDB time-series data warehouse deployment and monitoring support; global CDN acceleration for downloads.

GitHub stars are taking off!

Modular Architecture

The core feature of Pigsty v1.4 is a major refactor of the underlying architecture. In v1.4, the entire system decouples into 4 independent modules that can be maintained separately and freely combined:

Module Purpose
INFRA Infrastructure: monitoring/alerting/visualization/logging/DNS/NTP and other shared components
NODES Host node management module
PGSQL PostgreSQL database deployment and management module
REDIS Redis database deployment and management module

The new Pigsty v1.4 monitoring home dashboard

Typical deployment scenarios:

  • Single-node PostgreSQL distribution: Install INFRA + NODES + PGSQL modules sequentially on one machine to get a ready-to-use, self-monitoring database instance.

  • Production-grade host monitoring system: Install the INFRA module on one machine, install the NODES module on all monitored nodes. All host nodes get configured with software repos, packages, DNS, NTP, node monitoring, log collection, DCS Agent — everything needed for production.

  • Massive PostgreSQL clusters: Add the PGSQL module on nodes managed by Pigsty. One-click deploy various PostgreSQL clusters: single instance, primary with N replicas HA cluster, synchronous cluster, quorum-commit sync cluster, clusters with offline ETL roles, standby clusters for disaster recovery, delayed replication clusters, Citus distributed clusters, TimescaleDB clusters, MatrixDB data warehouse clusters.

  • Redis clusters: Add the REDIS module on Pigsty-managed nodes. Future database modules (like KAFKA, MINIO, MYSQL) can be added to Pigsty in similar fashion.

Modular playbooks and configuration parameters

New Database Support

PostgreSQL is a versatile database kernel, but as organizations and data grow, specialized data components become necessary. The two most typical are: caching (Redis) and data warehousing (Greenplum).

Redis further strengthens business system OLTP capabilities, offloads database pressure, and developers love its simple model. Greenplum significantly enhances OLAP capabilities, using the same language, drivers, and interfaces as PostgreSQL, scaling analytics from tens of TB to PB or even ZB scale.

Redis and Greenplum extend PostgreSQL’s capability boundaries in two directions — both are common PostgreSQL companions, frequently used together. Therefore, Pigsty v1.4 provides preliminary support for Redis and Greenplum.

Redis Overview Dashboard

Note that Pigsty supports not native Greenplum, but a fork: MatrixDB. The official Greenplum version is still 6.x based on PostgreSQL 9.6 kernel. MatrixDB is based on Greenplum 7 and PostgreSQL 12 kernel, with additional time-series functionality. So Pigsty uses MatrixDB as the Greenplum implementation.

In Pigsty v1.4, there’s no dedicated MATRIXDB module — MatrixDB deployment completely reuses the PGSQL module. You can configure MatrixDB with familiar configuration parameters. From Pigsty’s perspective, a MatrixDB data warehouse is logically N pairs of standard primary-replica PGSQL clusters: one standard Master cluster (Master & Standby), and multiple Segment clusters (Primary & Mirror) distributed across nodes. All PGSQL dashboards work directly with MatrixDB.

PGSQL MatrixDB Dashboard

The dedicated PGSQL Matrix dashboard shows core monitoring metrics for a MatrixDB deployment, while other monitoring dashboards reuse existing PGSQL panels.

Defining a 4-node MatrixDB requires only this configuration

Monitoring System Evolution

The monitoring system has always played a core role in Pigsty. In v1.4, Pigsty’s monitoring system has significant improvements.

Host Monitoring

Pigsty v1.4 introduces brand new node monitoring capabilities — a direct result of the modular refactor. Previously, machine monitoring metrics were 1:1 bound to PostgreSQL instances. For a PostgreSQL distribution, this design was fine. But as Pigsty evolved, this design became outdated.

NODES Overview panel, providing navigation for all nodes

Users may have various deployment strategies, such as deploying multiple database instances on one node, or even multiple different database types. In such cases, the right approach is to separate node management and monitoring from specific database types.

This brings two significant benefits: first, if users only need node monitoring and management without database monitoring, it’s much simpler than before; second, a single node can deploy multiple or even different types of databases while reusing the same node monitoring data. Anytime you click an IP address, you jump to the specific NODES Instance to view node details.

The former PGSQL Node is now NODES Instance

Node monitoring provides three levels: global overview, cluster, and single node. Node clusters can be configured to match PostgreSQL database clusters by default, or have independent identity configuration for viewing cluster resources from different perspectives.

New Nodes Cluster panel, focusing on aggregate metrics and horizontal comparison within a node group

While Pigsty is positioned as a batteries-included PostgreSQL distribution, it also contains host monitoring best practices. Some users don’t need database features at all — they just use Pigsty for host monitoring.

Log Collection

In Pigsty v1.4, Loki and Promtail log collection components are upgraded to default system components. Loki, made by Grafana Labs, uses a label system similar to Prometheus with LogQL similar to PromQL. It’s a lightweight, elegant log collection, processing, and analysis solution.

After a year of testing and refinement, Loki is now a default part of Pigsty, collecting various logs in real-time: node syslog, dmesg, cron logs, database postgres/pgbouncer/patroni logs, and Redis logs.

LOGS Instance monitoring panel in the INFRA section for real-time log browsing and searching

ELK is overkill for SRE logging needs — what people really want is an efficient, fast, massively parallel GREP. Loki excels at this.

Additionally, besides node logs, you can also view real-time infrastructure log data from the new INFRA Overview panel.

INFRA Overview panel showing infrastructure logs

PGSQL Monitoring

Pigsty v1.4 provides monitoring support for new database types, but classic PostgreSQL monitoring wasn’t neglected. In v1.4, many PGSQL monitoring panels were adjusted and remade. The most representative is the PGSQL Cluster panel.

New PGSQL Cluster monitoring panel first screen

PGSQL Cluster is one of the most core monitoring panels in Pigsty database monitoring, serving as a connecting hub to display an autonomous database cluster’s key status. The new design hides unnecessary information and focuses on cluster resources. You can quickly click cluster resource objects from the first screen to navigate to detailed monitoring panels: including nodes, instances, load balancers, services, databases, and service components.

Beyond cluster resource objects, PGSQL Cluster’s first screen only shows the most critical monitoring metrics, alert events, and cluster/instance pressure levels. Other details are hidden in the topic sections below.

Member details table in the hidden second section by default

The second significant improvement is the new PGSQL Databases panel. Previously, database-internal monitoring only focused on single objects within single instances. But for business objects like tables and indexes, the focus is on their overall metrics across the entire cluster. PGSQL Databases was created for this. You can query a database’s performance across the entire cluster, horizontally comparing differences between instances:

PGSQL Databases panel: agg(metrics{datname=*}) by (ins)

More importantly, you can see aggregate views of every table and query type across the cluster scope. For example, check a table’s or query type’s QPS on the cluster’s primary and replica instances, or confirm an index’s usage across different cluster instances — enabling targeted business and application optimization.

Cluster-level aggregate display of database objects: Tables & Queries, click to drill down

The colored TreeMap quickly reflects two-dimensional attributes: for tables, size represents space occupied, color represents access frequency. For queries, size represents total time spent on that query type, color represents average response time.

Application Dashboards

Besides the four core modules INFRA, NODES, PGSQL, REDIS, the Pigsty Grafana home has one more section: APP. This is for user applications. Any monitoring dashboard tagged with APP and Overview appears in Pigsty’s dashboard navigation. Pigsty ships with a ready-to-use small app PGLOG for analyzing PostgreSQL’s own CSV logs, quickly locating anomalies from logs and jumping to specific connection details.

PGLOG Overview, using shortcuts to quickly load logs into application tables for analysis

Additionally, Pigsty established a dedicated code repository pigsty-app for hosting Pigsty sample applications. Current applications include:

Application Description
ISD NOAA global surface weather station historical weather data query
COVID WHO COVID-19 pandemic data query
DBENG DB-Engine database popularity trends and predictions
APPLOG Apple app privacy log visualization
WORKTIME Work hours at major tech companies

More data application examples will be added continuously.

DBEng Trend: Using authoritative DB-Engines popularity data to predict when PostgreSQL will become the world’s most popular relational database

Installation Experience / CDN

Previously Pigsty used GitHub as the release platform, which was difficult to access from mainland China. So we enabled a global CDN domain http://download.pigsty.cc. For example, the latest source package and offline package download URLs are:

http://download.pigsty.cc/v1.4.0/pigsty.tgz  (2MB)
http://download.pigsty.cc/v1.4.0/pkg.tgz     (940MB)

Pigsty’s software packages were reorganized and slimmed down from 1.3GB to 940MB in v1.4. Users needing Greenplum and MatrixDB can download a separate offline package matrix.tgz (338MB).

Pigsty v1.4 provides a dedicated download script download for automatically downloading and extracting optional packages pkg.tgz, matrix.tgz, app.tgz. This script auto-detects network environment, using GitHub Releases outside the GFW and Tencent Cloud CDN inside China.

The Pigsty installation process is now:

bash -c "$(curl -fsSL http://download.pigsty.cc/get)" # Download
./download pkg matrix app   # Download and extract optional packages (optional)
cd ~/pigsty && ./configure  # Configure
make install                # Install

Case Study: Tantan

Tantan is Pigsty’s largest user case. In March 2022, Tantan decommissioned the last legacy PostgreSQL database pg.meta.tt, with all production databases migrated to Pigsty. One hundred clusters are all managed by Pigsty v1.3.1 (with v1.4 monitoring). Auto-failover is enabled for all clusters, marking the official completion of the two-year Database Ascension Project.

Tantan’s main production Pigsty deployment: 240 instances, 13,400 cores of PostgreSQL OLTP clusters

At Tantan, Pigsty underwent long-term, large-scale, rigorous production testing. Over two years of continuous refinement led to what it is today. In recent chaos engineering drills, ops randomly selected database machines for multiple crash tests. Pigsty automatically performed HA primary-replica/traffic failover with no human intervention. Replica crashes had no business impact; primary crashes affected business writes for less than 1 minute.

A typical replica crash scenario: read traffic quickly handled by primary, only a few in-flight queries interrupted, then immediate recovery

A typical primary crash scenario: 30s after primary goes down, replica is promoted to new primary, 30s of business write impact then self-healing

v1.4.0 Release Notes

Architecture

  • Decoupled system into 4 major categories: INFRA, NODES, PGSQL, REDIS, making Pigsty clearer and more extensible
  • Single-node deployment = INFRA + NODES + PGSQL
  • PGSQL cluster deployment = NODES + PGSQL
  • Redis cluster deployment = NODES + REDIS
  • Other database deployment = NODES + xxx (e.g., MONGO, KAFKA…)

Accessibility

  • CDN for mainland China
  • Use bash -c "$(curl -fsSL http://get.pigsty.cc/latest)" to get latest source
  • New download script to download and extract packages

Monitoring Enhancements

  • Split monitoring into 5 categories: INFRA, NODES, REDIS, PGSQL, APP
  • Logging enabled by default
    • loki and promtail now enabled by default, with prebuilt loki-rpm
  • Model and labels
    • Added hidden ds prometheus datasource variable to all dashboards
    • Added ip label to all metrics, used as join key between database and node metrics
  • INFRA Monitoring
    • INFRA main dashboard: INFRA Overview
    • Added Log dashboard: Logs Instance
    • PGLOG Analysis and PGLOG Session now treated as sample Pigsty APPs
  • NODES Monitoring Application
    • Pigsty can be used standalone as host monitoring software
    • Includes 4 core dashboards: Nodes Overview & Nodes Cluster & Nodes Instance & Nodes Alert
    • New identity variables for nodes: node_cluster and nodename
  • PGSQL Monitoring Enhancements
    • New PGSQL Cluster, simplified and focused on what matters in a cluster
    • New dashboard PGSQL Databases for cluster-level object monitoring
    • PGSQL Alert dashboard now focuses solely on PGSQL alerts
    • PGSQL Shard added to PGSQL
  • Redis Monitoring Enhancements
    • Added node monitoring to all Redis dashboards

MatrixDB Support

  • MatrixDB (Greenplum 7) can be deployed via pigsty-matrix.yml playbook
  • MatrixDB monitoring dashboard: PGSQL MatrixDB
  • Added sample configuration: pigsty-mxdb.yml

Software Upgrades

  • PostgreSQL 14.2
  • PostGIS 3.2
  • TimescaleDB 2.6
  • Patroni 2.1.3 (Prometheus metrics + failover slots)
  • HAProxy 2.5.5 (fixed stats errors, more metrics)
  • PG Exporter 0.4.1 (timeout parameters, etc.)
  • Grafana 8.4.4
  • Prometheus 2.33.4
  • Greenplum 6.19.4 / MatrixDB 4.4.0
  • Loki now provided as RPM package instead of ZIP archive

Bug Fixes

  • Removed Patroni’s Consul dependency, making migration to new Consul clusters easier
  • Fixed Prometheus bin/new script default data directory path
  • Added restart seconds in vip-manager systemd service
  • Fixed typos and tasks

API Changes

New Variables

  • node_cluster: Identity variable for node cluster
  • nodename_overwrite: If set, nodename will be set to node’s hostname
  • nodename_exchange: Exchange node hostnames between play hosts (in /etc/hosts)
  • node_dns_hosts_extra: Extra static DNS records easily overridable by single instance/cluster
  • patroni_enabled: If disabled, postgres & patroni bootstrap not executed during postgres role
  • pgbouncer_enabled: If disabled, pgbouncer not started during postgres role
  • pg_exporter_params: Extra URL parameters for pg_exporter when generating monitoring target URL
  • pg_provision: Boolean variable indicating whether to execute provisioning part of postgres role
  • no_cmdb: Used for infra.yml and infra-demo.yml playbooks, won’t create CMDB on meta node

v1.4.1 Release Notes

Bug fixes / Docker support / English documentation

Docker is now enabled by default on the meta node, allowing you to spin up various software.

Bug Fixes

  • Fixed Promtail & Loki configuration variable issues
  • Fixed Grafana legacy alerts
  • Disabled nameserver by default
  • Renamed pg-alias.sh for Patroni shortcuts
  • Disabled exemplars queries for all dashboards
  • Fixed Loki data directory issue
  • Changed autovacuum_freeze_max_age from 100000000 to 1000000000

2.10 - Pigsty v1.3: Redis Support, PGCAT Overhaul, PGSQL Enhancements

Originally published on VONNG.

GitHub Release | Release Note

Pigsty v1.3 is officially released, featuring Redis support, a rebuilt PGCAT application, and enhanced PGSQL monitoring.

Redis Support

While PostgreSQL is the world’s most advanced open-source relational database, every hero needs a sidekick. Pigsty v1.3 introduces a powerful caching companion for PostgreSQL: the world’s fastest database — Redis.

redis-partner

Redis delivers incredible performance, easily hitting 200-300k QPS on a single core.

redis-fast

The Pigsty demo now includes Redis cluster examples:

redis-demo

Three Deployment Modes

Redis has three classic deployment patterns: primary-replica (Standalone), native cluster (Cluster), and high-availability sentinel (Sentinel). Pigsty v1.3 supports all three.

redis-overview

The Redis Overview dashboard shows three sample clusters, each demonstrating a different deployment mode.

Declarative Configuration

Defining a Redis cluster works the same way as PostgreSQL. After declaring your config, just run redis.yml -l <cluster> to create the cluster:

redis-config

A Redis cluster only needs a few required identity parameters. Of course, you can use additional parameters for fine-grained configuration:

redis-params-1redis-params-2

Auto-Monitoring

Redis clusters and instances created with Pigsty are automatically integrated into the monitoring system.

redis-cluster

The Redis cluster dashboard homepage — click on a specific instance to jump to instance-level monitoring:

redis-instance

PGCAT Overhaul

v1.3 rebuilds the PGCAT application — a tool for browsing and visualizing PostgreSQL system catalogs directly from Grafana.

pgcat-instance

Single PostgreSQL instance catalog info: databases, active sessions, running queries.

pgcat-instance-2

More instance-level catalog info: configuration, replication, memory usage, persistence, roles.

pgcat-database

Single PostgreSQL database catalog info, including schemas, tables, indexes, sequences, and other objects.

pgcat-table

Redesigned PGCAT TABLE dashboard with detailed per-column statistics.

Agentless Design

PGCAT only needs a target database URL — no agent installation required. Even monitor-only deployments of existing instances get full PGCAT functionality.

pgsql-monitor-only

In Pigsty v1.3’s monitor-only deployment mode, external PostgreSQL instances are also registered in Grafana with PGCAT enabled by default.

PGSQL Enhancements

The core PGSQL monitoring application also received significant improvements.

pgsql-cluster

In Pigsty v1.3, the PGSQL Cluster dashboard adds quick-navigation panels for 10 key metrics.

Both PGSQL Instance and PGSQL Cluster now include quick-navigation panels for rapid problem identification. PGSQL Service was completely redesigned — simpler and more intuitive for quickly understanding cluster topology. Other dashboards also received optimizations and improvements.

Additionally, v1.3 includes improvements to the semi-automated database migration playbook and profiling tool support.

v1.3.0 Release Notes

Redis Support

Feature Description
Redis Deployment Standalone, Sentinel, and Cluster modes
Redis Monitoring Overview, Cluster, and Instance dashboards

PGCAT Overhaul

Dashboard Description
PGCAT Instance New instance-level catalog dashboard
PGCAT Database New database-level catalog dashboard
PGCAT Table Redesigned table-level dashboard

PGSQL Enhancements

Dashboard Improvements
PGSQL Cluster Added 10 key metric panels
PGSQL Instance Added 10 key metric panels
PGSQL Service Simplified and redesigned
Cross-references Navigation links between PGCAT and PGSQL dashboards

Monitor Deployment

  • Grafana datasources auto-register during monitor-only deployment

Software Upgrades

  • PostgreSQL 13 added to default package list
  • PostgreSQL upgraded to 14.1 as default
  • Added Greenplum RPM packages and dependencies
  • Added Redis RPM and source packages
  • Added perf as default package

v1.3.1 Release Notes

Monitoring

  • PGSQL & PGCAT dashboard improvements
  • Optimized PGCAT Instance & PGCAT Database layout
  • Added key metric panels to PGSQL Instance dashboard (consistent with PGSQL Cluster)
  • Added table/index bloat panels to PGCAT Database, removed PGCAT Bloat dashboard
  • Added index information to PGCAT Database dashboard
  • Fixed broken panels in Grafana 8.3
  • Added Redis index to Nginx homepage

Deployment

  • New infra-demo.yml playbook for one-click bootstrap
  • New infra-jupyter.yml playbook for optional JupyterLab server
  • New infra-pgweb.yml playbook for optional PGWeb server
  • Added pg alias on meta node for starting PostgreSQL cluster from admin user
  • Adjusted max_locks_per_transactions in all Patroni config templates per timescaledb-tune recommendations
  • Added citus.node_conninfo: 'sslmode=prefer' to config templates for SSL-free Citus usage
  • Added all extensions (except pgrouting) from PGDG14 to package list
  • Upgraded node_exporter to v1.3.1
  • Added PostgREST v9.0.0 for generating REST APIs from PostgreSQL schemas

Bug Fixes

  • Grafana security vulnerability fix (upgraded to v8.3.1, details)
  • Fixed pg_instance & pg_service issues in register role when starting playbook mid-run
  • Fixed Nginx homepage rendering on hosts without pg_cluster variable
  • Fixed style issues when upgrading to Grafana 8.3.1

2.11 - Pigsty v1.2: PG14 Default, Monitor Existing PG

Originally published on VONNG.

GitHub Release | Release Note

Pigsty v1.2 is officially released, making PostgreSQL 14 the default version and adding support for monitoring existing database instances independently.

PostgreSQL 14 Becomes the Default

PostgreSQL 14 was released last month with significant improvements across the board, especially in observability. After deployment and thorough testing in multiple production environments, PostgreSQL 14 is now Pigsty’s default database version.

Meanwhile, the time-series extension TimescaleDB 2.5 and geospatial extension PostGIS 3.1, both compatible with PG14, are now installed and enabled by default. Combined with the distributed database extension Citus 10, this delivers a truly batteries-included space-time hyper-converged open-source PostgreSQL distribution.

timescale-postgis-citus

All three are mutually compatible and can be used together.

Monitor-Only Deployment Mode

The second major feature is monitor-only deployment mode. Previously, Pigsty as a distribution tightly coupled its monitoring system with its deployment solution. However, many users want to use only Pigsty’s monitoring system to monitor existing database instances, cloud databases, or other RDS products and derivatives.

monitor-minio

Minimal deployment mode runs pg_exporter on different local ports to monitor external PostgreSQL instances.

In v1.2, Pigsty offers three optional monitoring deployment modes:

Mode Description
Full Complete Pigsty deployment with monitoring and control
Lean Deploy only monitoring-related components
Minimal Only requires a database connection string, no remote machine access needed

The new minimal deployment mode no longer requires login or admin privileges on remote machines — as long as you have a connection string with read-only access to the remote database, you can add it to monitoring. All monitoring functionality is consolidated on a single machine, making management simple and convenient.

monitor-only

Although you only get PostgreSQL metrics, most of Pigsty’s monitoring system functionality still works. Testing shows Pigsty can also directly monitor MatrixDB, Greenplum, and other PostgreSQL-derived/compatible database products.

Streamlined Configuration Templates

Configuration templates have been further streamlined: now there are only two templates — Production (default) and Sandbox.

Spec parameter templates are now richer, providing smooth transition options:

Spec Config Description
tiny 1C1G Minimal testing spec
mini 2C4G Development environment spec
small 4C8G Small production spec
medium 8C16G Medium production spec
large 16C32G Large production spec
oltp/olap/crit 64C400G Professional production spec

During configuration, the setup wizard automatically selects the appropriate parameter template based on machine specs.

configure

Pigsty maintains its tradition of one-liner installation: ./configure && make install.

Utility Playbooks

The new pgsql-migration playbook auto-generates the commands, scripts, and documentation needed for database migration, making online zero-downtime migrations based on logical replication simple (already used to migrate dozens of databases in production).

The pgsql-audit playbook generates audit reports for database instances based on audit requirements.

Sample Applications

v1.2 provides two new Pigsty App examples:

AppLog — An app for visualizing Apple iOS 15 privacy logs, showing which apps accessed which permissions.

applog

WorkTime — An app for querying work and rest hours at major tech companies.

worktime

Both apps are simple but practical, each built in under an hour. Pigsty is an excellent tool for rapidly prototyping functional applications.

Looking Ahead

PGSQL v8 — More clearly organized monitoring dashboards with role-specific views for different user groups.

pgsql-v8

PGCAT v2 — Richer system catalog navigation and browsing functionality.

pgcat-v2

REDIS v1beta — Redis is often used alongside PostgreSQL; future versions will integrate Redis deployment and monitoring as a complete solution.

redis-v1

v1.2.0 Release Notes

Core Features

  • Default to PostgreSQL 14
  • Default to TimescaleDB 2.5 extension
  • TimescaleDB and PostGIS enabled by default in CMDB

Monitor-Only Mode

  • Monitor existing PostgreSQL instances via connection URL only
  • pg_exporter deployed on local meta node
  • New PGSQL Cluster Monly dashboard for remote clusters

Software Upgrades

  • Grafana upgraded to 8.2.2
  • pev2 upgraded to v0.11.9
  • Promscale upgraded to 0.6.2
  • PgWeb upgraded to 0.11.9
  • New extensions: pglogical, pg_stat_monitor, orafce

Improvements

  • Auto-detect machine specs and use appropriate node_tune and pg_conf templates
  • Reworked bloat-related views, exposing more information
  • Removed TimescaleDB and Citus internal monitoring
  • Added pgsql-audit.yml playbook for creating audit reports
  • All config templates simplified to two: auto and demo

Bug Fixes

  • pgbouncer_exporter resource owner changed to {{ pg_dbsu }} instead of postgres
  • Fixed pg_exporter duplicate metrics on pg_table/pg_index during REINDEX TABLE CONCURRENTLY

Upgrade Notes

No API changes in v1.2.0 — existing pigsty.yml config files (PG13) still work. For infrastructure, re-running repo will handle most updates.

For databases, you can continue using existing PG13 instances. When PostGIS and TimescaleDB extensions are involved, in-place upgrades are complex — logical replication migrations are recommended. The new pgsql-migration.yml playbook generates scripts to help achieve near-zero-downtime cluster migrations.

2.12 - Pigsty v1.1: Homepage, Jupyter, Pev2, PgBadger

Originally published on VONNG.

GitHub Release | Release Note

Pigsty v1.1 is officially released, featuring a brand-new homepage design, plus support for JupyterLab, PGWeb, PEV2, PgBadger, and other useful tools.

Brand New Homepage

The Home Dashboard in Grafana has long served as Pigsty’s de facto “homepage.” Now Pigsty finally has a standalone, well-designed homepage of its own.

homepage

This homepage is a local version of the documentation site, served by the default Nginx.

Service Navigation

The homepage provides navigation to all Pigsty service components, including Consul, Grafana, Prometheus, AlertManager, and the newly introduced PGWeb and JupyterLab in v1.1. Click the component name/URL in the center, or use the Service dropdown menu in the top-right navigation bar.

Monitoring Navigation

The homepage can display clusters and instances in your Pigsty deployment (optional), with direct links to specific cluster/instance monitoring pages and admin interfaces.

monitor-nav

App Navigation

The App dropdown in the top-right corner is the entry point for Pigsty’s extended features. In v1.1, Pigsty ships with several useful and interesting apps, all configurable via options.

app-nav

Local Documentation

In Pigsty v1.1, you can access local offline documentation directly from the homepage, available in both English and Chinese.

local-docs

JupyterLab

If you use Python for data analysis, you’re probably familiar with Jupyter. Pigsty v1.0 bundled the JupyterLab package; v1.1 takes it further with native integration. JupyterLab is enabled by default in demo and personal configuration templates, but disabled by default in production deployments.

jupyter-1

With Jupyter Notebook, you can efficiently and agilely extract, process, analyze, transform, and visualize data — combining the power of Python and SQL.

jupyter-2

With great power comes great risk. Jupyter’s ability to execute arbitrary code is too risky for production environments, so it’s disabled by default in production configuration templates.

PGWeb

As a batteries-included database distribution, providing batteries-included GUI client tools is also important. PGWeb is a lightweight, browser-based PostgreSQL GUI client written in Go.

pgweb-1

Like Jupyter, PGWeb is enabled by default in demo and personal configuration templates, but disabled in production deployments. However, PGWeb requires a connection string to access the database, making it relatively safe for scenarios where individual users need to query small amounts of data in production.

pgweb-2

Users can browse schemas and objects in the database, quickly view table data, execute queries, and more.

PEV2

PEV2 is a handy execution plan visualizer that converts PostgreSQL EXPLAIN output into an intuitive execution plan tree.

pev2

This tool is extremely useful for optimizing slow queries and analyzing auto_explain results.

PgBadger

PgBadger is an excellent PostgreSQL log analysis tool that quickly generates beautiful, comprehensive analysis reports from CSV logs.

Use bin/pglog-summary [ip] [date] to pull logs from a specific node on a specific date and create a log analysis report.

pgbadger

Add this command to crontab to automatically generate database operation reports daily or near-real-time.

Software Updates

PostgreSQL 14 is officially released, and Pigsty v1.1 immediately added support. The pigsty-pg14 template can now create PostgreSQL databases with version 14 as default in production. However, since TimescaleDB doesn’t officially support PG14 yet (expected around 10/30), PG14 won’t be the default database version in Pigsty for now.

pg14

Pigsty will upgrade the default PG version to PG14 in v1.2.

Software upgrade list:

Component Version
PostgreSQL v13.4
pgbouncer v1.16
Grafana v8.1.4
Prometheus v2.29
node_exporter v1.2.2
HAProxy v2.1.1
Consul v1.10.2
vip-manager v1.0.1

Database Migration Playbook

Pigsty includes a built-in database online migration helper script: pgsql-migration.yml, providing an out-of-the-box zero-downtime database migration solution based on logical replication.

Fill in the source and target cluster information, and the playbook will automatically generate the scripts needed for migration — just execute them in sequence.

migration-1migration-2

Sample App: Privacy Log Visualization

Pigsty’s default demo apps now include a new one: Apple App Privacy Log Visualization (AppLog). Export privacy access records from iOS 15 and visualize them in this app.

applog-1applog-2

Handy Features

Dummy File Placeholder

v1.1 adds a new feature for database instances: Dummy File. The concept is simple — create a file of a certain size (e.g., 1–4GB) at /pg/dummy. When disk-full failures occur (when many operations can’t complete normally), just delete it to free up emergency space.

Promscale Support

v1.1 adds the Promscale package. This interesting component lets you replace Prometheus’s time-series storage with TimescaleDB (PostgreSQL).

v1.1.0 Release Notes

Feature Enhancements

  • Added pg_dummy_filesize to create filesystem space placeholder
  • Major homepage redesign
  • Added JupyterLab integration
  • Added PGWeb console integration
  • Added PgBadger support
  • Added PEV2 support, execution plan visualization tool
  • Added pglog tooling

Software Upgrades

  • PostgreSQL upgraded to v13.4 (with official PG14 support)
  • pgbouncer upgraded to v1.16 (metric definitions updated)
  • Grafana upgraded to v8.1.4
  • Prometheus upgraded to v2.29
  • node_exporter upgraded to v1.2.2
  • HAProxy upgraded to v2.1.1
  • Consul upgraded to v1.10.2
  • vip-manager upgraded to v1.0.1

API Changes

  • nginx_upstream now has different structure (incompatible)
  • New config entry: app_list, navigation entries rendered to homepage
  • New config entry: docs_enabled, setup local docs on default server
  • New config entry: pev2_enabled, setup local PEV2 tool
  • New config entry: pgbadger_enabled, create log summary/report directory
  • New config entry: jupyter_enabled, enable JupyterLab server on meta node
  • New config entry: jupyter_username, specify user to run JupyterLab
  • New config entry: jupyter_password, specify default password for JupyterLab
  • New config entry: pgweb_enabled, enable PGWeb server on meta node
  • New config entry: pgweb_username, specify user to run PGWeb
  • Renamed internal flag repo_exist to repo_exists
  • repo_address default value changed to pigsty instead of yum.pigsty
  • HAProxy access point changed to http://pigsty instead of http://h.pigsty

v1.1.1 Release Notes

  • Replaced TimescaleDB apache version with timescale version
  • Upgraded Prometheus to 2.30
  • Fixed pg_exporter config directory owner issue (changed to {{ pg_dbsu }})

Upgrade Notes

The main change in this version is TimescaleDB — using the official TimescaleDB License (TSL) version to replace the Apache License v2 version from the PGDG repository.

# Stop postgres instances with timescaledb
yum remove -y timescaledb_13

# Add TimescaleDB official repo
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/7/$basearch
repo_gpgcheck=0
gpgcheck=0
enabled=1

yum install timescaledb-2-postgresql13

2.13 - Pigsty v1.0: GA Release with Monitoring Overhaul

Originally published on VONNG.

GitHub Release | Release Note

After over a year of iterations and refinement, Pigsty officially ships v1.0.0 GA.

Pigsty (/ˈpɪɡˌstaɪ/) stands for PostgreSQL In Graphic STYle — PostgreSQL, visualized.

What is Pigsty?

Pigsty is a batteries-included PostgreSQL distribution that bundles production-grade cluster deployment, scaling, replication, failover, traffic routing, connection pooling, service discovery, access control, monitoring, alerting, and logging into a single cohesive package. It solves the hard problems you’ll face when running PostgreSQL — the world’s most advanced open-source relational database — in production.

Role Description
Distribution Batteries-included PostgreSQL distribution
Monitoring Professional-grade PostgreSQL observability
Deployment Simple, HA-ready deployment solution
Sandbox Versatile local sandbox & data visualization environment
Open Source Free as in freedom, Apache 2.0 licensed

Core Features

whatwherewho

Distribution

A distribution is a complete solution built from a database kernel plus a curated set of software packages. Linux is an OS kernel; RedHat, Debian, and SUSE are distributions built on top of it. PostgreSQL is a database kernel; Pigsty, BigSQL, Percona, and various cloud RDS offerings are distributions built on top of it.

distro

As a database distribution, Pigsty’s core strengths are:

  • Comprehensive, professional monitoring system
  • Simple, easy-to-use deployment solution
  • Stable, reliable high-availability architecture
  • Versatile, powerful sandbox environment
  • Free, friendly open-source license

Batteries Included

Batteries-included means: start with a fresh VM, run one command, and within 10 minutes you’ll have infrastructure, database, monitoring, and control plane fully operational.

Pigsty pushes deployment and monitoring to the extreme, turning the historically high-barrier work of deploying, managing, and operating large-scale database clusters into something any developer can handle.

For power users, Pigsty provides the most comprehensive monitoring system available. For everyone else, Pigsty provides the simplest deployment experience. For data engineers, Pigsty also integrates tools like JupyterLab and ECharts, making it a complete IDE for data development and visualization.

battery

Monitoring System

Pigsty ships with a professional-grade PostgreSQL monitoring system designed for large-scale database fleet management. It includes ~1200 metric types, 20+ dashboards, and thousands of panels, covering everything from fleet-wide overviews down to individual object details. Compared to alternatives, it leads by a wide margin in metric coverage and dashboard richness — delivering irreplaceable value for professionals.

A typical Pigsty deployment can manage hundreds of database clusters, collect thousands of metric types, handle millions of time series, and organize them into thousands of panels across dozens of dashboards in real-time. From global fleet overviews to per-object details (tables, queries, indexes, functions), it’s like having a real-time MRI/CT scanner for your database — everything laid bare.

dashboards

Dashboard gallery

pgsql-overview

Single query monitoring

pgsql-query

Single table monitoring

pgsql-table

Instance-level monitoring dashboard

pgsql-instance

Three Core Applications

Pigsty’s monitoring system is composed of three tightly integrated core applications:

PGSQL — Collect and visualize monitoring metrics

pgsql-instance

PGCAT — Browse database system catalogs directly

pgcat

PGLOG — Real-time log search and analysis

pglog

Pigsty’s monitoring system is built on industry best practices, using Prometheus and Grafana as the monitoring infrastructure. Open source, easy to customize, reusable, portable, no vendor lock-in. It can integrate with existing PostgreSQL instances and can also be used to monitor and manage other databases or applications (like Redis).

Deployment Solution

A database is software that manages data. A control plane is software that manages databases.

Pigsty includes an Ansible-based database management solution, with CLI and GUI wrappers on top. It handles core database management functions: cluster creation, destruction, scaling; user, database, and service provisioning.

Pigsty embraces the Infrastructure as Code philosophy, using Kubernetes-style declarative configuration. Describe your database and runtime environment through extensive config options, and idempotent playbooks automatically create the clusters you need — delivering a private-cloud-like experience.

Users simply describe “what kind of database they want” via config files or GUI — no need to worry about how Pigsty creates or modifies it. Pigsty will spin up the desired database cluster from bare metal nodes within minutes.

iac

For users who prefer not to work with config files and Ansible playbooks, Pigsty also offers optional CMDB mode and CLI/GUI tools wrapping common operations.

gui

For power users, Pigsty provides 160+ configurable parameters, allowing fine-grained control over every aspect of cluster and infrastructure runtime. Beginners can create reliable database clusters without changing a single config setting.

High Availability Clusters

Pigsty creates distributed, highly-available database clusters. In practice, as long as any instance in the cluster survives, the cluster can provide full read-write and read-only services.

Every database instance in the cluster is functionally equivalent — any instance can serve full read-write traffic through the built-in load balancer. Clusters automatically detect failures and perform primary-replica failover; typical failures self-heal in seconds to tens of seconds, with read-only traffic unaffected during the process.

Pigsty’s HA architecture is battle-tested in production, achieving complete high availability with minimal complexity — making traditional primary-replica databases feel like distributed databases.

ha-arch

Default access topology (DNS + L2 VIP + HAProxy, 7 options total)

failover

Sandbox Environment

PostgreSQL users aren’t just enterprises — countless individuals use it for software development, testing, experiments, demos, or for data cleaning, analysis, visualization, and storage. Setting up the environment is often the first hurdle.

The Pigsty sandbox solves this problem. With one click, spin up a complete production-grade PostgreSQL service on your laptop or PC (Vagrant calls VirtualBox to automatically create the VMs). The default sandbox is single-node (2c/4g) with all essential tools, suitable for various use cases. There’s also a four-node full sandbox for production-like environments, fully exploring Pigsty’s HA architecture and monitoring capabilities.

sandbox

Four-node sandbox architecture diagram

Data Analysis

Pigsty provides PostgreSQL as the backend database, JupyterLab as the Python IDE, Grafana as the frontend/backend runtime, and the Grafana ECharts Panel for advanced visualization. Together, these tools form a complete toolkit for data processing, analysis, and data application development.

Build your data analysis workflow on Pigsty, rapidly prototype data application POCs, and package/distribute/deploy them in a standardized way. Pigsty ships with two sample data applications:

COVID — Pandemic data visualization app

covid

Click to view individual country details and timeline maps

ISD — Global surface weather station historical data explorer

isd

Click to view individual station details and historical weather data

Roadmap

roadmap-1roadmap-2

Open Source

Pigsty is open-source under the Apache 2.0 license — free for commercial use, with modifications and derivatives subject to Apache License 2.0’s attribution requirements.

Pigsty’s mission: Use databases well, use good databases.

Give SMBs a truly self-controlled choice, and let everyone enjoy the power of PostgreSQL.

v1.0.0 Release Notes

Monitoring System Overhaul

  • New dashboards on Grafana 8.0
  • New metric definitions, added PG14 support
  • Simplified labeling system: static label set (job, cls, ins)
  • New alerting rules and derived metrics
  • Monitor multiple databases simultaneously
  • Real-time log search & csvlog analysis
  • Richly-linked dashboards, click through for drill-down/roll-up

Architecture Changes

  • Added Citus and TimescaleDB to default installation
  • Added PostgreSQL 14beta2 support
  • Simplified HAProxy admin page indexing
  • Decoupled infrastructure and PGSQL by adding new role register
  • Added new roles loki and promtail for logging
  • Added new role environ to setup environment for admin user on meta node
  • Default to static service discovery for Prometheus (instead of consul)
  • Added new role remove for graceful cluster and instance removal
  • Upgraded Prometheus and Grafana provisioning logic
  • Upgraded to vip-manager 1.0, node_exporter 1.2, pg_exporter 0.4, Grafana 8.0
  • Every database on every instance auto-registers as a Grafana datasource
  • Moved Consul registration to register role, changed Consul service tags
  • Added cmdb.sql as pg-meta baseline definition (CMDB & PGLOG)

Application Framework

  • Extensible framework for new features
  • Core app: PostgreSQL monitoring system pgsql
  • Core app: PostgreSQL catalog explorer pgcat
  • Core app: PostgreSQL csvlog analyzer pglog
  • Added sample app covid for COVID-19 data visualization
  • Added sample app isd for ISD weather data visualization

Other

  • Added JupyterLab for full Python data science environment
  • Added vonng-echarts-panel to restore ECharts support
  • Added wrapper scripts createpg, createdb, createuser
  • Added CMDB dynamic inventory scripts: load_conf.py, inventory_cmdb, inventory_conf
  • Removed obsolete playbooks: pgsql-monitor, pgsql-service, node-remove, etc.

API Changes

  • New variable: node_meta_pip_install
  • New variable: grafana_admin_username
  • New variable: grafana_database
  • New variable: grafana_pgurl
  • New variable: pg_shared_libraries
  • New variable: pg_exporter_auto_discovery
  • New variable: pg_exporter_exclude_database
  • New variable: pg_exporter_include_database
  • Variable renamed: grafana_url → grafana_endpoint

Bug Fixes

  • Fixed default timezone Asia/Shanghai (CST) issue
  • Fixed nofile limits for pgbouncer & patroni
  • pgbouncer user list and database list now generated when running tag pgbouncer

v1.0.1 Release Notes

2021-09-14

Documentation Update

  • Chinese documentation now available
  • Machine-translated English documentation now available

Bug Fixes

  • pgsql-remove no longer removes primary instances
  • Replaced pg_instance with pg_cluster + pg_seq (Start-At-Task could fail when pg_instance undefined)
  • Removed Citus from default shared preload libraries (Citus forces max_prepared_transaction to non-zero)
  • Added ssh sudo check in configure (now uses ssh -t sudo -n ls for permission check)
  • Fixed pg-backup script typo

Optimizations

  • Removed NTP sanity check alert (duplicate of ClockSkew)
  • Removed collector.systemd to reduce overhead

2.14 - Ready-to-Use PostgreSQL Distribution: Pigsty

Originally published on VONNG.

What is Pigsty

Pigsty is a ready-to-use production-grade open-source PostgreSQL distribution.

A distribution refers to a complete database solution consisting of a database kernel and its suite of software packages. For example, Linux is an operating system kernel, while RedHat, Debian, and SUSE are operating system distributions based on this kernel. PostgreSQL is a database kernel, while Pigsty, BigSQL, Percona, various cloud RDS services, and rebranded databases are database distributions based on this kernel.

Pigsty differs from other database distributions with five core features:

  • Comprehensive and professional monitoring system
  • Stable and reliable deployment solution
  • Simple and worry-free user interface
  • Flexible and open extension mechanism
  • Free and friendly open-source license

These five characteristics make Pigsty truly a ready-to-use PostgreSQL distribution.

Who Would Be Interested?

Pigsty’s target user groups include: DBAs, architects, OPS personnel, software vendors, cloud vendors, business developers, kernel developers, data developers; people interested in data analysis and data visualization; students, novice programmers, and users interested in trying databases.

For professional users like DBAs and architects, Pigsty provides a unique professional-grade PostgreSQL monitoring system, offering irreplaceable value for database management. Additionally, Pigsty comes with a stable and reliable, battle-tested production-grade PostgreSQL deployment solution that can automatically deploy PostgreSQL database clusters with monitoring and alerting, log collection, service discovery, connection pooling, load balancing, VIP, and high availability in production environments.

For developers (business developers, kernel developers, data developers), students, novice programmers, and users interested in trying databases, Pigsty provides an extremely low-barrier, one-click startup, one-click installation local sandbox. The local sandbox is identical to production environments except for machine specifications, including complete functionality: ready-to-use database instances and monitoring systems. It can be used for learning, development, testing, data analysis, and other scenarios.

Additionally, Pigsty provides a flexible extension mechanism called “Datalet.” People interested in data analysis and data visualization might be surprised to find that Pigsty can also serve as an integrated development environment for data analysis and visualization. Pigsty integrates PostgreSQL with common data analysis plugins and comes with Grafana and embedded Echarts support, allowing users to write, test, and distribute data mini-applications (Datalets). Such as: “Additional extension panel packages for Pigsty monitoring system,” “Redis monitoring system,” “PG log analysis system,” “application monitoring,” “data directory browser,” etc.

Finally, Pigsty adopts the free and friendly Apache License 2.0, which can be used commercially for free. As long as you comply with Apache 2 License’s attribution clauses, cloud vendors and software vendors are welcome to integrate and commercially develop secondary products.

Comprehensive Professional Monitoring System

You can’t manage what you don’t measure.

— Peter F.Drucker

Pigsty provides a professional-grade monitoring system, offering irreplaceable value to professional users.

Using medical equipment as an analogy, ordinary monitoring systems are like heart rate monitors and pulse oximeters that ordinary people can use without training. They can provide core vital sign indicators for patients: at least users can know if someone is about to die, but they’re powerless for diagnosis and treatment. For example, monitoring systems provided by various cloud and software vendors generally fall into this category: a dozen core indicators that tell you whether the database is still alive, giving people a rough idea, and that’s all.

Professional-grade monitoring systems are like CT scanners and MRI machines that can detect all internal details of objects. Professional physicians can quickly locate diseases and hidden dangers based on CT/MRI reports: treat diseases when present, maintain health when absent. Pigsty can deeply examine every table, every index, every query in every database, providing comprehensive metrics (1155 types) and converting them into insights through thousands of dashboards: nipping failures in the bud and providing real-time feedback for performance optimization.

Pigsty’s monitoring system is based on industry best practices, using Prometheus and Grafana as monitoring infrastructure. It’s open-source, highly customizable, reusable, portable, with no vendor lock-in. It can integrate with various existing database instances.

Stable Reliable Deployment Solution

A complex system that works is invariably found to have evolved from a simple system that works.

—John Gall, Systemantics (1975)

Databases are software for managing data; management systems are software for managing databases.

Pigsty has a built-in database management solution centered on Ansible. Based on this, it encapsulates command-line tools and graphical interfaces. It integrates core functions in database management: including database cluster creation, destruction, scaling; user, database, and service creation, etc. Pigsty adopts the “Infra as Code” design philosophy, using declarative configuration to describe and customize databases and runtime environments through numerous optional configuration options, and automatically creates required database clusters through idempotent preset playbooks, providing a near-private-cloud user experience.

Database clusters created by Pigsty are distributed and highly available. Pigsty-created databases achieve high availability based on DCS, Patroni, and Haproxy. Each database instance in a database cluster is idempotent in usage - any instance can provide complete read-write services through built-in load balancing components, offering a distributed database user experience. Database clusters can automatically perform failure detection and master-slave switching. Ordinary failures can self-heal in seconds to tens of seconds, with read-only traffic unaffected during this period. During failures, as long as any instance in the cluster survives, it can provide complete services externally.

Pigsty’s architectural solution has been carefully designed and evaluated, focusing on achieving required functionality with minimal complexity. This solution has been validated in production environments for long periods and large scales, and has been adopted by organizations in multiple industries including internet/B/G/M/F.

Simple Worry-Free User Interface

Pigsty aims to lower PostgreSQL’s usage barrier, so extensive work has been done on usability.

Installation Deployment

Someone told me that each equation I included in the book would halve the sales.

— Stephen Hawking

Pigsty deployment consists of three steps: download source code, configure environment, execute installation, all can be completed with one command. It follows the classic software installation pattern and provides a configuration wizard. All you need to prepare is a CentOS7.8 machine and root privileges. When managing new nodes, Pigsty uses Ansible to initiate management via ssh without requiring agent installation, making it easy even for novices to complete deployment.

Pigsty can manage hundreds of high-spec production nodes in production environments, and can also run independently on local 1-core 1GB virtual machines as ready-to-use database instances. When used on local computers, Pigsty provides a sandbox based on Vagrant and Virtualbox. It can spin up database environments identical to production with one command, for learning, development, testing, data analysis, data visualization, and other scenarios.

User Interface

Clearly, we must break away from the sequential and not limit the computers. We must state definitions and provide for priorities and descriptions of data. We must state relation‐ ships, not procedures.

—Grace Murray Hopper, Management and the Computer of the Future (1962)

Pigsty incorporates the essence of Kubernetes architectural design, adopting declarative configuration and idempotent operation playbooks. Users only need to describe “what kind of database they want” without caring how Pigsty creates or modifies it. Pigsty will create the required database cluster from bare metal nodes in minutes according to user configuration file manifests.

For management and usage, Pigsty provides different levels of user interfaces to meet different user needs. Novice users can use one-click local sandboxes and graphical user interfaces, while developers can choose to use pigsty-cli command-line tools and configuration files for management. Experienced DBAs, operations staff, and architects can directly use Ansible primitives for fine control over executed tasks.

Flexible Open Extension Mechanism

PostgreSQL’s extensibility has always been praised, with various extension plugins making PostgreSQL the most advanced open-source relational database. Pigsty also respects this value, providing an extension mechanism called “Datalet” that allows users and developers to further customize Pigsty for “unexpected” use cases, such as: data analysis and visualization.

When we have monitoring systems and management solutions, we also have the ready-to-use visualization platform Grafana and the powerful database PostgreSQL. This combination has tremendous power — especially for data-intensive applications. Users can perform data analysis and data visualization without writing frontend or backend code, creating richly interactive data application prototypes, or even the applications themselves.

Pigsty integrates Echarts and common map base layers, making it easy to implement advanced visualization needs. Compared to traditional scientific computing languages/plotting libraries like Julia, Matlab, and R, the PG + Grafana + Echarts combination allows you to create shareable, deliverable, standardized data applications or visualization works at extremely low cost.

Pigsty’s monitoring system itself is an exemplar of Datalet: all Pigsty advanced topic monitoring panels are released as Datalets. Pigsty also comes with some interesting Datalet examples: Redis monitoring system, COVID-19 data analysis, 7th population census data analysis, PG log mining, etc. More ready-to-use Datalets will be added later, continuously expanding Pigsty’s functionality and application scenarios.

Free Friendly Open-Source License

Once open source gets good enough, competing with it would be insane.

Larry Ellison —— Oracle CEO

In the software industry, open source is a major trend. The history of the internet is the history of open-source software. One core reason the IT industry has today’s prosperity and people can enjoy so many free information services is open-source software. Open source is a truly successful form of communism by developers (translating as community-ism would be more appropriate): software, the core means of production in the IT industry, becomes publicly owned by developers worldwide — everyone for me, me for everyone.

When an open-source programmer works, their labor might actually contain the crystallized wisdom of tens of thousands of top developers. Through open source, all community developers form a united force, greatly reducing the internal friction of reinventing wheels and allowing the entire industry’s technical level to advance at an unimaginable speed. Open source’s momentum is like a snowball that has become unstoppable today. Except for some special scenarios and path dependencies, doing closed-door development for self-reliance in software development has become a big joke.

Relying on open source, giving back to open source. Pigsty adopts the friendly Apache License 2.0, which can be used commercially for free. As long as you comply with Apache 2 License’s attribution clauses, cloud vendors and software vendors are welcome to integrate and develop secondary commercial products.

About Pigsty

A system cannot be successful if it is too strongly influenced by a single person. Once the initial design is complete and fairly robust, the real test begins as people with many different viewpoints undertake their own experiments. — Donald Knuth

Pigsty is built around the open-source database PostgreSQL. PostgreSQL is the world’s most advanced open-source relational database, and Pigsty’s goal is to be the best open-source PostgreSQL distribution.

Initially, Pigsty didn’t have such grand goals. Because I couldn’t find any monitoring system on the market that met my needs, I had to roll up my sleeves and make one myself. Unexpectedly, it worked exceptionally well, and quite a few external organizations and PG users hoped to use it. Subsequently, deployment and delivery of the monitoring system became a problem, so the database deployment management part was added; after production environment application, developers wanted local sandbox environments for testing, so local sandboxes were added; users complained that ansible wasn’t user-friendly, so the pigsty-cli command-line tool wrapper was created; users wanted to edit configuration files through UI, so Pigsty GUI was born. Like this, needs grew and features became richer, Pigsty became more complete through long-term polishing, far exceeding initial expectations.

Doing this is itself a challenge — making a distribution is somewhat like making a RedHat, making a SUSE, making an “RDS product.” Usually only professional companies and teams of a certain scale would attempt this. But I just wanted to try: is it possible for one person? Actually, besides being slower, there’s nothing impossible. Switching between product manager, developer, and end-user roles is a very interesting experience, and the biggest benefit of “eating dog food” is that you’re both developer and user — you know what you need and won’t slack off on your own requirements.

However, as Knuth said: “A system with too strong a personal touch cannot succeed.” To make Pigsty a project with vigorous vitality, it must be open-sourced and used by more people. “When the initial design is complete and stable enough, the real challenge begins when various users use it in their own ways.”

Pigsty has solved my own problems and needs very well. Now I hope it can help more people and make PostgreSQL’s ecosystem more prosperous and colorful.

2.15 - Pigsty v0.9: CLI + Logs

Originally published on VONNG.

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.9.0

New Stuff

  • One-liner install: curl -fsSL https://pigsty.cc/install | bash bootstraps everything.
  • pigsty-cli: wraps the common Ansible playbooks so you stop copy-pasting command lines. Still beta but already handy.
  • Loki + Promtail: Postgres, pgbouncer, and Patroni logs stream into Grafana with metrics extracted from log volume. infra-loki.yml and pgsql-promtail.yml wire things up.
  • Binary exporters: grab monitoring binaries with files/get_bin.sh if you don’t want to rely on repos.
  • Flight mode: once the meta node is initialized you can run bin/upgrade to switch into a dynamic inventory using data stored inside pg-meta.

Fixes

  • Cleaned up HAProxy health checks that were flooding PG and Patroni logs with connection reset noise.
  • Patroni logs now carry readable timestamps (no more millisecond fragments) and explicit time zones.
  • Monitoring queries run by dbuser_monitor log only when slower than 1s.
  • Grafana role refactor keeps the API stable, but uses CDN-hosted plugin bundles for faster installs.
  • Pgbouncer user creation now handles md5 passwords properly.
  • Hardened SQL templates for DB/user creation, fixed DNS orchestration edge cases, and tidied Makefile typos.

Knob Changes

  • node_disable_swap defaults to false; Pigsty no longer nukes swap by default.
  • node_sysctl_params stops writing kernel tunables unless you explicitly set them.
  • grafana_plugin: install now means “download from CDN if cache is missing.”
  • repo_url_packages pulls extra RPMs from the Pigsty CDN so installs inside China work out of the box.
  • proxy_env.no_proxy includes the CDN endpoints.
  • grafana_customize defaults to false; flip it on only if you have the Pigsty Pro UI bits.
  • node_admin_pk_current adds your current ~/.ssh/id_rsa.pub to the admin account.
  • Loki/Promtail knobs: loki_clean, loki_data_dir, promtail_enabled, promtail_clean, promtail_port, promtail_status_file, promtail_send_url.

2.16 - Pigsty v0.8: Service Provisioning

Originally published on VONNG.

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.8.0

v0.8 finalizes the provisioning API. Services are completely rebuilt: instead of a hard-coded primary/replica pair you can now declare any number of services, plug in HAProxy, swap in an external load balancer, or hand off to a custom VIP controller. Everything else in the supply chain stabilizes on top of this model.

Service API

The old vip and haproxy knobs moved under the service role. pg_services (plus pg_services_extra) define each exposed endpoint—name, ports, selectors, health checks, weights, and balancer hints. Selectors are JMESPath filters over cluster members, and optional selector_backup pools handle fail-in when replicas are gone. Out of the box we ship primary, replica, default, and offline service definitions; swap dst_port to point at postgres, pgbouncer, or any number.

The HAProxy stanza keeps per-service tuning (maxconn, algorithm, timeouts) while VIP config distinguishes L2/L4 implementations so you can drop Pigsty behind an existing load balancer.

Database Interface Tweaks

Locales can now be split into lc_collate and lc_ctype so extensions like pg_trgm behave with non-C collations. The rest of the pg_databases schema stays the same—owner/template/encoding/connlimit/revokeconn/pgbouncer/comment—just with better defaults and inline comments.

2.17 - Pigsty v0.7: Monitor-Only Deployments

Originally published on VONNG.

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.7.0

Pigsty v0.7 focuses on plugging existing fleets into Pigsty’s observability stack. The new monitor-only flow lets you drop Pigsty dashboards onto databases that were provisioned elsewhere, and the declarative APIs for databases and users got a much needed redesign.

Highlights

  • Monitor-only deployment flow (monly) with its own playbook.
  • Split static Prometheus target files by cluster for easier hand-editing.
  • New helper playbooks: pgsql-createuser.yml and pgsql-createdb.yml for live clusters.
  • Database and user schema definitions now cover owner/template/locale knobs plus per-role capabilities.
  • Bug fixes for extension schema typos and pgbouncer reload.

API Changes

New options:

prometheus_sd_target: batch
exporter_install: none
exporter_repo_url: ''
node_exporter_options: '--no-collector.softnet --collector.systemd --collector.ntp --collector.tcpstat --collector.processes'
pg_exporter_url: ''
pgbouncer_exporter_url: ''

Removed option:

exporter_binary_install

Structures affected: pg_default_roles, pg_users, pg_databases. Also fixed the pg_default_privilegs typo → pg_default_privileges.

Monitor-Only Mode

When you just want Pigsty’s observability without touching the way databases were provisioned, run the monly flow. Infra still gets bootstrapped on the meta node via ./infra.yml, but database nodes skip the provisioning playbooks and only run ./pgsql-monitor.yml. Config gets much shorter—most of the time you only keep infra vars and a handful of monitoring knobs.

Database Provisioning Interface

pg_databases now exposes owner/template/encoding/locale/connlimit/allowconn knobs plus revokeconn (strip CONNECT from public) and inline comments. Use ./pgsql-createdb.yml -e pg_database=<name> to create or mutate live databases; the generated SQL lives inside /pg/tmp/pg-db-<name>.sql on the primary.

User Provisioning Interface

pg_users swapped username → name, groups → roles, and exploded options into discrete flags (login, superuser, createdb, createrole, inherit, replication, bypassrls, connlimit). Users can also get expire_at / expire_in timers plus pgbouncer defaults to false. Apply changes through ./pgsql-createuser.yml -e pg_user=<name> which renders /pg/tmp/pg-user-<name>.sql on the primary.

2.18 - Pigsty v0.6: Provisioning Upgrades

Originally published on VONNG.

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.6.0

Pigsty v0.6 responds to user feedback with a redesigned provisioning path plus a monitoring stack that can sit beside any managed PG fleet—even a MyBase cluster built elsewhere.

Bug Fixes

  • Patroni no longer resets PG HBA on restart.
  • Fixed copy typos and the default primary for the pg-test sandbox cluster.
  • Patched the dashboard title typo on PG Overview.

Feature Work

  • Monitoring supply chain overhaul: Prometheus can now run fully static, exporters accept service_registry toggles, and exporter_binary_install lets you drop binaries without hitting repos. Each exporter has its own *_enabled flag.
  • Prometheus static discovery is rendered straight from inventory, so you can graft Pigsty dashboards onto any PG-as-a-service footprint.
  • HAProxy provisioning adds a global console at h.pigsty, optional auth, fallback routing to the primary when all replicas die, and per-service weight tuning.
  • ACL defaults now include dbrole_offline for slow-query/ETL workloads plus HBA rules that fence those workloads to marked nodes.
  • Component refresh: PostgreSQL 13.2, Prometheus 2.25, pg_exporter 0.3.2, node_exporter 1.1, Consul 1.9.3, and a faster ZJU PG mirror.

API Changes

New knobs:

service_registry: consul
prometheus_options: '--storage.tsdb.retention=30d'
prometheus_sd_method: consul
prometheus_sd_interval: 2s
pg_offline_query: false
node_exporter_enabled: true
pg_exporter_enabled: true
pgbouncer_exporter_enabled: true
dcs_disable_purge: false
pg_disable_purge: false
haproxy_weight: 100
haproxy_weight_fallback: 1

Removed knobs:

prometheus_metrics_path
prometheus_retention

2.19 - Pigsty v0.5: Declarative DB Templates

Originally published on VONNG.

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.5.0

v0.5.0

Outline

  • The official docs site (http://pigsty.cc/) is live.
  • Database templating becomes fully declarative: define users, roles, databases, ACLs, extensions, and schemas in config.
  • The default access model is refined and HBA management now comes straight from Pigsty instead of Patroni.
  • Grafana provisioning switched from shoving a sqlite file to JSON provisioning via API.
  • Added the pg-cluster-replication dashboard to the open bundle.
  • CentOS 7.8 offline bundle: pkg.tgz.

Declarative Database Layouts

Multi-tenant headaches go away once everything is described as code. The new templates let you declare users, passwords, role hierarchies, DB defaults, extensions, schemas, and default privileges in YAML so a single config file replaces piles of runbooks. A stripped example:

# per-cluster settings
pg_users:
  - username: test
    password: test
    comment: default test user
    groups: [ dbrole_readwrite ]
pg_databases:
  - name: test
    extensions: [{name: postgis}]
    parameters:
      search_path: public,monitor

# environment-wide system roles
pg_replication_username: replicator
pg_replication_password: DBUser.Replicator
pg_monitor_username: dbuser_monitor
pg_monitor_password: DBUser.Monitor
pg_admin_username: dbuser_admin
pg_admin_password: DBUser.Admin

# default roles
pg_default_roles:
  - username: dbrole_readonly
    options: NOLOGIN
    comment: role for readonly access

  - username: dbrole_readwrite
    options: NOLOGIN
    comment: role for read-write access
    groups: [ dbrole_readonly ]

  - username: dbrole_admin
    options: NOLOGIN BYPASSRLS
    comment: role for object creation
    groups: [dbrole_readwrite,pg_monitor,pg_signal_backend]

  - username: postgres
    options: SUPERUSER LOGIN
    comment: system superuser

  - username: replicator
    options: REPLICATION LOGIN
    groups: [pg_monitor, dbrole_readonly]
    comment: system replicator

  - username: dbuser_monitor
    options: LOGIN CONNECTION LIMIT 10
    comment: system monitor user
    groups: [pg_monitor, dbrole_readonly]

  - username: dbuser_admin
    options: LOGIN BYPASSRLS
    comment: system admin user
    groups: [dbrole_admin]

  - username: dbuser_stats
    password: DBUser.Stats
    options: LOGIN
    comment: business read-only user for statistics
    groups: [dbrole_readonly]

# default privileges applied to dbsu/admin objects
pg_default_privilegs:
  - GRANT USAGE                         ON SCHEMAS   TO dbrole_readonly
  - GRANT SELECT                        ON TABLES    TO dbrole_readonly
  - GRANT SELECT                        ON SEQUENCES TO dbrole_readonly
  - GRANT EXECUTE                       ON FUNCTIONS TO dbrole_readonly
  - GRANT INSERT, UPDATE, DELETE        ON TABLES    TO dbrole_readwrite
  - GRANT USAGE,  UPDATE                ON SEQUENCES TO dbrole_readwrite
  - GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES    TO dbrole_admin
  - GRANT CREATE                        ON SCHEMAS   TO dbrole_admin
  - GRANT USAGE                         ON TYPES     TO dbrole_admin

pg_default_schemas: [monitor]

pg_default_extensions:
  - { name: 'pg_stat_statements',  schema: 'monitor' }
  - { name: 'pgstattuple',         schema: 'monitor' }
  - { name: 'pg_qualstats',        schema: 'monitor' }
  - { name: 'pg_buffercache',      schema: 'monitor' }
  - { name: 'pageinspect',         schema: 'monitor' }
  - { name: 'pg_prewarm',          schema: 'monitor' }
  - { name: 'pg_visibility',       schema: 'monitor' }
  - { name: 'pg_freespacemap',     schema: 'monitor' }
  - { name: 'pg_repack',           schema: 'monitor' }
  - name: postgres_fdw
  - name: file_fdw
  - name: btree_gist
  - name: btree_gin
  - name: pg_trgm
  - name: intagg
  - name: intarray

pg_hba_rules:
  - title: allow meta node password access
    role: common
    rules:
      - host    all     all                         10.10.10.10/32      md5

  - title: allow intranet admin password access
    role: common
    rules:
      - host    all     +dbrole_admin               10.0.0.0/8          md5
      - host    all     +dbrole_admin               172.16.0.0/12       md5
      - host    all     +dbrole_admin               192.168.0.0/16      md5

  - title: allow intranet password access
    role: common
    rules:
      - host    all             all                 10.0.0.0/8          md5
      - host    all             all                 172.16.0.0/12       md5
      - host    all             all                 192.168.0.0/16      md5

  - title: allow local read-write access
    role: common
    rules:
      - local   all     +dbrole_readwrite                               md5
      - host    all     +dbrole_readwrite           127.0.0.1/32        md5

  - title: allow read-only access
    role: replica
    rules:
      - local   all     +dbrole_readonly                               md5
      - host    all     +dbrole_readonly           127.0.0.1/32        md5
pg_hba_rules_extra: []

pgbouncer_hba_rules:
  - title: local password access
    role: common
    rules:
      - local  all          all                                     md5
      - host   all          all                     127.0.0.1/32    md5

  - title: intranet password access
    role: common
    rules:
      - host   all          all                     10.0.0.0/8      md5
      - host   all          all                     172.16.0.0/12   md5
      - host   all          all                     192.168.0.0/16  md5
pgbouncer_hba_rules_extra: []

Templates and Permissions

Two SQL templates (pg-init-template.sql for template1 and pg-init-business.sql for business databases) now give you hooks to seed any custom logic. The default ACL layout was tightened for multi-tenant instances: regular users no longer get implicit CONNECT on foreign databases, CREATE on their own DB, or CREATE inside public.

Provisioning Updates

Grafana provisioning now happens through the API, so you can feed dashboards into an existing Grafana by simply pointing grafana_url at a username/password endpoint. Pigsty generates HBAs on its own so Patroni stays focused on HA, and the supply chain is cleaner.

2.20 - Pigsty v0.4: PG13 and Better Docs

Originally published on VONNG.

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.4.0

Pigsty v0.4 is our second public beta. The observability stack was rebuilt around Grafana 7.3, and ten curated dashboards became the default open-source payload. pg_exporter 0.3.1 drives metrics, and the alert wiring has been cleaned up for the new Grafana release.

Open-Source Dashboards

The OSS build now exposes ten high-signal Grafana panels: PG Overview, Cluster, Service, Instance, Database, Query, Table, Table Catalog, Table Detail, and Node. Even with a lean set it easily outclasses most “enterprise” PG monitoring suites.

Software Refresh

  • PostgreSQL 13.1 + Patroni 2.0.1-4, with citus added to the repo
  • pg_exporter upgraded to 0.3.1
  • Grafana jumps to 7.3; a ton of compatibility fixes landed
  • Prometheus 2.23 with the new UI enabled
  • Consul 1.9 and related components updated

Other Improvements

  • Updated Prometheus alert rules and Alertmanager info links
  • Fixed a batch of bugs and typos
  • Added a tiny backup script for quick dumps

Offline Bundle

Need an air-gapped install? Grab the CentOS 7.8 package bundle (pkg.tgz) from GitHub and deploy from local media.

2.21 - Pigsty v0.3: First Public Beta

Originally published on VONNG.

GitHub Release: https://github.com/pgsty/pigsty/releases/tag/v0.3.0

Pigsty v0.3.0 is the very first public preview. It packages a lean observability stack plus a reproducible offline bundle so you can spin up a real PostgreSQL lab without touching the public Internet.

Observability Stack

The open build ships eight curated Grafana dashboards: PG Overview, Cluster, Service, Instance, Database, Table Overview, Table Catalog, and a bare-metal Node view. Even with a trimmed set the coverage still crushes most “enterprise” monitoring stories.

Offline Bundle

Shipyard environments can fetch the CentOS 7.8 offline bundle directly from GitHub (pkg.tgz). Drop it on the management node and you have a deterministic install no matter how broken the mirrors are.

3 - Design

Architecture decisions and implementation notes for Pigsty engineering.

Design notes document the context, constraints, decisions, and trade-offs behind Pigsty’s architecture and implementation.