Skip to content

Pigsty v2.0.0

Major architecture upgrade, compatibility/security/maintainability
Major architecture upgrade, compatibility/security/maintainability

v2.0.0 ·

Highlights

  • Perfect integration of PostgreSQL 15, PostGIS 3.3, Citus 11.2, TimescaleDB 2.10 — distributed geospatial time-series hyper-converged database
  • Major OS compatibility improvements: supports EL7, 8, 9, plus RHEL, CentOS, Rocky, OracleLinux, AlmaLinux compatible distros
  • Security improvements: self-signed CA, global SSL network encryption, scram-sha-256 password auth, AES-encrypted backups, redesigned HBA rule system
  • Patroni upgraded to 3.0, providing native HA Citus distributed cluster support, FailSafe mode enabled by default — no fear of DCS failures causing global primary outages
  • Out-of-the-box PITR support based on pgBackRest, default support for local filesystem and dedicated MinIO/S3 cluster backups
  • New ETCD module: independently deployable, easy scaling, built-in monitoring and HA, completely replacing Consul as DCS for HA PG
  • New MINIO module: independently deployable, multi-disk multi-node support, S3 local replacement, also for centralized PostgreSQL backup repository
  • Significantly simplified configuration parameters, usable without defaults; templates auto-adjust host and PG parameters based on machine specs, HBA/service definitions more concise and universal
  • License changed from Apache License 2.0 to AGPL 3.0 due to Grafana and MinIO dependencies

Compatibility

  • Supports EL7, EL8, EL9 major versions with corresponding offline packages, default dev/test environment upgraded from EL7 to EL9
  • Supports more EL-compatible Linux distros: RHEL, CentOS, RockyLinux, AlmaLinux, OracleLinux, etc.
  • Source and offline package naming conventions changed — version, OS version, and architecture now reflected in package names
  • PGSQL: PostgreSQL 15.2, PostGIS 3.3, Citus 11.2, TimescaleDB 2.10 now work together harmoniously
  • PGSQL: Patroni upgraded to 3.0 as PGSQL HA component
    • ETCD now default DCS, replacing Consul, eliminating one Consul Agent failure point
    • vip-manager upgraded to 2.1 using ETCDv3 API, completely deprecating ETCDv2 API; same for Patroni
    • Native HA Citus distributed cluster support using fully open-source Citus 11.2
    • FailSafe mode enabled by default — no fear of DCS failures causing global primary outages
  • PGSQL: pgBackrest v2.44 introduced for out-of-the-box PostgreSQL PITR
    • Default backup repo on primary’s backup directory, rolling two-day recovery window
    • Default alternative repo is dedicated MinIO/S3 cluster, rolling two-week recovery window; local use requires enabling MinIO module
  • ETCD now an independently deployed module with complete scale-out/in solution and monitoring
  • MINIO now an independently deployed module, multi-disk multi-node support, S3 local replacement, also for centralized backup repository
  • NODE module now includes haproxy, docker, node_exporter, promtail components
    • chronyd now replaces ntpd as default NTP service on all nodes
    • HAPROXY now part of NODE rather than PGSQL-exclusive, can expose services via NodePort
    • PGSQL module can now use dedicated centralized HAPROXY cluster for unified external service
  • INFRA module now includes dnsmasq, nginx, prometheus, grafana, loki components
    • DNSMASQ server in Infra module enabled by default, added as default DNS server for all nodes
    • Added blackbox_exporter for host PING probing, pushgateway for batch job metrics
    • loki and promtail now use Grafana’s default packages with official Grafana Echarts panel plugin
    • Monitoring support for PostgreSQL 15’s new observability points, added Patroni monitoring
  • Software version upgrades
    • 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 20230131022419 / mcli 20230128202938
    • Prometheus 2.42 / Grafana 9.3 / Loki & Promtail 2.7 / Node Exporter 1.5

