Release Notes

Pigsty release note history
Version Time Description Release
v2.7.0 2024-05-16 Extension Overwhelming, new docker apps v2.7.0
v2.6.0 2024-02-29 PG 16 as default version, ParadeDB & DuckDB v2.6.0
v2.5.1 2023-12-01 Routine update, pg16 major extensions v2.5.1
v2.5.0 2023-10-24 Ubuntu/Debian Support: bullseye, bookworm, jammy, focal v2.5.0
v2.4.1 2023-09-24 Supabase/PostgresML support, graphql, jwt, pg_net, vault v2.4.1
v2.4.0 2023-09-14 PG16, RDS Monitor, New Extensions v2.4.0
v2.3.1 2023-09-01 PGVector with HNSW, PG16 RC1, Chinese Docs, Bug Fix v2.3.1
v2.3.0 2023-08-20 PGSQL/REDIS Update, NODE VIP, Mongo/FerretDB, MYSQL Stub v2.3.0
v2.2.0 2023-08-04 Dashboard & Provision overhaul, UOS compatibility v2.2.0
v2.1.0 2023-06-10 PostgreSQL 12 ~ 16beta support v2.1.0
v2.0.2 2023-03-31 Add pgvector support and fix MinIO CVE v2.0.2
v2.0.1 2023-03-21 v2 Bug Fix, security enhance and bump grafana version v2.0.1
v2.0.0 2023-02-28 Compatibility Security Maintainability Enhancement v2.0.0
v1.5.1 2022-06-18 Grafana Security Hotfix v1.5.1
v1.5.0 2022-05-31 Docker Applications v1.5.0
v1.4.1 2022-04-20 Bug fix & Full translation of English documents. v1.4.1
v1.4.0 2022-03-31 MatrixDB Support, Separated INFRA, NODES, PGSQL, REDIS v1.4.0
v1.3.0 2021-11-30 PGCAT Overhaul & PGSQL Enhancement & Redis Support Beta v1.3.0
v1.2.0 2021-11-03 Upgrade default Postgres to 14, monitoring existing pg v1.2.0
v1.1.0 2021-10-12 HomePage, JupyterLab, PGWEB, Pev2 & Pgbadger v1.1.0
v1.0.0 2021-07-26 v1 GA, Monitoring System Overhaul v1.0.0
v0.9.0 2021-04-04 Pigsty GUI, CLI, Logging Integration v0.9.0
v0.8.0 2021-03-28 Service Provision v0.8.0
v0.7.0 2021-03-01 Monitor only deployment v0.7.0
v0.6.0 2021-02-19 Architecture Enhancement v0.6.0
v0.5.0 2021-01-07 Database Customize Template v0.5.0
v0.4.0 2020-12-14 PostgreSQL 13 Support, Official Documentation v0.4.0
v0.3.0 2020-10-22 Provisioning Solution GA v0.3.0
v0.2.0 2020-07-10 PGSQL Monitoring v6 GA v0.2.0
v0.1.0 2020-06-20 Validation on Testing Environment v0.1.0
v0.0.5 2020-08-19 Offline Installation Mode v0.0.5
v0.0.4 2020-07-27 Refactor playbooks into ansible roles v0.0.4
v0.0.3 2020-06-22 Interface enhancement v0.0.3
v0.0.2 2020-04-30 First Commit v0.0.2
v0.0.1 2019-05-15 POC v0.0.1

v2.7.0

Highlight

Extension Overwhelming, adding numerous new extensions written in rust & pgrx:

  • pg_search v0.7.0 : Full text search over SQL tables using the BM25 algorithm
  • pg_lakehouse v0.7.0 : Query engine over object stores like S3 and table formats like Delta Lake
  • pg_analytics v0.6.1 : Accelerates analytical query processing inside Postgres
  • pg_graphql v1.5.4 : GraphQL support to your PostgreSQL database.
  • pg_jsonschema v0.3.1 : PostgreSQL extension providing JSON Schema validation
  • wrappers v0.3.1 : Postgres Foreign Data Wrappers Collections by Supabase
  • pgmq v1.5.2 : A lightweight message queue. Like AWS SQS and RSMQ but on Postgres.
  • pg_tier v0.0.3 : Postgres Extension written in Rust, to enable data tiering to AWS S3
  • pg_vectorize v0.15.0 : The simplest way to orchestrate vector search on Postgres
  • pg_later v0.1.0 : Execute SQL now and get the results later.
  • pg_idkit v0.2.3 : Generating many popular types of identifiers
  • plprql v0.1.0 : Use PRQL in PostgreSQL
  • pgsmcrypto v0.1.0 : PostgreSQL SM Algorithm Extension
  • pg_tiktoken v0.0.1 : OpenAI tiktoken tokenizer for postgres
  • pgdd v0.5.2 : Access Data Dictionary metadata with pure SQL

And some new extensions in plain C & C++

  • parquet_s3_fdw 1.1.0 : ParquetS3 Foreign Data Wrapper for PostgresSQL
  • plv8 3.2.2 : V8 Engine Javascript Procedural Language add-on for PostgreSQL
  • md5hash 1.0.1 : Custom data type for storing MD5 hashes rather than text
  • pg_tde 1.0 alpha: Experimental encrypted access method for PostgreSQL
  • pg_dirtyread 2.6 : Read dead but unvacuumed tuples from a PostgreSQL relation
  • New deb PGDG extensions: pg_roaringbitmap, pgfaceting, mobilitydb, pgsql-http, pg_hint_plan, pg_statviz, pg_rrule
  • New rpm PGDG extensions: pg_profile, pg_show_plans, use PGDG’s pgsql_http, pgsql_gzip, pg_net, pg_bigm instead of Pigsty RPM.

New Features

  • running on certain docker containers.
  • prepare arm64 packages for infra & pgsql packages for el & deb distros.
  • new installation script to download from cloudflare, and more hint.
  • new monitoring dashboard for PGSQL PITR to assist the PITR procedure.
  • make preparation for running pigsty inside docker VM containers
  • add a fool-proof design for running pgsql.yml on node that is not managed by pigsty
  • add config template for each major version: el7, el8, el9, debian11, debian12, ubuntu20, ubuntu22

Software Upgrade

  • 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 (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 recompile with 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)

Docker Application

  • Odoo: launch open source ERP and plugins
  • Jupyter: run jupyter notebook container
  • PolarDB: run the demo PG RAC playground.
  • supabase: bump to the latest GA version.
  • bytebase: use the latest tag instead of ad hoc version.
  • pg_exporter: update docker image example

Bug Fix

  • Fix role pg_exporters white space in variable templates
  • Fix minio_cluster not commented in global variables
  • Fix the non-exist postgis34 in el7 config template
  • Fix EL8 python3.11-cryptography deps to python3-cryptography according to upstream
  • Fix /pg/bin/pg-role can not get OS user name from environ in non-interact mode
  • Fix /pg/bin/pg-pitr can not hint -X -P flag properly

API Change

  • New parameter node_write_etc_hosts to control whether to write /etc/hosts file on target nodes.
  • Relocatable prometheus target directory with new parameter prometheus_sd_dir.
  • Add -x|--proxy flag to enable and use value of global proxy env by @waitingsong in https://github.com/Vonng/pigsty/pull/405
  • No longer parse infra nginx log details since it brings too much labels to the log.
  • Use alertmanager API Version v2 instead of v1 in prometheus config.
  • Use /pg/cert/ca.crt instead of /etc/pki/ca.crt in pgsql roles.

New Contributors

Package Checksums

ec271a1d34b2b1360f78bfa635986c3a  pigsty-pkg-v2.7.0.el8.x86_64.tgz
f3304bfd896b7e3234d81d8ff4b83577  pigsty-pkg-v2.7.0.debian12.x86_64.tgz
5b071c2a651e8d1e68fc02e7e922f2b3  pigsty-pkg-v2.7.0.ubuntu22.x86_64.tgz

v2.6.0

Highlight

Configuration

  • Disable Grafana Unified Alert to work around the “Database Locked” error。
  • add node_repo_modules to add upstream repos (including local one) to node
  • remove node_local_repo_urls, replaced by node_repo_modules & repo_upstream.
  • remove node_repo_method, replaced by node_repo_modules.
  • add the new local repo into repo_upstream instead of node_local_repo_urls
  • add chrony into node_default_packages
  • remove redis,minio,postgresql client from infra packages
  • replace repo_upstream.baseurl $releasever for pgdg el8/el9 with major.minor instead of major version

Software Upgrade

  • Grafana 10.3.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
  • HAProxy 2.9.5
  • DuckDB 0.10.0
  • FerretDB 1.19
  • Metabase: new docker compose app template added

PostgreSQL x Pigsty Extensions

  • PostgreSQL Minor Version Upgrade 16.2, 15.6, 14.11, 13.14, 12.18
  • PostgreSQL 16 is now used as the default major version
  • pg_exporter 0.6.1, security fix
  • Patroni 3.2.2
  • pgBadger 12.4
  • pgBouncer 1.22
  • pgBackRest 2.50
  • vip-manager 2.3.0
  • PostGIS 3.4.1
  • PGVector 0.6.0
  • TimescaleDB 2.14.1
  • New Extension duckdb_fdw v1.1
  • New Extension pgsql-gzip v1.0.0
  • New Extension pg_sparse from ParadeDB: v0.5.6
  • New Extension pg_bm25 from ParadeDB: v0.5.6
  • New Extension pg_analytics from ParadeDB: v0.5.6
  • Bump AI/ML Extension pgml to v2.8.1 with pg16 support
  • Bump Columnar Extension hydra to v1.1.1 with pg16 support
  • Bump Graph Extension age to v1.5.0 with pg16 support
  • Bump Packaging Extension pg_tle to v1.3.4 with pg16 support
  • Bump GraphQL Extension pg_graphql to v1.5.0 to support supabase
330e9bc16a2f65d57264965bf98174ff  pigsty-v2.6.0.tgz
81abcd0ced798e1198740ab13317c29a  pigsty-pkg-v2.6.0.debian11.x86_64.tgz
7304f4458c9abd3a14245eaf72f4eeb4  pigsty-pkg-v2.6.0.debian12.x86_64.tgz
f914fbb12f90dffc4e29f183753736bb  pigsty-pkg-v2.6.0.el7.x86_64.tgz
fc23d122d0743d1c1cb871ca686449c0  pigsty-pkg-v2.6.0.el8.x86_64.tgz
9d258dbcecefd232f3a18bcce512b75e  pigsty-pkg-v2.6.0.el9.x86_64.tgz
901ee668621682f99799de8932fb716c  pigsty-pkg-v2.6.0.ubuntu20.x86_64.tgz
39872cf774c1fe22697c428be2fc2c22  pigsty-pkg-v2.6.0.ubuntu22.x86_64.tgz

