# RELEASENOTE > Historical documentation recovered from the Pigsty v2.7.0 product tag. --- | Version | Time | Description | Release | |:----------------|:----------:|----------------------------------------------------------|-------------------------------------------------------------------------------------------| | [v2.7.0](#v270) | 2024-05-20 | Extension Overwhelming, new docker apps | [v2.7.0](https://github.com/Vonng/pigsty/releases/tag/v2.7.0) | | [v2.6.0](#v260) | 2024-02-28 | PG 16 as default version, ParadeDB & DuckDB | [v2.6.0](https://github.com/Vonng/pigsty/releases/tag/v2.6.0) | | [v2.5.1](#v251) | 2023-12-01 | Routine update, pg16 major extensions | [v2.5.1](https://github.com/Vonng/pigsty/releases/tag/v2.5.1) | | [v2.5.0](#v250) | 2023-10-24 | Ubuntu/Debian Support: bullseye, bookworm, jammy, focal | [v2.5.0](https://github.com/Vonng/pigsty/releases/tag/v2.5.0) | | [v2.4.1](#v241) | 2023-09-24 | Supabase/PostgresML support, graphql, jwt, pg_net, vault | [v2.4.1](https://github.com/Vonng/pigsty/releases/tag/v2.4.1) | | [v2.4.0](#v240) | 2023-09-14 | PG16, RDS Monitor, New Extensions | [v2.4.0](https://github.com/Vonng/pigsty/releases/tag/v2.4.0) | | [v2.3.1](#v231) | 2023-09-01 | PGVector with HNSW, PG16 RC1, Chinese Docs, Bug Fix | [v2.3.1](https://github.com/Vonng/pigsty/releases/tag/v2.3.1) | | [v2.3.0](#v230) | 2023-08-20 | PGSQL/REDIS Update, NODE VIP, Mongo/FerretDB, MYSQL Stub | [v2.3.0](https://github.com/Vonng/pigsty/releases/tag/v2.3.0) | | [v2.2.0](#v220) | 2023-08-04 | Dashboard & Provision overhaul, UOS compatibility | [v2.2.0](https://github.com/Vonng/pigsty/releases/tag/v2.2.0) | | [v2.1.0](#v210) | 2023-06-10 | PostgreSQL 12 ~ 16beta support | [v2.1.0](https://github.com/Vonng/pigsty/releases/tag/v2.1.0) | | [v2.0.2](#v202) | 2023-03-31 | Add pgvector support and fix MinIO CVE | [v2.0.2](https://github.com/Vonng/pigsty/releases/tag/v2.0.2) | | [v2.0.1](#v201) | 2023-03-21 | v2 Bug Fix, security enhance and bump grafana version | [v2.0.1](https://github.com/Vonng/pigsty/releases/tag/v2.0.1) | | [v2.0.0](#v200) | 2023-02-28 | Compatibility Security Maintainability Enhancement | [v2.0.0](https://github.com/Vonng/pigsty/releases/tag/v2.0.0) | | [v1.5.1](#v151) | 2022-06-18 | Grafana Security Hotfix | [v1.5.1](https://github.com/Vonng/pigsty/releases/tag/v1.5.1) | | [v1.5.0](#v150) | 2022-05-31 | Docker Applications | [v1.5.0](https://github.com/Vonng/pigsty/releases/tag/v1.5.0) | | [v1.4.1](#v141) | 2022-04-20 | Bug fix & Full translation of English documents. | [v1.4.1](https://github.com/Vonng/pigsty/releases/tag/v1.4.1) | | [v1.4.0](#v140) | 2022-03-31 | MatrixDB Support, Separated INFRA, NODES, PGSQL, REDIS | [v1.4.0](https://github.com/Vonng/pigsty/releases/tag/v1.4.0) | | [v1.3.0](#v130) | 2021-11-30 | PGCAT Overhaul & PGSQL Enhancement & Redis Support Beta | [v1.3.0](https://github.com/Vonng/pigsty/releases/tag/v1.3.0) | | [v1.2.0](#v120) | 2021-11-03 | Upgrade default Postgres to 14, monitoring existing pg | [v1.2.0](https://github.com/Vonng/pigsty/releases/tag/v1.2.0) | | [v1.1.0](#v110) | 2021-10-12 | HomePage, JupyterLab, PGWEB, Pev2 & Pgbadger | [v1.1.0](https://github.com/Vonng/pigsty/releases/tag/v1.1.0) | | [v1.0.0](#v100) | 2021-07-26 | v1 GA, Monitoring System Overhaul | [v1.0.0](https://github.com/Vonng/pigsty/releases/tag/v1.0.0) | | [v0.9.0](#v090) | 2021-04-04 | Pigsty GUI, CLI, Logging Integration | [v0.9.0](https://github.com/Vonng/pigsty/releases/tag/v0.9.0) | | [v0.8.0](#v080) | 2021-03-28 | Service Provision | [v0.8.0](https://github.com/Vonng/pigsty/releases/tag/v0.8.0) | | [v0.7.0](#v070) | 2021-03-01 | Monitor only deployment | [v0.7.0](https://github.com/Vonng/pigsty/releases/tag/v0.7.0) | | [v0.6.0](#v060) | 2021-02-19 | Architecture Enhancement | [v0.6.0](https://github.com/Vonng/pigsty/releases/tag/v0.6.0) | | [v0.5.0](#v050) | 2021-01-07 | Database Customize Template | [v0.5.0](https://github.com/Vonng/pigsty/releases/tag/v0.5.0) | | [v0.4.0](#v040) | 2020-12-14 | PostgreSQL 13 Support, Official Documentation | [v0.4.0](https://github.com/Vonng/pigsty/releases/tag/v0.4.0) | | [v0.3.0](#v030) | 2020-10-22 | Provisioning Solution GA | [v0.3.0](https://github.com/Vonng/pigsty/releases/tag/v0.3.0) | | v0.2.0 | 2020-07-10 | PGSQL Monitoring v6 GA | [v0.2.0](https://github.com/Vonng/pigsty/commit/385e33a62a19817e8ba19997260e6b77d99fe2ba) | | v0.1.0 | 2020-06-20 | Validation on Testing Environment | [v0.1.0](https://github.com/Vonng/pigsty/commit/1cf2ea5ee91db071de00ec805032928ff582453b) | | v0.0.5 | 2020-08-19 | Offline Installation Mode | [v0.0.5](https://github.com/Vonng/pigsty/commit/0fe9e829b298fe5e56307de3f78c95071de28245) | | v0.0.4 | 2020-07-27 | Refactor playbooks into ansible roles | [v0.0.4](https://github.com/Vonng/pigsty/commit/90b44259818d2c71e37df5250fe8ed1078a883d0) | | v0.0.3 | 2020-06-22 | Interface enhancement | [v0.0.3](https://github.com/Vonng/pigsty/commit/4c5c68ccd57bc32a9e9c98aa3f264aa19f45c7ee) | | v0.0.2 | 2020-04-30 | First Commit | [v0.0.2](https://github.com/Vonng/pigsty/commit/dd646775624ddb33aef7884f4f030682bdc371f8) | | v0.0.1 | 2019-05-15 | POC | [v0.0.1](https://github.com/Vonng/pg/commit/fa2ade31f8e81093eeba9d966c20120054f0646b) | ---------------- ## v2.7.0 **Highlight** Extension Overwhelming, adding numerous new extensions written in `rust` & `pgrx`: - [pg_search](https://github.com/paradedb/paradedb/tree/dev/pg_search) v0.7.0 : Full text search over SQL tables using the BM25 algorithm - [pg_lakehouse](https://github.com/paradedb/paradedb/tree/dev/pg_lakehouse) v0.7.0 : Query engine over object stores like S3 and table formats like Delta Lake - [pg_analytics](https://github.com/paradedb/pg_analytics) v0.6.1 : Accelerates analytical query processing inside Postgres - [pg_graphql](https://github.com/supabase/pg_graphql) v1.5.4 : GraphQL support to your PostgreSQL database. - [pg_jsonschema](https://github.com/supabase/pg_jsonschema) v0.3.1 : PostgreSQL extension providing JSON Schema validation - [wrappers](https://github.com/supabase/wrappers) v0.3.1 : Postgres Foreign Data Wrappers Collections by Supabase - [pgmq](https://github.com/tembo-io/pgmq) v1.5.2 : A lightweight message queue. Like AWS SQS and RSMQ but on Postgres. - [pg_tier](https://github.com/tembo-io/pg_tier) v0.0.3 : Postgres Extension written in Rust, to enable data tiering to AWS S3 - [pg_vectorize](https://github.com/tembo-io/pg_vectorize) v0.15.0 : The simplest way to orchestrate vector search on Postgres - [pg_later](https://github.com/tembo-io/pg_later) v0.1.0 : Execute SQL now and get the results later. - [pg_idkit](https://github.com/VADOSWARE/pg_idkit) v0.2.3 : Generating many popular types of identifiers - [plprql](https://github.com/kaspermarstal/plprql) v0.1.0 : Use PRQL in PostgreSQL - [pgsmcrypto](https://github.com/zhuobie/pgsmcrypto) v0.1.0 : PostgreSQL SM Algorithm Extension - [pg_tiktoken](https://github.com/kelvich/pg_tiktoken) v0.0.1 : OpenAI tiktoken tokenizer for postgres - [pgdd](https://github.com/rustprooflabs/pgdd) v0.5.2 : Access Data Dictionary metadata with pure SQL And some new extensions in plain C & C++ - [parquet_s3_fdw](https://github.com/pgspider/parquet_s3_fdw) 1.1.0 : ParquetS3 Foreign Data Wrapper for PostgresSQL - [plv8](https://github.com/plv8/plv8) 3.2.2 : V8 Engine Javascript Procedural Language add-on for PostgreSQL - [md5hash](https://github.com/tvondra/md5hash) 1.0.1 : Custom data type for storing MD5 hashes rather than text - [pg_tde](https://github.com/Percona-Lab/pg_tde) 1.0 alpha: Experimental encrypted access method for PostgreSQL - [pg_dirtyread](https://github.com/df7cb/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](https://github.com/Vonng/pigsty/issues/402) 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](https://github.com/Vonng/pigsty/tree/v2.7.0/app/odoo): launch open source ERP and plugins - [Jupyter](https://github.com/Vonng/pigsty/tree/v2.7.0/app/jupyter): run jupyter notebook container - [PolarDB](https://github.com/Vonng/pigsty/tree/v2.7.0/app/polardb): run the demo PG RAC playground. - [supabase](https://github.com/Vonng/pigsty/tree/v2.7.0/app/supabase): bump to the latest GA version. - [bytebase](https://github.com/Vonng/pigsty/tree/v2.7.0/app/bytebase): use the `latest` tag instead of ad hoc version. - [pg_exporter](https://github.com/Vonng/pigsty/tree/v2.7.0/app/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** ```bash 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](https://www.paradedb.com/) extensions: `pg_analytics`, `pg_bm25`, and `pg_sparse` * Introduce [DuckDB](https://duckdb.org/) 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](https://github.com/alitrack/duckdb_fdw) v1.1 - New Extension [pgsql-gzip](https://github.com/pramsey/pgsql-gzip) v1.0.0 - New Extension [pg_sparse](https://github.com/paradedb/paradedb/tree/dev/pg_sparse) from ParadeDB: v0.5.6 - New Extension [pg_bm25](https://github.com/paradedb/paradedb/tree/dev/pg_bm25) from ParadeDB: v0.5.6 - New Extension [pg_analytics](https://github.com/paradedb/paradedb/tree/dev/pg_analytics) from ParadeDB: v0.5.6 - Bump AI/ML Extension [pgml](https://github.com/postgresml/postgresml) to v2.8.1 with pg16 support - Bump Columnar Extension [hydra](https://github.com/hydradatabase/) to v1.1.1 with pg16 support - Bump Graph Extension [age](https://github.com/apache/age) to v1.5.0 with pg16 support - Bump Packaging Extension [pg_tle](https://github.com/aws/pg_tle) to v1.3.4 with pg16 support - Bump GraphQL Extension [pg_graphql](https://github.com/supabase/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 ```bash curl -L https://get.pigsty.cc/latest | bash ``` **Highlights** - [Ubuntu](https://github.com/Vonng/pigsty/blob/v2.5.0/files/pigsty/ubuntu.yml) / [Debian](https://github.com/Vonng/pigsty/blob/v2.5.0/files/pigsty/debian.yml) 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`](https://github.com/Vonng/pigsty/blob/v2.5.0/files/pigsty/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 ```yaml - 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](https://supabase.com/) support: run open-source Firebase alternative with external postgres managed by Pigsty: [example config](https://github.com/Vonng/pigsty/tree/v2.7.0/app/supabase) - [PostgresML](https://postgresml.org/) support: Run LLMs, vector operations, classical Machine Learning in Postgres. - GraphQL support: [`pg_graphql`](https://github.com/supabase/pg_graphql) reflects a GraphQL schema from the existing SQL schema. - Async HTTP Client support [`pg_net`](https://github.com/supabase/pg_net) enables asynchronous (non-blocking) HTTP/HTTPS requests with SQL - JWT support: [`pgjwt`](https://github.com/michelp/pgjwt) is the PostgreSQL implementation of JWT (JSON Web Tokens) - Vault support: [`vault`](https://github.com/supabase/vault) can store encrypted secrets in the Vault - New component [`pg_filedump`](https://github.com/df7cb/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](https://github.com/FerretDB/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`](https://get.pigsty.cc) and [`demo.pigsty.cc`](https://demo.pigsty.cc) * PGSQL: Fix [CVE-2023-39417](https://www.postgresql.org/about/news/postgresql-154-149-1312-1216-1121-and-postgresql-16-beta-3-released-2689/) 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](https://nocodb.com/) * REDIS: bump to v7.2 and rework on dashboards * MONGO: basic deploy & monitor support with [FerretDB](https://www.ferretdb.io/) 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](https://github.com/Vonng/pigsty/commit/29434bdd39548d95d80a236de9099874ed564f9b) * Append infra os group rather than set it as primary group * Fix redis sentinel systemd enable status [5c96feb](https://github.com/Vonng/pigsty/commit/5c96feb598ad6e44daa7a595e34c87e67952777b) * Loose `bootstrap` & `configure` if `/etc/redhat-release` not exists * Fix grafana 9.x [CVE-2023-1410](https://grafana.com/blog/2023/03/22/grafana-security-release-new-versions-with-security-fixes-for-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/` ```bash 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`](https://github.com/pgvector/pgvector) * New extension [`pgvector`](https://github.com/Vonng/pigsty/issues/267) * [MinIO CVE-2023-28432](https://github.com/Vonng/pigsty/issues/265) fix, and upgrade to 20230324 with new policy API: **Changes** * New extension [`pgvector`](https://github.com/Vonng/pigsty/issues/267) for storing OpenAI embedding and searching similar vectors. * [MinIO CVE-2023-28432](https://github.com/Vonng/pigsty/issues/265) 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 ```bash 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](https://github.com/Vonng/pigsty/releases/tag/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** ```bash 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** ```bash 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](https://github.com/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: ```bash curl -fsSL http://get.pigsty.cc/latest | bash ```
Download directly from GitHub Release ```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 `/` 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](https://github.com/Vonng/pigsty/commit/68f8c8da0576e043686137aca47bad01dd4e82c0) * [add docker-compose to default pacakge list](https://github.com/Vonng/pigsty/commit/765077744836d8600a8703dce8623182784d65a7) * [disable nameserver by default & enable docker role by default](https://github.com/Vonng/pigsty/commit/9b8b7c5209d09049716318b624001de3b567452a) **Bug Fix** * [fix promtail & loki config var issue](https://github.com/Vonng/pigsty/commit/100f31842d473001f946200d9b8e2e3e89add35a) * Fix grafana legacy alerts. * Disable nameserver by default * Rename pg-alias.sh for patroni shortcuts * [disable exemplars queries for all dashboards](https://github.com/Vonng/pigsty/commit/62d74f15f8c164e5971d9d1d1215869359267b53) * [fix loki data dir issue](https://github.com/Vonng/pigsty/commit/b277f488b7ed0fd0a7eeeb995e2efd75a048e0ef) https://github.com/Vonng/pigsty/issues/100 * [change autovacuum_freeze_max_age from 100000000 to 1000000000](https://github.com/Vonng/pigsty/commit/1690b490205af24d5f0394aba99007c0d9ebc49d) ---------------- ## 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](https://github.com/Vonng/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: ```bash 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](https://grafana.com/blog/2021/12/07/grafana-8.3.1-8.2.7-8.1.8-and-8.0.7-released-with-high-severity-security-fix/)) * 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 ```bash 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`](https://doc.pigsty.cc/#/zh-cn/v-node?id=node_meta_pip_install) * rename var: `grafana_url` to [`grafana_endpoint`](https://doc.pigsty.cc/#/zh-cn/v-meta?id=grafana_endpoint) * new var: [`grafana_admin_username`](https://doc.pigsty.cc/#/zh-cn/v-meta?id=grafana_admin_username) * new var: [`grafana_database`](https://doc.pigsty.cc/#/zh-cn/v-meta?id=grafana_database) * new var: [`grafana_pgurl`](https://doc.pigsty.cc/#/zh-cn/v-meta?id=grafana_pgurl) * new var: [`pg_shared_libraries`](https://doc.pigsty.cc/#/zh-cn/v-pg-provision?id=pg_shared_libraries) * new var: [`pg_exporter_auto_discovery`](https://doc.pigsty.cc/#/zh-cn/v-monitor?id=pg_exporter_auto_discovery) * new var: [`pg_exporter_exclude_database`](https://doc.pigsty.cc/#/zh-cn/v-monitor?id=pg_exporter_exclude_database) * new var: [`pg_exporter_include_database`](https://doc.pigsty.cc/#/zh-cn/v-monitor?id=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
**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`. ```yaml #------------------------------------------------------------------------------ # 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** ```yml # - 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? ```yaml # 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. ```yaml 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** * [Monitor Only Deployment Support #25](https://github.com/Vonng/pigsty/issues/25) * [Split monolith static monitor target file into per-cluster conf #36](https://github.com/Vonng/pigsty/issues/36) * [Add create user playbook #29](https://github.com/Vonng/pigsty/issues/29) * [Add create database playbook #28](https://github.com/Vonng/pigsty/issues/28) * [Database provisioning interface enhancement #33](https://github.com/Vonng/pigsty/issues/33) * [User provisioning interface enhancement #34](https://github.com/Vonng/pigsty/issues/34) **Bug Fix** * [Create extension with schema typo #32](https://github.com/Vonng/pigsty/issues/32) * [pgbouncer reload with systemctl not work #35](https://github.com/Vonng/pigsty/issues/35) **API Changes** ***New Options*** ```yml 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*** ```yml exporter_binary_install: false # covered by exporter_install ``` ***Structure Changes*** ```yaml pg_default_roles # refer to pg_users pg_users # refer to pg_users pg_databases # refer to pg_databases ``` ***Rename Options*** ```yml pg_default_privilegs -> pg_default_privileges # fix typo ``` **Enhancement** **Monitoring Provisioning Enhancement** * [Decouple consul #13](https://github.com/Vonng/pigsty/issues/13) * [Binary install mode for node_exporter and pg_exporter #14](https://github.com/Vonng/pigsty/issues/14) * [Prometheus static targets mode support #11](https://github.com/Vonng/pigsty/issues/11) **Haproxy Enhancement** * [Adjust relative traffic weight with configuration #10](https://github.com/Vonng/pigsty/issues/10) * [HAProxy admin page access via nginx #12](https://github.com/Vonng/pigsty/issues/12) * [Readonly traffic fallback on primary if all replicas down #8](https://github.com/Vonng/pigsty/issues/8) **Security Enhancement** * [Offline access control #7](https://github.com/Vonng/pigsty/issues/7) * [Safeguard for purge functionality #9](https://github.com/Vonng/pigsty/issues/9) **Software Update** * [Upgrade to PG 13.2 #6](https://github.com/Vonng/pigsty/issues/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*** ```yaml 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*** ```yaml prometheus_metrics_path # duplicate with exporter_metrics_path prometheus_retention # covered by `prometheus_options` ``` **Database Definition** [Database provisioning interface enhancement #33](https://github.com/Vonng/pigsty/issues/33) ***Old Schema*** ```yaml 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*** ```yaml 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. ```bash ./pgsql-createdb.yml -e pg_database= ``` **User Definition** [User provisioning interface enhancement #34](https://github.com/Vonng/pigsty/issues/34) ***Old Schema*** ```yaml 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*** ```yaml 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. ```bash ./pgsql-createuser.yml -e pg_user= ```
---------------- ## v0.6.0 Architecture Enhancement
**Bug Fix** * [pg_hba reset on patroni restart (patroni 2.0) #5](https://github.com/Vonng/pigsty/issues/5) * Merge [Fix name of dashboard #1](https://github.com/Vonng/pigsty/pull/1), Fix PG Overview Dashboard typo * Fix default primary instance to `pg-test-1` of cluster `pg-test` in sandbox environment * Fix obsolete comments **Monitoring Provisioning Enhancement** * [Decouple consul #13](https://github.com/Vonng/pigsty/issues/13) * [Binary install mode for node_exporter and pg_exporter #14](https://github.com/Vonng/pigsty/issues/14) * [Prometheus static targets mode support #11](https://github.com/Vonng/pigsty/issues/11) **Haproxy Enhancement** * [Adjust relative traffic weight with configuration #10](https://github.com/Vonng/pigsty/issues/10) * [HAProxy admin page access via nginx #12](https://github.com/Vonng/pigsty/issues/12) * [Readonly traffic fallback on primary if all replicas down #8](https://github.com/Vonng/pigsty/issues/8) **Security Enhancement** * [Offline access control #7](https://github.com/Vonng/pigsty/issues/7) * [Safeguard for purge functionality #9](https://github.com/Vonng/pigsty/issues/9) **Software Update** * [Upgrade to PG 13.2 #6](https://github.com/Vonng/pigsty/issues/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** ```yaml 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** ```yaml prometheus_metrics_path # duplicate with exporter_metrics_path prometheus_retention # covered by `prometheus_options` ```
---------------- ## v0.5.0 Pigsty now have an [Official Site](http://pigsty.cc/) 🎉 !
**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](https://github.com/Vonng/pigsty/releases/download/v0.5.0/pkg.tgz) (v0.5) **Database Provision Template** Now you can customize your database content with pigsty ! ```yaml 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](https://github.com/Vonng/pigsty/blob/v0.5.0/roles/postgres/templates/pg-init-template.sql) wil be used as default template1 database init script [pg-init-business.sql](https://github.com/Vonng/pigsty/blob/v0.5.0/roles/postgres/templates/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 ```yaml # - 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`](https://github.com/Vonng/pg_exporter/releases/tag/v0.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](https://github.com/Vonng/pigsty/releases/download/v0.4.0/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](https://github.com/Vonng/pigsty/releases/download/v0.3.0/pkg.tgz) is the latest offline install package (1GB rpm packages, made under CentOS 7.8)