v2.0.0 Release

[Vonng]

"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.PG_SERVICE.pg_vip_address : vip address in <ipv4>/<mask> format, require if vip is enabled
• PGSQL.PG_SERVICE.pg_vip_interface : vip network interface to listen, eth0 by default
• PGSQL.PG_SERVICE.pg_dns_suffix : pgsql cluster dns name suffix, '' by default
• PGSQL.PG_SERVICE.pg_dns_target : auto, primary, vip, none, or ad hoc ip
• ETCD.etcd_seq : etcd instance identifier, REQUIRED
• ETCD.etcd_cluster : etcd cluster & group name, etcd by default
• ETCD.etcd_safeguard : prevent purging running etcd instance?
• ETCD.etcd_clean : purging existing etcd during initialization?
• ETCD.etcd_data : etcd data directory, /data/etcd by default
• ETCD.etcd_port : etcd client port, 2379 by default
• ETCD.etcd_peer_port : etcd peer port, 2380 by default
• ETCD.etcd_init : etcd initial cluster state, new or existing
• ETCD.etcd_election_timeout : etcd election timeout, 1000ms by default
• ETCD.etcd_heartbeat_interval : etcd heartbeat interval, 100ms by default
• MINIO.minio_seq : minio instance identifier, REQUIRED
• MINIO.minio_cluster : minio cluster name, minio by default
• MINIO.minio_clean : cleanup minio during init?, false by default
• MINIO.minio_user : minio os user, minio by default
• MINIO.minio_node : minio node name pattern
• MINIO.minio_data : minio data dir(s), use {x...y} to specify multi drivers
• MINIO.minio_domain : minio external domain name, sss.pigsty by default
• MINIO.minio_port : minio service port, 9000 by default
• MINIO.minio_admin_port : minio console port, 9001 by default
• MINIO.minio_access_key : root access key, minioadmin by default
• MINIO.minio_secret_key : root secret key, minioadmin by default
• MINIO.minio_extra_vars : extra environment variables for minio server
• MINIO.minio_alias : alias name for local minio deployment
• MINIO.minio_buckets : list of minio bucket to be created
• MINIO.minio_users : list of minio user to be created

Removed Parameters

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

Renamed Parameters

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

Checksums

MD5 (pigsty-v2.0.0.tgz) = 78a6ae5ff9ab0b8aa3848805aeadd1d8
MD5 (pigsty-pkg-v2.0.0.el7.x86_64.tgz) = 9ff3c973fa5915f65622b91419817c9b
MD5 (pigsty-pkg-v2.0.0.el8.x86_64.tgz) = bd108a6c8f026cb79ee62c3b68b72176
MD5 (pigsty-pkg-v2.0.0.el9.x86_64.tgz) = e24288770f240af0511b0c38fa2f4774

Special thanks to @alemacci for his great contribution!

---

This discussion was created from the release v2.0.0 Release.