Module: PGSQL

Deploy and manage world’s most advanced open-source relational database — PostgreSQL, customizable and production-ready!

The world’s most advanced open-source relational database!
Pigsty brings it to full potential: batteries-included, reliable, observable, maintainable, and scalable! Config | Admin | Playbooks | Dashboards | Parameters

Overview

Learn key topics and concepts about PostgreSQL.

Config

Describe your desired PostgreSQL cluster

Identity Params: Define identity params for a PostgreSQL cluster
Primary Instance: Create single-instance “cluster” with one primary
Replica Instance: Create basic HA cluster with one primary and one replica
Offline Instance: Create special read-only instance for OLAP/ETL/interactive queries
Sync Standby: Enable sync commit to ensure zero data loss
Quorum Commit: Use quorum sync commit for higher consistency level
Standby Cluster: Clone existing cluster and keep in sync (DR cluster)
Delayed Cluster: Clone existing cluster with delayed replay for emergency recovery
Citus Cluster: Define and create Citus distributed database cluster
Major Version: Deploy cluster with different PostgreSQL major version

Admin

Manage your PostgreSQL clusters.

Playbooks

Use idempotent playbooks to materialize your config.

pgsql.yml: Init PostgreSQL cluster or add new replicas.
pgsql-rm.yml: Remove PostgreSQL cluster or specific instance
pgsql-user.yml: Add new biz user to existing PostgreSQL cluster
pgsql-db.yml: Add new biz database to existing PostgreSQL cluster
pgsql-monitor.yml: Monitor remote postgres instance
pgsql-migration.yml: Generate migration manual and scripts

Example: Install PGSQL Module

Example: Remove PGSQL Module

Monitoring

Check PostgreSQL status via Grafana dashboards.

Pigsty has 26 PostgreSQL-related dashboards:

Overview	Cluster	Instance	Database
PGSQL Overview	PGSQL Cluster	PGSQL Instance	PGSQL Database
PGSQL Alert	PGRDS Cluster	PGRDS Instance	PGCAT Database
PGSQL Shard	PGSQL Activity	PGCAT Instance	PGSQL Tables
	PGSQL Replication	PGSQL Persist	PGSQL Table
	PGSQL Service	PGSQL Proxy	PGCAT Table
	PGSQL Databases	PGSQL Pgbouncer	PGSQL Query
	PGSQL Patroni	PGSQL Session	PGCAT Query
	PGSQL PITR	PGSQL Xacts	PGCAT Locks
		PGSQL Exporter	PGCAT Schema

Parameters

Config params for the PGSQL module

PG_ID: Calculate & validate PostgreSQL instance identity
PG_BUSINESS: PostgreSQL biz object definitions
PG_INSTALL: Install PostgreSQL kernel, pkgs & extensions
PG_BOOTSTRAP: Init HA PostgreSQL cluster with Patroni
PG_PROVISION: Create PostgreSQL users, databases & in-db objects
PG_BACKUP: Setup backup repo with pgbackrest
PG_ACCESS: Expose PostgreSQL services, bindVIP (optional), register DNS
PG_MONITOR: Add monitoring for PostgreSQL instance and register to infra
PG_REMOVE: Remove PostgreSQL cluster, instance and related resources

Full Parameter List

