Overview

Configuration

the cluster you want

Identity: Parameters used for describing a PostgreSQL cluster
: Define a single instance cluster
Replica: Define a basic HA cluster with one primary & one replica
: Enable synchronous commit to ensure no data loss
Quorum Commit: Use quorum sync commit for an even higher consistency level
: Clone an existing cluster and follow it
Delayed Cluster: Clone an existing cluster for emergency data recovery
: Define a Citus distributed database cluster

: Init HA PostgreSQL clusters or adding new replicas.
pgsql-rm.yml : Remove PostgreSQL cluster, or remove replicas
: Add new business user to existing PostgreSQL cluster
pgsql-db.yml : Add new business database to existing PostgreSQL cluster
: Monitor remote postgres instance with local exporters
pgsql-migration.yml : Generate Migration manual & scripts for existing PostgreSQL

Dashboards

There are 23 default grafana dashboards about PostgreSQL and categorized into 4 levels, check PGSQL Monitor for details.

Admin your existing clusters

Parameters

PG_ID : Calculate & Check Postgres Identity
: Postgres Business Object Definition
PG_INSTALL : Install PGSQL Packages & Extensions
: Init a HA Postgres Cluster with Patroni
PG_PROVISION : Create users, databases, and in-database objects
: Setup backup repo with pgbackrest
PG_SERVICE : Exposing pg service, bind vip and register DNS
: Add Monitor for PGSQL Instance

Parameters