v2.5.1

Routine update with v16.1, v15.5, 14.10, 13.13, 12.17, 11.22

Now PostgreSQL 16 has all the core extensions available (pg_repack & timescaledb added)

  • Software Version Upgrade:
    • 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.0 (with PG 16 support)
    • Grafana v10.2.2
    • FerretDB 1.15
    • SealOS 4.3.7
    • Bytebase 2.11.1
  • Remove monitor schema prefix from PGCAT dashboard queries
  • New template wool.yml for Aliyun free ECS singleton
  • Add python3-jmespath in addition to python3.11-jmespath for el9
31ee48df1007151009c060e0edbd74de  pigsty-pkg-v2.5.1.el7.x86_64.tgz
a40f1b864ae8a19d9431bcd8e74fa116  pigsty-pkg-v2.5.1.el8.x86_64.tgz
c976cd4431fc70367124fda4e2eac0a7  pigsty-pkg-v2.5.1.el9.x86_64.tgz
7fc1b5bdd3afa267a5fc1d7cb1f3c9a7  pigsty-pkg-v2.5.1.debian11.x86_64.tgz
add0731dc7ed37f134d3cb5b6646624e  pigsty-pkg-v2.5.1.debian12.x86_64.tgz
99048d09fa75ccb8db8e22e2a3b41f28  pigsty-pkg-v2.5.1.ubuntu20.x86_64.tgz
431668425f8ce19388d38e5bfa3a948c  pigsty-pkg-v2.5.1.ubuntu22.x86_64.tgz

v2.5.0

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

Highlights

  • Ubuntu / Debian Support: bullseye, bookworm, jammy, focal

  • Dedicate yum/apt repo on repo.pigsty.cc and mirror on packagecloud.io

  • Anolis OS Support (EL 8.8 Compatible)

  • PG Major Candidate: Use PostgreSQL 16 instead of PostgreSQL 14.

  • New Dashboard PGSQL Exporter, PGSQL Patroni, rework on PGSQL Query

  • Extensions Update:

    • Bump PostGIS version to v3.4 on el8, el9, ubuntu22, keep postgis 33 on EL7
    • Remove extension pg_embedding because it is no longer maintained, use pgvector instead.
    • New extension on EL: pointcloud with LIDAR data type support.
    • New extension on EL: imgsmlrpg_similaritypg_bigm 扩展。
    • Include columnar extension hydra and remove citus from default installed extension list.
    • Recompile pg_filedump as PG major version independent package.
  • Software Version Upgrade:

    • 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

Pigsty has two ubuntu LTS support: 22.04 (jammy) and 20.04 (focal), and ship corresponding offline packages for them.

