Release Notes

History of pigsty releases

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

1. MD5 (pigsty-pkg-v2.0.2.el7.x86_64.tgz) = d46440a115d741386d29d6de646acfe2
2. MD5 (pigsty-pkg-v2.0.2.el8.x86_64.tgz) = 5fa268b5545ac96b40c444210157e1e1
3. MD5 (pigsty-pkg-v2.0.2.el9.x86_64.tgz) = c8b113d57c769ee86a22579fc98e8345

v2.0.1

Bug fix for v2.0.0 and security improvement.

Enhancement

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

Bug Fixes

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

Caveats

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

Affected packages: postgis33_15, pgloader, postgresql_anonymizer_15*, postgresql_faker_15

How to Upgrade

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

Checksums

1. MD5 (pigsty-pkg-v2.0.1.el7.x86_64.tgz) = 5cfbe98fd9706b9e0f15c1065971b3f6
2. MD5 (pigsty-pkg-v2.0.1.el8.x86_64.tgz) = c34aa460925ae7548866bf51b8b8759c
3. 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:

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

Download directly from GitHub Release

1. bash -c "$(curl -fsSL https://raw.githubusercontent.com/Vonng/pigsty/master/bin/get)"

3. # or download tarball directly with curl (EL9)
4. curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-v2.0.0.tgz -o ~/pigsty.tgz
5. 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
6. # EL7: https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-pkg-v2.0.0.el7.x86_64.tgz
7. # EL8: https://github.com/Vonng/pigsty/releases/download/v2.0.0/pigsty-pkg-v2.0.0.el8.x86_64.tgz

Highlights

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

Compatibility

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

Security

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

Maintainability

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

API Changes

69 parameters added, 16 parameters removed, rename 14 parameters

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

Removed Parameters

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

Renamed Parameters

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

Checksums

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

Special thanks to @alemacci for his great contribution!

v1.5.1

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

Please upgrade postgres to 14.4 ASAP.

Software Upgrade

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

Bug fix

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

API Changes

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

New Apps

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

v1.5.0

Highlights

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

Monitoring

Dashboards

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

Architecture

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

Provisioning

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

Software Updates

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

New software / application based on docker:

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

Bug Fix

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

API Change

New Variable

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

Reforge

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

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

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

Rename

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

v1.4.1

Routine bug fix / Docker Support / English Docs

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

English document is available now.

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

Bug Fix

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

v1.4.0

Architecture

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

Accessibility

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

Monitor Enhancement

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

MatrixDB Support

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

Provisioning Enhancement

Now pigsty work flow works as this:

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

10. infra-demo.yml =
11. infra.yml -l meta     +
12. nodes.yml -l pg-test  +
13. pgsql.yml -l pg-test +
14. infra-loki.yml + infra-jupyter.yml + infra-pgweb.yml

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

Software Upgrade

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

Bug Fix

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

API Changes

New Variable

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

1. MD5 (app.tgz) = f887313767982b31a2b094e5589a75ea
2. MD5 (matrix.tgz) = 3d063437c482d94bd7e35df1a08bbc84
3. MD5 (pigsty.tgz) = e143b88ebea1474f9ebaffddc6072c49
4. MD5 (pkg.tgz) = 73e8f5ce995b1f1760cb63c1904fb91b

v1.3.1

[Monitor]

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

[Deploy]

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

[BugFix]

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

v1.3.0

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

• [ENHANCEMENT] Redis Monitor

• Redis Overview Dashboard

• Redis Cluster Dashboard
• Redis Instance Dashboard

• [ENHANCEMENT] monitor: PGCAT Overhaul

• New Dashboard: PGCAT Instance

• New Dashboard: PGCAT Database Dashboard
• Remake Dashboard: PGCAT Table

• [ENHANCEMENT] monitor: PGSQL Enhancement

• New Panels: PGSQL Cluster, add 10 key metrics panel (toggled by default)

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

• [ENHANCEMENT] monitor deploy

• Now grafana datasource is automatically registered during monly deployment

• [ENHANCEMENT] software upgrade

• add PostgreSQL 13 to default package list

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

v1.2.0

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

How to upgrade from v1.1.1

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

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

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

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

v1.1.1

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

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

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

4. [timescale_timescaledb]
5. name=timescale_timescaledb
6. baseurl=https://packagecloud.io/timescale/timescaledb/el/7/$basearch
7. repo_gpgcheck=0
8. gpgcheck=0
9. enabled=1

11. yum install timescaledb-2-postgresql13

v1.1.0

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

API Changes

• nginx_upstream now holds different structures. (incompatible)
• new config entries: app_list, render into home page’s nav entries
• new config entries: docs_enabled, setup local docs on default server.
• new config entries: pev2_enabled, setup local pev2 utils.
• new config entries: pgbadger_enabled, create log summary/report dir
• new config entries: jupyter_enabled, enable jupyter lab server on meta node
• new config entries: jupyter_username, specify which user to run jupyter lab
• new config entries: jupyter_password, specify jupyter lab default password
• new config entries: pgweb_enabled, enable pgweb server on meta node
• new config entries: pgweb_username, specify which user to run pgweb
• rename internal flag repo_exist into repo_exists
• now default value for repo_address is pigsty instead of yum.pigsty
• now haproxy access point is http://pigsty instead of http://h.pigsty

v1.0.1

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

v1.0.0

v1 GA, Monitoring System Overhaul

Highlights

• Monitoring System Overhaul

• New Dashboards on Grafana 8.0

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

• Architecture Changes

• Add citus & timescaledb as part of default installation

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

• Application Framework

• Extensible framework for new functionalities

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

• Misc

• Add jupyterlab which brings entire python environment for data science

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

API Change

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

Bug Fix

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

v0.9.0

Pigsty GUI, CLI, Logging Intergration

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 patro

（本文超出站点单文档体积上限，已截断）