Parameter	Section	Type	Level	Comment
pg_mode		enum	C	pgsql cluster mode: pgsql,citus,gpsql
pg_cluster		string	C	pgsql cluster name, REQUIRED identity parameter
pg_seq		int	I	pgsql instance seq number, REQUIRED identity parameter
pg_role		enum	I	pgsql role, REQUIRED, could be primary,replica,offline
pg_instances		dict	I	define multiple pg instances on node in format
pg_upstream		ip	I	repl upstream ip addr for standby cluster or cascade replica
pg_shard		string	C	pgsql shard name, optional identity for sharding clusters
pg_group		int	C	pgsql shard index number, optional identity for sharding clusters
gp_role		enum	C	greenplum role of this cluster, could be master or segment
pg_exporters		dict	C	additional pg_exporters to monitor remote postgres instances
pg_offline_query		bool	I	set to true to enable offline query on this instance
pg_users		user[]	C	postgres business users
pg_databases		database[]	C	postgres business databases
pg_services		service[]	C	postgres business services
pg_hba_rules		hba[]	C	business hba rules for postgres
pgb_hba_rules		hba[]	C	business hba rules for pgbouncer
pg_replication_username		username	G	postgres replication username, `replicator` by default
pg_replication_password		password	G	postgres replication password, `DBUser.Replicator` by default
pg_admin_username		username	G	postgres admin username, `dbuser_dba` by default
pg_admin_password		password	G	postgres admin password in plain text, `DBUser.DBA` by default
pg_monitor_username		username	G	postgres monitor username, `dbuser_monitor` by default
pg_monitor_password		password	G	postgres monitor password, `DBUser.Monitor` by default
pg_dbsu_password		password	G/C	dbsu password, empty string means no dbsu password by default
pg_dbsu		username	C	os dbsu name, postgres by default, better not change it
pg_dbsu_uid		int	C	os dbsu uid and gid, 26 for default postgres users and groups
pg_dbsu_sudo		enum	C	dbsu sudo privilege, none,limit,all,nopass. limit by default
pg_dbsu_home		path	C	postgresql home directory, `/var/lib/pgsql` by default
pg_dbsu_ssh_exchange		bool	C	exchange postgres dbsu ssh key among same pgsql cluster
pg_version		enum	C	postgres major version to be installed, 15 by default
pg_bin_dir		path	C	postgres binary dir, `/usr/pgsql/bin` by default
pg_log_dir		path	C	postgres log dir, `/pg/log/postgres` by default
pg_packages		string[]	C	pg packages to be installed, `${pg_version}` will be replaced
pg_extensions		string[]	C	pg extensions to be installed, will be replaced
pg_safeguard		bool	G/C/A	prevent purging running postgres instance? false by default
pg_clean		bool	G/C/A	purging existing postgres during pgsql init? true by default
pg_data		path	C	postgres data directory, `/pg/data` by default
pg_fs_main		path	C	mountpoint/path for postgres main data, `/data` by default
pg_fs_bkup		path	C	mountpoint/path for pg backup data, `/data/backup` by default
pg_storage_type		enum	C	storage type for pg main data, SSD,HDD, SSD by default
pg_dummy_filesize		size	C	size of `/pg/dummy`, hold 64MB disk space for emergency use
pg_listen		ip	C	postgres listen address, `0.0.0.0` (all ipv4 addr) by default
pg_port		port	C	postgres listen port, 5432 by default
pg_localhost		path	C	postgres unix socket dir for localhost connection
pg_namespace		path	C	top level key namespace in etcd, used by patroni & vip
patroni_enabled		bool	C	if disabled, no postgres cluster will be created during init
patroni_mode		enum	C	patroni working mode: default,pause,remove
patroni_port		port	C	patroni listen port, 8008 by default
patroni_log_dir		path	C	patroni log dir, `/pg/log/patroni` by default
patroni_ssl_enabled		bool	G	secure patroni RestAPI communications with SSL?
patroni_watchdog_mode		enum	C	patroni watchdog mode: automatic,required,off. off by default
patroni_username		username	C	patroni restapi username, `postgres` by default
patroni_password		password	C	patroni restapi password, `Patroni.API` by default
pg_conf		enum	C	config template: oltp,olap,crit,tiny. `oltp.yml` by default
pg_max_conn		int	C	postgres max connections, `auto` will use recommended value
pg_shared_buffer_ratio		float	C	postgres shared buffer memory ratio, 0.25 by default, 0.1~0.4
pg_rto		int	C	recovery time objective in seconds, by default
pg_rpo		int	C	recovery point objective in bytes, `1MiB` at most by default
pg_libs		string	C	preloaded libraries, `pg_stat_statements,auto_explain` by default
pg_delay		interval	I	replication apply delay for standby cluster leader
pg_checksum		bool	C	enable data checksum for postgres cluster?
pg_pwd_enc		enum	C	passwords encryption algorithm: md5,scram-sha-256
pg_encoding		enum	C	database cluster encoding, `UTF8` by default
pg_locale		enum	C	database cluster local, `C` by default
pg_lc_collate		enum	C	database cluster collate, `C` by default
pg_lc_ctype		enum	C	database character type, `en_US.UTF8` by default
pgbouncer_enabled		bool	C	if disabled, pgbouncer will not be launched on pgsql host
pgbouncer_port		port	C	pgbouncer listen port, 6432 by default
pgbouncer_log_dir		path	C	pgbouncer log dir, `/pg/log/pgbouncer` by default
pgbouncer_auth_query		bool	C	query postgres to retrieve unlisted business users?
pgbouncer_poolmode		enum	C	pooling mode: transaction,session,statement, transaction by default
pgbouncer_sslmode		enum	C	pgbouncer client ssl mode, disable by default
pg_provision		bool	C	provision postgres cluster after bootstrap
pg_init		string	G/C	provision init script for cluster template, `pg-init` by default
pg_default_roles		role[]	G/C	default roles and users in postgres cluster
pg_default_privileges		string[]	G/C	default privileges when created by admin user
pg_default_schemas		string[]	G/C	default schemas to be created
pg_default_extensions		extension[]	G/C	default extensions to be created
pg_reload		bool	A	reload postgres after hba changes
pg_default_hba_rules		hba[]	G/C	postgres default host-based authentication rules
pgb_default_hba_rules		hba[]	G/C	pgbouncer default host-based authentication rules
pgbackrest_enabled		bool	C	enable pgbackrest on pgsql host?
pgbackrest_clean		bool	C	remove pg backup data during init?
pgbackrest_log_dir		path	C	pgbackrest log dir, `/pg/log/pgbackrest` by default
pgbackrest_method		enum	C	pgbackrest repo method: local,minio,etc…
pgbackrest_repo		dict	G/C	pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
	PG_SERVICE	int	I	relative load balance weight in service, 100 by default, 0-255
	PG_SERVICE	enum	G/C	dedicate haproxy node group name, or empty string for local nodes by default
	PG_SERVICE	enum	G/C	default service destination if svc.dest=‘default’
	PG_SERVICE	service[]	G/C	postgres default service definitions
	PG_SERVICE	bool	C	enable a l2 vip for pgsql primary? false by default
	PG_SERVICE	cidr4	C	vip address in `<ipv4>/<mask>` format, require if vip is enabled
	PG_SERVICE	string	C/I	vip network interface to listen, eth0 by default
	PG_SERVICE	string	C	pgsql dns suffix, ’’ by default
	PG_SERVICE	enum	C	auto, primary, vip, none, or ad hoc ip
	PG_EXPORTER	bool	C	enable pg_exporter on pgsql hosts?
	PG_EXPORTER	string	C	pg_exporter configuration file name
	PG_EXPORTER	string	C	pg_exporter collector ttl stage in seconds, ‘1,10,60,300’ by default
	PG_EXPORTER	port	C	pg_exporter listen port, 9630 by default
	PG_EXPORTER	string	C	extra url parameters for pg_exporter dsn
	PG_EXPORTER	pgurl	C	overwrite auto-generate pg dsn if specified
	PG_EXPORTER	bool	C	enable auto database discovery? enabled by default
	PG_EXPORTER	string	C	csv of database that WILL NOT be monitored during auto-discovery
	PG_EXPORTER	string	C	csv of database that WILL BE monitored during auto-discovery
	PG_EXPORTER	int	C	pg_exporter connect timeout in ms, 200 by default
	PG_EXPORTER	arg	C	overwrite extra options for pg_exporter
	PG_EXPORTER	bool	C	enable pgbouncer_exporter on pgsql hosts?
	PG_EXPORTER	port	C	pgbouncer_exporter listen port, 9631 by default
	PG_EXPORTER	pgurl	C	overwrite auto-generate pgbouncer dsn if specified
	PG_EXPORTER	arg	C	overwrite extra options for pgbouncer_exporter

Fork an existing PostgreSQL cluster.
Create a standby cluster of an existing PostgreSQL cluster.
Create a delayed cluster of another pgsql cluster?
Monitoring an existing postgres instance?
Migration from an external PostgreSQL with logical replication?
Use MinIO as a central pgBackRest repo.
Use dedicate etcd cluster for DCS?
Use dedicated haproxy for exposing PostgreSQL service.
Deploy a multi-node MinIO cluster?
Use CMDB instead of Config as inventory.
Use PostgreSQL as grafana backend storage ?

Last modified 2023-02-27: refresh en docs to v2.0 (e82b371)