Security

  • Complete local self-signed CA: pigsty-ca for issuing internal component certificates
  • User creation/password changes no longer leave traces in log files
  • Nginx enables SSL support by default (for HTTPS, trust pigsty-ca in your system or use Chrome thisisunsafe)
  • ETCD fully enables SSL encryption for client and peer communication
  • PostgreSQL SSL support added and enabled by default, management connections use SSL
  • Pgbouncer SSL support added, disabled by default for performance
  • Patroni SSL support added, management API restricted to local and admin node access with password auth
  • PostgreSQL default password auth changed from md5 to scram-sha-256
  • Pgbouncer auth query support added for dynamic connection pool user management
  • pgBackRest uses AES-256-CBC encryption by default for remote centralized backup storage
  • High-security template provided: enforces global SSL and requires admin certificate login
  • All default HBA rules now explicitly defined in config files

Maintainability

  • Existing config templates auto-adjust optimizations based on machine specs (CPU/memory/storage)
  • Postgres/Pgbouncer/Patroni/pgBackRest log directories now dynamically configurable: default /pg/log/<type>/
  • Original IP placeholder 10.10.10.10 replaced with dedicated variable ${admin_ip}, referenceable in multiple places for switching backup admin nodes
  • region can be specified to use upstream mirrors from different regions for faster package downloads
  • Finer-grained upstream source addresses now allowed based on EL version, architecture, and region
  • Terraform templates for Alibaba Cloud and AWS China provided for one-click EC2 VM provisioning
  • Multiple Vagrant sandbox templates provided: meta, full, el7/8/9, minio, build, citus
  • New dedicated playbook: pgsql-monitor.yml for monitoring existing Postgres instances or RDS
  • New dedicated playbook: pgsql-migration.yml for seamless logical replication migration to Pigsty-managed clusters
  • Series of dedicated shell utilities added, wrapping common ops operations
  • All Ansible roles optimized for simplicity, readability, and maintainability — usable without default parameters
  • Additional Pgbouncer parameters can be defined at business database/user level

API Changes

Pigsty v2.0 has extensive changes: 64 new parameters, 13 removed, 17 renamed.

