27 Feb 05:00

Vonng

18fe3d5

v2.0.0 Release

"PIGSTY" is now the abbr of "PostgreSQL in Great STYle"

Get pigsty v2.0.0 via the following command:

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

Download directly from GitHub Release

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

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

Highlights

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

Compatibility

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

Added 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...

Contributors

Vonng and alemacci

Assets 7

0 Join discussion

18 Jun 07:05

Vonng

v1.5.1

87036e8

v1.5.1 Release (PG14.4 BUGFIX)

Highlights

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

MD5 (pigsty.tgz) = 5a7403a85b20b2b56f874fa5304979f6
MD5 (pkg.tgz) = efdf59af503d49430c7989383a6b4b32
MD5 (app.tgz) = 7f5712993d68e09798e1d40cea6c6d87
MD5 (docker.tgz) = cdbd2cbd3fd4c026ca5055475bdf2067
MD5 (matrix.tgz) = 3d063437c482d94bd7e35df1a08bbc84

Assets 7

21 May 08:16

Vonng

v1.5.0

16503db

v1.5.0 Release @ 2022-05-22

Docker Support & Infra Monitoring & CMDB & ETCD & Better Cold Backup

Highlights

Complete Docker Support, enable on meta nodes by default with ton's of software templates.
- gitea, 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 a single instance with redis_port option.
Better cold backup support: crontab for backup, delayed standby with pg_delay
Use ETCD as DCS, an 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

The 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 exist
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

Reforge

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

Now *_safeguard are boolean flags to avoid purging running instances 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_packages_meta -> node_packages_meta
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

Checksums

MD5 (app.tgz) = 7f5712993d68e09798e1d40cea6c6d87
MD5 (docker.tgz) = 53f3c12283d495e4cca55c7fbcaeabfd
MD5 (matrix.tgz) = 3d063437c482d94bd7e35df1a08bbc84
MD5 (pigsty.tgz) = b3b85e124c2cbcb3d64497585f2e53e8
MD5 (pkg.tgz) = 334abc323b78cb22b47d4b4e32635541

Assets 7

19 Apr 00:46

Vonng

v1.4.1

c33b5fc

v1.4.1 add Docker Support

Routine bug fix / Docker Support / English Docs

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

English document is available now.

Bug Fix

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 #100
change autovacuum_freeze_max_age from 100000000 to 1000000000

MD5 (pigsty.tgz) = 6f2db6af16e96a6842f28e299f98b96b
MD5 (pkg.tgz) = 9a8da6b926b2742bc37a8a339401f47a
MD5 (app.tgz) = b9a3de9cdea10cfaedf3316941a089ad
MD5 (matrix.tgz) = 3d063437c482d94bd7e35df1a08bbc84

Assets 6

29 Mar 01:47

Vonng

v1.4.0

f2f90cc

v1.4.0 Release

Quick Start

Get a new Linux x86_64 CentOS 7.8 node. with nopass sudo & ssh access, then:

bash -c "$(curl -fsSL http://download.pigsty.cc/get)"   # get latest pigsty source
cd ~/pigsty && ./configure --download --non-interactive # pre-check and config templating 
./infra.yml                                             # install pigsty on current node

You can also download pigsty source & software packages with curl:

curl -SL https://github.com/Vonng/pigsty/releases/download/v1.4.0/pkg.tgz -o /tmp/pkg.tgz
curl -SL https://github.com/Vonng/pigsty/releases/download/v1.4.0/pigsty.tgz | gzip -d | tar -xC

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:

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

infra-demo.yml = 
           infra.yml -l meta     +
           nodes.yml -l pg-test  +
           pgsql.yml -l pg-test +
           infra-loki.yml + infra-jupyter.yml + infra-pgweb.yml

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

Software Upgrade

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

Bug Fix

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

API Changes

New Variable

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

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

Assets 6

10 Dec 05:26

Vonng

v1.3.1

d5f2e3f

v1.3.1 Bug fix & Dashboard Polish & Software Upgrade

