Release Notes

    Highlight

    Store OpenAI embedding and search similar vectors with

    • New extension pgvector
    • fix, and upgrade to 20230324 with new policy API:

    Changes

    • New extension pgvector for storing OpenAI embedding and searching similar vectors.
    • 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

    v2.0.1

    Bug fix for 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 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”

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

    Bug Fix

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

    [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
    • 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

    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

    Pigsty GUI, CLI, Logging Intergration

    Features

    • One-Line Installation

      Run this on meta node /bin/bash -c "$(curl -fsSL https://pigsty.cc/install)"

    • MetaDB provisioning

      Now you can use pgsql database on meta node as inventory instead of static yaml file affter bootstrap.

    • Add Loki & Prometail as optinal logging collector

      Now you can view, query, search postgres|pgbouncer|patroni logs with Grafana UI (PG Instance Log)

    • Pigsty CLI/GUI (beta)

      Mange you pigsty deployment with much more human-friendly command line interface.

    Bug Fix

    • Log related issues
      • fix connection reset by peer entries in postgres log caused by Haproxy health check.
      • fix Connect Reset Exception in patroni logs caused by haproxy health check
      • fix patroni log time format (remove mill seconds, add timezone)
      • set log_min_duration_statement=1s for dbuser_monitor to get ride of monitor logs.
    • Fix pgbouncer-create-user does not handle md5 password properly
    • Fix obsolete Makefile entries
    • Fix node dns nameserver lost when abort during resolv.conf rewrite
    • Fix db/user template and entry not null check

    API Change

    • Set default value of node_disable_swap to false
    • Remove example enties of node_sysctl_params.
    • grafana_plugin default install will now download from CDN if plugins not exists
    • repo_url_packages now download rpm via pigsty CDN to accelerate.
    • proxy_env.no_proxy now add pigsty CDN to noproxy sites。
    • grafana_customize set to false by default，enable it means install pigsty pro UI.
    • node_admin_pk_current add current user’s ~/.ssh/id_rsa.pub to admin pks
    • loki_clean whether to cleanup existing loki data during init
    • loki_data_dir set default data dir for loki logging service
    • promtail_enabled enabling promtail logging agent service?
    • promtail_clean remove existing promtail status during init?
    • promtail_port default port used by promtail, 9080 by default
    • promtail_status_file location of promtail status file
    • promtail_send_url endpoint of loki service which receives log data

    v0.8.0

    Service Provisioning support is added in this release

    New Features

    • Service provision.
    • full locale support.

    API Changes

    Role vip and haproxy are merged into service.

    1. #------------------------------------------------------------------------------
    2. # SERVICE PROVISION
    3. #------------------------------------------------------------------------------
    4. pg_weight: 100              # default load balance weight (instance level)
    6. # - service - #
    7. pg_services:                                  # how to expose postgres service in cluster?
    8.   # primary service will route {ip|name}:5433 to primary pgbouncer (5433->6432 rw)
    9.   - name: primary           # service name {{ pg_cluster }}_primary
    10.     src_ip: "*"
    11.     src_port: 5433
    12.     dst_port: pgbouncer     # 5433 route to pgbouncer
    13.     check_url: /primary     # primary health check, success when instance is primary
    14.     selector: "[]"          # select all instance as primary service candidate
    16.   # replica service will route {ip|name}:5434 to replica pgbouncer (5434->6432 ro)
    17.   - name: replica           # service name {{ pg_cluster }}_replica
    18.     src_ip: "*"
    19.     src_port: 5434
    20.     dst_port: pgbouncer
    21.     check_url: /read-only   # read-only health check. (including primary)
    22.     selector: "[]"          # select all instance as replica service candidate
    23.     selector_backup: "[? pg_role == `primary`]"   # primary are used as backup server in replica service
    25.   # default service will route {ip|name}:5436 to primary postgres (5436->5432 primary)
    26.   - name: default           # service's actual name is {{ pg_cluster }}-{{ service.name }}
    27.     src_ip: "*"             # service bind ip address, * for all, vip for cluster virtual ip address
    28.     src_port: 5436          # bind port, mandatory
    29.     dst_port: postgres      # target port: postgres|pgbouncer|port_number , pgbouncer(6432) by default
    30.     check_method: http      # health check method: only http is available for now
    31.     check_port: patroni     # health check port:  patroni|pg_exporter|port_number , patroni by default
    32.     check_url: /primary     # health check url path, / as default
    33.     check_code: 200         # health check http code, 200 as default
    34.     selector: "[]"          # instance selector
    35.     haproxy:                # haproxy specific fields
    36.       maxconn: 3000         # default front-end connection
    37.       balance: roundrobin   # load balance algorithm (roundrobin by default)
    38.       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'
    40.   # offline service will route {ip|name}:5438 to offline postgres (5438->5432 offline)
    41.   - name: offline           # service name {{ pg_cluster }}_replica
    43.     src_port: 5438
    44.     dst_port: postgres
    45.     check_url: /replica     # offline MUST be a replica
    46.     selector: "[? pg_role == `offline` || pg_offline_query ]"         # instances with pg_role == 'offline' or instance marked with 'pg_offline_query == true'
    47.     selector_backup: "[? pg_role == `replica` && !pg_offline_query]"  # replica are used as backup server in offline service
    49. pg_services_extra: []        # extra services to be added
    51. # - haproxy - #
    52. haproxy_enabled: true                         # enable haproxy among every cluster members
    53. haproxy_reload: true                          # reload haproxy after config
    54. haproxy_policy: roundrobin                    # roundrobin, leastconn
    55. haproxy_admin_auth_enabled: false             # enable authentication for haproxy admin?
    56. haproxy_admin_username: admin                 # default haproxy admin username
    57. haproxy_admin_password: admin                 # default haproxy admin password
    58. haproxy_exporter_port: 9101                   # default admin/exporter port
    59. haproxy_client_timeout: 3h                    # client side connection timeout
    60. haproxy_server_timeout: 3h                    # server side connection timeout
    62. # - vip - #
    63. vip_mode: none                                # none | l2 | l4
    64. vip_reload: true                              # whether reload service after config
    65. # vip_address: 127.0.0.1                      # virtual ip address ip (l2 or l4)
    66. # vip_cidrmask: 24                            # virtual ip address cidr mask (l2 only)
    67. # vip_interface: eth0                         # virtual ip network interface (l2 only)

    New Options

    Remove Options

    1. haproxy_check_port                            # covered by service options
    2. haproxy_primary_port
    3. haproxy_replica_port
    4. haproxy_backend_port
    5. haproxy_weight
    6. haproxy_weight_fallback
    7. vip_enabled                                   # replace by vip_mode

    Service

    pg_services and pg_services_extra Defines the services in cluster:

    A service has some mandatory fields:

    • name: service’s name
    • selector: which instances belonging to this service?
    1.   # default service will route {ip|name}:5436 to primary postgres (5436->5432 primary)
    2.   - name: default           # service's actual name is {{ pg_cluster }}-{{ service.name }}
    3.     src_ip: "*"             # service bind ip address, * for all, vip for cluster virtual ip address
    4.     src_port: 5436          # bind port, mandatory
    5.     dst_port: postgres      # target port: postgres|pgbouncer|port_number , pgbouncer(6432) by default
    6.     check_method: http      # health check method: only http is available for now
    7.     check_port: patroni     # health check port:  patroni|pg_exporter|port_number , patroni by default
    8.     check_url: /primary     # health check url path, / as default
    9.     check_code: 200         # health check http code, 200 as default
    10.     selector: "[]"          # instance selector
    11.     haproxy:                # haproxy specific fields
    12.       maxconn: 3000         # default front-end connection
    13.       balance: roundrobin   # load balance algorithm (roundrobin by default)
    14.       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.

    1. pg_databases:
    2.   - name: meta                      # name is the only required field for a database
    3.     # owner: postgres                 # optional, database owner
    4.     # template: template1             # optional, template1 by default
    5.     # encoding: UTF8                # optional, UTF8 by default , must same as template database, leave blank to set to db default
    6.     # locale: C                     # optional, C by default , must same as template database, leave blank to set to db default
    7.     # lc_collate: C                 # optional, C by default , must same as template database, leave blank to set to db default
    8.     # lc_ctype: C                   # optional, C by default , must same as template database, leave blank to set to db default
    9.     allowconn: true                 # optional, true by default, false disable connect at all
    10.     revokeconn: false               # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
    11.     # tablespace: pg_default          # optional, 'pg_default' is the default tablespace
    12.     connlimit: -1                   # optional, connection limit, -1 or none disable limit (default)
    13.     extensions:                     # optional, extension name and where to create
    14.       - {name: postgis, schema: public}
    15.     parameters:                     # optional, extra parameters with ALTER DATABASE
    16.       enable_partitionwise_join: true
    17.     pgbouncer: true                 # optional, add this database to pgbouncer list? true by default
    18.     comment: pigsty meta database   # optional, comment string for database

    v0.7.0

    Monitor only deployment support

    Overview

      • Now you can monitoring existing postgres clusters without Pigsty provisioning solution.
      • Intergration with other provisioning solution is available and under further test.

    • Database/User Management

      • Update user/database definition schema to cover more usecases.
      • Add pgsql-createdb.yml and pgsql-user.yml to mange user/db on running clusters.

    Features

    Bug Fix

    API Changes

    New Options

    1. prometheus_sd_target: batch                   # batch|single
    2. exporter_install: none                        # none|yum|binary
    3. exporter_repo_url: ''                         # add to yum repo if set
    4. node_exporter_options: '--no-collector.softnet --collector.systemd --collector.ntp --collector.tcpstat --collector.processes'                          # default opts for node_exporter
    5. pg_exporter_url: ''                           # optional, overwrite default pg_exporter target
    6. pgbouncer_exporter_url: ''                    # optional, overwrite default pgbouncer_expoter target

    Remove Options

    1. exporter_binary_install: false                 # covered by exporter_install

    Structure Changes

    1. pg_default_roles                               # refer to pg_users
    2. pg_users                                       # refer to pg_users
    3. pg_databases                                   # refer to pg_databases

    Rename Options

    1. pg_default_privilegs -> pg_default_privileges  # fix typo

    Enhancement

    Monitoring Provisioning Enhancement

    Haproxy Enhancement

    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

    1. service_registry: consul                      # none | consul | etcd | both
    2. prometheus_options: '--storage.tsdb.retention=30d'  # prometheus cli opts
    3. prometheus_sd_method: consul                  # Prometheus service discovery method：static|consul
    4. prometheus_sd_interval: 2s                    # Prometheus service discovery refresh interval
    5. pg_offline_query: false                       # set to true to allow offline queries on this instance
    6. node_exporter_enabled: true                   # enabling Node Exporter
    7. pg_exporter_enabled: true                     # enabling PG Exporter
    8. pgbouncer_exporter_enabled: true              # enabling Pgbouncer Exporter
    9. export_binary_install: false                  # install Node/PG Exporter via copy binary
    10. dcs_disable_purge: false                      # force dcs_exists_action = abort to avoid dcs purge
    11. pg_disable_purge: false                       # force pg_exists_action = abort to avoid pg purge
    12. haproxy_weight: 100                           # relative lb weight for backend instance
    13. haproxy_weight_fallback: 1                    # primary server weight in replica service group

    Obsolete Config Entries

    1. prometheus_metrics_path                       # duplicate with exporter_metrics_path 
    2. prometheus_retention                          # covered by `prometheus_options`

    Database Definition

    Database provisioning interface enhancement #33

    Old Schema

    New Schema

    1. pg_databases:
    2.   - name: meta                      # name is the only required field for a database
    3.     owner: postgres                 # optional, database owner
    4.     template: template1             # optional, template1 by default
    5.     encoding: UTF8                  # optional, UTF8 by default
    6.     locale: C                       # optional, C by default
    7.     allowconn: true                 # optional, true by default, false disable connect at all
    8.     revokeconn: false               # optional, false by default, true revoke connect from public # (only default user and owner have connect privilege on database)
    9.     tablespace: pg_default          # optional, 'pg_default' is the default tablespace
    10.     connlimit: -1                   # optional, connection limit, -1 or none disable limit (default)
    11.     extensions:                     # optional, extension name and where to create
    12.       - {name: postgis, schema: public}
    13.     parameters:                     # optional, extra parameters with ALTER DATABASE
    14.       enable_partitionwise_join: true
    15.     pgbouncer: true                 # optional, add this database to pgbouncer list? true by default
    16.     comment: pigsty meta database   # optional, comment string for database

    Changes

    • Add new options: template , encoding, locale, allowconn, tablespace, connlimit
    • Add new option revokeconn, which revoke connect privileges from public for this database
    • Add comment field for database

    Apply Changes

    You can create new database on running postgres clusters with pgsql-createdb.yml playbook.

    1. Define your new database in config files
    2. Pass new database.name with option pg_database to playbook.
    1. ./pgsql-createdb.yml -e pg_database=<your_new_database_name>

    User Definition

    Old Schema

    1. pg_users:
    2.   - username: test                  # example production user have read-write access
    3.     password: test                  # example user's password
    4.     options: LOGIN                  # extra options
    5.     groups: [ dbrole_readwrite ]    # dborole_admin|dbrole_readwrite|dbrole_readonly
    6.     comment: default test user for production usage
    7.     pgbouncer: true                 # add to pgbouncer

    New Schema

    1. pg_users:
    2.   # complete example of user/role definition for production user
    3.   - name: dbuser_meta               # example production user have read-write access
    4.     password: DBUser.Meta           # example user's password, can be encrypted
    5.     login: true                     # can login, true by default (should be false for role)
    6.     superuser: false                # is superuser? false by default
    7.     createdb: false                 # can create database? false by default
    8.     createrole: false               # can create role? false by default
    9.     inherit: true                   # can this role use inherited privileges?
    10.     replication: false              # can this role do replication? false by default
    11.     bypassrls: false                # can this role bypass row level security? false by default
    12.     connlimit: -1                   # connection limit, -1 disable limit
    13.     expire_at: '2030-12-31'         # 'timestamp' when this role is expired
    14.     expire_in: 365                  # now + n days when this role is expired (OVERWRITE expire_at)
    15.     roles: [dbrole_readwrite]       # dborole_admin|dbrole_readwrite|dbrole_readonly
    16.     pgbouncer: true                 # add this user to pgbouncer? false by default (true for production user)
    17.     parameters:                     # user's default search path
    18.       search_path: public
    19.     comment: test user

    Changes

    • username field rename to name
    • groups field rename to roles
    • options now split into separated configration entries: login, superuser, createdb, createrole, inherit, replication,bypassrls,connlimit
    • expire_at and expire_in options
    • pgbouncer option for user is now false by default

    Apply Changes

    You can create new users on running postgres clusters with pgsql-createuser.yml playbook.

    1. Define your new users in config files (pg_users)
    2. Pass new user.name with option pg_user to playbook.
    1. ./pgsql-createuser.yml -e pg_user=<your_new_user_name>

    v0.6.0

    Architecture Enhancement

    Bug Fix

    • Merge Fix name of dashboard #1, Fix PG Overview Dashboard typo
    • Fix default primary instance to pg-test-1 of cluster pg-test in sandbox environment
    • Fix obsolete comments

    Monitoring Provisioning Enhancement

    Haproxy Enhancement

    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

    1. service_registry: consul                      # none | consul | etcd | both
    2. prometheus_options: '--storage.tsdb.retention=30d'  # prometheus cli opts
    3. prometheus_sd_method: consul                  # Prometheus service discovery method：static|consul
    4. prometheus_sd_interval: 2s                    # Prometheus service discovery refresh interval
    5. pg_offline_query: false                       # set to true to allow offline queries on this instance
    6. node_exporter_enabled: true                   # enabling Node Exporter
    7. pg_exporter_enabled: true                     # enabling PG Exporter
    8. pgbouncer_exporter_enabled: true              # enabling Pgbouncer Exporter
    9. export_binary_install: false                  # install Node/PG Exporter via copy binary
    10. dcs_disable_purge: false                      # force dcs_exists_action = abort to avoid dcs purge
    11. pg_disable_purge: false                       # force pg_exists_action = abort to avoid pg purge
    12. haproxy_weight: 100                           # relative lb weight for backend instance
    13. haproxy_weight_fallback: 1                    # primary server weight in replica service group

    Obsolete Config Entries

    1. prometheus_metrics_path                       # duplicate with exporter_metrics_path 
    2. prometheus_retention                          # covered by `prometheus_options`

    v0.5.0

    Pigsty now have an 🎉 !

    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: (v0.5)

    Database Provision Template

    Now you can customize your database content with pigsty !

    1. pg_users:
    2.   - username: test
    3.     password: test
    4.     comment: default test user
    5.     groups: [ dbrole_readwrite ]    # dborole_admin|dbrole_readwrite|dbrole_readonly
    6. pg_databases:                       # create a business database 'test'
    7.   - name: test
    8.     extensions: [{name: postgis}]   # create extra extension postgis
    9.     parameters:                     # overwrite database meta's default search_path
    10.       search_path: public,monitor

    pg-init-template.sql wil be used as default template1 database init script will be used as default business database init script

    you can customize default role system, schemas, extensions, privileges with variables now:

    Template Configuration

    1. # - system roles - #
    2. pg_replication_username: replicator           # system replication user
    3. pg_replication_password: DBUser.Replicator    # system replication password
    4. pg_monitor_username: dbuser_monitor           # system monitor user
    5. pg_monitor_password: DBUser.Monitor           # system monitor password
    6. pg_admin_username: dbuser_admin               # system admin user
    7. pg_admin_password: DBUser.Admin               # system admin password
    9. # - default roles - #
    10. pg_default_roles:
    11.   - username: dbrole_readonly                 # sample user:
    12.     options: NOLOGIN                          # role can not login
    13.     comment: role for readonly access         # comment string
    15.   - username: dbrole_readwrite                # sample user: one object for each user
    16.     options: NOLOGIN
    17.     comment: role for read-write access
    18.     groups: [ dbrole_readonly ]               # read-write includes read-only access
    20.   - username: dbrole_admin                    # sample user: one object for each user
    21.     options: NOLOGIN BYPASSRLS                # admin can bypass row level security
    22.     comment: role for object creation
    23.     groups: [dbrole_readwrite,pg_monitor,pg_signal_backend]
    25.   # NOTE: replicator, monitor, admin password are overwritten by separated config entry
    26.   - username: postgres                        # reset dbsu password to NULL (if dbsu is not postgres)
    27.     options: SUPERUSER LOGIN
    28.     comment: system superuser
    30.   - username: replicator
    31.     options: REPLICATION LOGIN
    32.     groups: [pg_monitor, dbrole_readonly]
    33.     comment: system replicator
    35.   - username: dbuser_monitor
    36.     options: LOGIN CONNECTION LIMIT 10
    37.     comment: system monitor user
    38.     groups: [pg_monitor, dbrole_readonly]
    40.   - username: dbuser_admin
    41.     options: LOGIN BYPASSRLS
    42.     comment: system admin user
    43.     groups: [dbrole_admin]
    45.   - username: dbuser_stats
    46.     password: DBUser.Stats
    47.     options: LOGIN
    48.     comment: business read-only user for statistics
    49.     groups: [dbrole_readonly]
    52. # object created by dbsu and admin will have their privileges properly set
    53. pg_default_privilegs:
    54.   - GRANT USAGE                         ON SCHEMAS   TO dbrole_readonly
    55.   - GRANT SELECT                        ON TABLES    TO dbrole_readonly
    56.   - GRANT SELECT                        ON SEQUENCES TO dbrole_readonly
    57.   - GRANT EXECUTE                       ON FUNCTIONS TO dbrole_readonly
    58.   - GRANT INSERT, UPDATE, DELETE        ON TABLES    TO dbrole_readwrite
    59.   - GRANT USAGE,  UPDATE                ON SEQUENCES TO dbrole_readwrite
    60.   - GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES    TO dbrole_admin
    61.   - GRANT CREATE                        ON SCHEMAS   TO dbrole_admin
    62.   - GRANT USAGE                         ON TYPES     TO dbrole_admin
    64. # schemas
    65. pg_default_schemas: [monitor]
    67. # extension
    68. pg_default_extensions:
    69.   - { name: 'pg_stat_statements',  schema: 'monitor' }
    70.   - { name: 'pgstattuple',         schema: 'monitor' }
    71.   - { name: 'pg_qualstats',        schema: 'monitor' }
    72.   - { name: 'pg_buffercache',      schema: 'monitor' }
    73.   - { name: 'pageinspect',         schema: 'monitor' }
    74.   - { name: 'pg_prewarm',          schema: 'monitor' }
    75.   - { name: 'pg_visibility',       schema: 'monitor' }
    76.   - { name: 'pg_freespacemap',     schema: 'monitor' }
    77.   - { name: 'pg_repack',           schema: 'monitor' }
    78.   - name: postgres_fdw
    79.   - name: file_fdw
    80.   - name: btree_gist
    81.   - name: btree_gin
    82.   - name: pg_trgm
    83.   - name: intagg
    84.   - name: intarray
    86. # postgres host-based authentication rules
    87. pg_hba_rules:
    88.   - title: allow meta node password access
    89.     role: common
    90.     rules:
    91.       - host    all     all                         10.10.10.10/32      md5
    93.   - title: allow intranet admin password access
    94.     role: common
    95.     rules:
    96.       - host    all     +dbrole_admin               10.0.0.0/8          md5
    97.       - host    all     +dbrole_admin               172.16.0.0/12       md5
    98.       - host    all     +dbrole_admin               192.168.0.0/16      md5
    100.   - title: allow intranet password access
    101.     role: common
    102.     rules:
    103.       - host    all             all                 10.0.0.0/8          md5
    104.       - host    all             all                 172.16.0.0/12       md5
    105.       - host    all             all                 192.168.0.0/16      md5
    107.   - title: allow local read-write access (local production user via pgbouncer)
    108.     role: common
    109.     rules:
    110.       - local   all     +dbrole_readwrite                               md5
    111.       - host    all     +dbrole_readwrite           127.0.0.1/32        md5
    113.   - title: allow read-only user (stats, personal) password directly access
    114.     role: replica
    115.     rules:
    116.       - local   all     +dbrole_readonly                               md5
    117.       - host    all     +dbrole_readonly           127.0.0.1/32        md5
    118. pg_hba_rules_extra: []
    120. # pgbouncer host-based authentication rules
    121. pgbouncer_hba_rules:
    122.   - title: local password access
    123.     role: common
    124.     rules:
    125.       - local  all          all                                     md5
    126.       - host   all          all                     127.0.0.1/32    md5
    128.   - title: intranet password access
    129.     role: common
    130.     rules:
    131.       - host   all          all                     10.0.0.0/8      md5
    132.       - host   all          all                     172.16.0.0/12   md5
    133.       - host   all          all                     192.168.0.0/16  md5
    134. 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
    • 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

    Offline Installation

    • pkg.tgz is the latest offline install package (1GB rpm packages, made under CentOS 7.8)