RELEASENOTE

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

• @NeroSong made their first contribution in https://github.com/Vonng/pigsty/pull/373
• @waitingsong made their first contribution in https://github.com/Vonng/pigsty/pull/405

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

• Use PostgreSQL 16 as the default major version (16.2)
• Introduce ParadeDB extensions: pg_analytics, pg_bm25, and pg_sparse
• Introduce DuckDB and corresponding foreign data wrapper: duckdb_fdw
• Cloudflare CDN https://repo.pigsty.io and QCloud CDN https://repo.pigsty.cc

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

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: imgsmlr， pg_similarity，pg_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.1

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.1.el7.x86_64.tgz) = 81db95f1c591008725175d280ad23615
MD5 (pigsty-pkg-v2.3.1.el8.x86_64.tgz) = 6f4d169b36f6ec4aa33bfd5901c9abbe
MD5 (pigsty-pkg-v2.3.1.el9.x86_64.tgz) = 4bc9ae920e7de6dd8988ca7ee681459d

v2.2.0

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

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:
  • http://get.pigsty.cc/rpm/pev.html
  • http://get.pigsty.cc/rpm/chart.tgz
• 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

• New extension pgvector
• MinIO CVE-2023-28432 fix, and upgrade to 20230324 with new policy API:

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

# get from GitHub
bash -c "$(curl -fsSL https://raw.githubusercontent.com/Vonng/pigsty/v2.0.0/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.

• add docker to default packages
• add docker-compose to default pacakge list
• disable nameserver by default & enable docker role by default

Bug Fix

• fix promtail & loki config var issue
• Fix grafana legacy alerts.
• Disable nameserver by default
• Rename pg-alias.sh for patroni shortcuts
• disable exemplars queries for all dashboards
• fix loki data dir issue https://github.com/Vonng/pigsty/issues/100
• change autovacuum_freeze_max_age from 100000000 to 1000000000

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, … planned)

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

• new var : node_meta_pip_install
• rename var: grafana_url to grafana_endpoint
• new var: grafana_admin_username
• new var: grafana_database
• new var: grafana_pgurl
• new var: pg_shared_libraries
• new var: pg_exporter_auto_discovery
• new var: pg_exporter_exclude_database
• new var: pg_exporter_include_database

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

v0.8.0

Service Provisioning support is added in this release

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

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

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

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

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

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

exporter_binary_install: false                 # covered by exporter_install

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

pg_default_privilegs -> pg_default_privileges  # fix typo

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

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

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

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

./pgsql-createdb.yml -e pg_database=<your_new_database_name>

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

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

./pgsql-createuser.yml -e pg_user=<your_new_user_name>

v0.6.0

Architecture Enhancement

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

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

v0.5.0

Pigsty now have an Official Site 🎉 !

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

# - 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 ! 🎉

v0.3.0

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