Parameter	Section	Type	Level	Description
`pg_mode`	`PG_ID`	enum	C	pgsql cluster mode: pgsql,citus,gpsql
`pg_cluster`	`PG_ID`	string	C	pgsql cluster name, REQUIRED identity param
`pg_seq`	`PG_ID`	int	I	pgsql instance seq number, REQUIRED identity param
`pg_role`	`PG_ID`	enum	I	pgsql role, REQUIRED, could be primary,replica,offline
`pg_instances`	`PG_ID`	dict	I	define multiple pg instances on node in `{port:ins_vars}` format
`pg_upstream`	`PG_ID`	ip	I	repl upstream ip for standby cluster or cascade replica
`pg_shard`	`PG_ID`	string	C	pgsql shard name, optional identity for sharding clusters
`pg_group`	`PG_ID`	int	C	pgsql shard index number, optional identity for sharding clusters
`gp_role`	`PG_ID`	enum	C	greenplum role of this cluster, could be master or segment
`pg_exporters`	`PG_ID`	dict	C	additional pg_exporters to monitor remote postgres instances
`pg_offline_query`	`PG_ID`	bool	I	set true to enable offline query on this instance
`pg_users`	`PG_BUSINESS`	user[]	C	postgres biz users
`pg_databases`	`PG_BUSINESS`	database[]	C	postgres biz databases
`pg_services`	`PG_BUSINESS`	service[]	C	postgres biz services
`pg_hba_rules`	`PG_BUSINESS`	hba[]	C	biz hba rules for postgres
`pgb_hba_rules`	`PG_BUSINESS`	hba[]	C	biz hba rules for pgbouncer
`pg_replication_username`	`PG_BUSINESS`	username	G	postgres replication username, `replicator` by default
`pg_replication_password`	`PG_BUSINESS`	password	G	postgres replication password, `DBUser.Replicator` by default
`pg_admin_username`	`PG_BUSINESS`	username	G	postgres admin username, `dbuser_dba` by default
`pg_admin_password`	`PG_BUSINESS`	password	G	postgres admin password in plain text, `DBUser.DBA` by default
`pg_monitor_username`	`PG_BUSINESS`	username	G	postgres monitor username, `dbuser_monitor` by default
`pg_monitor_password`	`PG_BUSINESS`	password	G	postgres monitor password, `DBUser.Monitor` by default
`pg_dbsu_password`	`PG_BUSINESS`	password	G/C	dbsu password, empty string means no dbsu password by default
`pg_dbsu`	`PG_INSTALL`	username	C	os dbsu name, postgres by default, better not change it
`pg_dbsu_uid`	`PG_INSTALL`	int	C	os dbsu uid and gid, 26 for default postgres users and groups
`pg_dbsu_sudo`	`PG_INSTALL`	enum	C	dbsu sudo privilege, none,limit,all,nopass. limit by default
`pg_dbsu_home`	`PG_INSTALL`	path	C	postgresql home dir, `/var/lib/pgsql` by default
`pg_dbsu_ssh_exchange`	`PG_INSTALL`	bool	C	exchange postgres dbsu ssh key among same pgsql cluster
`pg_version`	`PG_INSTALL`	enum	C	postgres major version to install, 18 by default
`pg_bin_dir`	`PG_INSTALL`	path	C	postgres binary dir, `/usr/pgsql/bin` by default
`pg_log_dir`	`PG_INSTALL`	path	C	postgres log dir, `/pg/log/postgres` by default
`pg_packages`	`PG_INSTALL`	string[]	C	pg pkgs to install, `${pg_version}` will be replaced
`pg_extensions`	`PG_INSTALL`	string[]	C	pg extensions to install, `${pg_version}` will be replaced
`pg_clean`	`PG_BOOTSTRAP`	bool	G/C/A	purge existing postgres during pgsql init? true by default
`pg_data`	`PG_BOOTSTRAP`	path	C	postgres data dir, `/pg/data` by default
`pg_fs_main`	`PG_BOOTSTRAP`	path	C	mountpoint/path for postgres main data, `/data` by default
`pg_fs_bkup`	`PG_BOOTSTRAP`	path	C	mountpoint/path for pg backup data, `/data/backup` by default
`pg_storage_type`	`PG_BOOTSTRAP`	enum	C	storage type for pg main data, SSD,HDD, SSD by default
`pg_dummy_filesize`	`PG_BOOTSTRAP`	size	C	size of `/pg/dummy`, hold 64MB disk space for emergency use
`pg_listen`	`PG_BOOTSTRAP`	ip(s)	C/I	postgres/pgbouncer listen addr, comma separated list
`pg_port`	`PG_BOOTSTRAP`	port	C	postgres listen port, 5432 by default
`pg_localhost`	`PG_BOOTSTRAP`	path	C	postgres unix socket dir for localhost connection
`pg_namespace`	`PG_BOOTSTRAP`	path	C	top level key namespace in etcd, used by patroni & vip
`patroni_enabled`	`PG_BOOTSTRAP`	bool	C	if disabled, no postgres cluster will be created during init
`patroni_mode`	`PG_BOOTSTRAP`	enum	C	patroni working mode: default,pause,remove
`patroni_port`	`PG_BOOTSTRAP`	port	C	patroni listen port, 8008 by default
`patroni_log_dir`	`PG_BOOTSTRAP`	path	C	patroni log dir, `/pg/log/patroni` by default
`patroni_ssl_enabled`	`PG_BOOTSTRAP`	bool	G	secure patroni RestAPI comms with SSL?
`patroni_watchdog_mode`	`PG_BOOTSTRAP`	enum	C	patroni watchdog mode: automatic,required,off. off by default
`patroni_username`	`PG_BOOTSTRAP`	username	C	patroni restapi username, `postgres` by default
`patroni_password`	`PG_BOOTSTRAP`	password	C	patroni restapi password, `Patroni.API` by default
`pg_etcd_password`	`PG_BOOTSTRAP`	password	C	etcd password for this pg cluster, empty to use pg_cluster
`pg_primary_db`	`PG_BOOTSTRAP`	string	C	primary database in this cluster, optional, postgres by default
`pg_parameters`	`PG_BOOTSTRAP`	dict	C	extra params in postgresql.auto.conf
`pg_files`	`PG_BOOTSTRAP`	path[]	C	extra files to copy to postgres data dir
`pg_conf`	`PG_BOOTSTRAP`	enum	C	config template: oltp,olap,crit,tiny. `oltp.yml` by default
`pg_max_conn`	`PG_BOOTSTRAP`	int	C	postgres max connections, `auto` will use recommended value
`pg_shared_buffer_ratio`	`PG_BOOTSTRAP`	float	C	postgres shared buffer mem ratio, 0.25 by default, 0.1~0.4
`pg_io_method`	`PG_BOOTSTRAP`	enum	C	io method for postgres: auto,sync,worker,io_uring, worker by default
`pg_rto`	`PG_BOOTSTRAP`	int	C	recovery time objective in seconds, `30s` by default
`pg_rpo`	`PG_BOOTSTRAP`	int	C	recovery point objective in bytes, `1MiB` at most by default
`pg_libs`	`PG_BOOTSTRAP`	string	C	preloaded libs, `timescaledb,pg_stat_statements,auto_explain` by default
`pg_delay`	`PG_BOOTSTRAP`	interval	I	replication apply delay for standby cluster leader
`pg_checksum`	`PG_BOOTSTRAP`	bool	C	enable data checksum for postgres cluster?
`pg_pwd_enc`	`PG_BOOTSTRAP`	enum	C	password encryption algo: md5,scram-sha-256
`pg_encoding`	`PG_BOOTSTRAP`	enum	C	database cluster encoding, `UTF8` by default
`pg_locale`	`PG_BOOTSTRAP`	enum	C	database cluster locale, `C` by default
`pg_lc_collate`	`PG_BOOTSTRAP`	enum	C	database cluster collate, `C` by default
`pg_lc_ctype`	`PG_BOOTSTRAP`	enum	C	database char type, `C` by default
`pgsodium_key`	`PG_BOOTSTRAP`	string	C	pgsodium key, 64 hex digit, default to sha256(pg_cluster)
`pgsodium_getkey_script`	`PG_BOOTSTRAP`	path	C	pgsodium getkey script path
`pgbouncer_enabled`	`PG_ACCESS`	bool	C	if disabled, pgbouncer will not be launched on pgsql host
`pgbouncer_port`	`PG_ACCESS`	port	C	pgbouncer listen port, 6432 by default
`pgbouncer_log_dir`	`PG_ACCESS`	path	C	pgbouncer log dir, `/pg/log/pgbouncer` by default
`pgbouncer_auth_query`	`PG_ACCESS`	bool	C	query postgres to retrieve unlisted biz users?
`pgbouncer_poolmode`	`PG_ACCESS`	enum	C	pooling mode: transaction,session,statement, transaction by default
`pgbouncer_sslmode`	`PG_ACCESS`	enum	C	pgbouncer client ssl mode, disable by default
`pgbouncer_ignore_param`	`PG_ACCESS`	string[]	C	pgbouncer ignore_startup_parameters list
`pg_provision`	`PG_PROVISION`	bool	C	provision postgres cluster after bootstrap
`pg_init`	`PG_PROVISION`	string	G/C	provision init script for cluster template, `pg-init` by default
`pg_default_roles`	`PG_PROVISION`	role[]	G/C	default roles and users in postgres cluster
`pg_default_privileges`	`PG_PROVISION`	string[]	G/C	default privileges when created by admin user
`pg_default_schemas`	`PG_PROVISION`	string[]	G/C	default schemas to be created
`pg_default_extensions`	`PG_PROVISION`	extension[]	G/C	default extensions to be created
`pg_reload`	`PG_PROVISION`	bool	A	reload postgres after hba changes
`pg_default_hba_rules`	`PG_PROVISION`	hba[]	G/C	postgres default host-based auth rules
`pgb_default_hba_rules`	`PG_PROVISION`	hba[]	G/C	pgbouncer default host-based auth rules
`pgbackrest_enabled`	`PG_BACKUP`	bool	C	enable pgbackrest on pgsql host?
`pgbackrest_clean`	`PG_BACKUP`	bool	C	remove pg backup data during init?
`pgbackrest_log_dir`	`PG_BACKUP`	path	C	pgbackrest log dir, `/pg/log/pgbackrest` by default
`pgbackrest_method`	`PG_BACKUP`	enum	C	pgbackrest repo method: local,minio,etc…
`pgbackrest_init_backup`	`PG_BACKUP`	bool	C	take a full backup after pgbackrest init?
`pgbackrest_repo`	`PG_BACKUP`	dict	G/C	pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
`pg_weight`	`PG_ACCESS`	int	I	relative load balance weight in service, 100 by default, 0-255
`pg_service_provider`	`PG_ACCESS`	enum	G/C	dedicated haproxy node group name, or empty string for local nodes by default
`pg_default_service_dest`	`PG_ACCESS`	enum	G/C	default service dest if svc.dest=‘default’
`pg_default_services`	`PG_ACCESS`	service[]	G/C	postgres default service definitions
`pg_vip_enabled`	`PG_ACCESS`	bool	C	enable L2 VIP for pgsql primary? false by default
`pg_vip_address`	`PG_ACCESS`	cidr4	C	vip addr in `<ipv4>/<mask>` format, required if vip is enabled
`pg_vip_interface`	`PG_ACCESS`	string	C/I	vip network interface to listen, eth0 by default
`pg_dns_suffix`	`PG_ACCESS`	string	C	pgsql dns suffix, ’’ by default
`pg_dns_target`	`PG_ACCESS`	enum	C	auto, primary, vip, none, or ad hoc ip
`pg_exporter_enabled`	`PG_MONITOR`	bool	C	enable pg_exporter on pgsql hosts?
`pg_exporter_config`	`PG_MONITOR`	string	C	pg_exporter config file name
`pg_exporter_cache_ttls`	`PG_MONITOR`	string	C	pg_exporter collector ttl stage in seconds, ‘1,10,60,300’ by default
`pg_exporter_port`	`PG_MONITOR`	port	C	pg_exporter listen port, 9630 by default
`pg_exporter_params`	`PG_MONITOR`	string	C	extra url params for pg_exporter dsn
`pg_exporter_url`	`PG_MONITOR`	pgurl	C	overwrite auto-gen pg dsn if specified
`pg_exporter_auto_discovery`	`PG_MONITOR`	bool	C	enable auto database discovery? enabled by default
`pg_exporter_exclude_database`	`PG_MONITOR`	string	C	csv of database that WILL NOT be monitored during auto-discovery
`pg_exporter_include_database`	`PG_MONITOR`	string	C	csv of database that WILL BE monitored during auto-discovery
`pg_exporter_connect_timeout`	`PG_MONITOR`	int	C	pg_exporter connect timeout in ms, 200 by default
`pg_exporter_options`	`PG_MONITOR`	arg	C	overwrite extra options for pg_exporter
`pgbouncer_exporter_enabled`	`PG_MONITOR`	bool	C	enable pgbouncer_exporter on pgsql hosts?
`pgbouncer_exporter_port`	`PG_MONITOR`	port	C	pgbouncer_exporter listen port, 9631 by default
`pgbouncer_exporter_url`	`PG_MONITOR`	pgurl	C	overwrite auto-gen pgbouncer dsn if specified
`pgbouncer_exporter_options`	`PG_MONITOR`	arg	C	overwrite extra options for pgbouncer_exporter
`pgbackrest_exporter_enabled`	`PG_MONITOR`	bool	C	enable pgbackrest_exporter on pgsql hosts?
`pgbackrest_exporter_port`	`PG_MONITOR`	port	C	pgbackrest_exporter listen port, 9854 by default
`pgbackrest_exporter_options`	`PG_MONITOR`	arg	C	overwrite extra options for pgbackrest_exporter
`pg_safeguard`	`PG_REMOVE`	bool	G/C/A	prevent purging running postgres instance? false by default
`pg_rm_data`	`PG_REMOVE`	bool	G/C/A	remove postgres data during remove? true by default
`pg_rm_backup`	`PG_REMOVE`	bool	G/C/A	remove pgbackrest backup during primary remove? true by default
`pg_rm_pkg`	`PG_REMOVE`	bool	G/C/A	uninstall postgres pkgs during remove? true by default

Tutorials

Tutorials for using/managing PostgreSQL in Pigsty.

Clone an existing PostgreSQL cluster
Create an online standby cluster of existing PostgreSQL cluster
Create a delayed standby cluster of existing PostgreSQL cluster
Monitor an existing postgres instance
Migrate from external PostgreSQL to Pigsty-managed PostgreSQL using logical replication
Use MinIO as centralized pgBackRest backup repo
Use dedicated etcd cluster as PostgreSQL / Patroni DCS
Use dedicated haproxy load balancer cluster to expose PostgreSQL services
Use pg-meta CMDB instead of pigsty.yml as inventory source
Use PostgreSQL as Grafana backend storage
Use PostgreSQL as Prometheus backend storage