New 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 version: 7,8,9
  • INFRA.CA.ca_cn: CA Common Name, default pigsty-ca
  • INFRA.CA.cert_validity: Certificate validity, default 20 years
  • INFRA.REPO.repo_enabled: Build local yum repo on infra node?
  • INFRA.REPO.repo_upstream: Upstream yum repo definition list
  • INFRA.REPO.repo_home: Local yum repo home directory, usually same as nginx_home ‘/www’
  • INFRA.NGINX.nginx_ssl_port: HTTPS listen port
  • INFRA.NGINX.nginx_ssl_enabled: Enable nginx HTTPS?
  • INFRA.PROMETHEUS.alertmanager_endpoint: Alertmanager endpoint (ip|domain):port format
  • NODE.NODE_TUNE.node_hugepage_ratio: Memory hugepage ratio, default 0 (disabled)
  • NODE.HAPROXY.haproxy_service: List of haproxy services to expose
  • 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
  • PGSQL.PG_INSTALL.pg_log_dir: postgres log directory, default /pg/data/log
  • PGSQL.PG_BOOTSTRAP.pg_storage_type: SSD|HDD, default SSD
  • PGSQL.PG_BOOTSTRAP.patroni_log_dir: patroni log directory, default /pg/log
  • PGSQL.PG_BOOTSTRAP.patroni_ssl_enabled: Use SSL for patroni RestAPI?
  • 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, default postgres
  • PGSQL.PG_BOOTSTRAP.pg_max_conn: postgres max connections, auto uses recommended value
  • PGSQL.PG_BOOTSTRAP.pg_shmem_ratio: postgres shared memory ratio, default 0.25, range 0.1~0.4
  • PGSQL.PG_BOOTSTRAP.pg_rto: Recovery Time Objective, failover ttl, default 30s
  • PGSQL.PG_BOOTSTRAP.pg_rpo: Recovery Point Objective, max 1MB data loss by default
  • PGSQL.PG_BOOTSTRAP.pg_pwd_enc: Password encryption algorithm: md5|scram-sha-256
  • PGSQL.PG_BOOTSTRAP.pgbouncer_log_dir: pgbouncer log directory, default /var/log/pgbouncer
  • PGSQL.PG_BOOTSTRAP.pgbouncer_auth_query: If enabled, query pg_authid for biz users instead of populating user list
  • PGSQL.PG_BOOTSTRAP.pgbouncer_sslmode: pgbouncer client SSL: disable|allow|prefer|require|verify-ca|verify-full
  • PGSQL.PG_BOOTSTRAP.pg_service_provider: Dedicated haproxy node group name, or empty for local node
  • PGSQL.PG_BOOTSTRAP.pg_default_service_dest: Default service destination if svc.dest=‘default’
  • PGSQL.PG_BACKUP.pgbackrest_enabled: Enable pgbackrest?
  • PGSQL.PG_BACKUP.pgbackrest_clean: Remove pgbackrest data during init?
  • PGSQL.PG_BACKUP.pgbackrest_log_dir: pgbackrest log directory, default /pg/log
  • PGSQL.PG_BACKUP.pgbackrest_method: pgbackrest backup repo method: local or minio
  • PGSQL.PG_BACKUP.pgbackrest_repo: pgbackrest backup repo config
  • PGSQL.PG_DNS.pg_dns_suffix: pgsql dns suffix, default empty
  • PGSQL.PG_DNS.pg_dns_target: auto, primary, vip, none, or ad hoc ip
  • ETCD.etcd_seq: etcd instance identifier, required
  • ETCD.etcd_cluster: etcd cluster and group name, default etcd
  • ETCD.etcd_safeguard: Prevent purging running etcd instances?
  • ETCD.etcd_clean: Clean existing etcd during init?
  • ETCD.etcd_data: etcd data directory, default /data/etcd
  • ETCD.etcd_port: etcd client port, default 2379
  • ETCD.etcd_peer_port: etcd peer port, default 2380
  • ETCD.etcd_init: etcd initial cluster state: new or existing
  • ETCD.etcd_election_timeout: etcd election timeout, default 1000ms
  • ETCD.etcd_heartbeat_interval: etcd heartbeat interval, default 100ms
  • MINIO.minio_seq: minio instance identifier, required
  • MINIO.minio_cluster: minio cluster name, default minio
  • MINIO.minio_clean: Clean minio during init? default false
  • MINIO.minio_user: minio OS user, default minio
  • MINIO.minio_node: minio node name pattern
  • MINIO.minio_data: minio data directory, use {x…y} for multiple drives
  • MINIO.minio_domain: minio external domain, default sss.pigsty
  • MINIO.minio_port: minio service port, default 9000
  • MINIO.minio_admin_port: minio console port, default 9001
  • MINIO.minio_access_key: root access key, default minioadmin
  • MINIO.minio_secret_key: root secret key, default minioadmin
  • MINIO.minio_extra_vars: extra environment variables for minio server
  • MINIO.minio_alias: alias for local minio deployment
  • MINIO.minio_buckets: list of minio buckets to create
  • MINIO.minio_users: list of minio users to create

Removed Parameters

  • INFRA.CA.ca_homedir: CA home directory, now fixed to /etc/pki/
  • INFRA.CA.ca_cert: CA certificate filename, now fixed to ca.key
  • INFRA.CA.ca_key: CA key filename, now fixed to ca.key
  • INFRA.REPO.repo_upstreams: Replaced by repo_upstream
  • PGSQL.PG_INSTALL.pgdg_repo: Now handled by node playbooks
  • PGSQL.PG_INSTALL.pg_add_repo: Now handled by node playbooks
  • PGSQL.PG_IDENTITY.pg_backup: Unused and conflicted with partial names
  • PGSQL.PG_IDENTITY.pg_preflight_skip: No longer used, replaced by pg_id
  • DCS.dcs_name: Removed due to etcd usage
  • DCS.dcs_servers: Replaced by ad hoc group etcd
  • DCS.dcs_registry: Removed due to etcd usage
  • DCS.dcs_safeguard: Replaced by etcd_safeguard
  • DCS.dcs_clean: Replaced by etcd_clean

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
  • vip_mode -> pg_vip_enabled
  • vip_address -> pg_vip_address
  • vip_interface -> pg_vip_interface
  • node_packages_default -> node_default_packages
  • node_packages_meta -> infra_packages
  • node_packages_meta_pip -> infra_packages_pip
  • node_data_dir -> node_data

Special thanks to Italian user @alemacci for contributions on SSL encryption, backup, multi-OS distro adaptation, and adaptive parameter templates!

Sources