Battery-Included PostgreSQL Distro as a Free RDS Alternative
Bug fix for v2.0.0 and security improvement.
copy
instead of rsync
to copy grafana dashboards.dual.yml
for two-node deployment.log_connections
and log_disconnections
in crit.yml
template.$lib/passwordcheck
in pg_libs
in crit.yml
template.pg_monitor
role.dbrole_readonly
from dbuser_monitor
to limit monitor user privilege{{ inventory_hostname }}
instead of 0.0.0.0
pg_listen
${ip}
, ${lo}
, ${vip}
in pg_listen
pg_mode
error when adding postgres user with bin/pgsql-user
-a password
to redis-join task @ redis.yml
infra-rm.yml
.remove infra data
prometheus
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
cd ~/pigsty; tar -zcf /tmp/files.tgz files; rm -rf ~/pigsty # backup files dir and remove
cd ~; bash -c "$(curl -fsSL http://download.pigsty.cc/get)" # get latest pigsty source
cd ~/pigsty; rm -rf files; tar -xf /tmp/files.tgz -C ~/pigsty # restore files dir
MD5 (pigsty-v2.0.1.tgz) = 7addc87bc4edb46c3eacf723163ffbd4
MD5 (pigsty-pkg-v2.0.1.el7.x86_64.tgz) = 5cfbe98fd9706b9e0f15c1065971b3f6
MD5 (pigsty-pkg-v2.0.1.el8.x86_64.tgz) = c34aa460925ae7548866bf51b8b8759c
MD5 (pigsty-pkg-v2.0.1.el9.x86_64.tgz) = 055057cebd93c473a67fb63bcde22d33
Special thanks to @cocoonkid for his feedback.
"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
# 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
scram-sha-256
pwd encryption, and more.pgbackrest
, local or S3/minio.ETCD
, which can be easily deployed and scaled in/out. Used as DCS instead of Consul.MINIO
, local S3 alternative for the optional central backup repo for PGSQL PITR.node
itself, haproxy
, docker
, node_exporter
, and promtail
chronyd
is used as default NTP client instead of ntpd
NODE
instead of PGSQL
, which can be used for exposing servicesdnsmasq
, nginx
, prometheus
, grafana
, loki
thisisunsafe
to dismiss the warning)scram-sha-256
auth for postgres password encryption by @alemacciAES-256-CBC
for pgbackrest
encryption by @alemacci10.10.10.10
with ${admin_ip}
that can be referencedregion
& arch.meta
, full
, el7
el8
, el9
, build
, minio
, citus
, etc...pgsql-monitor.yml
for monitoring existing pg instance or RDS PG.pgsql-migration.yml
for migrating existing pg instance to pigsty managed pg.bin/
to simplify the daily administration tasks.69 parameters were added, 16 parameters were removed, rename 14 parameters
Added Parameters
INFRA
.META
.admin_ip
: primary meta node ip addressINFRA
.META
.region
: upstream mirror region: default|china|europeINFRA
.META
.os_version
: enterprise linux release version: 7,8,9INFRA
.CA
.ca_cn
: ca common name, pigsty-ca by defaultINFRA
.CA
.cert_validity
: cert validity, 20 years by defaultINFRA
.REPO
.repo_enabled
: build a local yum repo on infra node?INFRA
.REPO
.repo_upstream
: list of upstream yum repo definitionINFRA
.REPO
.repo_home
: home dir of local yum repo, usually same as nginx_home '/www'INFRA
.NGINX
.nginx_ssl_port
: https listen portINFRA
.NGINX
.nginx_ssl_enabled
: nginx https enabled?INFRA
.PROMTETHEUS
.alertmanager_endpoint
: altermanager endpoint in (ip|domain):port formatNODE
.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 defaultNODE
.NODE_TUNE
.node_overcommit_ratio
: node mem overcommit ratio, 0 disable it by defaultNODE
.HAPROXY
.haproxy_service
: list of haproxy service to be exposedPGSQL
.PG_ID
.pg_mode
: pgsql cluster mode: pgsql,citus,gpsqlPGSQL
.PG_BUSINESS
.pg_dbsu_password
: dbsu password, empty string means no dbsu password by defaultPGSQL
.PG_INSTALL
.pg_log_dir
: postgres log dir, /pg/data/log
by defaultPGSQL
.PG_BOOTSTRAP
.pg_storage_type
: SSD|HDD, SSD by defaultPGSQL
.PG_BOOTSTRAP
.patroni_log_dir
: patroni log dir, /pg/log
by defaultPGSQL
.PG_BOOTSTRAP
.patroni_ssl_enabled
: secure patroni RestAPI communications with SSL?PGSQL
.PG_BOOTSTRAP
.patroni_username
: patroni rest api usernamePGSQL
.PG_BOOTSTRAP
.patroni_password
: patroni rest api password (IMPORTANT: CHANGE THIS)PGSQL
.PG_BOOTSTRAP
.patroni_citus_db
: citus database managed by patroni, postgres by defaultPGSQL
.PG_BOOTSTRAP
.pg_max_conn
: postgres max connections, auto
will use recommended valuePGSQL
.PG_BOOTSTRAP
.pg_shared_buffer_ratio
: postgres shared buffer memory ratio, 0.25 by default, 0.1~0.4PGSQL
.PG_BOOTSTRAP
.pg_rto
: recovery time objective, ttl to failover, 30s by defaultPGSQL
.PG_BOOTSTRAP
.pg_rpo
: recovery point objective, 1MB data loss at most by defaultPGSQL
.PG_BOOTSTRAP
.pg_pwd_enc
: algorithm for encrypting passwords: md5|scram-sha-256PGSQL
.PG_BOOTSTRAP
.pgbouncer_log_dir
: pgbouncer log dir, /var/log/pgbouncer
by defaultPGSQL
.PG_BOOTSTRAP
.pgbouncer_auth_query
: if enabled, query pg_authid table to retrieve biz users instead of populating userlistPGSQL
.PG_BOOTSTRAP
.pgbouncer_sslmode
: SSL for pgbouncer client: disable|allow|prefer|require|verify-ca|verify-fullPGSQL
.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 defaultPGSQL
.PG_BACKUP
.pgbackrest_method
: pgbackrest backup repo method, local or minioPGSQL
.PG_BACKUP
.pgbackrest_repo
: pgbackrest backup repo configPGSQL
.PG_SERVICE
.pg_service_provider
: dedicate haproxy node group name, or empty string for local nodes by defaultPGSQL
.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 defaultPGSQL
.PG_SERVICE
.pg_vip_address
: vip address in <ipv4>/<mask>
format, require if vip is enabledPGSQL
.PG_SERVICE
.pg_vip_interface
: vip network interface to listen, eth0 by defaultPGSQL
.PG_SERVICE
.pg_dns_suffix
: pgsql cluster dns name suffix, '' by defaultPGSQL
.PG_SERVICE
.pg_dns_target
: auto, primary, vip, none, or ad hoc ipETCD
.etcd_seq
: etcd instance identifier, REQUIREDETCD
.etcd_cluster
: etcd cluster & group name, etcd by defaultETCD
.etcd_safeguard
: prevent purging running etcd instance?ETCD
.etcd_clean
: purging existing etcd during initialization?ETCD
.etcd_data
: etcd data directory, /data/etcd by defaultETCD
.etcd_port
: etcd client port, 2379 by defaultETCD
.etcd_peer_port
: etcd peer port, 2380 by defaultETCD
.etcd_init
: etcd initial cluster state, new or existingETCD
.etcd_election_timeout
: etcd election timeout, 1000ms by defaultETCD
.etcd_heartbeat_interval
: etcd heartbeat interval, 100ms by defaultMINIO
.minio_seq
: minio instance identifier, REQUIREDMINIO
.minio_cluster
: minio cluster name, minio by defaultMINIO
.minio_clean
: cleanup minio during init?, false by defaultMINIO
.minio_user
: minio os user, minio
by defaultMINIO
.minio_node
: minio node name patternMINIO
.minio_data
: minio data dir(s), use {x...y} to specify multi driversMINIO
.minio_domain
: minio external domain name, sss.pigsty
by defaultMINIO
.minio_port
: minio service port, 9000 by defaultMINIO
.minio_admin_port
: minio console port, 9001 by defaultMINIO
.minio_access_key
: root access key, minioadmin
by defaultMINIO
.minio_secret_key
: root secret key, minioadmin
by defaultMINIO
.minio_extra_vars
: extra environment variables for minio serverMINIO
.minio_alias
: alias name for local minio deploymentMINIO
.minio_buckets
: list of minio bucket to be createdMINIO
.minio_users
: list of minio user to be createdRemoved 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 playbooksPGSQL
.PG_INSTALL
.pg_add_repo
: now taken care by node playbooksPGSQL
.PG_IDENTITY
.pg_backup
: not used and conflict with section namePGSQL
.PG_IDENTITY
.pg_preflight_skip
: not used anymore, replace by pg_id
DCS
.dcs_name
: removed due to using etcdDCS
.dcs_servers
: replaced by using ad hoc group etcd
DCS
.dcs_registry
: removed due to using etcdDCS
.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
MD5 (pigsty-v2.0.0.tgz) = 49feb26044cb94852bdeafe4c7c5c17f
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!