Release Notes
Module:
Categories:
Version | Time | Description | Release |
---|---|---|---|
v3.0.3 | 2024-09-27 | PostgreSQL 17, Etcd Enhancement, IvorySQL 3.4,PostGIS 3.5 | v3.0.3 |
v3.0.2 | 2024-09-07 | Mini Install, PolarDB 15, Bloat View Update | v3.0.2 |
v3.0.1 | 2024-08-31 | Oracle Compatibility, Patroni 4.0, Routine Bug Fix | v3.0.1 |
v3.0.0 | 2024-08-30 | Extension Exploding & Plugable Kernels (MSSQL,Oracle) | v3.0.0 |
v2.7.0 | 2024-05-16 | Extension Overwhelming, new docker apps | v2.7.0 |
v2.6.0 | 2024-02-29 | PG 16 as default version, ParadeDB & DuckDB | v2.6.0 |
v2.5.1 | 2023-12-01 | Routine update, pg16 major extensions | v2.5.1 |
v2.5.0 | 2023-10-24 | Ubuntu/Debian Support: bullseye, bookworm, jammy, focal | v2.5.0 |
v2.4.1 | 2023-09-24 | Supabase/PostgresML support, graphql, jwt, pg_net, vault | v2.4.1 |
v2.4.0 | 2023-09-14 | PG16, RDS Monitor, New Extensions | v2.4.0 |
v2.3.1 | 2023-09-01 | PGVector with HNSW, PG16 RC1, Chinese Docs, Bug Fix | v2.3.1 |
v2.3.0 | 2023-08-20 | PGSQL/REDIS Update, NODE VIP, Mongo/FerretDB, MYSQL Stub | v2.3.0 |
v2.2.0 | 2023-08-04 | Dashboard & Provision overhaul, UOS compatibility | v2.2.0 |
v2.1.0 | 2023-06-10 | PostgreSQL 12 ~ 16beta support | v2.1.0 |
v2.0.2 | 2023-03-31 | Add pgvector support and fix MinIO CVE | v2.0.2 |
v2.0.1 | 2023-03-21 | v2 Bug Fix, security enhance and bump grafana version | v2.0.1 |
v2.0.0 | 2023-02-28 | Compatibility Security Maintainability Enhancement | v2.0.0 |
v1.5.1 | 2022-06-18 | Grafana Security Hotfix | v1.5.1 |
v1.5.0 | 2022-05-31 | Docker Applications | v1.5.0 |
v1.4.1 | 2022-04-20 | Bug fix & Full translation of English documents. | v1.4.1 |
v1.4.0 | 2022-03-31 | MatrixDB Support, Separated INFRA, NODES, PGSQL, REDIS | v1.4.0 |
v1.3.0 | 2021-11-30 | PGCAT Overhaul & PGSQL Enhancement & Redis Support Beta | v1.3.0 |
v1.2.0 | 2021-11-03 | Upgrade default Postgres to 14, monitoring existing pg | v1.2.0 |
v1.1.0 | 2021-10-12 | HomePage, JupyterLab, PGWEB, Pev2 & Pgbadger | v1.1.0 |
v1.0.0 | 2021-07-26 | v1 GA, Monitoring System Overhaul | v1.0.0 |
v0.9.0 | 2021-04-04 | Pigsty GUI, CLI, Logging Integration | v0.9.0 |
v0.8.0 | 2021-03-28 | Service Provision | v0.8.0 |
v0.7.0 | 2021-03-01 | Monitor only deployment | v0.7.0 |
v0.6.0 | 2021-02-19 | Architecture Enhancement | v0.6.0 |
v0.5.0 | 2021-01-07 | Database Customize Template | v0.5.0 |
v0.4.0 | 2020-12-14 | PostgreSQL 13 Support, Official Documentation | v0.4.0 |
v0.3.0 | 2020-10-22 | Provisioning Solution GA | v0.3.0 |
v0.2.0 | 2020-07-10 | PGSQL Monitoring v6 GA | v0.2.0 |
v0.1.0 | 2020-06-20 | Validation on Testing Environment | v0.1.0 |
v0.0.5 | 2020-08-19 | Offline Installation Mode | v0.0.5 |
v0.0.4 | 2020-07-27 | Refactor playbooks into ansible roles | v0.0.4 |
v0.0.3 | 2020-06-22 | Interface enhancement | v0.0.3 |
v0.0.2 | 2020-04-30 | First Commit | v0.0.2 |
v0.0.1 | 2019-05-15 | POC | v0.0.1 |
v3.0.3
New Feature:
- PostgreSQL 17 support through config file
- WiltonDB 16, compatible with PG 16
- IvorySQL 3.4, sync with PostgreSQL 16.4
- ETCD alerting & monitoring enhancement
Version Upgrade:
- PostGIS 3.5
- Grafana 11.2
- duckdb 1.1
- pg_search 0.10.2
- pg_analytics 0.1.4
v3.0.2
Features
- Minimal Installation support with
pgsql-min.yml
(NODE, PGSQL, ETCD only) - Official support for the latest PolarDB for PostgreSQL Kernel v15
- Optimize
monitor.pg_table_bloat
andmonitor.pg_index_bloat
with security definer wrap functions to avoid PolarDB statistics view permission issue. - Honor
prometheus_enabled
,grafana_enabled
option in target register, not register when disabled. - Add
PGDATABASE
andPGPORT
environment variable to/etc/profile.d/pgsql.sh
, set topg_primary_db
(defaultpostgres
)
Changes
- Remove PolarDB 11, CloudberryDB 1.5.4 packages from pgsql repo.
- New dedicate repo for PolarDB 15, CloudberryDB 1.6.0, MSSQL, IvorySQL.
Bug Fix
- Fix redis
/etc/tmp.files.d
entry name - Set
PGHOST
andPGPORT
when managing pgbouncer users. - Remove support for
pg_snakeoil
due toclamv
deps break on EL8 - Remove role
pgsql
notify handler for ansible 2.9 compatibility
v3.0.1
Features & Enhancement
- Oracle Compatibility through PolarDB-O (3rd commercial kernel)
- Rewrite monitoring views and SQL statements using Oracle-compatible SQL syntax
- Patroni 4 support and adaptation
- New extension
pg_analytics
enhances PostgreSQL with analytics capabilities via DuckDB - Added new extensions:
odbc_fdw
andjdbc_fdw
, providing universal external data source connectivity - Added new kernel
cloudberrydb
(open-source fork by the original Greenplum developers) - Add a restart after PGSQL primary initialization to apply
pg_param
&pg_files
, no need to reboot after provisioning a Supabase PG / PolarDB cluster. - Add new grafana plugin:
volkovlabs-rss-datasource
- Add new dashboard panel: Extensions in PGCAT databases
- Bump Pev2 version to 1.12.1
Bug Fix
- Fix Grafana 11.1.4 panel plugin failed to load by default
- Fix BlackBox Exporter Ping probe failed on certain OS Distro (run as root rather than prometheus)
- Make suare
/var/run/postgresql
and/var/run/redis
temporary directory always auto-create after reboot - Fix
cache.yml
playbook not correctly remove outdated Patroni 3.0.4 RPM package - Correct description errors in some alerting rules
- Remove outdated Bootstrap User/HBA parameters from Patroni configuration file
v3.0.0
Get started with:
curl -fsSL https://repo.pigsty.io/get | bash
cd ~/pigsty; ./bootstrap; ./configure; ./install.yml
Highlight Features
Extension Exploding:
Pigsty now has an unprecedented 336 available extensions for PostgreSQL. This includes 121 extension RPM packages and 133 DEB packages, surpassing the total number of extensions provided by the PGDG official repository (135 RPM/109 DEB). Pigsty has ported unique PG extensions from the EL/DEB system to each other, achieving a great alignment of extension ecosystems between the two major distributions.
A crude list of the extension ecosystem is as follows:
- timescaledb periods temporal_tables emaj table_version pg_cron pg_later pg_background pg_timetable
- postgis pgrouting pointcloud pg_h3 q3c ogr_fdw geoip #pg_geohash #mobilitydb
- pgvector pgvectorscale pg_vectorize pg_similarity pg_tiktoken pgml #smlar
- pg_search pg_bigm zhparser hunspell
- hydra pg_lakehouse pg_duckdb duckdb_fdw pg_fkpart pg_partman plproxy #pg_strom citus
- pg_hint_plan age hll rum pg_graphql pg_jsonschema jsquery index_advisor hypopg imgsmlr pg_ivm pgmq pgq #rdkit
- pg_tle plv8 pllua plprql pldebugger plpgsql_check plprofiler plsh #pljava plr pgtap faker dbt2
- prefix semver pgunit md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_currency pgmp numeral pg_rational pguint ip4r timestamp9 chkpass #pg_uri #pgemailaddr #acl #debversion #pg_rrule
- topn pg_gzip pg_http pg_net pg_html5_email_address pgsql_tweaks pg_extra_time pg_timeit count_distinct extra_window_functions first_last_agg tdigest aggs_for_arrays pg_arraymath pg_idkit pg_uuidv7 permuteseq pg_hashids
- sequential_uuids pg_math pg_random pg_base36 pg_base62 floatvec pg_financial pgjwt pg_hashlib shacrypt cryptint pg_ecdsa pgpcre icu_ext envvar url_encode #pg_zstd #aggs_for_vecs #quantile #lower_quantile #pgqr #pg_protobuf
- pg_repack pg_squeeze pg_dirtyread pgfincore pgdd ddlx pg_prioritize pg_checksums pg_readonly safeupdate pg_permissions pgautofailover pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_savior table_log pg_fio #pgpool pgagent
- pg_profile pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_store_plans pg_track_settings pg_wait_sampling system_stats pg_meta pgnodemx pg_sqlog bgw_replstatus pgmeminfo toastinfo pagevis powa pg_top #pg_statviz #pgexporter_ext #pg_mon
- passwordcheck supautils pgsodium pg_vault anonymizer pg_tde pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor noset #sslutils
- wrappers multicorn mysql_fdw tds_fdw sqlite_fdw pgbouncer_fdw mongo_fdw redis_fdw pg_redis_pubsub kafka_fdw hdfs_fdw firebird_fdw aws_s3 log_fdw #oracle_fdw #db2_fdw
- orafce pgtt session_variable pg_statement_rollback pg_dbms_metadata pg_dbms_lock pgmemcache #pg_dbms_job #wiltondb
- pglogical pgl_ddl_deploy pg_failover_slots wal2json wal2mongo decoderbufs decoder_raw mimeo pgcopydb pgloader pg_fact_loader pg_bulkload pg_comparator pgimportdoc pgexportdoc #repmgr #slony
- gis-stack rag-stack fdw-stack fts-stack etl-stack feat-stack olap-stack supa-stack stat-stack json-stack
Plugable Kernels:
Pigsty v3 allows you to replace the PostgreSQL kernel, currently supporting Babelfish (SQL Server compatible, with wire protocol emulation), IvorySQL (Oracle compatible), and RAC PolarDB for PostgreSQL. Additionally, self-hosted Supabase is now available on Debian systems. You can emulate MSSQL (via WiltonDB), Oracle (via IvorySQL), Oracle RAC (via PolarDB), MongoDB (via FerretDB), and Firebase (via Supabase) in Pigsty with production-grade PostgreSQL clusters featuring HA, IaC, PITR, and monitoring.
Pro Edition:
We now offer PGSTY Pro, a professional edition that provides value-added services on top of the open-source features. The professional edition includes additional modules: MSSQL, Oracle, Mongo, K8S, Victoria, Kafka, etc., and offers broader support for PG major versions, operating systems, and chip architectures. It provides offline installation packages customized for precise minor versions of all operating systems, and support for legacy systems like EL7, Debian 11, Ubuntu 20.04.
Major Changes
This Pigsty release updates the major version number from 2.x to 3.0, with several significant changes:
- Primary supported operating systems updated to: EL 8 / EL 9 / Debian 12 / Ubuntu 22.04
- EL7 / Debian 11 / Ubuntu 20.04 systems are now deprecated and no longer supported.
- Users needing to run on these systems should consider our subscription service.
- Default to online installation, offline packages are no longer provided to resolve minor OS version compatibility issues.
- The
bootstrap
process will no longer prompt for downloading offline packages, but if/tmp/pkg.tgz
exists, it will still use the offline package automatically. - For offline installation needs, please create offline packages yourself or consider our subscription service.
- The
- Unified adjustment of upstream software repositories used by Pigsty, address changes, and GPG signing and verification for all packages.
- Standard repository:
https://repo.pigsty.io/{apt/yum}
- Domestic mirror:
https://repo.pigsty.cc/{apt/yum}
- Standard repository:
- API parameter changes and configuration template changes
- Configuration templates for EL and Debian systems are now consolidated, with differing parameters managed in the
roles/node_id/vars/
directory. - Configuration directory changes, all configuration file templates are now placed in the
conf
directory and categorized intodefault
,dbms
,demo
,build
.
- Configuration templates for EL and Debian systems are now consolidated, with differing parameters managed in the
Docker
is now completely treated as a separate module, and will not be downloaded by default- New beta module:
KAFKA
- New beta module:
KUBE
Other New Features
- Epic enhancement of PG OLAP analysis capabilities: DuckDB 1.0.0, DuckDB FDW, and PG Lakehouse, Hydra have been ported to the Debian system.
- Strengthened PG vector search and full-text search capabilities: Vectorscale provides DiskANN vector indexing, Hunspell dictionary support, pg_search 0.8.6.
- Resolved package build issues for ParadeDB, now available on Debian/Ubuntu.
- All required extensions for Supabase are now available on Debian/Ubuntu, making Supabase self-hostable across all OSes.
- Provided capability for scenario-based pre-configured extension stacks. If you’re unsure which extensions to install, we offer extension recommendation packages (Stacks) tailored for specific application scenarios.
- Created metadata tables, documentation, indexes, and name mappings for all PostgreSQL ecosystem extensions, ensuring alignment and usability for both EL and Debian systems.
- Enhanced
proxy_env
parameter functionality to mitigate DockerHub ban issues, simplifying configuration. - Established a new dedicated software repository offering all extension plugins for versions 12-17, with the PG16 extension repository implemented by default in Pigsty.
- Upgraded existing software repositories, employing standard signing and verification mechanisms to ensure package integrity and security. The APT repository adopts a new standard layout built through
reprepro
. - Provided sandbox environments for 1, 2, 3, 4, 43 nodes:
meta
,dual
,trio
,full
,prod
, and quick configuration templates for 7 major OS Distros. - Add PostgreSQL 17 and pgBouncer 1.23 metrics support in pg_exporter config, adding related dashboard panels.
- Add logs panel for PGSQL Pgbouncer / PGSQL Patroni Dashboard
- Add new playbook
cache.yml
to make offline packages, instead of bashbin/cache
andbin/release-pkg
API Changes
- New parameter option:
pg_mode
now have several new options:pgsql
: Standard PostgreSQL high availability cluster.citus
: Citus horizontally distributed PostgreSQL native high availability cluster.gpsql
: Monitoring for Greenplum and GP compatible databases (Pro edition).mssql
: Install WiltonDB / Babelfish to provide Microsoft SQL Server compatibility mode for standard PostgreSQL high availability clusters, with wire protocol level support, extensions unavailable.ivory
: Install IvorySQL to provide Oracle compatibility for PostgreSQL high availability clusters, supporting Oracle syntax/data types/functions/stored procedures, extensions unavailable (Pro edition).polar
: Install PolarDB for PostgreSQL (PG RAC) open-source version to support localization database capabilities, extensions unavailable (Pro edition).
- New parameter:
pg_parameters
, used to specify parameters inpostgresql.auto.conf
at the instance level, overriding cluster configurations for personalized settings on different instance members. - New parameter:
pg_files
, used to specify additional files to be written to the PostgreSQL data directory, to support license feature required by some kernel forks. - New parameter:
repo_extra_packages
, used to specify additional packages to download, to be used in conjunction withrepo_packages
, facilitating the specification of extension lists unique to OS versions. - Parameter renaming:
patroni_citus_db
renamed topg_primary_db
, used to specify the primary database in the cluster (used in Citus mode). - Parameter enhancement: Proxy server configurations in
proxy_env
will be written to the Docker Daemon to address internet access issues, and theconfigure -x
option will automatically write the proxy server configuration of the current environment. - Parameter enhancement: Allow using
path
item ininfra_portal
entries, to expose local dir as web service rather than proxy to another upstream. - Parameter enhancement: The
repo_url_packages
inrepo.pigsty.io
will automatically switch torepo.pigsty.cc
when the region is China, addressing internet access issues. Additionally, the downloaded file name can now be specified. - Parameter enhancement: The
extension
field inpg_databases.extensions
now supports both dictionary and extension name string modes. The dictionary mode offersversion
support, allowing the installation of specific extension versions. - Parameter enhancement: If the
repo_upstream
parameter is not explicitly overridden, it will extract the default value for the corresponding system fromrpm.yml
ordeb.yml
. - Parameter enhancement: If the
repo_packages
parameter is not explicitly overridden, it will extract the default value for the corresponding system fromrpm.yml
ordeb.yml
. - Parameter enhancement: If the
infra_packages
parameter is not explicitly overridden, it will extract the default value for the corresponding system fromrpm.yml
ordeb.yml
. - Parameter enhancement: If the
node_default_packages
parameter is not explicitly overridden, it will extract the default value for the corresponding system fromrpm.yml
ordeb.yml
. - Parameter enhancement: The extensions specified in
pg_packages
andpg_extensions
will now perform a lookup and translation from thepg_package_map
defined inrpm.yml
ordeb.yml
. - Parameter enhancement: Packages specified in
node_packages
andpg_extensions
will be upgraded to the latest version upon installation. The default value innode_packages
is now[openssh-server]
, helping to fix the OpenSSH CVE. - Parameter enhancement:
pg_dbsu_uid
will automatically adjust to26
(EL) or543
(Debian) based on the operating system type, avoiding manual adjustments. - pgBouncer Parameter update,
max_prepared_statements = 128
enabled prepared statement support in transaction pooling mode, and setserver_lifetime
to 600. - Patroni template parameter update, uniformly increase
max_worker_processes
+8 available backend processes, increasemax_wal_senders
andmax_replication_slots
to 50, and increase the OLAP template temporary file size limit to 1/5 of the main disk.
Software Upgrade
The main components of Pigsty are upgraded to the following versions (as of the release time):
- PostgreSQL 16.4, 15.8, 14.13, 13.16, 12.20
- pg_exporter : 0.7.0
- Patroni: 3.3.2
- pgBouncer: 1.23.1
- pgBackRest: 2.53.1
- duckdb : 1.0.0
- etcd : 3.5.15
- pg_timetable: 5.9.0
- ferretdb: 1.23.1
- vip-manager: 2.6.0
- minio: 20240817012454
- mcli: 20240817113350
- grafana : 11.1.4
- loki : 3.1.1
- promtail : 3.0.0
- prometheus : 2.54.0
- pushgateway : 1.9.0
- alertmanager : 0.27.0
- blackbox_exporter : 0.25.0
- nginx_exporter : 1.3.0
- node_exporter : 1.8.2
- keepalived_exporter : 0.7.0
- pgbackrest_exporter 0.18.0
- mysqld_exporter : 0.15.1
- redis_exporter : v1.62.0
- kafka_exporter : 1.8.0
- mongodb_exporter : 0.40.0
- VictoriaMetrics : 1.102.1
- VictoriaLogs : v0.28.0
- sealos: 5.0.0
- vector : 0.40.0
The complete list of 336 available PostgreSQL extensions can be found here.
Docker Application
Pigsty now offers out-of-the-box Dify and Odoo Docker Compose templates:
- Dify: Open Source AI Agent Workflow & LLMOps, with PostgreSQL & PGVector
- Odoo: Open Source ERP System
There two new beta modules available in Pigsty Pro version:
KAFKA
: Deploy a high-availability Kafka cluster supported by the Kraft protocol.KUBE
: Deploy a Kubernetes cluster managed by Pigsty using cri-dockerd or containerd.
Bug Fix
- Fix Debian/Ubuntu postgres service been overwritten by postgresql-common after server reboot.
- Fix CVE-2024-6387 by automatically repairing during the Pigsty installation process using the default value
[openssh-server]
innode_packages
. - Fix memory consumption issues caused by Loki parsing Nginx log tag cardinality being too large.
- Fix bootstrap failure caused by upstream Ansible dependency changes in EL8 systems (python3.11-jmespath upgraded to python3.12-jmespath).
v2.7.0
Highlight
Extension Overwhelming, adding numerous new extensions written in rust
& pgrx
:
- pg_search v0.7.0 : Full text search over SQL tables using the BM25 algorithm
- pg_lakehouse v0.7.0 : Query engine over object stores like S3 and table formats like Delta Lake
- pg_analytics v0.6.1 : Accelerates analytical query processing inside Postgres
- pg_graphql v1.5.4 : GraphQL support to your PostgreSQL database.
- pg_jsonschema v0.3.1 : PostgreSQL extension providing JSON Schema validation
- wrappers v0.3.1 : Postgres Foreign Data Wrappers Collections by Supabase
- pgmq v1.5.2 : A lightweight message queue. Like AWS SQS and RSMQ but on Postgres.
- pg_tier v0.0.3 : Postgres Extension written in Rust, to enable data tiering to AWS S3
- pg_vectorize v0.15.0 : The simplest way to orchestrate vector search on Postgres
- pg_later v0.1.0 : Execute SQL now and get the results later.
- pg_idkit v0.2.3 : Generating many popular types of identifiers
- plprql v0.1.0 : Use PRQL in PostgreSQL
- pgsmcrypto v0.1.0 : PostgreSQL SM Algorithm Extension
- pg_tiktoken v0.0.1 : OpenAI tiktoken tokenizer for postgres
- pgdd v0.5.2 : Access Data Dictionary metadata with pure SQL
And some new extensions in plain C & C++
- parquet_s3_fdw 1.1.0 : ParquetS3 Foreign Data Wrapper for PostgresSQL
- plv8 3.2.2 : V8 Engine Javascript Procedural Language add-on for PostgreSQL
- md5hash 1.0.1 : Custom data type for storing MD5 hashes rather than text
- pg_tde 1.0 alpha: Experimental encrypted access method for PostgreSQL
- pg_dirtyread 2.6 : Read dead but unvacuumed tuples from a PostgreSQL relation
- New deb PGDG extensions:
pg_roaringbitmap
,pgfaceting
,mobilitydb
,pgsql-http
,pg_hint_plan
,pg_statviz
,pg_rrule
- New rpm PGDG extensions:
pg_profile
,pg_show_plans
, use PGDG’spgsql_http
,pgsql_gzip
,pg_net
,pg_bigm
instead of Pigsty RPM.
New Features
- running on certain
docker
containers. - prepare arm64 packages for infra & pgsql packages for el & deb distros.
- new installation script to download from cloudflare, and more hint.
- new monitoring dashboard for PGSQL PITR to assist the PITR procedure.
- make preparation for running pigsty inside docker VM containers
- add a fool-proof design for running pgsql.yml on node that is not managed by pigsty
- add config template for each major version: el7, el8, el9, debian11, debian12, ubuntu20, ubuntu22
Software Upgrade
- PostgreSQL 16.3
- Patroni 3.3.0
- pgBackRest 2.51
- vip-manager v2.5.0
- Haproxy 2.9.7
- Grafana 10.4.2
- Prometheus 2.51
- Loki & Promtail: 3.0.0 (breaking changes!)
- Alertmanager 0.27.0
- BlackBox Exporter 0.25.0
- Node Exporter 1.8.0
- pgBackrest Exporter 0.17.0
- duckdb 0.10.2
- etcd 3.5.13
- minio-20240510014138 / mcli-20240509170424
- pev2 v1.8.0 -> v1.11.0
- pgvector 0.6.1 -> 0.7.0
- pg_tle: v1.3.4 -> v1.4.0
- hydra: v1.1.1 -> v1.1.2
- duckdb_fdw: v1.1.0 recompile with libduckdb 0.10.2
- pg_bm25 0.5.6 -> pg_search 0.7.0
- pg_analytics: 0.5.6 -> 0.6.1
- pg_graphql: 1.5.0 -> 1.5.4
- pg_net 0.8.0 -> 0.9.1
- pg_sparse (deprecated)
Docker Application
- Odoo: launch open source ERP and plugins
- Jupyter: run jupyter notebook container
- PolarDB: run the demo PG RAC playground.
- supabase: bump to the latest GA version.
- bytebase: use the
latest
tag instead of ad hoc version. - pg_exporter: update docker image example
Bug Fix
- Fix role pg_exporters white space in variable templates
- Fix
minio_cluster
not commented in global variables - Fix the non-exist
postgis34
in el7 config template - Fix EL8
python3.11-cryptography
deps topython3-cryptography
according to upstream - Fix
/pg/bin/pg-role
can not get OS user name from environ in non-interact mode - Fix
/pg/bin/pg-pitr
can not hint -X -P flag properly
API Change
- New parameter
node_write_etc_hosts
to control whether to write/etc/hosts
file on target nodes. - Relocatable prometheus target directory with new parameter
prometheus_sd_dir
. - Add
-x|--proxy
flag to enable and use value of global proxy env by @waitingsong in https://github.com/Vonng/pigsty/pull/405 - No longer parse infra nginx log details since it brings too much labels to the log.
- Use alertmanager API Version v2 instead of v1 in prometheus config.
- Use
/pg/cert/ca.crt
instead of/etc/pki/ca.crt
in pgsql roles.
New Contributors
- @NeroSong made their first contribution in https://github.com/Vonng/pigsty/pull/373
- @waitingsong made their first contribution in https://github.com/Vonng/pigsty/pull/405
Package Checksums
ec271a1d34b2b1360f78bfa635986c3a pigsty-pkg-v2.7.0.el8.x86_64.tgz
f3304bfd896b7e3234d81d8ff4b83577 pigsty-pkg-v2.7.0.debian12.x86_64.tgz
5b071c2a651e8d1e68fc02e7e922f2b3 pigsty-pkg-v2.7.0.ubuntu22.x86_64.tgz
v2.6.0
Highlight
- Use PostgreSQL 16 as the default major version (16.2)
- Introduce ParadeDB extensions:
pg_analytics
,pg_bm25
, andpg_sparse
- Introduce DuckDB and corresponding foreign data wrapper:
duckdb_fdw
- Cloudflare CDN https://repo.pigsty.io and QCloud CDN https://repo.pigsty.cc
Configuration
- Disable Grafana Unified Alert to work around the “Database Locked” error。
- add
node_repo_modules
to add upstream repos (including local one) to node - remove
node_local_repo_urls
, replaced bynode_repo_modules
&repo_upstream
. - remove
node_repo_method
, replaced bynode_repo_modules
. - add the new
local
repo intorepo_upstream
instead ofnode_local_repo_urls
- add
chrony
intonode_default_packages
- remove redis,minio,postgresql client from infra packages
- replace
repo_upstream.baseurl
$releasever for pgdg el8/el9 withmajor.minor
instead ofmajor
version
Software Upgrade
- Grafana 10.3.3
- Prometheus 2.47
- node_exporter 1.7.0
- HAProxy 2.9.5
- Loki / Promtail 2.9.4
- minio-20240216110548 / mcli-20240217011557
- etcd 3.5.11
- Redis 7.2.4
- Bytebase 2.13.2
- HAProxy 2.9.5
- DuckDB 0.10.0
- FerretDB 1.19
- Metabase: new docker compose app template added
PostgreSQL x Pigsty Extensions
- PostgreSQL Minor Version Upgrade 16.2, 15.6, 14.11, 13.14, 12.18
- PostgreSQL 16 is now used as the default major version
- pg_exporter 0.6.1, security fix
- Patroni 3.2.2
- pgBadger 12.4
- pgBouncer 1.22
- pgBackRest 2.50
- vip-manager 2.3.0
- PostGIS 3.4.1
- PGVector 0.6.0
- TimescaleDB 2.14.1
- New Extension duckdb_fdw v1.1
- New Extension pgsql-gzip v1.0.0
- New Extension pg_sparse from ParadeDB: v0.5.6
- New Extension pg_bm25 from ParadeDB: v0.5.6
- New Extension pg_analytics from ParadeDB: v0.5.6
- Bump AI/ML Extension pgml to v2.8.1 with pg16 support
- Bump Columnar Extension hydra to v1.1.1 with pg16 support
- Bump Graph Extension age to v1.5.0 with pg16 support
- Bump Packaging Extension pg_tle to v1.3.4 with pg16 support
- Bump GraphQL Extension pg_graphql to v1.5.0 to support supabase
330e9bc16a2f65d57264965bf98174ff pigsty-v2.6.0.tgz
81abcd0ced798e1198740ab13317c29a pigsty-pkg-v2.6.0.debian11.x86_64.tgz
7304f4458c9abd3a14245eaf72f4eeb4 pigsty-pkg-v2.6.0.debian12.x86_64.tgz
f914fbb12f90dffc4e29f183753736bb pigsty-pkg-v2.6.0.el7.x86_64.tgz
fc23d122d0743d1c1cb871ca686449c0 pigsty-pkg-v2.6.0.el8.x86_64.tgz
9d258dbcecefd232f3a18bcce512b75e pigsty-pkg-v2.6.0.el9.x86_64.tgz
901ee668621682f99799de8932fb716c pigsty-pkg-v2.6.0.ubuntu20.x86_64.tgz
39872cf774c1fe22697c428be2fc2c22 pigsty-pkg-v2.6.0.ubuntu22.x86_64.tgz
v2.5.1
Routine update with v16.1, v15.5, 14.10, 13.13, 12.17, 11.22
Now PostgreSQL 16 has all the core extensions available (pg_repack
& timescaledb
added)
- Software Version Upgrade:
- PostgreSQL to v16.1, v15.5, 14.10, 13.13, 12.17, 11.22
- Patroni v3.2.0
- PgBackrest v2.49
- Citus 12.1
- TimescaleDB 2.13.0 (with PG 16 support)
- Grafana v10.2.2
- FerretDB 1.15
- SealOS 4.3.7
- Bytebase 2.11.1
- Remove
monitor
schema prefix from PGCAT dashboard queries - New template
wool.yml
for Aliyun free ECS singleton - Add
python3-jmespath
in addition topython3.11-jmespath
for el9
31ee48df1007151009c060e0edbd74de pigsty-pkg-v2.5.1.el7.x86_64.tgz
a40f1b864ae8a19d9431bcd8e74fa116 pigsty-pkg-v2.5.1.el8.x86_64.tgz
c976cd4431fc70367124fda4e2eac0a7 pigsty-pkg-v2.5.1.el9.x86_64.tgz
7fc1b5bdd3afa267a5fc1d7cb1f3c9a7 pigsty-pkg-v2.5.1.debian11.x86_64.tgz
add0731dc7ed37f134d3cb5b6646624e pigsty-pkg-v2.5.1.debian12.x86_64.tgz
99048d09fa75ccb8db8e22e2a3b41f28 pigsty-pkg-v2.5.1.ubuntu20.x86_64.tgz
431668425f8ce19388d38e5bfa3a948c pigsty-pkg-v2.5.1.ubuntu22.x86_64.tgz
v2.5.0
curl -L https://get.pigsty.cc/latest | bash
Highlights
-
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, usepgvector
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 removecitus
from default installed extension list. - Recompile
pg_filedump
as PG major version independent package.
-
Software Version Upgrade:
- Grafana to v10.1.5
- Prometheus to v2.47
- Promtail/Loki to v2.9.1
- Node Exporter to v1.6.1
- Bytebase to v2.10.0
- patroni to v3.1.2
- pgbouncer to v1.21.0
- pg_exporter to v0.6.0
- pgbackrest to v2.48.0
- pgbadger to v12.2
- pg_graphql to v1.4.0
- pg_net to v0.7.3
- ferretdb to v0.12.1
- sealos to 4.3.5
- Supabase support to
20231013070755
Ubuntu Support
Pigsty has two ubuntu LTS support: 22.04 (jammy) and 20.04 (focal), and ship corresponding offline packages for them.
Some parameters need to be specified explicitly when deploying on Ubuntu, please refer to ubuntu.yml
repo_upstream
: Adjust according to ubuntu / debian repo.repo_packages
: Adjust according to ubuntu / debian naming conventionnode_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 replacepostgresql16
withpostgresql-client-16
pg_packages
: replace all_
with-
in names,patroni-etcd
not needed on ubuntupg_extensions
: different naming convention, nopasswordcheck_cracklib
on ubuntu.pg_dbsu_uid
: You have to manually specifypg_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 unusedkarma,mtail,dellhw_exporter
and pg 14 extra extensions, adding pg 16 extra extensions. -
node_default_packages
now addpython3-pip
as default packages. -
pg_libs
:timescaledb
is remove from shared_preload_libraries by default. -
pg_extensions
: citus is nolonger installed by default, andpasswordcheck_cracklib
is installed by default- pg_repack_${pg_version}* wal2json_${pg_version}* passwordcheck_cracklib_${pg_version}* - postgis34_${pg_version}* timescaledb-2-postgresql-${pg_version}* pgvector_${pg_version}*
87e0be2edc35b18709d7722976e305b0 pigsty-pkg-v2.5.0.el7.x86_64.tgz
e71304d6f53ea6c0f8e2231f238e8204 pigsty-pkg-v2.5.0.el8.x86_64.tgz
39728496c134e4352436d69b02226ee8 pigsty-pkg-v2.5.0.el9.x86_64.tgz
e3f548a6c7961af6107ffeee3eabc9a7 pigsty-pkg-v2.5.0.debian11.x86_64.tgz
1e469cc86a19702e48d7c1a37e2f14f9 pigsty-pkg-v2.5.0.debian12.x86_64.tgz
cc3af3b7c12f98969d3c6962f7c4bd8f pigsty-pkg-v2.5.0.ubuntu20.x86_64.tgz
c5b2b1a4867eee624e57aed58ac65a80 pigsty-pkg-v2.5.0.ubuntu22.x86_64.tgz
v2.4.1
Highlights
- Supabase support: run open-source Firebase alternative with external postgres managed by Pigsty: example config
- PostgresML support: Run LLMs, vector operations, classical Machine Learning in Postgres.
- GraphQL support:
pg_graphql
reflects a GraphQL schema from the existing SQL schema. - Async HTTP Client support
pg_net
enables asynchronous (non-blocking) HTTP/HTTPS requests with SQL - JWT support:
pgjwt
is the PostgreSQL implementation of JWT (JSON Web Tokens) - Vault support:
vault
can store encrypted secrets in the Vault - New component
pg_filedump
for pg 14 & 15, low-level data recovery tool for PostgreSQL - New extension
hydra
the columnar available for PG 13 - 15. - Reduce offline packages size for el9 400MB by removing
proj-data*
- Bump FerretDB version to v1.10
efabe7632d8994f3fb58f9838b8f9d7d pigsty-pkg-v2.4.1.el7.x86_64.tgz # 1.1G
ea78957e8c8434b120d1c8c43d769b56 pigsty-pkg-v2.4.1.el8.x86_64.tgz # 1.4G
4ef280a7d28872814e34521978b851bb pigsty-pkg-v2.4.1.el9.x86_64.tgz # 1.3G
v2.4.0
Get started with bash -c "$(curl -fsSL https://get.pigsty.cc/latest)"
.
Highlights
- PostgreSQL 16 support
- The first LTS version with business support and consulting service
- Monitoring existing PostgreSQL, RDS for PostgreSQL / PolarDB with PGRDS Dashboards
- New extension: Apache AGE, openCypher graph query engine on PostgreSQL
- New extension: zhparser, full text search for Chinese language
- New extension: pg_roaringbitmap, roaring bitmap for PostgreSQL
- New extension: pg_embedding, hnsw alternative to pgvector
- New extension: pg_tle, admin / manage stored procedure extensions
- New extension: pgsql-http, issue http request with SQL interface
- Add extensions: pg_auth_mon pg_checksums pg_failover_slots pg_readonly postgresql-unit pg_store_plans pg_uuidv7 set_user
- Redis enhancement: add monitoring panels for redis sentinel, and auto HA configuration for redis ms cluster.
API Change
- New Parameter:
REDIS
.redis_sentinel_monitor
: specify masters monitor by redis sentinel cluster
Bug Fix
- Fix Grafana 10.1 registered datasource will use random uid rather than
ins.datname
MD5 (pigsty-pkg-v2.4.0.el7.x86_64.tgz) = 257443e3c171439914cbfad8e9f72b17
MD5 (pigsty-pkg-v2.4.0.el8.x86_64.tgz) = 41ad8007ffbfe7d5e8ba5c4b51ff2adc
MD5 (pigsty-pkg-v2.4.0.el9.x86_64.tgz) = 9a950aed77a6df90b0265a6fa6029250
v2.3.1
Get started with bash -c "$(curl -fsSL https://get.pigsty.cc/latest)"
.
Highlights
- PGVector 0.5 with HNSW index support
- PostgreSQL 16 RC1 for el8/el9 Adding SealOS for kubernetes support
Bug Fix
- Fix
infra
.repo
.repo_pkg
task when downloading rpm with*
in their names inrepo_packages
.- if
/www/pigsty
already have package name match that pattern, some rpm will be skipped.
- if
- Change default value of
vip_dns_suffix
to''
empty string rather than.vip
- Grant sudo privilege for postgres dbsu when
pg_dbsu_sudo
=limit
andpatroni_watchdog_mode
=required
/usr/bin/sudo /sbin/modprobe softdog
: enable watchdog module before launching patroni/usr/bin/sudo /bin/chown {{ pg_dbsu }} /dev/watchdog
: chown watchdog before launching patroni
Documentation Update
- Add details to English documentation
- Add Chinese/zh-cn documentation
Software Upgrade
- PostgreSQL 16 RC1 on el8/el9
- PGVector 0.5.0 with hnsw index
- TimescaleDB 2.11.2
- grafana 10.1.0
- loki & promtail 2.8.4
- mcli-20230829225506 / minio-20230829230735
- ferretdb 1.9
- sealos 4.3.3
- pgbadger 1.12.2
ce69791eb622fa87c543096cdf11f970 pigsty-pkg-v2.3.1.el7.x86_64.tgz
495aba9d6d18ce1ebed6271e6c96b63a pigsty-pkg-v2.3.1.el8.x86_64.tgz
38b45582cbc337ff363144980d0d7b64 pigsty-pkg-v2.3.1.el9.x86_64.tgz
v2.3.0
Get started with bash -c "$(curl -fsSL https://get.pigsty.cc/latest)"
Highlight
- INFRA: NODE/PGSQL VIP monitoring support
- NODE: Allow bind
node_vip
to node cluster withkeepalived
- REPO: Dedicate yum repo, enable https for
get.pigsty.cc
anddemo.pigsty.cc
- PGSQL: Fix CVE-2023-39417 with PostgreSQL 15.4, 14.9, 13.12, 12.16, bump patroni version to v3.1.0
- APP: Bump
app/bytebase
to v2.6.0,app/ferretdb
version to v1.8, new application nocodb - REDIS: bump to v7.2 and rework on dashboards
- MONGO: basic deploy & monitor support with FerretDB 1.8
- MYSQL: add prometheus/grafana/ca stub for future implementation.
API Change
Add 1 new section NODE
.NODE_VIP
with 8 new parameter
NODE
.VIP
.vip_enabled
: enable vip on this node cluster?NODE
.VIP
.vip_address
: node vip address in ipv4 format, required if vip is enabledNODE
.VIP
.vip_vrid
: required, integer, 1-255 should be unique among same VLANNODE
.VIP
.vip_role
:master/backup
, backup by default, use as init roleNODE
.VIP
.vip_preempt
: optional,true/false
, false by default, enable vip preemptionNODE
.VIP
.vip_interface
: node vip network interface to listen,eth0
by defaultNODE
.VIP
.vip_dns_suffix
: node vip dns name suffix,.vip
by defaultNODE
.VIP
.vip_exporter_port
: keepalived exporter listen port, 9650 by default
MD5 (pigsty-pkg-v2.3.0.el7.x86_64.tgz) = 81db95f1c591008725175d280ad23615
MD5 (pigsty-pkg-v2.3.0.el8.x86_64.tgz) = 6f4d169b36f6ec4aa33bfd5901c9abbe
MD5 (pigsty-pkg-v2.3.0.el9.x86_64.tgz) = 4bc9ae920e7de6dd8988ca7ee681459d
v2.2.0
Get started with bash -c "$(curl -fsSL http://get.pigsty.cc/latest)"
Release Note: https://doc.pigsty.cc/#/RELEASENOTE
Highlight
- Monitoring Dashboards Overhaul: https://demo.pigsty.cc
- Vagrant Sandbox Overhaul: libvirt support and new templates
- Pigsty EL Yum Repo: Building simplified
- OS Compatibility: UOS-v20-1050e support
- New config template: prod simulation with 42 nodes
- Use official pgdg citus distribution for el7
Software Upgrade
- PostgreSQL 16 beta2
- Citus 12 / PostGIS 3.3.3 / TimescaleDB 2.11.1 / PGVector 0.44
- patroni 3.0.4 / pgbackrest 2.47 / pgbouncer 1.20
- grafana 10.0.3 / loki/promtail/logcli 2.8.3
- etcd 3.5.9 / haproxy v2.8.1 / redis v7.0.12
- minio 20230711212934 / mcli 20230711233044
Bug Fix
- Fix docker group ownership issue 29434bd
- Append infra os group rather than set it as primary group
- Fix redis sentinel systemd enable status 5c96feb
- Loose
bootstrap
&configure
if/etc/redhat-release
not exists - Fix grafana 9.x CVE-2023-1410 with 10.0.2
- Add PG 14 - 16 new command tags and error codes for
pglog
schema
API Change
Add 1 new parameter
INFRA
.NGINX
.nginx_exporter_enabled
: now you can disable nginx_exporter with this parameter
Default value changes:
repo_modules
:node,pgsql,infra
: redis is removed from itrepo_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 inpigsty-misc
- remove
grafana
in build config for acceleration
- add
repo_packages
:- ansible python3 python3-pip python3-requests python3.11-jmespath dnf-utils modulemd-tools # el7: python36-requests python36-idna yum-utils
- grafana loki logcli promtail prometheus2 alertmanager karma pushgateway node_exporter blackbox_exporter nginx_exporter redis_exporter
- redis etcd minio mcli haproxy vip-manager pg_exporter nginx createrepo_c sshpass chrony dnsmasq docker-ce docker-compose-plugin flamegraph
- lz4 unzip bzip2 zlib yum pv jq git ncdu make patch bash lsof wget uuid tuned perf nvme-cli numactl grubby sysstat iotop htop rsync tcpdump
- netcat socat ftp lrzsz net-tools ipvsadm bind-utils telnet audit ca-certificates openssl openssh-clients readline vim-minimal
- postgresql13* wal2json_13* pg_repack_13* passwordcheck_cracklib_13* postgresql12* wal2json_12* pg_repack_12* passwordcheck_cracklib_12* timescaledb-tools
- postgresql15 postgresql15* citus_15* pglogical_15* wal2json_15* pg_repack_15* pgvector_15* timescaledb-2-postgresql-15* postgis33_15* passwordcheck_cracklib_15* pg_cron_15*
- postgresql14 postgresql14* citus_14* pglogical_14* wal2json_14* pg_repack_14* pgvector_14* timescaledb-2-postgresql-14* postgis33_14* passwordcheck_cracklib_14* pg_cron_14*
- postgresql16* wal2json_16* pgvector_16* pg_squeeze_16* postgis34_16* passwordcheck_cracklib_16* pg_cron_16*
- patroni patroni-etcd pgbouncer pgbadger pgbackrest pgloader pg_activity pg_partman_15 pg_permissions_15 pgaudit17_15 pgexportdoc_15 pgimportdoc_15 pg_statement_rollback_15*
- orafce_15* mysqlcompat_15 mongo_fdw_15* tds_fdw_15* mysql_fdw_15 hdfs_fdw_15 sqlite_fdw_15 pgbouncer_fdw_15 multicorn2_15* powa_15* pg_stat_kcache_15* pg_stat_monitor_15* pg_qualstats_15 pg_track_settings_15 pg_wait_sampling_15 system_stats_15
- plprofiler_15* plproxy_15 plsh_15* pldebugger_15 plpgsql_check_15* pgtt_15 pgq_15* pgsql_tweaks_15 count_distinct_15 hypopg_15 timestamp9_15* semver_15* prefix_15* rum_15 geoip_15 periods_15 ip4r_15 tdigest_15 hll_15 pgmp_15 extra_window_functions_15 topn_15
- pg_background_15 e-maj_15 pg_catcheck_15 pg_prioritize_15 pgcopydb_15 pg_filedump_15 pgcryptokey_15 logerrors_15 pg_top_15 pg_comparator_15 pg_ivm_15* pgsodium_15* pgfincore_15* ddlx_15 credcheck_15 safeupdate_15 pg_squeeze_15* pg_fkpart_15 pg_jobmon_15
repo_url_packages
:node_default_packages
:- lz4,unzip,bzip2,zlib,yum,pv,jq,git,ncdu,make,patch,bash,lsof,wget,uuid,tuned,nvme-cli,numactl,grubby,sysstat,iotop,htop,rsync,tcpdump
- netcat,socat,ftp,lrzsz,net-tools,ipvsadm,bind-utils,telnet,audit,ca-certificates,openssl,readline,vim-minimal,node_exporter,etcd,haproxy,python3,python3-pip
infra_packages
- grafana,loki,logcli,promtail,prometheus2,alertmanager,karma,pushgateway
- node_exporter,blackbox_exporter,nginx_exporter,redis_exporter,pg_exporter
- nginx,dnsmasq,ansible,postgresql15,redis,mcli,python3-requests
PGSERVICE
in.pigsty
is removed, replaced withPGDATABASE=postgres
.
FHS Changes:
bin/dns
andbin/ssh
now moved tovagrant/
MD5 (pigsty-pkg-v2.2.0.el7.x86_64.tgz) = 5fb6a449a234e36c0d895a35c76add3c
MD5 (pigsty-pkg-v2.2.0.el8.x86_64.tgz) = c7211730998d3b32671234e91f529fd0
MD5 (pigsty-pkg-v2.2.0.el9.x86_64.tgz) = 385432fe86ee0f8cbccbbc9454472fdd
v2.1.0
Highlight
- PostgreSQL 16 beta support, and 12 ~ 15 support.
- Add PGVector for AI Embedding for 12 - 15
- Add 6 extra panel & datasource plugins for grafana
- Add
bin/profile
to profile remote process and generate flamegraph - Add
bin/validate
to validate pigsty.yml configuration file - Add
bin/repo-add
to add upstream repo files to /etc/yum.repos.d - PostgreSQL 16 observability:
pg_stat_io
and corresponding dashboards
Software Upgrade
- PostgreSQL 15.3 , 14.8, 13.11, 12.15, 11.20, and 16 beta1
- pgBackRest 2.46
- pgbouncer 1.19
- Redis 7.0.11
- Grafana v9.5.3
- Loki / Promtail / Logcli 2.8.2
- Prometheus 2.44
- TimescaleDB 2.11.0
- minio-20230518000536 / mcli-20230518165900
- Bytebase v2.2.0
Enhancement
- Now use all
id*.pub
when installing local user’s public key
v2.0.2
Highlight
Store OpenAI embedding and search similar vectors with pgvector
- New extension
pgvector
- MinIO CVE-2023-28432 fix, and upgrade to 20230324 with new policy API:
Changes
- New extension
pgvector
for storing OpenAI embedding and searching similar vectors. - MinIO CVE-2023-28432 fix, and upgrade to 20230324 with new policy API.
- Add reload functionality to DNSMASQ systemd services
- Bump pev to v1.8
- Bump grafana to v9.4.7
- Bump MinIO and MCLI version to 20230324
- Bump bytebase version to v1.15.0
- Upgrade monitoring dashboards and fix dead links
- Upgrade aliyun terraform template image to rockylinux 9
- Adopt grafana provisioning API change since v9.4
- Add asciinema videos for various administration tasks
- Fix broken EL8 pgsql deps: remove anonymizer_15 faker_15 and pgloader
MD5 (pigsty-pkg-v2.0.2.el7.x86_64.tgz) = d46440a115d741386d29d6de646acfe2
MD5 (pigsty-pkg-v2.0.2.el8.x86_64.tgz) = 5fa268b5545ac96b40c444210157e1e1
MD5 (pigsty-pkg-v2.0.2.el9.x86_64.tgz) = c8b113d57c769ee86a22579fc98e8345
v2.0.1
Bug fix for v2.0.0 and security improvement.
Enhancement
- Replace the pig shape logo for compliance with the PostgreSQL trademark policy.
- Bump grafana version to v9.4 with better UI and bugfix.
- Bump patroni version to v3.0.1 with some bugfix.
- Change: rollback grafana systemd service file to rpm default.
- Use slow
copy
instead ofrsync
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
andlog_disconnections
incrit.yml
template. - Enable
$lib/passwordcheck
inpg_libs
incrit.yml
template. - Explicitly grant monitor view permission to
pg_monitor
role. - Remove default
dbrole_readonly
fromdbuser_monitor
to limit monitor user privilege - Now patroni listen on
{{ inventory_hostname }}
instead of0.0.0.0
- Now you can control postgres/pgbouncer listen to address with
pg_listen
- Now you can use placeholder
${ip}
,${lo}
,${vip}
inpg_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 withbin/pgsql-user
- Add
-a password
to redis-join task @redis.yml
- Fix missing default value in
infra-rm.yml
.remove infra data
- Fix prometheus targets file ownership to
prometheus
- Use admin user rather than root to delete metadata in DCS
- Fix Meta datasource missing database name due to grafana 9.4 bug.
Caveats
Official EL8 pgdg upstream is broken now, DO use it with caution!
Affected packages: postgis33_15, pgloader, postgresql_anonymizer_15*, postgresql_faker_15
How to Upgrade
cd ~/pigsty; tar -zcf /tmp/files.tgz files; rm -rf ~/pigsty # backup files dir and remove
cd ~; bash -c "$(curl -fsSL http://get.pigsty.cc/latest)" # get latest pigsty source
cd ~/pigsty; rm -rf files; tar -xf /tmp/files.tgz -C ~/pigsty # restore files dir
Checksums
MD5 (pigsty-pkg-v2.0.1.el7.x86_64.tgz) = 5cfbe98fd9706b9e0f15c1065971b3f6
MD5 (pigsty-pkg-v2.0.1.el8.x86_64.tgz) = c34aa460925ae7548866bf51b8b8759c
MD5 (pigsty-pkg-v2.0.1.el9.x86_64.tgz) = 055057cebd93c473a67fb63bcde22d33
Special thanks to @cocoonkid for his feedback.
v2.0.0
“PIGSTY” is now the abbr of “PostgreSQL in Great STYle”
or “PostgreSQL & Infrastructure & Governance System allTogether for You”.
Get pigsty v2.0.0 via the following command:
curl -fsSL http://get.pigsty.cc/latest | bash
Download directly from GitHub Release
# get from GitHub
bash -c "$(curl -fsSL https://raw.githubusercontent.com/Vonng/pigsty/master/bin/get)"
# or download tarball directly with curl
curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-v2.0.0.tgz -o ~/pigsty.tgz # SRC
curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-pkg-v2.0.0.el9.x86_64.tgz -o /tmp/pkg.tgz # EL9
curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-pkg-v2.0.0.el8.x86_64.tgz -o /tmp/pkg.tgz # EL8
curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-pkg-v2.0.0.el7.x86_64.tgz -o /tmp/pkg.tgz # EL7
Highlights
- PostgreSQL 15.2, PostGIS 3.3, Citus 11.2, TimescaleDB 2.10 now works together and unite as one.
- Now works on EL 7,8,9 for RHEL, CentOS, Rocky, AlmaLinux, and other EL compatible distributions
- Security enhancement with self-signed CA, full SSL support,
scram-sha-256
pwd encryption, and more. - Patroni 3.0 with native HA citus cluster support and dcs failsafe mode to prevent global DCS failures.
- Auto-Configured, Battery-Included PITR for PostgreSQL powered by
pgbackrest
, local or S3/minio. - Dedicate module
ETCD
which can be easily deployed and scaled in/out. Used as DCS instead of Consul. - Dedicate module
MINIO
, local S3 alternative for the optional central backup repo for PGSQL PITR. - Better config templates with adaptive tuning for Node & PG according to your hardware spec.
- Use AGPL v3.0 license instead of Apache 2.0 license due to Grafana & MinIO reference.
Compatibility
- Pigsty now works on EL7, EL8, EL9, and offers corresponding pre-packed offline packages.
- Pigsty now works on EL compatible distributions: RHEL, CentOS, Rocky, AlmaLinux, OracleLinux,…
- Pigsty now use RockyLinux 9 as default developing & testing environment instead of CentOS 7
- EL version, CPU arch, and pigsty version string are part of source & offline package names.
- PGSQL: PostgreSQL 15.2 / PostGIS 3.3 / TimescaleDB 2.10 / Citus 11.2 now works together.
- PGSQL: Patroni 3.0 is used as default HA solution for PGSQL, and etcd is used as default DCS.
- Patroni 3.0 with DCS failsafe mode to prevent global DCS failures (demoting all primary)
- Patroni 3.0 with native HA citus cluster support, with entirely open sourced v11 citus.
- vip-manager 2.x with ETCDv3 API, ETCDv2 API is deprecated, so does patroni.
- PGSQL: pgBackRest v2.44 is introduced to provide battery-include PITR for PGSQL.
- it will use local backup FS on primary by default for a two-day retention policy
- it will use S3/minio as an alternative central backup repo for a two-week retention policy
- ETCD is used as default DCS instead of Consul, And V3 API is used instead of V2 API.
- NODE module now consist of
node
itself,haproxy
,docker
,node_exporter
, andpromtail
chronyd
is used as default NTP client instead ofntpd
- HAPROXY now attach to
NODE
instead ofPGSQL
, 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
forpgbackrest
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 addressINFRA
.META
.region
: upstream mirror region: default|china|europeINFRA
.META
.os_version
: enterprise linux release version: 7,8,9INFRA
.CA
.ca_cn
: ca common name, pigsty-ca by defaultINFRA
.CA
.cert_validity
: cert validity, 20 years by defaultINFRA
.REPO
.repo_enabled
: build a local yum repo on infra node?INFRA
.REPO
.repo_upstream
: list of upstream yum repo definitionINFRA
.REPO
.repo_home
: home dir of local yum repo, usually same as nginx_home ‘/www’INFRA
.NGINX
.nginx_ssl_port
: https listen portINFRA
.NGINX
.nginx_ssl_enabled
: nginx https enabled?INFRA
.PROMTETHEUS
.alertmanager_endpoint
: altermanager endpoint in (ip|domain):port formatNODE
.NODE_TUNE
.node_hugepage_count
: number of 2MB hugepage, take precedence overnode_hugepage_ratio
NODE
.NODE_TUNE
.node_hugepage_ratio
: mem hugepage ratio, 0 disable it by defaultNODE
.NODE_TUNE
.node_overcommit_ratio
: node mem overcommit ratio, 0 disable it by defaultNODE
.HAPROXY
.haproxy_service
: list of haproxy service to be exposedPGSQL
.PG_ID
.pg_mode
: pgsql cluster mode: pgsql,citus,gpsqlPGSQL
.PG_BUSINESS
.pg_dbsu_password
: dbsu password, empty string means no dbsu password by defaultPGSQL
.PG_INSTALL
.pg_log_dir
: postgres log dir,/pg/data/log
by defaultPGSQL
.PG_BOOTSTRAP
.pg_storage_type
: SSD|HDD, SSD by defaultPGSQL
.PG_BOOTSTRAP
.patroni_log_dir
: patroni log dir,/pg/log
by defaultPGSQL
.PG_BOOTSTRAP
.patroni_ssl_enabled
: secure patroni RestAPI communications with SSL?PGSQL
.PG_BOOTSTRAP
.patroni_username
: patroni rest api usernamePGSQL
.PG_BOOTSTRAP
.patroni_password
: patroni rest api password (IMPORTANT: CHANGE THIS)PGSQL
.PG_BOOTSTRAP
.patroni_citus_db
: citus database managed by patroni, postgres by defaultPGSQL
.PG_BOOTSTRAP
.pg_max_conn
: postgres max connections,auto
will use recommended valuePGSQL
.PG_BOOTSTRAP
.pg_shared_buffer_ratio
: postgres shared buffer memory ratio, 0.25 by default, 0.1~0.4PGSQL
.PG_BOOTSTRAP
.pg_rto
: recovery time objective, ttl to failover, 30s by defaultPGSQL
.PG_BOOTSTRAP
.pg_rpo
: recovery point objective, 1MB data loss at most by defaultPGSQL
.PG_BOOTSTRAP
.pg_pwd_enc
: algorithm for encrypting passwords: md5|scram-sha-256PGSQL
.PG_BOOTSTRAP
.pgbouncer_log_dir
: pgbouncer log dir,/var/log/pgbouncer
by defaultPGSQL
.PG_BOOTSTRAP
.pgbouncer_auth_query
: if enabled, query pg_authid table to retrieve biz users instead of populating userlistPGSQL
.PG_BOOTSTRAP
.pgbouncer_sslmode
: SSL for pgbouncer client: disable|allow|prefer|require|verify-ca|verify-fullPGSQL
.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 defaultPGSQL
.PG_BACKUP
.pgbackrest_method
: pgbackrest backup repo method, local or minioPGSQL
.PG_BACKUP
.pgbackrest_repo
: pgbackrest backup repo configPGSQL
.PG_SERVICE
.pg_service_provider
: dedicate haproxy node group name, or empty string for local nodes by defaultPGSQL
.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 defaultPGSQL
.PG_SERVICE
.pg_vip_address
: vip address in<ipv4>/<mask>
format, require if vip is enabledPGSQL
.PG_SERVICE
.pg_vip_interface
: vip network interface to listen, eth0 by defaultPGSQL
.PG_SERVICE
.pg_dns_suffix
: pgsql cluster dns name suffix, ’’ by defaultPGSQL
.PG_SERVICE
.pg_dns_target
: auto, primary, vip, none, or ad hoc ipETCD
.etcd_seq
: etcd instance identifier, REQUIREDETCD
.etcd_cluster
: etcd cluster & group name, etcd by defaultETCD
.etcd_safeguard
: prevent purging running etcd instance?ETCD
.etcd_clean
: purging existing etcd during initialization?ETCD
.etcd_data
: etcd data directory, /data/etcd by defaultETCD
.etcd_port
: etcd client port, 2379 by defaultETCD
.etcd_peer_port
: etcd peer port, 2380 by defaultETCD
.etcd_init
: etcd initial cluster state, new or existingETCD
.etcd_election_timeout
: etcd election timeout, 1000ms by defaultETCD
.etcd_heartbeat_interval
: etcd heartbeat interval, 100ms by defaultMINIO
.minio_seq
: minio instance identifier, REQUIREDMINIO
.minio_cluster
: minio cluster name, minio by defaultMINIO
.minio_clean
: cleanup minio during init?, false by defaultMINIO
.minio_user
: minio os user,minio
by defaultMINIO
.minio_node
: minio node name patternMINIO
.minio_data
: minio data dir(s), use {x…y} to specify multi driversMINIO
.minio_domain
: minio external domain name,sss.pigsty
by defaultMINIO
.minio_port
: minio service port, 9000 by defaultMINIO
.minio_admin_port
: minio console port, 9001 by defaultMINIO
.minio_access_key
: root access key,minioadmin
by defaultMINIO
.minio_secret_key
: root secret key,minioadmin
by defaultMINIO
.minio_extra_vars
: extra environment variables for minio serverMINIO
.minio_alias
: alias name for local minio deploymentMINIO
.minio_buckets
: list of minio bucket to be createdMINIO
.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 asca.key
INFRA
.CA
.ca_key
: ca key filename, now fixed asca.key
INFRA
.REPO
.repo_upstreams
: replaced byrepo_upstream
PGSQL
.PG_INSTALL
.pgdg_repo
: now taken care by node playbooksPGSQL
.PG_INSTALL
.pg_add_repo
: now taken care by node playbooksPGSQL
.PG_IDENTITY
.pg_backup
: not used and conflict with section namePGSQL
.PG_IDENTITY
.pg_preflight_skip
: not used anymore, replace bypg_id
DCS
.dcs_name
: removed due to using etcdDCS
.dcs_servers
: replaced by using ad hoc groupetcd
DCS
.dcs_registry
: removed due to using etcdDCS
.dcs_safeguard
: replaced byetcd_safeguard
DCS
.dcs_clean
: replaced byetcd_clean
PGSQL
.PG_VIP
.vip_mode
: replaced bypg_vip_enabled
PGSQL
.PG_VIP
.vip_address
: replaced bypg_vip_address
PGSQL
.PG_VIP
.vip_interface
: replaced bypg_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
andgrafana_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
anddefault
supportpg_bgwriter
,pg_wal
,pg_query
,pg_db
,pgbouncer_stat
now use seconds instead of ms and µspg_table
counters now have default value 0 instead of NaNpg_class
is replaced bypg_table
andpg_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
andnode_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 appendnode_crontab
: node crontab to be appended or overwrittennameserver_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 leadergrafana_customize_logo
: customize grafana icon
Reforge
Now *_clean
are boolean flags to clean up existing instance during init.
And *_safeguard
are boolean flags to avoid purging running instance when executing any playbook.
pg_exists_action
->pg_clean
pg_disable_purge
->pg_safeguard
dcs_exists_action
->dcs_clean
dcs_disable_purge
->dcs_safeguard
Rename
node_ntp_config
->node_ntp_enabled
node_admin_setup
->node_admin_enabled
node_admin_pks
->node_admin_pk_list
node_dns_hosts
->node_etc_hosts_default
node_dns_hosts_extra
->node_etc_hosts
node_dns_server
->node_dns_method
node_local_repo_url
->node_repo_local_urls
node_packages
->node_packages_default
node_extra_packages
->node_packages
node_meta_pip_install
->node_packages_meta_pip
node_sysctl_params
->node_tune_params
app_list
->nginx_indexes
grafana_plugin
->grafana_plugin_method
grafana_cache
->grafana_plugin_cache
grafana_plugins
->grafana_plugin_list
grafana_git_plugin_git
->grafana_plugin_git
haproxy_admin_auth_enabled
->haproxy_auth_enabled
pg_shared_libraries
->pg_libs
dcs_type
->pg_dcs_type
v1.4.1
Routine bug fix / Docker Support / English Docs
Now docker is enabled on meta node by default. You can launch ton’s of SaaS with it
English document is available now.
- add docker to default packages
- add docker-compose to default pacakge list
- disable nameserver by default & enable docker role by default
Bug Fix
- fix promtail & loki config var issue
- Fix grafana legacy alerts.
- Disable nameserver by default
- Rename pg-alias.sh for patroni shortcuts
- disable exemplars queries for all dashboards
- fix loki data dir issue https://github.com/Vonng/pigsty/issues/100
- change autovacuum_freeze_max_age from 100000000 to 1000000000
v1.4.0
Architecture
- Decouple system into 4 major categories:
INFRA
,NODES
,PGSQL
,REDIS
, which makes pigsty far more clear and more extensible. - Single Node Deployment =
INFRA
+NODES
+PGSQL
- Deploy pgsql clusters =
NODES
+PGSQL
- Deploy redis clusters =
NODES
+REDIS
- Deploy other databases =
NODES
+ xxx (e.gMONGO
,KAFKA
, … TBD)
Accessibility
- CDN for mainland China.
- Get the latest source with
bash -c "$(curl -fsSL http://get.pigsty.cc/latest)"
- Download & Extract packages with new
download
script.
Monitor Enhancement
- Split monitoring system into 5 major categories:
INFRA
,NODES
,REDIS
,PGSQL
,APP
- Logging enabled by default
- now
loki
andpromtail
are enabled by default. with prebuilt loki-rpm
- now
- 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
- A hidden
- 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
andnodename
- Variable
pg_hostname
now means set hostname same as postgres instance name to keep backward-compatible - Variable
nodename_overwrite
control whether overwrite node’s hostname with nodename - Variable
nodename_exchange
will write nodename to each other’s/etc/hosts
- All nodes metrics reference are overhauled, join by
ip
- Nodes monitoring targets are managed alone under
/etc/prometheus/targets/nodes
- PGSQL Monitoring Enhancement
- Complete new PGSQL Cluster which simplify and focus on important stuff among cluster.
- New Dashboard PGSQL Databases which is cluster level object monitoring. Such as tables & queries among the entire cluster rather than single instance.
- PGSQL Alert dashboard now only focus on pgsql alerts.
- PGSQL Shard are added to PGSQL
- Redis Monitoring Enhancement
- Add nodes monitoring for all redis dashboards.
MatrixDB Support
- MatrixDB (Greenplum 7) can be deployed via
pigsty-matrix.yml
playbook - MatrixDB Monitor Dashboards : PGSQL MatrixDB
- Example configuration added:
pigsty-mxdb.yml
Provisioning Enhancement
Now pigsty work flow works as this:
infra.yml ---> install pigsty on single meta node
| then add more nodes under pigsty's management
|
nodes.yml ---> prepare nodes for pigsty (node setup, dcs, node_exporter, promtail)
| then choose one playbook to deploy database clusters on those nodes
|
^--> pgsql.yml install postgres on prepared nodes
^--> redis.yml install redis on prepared nodes
infra-demo.yml =
infra.yml -l meta +
nodes.yml -l pg-test +
pgsql.yml -l pg-test +
infra-loki.yml + infra-jupyter.yml + infra-pgweb.yml
nodes.yml
to setup & prepare nodes for pigsty- setup node, node_exporter, consul agent on nodes
node-remove.yml
are used for node de-register
pgsql.yml
now only works on prepared nodespgsql-remove
now only responsible for postgres itself. (dcs and node monitor are taken bynode.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.
- and
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 clusternodename_overwrite
: If set, nodename will be set to node’s hostnamenodename_exchange
: exchange node hostname (in/etc/hosts
) among play hostsnode_dns_hosts_extra
: extra static dns records which can be easily overwritten by single instance/clusterpatroni_enabled
: if disabled, postgres & patroni bootstrap will not be performed during rolepostgres
pgbouncer_enabled
: if disabled, pgbouncer will not be launched during rolepostgres
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 rolepostgres
(template, db,user)no_cmdb
: cli args forinfra.yml
andinfra-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 totimescaledb-tune
’s advise - Add
citus.node_conninfo: 'sslmode=prefer'
to conf templates in order to use citus without SSL - Add all extensions (except for pgrouting) in pgdg14 in package list
- Upgrade node_exporter to v1.3.1
- Add PostgREST v9.0.0 to package list. Generate API from postgres schema.
[BugFix]
- Grafana’s security breach (upgrade to v8.3.1 issue)
- fix
pg_instance
&pg_service
inregister
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
andpg_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 ifvagrant
is the default user, otherwisepigsty-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 withtimescale
version - [ENHANCEMENT] upgrade prometheus to 2.30
- [BUG FIX] now pg_exporter config dir’s owner are {{ pg_dbsu }} instead of prometheus
How to upgrade from v1.1.0
The major change in this release is timescaledb. Which replace old apache
license version with timescale
license version
stop/pause postgres instance with timescaledb
yum remove -y timescaledb_13
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/7/$basearch
repo_gpgcheck=0
gpgcheck=0
enabled=1
yum install timescaledb-2-postgresql13
v1.1.0
- [ENHANCEMENT] add
pg_dummy_filesize
to create fs space placeholder - [ENHANCEMENT] home page overhaul
- [ENHANCEMENT] add jupyter lab integration
- [ENHANCEMENT] add pgweb console integration
- [ENHANCEMENT] add pgbadger support
- [ENHANCEMENT] add pev2 support, explain visualizer
- [ENHANCEMENT] add pglog utils
- [ENHANCEMENT] update default pkg.tgz software version:
- upgrade postgres to v13.4 (with official pg14 support)
- upgrade pgbouncer to v1.16 (metrics definition updates)
- upgrade grafana to v8.1.4
- upgrade prometheus to v2.2.29
- upgrade node_exporter to v1.2.2
- upgrade haproxy to v2.1.1
- upgrade consul to v1.10.2
- upgrade vip-manager to v1.0.1
API Changes
-
nginx_upstream
now holds different structures. (incompatible) -
new config entries:
app_list
, render into home page’s nav entries -
new config entries:
docs_enabled
, setup local docs on default server. -
new config entries:
pev2_enabled
, setup local pev2 utils. -
new config entries:
pgbadger_enabled
, create log summary/report dir -
new config entries:
jupyter_enabled
, enable jupyter lab server on meta node -
new config entries:
jupyter_username
, specify which user to run jupyter lab -
new config entries:
jupyter_password
, specify jupyter lab default password -
new config entries:
pgweb_enabled
, enable pgweb server on meta node -
new config entries:
pgweb_username
, specify which user to run pgweb -
rename internal flag
repo_exist
intorepo_exists
-
now default value for
repo_address
ispigsty
instead ofyum.pigsty
-
now haproxy access point is
http://pigsty
instead ofhttp://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
- now
- 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
andpromtail
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 ofconsul
) - Add new role
remove
to gracefully remove cluster & instance - Upgrade prometheus & grafana provisioning logics.
- Upgrade to vip-manager 1.0 , node_exporter 1.2 , pg_exporter 0.4, grafana 8.0
- Now every database on every instance can be auto-registered as grafana datasource
- Move consul register tasks to role
register
, change consul service tags - Add cmdb.sql as pg-meta baseline definition (CMDB & PGLOG)
-
Application Framework
- Extensible framework for new functionalities
- core app: PostgreSQL Monitor System:
pgsql
- core app: PostgreSQL Catalog explorer:
pgcat
- core app: PostgreSQL Csvlog Analyzer:
pglog
- add example app
covid
for visualizing covid-19 data. - add example app
isd
for visualizing isd data.
-
Misc
- Add jupyterlab which brings entire python environment for data science
- Add
vonng-echarts-panel
to bring Echarts support back. - Add wrap script
createpg
,createdb
,createuser
- Add cmdb dynamic inventory scripts:
load_conf.py
,inventory_cmdb
,inventory_conf
- Remove obsolete playbooks:
pgsql-monitor
,pgsql-service
,node-remove
, etc….
API Change
- new var :
node_meta_pip_install
- rename var:
grafana_url
tografana_endpoint
- new var:
grafana_admin_username
- new var:
grafana_database
- new var:
grafana_pgurl
- new var:
pg_shared_libraries
- new var:
pg_exporter_auto_discovery
- new var:
pg_exporter_exclude_database
- new var:
pg_exporter_include_database
Bug Fix
- Fix default timezone Asia/Shanghai (CST) issue
- Fix nofile limit for pgbouncer & patroni
- Pgbouncer userlist & database list will be generated when executing tag
pgbouncer
v0.9.0
Pigsty GUI, CLI, Logging Intergration
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
fordbuser_monitor
to get ride of monitor logs.
- fix
- 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
tofalse
- Remove example enties of
node_sysctl_params
. grafana_plugin
defaultinstall
will now download from CDN if plugins not existsrepo_url_packages
now download rpm via pigsty CDN to accelerate.proxy_env.no_proxy
now add pigsty CDN tonoproxy
sites。grafana_customize
set tofalse
by default,enable it means install pigsty pro UI.node_admin_pk_current
add current user’s~/.ssh/id_rsa.pub
to admin pksloki_clean
whether to cleanup existing loki data during initloki_data_dir
set default data dir for loki logging servicepromtail_enabled
enabling promtail logging agent service?promtail_clean
remove existing promtail status during init?promtail_port
default port used by promtail, 9080 by defaultpromtail_status_file
location of promtail status filepromtail_send_url
endpoint of loki service which receives log data
v0.8.0
Service Provisioning support is added in this release
New Features
- Service provision.
- full locale support.
API Changes
Role vip
and haproxy
are merged into service
.
#------------------------------------------------------------------------------
# SERVICE PROVISION
#------------------------------------------------------------------------------
pg_weight: 100 # default load balance weight (instance level)
# - service - #
pg_services: # how to expose postgres service in cluster?
# primary service will route {ip|name}:5433 to primary pgbouncer (5433->6432 rw)
- name: primary # service name {{ pg_cluster }}_primary
src_ip: "*"
src_port: 5433
dst_port: pgbouncer # 5433 route to pgbouncer
check_url: /primary # primary health check, success when instance is primary
selector: "[]" # select all instance as primary service candidate
# replica service will route {ip|name}:5434 to replica pgbouncer (5434->6432 ro)
- name: replica # service name {{ pg_cluster }}_replica
src_ip: "*"
src_port: 5434
dst_port: pgbouncer
check_url: /read-only # read-only health check. (including primary)
selector: "[]" # select all instance as replica service candidate
selector_backup: "[? pg_role == `primary`]" # primary are used as backup server in replica service
# default service will route {ip|name}:5436 to primary postgres (5436->5432 primary)
- name: default # service's actual name is {{ pg_cluster }}-{{ service.name }}
src_ip: "*" # service bind ip address, * for all, vip for cluster virtual ip address
src_port: 5436 # bind port, mandatory
dst_port: postgres # target port: postgres|pgbouncer|port_number , pgbouncer(6432) by default
check_method: http # health check method: only http is available for now
check_port: patroni # health check port: patroni|pg_exporter|port_number , patroni by default
check_url: /primary # health check url path, / as default
check_code: 200 # health check http code, 200 as default
selector: "[]" # instance selector
haproxy: # haproxy specific fields
maxconn: 3000 # default front-end connection
balance: roundrobin # load balance algorithm (roundrobin by default)
default_server_options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'
# offline service will route {ip|name}:5438 to offline postgres (5438->5432 offline)
- name: offline # service name {{ pg_cluster }}_replica
src_ip: "*"
src_port: 5438
dst_port: postgres
check_url: /replica # offline MUST be a replica
selector: "[? pg_role == `offline` || pg_offline_query ]" # instances with pg_role == 'offline' or instance marked with 'pg_offline_query == true'
selector_backup: "[? pg_role == `replica` && !pg_offline_query]" # replica are used as backup server in offline service
pg_services_extra: [] # extra services to be added
# - haproxy - #
haproxy_enabled: true # enable haproxy among every cluster members
haproxy_reload: true # reload haproxy after config
haproxy_policy: roundrobin # roundrobin, leastconn
haproxy_admin_auth_enabled: false # enable authentication for haproxy admin?
haproxy_admin_username: admin # default haproxy admin username
haproxy_admin_password: admin # default haproxy admin password
haproxy_exporter_port: 9101 # default admin/exporter port
haproxy_client_timeout: 3h # client side connection timeout
haproxy_server_timeout: 3h # server side connection timeout
# - vip - #
vip_mode: none # none | l2 | l4
vip_reload: true # whether reload service after config
# vip_address: 127.0.0.1 # virtual ip address ip (l2 or l4)
# vip_cidrmask: 24 # virtual ip address cidr mask (l2 only)
# vip_interface: eth0 # virtual ip network interface (l2 only)
New Options
# - localization - #
pg_encoding: UTF8 # default to UTF8
pg_locale: C # default to C
pg_lc_collate: C # default to C
pg_lc_ctype: en_US.UTF8 # default to en_US.UTF8
pg_reload: true # reload postgres after hba changes
vip_mode: none # none | l2 | l4
vip_reload: true # whether reload service after config
Remove Options
haproxy_check_port # covered by service options
haproxy_primary_port
haproxy_replica_port
haproxy_backend_port
haproxy_weight
haproxy_weight_fallback
vip_enabled # replace by vip_mode
Service
pg_services
and pg_services_extra
Defines the services in cluster:
A service has some mandatory fields:
name
: service’s namesrc_port
: which port to listen and expose service?selector
: which instances belonging to this service?
# default service will route {ip|name}:5436 to primary postgres (5436->5432 primary)
- name: default # service's actual name is {{ pg_cluster }}-{{ service.name }}
src_ip: "*" # service bind ip address, * for all, vip for cluster virtual ip address
src_port: 5436 # bind port, mandatory
dst_port: postgres # target port: postgres|pgbouncer|port_number , pgbouncer(6432) by default
check_method: http # health check method: only http is available for now
check_port: patroni # health check port: patroni|pg_exporter|port_number , patroni by default
check_url: /primary # health check url path, / as default
check_code: 200 # health check http code, 200 as default
selector: "[]" # instance selector
haproxy: # haproxy specific fields
maxconn: 3000 # default front-end connection
balance: roundrobin # load balance algorithm (roundrobin by default)
default_server_options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'
Database
Add additional locale support: lc_ctype
and lc_collate
.
It’s mainly because of pg_trgm
’s weird behavior on i18n characters.
pg_databases:
- name: meta # name is the only required field for a database
# owner: postgres # optional, database owner
# template: template1 # optional, template1 by default
# encoding: UTF8 # optional, UTF8 by default , must same as template database, leave blank to set to db default
# locale: C # optional, C by default , must same as template database, leave blank to set to db default
# lc_collate: C # optional, C by default , must same as template database, leave blank to set to db default
# lc_ctype: C # optional, C by default , must same as template database, leave blank to set to db default
allowconn: true # optional, true by default, false disable connect at all
revokeconn: false # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
# tablespace: pg_default # optional, 'pg_default' is the default tablespace
connlimit: -1 # optional, connection limit, -1 or none disable limit (default)
extensions: # optional, extension name and where to create
- {name: postgis, schema: public}
parameters: # optional, extra parameters with ALTER DATABASE
enable_partitionwise_join: true
pgbouncer: true # optional, add this database to pgbouncer list? true by default
comment: pigsty meta database # optional, comment string for database
v0.7.0
Monitor only deployment support
Overview
-
Monitor Only Deployment
- Now you can monitoring existing postgres clusters without Pigsty provisioning solution.
- Intergration with other provisioning solution is available and under further test.
-
Database/User Management
- Update user/database definition schema to cover more usecases.
- Add
pgsql-createdb.yml
andpgsql-user.yml
to mange user/db on running clusters.
Features
- Monitor Only Deployment Support #25
- Split monolith static monitor target file into per-cluster conf #36
- Add create user playbook #29
- Add create database playbook #28
- Database provisioning interface enhancement #33
- User provisioning interface enhancement #34
Bug Fix
API Changes
New Options
prometheus_sd_target: batch # batch|single
exporter_install: none # none|yum|binary
exporter_repo_url: '' # add to yum repo if set
node_exporter_options: '--no-collector.softnet --collector.systemd --collector.ntp --collector.tcpstat --collector.processes' # default opts for node_exporter
pg_exporter_url: '' # optional, overwrite default pg_exporter target
pgbouncer_exporter_url: '' # optional, overwrite default pgbouncer_expoter target
Remove Options
exporter_binary_install: false # covered by exporter_install
Structure Changes
pg_default_roles # refer to pg_users
pg_users # refer to pg_users
pg_databases # refer to pg_databases
Rename Options
pg_default_privilegs -> pg_default_privileges # fix typo
Enhancement
Monitoring Provisioning Enhancement
- Decouple consul #13
- Binary install mode for node_exporter and pg_exporter #14
- Prometheus static targets mode support #11
Haproxy Enhancement
- Adjust relative traffic weight with configuration #10
- HAProxy admin page access via nginx #12
- Readonly traffic fallback on primary if all replicas down #8
Security Enhancement
Software Update
-
Prometheus 2.25 / Grafana 7.4 / Consul 1.9.3 / Node Exporter 1.1 / PG Exporter 0.3.2
API Change
New Config Entries
service_registry: consul # none | consul | etcd | both
prometheus_options: '--storage.tsdb.retention=30d' # prometheus cli opts
prometheus_sd_method: consul # Prometheus service discovery method:static|consul
prometheus_sd_interval: 2s # Prometheus service discovery refresh interval
pg_offline_query: false # set to true to allow offline queries on this instance
node_exporter_enabled: true # enabling Node Exporter
pg_exporter_enabled: true # enabling PG Exporter
pgbouncer_exporter_enabled: true # enabling Pgbouncer Exporter
export_binary_install: false # install Node/PG Exporter via copy binary
dcs_disable_purge: false # force dcs_exists_action = abort to avoid dcs purge
pg_disable_purge: false # force pg_exists_action = abort to avoid pg purge
haproxy_weight: 100 # relative lb weight for backend instance
haproxy_weight_fallback: 1 # primary server weight in replica service group
Obsolete Config Entries
prometheus_metrics_path # duplicate with exporter_metrics_path
prometheus_retention # covered by `prometheus_options`
Database Definition
Database provisioning interface enhancement #33
Old Schema
pg_databases: # create a business database 'meta'
- name: meta
schemas: [meta] # create extra schema named 'meta'
extensions: [{name: postgis}] # create extra extension postgis
parameters: # overwrite database meta's default search_path
search_path: public, monitor
New Schema
pg_databases:
- name: meta # name is the only required field for a database
owner: postgres # optional, database owner
template: template1 # optional, template1 by default
encoding: UTF8 # optional, UTF8 by default
locale: C # optional, C by default
allowconn: true # optional, true by default, false disable connect at all
revokeconn: false # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
tablespace: pg_default # optional, 'pg_default' is the default tablespace
connlimit: -1 # optional, connection limit, -1 or none disable limit (default)
extensions: # optional, extension name and where to create
- {name: postgis, schema: public}
parameters: # optional, extra parameters with ALTER DATABASE
enable_partitionwise_join: true
pgbouncer: true # optional, add this database to pgbouncer list? true by default
comment: pigsty meta database # optional, comment string for database
Changes
- Add new options:
template
,encoding
,locale
,allowconn
,tablespace
,connlimit
- Add new option
revokeconn
, which revoke connect privileges from public for this database - Add
comment
field for database
Apply Changes
You can create new database on running postgres clusters with pgsql-createdb.yml
playbook.
- Define your new database in config files
- Pass new database.name with option
pg_database
to playbook.
./pgsql-createdb.yml -e pg_database=<your_new_database_name>
User Definition
User provisioning interface enhancement #34
Old Schema
pg_users:
- username: test # example production user have read-write access
password: test # example user's password
options: LOGIN # extra options
groups: [ dbrole_readwrite ] # dborole_admin|dbrole_readwrite|dbrole_readonly
comment: default test user for production usage
pgbouncer: true # add to pgbouncer
New Schema
pg_users:
# complete example of user/role definition for production user
- name: dbuser_meta # example production user have read-write access
password: DBUser.Meta # example user's password, can be encrypted
login: true # can login, true by default (should be false for role)
superuser: false # is superuser? false by default
createdb: false # can create database? false by default
createrole: false # can create role? false by default
inherit: true # can this role use inherited privileges?
replication: false # can this role do replication? false by default
bypassrls: false # can this role bypass row level security? false by default
connlimit: -1 # connection limit, -1 disable limit
expire_at: '2030-12-31' # 'timestamp' when this role is expired
expire_in: 365 # now + n days when this role is expired (OVERWRITE expire_at)
roles: [dbrole_readwrite] # dborole_admin|dbrole_readwrite|dbrole_readonly
pgbouncer: true # add this user to pgbouncer? false by default (true for production user)
parameters: # user's default search path
search_path: public
comment: test user
Changes
username
field rename toname
groups
field rename toroles
options
now split into separated configration entries:login
,superuser
,createdb
,createrole
,inherit
,replication
,bypassrls
,connlimit
expire_at
andexpire_in
optionspgbouncer
option for user is nowfalse
by default
Apply Changes
You can create new users on running postgres clusters with pgsql-createuser.yml
playbook.
- Define your new users in config files (
pg_users
) - Pass new user.name with option
pg_user
to playbook.
./pgsql-createuser.yml -e pg_user=<your_new_user_name>
v0.6.0
Architecture Enhancement
Bug Fix
- pg_hba reset on patroni restart (patroni 2.0) #5
- Merge Fix name of dashboard #1, Fix PG Overview Dashboard typo
- Fix default primary instance to
pg-test-1
of clusterpg-test
in sandbox environment - Fix obsolete comments
Monitoring Provisioning Enhancement
- Decouple consul #13
- Binary install mode for node_exporter and pg_exporter #14
- Prometheus static targets mode support #11
Haproxy Enhancement
- Adjust relative traffic weight with configuration #10
- HAProxy admin page access via nginx #12
- Readonly traffic fallback on primary if all replicas down #8
Security Enhancement
Software Update
-
Prometheus 2.25 / Grafana 7.4 / Consul 1.9.3 / Node Exporter 1.1 / PG Exporter 0.3.2
API Change
New Config Entries
service_registry: consul # none | consul | etcd | both
prometheus_options: '--storage.tsdb.retention=30d' # prometheus cli opts
prometheus_sd_method: consul # Prometheus service discovery method:static|consul
prometheus_sd_interval: 2s # Prometheus service discovery refresh interval
pg_offline_query: false # set to true to allow offline queries on this instance
node_exporter_enabled: true # enabling Node Exporter
pg_exporter_enabled: true # enabling PG Exporter
pgbouncer_exporter_enabled: true # enabling Pgbouncer Exporter
export_binary_install: false # install Node/PG Exporter via copy binary
dcs_disable_purge: false # force dcs_exists_action = abort to avoid dcs purge
pg_disable_purge: false # force pg_exists_action = abort to avoid pg purge
haproxy_weight: 100 # relative lb weight for backend instance
haproxy_weight_fallback: 1 # primary server weight in replica service group
Obsolete Config Entries
prometheus_metrics_path # duplicate with exporter_metrics_path
prometheus_retention # covered by `prometheus_options`
v0.5.0
Pigsty now have an Official Site 🎉 !
New Features
- Add Database Provision Template
- Add Init Template
- Add Business Init Template
- Refactor HBA Rules variables
- Fix dashboards bugs.
- Move
pg-cluster-replication
to default dashboards - Use ZJU PostgreSQL mirror as default to accelerate repo build phase.
- Move documentation to official site: https://pigsty.cc
- Download newly created offline installation packages: pkg.tgz (v0.5)
Database Provision Template
Now you can customize your database content with pigsty !
pg_users:
- username: test
password: test
comment: default test user
groups: [ dbrole_readwrite ] # dborole_admin|dbrole_readwrite|dbrole_readonly
pg_databases: # create a business database 'test'
- name: test
extensions: [{name: postgis}] # create extra extension postgis
parameters: # overwrite database meta's default search_path
search_path: public,monitor
pg-init-template.sql wil be used as default template1 database init script pg-init-business.sql will be used as default business database init script
you can customize default role system, schemas, extensions, privileges with variables now:
Template Configuration
# - system roles - #
pg_replication_username: replicator # system replication user
pg_replication_password: DBUser.Replicator # system replication password
pg_monitor_username: dbuser_monitor # system monitor user
pg_monitor_password: DBUser.Monitor # system monitor password
pg_admin_username: dbuser_admin # system admin user
pg_admin_password: DBUser.Admin # system admin password
# - default roles - #
pg_default_roles:
- username: dbrole_readonly # sample user:
options: NOLOGIN # role can not login
comment: role for readonly access # comment string
- username: dbrole_readwrite # sample user: one object for each user
options: NOLOGIN
comment: role for read-write access
groups: [ dbrole_readonly ] # read-write includes read-only access
- username: dbrole_admin # sample user: one object for each user
options: NOLOGIN BYPASSRLS # admin can bypass row level security
comment: role for object creation
groups: [dbrole_readwrite,pg_monitor,pg_signal_backend]
# NOTE: replicator, monitor, admin password are overwritten by separated config entry
- username: postgres # reset dbsu password to NULL (if dbsu is not postgres)
options: SUPERUSER LOGIN
comment: system superuser
- username: replicator
options: REPLICATION LOGIN
groups: [pg_monitor, dbrole_readonly]
comment: system replicator
- username: dbuser_monitor
options: LOGIN CONNECTION LIMIT 10
comment: system monitor user
groups: [pg_monitor, dbrole_readonly]
- username: dbuser_admin
options: LOGIN BYPASSRLS
comment: system admin user
groups: [dbrole_admin]
- username: dbuser_stats
password: DBUser.Stats
options: LOGIN
comment: business read-only user for statistics
groups: [dbrole_readonly]
# object created by dbsu and admin will have their privileges properly set
pg_default_privilegs:
- GRANT USAGE ON SCHEMAS TO dbrole_readonly
- GRANT SELECT ON TABLES TO dbrole_readonly
- GRANT SELECT ON SEQUENCES TO dbrole_readonly
- GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly
- GRANT INSERT, UPDATE, DELETE ON TABLES TO dbrole_readwrite
- GRANT USAGE, UPDATE ON SEQUENCES TO dbrole_readwrite
- GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dbrole_admin
- GRANT CREATE ON SCHEMAS TO dbrole_admin
- GRANT USAGE ON TYPES TO dbrole_admin
# schemas
pg_default_schemas: [monitor]
# extension
pg_default_extensions:
- { name: 'pg_stat_statements', schema: 'monitor' }
- { name: 'pgstattuple', schema: 'monitor' }
- { name: 'pg_qualstats', schema: 'monitor' }
- { name: 'pg_buffercache', schema: 'monitor' }
- { name: 'pageinspect', schema: 'monitor' }
- { name: 'pg_prewarm', schema: 'monitor' }
- { name: 'pg_visibility', schema: 'monitor' }
- { name: 'pg_freespacemap', schema: 'monitor' }
- { name: 'pg_repack', schema: 'monitor' }
- name: postgres_fdw
- name: file_fdw
- name: btree_gist
- name: btree_gin
- name: pg_trgm
- name: intagg
- name: intarray
# postgres host-based authentication rules
pg_hba_rules:
- title: allow meta node password access
role: common
rules:
- host all all 10.10.10.10/32 md5
- title: allow intranet admin password access
role: common
rules:
- host all +dbrole_admin 10.0.0.0/8 md5
- host all +dbrole_admin 172.16.0.0/12 md5
- host all +dbrole_admin 192.168.0.0/16 md5
- title: allow intranet password access
role: common
rules:
- host all all 10.0.0.0/8 md5
- host all all 172.16.0.0/12 md5
- host all all 192.168.0.0/16 md5
- title: allow local read-write access (local production user via pgbouncer)
role: common
rules:
- local all +dbrole_readwrite md5
- host all +dbrole_readwrite 127.0.0.1/32 md5
- title: allow read-only user (stats, personal) password directly access
role: replica
rules:
- local all +dbrole_readonly md5
- host all +dbrole_readonly 127.0.0.1/32 md5
pg_hba_rules_extra: []
# pgbouncer host-based authentication rules
pgbouncer_hba_rules:
- title: local password access
role: common
rules:
- local all all md5
- host all all 127.0.0.1/32 md5
- title: intranet password access
role: common
rules:
- host all all 10.0.0.0/8 md5
- host all all 172.16.0.0/12 md5
- host all all 192.168.0.0/16 md5
pgbouncer_hba_rules_extra: []
v0.4.0
The second public beta (v0.4.0) of pigsty is available now ! 🎉
Monitoring System
Skim version of monitoring system consist of 10 essential dashboards:
- PG Overview
- PG Cluster
- PG Service
- PG Instance
- PG Database
- PG Query
- PG Table
- PG Table Catalog
- PG Table Detail
- Node
Software upgrade
- Upgrade to PostgreSQL 13.1, Patroni 2.0.1-4, add citus to repo.
- Upgrade to
pg_exporter 0.3.1
- Upgrade to Grafana 7.3, Ton’s of compatibility work
- Upgrade to prometheus 2.23, with new UI as default
- Upgrade to consul 1.9
Misc
- Update prometheus alert rules
- Fix alertmanager info links
- Fix bugs and typos.
- add a simple backup script
Offline Installation
- pkg.tgz is the latest offline install package (1GB rpm packages, made under CentOS 7.8)
v0.3.0
The first public beta (v0.3.0) of pigsty is available now ! 🎉
Monitoring System
Skim version of monitoring system consist of 8 essential dashboards:
- PG Overview
- PG Cluster
- PG Service
- PG Instance
- PG Database
- PG Table Overview
- PG Table Catalog
- Node
Database Cluster Provision
- All config files are merged into one file:
conf/all.yml
by default - Use
infra.yml
to provision meta node(s) and infrastructure - Use
initdb.yml
to provision database clusters - Use
ins-add.yml
to add new instance to database cluster - Use
ins-del.yml
to remove instance from database cluster
Offline Installation
- pkg.tgz is the latest offline install package (1GB rpm packages, made under CentOS 7.8)
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.