Some parameters need to be specified explicitly when deploying on Ubuntu, please refer to ubuntu.yml

  • repo_upstream: Adjust according to ubuntu / debian repo.
  • repo_packages: Adjust according to ubuntu / debian naming convention
  • node_repo_local_urls: use the default value: ['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,
    • new packages acl to ensure ansible tmp file privileges are set correctly
  • infra_packages: replace all _ with - in names, and replace postgresql16 with postgresql-client-16
  • pg_packages: replace all _ with - in names, patroni-etcd not needed on ubuntu
  • pg_extensions: different naming convention, no passwordcheck_cracklib on ubuntu.
  • pg_dbsu_uid: You have to manually specify pg_dbsu_uid on ubuntu, because PGDG deb package does not specify pg dbsu uid.

API Changes

default values of following parameters have changed:

  • repo_modules: infra,node,pgsql,redis,minio

  • repo_upstream: Now add Pigsty Infra/MinIO/Redis/PGSQL modular upstream repo.

  • repo_packages: remove unused karma,mtail,dellhw_exporter and pg 14 extra extensions, adding pg 16 extra extensions.

  • node_default_packages now add python3-pip as default packages.

  • pg_libs: timescaledb is remove from shared_preload_libraries by default.

  • pg_extensions: citus is nolonger installed by default, and passwordcheck_cracklib is installed by default

    - pg_repack_${pg_version}* wal2json_${pg_version}* passwordcheck_cracklib_${pg_version}*
    - postgis34_${pg_version}* timescaledb-2-postgresql-${pg_version}* pgvector_${pg_version}*
    
87e0be2edc35b18709d7722976e305b0  pigsty-pkg-v2.5.0.el7.x86_64.tgz
e71304d6f53ea6c0f8e2231f238e8204  pigsty-pkg-v2.5.0.el8.x86_64.tgz
39728496c134e4352436d69b02226ee8  pigsty-pkg-v2.5.0.el9.x86_64.tgz
e3f548a6c7961af6107ffeee3eabc9a7  pigsty-pkg-v2.5.0.debian11.x86_64.tgz
1e469cc86a19702e48d7c1a37e2f14f9  pigsty-pkg-v2.5.0.debian12.x86_64.tgz
cc3af3b7c12f98969d3c6962f7c4bd8f  pigsty-pkg-v2.5.0.ubuntu20.x86_64.tgz
c5b2b1a4867eee624e57aed58ac65a80  pigsty-pkg-v2.5.0.ubuntu22.x86_64.tgz

v2.4.1

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.
  • Reduce offline packages size for el9 400MB by removing proj-data*
  • Bump FerretDB version to v1.10
efabe7632d8994f3fb58f9838b8f9d7d  pigsty-pkg-v2.4.1.el7.x86_64.tgz # 1.1G
ea78957e8c8434b120d1c8c43d769b56  pigsty-pkg-v2.4.1.el8.x86_64.tgz # 1.4G
4ef280a7d28872814e34521978b851bb  pigsty-pkg-v2.4.1.el9.x86_64.tgz # 1.3G

v2.4.0

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

Highlights

  • PostgreSQL 16 support
  • The first LTS version with business support and consulting service
  • Monitoring existing PostgreSQL, RDS for PostgreSQL / PolarDB with PGRDS Dashboards
  • 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 request with SQL interface
  • Add extensions: pg_auth_mon pg_checksums pg_failover_slots pg_readonly postgresql-unit pg_store_plans pg_uuidv7 set_user
  • Redis enhancement: add monitoring panels for redis sentinel, and auto HA configuration for redis ms cluster.

API Change

  • New Parameter: REDIS.redis_sentinel_monitor: specify masters monitor by redis sentinel cluster

Bug Fix

  • Fix Grafana 10.1 registered datasource will use random uid rather than ins.datname
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

v2.3.1

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

Highlights

  • PGVector 0.5 with HNSW index support
  • PostgreSQL 16 RC1 for el8/el9 Adding SealOS for kubernetes support

Bug Fix

  • Fix infra.repo.repo_pkg task when downloading rpm with * in their names in repo_packages.
    • if /www/pigsty already have package name match that pattern, some rpm will be skipped.
  • Change default value of vip_dns_suffix to '' empty string rather than .vip
  • Grant sudo privilege for postgres dbsu when pg_dbsu_sudo = limit and patroni_watchdog_mode = required
    • /usr/bin/sudo /sbin/modprobe softdog: enable watchdog module before launching patroni
    • /usr/bin/sudo /bin/chown {{ pg_dbsu }} /dev/watchdog: chown watchdog before launching patroni

Documentation Update

  • Add details to English documentation
  • Add Chinese/zh-cn documentation

Software Upgrade

  • PostgreSQL 16 RC1 on el8/el9
  • PGVector 0.5.0 with hnsw index
  • TimescaleDB 2.11.2
  • grafana 10.1.0
  • loki & promtail 2.8.4
  • mcli-20230829225506 / minio-20230829230735
  • ferretdb 1.9
  • sealos 4.3.3
  • pgbadger 1.12.2
ce69791eb622fa87c543096cdf11f970  pigsty-pkg-v2.3.1.el7.x86_64.tgz
495aba9d6d18ce1ebed6271e6c96b63a  pigsty-pkg-v2.3.1.el8.x86_64.tgz
38b45582cbc337ff363144980d0d7b64  pigsty-pkg-v2.3.1.el9.x86_64.tgz

v2.3.0

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

Highlight

  • INFRA: NODE/PGSQL VIP monitoring support
  • NODE: Allow bind node_vip to node cluster with keepalived
  • REPO: Dedicate yum repo, enable https for get.pigsty.cc and demo.pigsty.cc
  • PGSQL: Fix CVE-2023-39417 with PostgreSQL 15.4, 14.9, 13.12, 12.16, bump patroni version to v3.1.0
  • APP: Bump app/bytebase to v2.6.0, app/ferretdb version to v1.8, new application nocodb
  • REDIS: bump to v7.2 and rework on dashboards
  • MONGO: basic deploy & monitor support with FerretDB 1.8
  • MYSQL: add prometheus/grafana/ca stub for future implementation.

API Change

Add 1 new section NODE.NODE_VIP with 8 new parameter

  • NODE.VIP.vip_enabled : enable vip on this node cluster?
  • NODE.VIP.vip_address : node vip address in ipv4 format, required if vip is enabled
  • NODE.VIP.vip_vrid : required, integer, 1-255 should be unique among same VLAN
  • NODE.VIP.vip_role : master/backup, backup by default, use as init role
  • NODE.VIP.vip_preempt : optional, true/false, false by default, enable vip preemption
  • NODE.VIP.vip_interface : node vip network interface to listen, eth0 by default
  • NODE.VIP.vip_dns_suffix : node vip dns name suffix, .vip by default
  • NODE.VIP.vip_exporter_port : keepalived exporter listen port, 9650 by default
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.2.0

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

Release Note: https://doc.pigsty.cc/#/RELEASENOTE

Highlight

  • Monitoring Dashboards Overhaul: https://demo.pigsty.cc
  • Vagrant Sandbox Overhaul: libvirt support and new templates
  • Pigsty EL Yum Repo: Building simplified
  • OS Compatibility: UOS-v20-1050e support
  • New config template: prod simulation with 42 nodes
  • Use official pgdg citus distribution for el7

Software Upgrade

  • 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 Fix

  • Fix docker group ownership issue 29434bd
  • Append infra os group rather than set it as primary group
  • Fix redis sentinel systemd enable status 5c96feb
  • Loose bootstrap & configure if /etc/redhat-release not exists
  • Fix grafana 9.x CVE-2023-1410 with 10.0.2
  • Add PG 14 - 16 new command tags and error codes for pglog schema

API Change

Add 1 new parameter

  • INFRA.NGINX.nginx_exporter_enabled : now you can disable nginx_exporter with this parameter

Default value changes:

  • repo_modules: node,pgsql,infra : redis is removed from it
  • repo_upstream:
    • add pigsty-el: distribution independent rpms: such as grafana, minio, pg_exporter, etc…
    • add pigsty-misc: misc rpms: such as redis, minio, pg_exporter, etc…
    • remove citus repo since pgdg now have full official citus support (on el7)
    • remove remi , since redis is now included in pigsty-misc
    • remove grafana in build config for acceleration
  • repo_packages:
    • ansible python3 python3-pip python3-requests python3.11-jmespath dnf-utils modulemd-tools # el7: python36-requests python36-idna yum-utils
    • grafana loki logcli promtail prometheus2 alertmanager karma pushgateway node_exporter blackbox_exporter nginx_exporter redis_exporter
    • redis etcd minio mcli haproxy vip-manager pg_exporter nginx createrepo_c sshpass chrony dnsmasq docker-ce docker-compose-plugin flamegraph
    • lz4 unzip bzip2 zlib yum pv jq git ncdu make patch bash lsof wget uuid tuned perf nvme-cli numactl grubby sysstat iotop htop rsync tcpdump
    • netcat socat ftp lrzsz net-tools ipvsadm bind-utils telnet audit ca-certificates openssl openssh-clients readline vim-minimal
    • postgresql13* wal2json_13* pg_repack_13* passwordcheck_cracklib_13* postgresql12* wal2json_12* pg_repack_12* passwordcheck_cracklib_12* timescaledb-tools
    • postgresql15 postgresql15* citus_15* pglogical_15* wal2json_15* pg_repack_15* pgvector_15* timescaledb-2-postgresql-15* postgis33_15* passwordcheck_cracklib_15* pg_cron_15*
    • postgresql14 postgresql14* citus_14* pglogical_14* wal2json_14* pg_repack_14* pgvector_14* timescaledb-2-postgresql-14* postgis33_14* passwordcheck_cracklib_14* pg_cron_14*
    • postgresql16* wal2json_16* pgvector_16* pg_squeeze_16* postgis34_16* passwordcheck_cracklib_16* pg_cron_16*
    • patroni patroni-etcd pgbouncer pgbadger pgbackrest pgloader pg_activity pg_partman_15 pg_permissions_15 pgaudit17_15 pgexportdoc_15 pgimportdoc_15 pg_statement_rollback_15*
    • orafce_15* mysqlcompat_15 mongo_fdw_15* tds_fdw_15* mysql_fdw_15 hdfs_fdw_15 sqlite_fdw_15 pgbouncer_fdw_15 multicorn2_15* powa_15* pg_stat_kcache_15* pg_stat_monitor_15* pg_qualstats_15 pg_track_settings_15 pg_wait_sampling_15 system_stats_15
    • plprofiler_15* plproxy_15 plsh_15* pldebugger_15 plpgsql_check_15* pgtt_15 pgq_15* pgsql_tweaks_15 count_distinct_15 hypopg_15 timestamp9_15* semver_15* prefix_15* rum_15 geoip_15 periods_15 ip4r_15 tdigest_15 hll_15 pgmp_15 extra_window_functions_15 topn_15
    • pg_background_15 e-maj_15 pg_catcheck_15 pg_prioritize_15 pgcopydb_15 pg_filedump_15 pgcryptokey_15 logerrors_15 pg_top_15 pg_comparator_15 pg_ivm_15* pgsodium_15* pgfincore_15* ddlx_15 credcheck_15 safeupdate_15 pg_squeeze_15* pg_fkpart_15 pg_jobmon_15
  • repo_url_packages:
  • node_default_packages:
    • lz4,unzip,bzip2,zlib,yum,pv,jq,git,ncdu,make,patch,bash,lsof,wget,uuid,tuned,nvme-cli,numactl,grubby,sysstat,iotop,htop,rsync,tcpdump
    • netcat,socat,ftp,lrzsz,net-tools,ipvsadm,bind-utils,telnet,audit,ca-certificates,openssl,readline,vim-minimal,node_exporter,etcd,haproxy,python3,python3-pip
  • infra_packages
    • grafana,loki,logcli,promtail,prometheus2,alertmanager,karma,pushgateway
    • node_exporter,blackbox_exporter,nginx_exporter,redis_exporter,pg_exporter
    • nginx,dnsmasq,ansible,postgresql15,redis,mcli,python3-requests
  • PGSERVICE in .pigsty is removed, replaced with PGDATABASE=postgres.

FHS Changes:

  • bin/dns and bin/ssh now moved to vagrant/
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

v2.1.0

Highlight

  • PostgreSQL 16 beta support, and 12 ~ 15 support.
  • Add PGVector for AI Embedding for 12 - 15
  • Add 6 extra panel & datasource plugins for grafana
  • Add bin/profile to profile remote process and generate flamegraph
  • Add bin/validate to validate pigsty.yml configuration file
  • Add bin/repo-add to add upstream repo files to /etc/yum.repos.d
  • PostgreSQL 16 observability: pg_stat_io and corresponding dashboards

Software Upgrade

  • 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

Enhancement

  • Now use all id*.pub when installing local user’s public key

v2.0.2

Highlight

Store OpenAI embedding and search similar vectors with pgvector

Changes

  • New extension pgvector for storing OpenAI embedding and searching similar vectors.
  • MinIO CVE-2023-28432 fix, and upgrade to 20230324 with new policy API.
  • Add reload functionality to DNSMASQ systemd services
  • Bump pev to v1.8
  • Bump grafana to v9.4.7
  • Bump MinIO and MCLI version to 20230324
  • Bump bytebase version to v1.15.0
  • Upgrade monitoring dashboards and fix dead links
  • Upgrade aliyun terraform template image to rockylinux 9
  • Adopt grafana provisioning API change since v9.4
  • Add asciinema videos for various administration tasks
  • Fix broken EL8 pgsql deps: remove anonymizer_15 faker_15 and 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

v2.0.1

Bug fix for v2.0.0 and security improvement.

Enhancement

  • Replace the pig shape logo for compliance with the PostgreSQL trademark policy.
  • Bump grafana version to v9.4 with better UI and bugfix.
  • Bump patroni version to v3.0.1 with some bugfix.
  • Change: rollback grafana systemd service file to rpm default.
  • Use slow copy instead of rsync to copy grafana dashboards.
  • Enhancement: add back default repo files after bootstrap
  • Add asciinema video for various administration tasks.
  • Security Enhance Mode: restrict monitor user privilege.
  • 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 monitor view permission to pg_monitor role.
  • Remove default dbrole_readonly from dbuser_monitor to limit monitor user privilege
  • Now patroni listen on {{ inventory_hostname }} instead of 0.0.0.0
  • Now you can control postgres/pgbouncer listen to address with pg_listen
  • Now you can use placeholder ${ip}, ${lo}, ${vip} in pg_listen
  • Bump Aliyun terraform image to rocky Linux 9 instead of centos 7.9
  • Bump bytebase to v1.14.0

Bug Fixes

  • Add missing advertise address for alertmanager
  • Fix missing pg_mode error when adding postgres user with bin/pgsql-user
  • Add -a password to redis-join task @ redis.yml
  • Fix missing default value in infra-rm.yml.remove infra data
  • Fix prometheus targets file ownership to prometheus
  • Use admin user rather than root to delete metadata in DCS
  • Fix Meta datasource missing database name due to grafana 9.4 bug.

Caveats

Official EL8 pgdg upstream is broken now, DO use it with caution!

Affected packages: postgis33_15, pgloader, postgresql_anonymizer_15*, postgresql_faker_15

How to Upgrade

cd ~/pigsty; tar -zcf /tmp/files.tgz files; rm -rf ~/pigsty    # backup files dir and remove
cd ~; bash -c "$(curl -fsSL http://get.pigsty.cc/latest)"    # get latest pigsty source
cd ~/pigsty; rm -rf files; tar -xf /tmp/files.tgz -C ~/pigsty  # restore files dir

Checksums

MD5 (pigsty-pkg-v2.0.1.el7.x86_64.tgz) = 5cfbe98fd9706b9e0f15c1065971b3f6
MD5 (pigsty-pkg-v2.0.1.el8.x86_64.tgz) = c34aa460925ae7548866bf51b8b8759c
MD5 (pigsty-pkg-v2.0.1.el9.x86_64.tgz) = 055057cebd93c473a67fb63bcde22d33

Special thanks to @cocoonkid for his feedback.


v2.0.0

“PIGSTY” is now the abbr of “PostgreSQL in Great STYle”

or “PostgreSQL & Infrastructure & Governance System allTogether for You”.

Get pigsty v2.0.0 via the following command:

curl -fsSL http://get.pigsty.cc/latest | bash
Download directly from GitHub Release
# get from GitHub
bash -c "$(curl -fsSL https://raw.githubusercontent.com/Vonng/pigsty/master/bin/get)"

# or download tarball directly with curl
curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-v2.0.0.tgz -o ~/pigsty.tgz                 # SRC
curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-pkg-v2.0.0.el9.x86_64.tgz -o /tmp/pkg.tgz  # EL9
curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-pkg-v2.0.0.el8.x86_64.tgz -o /tmp/pkg.tgz  # EL8
curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-pkg-v2.0.0.el7.x86_64.tgz -o /tmp/pkg.tgz  # EL7

Highlights

  • PostgreSQL 15.2, PostGIS 3.3, Citus 11.2, TimescaleDB 2.10 now works together and unite as one.
  • Now works on EL 7,8,9 for RHEL, CentOS, Rocky, AlmaLinux, and other EL compatible distributions
  • Security enhancement with self-signed CA, full SSL support, scram-sha-256 pwd encryption, and more.
  • Patroni 3.0 with native HA citus cluster support and dcs failsafe mode to prevent global DCS failures.
  • Auto-Configured, Battery-Included PITR for PostgreSQL powered by pgbackrest, local or S3/minio.
  • Dedicate module ETCD which can be easily deployed and scaled in/out. Used as DCS instead of Consul.
  • Dedicate module MINIO, local S3 alternative for the optional central backup repo for PGSQL PITR.
  • Better config templates with adaptive tuning for Node & PG according to your hardware spec.
  • Use AGPL v3.0 license instead of Apache 2.0 license due to Grafana & MinIO reference.

Compatibility

  • Pigsty now works on EL7, EL8, EL9, and offers corresponding pre-packed offline packages.
  • Pigsty now works on EL compatible distributions: RHEL, CentOS, Rocky, AlmaLinux, OracleLinux,…
  • Pigsty now use RockyLinux 9 as default developing & testing environment instead of CentOS 7
  • EL version, CPU arch, and pigsty version string are part of source & offline package names.
  • PGSQL: PostgreSQL 15.2 / PostGIS 3.3 / TimescaleDB 2.10 / Citus 11.2 now works together.
  • PGSQL: Patroni 3.0 is used as default HA solution for PGSQL, and etcd is used as default DCS.
    • Patroni 3.0 with DCS failsafe mode to prevent global DCS failures (demoting all primary)
    • Patroni 3.0 with native HA citus cluster support, with entirely open sourced v11 citus.
    • vip-manager 2.x with ETCDv3 API, ETCDv2 API is deprecated, so does patroni.
  • PGSQL: pgBackRest v2.44 is introduced to provide battery-include PITR for PGSQL.
    • it will use local backup FS on primary by default for a two-day retention policy
    • it will use S3/minio as an alternative central backup repo for a two-week retention policy
  • ETCD is used as default DCS instead of Consul, And V3 API is used instead of V2 API.
  • NODE module now consist of node itself, haproxy, docker, node_exporter, and promtail
    • chronyd is used as default NTP client instead of ntpd
    • HAPROXY now attach to NODE instead of PGSQL, which can be used for exposing services
    • You can register PG Service to dedicate haproxy clusters rather than local cluster nodes.
    • You can expose ad hoc service in a NodePort manner with haproxy, not limited to pg services.
  • INFRA now consist of dnsmasq, nginx, prometheus, grafana, loki
    • DNSMASQ is enabled on all infra nodes, and added to all nodes as the default resolver.
    • Add blackbox_exporter for ICMP probe, add pushgateway for batch job metrics.
    • Switch to official loki & promtail rpm packages. Use official Grafana Echarts Panel.
    • Add infra dashboards for self-monitoring, add patroni & pg15 metrics to monitoring system
  • Software Upgrade
    • 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 20230222182345 / mcli 20230216192011
    • Prometheus 2.42 / Grafana 9.3 / Loki & Promtail 2.7 / Node Exporter 1.5

Security

  • A full-featured self-signed CA enabled by default
  • Redact password in postgres logs.
  • SSL for Nginx (you have to trust the self-signed CA or use thisisunsafe to dismiss warning)
  • SSL for etcd peer/client traffics by @alemacci
  • SSL for postgres/pgbouncer/patroni by @alemacci
  • scram-sha-256 auth for postgres password encryption by @alemacci
  • Pgbouncer Auth Query by @alemacci
  • Use AES-256-CBC for pgbackrest encryption by @alemacci
  • Adding a security enhancement config template which enforce global SSL
  • Now all hba rules are defined in config inventory, no default rules.

Maintainability

  • Adaptive tuning template for PostgreSQL & Patroni by @Vonng, @alemacci
  • configurable log dir for Patroni & Postgres & Pgbouncer & Pgbackrest by @alemacci
  • Replace fixed ip placeholder 10.10.10.10 with ${admin_ip} that can be referenced
  • Adaptive upstream repo definition that can be switched according EL ver, region & arch.
  • Terraform Templates for AWS CN & Aliyun, which can be used for sandbox IaaS provisioning
  • Vagrant Templates: meta, full, el7 el8, el9, build, minio, citus, etc…
  • New playbook pgsql-monitor.yml for monitoring existing pg instance or RDS PG.
  • New playbook pgsql-migration.yml for migrating existing pg instance to pigsty manged pg.
  • New shell utils under bin/ to simplify the daily administration tasks.
  • Optimize ansible role implementation. which can be used without default parameter values.
  • Now you can define pgbouncer parameters on database & user level

API Changes

69 parameters added, 16 parameters removed, rename 14 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 release version: 7,8,9
  • INFRA.CA.ca_cn : ca common name, pigsty-ca by default
  • INFRA.CA.cert_validity : cert validity, 20 years by default
  • INFRA.REPO.repo_enabled : build a local yum repo on infra node?
  • INFRA.REPO.repo_upstream : list of upstream yum repo definition
  • INFRA.REPO.repo_home : home dir of local yum repo, usually same as nginx_home ‘/www’
  • INFRA.NGINX.nginx_ssl_port : https listen port
  • INFRA.NGINX.nginx_ssl_enabled : nginx https enabled?
  • INFRA.PROMTETHEUS.alertmanager_endpoint : altermanager endpoint in (ip|domain):port format
  • NODE.NODE_TUNE.node_hugepage_count : number of 2MB hugepage, take precedence over node_hugepage_ratio
  • NODE.NODE_TUNE.node_hugepage_ratio : mem hugepage ratio, 0 disable it by default
  • NODE.NODE_TUNE.node_overcommit_ratio : node mem overcommit ratio, 0 disable it by default
  • NODE.HAPROXY.haproxy_service : list of haproxy service to be exposed
  • 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 by default
  • PGSQL.PG_INSTALL.pg_log_dir : postgres log dir, /pg/data/log by default
  • PGSQL.PG_BOOTSTRAP.pg_storage_type : SSD|HDD, SSD by default
  • PGSQL.PG_BOOTSTRAP.patroni_log_dir : patroni log dir, /pg/log by default
  • PGSQL.PG_BOOTSTRAP.patroni_ssl_enabled : secure patroni RestAPI communications with SSL?
  • 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, postgres by default
  • PGSQL.PG_BOOTSTRAP.pg_max_conn : postgres max connections, auto will use recommended value
  • PGSQL.PG_BOOTSTRAP.pg_shared_buffer_ratio : postgres shared buffer memory ratio, 0.25 by default, 0.1~0.4
  • PGSQL.PG_BOOTSTRAP.pg_rto : recovery time objective, ttl to failover, 30s by default
  • PGSQL.PG_BOOTSTRAP.pg_rpo : recovery point objective, 1MB data loss at most by default
  • PGSQL.PG_BOOTSTRAP.pg_pwd_enc : algorithm for encrypting passwords: md5|scram-sha-256
  • PGSQL.PG_BOOTSTRAP.pgbouncer_log_dir : pgbouncer log dir, /var/log/pgbouncer by default
  • PGSQL.PG_BOOTSTRAP.pgbouncer_auth_query : if enabled, query pg_authid table to retrieve biz users instead of populating userlist
  • PGSQL.PG_BOOTSTRAP.pgbouncer_sslmode : SSL for pgbouncer client: disable|allow|prefer|require|verify-ca|verify-full
  • PGSQL.PG_BACKUP.pgbackrest_enabled : pgbackrest enabled?
  • PGSQL.PG_BACKUP.pgbackrest_clean : remove pgbackrest data during init ?
  • PGSQL.PG_BACKUP.pgbackrest_log_dir : pgbackrest log dir, /pg/log by default
  • PGSQL.PG_BACKUP.pgbackrest_method : pgbackrest backup repo method, local or minio
  • PGSQL.PG_BACKUP.pgbackrest_repo : pgbackrest backup repo config
  • PGSQL.PG_SERVICE.pg_service_provider : dedicate haproxy node group name, or empty string for local nodes by default
  • PGSQL.PG_SERVICE.pg_default_service_dest : default service destination if svc.dest=‘default’
  • PGSQL.PG_SERVICE.pg_vip_enabled : enable a l2 vip for pgsql primary? false by default
  • PGSQL.PG_SERVICE.pg_vip_address : vip address in <ipv4>/<mask> format, require if vip is enabled
  • PGSQL.PG_SERVICE.pg_vip_interface : vip network interface to listen, eth0 by default
  • PGSQL.PG_SERVICE.pg_dns_suffix : pgsql cluster dns name suffix, ’’ by default
  • PGSQL.PG_SERVICE.pg_dns_target : auto, primary, vip, none, or ad hoc ip
  • ETCD.etcd_seq : etcd instance identifier, REQUIRED
  • ETCD.etcd_cluster : etcd cluster & group name, etcd by default
  • ETCD.etcd_safeguard : prevent purging running etcd instance?
  • ETCD.etcd_clean : purging existing etcd during initialization?
  • ETCD.etcd_data : etcd data directory, /data/etcd by default
  • ETCD.etcd_port : etcd client port, 2379 by default
  • ETCD.etcd_peer_port : etcd peer port, 2380 by default
  • ETCD.etcd_init : etcd initial cluster state, new or existing
  • ETCD.etcd_election_timeout : etcd election timeout, 1000ms by default
  • ETCD.etcd_heartbeat_interval : etcd heartbeat interval, 100ms by default
  • MINIO.minio_seq : minio instance identifier, REQUIRED
  • MINIO.minio_cluster : minio cluster name, minio by default
  • MINIO.minio_clean : cleanup minio during init?, false by default
  • MINIO.minio_user : minio os user, minio by default
  • MINIO.minio_node : minio node name pattern
  • MINIO.minio_data : minio data dir(s), use {x…y} to specify multi drivers
  • MINIO.minio_domain : minio external domain name, sss.pigsty by default
  • MINIO.minio_port : minio service port, 9000 by default
  • MINIO.minio_admin_port : minio console port, 9001 by default
  • MINIO.minio_access_key : root access key, minioadmin by default
  • MINIO.minio_secret_key : root secret key, minioadmin by default
  • MINIO.minio_extra_vars : extra environment variables for minio server
  • MINIO.minio_alias : alias name for local minio deployment
  • MINIO.minio_buckets : list of minio bucket to be created
  • MINIO.minio_users : list of minio user to be created

Removed Parameters

  • INFRA.CA.ca_homedir: ca home dir, now fixed as /etc/pki/
  • INFRA.CA.ca_cert: ca cert filename, now fixed as ca.key
  • INFRA.CA.ca_key: ca key filename, now fixed as ca.key
  • INFRA.REPO.repo_upstreams: replaced by repo_upstream
  • PGSQL.PG_INSTALL.pgdg_repo: now taken care by node playbooks
  • PGSQL.PG_INSTALL.pg_add_repo: now taken care by node playbooks
  • PGSQL.PG_IDENTITY.pg_backup: not used and conflict with section name
  • PGSQL.PG_IDENTITY.pg_preflight_skip: not used anymore, replace by pg_id
  • DCS.dcs_name : removed due to using etcd
  • DCS.dcs_servers : replaced by using ad hoc group etcd
  • DCS.dcs_registry : removed due to using etcd
  • DCS.dcs_safeguard : replaced by etcd_safeguard
  • DCS.dcs_clean : replaced by etcd_clean
  • PGSQL.PG_VIP.vip_mode : replaced by pg_vip_enabled
  • PGSQL.PG_VIP.vip_address : replaced by pg_vip_address
  • PGSQL.PG_VIP.vip_interface : replaced by pg_vip_interface

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
  • node_packages_default -> node_default_packages
  • node_packages_meta -> infra_packages
  • node_packages_meta_pip -> infra_packages_pip
  • node_data_dir -> node_data

Checksums

MD5 (pigsty-pkg-v2.0.0.el7.x86_64.tgz) = 9ff3c973fa5915f65622b91419817c9b
MD5 (pigsty-pkg-v2.0.0.el8.x86_64.tgz) = bd108a6c8f026cb79ee62c3b68b72176
MD5 (pigsty-pkg-v2.0.0.el9.x86_64.tgz) = e24288770f240af0511b0c38fa2f4774

Special thanks to @alemacci for his great contribution!


v1.5.1

WARNING: CREATE INDEX|REINDEX CONCURRENTLY PostgreSQL 14.0 - 14.3 may lead to index data corruption!

Please upgrade postgres to 14.4 ASAP.

Software Upgrade

  • upgrade postgres to 14.4 (important bug fix)
  • upgrade citus to 11.0-2 (with enterprise features)
  • upgrade timescaledb to 2.7 (more continuous aggregates)
  • Upgrade patroni to 2.1.4 (new sync health-check)
  • Upgrade haproxy to 2.6.0 (cli, reload, ssl,…)
  • Upgrade grafana to 9.0.0 (new ui)
  • Upgrade prometheus 2.36.0

Bug fix

  • Fix typo in pgsql-migration.yml
  • remove pid file in haproxy config
  • remove i686 packages when using repotrack under el7
  • Fix redis service systemctl enabled issue
  • Fix patroni systemctl service enabled=no by default issue
  • stop vip-manager when purging existing postgres

API Changes

  • Mark grafana_database and grafana_pgurl as obsolete
  • Add some new etcd & pgsql alias (optional)

New Apps

  • wiki.js : Local wiki with Postgres
  • FerretDB : MongoDB API over Postgres

v1.5.0

Highlights

  • Complete Docker Support, enable on meta nodes by default with lot’s of software templates.
    • bytebase pgadmin4 pgweb postgrest kong minio,…
  • Infra Self Monitoring: Nginx, ETCD, Consul, Grafana, Prometheus, Loki, etc…
  • New CMDB design compatible with redis & greenplum, visualize with CMDB Overview
  • Service Discovery : Consul SD now works again for prometheus targets management
  • Redis playbook now works on single instance with redis_port option.
  • Better cold backup support: crontab for backup, delayed standby with pg_delay
  • Use ETCD as DCS, alternative to Consul
  • Nginx Log Enhancement

Monitoring

Dashboards

  • CMDB Overview: Visualize CMDB Inventory
  • DCS Overview: Show consul & etcd metrics
  • Nginx Overview: Visualize nginx metrics & access/error logs
  • Grafana Overview: Grafana self Monitoring
  • Prometheus Overview:Prometheus self Monitoring
  • INFRA Dashboard & Home Dashboard Reforge

Architecture

  • Infra monitoring targets now have a separated target dir targets/infra
  • Consul SD is available for prometheus
  • etcd , consul , patroni, docker metrics
  • Now infra targets are managed by role infra_register
  • Upgrade pg_exporter to v0.5.0 with scale and default support
    • pg_bgwriter, pg_wal, pg_query, pg_db, pgbouncer_stat now use seconds instead of ms and µs
    • pg_table counters now have default value 0 instead of NaN
    • pg_class is replaced by pg_table and pg_index
    • pg_table_size is now enabled with 300s ttl

Provisioning

  • New optional package docker.tgz contains: Pgadmin, Pgweb, Postgrest, ByteBase, Kong, Minio, etc.
  • New Role etcd to deploy & monitor etcd dcs service
  • Specify which type of DCS to use with pg_dcs_type (etcd now available)
  • Add pg_checksum option to enable data checksum
  • Add pg_delay option to setup delayed standby leaders
  • Add node_crontab and node_crontab_overwrite to create routine jobs such as cold backup
  • Add a series of *_enable options to control components
  • Loki and Promtail are now installed using the RPM package made by frpm.
  • Allow customize monitoring logo

Software Updates

  • Upgrade PostgreSQL to 14.3
  • Upgrade Redis to 6.2.7
  • Upgrade PG Exporter to 0.5.0
  • Upgrade Consul to 1.12.0
  • Upgrade vip-manager to v1.0.2
  • Upgrade Grafana to v8.5.2
  • Upgrade HAproxy to 2.5.7 without rsyslog dependency
  • Upgrade Loki & Promtail to v2.5.0 with RPM packages
  • New packages: pg_probackup

New software / application based on docker:

  • bytebase : DDL Schema Migrator
  • pgadmin4 : Web Admin UI for PostgreSQL
  • pgweb : Web Console for PostgreSQL
  • postgrest : Auto generated REST API for PostgreSQL
  • kong : API Gateway which use PostgreSQL as backend storage
  • swagger openapi : API Specification Generator
  • Minio : S3-compatible object storage
  • Gitea : Private local git service

Bug Fix

  • Fix loki & promtail /etc/default config file name issue
  • Now node_data_dir (/data) is created before consul init if not exists
  • Fix haproxy silence /var/log/messages with inappropriate rsyslog dependency

API Change

New Variable

  • node_data_dir : major data mount path, will be created if not exist.
  • node_crontab_overwrite : overwrite /etc/crontab instead of append
  • node_crontab: node crontab to be appended or overwritten
  • nameserver_enabled: enable nameserver on this meta node?
  • prometheus_enabled: enable prometheus on this meta node?
  • grafana_enabled: enable grafana on this meta node?
  • loki_enabled: enable loki on this meta node?
  • docker_enable: enable docker on this node?
  • consul_enable: enable consul server/agent?
  • etcd_enable: enable etcd server/clients?
  • pg_checksum: enable pg cluster data-checksum?
  • pg_delay: recovery min apply delay for standby leader
  • grafana_customize_logo: customize grafana icon

Reforge

Now *_clean are boolean flags to clean up existing instance during init.

And *_safeguard are boolean flags to avoid purging running instance when executing any playbook.

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

Rename

  • 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_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.4.1

Routine bug fix / Docker Support / English Docs

Now docker is enabled on meta node by default. You can launch ton’s of SaaS with it

English document is available now.

Bug Fix


v1.4.0

Architecture

  • Decouple system into 4 major categories: INFRA, NODES, PGSQL, REDIS, which makes pigsty far more clear and more extensible.
  • Single Node Deployment = INFRA + NODES + PGSQL
  • Deploy pgsql clusters = NODES + PGSQL
  • Deploy redis clusters = NODES + REDIS
  • Deploy other databases = NODES + xxx (e.g MONGO, KAFKA, … TBD)

Accessibility

  • CDN for mainland China.
  • Get the latest source with bash -c "$(curl -fsSL http://get.pigsty.cc/latest)"
  • Download & Extract packages with new download script.

Monitor Enhancement

  • Split monitoring system into 5 major categories: INFRA, NODES, REDIS, PGSQL, APP
  • Logging enabled by default
    • now loki and promtail are enabled by default. with prebuilt loki-rpm
  • Models & Labels
    • A hidden ds prometheus datasource variable is added for all dashboards, so you can easily switch different datasource simply by select a new one rather than modifying Grafana Datasources & Dashboards
    • An ip label is added for all metrics, and will be used as join key between database metrics & nodes metrics
  • INFRA Monitoring
    • Home dashboard for infra: INFRA Overview
    • Add logging Dashboards : Logs Instance
    • PGLOG Analysis & PGLOG Session now treated as an example Pigsty APP.
  • NODES Monitoring Application
    • If you don’t care database at all, Pigsty now can be used as host monitoring software alone!
    • Consist of 4 core dashboards: Nodes Overview & Nodes Cluster & Nodes Instance & Nodes Alert
    • Introduce new identity variables for nodes: node_cluster and nodename
    • Variable pg_hostname now means set hostname same as postgres instance name to keep backward-compatible
    • Variable nodename_overwrite control whether overwrite node’s hostname with nodename
    • Variable nodename_exchange will write nodename to each other’s /etc/hosts
    • All nodes metrics reference are overhauled, join by ip
    • Nodes monitoring targets are managed alone under /etc/prometheus/targets/nodes
  • PGSQL Monitoring Enhancement
    • Complete new PGSQL Cluster which simplify and focus on important stuff among cluster.
    • New Dashboard PGSQL Databases which is cluster level object monitoring. Such as tables & queries among the entire cluster rather than single instance.
    • PGSQL Alert dashboard now only focus on pgsql alerts.
    • PGSQL Shard are added to PGSQL
  • Redis Monitoring Enhancement
    • Add nodes monitoring for all redis dashboards.

MatrixDB Support

  • MatrixDB (Greenplum 7) can be deployed via pigsty-matrix.yml playbook
  • MatrixDB Monitor Dashboards : PGSQL MatrixDB
  • Example configuration added: pigsty-mxdb.yml

Provisioning Enhancement

Now pigsty work flow works as this:

 infra.yml ---> install pigsty on single meta node
      |          then add more nodes under pigsty's management
      |
 nodes.yml ---> prepare nodes for pigsty (node setup, dcs, node_exporter, promtail)
      |          then choose one playbook to deploy database clusters on those nodes
      |
      ^--> pgsql.yml   install postgres on prepared nodes
      ^--> redis.yml   install redis on prepared nodes

infra-demo.yml = 
           infra.yml -l meta     +
           nodes.yml -l pg-test  +
           pgsql.yml -l pg-test +
           infra-loki.yml + infra-jupyter.yml + infra-pgweb.yml
 
  • nodes.yml to setup & prepare nodes for pigsty
    • setup node, node_exporter, consul agent on nodes
    • node-remove.yml are used for node de-register
  • pgsql.yml now only works on prepared nodes
    • pgsql-remove now only responsible for postgres itself. (dcs and node monitor are taken by node.yml)
    • Add a series of new options to reuse postgres role in greenplum/matrixdb
  • redis.yml now works on prepared nodes
    • and redis-remove.yml now remove redis from nodes.
  • pgsql-matrix.yml now install matrixdb (Greenplum 7) on prepared nodes.

Software Upgrade

  • PostgreSQL 14.2
  • PostGIS 3.2
  • TimescaleDB 2.6
  • Patroni 2.1.3 (Prometheus Metrics + Failover Slots)
  • HAProxy 2.5.5 (Fix stats error, more metrics)
  • PG Exporter 0.4.1 (Timeout Parameters, and)
  • Grafana 8.4.4
  • Prometheus 2.33.4
  • Greenplum 6.19.4 / MatrixDB 4.4.0
  • Loki are now shipped as rpm packages instead of zip archives

Bug Fix

  • Remove consul dependency for patroni , which makes it much more easier to migrate to a new consul cluster
  • Fix prometheus bin/new scripts default data dir path : /export/prometheus to /data/prometheus
  • Fix typos and tasks
  • Add restart seconds to vip-manager systemd service

API Changes

New Variable

  • node_cluster: Identity variable for node cluster
  • nodename_overwrite: If set, nodename will be set to node’s hostname
  • nodename_exchange : exchange node hostname (in /etc/hosts) among play hosts
  • node_dns_hosts_extra : extra static dns records which can be easily overwritten by single instance/cluster
  • patroni_enabled: if disabled, postgres & patroni bootstrap will not be performed during role postgres
  • pgbouncer_enabled : if disabled, pgbouncer will not be launched during role postgres
  • pg_exporter_params: extra url parameters for pg_exporter when generating monitor target url.
  • pg_provision: bool var to indicate whether perform provision part of role postgres (template, db,user)
  • no_cmdb: cli args for infra.yml and infra-demo.yml playbook which will not create cmdb on meta node.
MD5 (app.tgz) = f887313767982b31a2b094e5589a75ea
MD5 (matrix.tgz) = 3d063437c482d94bd7e35df1a08bbc84
MD5 (pigsty.tgz) = e143b88ebea1474f9ebaffddc6072c49
MD5 (pkg.tgz) = 73e8f5ce995b1f1760cb63c1904fb91b

v1.3.1

[Monitor]

  • PGSQL & PGCAT Dashboard polish
  • optimize layout for pgcat instance & pgcat database
  • add key metrics panels to pgsql instance dashboard, keep consist with pgsql cluster
  • add table/index bloat panels to pgcat database, remove pgcat bloat dashboard.
  • add index information in pgcat database dashboard
  • fix broken panels in grafana 8.3
  • add redis index in nginx homepage

[Deploy]

  • New infra-demo.yml playbook for one-pass bootstrap
  • Use infra-jupyter.yml playbook to deploy optional jupyter lab server
  • Use infra-pgweb.yml playbook to deploy optional pgweb server
  • New pg alias on meta node, can initiate postgres cluster from admin user (in addition to postgres)
  • Adjust all patroni conf templates’s max_locks_per_transactions according to timescaledb-tune ’s advise
  • Add citus.node_conninfo: 'sslmode=prefer' to conf templates in order to use citus without SSL
  • Add all extensions (except for pgrouting) in pgdg14 in package list
  • Upgrade node_exporter to v1.3.1
  • Add PostgREST v9.0.0 to package list. Generate API from postgres schema.

[BugFix]

  • Grafana’s security breach (upgrade to v8.3.1 issue)
  • fix pg_instance & pg_service in register role when start from middle of playbook
  • Fix nginx homepage render issue when host without pg_cluster variable exists
  • Fix style issue when upgrading to grafana 8.3.1

v1.3.0

  • [ENHANCEMENT] Redis Deployment (cluster,sentinel,standalone)

  • [ENHANCEMENT] Redis Monitor

    • Redis Overview Dashboard
    • Redis Cluster Dashboard
    • Redis Instance Dashboard
  • [ENHANCEMENT] monitor: PGCAT Overhaul

    • New Dashboard: PGCAT Instance
    • New Dashboard: PGCAT Database Dashboard
    • Remake Dashboard: PGCAT Table
  • [ENHANCEMENT] monitor: PGSQL Enhancement

    • New Panels: PGSQL Cluster, add 10 key metrics panel (toggled by default)
    • New Panels: PGSQL Instance, add 10 key metrics panel (toggled by default)
    • Simplify & Redesign: PGSQL Service
    • Add cross-references between PGCAT & PGSL dashboards
  • [ENHANCEMENT] monitor deploy

    • Now grafana datasource is automatically registered during monly deployment
  • [ENHANCEMENT] software upgrade

    • add PostgreSQL 13 to default package list
    • upgrade to PostgreSQL 14.1 by default
    • add greenplum rpm and dependencies
    • add redis rpm & source packages
    • add perf as default packages

v1.2.0

  • [ENHANCEMENT] Use PostgreSQL 14 as default version
  • [ENHANCEMENT] Use TimescaleDB 2.5 as default extension
    • now timescaledb & postgis are enabled in cmdb by default
  • [ENHANCEMENT] new monitor-only mode:
    • you can use pigsty to monitor existing pg instances with a connectable url only
    • pg_exporter will be deployed on meta node locally
    • new dashboard PGSQL Cluster Monly for remote clusters
  • [ENHANCEMENT] Software upgrade
    • grafana to 8.2.2
    • pev2 to v0.11.9
    • promscale to 0.6.2
    • pgweb to 0.11.9
    • Add new extensions: pglogical pg_stat_monitor orafce
  • [ENHANCEMENT] Automatic detect machine spec and use proper node_tune and pg_conf templates
  • [ENHANCEMENT] Rework on bloat related views, now more information are exposed
  • [ENHANCEMENT] Remove timescale & citus internal monitoring
  • [ENHANCEMENT] New playbook pgsql-audit.yml to create audit report.
  • [BUG FIX] now pgbouncer_exporter resource owner are {{ pg_dbsu }} instead of postgres
  • [BUG FIX] fix pg_exporter duplicate metrics on pg_table pg_index while executing REINDEX TABLE CONCURRENTLY
  • [CHANGE] now all config templates are minimize into two: auto & demo. (removed: pub4, pg14, demo4, tiny, oltp )
    • pigsty-demo is configured if vagrant is the default user, otherwise pigsty-auto is used.

How to upgrade from v1.1.1

There’s no API change in 1.2.0 You can still use old pigsty.yml configuration files (PG13).

For the infrastructure part. Re-execution of repo will do most of the parts

As for the database. You can still use the existing PG13 instances. In-place upgrade is quite tricky especially when involving extensions such as PostGIS & Timescale. I would highly recommend performing a database migration with logical replication.

The new playbook pgsql-migration.yml will make this a lot easier. It will create a series of scripts which will help you to migrate your cluster with near-zero downtime.


v1.1.1

  • [ENHANCEMENT] replace timescaledb apache version with timescale version
  • [ENHANCEMENT] upgrade prometheus to 2.30
  • [BUG FIX] now pg_exporter config dir’s owner are {{ pg_dbsu }} instead of prometheus

How to upgrade from v1.1.0 The major change in this release is timescaledb. Which replace old apache license version with timescale license version

stop/pause postgres instance with timescaledb
yum remove -y timescaledb_13

[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 

v1.1.0

  • [ENHANCEMENT] add pg_dummy_filesize to create fs space placeholder
  • [ENHANCEMENT] home page overhaul
  • [ENHANCEMENT] add jupyter lab integration
  • [ENHANCEMENT] add pgweb console integration
  • [ENHANCEMENT] add pgbadger support
  • [ENHANCEMENT] add pev2 support, explain visualizer
  • [ENHANCEMENT] add pglog utils
  • [ENHANCEMENT] update default pkg.tgz software version:
    • upgrade postgres to v13.4 (with official pg14 support)
    • upgrade pgbouncer to v1.16 (metrics definition updates)
    • upgrade grafana to v8.1.4
    • upgrade prometheus to v2.2.29
    • upgrade node_exporter to v1.2.2
    • upgrade haproxy to v2.1.1
    • upgrade consul to v1.10.2
    • upgrade vip-manager to v1.0.1

API Changes

  • nginx_upstream now holds different structures. (incompatible)

  • new config entries: app_list, render into home page’s nav entries

  • new config entries: docs_enabled, setup local docs on default server.

  • new config entries: pev2_enabled, setup local pev2 utils.

  • new config entries: pgbadger_enabled, create log summary/report dir

  • new config entries: jupyter_enabled, enable jupyter lab server on meta node

  • new config entries: jupyter_username, specify which user to run jupyter lab

  • new config entries: jupyter_password, specify jupyter lab default password

  • new config entries: pgweb_enabled, enable pgweb server on meta node

  • new config entries: pgweb_username, specify which user to run pgweb

  • rename internal flag repo_exist into repo_exists

  • now default value for repo_address is pigsty instead of yum.pigsty

  • now haproxy access point is http://pigsty instead of http://h.pigsty


v1.0.1

  • Documentation Update
    • Chinese document now viable
    • Machine-Translated English document now viable
  • Bug Fix: pgsql-remove does not remove primary instance.
  • Bug Fix: replace pg_instance with pg_cluster + pg_seq
    • Start-At-Task may fail due to pg_instance undefined
  • Bug Fix: remove citus from default shared preload library
    • citus will force max_prepared_transaction to non-zero value
  • Bug Fix: ssh sudo checking in configure:
    • now ssh -t sudo -n ls is used for privilege checking
  • Typo Fix: pg-backup script typo
  • Alert Adjust: Remove ntp sanity check alert (dupe with ClockSkew)
  • Exporter Adjust: remove collector.systemd to reduce overhead

v1.0.0

v1 GA, Monitoring System Overhaul

Highlights

  • Monitoring System Overhaul

    • New Dashboards on Grafana 8.0
    • New metrics definition, with extra PG14 support
    • Simplified labeling system: static label set: (job, cls, ins)
    • New Alerting Rules & Derived Metrics
    • Monitoring multiple database at one time
    • Realtime log search & csvlog analysis
    • Link-Rich Dashboards, click graphic elements to drill-down|roll-up
  • Architecture Changes

    • Add citus & timescaledb as part of default installation
    • Add PostgreSQL 14beta2 support
    • Simply haproxy admin page index
    • Decouple infra & pgsql by adding a new role register
    • Add new role loki and promtail for logging
    • Add new role environ for setting up environment for admin user on admin node
    • Using static service-discovery for prometheus by default (instead of consul)
    • Add new role remove to gracefully remove cluster & instance
    • Upgrade prometheus & grafana provisioning logics.
    • Upgrade to vip-manager 1.0 , node_exporter 1.2 , pg_exporter 0.4, grafana 8.0
    • Now every database on every instance can be auto-registered as grafana datasource
    • Move consul register tasks to role register, change consul service tags
    • Add cmdb.sql as pg-meta baseline definition (CMDB & PGLOG)
  • Application Framework

    • Extensible framework for new functionalities
    • core app: PostgreSQL Monitor System: pgsql
    • core app: PostgreSQL Catalog explorer: pgcat
    • core app: PostgreSQL Csvlog Analyzer: pglog
    • add example app covid for visualizing covid-19 data.
    • add example app isd for visualizing isd data.
  • Misc

    • Add jupyterlab which brings entire python environment for data science
    • Add vonng-echarts-panel to bring Echarts support back.
    • Add wrap script createpg , createdb, createuser
    • Add cmdb dynamic inventory scripts: load_conf.py, inventory_cmdb, inventory_conf
    • Remove obsolete playbooks: pgsql-monitor, pgsql-service, node-remove, etc….

API Change

Bug Fix

  • Fix default timezone Asia/Shanghai (CST) issue
  • Fix nofile limit for pgbouncer & patroni
  • Pgbouncer userlist & database list will be generated when executing tag pgbouncer

v0.9.0

Pigsty GUI, CLI, Logging Intergration

Features

  • One-Line Installation

    Run this on meta node /bin/bash -c "$(curl -fsSL https://pigsty.cc/install)"

  • MetaDB provisioning

    Now you can use pgsql database on meta node as inventory instead of static yaml file affter bootstrap.

  • Add Loki & Prometail as optinal logging collector

    Now you can view, query, search postgres|pgbouncer|patroni logs with Grafana UI (PG Instance Log)

  • Pigsty CLI/GUI (beta)

    Mange you pigsty deployment with much more human-friendly command line interface.

Bug Fix

  • Log related issues
    • fix connection reset by peer entries in postgres log caused by Haproxy health check.
    • fix Connect Reset Exception in patroni logs caused by haproxy health check
    • fix patroni log time format (remove mill seconds, add timezone)
    • set log_min_duration_statement=1s for dbuser_monitor to get ride of monitor logs.
  • Fix pgbouncer-create-user does not handle md5 password properly
  • Fix obsolete Makefile entries
  • Fix node dns nameserver lost when abort during resolv.conf rewrite
  • Fix db/user template and entry not null check

API Change

  • Set default value of node_disable_swap to false
  • Remove example enties of node_sysctl_params.
  • grafana_plugin default install will now download from CDN if plugins not exists
  • repo_url_packages now download rpm via pigsty CDN to accelerate.
  • proxy_env.no_proxy now add pigsty CDN to noproxy sites。
  • grafana_customize set to false by default,enable it means install pigsty pro UI.
  • node_admin_pk_current add current user’s ~/.ssh/id_rsa.pub to admin pks
  • loki_clean whether to cleanup existing loki data during init
  • loki_data_dir set default data dir for loki logging service
  • promtail_enabled enabling promtail logging agent service?
  • promtail_clean remove existing promtail status during init?
  • promtail_port default port used by promtail, 9080 by default
  • promtail_status_file location of promtail status file
  • promtail_send_url endpoint of loki service which receives log data

v0.8.0

Service Provisioning support is added in this release

New Features

  • Service provision.
  • full locale support.

API Changes

Role vip and haproxy are merged into service.

#------------------------------------------------------------------------------
# SERVICE PROVISION
#------------------------------------------------------------------------------
pg_weight: 100              # default load balance weight (instance level)

# - service - #
pg_services:                                  # how to expose postgres service in cluster?
  # primary service will route {ip|name}:5433 to primary pgbouncer (5433->6432 rw)
  - name: primary           # service name {{ pg_cluster }}_primary
    src_ip: "*"
    src_port: 5433
    dst_port: pgbouncer     # 5433 route to pgbouncer
    check_url: /primary     # primary health check, success when instance is primary
    selector: "[]"          # select all instance as primary service candidate

  # replica service will route {ip|name}:5434 to replica pgbouncer (5434->6432 ro)
  - name: replica           # service name {{ pg_cluster }}_replica
    src_ip: "*"
    src_port: 5434
    dst_port: pgbouncer
    check_url: /read-only   # read-only health check. (including primary)
    selector: "[]"          # select all instance as replica service candidate
    selector_backup: "[? pg_role == `primary`]"   # primary are used as backup server in replica service

  # default service will route {ip|name}:5436 to primary postgres (5436->5432 primary)
  - name: default           # service's actual name is {{ pg_cluster }}-{{ service.name }}
    src_ip: "*"             # service bind ip address, * for all, vip for cluster virtual ip address
    src_port: 5436          # bind port, mandatory
    dst_port: postgres      # target port: postgres|pgbouncer|port_number , pgbouncer(6432) by default
    check_method: http      # health check method: only http is available for now
    check_port: patroni     # health check port:  patroni|pg_exporter|port_number , patroni by default
    check_url: /primary     # health check url path, / as default
    check_code: 200         # health check http code, 200 as default
    selector: "[]"          # instance selector
    haproxy:                # haproxy specific fields
      maxconn: 3000         # default front-end connection
      balance: roundrobin   # load balance algorithm (roundrobin by default)
      default_server_options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'

  # offline service will route {ip|name}:5438 to offline postgres (5438->5432 offline)
  - name: offline           # service name {{ pg_cluster }}_replica
    src_ip: "*"
    src_port: 5438
    dst_port: postgres
    check_url: /replica     # offline MUST be a replica
    selector: "[? pg_role == `offline` || pg_offline_query ]"         # instances with pg_role == 'offline' or instance marked with 'pg_offline_query == true'
    selector_backup: "[? pg_role == `replica` && !pg_offline_query]"  # replica are used as backup server in offline service

pg_services_extra: []        # extra services to be added

# - haproxy - #
haproxy_enabled: true                         # enable haproxy among every cluster members
haproxy_reload: true                          # reload haproxy after config
haproxy_policy: roundrobin                    # roundrobin, leastconn
haproxy_admin_auth_enabled: false             # enable authentication for haproxy admin?
haproxy_admin_username: admin                 # default haproxy admin username
haproxy_admin_password: admin                 # default haproxy admin password
haproxy_exporter_port: 9101                   # default admin/exporter port
haproxy_client_timeout: 3h                    # client side connection timeout
haproxy_server_timeout: 3h                    # server side connection timeout

# - vip - #
vip_mode: none                                # none | l2 | l4
vip_reload: true                              # whether reload service after config
# vip_address: 127.0.0.1                      # virtual ip address ip (l2 or l4)
# vip_cidrmask: 24                            # virtual ip address cidr mask (l2 only)
# vip_interface: eth0                         # virtual ip network interface (l2 only)

New Options

# - localization - #
pg_encoding: UTF8                             # default to UTF8
pg_locale: C                                  # default to C
pg_lc_collate: C                              # default to C
pg_lc_ctype: en_US.UTF8                       # default to en_US.UTF8

pg_reload: true                               # reload postgres after hba changes
vip_mode: none                                # none | l2 | l4
vip_reload: true                              # whether reload service after config

Remove Options

haproxy_check_port                            # covered by service options
haproxy_primary_port
haproxy_replica_port
haproxy_backend_port
haproxy_weight
haproxy_weight_fallback
vip_enabled                                   # replace by vip_mode

Service

pg_services and pg_services_extra Defines the services in cluster:

A service has some mandatory fields:

  • name: service’s name
  • src_port: which port to listen and expose service?
  • selector: which instances belonging to this service?
  # default service will route {ip|name}:5436 to primary postgres (5436->5432 primary)
  - name: default           # service's actual name is {{ pg_cluster }}-{{ service.name }}
    src_ip: "*"             # service bind ip address, * for all, vip for cluster virtual ip address
    src_port: 5436          # bind port, mandatory
    dst_port: postgres      # target port: postgres|pgbouncer|port_number , pgbouncer(6432) by default
    check_method: http      # health check method: only http is available for now
    check_port: patroni     # health check port:  patroni|pg_exporter|port_number , patroni by default
    check_url: /primary     # health check url path, / as default
    check_code: 200         # health check http code, 200 as default
    selector: "[]"          # instance selector
    haproxy:                # haproxy specific fields
      maxconn: 3000         # default front-end connection
      balance: roundrobin   # load balance algorithm (roundrobin by default)
      default_server_options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'

Database

Add additional locale support: lc_ctype and lc_collate.

It’s mainly because of pg_trgm ’s weird behavior on i18n characters.

pg_databases:
  - name: meta                      # name is the only required field for a database
    # owner: postgres                 # optional, database owner
    # template: template1             # optional, template1 by default
    # encoding: UTF8                # optional, UTF8 by default , must same as template database, leave blank to set to db default
    # locale: C                     # optional, C by default , must same as template database, leave blank to set to db default
    # lc_collate: C                 # optional, C by default , must same as template database, leave blank to set to db default
    # lc_ctype: C                   # optional, C by default , must same as template database, leave blank to set to db default
    allowconn: true                 # optional, true by default, false disable connect at all
    revokeconn: false               # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
    # tablespace: pg_default          # optional, 'pg_default' is the default tablespace
    connlimit: -1                   # optional, connection limit, -1 or none disable limit (default)
    extensions:                     # optional, extension name and where to create
      - {name: postgis, schema: public}
    parameters:                     # optional, extra parameters with ALTER DATABASE
      enable_partitionwise_join: true
    pgbouncer: true                 # optional, add this database to pgbouncer list? true by default
    comment: pigsty meta database   # optional, comment string for database

v0.7.0

Monitor only deployment support

Overview

  • Monitor Only Deployment

    • Now you can monitoring existing postgres clusters without Pigsty provisioning solution.
    • Intergration with other provisioning solution is available and under further test.
  • Database/User Management

    • Update user/database definition schema to cover more usecases.
    • Add pgsql-createdb.yml and pgsql-user.yml to mange user/db on running clusters.

Features

Bug Fix

API Changes

New Options

prometheus_sd_target: batch                   # batch|single
exporter_install: none                        # none|yum|binary
exporter_repo_url: ''                         # add to yum repo if set
node_exporter_options: '--no-collector.softnet --collector.systemd --collector.ntp --collector.tcpstat --collector.processes'                          # default opts for node_exporter
pg_exporter_url: ''                           # optional, overwrite default pg_exporter target
pgbouncer_exporter_url: ''                    # optional, overwrite default pgbouncer_expoter target

Remove Options

exporter_binary_install: false                 # covered by exporter_install

Structure Changes

pg_default_roles                               # refer to pg_users
pg_users                                       # refer to pg_users
pg_databases                                   # refer to pg_databases

Rename Options

pg_default_privilegs -> pg_default_privileges  # fix typo

Enhancement

Monitoring Provisioning Enhancement

Haproxy Enhancement

Security Enhancement

Software Update

  • Upgrade to PG 13.2 #6

  • Prometheus 2.25 / Grafana 7.4 / Consul 1.9.3 / Node Exporter 1.1 / PG Exporter 0.3.2

API Change

New Config Entries

service_registry: consul                      # none | consul | etcd | both
prometheus_options: '--storage.tsdb.retention=30d'  # prometheus cli opts
prometheus_sd_method: consul                  # Prometheus service discovery method:static|consul
prometheus_sd_interval: 2s                    # Prometheus service discovery refresh interval
pg_offline_query: false                       # set to true to allow offline queries on this instance
node_exporter_enabled: true                   # enabling Node Exporter
pg_exporter_enabled: true                     # enabling PG Exporter
pgbouncer_exporter_enabled: true              # enabling Pgbouncer Exporter
export_binary_install: false                  # install Node/PG Exporter via copy binary
dcs_disable_purge: false                      # force dcs_exists_action = abort to avoid dcs purge
pg_disable_purge: false                       # force pg_exists_action = abort to avoid pg purge
haproxy_weight: 100                           # relative lb weight for backend instance
haproxy_weight_fallback: 1                    # primary server weight in replica service group

Obsolete Config Entries

prometheus_metrics_path                       # duplicate with exporter_metrics_path 
prometheus_retention                          # covered by `prometheus_options`

Database Definition

Database provisioning interface enhancement #33

Old Schema

pg_databases:                       # create a business database 'meta'
  - name: meta
    schemas: [meta]                 # create extra schema named 'meta'
    extensions: [{name: postgis}]   # create extra extension postgis
    parameters:                     # overwrite database meta's default search_path
      search_path: public, monitor

New Schema

pg_databases:
  - name: meta                      # name is the only required field for a database
    owner: postgres                 # optional, database owner
    template: template1             # optional, template1 by default
    encoding: UTF8                  # optional, UTF8 by default
    locale: C                       # optional, C by default
    allowconn: true                 # optional, true by default, false disable connect at all
    revokeconn: false               # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
    tablespace: pg_default          # optional, 'pg_default' is the default tablespace
    connlimit: -1                   # optional, connection limit, -1 or none disable limit (default)
    extensions:                     # optional, extension name and where to create
      - {name: postgis, schema: public}
    parameters:                     # optional, extra parameters with ALTER DATABASE
      enable_partitionwise_join: true
    pgbouncer: true                 # optional, add this database to pgbouncer list? true by default
    comment: pigsty meta database   # optional, comment string for database

Changes

  • Add new options: template , encoding, locale, allowconn, tablespace, connlimit
  • Add new option revokeconn, which revoke connect privileges from public for this database
  • Add comment field for database

Apply Changes

You can create new database on running postgres clusters with pgsql-createdb.yml playbook.

  1. Define your new database in config files
  2. Pass new database.name with option pg_database to playbook.
./pgsql-createdb.yml -e pg_database=<your_new_database_name>

User Definition

User provisioning interface enhancement #34

Old Schema

pg_users:
  - username: test                  # example production user have read-write access
    password: test                  # example user's password
    options: LOGIN                  # extra options
    groups: [ dbrole_readwrite ]    # dborole_admin|dbrole_readwrite|dbrole_readonly
    comment: default test user for production usage
    pgbouncer: true                 # add to pgbouncer

New Schema

pg_users:
  # complete example of user/role definition for production user
  - name: dbuser_meta               # example production user have read-write access
    password: DBUser.Meta           # example user's password, can be encrypted
    login: true                     # can login, true by default (should be false for role)
    superuser: false                # is superuser? false by default
    createdb: false                 # can create database? false by default
    createrole: false               # can create role? false by default
    inherit: true                   # can this role use inherited privileges?
    replication: false              # can this role do replication? false by default
    bypassrls: false                # can this role bypass row level security? false by default
    connlimit: -1                   # connection limit, -1 disable limit
    expire_at: '2030-12-31'         # 'timestamp' when this role is expired
    expire_in: 365                  # now + n days when this role is expired (OVERWRITE expire_at)
    roles: [dbrole_readwrite]       # dborole_admin|dbrole_readwrite|dbrole_readonly
    pgbouncer: true                 # add this user to pgbouncer? false by default (true for production user)
    parameters:                     # user's default search path
      search_path: public
    comment: test user

Changes

  • username field rename to name
  • groups field rename to roles
  • options now split into separated configration entries: login, superuser, createdb, createrole, inherit, replication,bypassrls,connlimit
  • expire_at and expire_in options
  • pgbouncer option for user is now false by default

Apply Changes

You can create new users on running postgres clusters with pgsql-createuser.yml playbook.

  1. Define your new users in config files (pg_users)
  2. Pass new user.name with option pg_user to playbook.
./pgsql-createuser.yml -e pg_user=<your_new_user_name>

v0.6.0

Architecture Enhancement

Bug Fix

Monitoring Provisioning Enhancement

Haproxy Enhancement

Security Enhancement

Software Update

  • Upgrade to PG 13.2 #6

  • Prometheus 2.25 / Grafana 7.4 / Consul 1.9.3 / Node Exporter 1.1 / PG Exporter 0.3.2

API Change

New Config Entries

service_registry: consul                      # none | consul | etcd | both
prometheus_options: '--storage.tsdb.retention=30d'  # prometheus cli opts
prometheus_sd_method: consul                  # Prometheus service discovery method:static|consul
prometheus_sd_interval: 2s                    # Prometheus service discovery refresh interval
pg_offline_query: false                       # set to true to allow offline queries on this instance
node_exporter_enabled: true                   # enabling Node Exporter
pg_exporter_enabled: true                     # enabling PG Exporter
pgbouncer_exporter_enabled: true              # enabling Pgbouncer Exporter
export_binary_install: false                  # install Node/PG Exporter via copy binary
dcs_disable_purge: false                      # force dcs_exists_action = abort to avoid dcs purge
pg_disable_purge: false                       # force pg_exists_action = abort to avoid pg purge
haproxy_weight: 100                           # relative lb weight for backend instance
haproxy_weight_fallback: 1                    # primary server weight in replica service group

Obsolete Config Entries

prometheus_metrics_path                       # duplicate with exporter_metrics_path 
prometheus_retention                          # covered by `prometheus_options`

v0.5.0

Pigsty now have an Official Site 🎉 !

New Features

  • Add Database Provision Template
  • Add Init Template
  • Add Business Init Template
  • Refactor HBA Rules variables
  • Fix dashboards bugs.
  • Move pg-cluster-replication to default dashboards
  • Use ZJU PostgreSQL mirror as default to accelerate repo build phase.
  • Move documentation to official site: https://pigsty.cc
  • Download newly created offline installation packages: pkg.tgz (v0.5)

Database Provision Template

Now you can customize your database content with pigsty !

pg_users:
  - username: test
    password: test
    comment: default test user
    groups: [ dbrole_readwrite ]    # dborole_admin|dbrole_readwrite|dbrole_readonly
pg_databases:                       # create a business database 'test'
  - name: test
    extensions: [{name: postgis}]   # create extra extension postgis
    parameters:                     # overwrite database meta's default search_path
      search_path: public,monitor

pg-init-template.sql wil be used as default template1 database init script pg-init-business.sql will be used as default business database init script

you can customize default role system, schemas, extensions, privileges with variables now:

Template Configuration
# - system roles - #
pg_replication_username: replicator           # system replication user
pg_replication_password: DBUser.Replicator    # system replication password
pg_monitor_username: dbuser_monitor           # system monitor user
pg_monitor_password: DBUser.Monitor           # system monitor password
pg_admin_username: dbuser_admin               # system admin user
pg_admin_password: DBUser.Admin               # system admin password

# - default roles - #
pg_default_roles:
  - username: dbrole_readonly                 # sample user:
    options: NOLOGIN                          # role can not login
    comment: role for readonly access         # comment string

  - username: dbrole_readwrite                # sample user: one object for each user
    options: NOLOGIN
    comment: role for read-write access
    groups: [ dbrole_readonly ]               # read-write includes read-only access

  - username: dbrole_admin                    # sample user: one object for each user
    options: NOLOGIN BYPASSRLS                # admin can bypass row level security
    comment: role for object creation
    groups: [dbrole_readwrite,pg_monitor,pg_signal_backend]

  # NOTE: replicator, monitor, admin password are overwritten by separated config entry
  - username: postgres                        # reset dbsu password to NULL (if dbsu is not 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]


# object created by dbsu and admin will have their privileges properly set
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

# schemas
pg_default_schemas: [monitor]

# extension
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

# postgres host-based authentication rules
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 (local production user via pgbouncer)
    role: common
    rules:
      - local   all     +dbrole_readwrite                               md5
      - host    all     +dbrole_readwrite           127.0.0.1/32        md5

  - title: allow read-only user (stats, personal) password directly access
    role: replica
    rules:
      - local   all     +dbrole_readonly                               md5
      - host    all     +dbrole_readonly           127.0.0.1/32        md5
pg_hba_rules_extra: []

# pgbouncer host-based authentication rules
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: []

v0.4.0

The second public beta (v0.4.0) of pigsty is available now ! 🎉

Monitoring System

Skim version of monitoring system consist of 10 essential dashboards:

  • PG Overview
  • PG Cluster
  • PG Service
  • PG Instance
  • PG Database
  • PG Query
  • PG Table
  • PG Table Catalog
  • PG Table Detail
  • Node

Software upgrade

  • Upgrade to PostgreSQL 13.1, Patroni 2.0.1-4, add citus to repo.
  • Upgrade to pg_exporter 0.3.1
  • Upgrade to Grafana 7.3, Ton’s of compatibility work
  • Upgrade to prometheus 2.23, with new UI as default
  • Upgrade to consul 1.9

Misc

  • Update prometheus alert rules
  • Fix alertmanager info links
  • Fix bugs and typos.
  • add a simple backup script

Offline Installation

  • pkg.tgz is the latest offline install package (1GB rpm packages, made under CentOS 7.8)

v0.3.0

The first public beta (v0.3.0) of pigsty is available now ! 🎉

Monitoring System

Skim version of monitoring system consist of 8 essential dashboards:

  • PG Overview
  • PG Cluster
  • PG Service
  • PG Instance
  • PG Database
  • PG Table Overview
  • PG Table Catalog
  • Node

Database Cluster Provision

  • All config files are merged into one file: conf/all.yml by default
  • Use infra.yml to provision meta node(s) and infrastructure
  • Use initdb.yml to provision database clusters
  • Use ins-add.yml to add new instance to database cluster
  • Use ins-del.yml to remove instance from database cluster

Offline Installation

  • pkg.tgz is the latest offline install package (1GB rpm packages, made under CentOS 7.8)

Last modified 2024-05-20: update extension list (c450f86)