[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

How to upgrade ?

Manually upgrade grafana with:

cd /etc/yum.repos.d
cp -f backup/grafana.repo .
yum clean all && yum makecache
yum upgrade -y grafana # 8.3.1-1
systemctl restart grafana-server

Install

curl -SL https://github.com/Vonng/pigsty/releases/download/v1.3.1/pkg.tgz -o /tmp/pkg.tgz
curl -SL https://github.com/Vonng/pigsty/releases/download/v1.3.1/pigsty.tgz | gzip -d | tar -xC ~ && cd ~/pigsty  
./configure
make install

v1.3.1 问题修复，面板改善与软件升级

[监控]

PGSQL & PGCAT 面板精制抛光
调整 PGCAT Instance & PGCAT Database的默认布局，优化信息呈现方式
在PGSQL Instance面板中添加Key Metrics，并与PGSQL Cluster保持一致
将表膨胀面板 PGCAT Bloat 集成入 PGCAT Database 面板
在 PGCAT Database 面板中添加索引相关信息
根据Grafana 8.3的变化调整所有Panel样式：默认列名变化，Fill Opacity默认值变化
在Pigsty Nginx首页添加了Redis集群的快速导航

[部署]

添加新的 infra-demo.yml 剧本，用于一次性快速拉起多节点沙箱，或专用于特殊配置的环境。
将 JupyterLab 从基础设施部署剧本中移除，成为独立的可选组件，使用infra-jupyter.yml剧本部署
将 PGWeb 从基础设施部署剧本中移除，成为独立的可选组件，使用infra-pgweb.yml剧本部署
在管理节点上添加新的pg管理命令别名，可以从管理节点上对数据库节点发起控制。
根据TimescaleDB-tune工具的建议，调整了所有模版中参数max_locks_per_transactions的默认值。
为所有配置模板添加了参数citus.node_conninfo: 'sslmode=prefer'，以便在不启用SSL的情况下使用Citus
PGDG14源中的所有扩展插件（pgrouting除外）均已加入软件列表中。
NodeExporter的默认版本升级至v1.3.1
将 PostgREST 9.0 加入默认下载软件包中，可用于自动从数据库模式生成后端REST API。

[问题修复]

修复了Grafana任意文件访问高危漏洞
修复了从半路中重新执行pgsql剧本时，当执行至注册Consul服务时，pg_instance & pg_service 变量不存在的问题
修复了当存在未定义pg_cluster节点时，Nginx主页渲染失败的问题
修复了升级至Grafana8.3 导致的样式变化

MD5 (app.tgz) = f604d27c7ed3dc1dec9c315e88e43df5
MD5 (pigsty.tgz) = 5f7c04114f4753e524e3e7cd4b16f113
MD5 (pkg.tgz) = 17204439621f324db5630413bb011df1

Assets 5

29 Nov 06:48

Vonng

v1.3.0

501d8c6

v1.3 Redis Support & PGCAT Overhaul

[FEATURE] Redis Deployment
- standalone mode
- cluster mode
- sentinel mode
- redis_exporter & prometheus record & alert rules
[FEATURE] Redis Monitor
[FEATURE] monitor: PGCAT Overhaul
- New Dashboard: PGCAT Instance
- New Dashboard: PGCAT Database Dashboard
- Remake Dashboard: PGCAT Table Dashboard
- Now Grafana datasource is automatically registered during monly deployment (PGCAT is enabled)
[FEATURE] 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] software upgrade
- add PostgreSQL 13 back 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
[BUG FIX] shell script file permission fixed: #69
[BUG FIX] fix pglog shortcuts (pglog12, pglog13, pglog14) to adopt different csvlog versions. add support for PG14 new error codes.
[CHANGE] change default values for max_locks_per_transactions
[CHANGE] increase default swapness from 0 to 10 for tuned config templates.
[CHANGE] default monitor targets file are now @ /etc/prometheus/targets instead of /etc/pigsty/targets

How to Upgrade?

This upgrade is all about prometheus & grafana. Following play will do the trick:

cd pigsty && git pull # upgrade pigsty from v1.2 to v1.3
./infra.yml -t grafana_provision,prometheus_config,prometheus_reload

Assets 5

03 Nov 03:13

Vonng

v1.2.0

55f9937

v1.2.0 Release: PG 14 by Default

[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
- Postgres to 14.0
- TimescaleDB to 2.5
- 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
[BUG FIX] fix register_grafana logic: put password in secureJsonData instead of jsonData
[BUG FIX] fix cmdb.sql: now pglog.sample can handle different version of postgres csv log
- pglog.sample is a parent table with three child table sample12, sample13, sample14
- pglog shortcuts are enhanced into pglog12, pglog13, pglog14 (<- pglog). which will pour csvlog into right table.
[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.
[Application] applog can visualize your Apple iOS15 privacy log.

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 infra.yml will do most of the parts

As for the database. You can still use the existing PG13 instances. In-place upgrade to PG14 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.

Assets 5

20 Oct 15:11

Vonng

v1.1.1

c7e7530

v1.1.1 TimescaleDB upgrade and new Patroni Templates

Pigsty v.1.1.1

Upgrade timescaledb from apache version to timescale version.
Add a series of new patroni config templates.

The default value for timescaledb.license is apache rather than timescale as the doc describes #3718

Changes

[ENHANCEMENT] replace timescaledb apache version with timescale version
[ENHANCEMENT] upgrade prometheus to 2.30
[ENHANCEMENT] add new patroni config templates
[BUG FIX] now pg_exporter config dir's owner are {{ pg_dbsu }} instead of prometheus

What's new in patroni config tempaltes?

Now there are a series of pre-defined patroni config templates aims for different machine spec.

Conf Name	CPU	Mem	Disk
`tiny`	1	1GB	40GB
`mini`	2	4GB	100GB
`small`	4	8GB	200GB
`medium`	8	16GB	500GB
`large`	16	32GB	1TB
`xlarge`	32	64GB	2TB
`oltp,crit,olap`	64	400GB	4TB

How to upgrade from existing setup ?

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

(reinstall & restart required)

You can failover with patroni, then perform the timescaledb upgrade.

systemctl stop patroni
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

Assets 5

13 Oct 08:01

Vonng

v1.1

36fffae

v1.1 HomePage & Client Utils

Demo

Check http://home.pigsty.cc for the brand new user interface! (home page) 🎉

Enhancements

[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:
[ENHANCEMENT] add pg_dummy_filesize to create fs space placeholder
[ENHANCEMENT] now haproxy admin proxy are served under default server (h.pigsty is no longer needed)
[ENHANCEMENT] add a new example app: applog which could visualize Apple iOS15 privacy log with grafana dashboards.
[ENHANCEMENT] add promscale to default pkg.tgz (replace prometheus tsdb with timescaledb)

Software Upgrades

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

Bug Fixes

pgbouncer 1.16 SHOW POOLS, SHOW DATABASES returns an extra column
ignore errors when provisioning database in existing clusters (skip instead of halt)

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 (internal)
now default value for repo_address is pigsty instead of yum.pigsty
now haproxy access point is http://pigsty instead of http://h.pigsty

Assets 4

Releases: Vonng/pigsty

v2.0.0 Release

Highlights

Compatibility

Security

Maintainability

API Changes

Contributors

v1.5.1 Release (PG14.4 BUGFIX)

Highlights

Software Upgrade

Bug fix:

API Changes

New Apps

v1.5.0 Release @ 2022-05-22

Highlights

Monitoring

Provisioning

Bug Fix

API Change

Checksums

v1.4.1 add Docker Support

Bug Fix

v1.4.0 Release

Quick Start

Architecture

Accessibility

Monitor Enhancement

MatrixDB Support

Provisioning Enhancement

Software Upgrade

Bug Fix

API Changes

v1.3.1 Bug fix & Dashboard Polish & Software Upgrade

How to upgrade ?

Install

v1.3.1 问题修复，面板改善与软件升级

v1.3 Redis Support & PGCAT Overhaul

How to Upgrade?

v1.2.0 Release: PG 14 by Default

How to upgrade from v1.1.1

v1.1.1 TimescaleDB upgrade and new Patroni Templates

Pigsty v.1.1.1

Changes

What's new in patroni config tempaltes?

How to upgrade from existing setup ?

v1.1 HomePage & Client Utils

Demo

Enhancements

Software Upgrades

Bug Fixes

API Changes