Module: PGSQL

The most advanced open-source relational database in the world with HA, PITR, IaC and more!
Module:
Categories:

The most advanced open-source relational database in the world!

With battery-included observability, reliability, and maintainability powered by Pigsty

Concept

Overview of PostgreSQL in Pigsty

Configuration

Describe the cluster you want

  • Identity: Parameters used for describing a PostgreSQL cluster
  • Primary: Define a single instance cluster
  • Replica: Define a basic HA cluster with one primary & one replica
  • Offline: Define a dedicated instance for OLAP/ETL/Interactive queries.
  • Sync Standby: Enable synchronous commit to ensure no data loss
  • Quorum Commit: Use quorum sync commit for an even higher consistency level
  • Standby Cluster: Clone an existing cluster and follow it
  • Delayed Cluster: Clone an existing cluster for emergency data recovery
  • Citus Cluster: Define a Citus distributed database cluster
  • Major Version: Define a PostgreSQL cluster with specific major version

Administration

Admin your existing clusters

Playbook

Materialize the cluster with idempotent playbooks

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

asciicast

Example: Remove PGSQL module

asciicast

Dashboard

There are 26 default grafana dashboards about PostgreSQL and categorized into 4 levels. Check Dashboards for details.

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

Parameter

API Reference for PGSQL module:

  • PG_ID : Calculate & Check Postgres Identity
  • PG_BUSINESS : Postgres Business Object Definition
  • PG_INSTALL : Install PGSQL Packages & Extensions
  • PG_BOOTSTRAP : Init a HA Postgres Cluster with Patroni
  • PG_PROVISION : Create users, databases, and in-database objects
  • PG_BACKUP : Setup backup repo with pgbackrest
  • PG_SERVICE : Exposing pg service, bind vip and register DNS
  • PG_EXPORTER : Add Monitor for PGSQL Instance
Parameters
Parameter Section Type Level Comment
pg_mode PG_ID enum C pgsql cluster mode: pgsql,citus,gpsql
pg_cluster PG_ID string C pgsql cluster name, REQUIRED identity parameter
pg_seq PG_ID int I pgsql instance seq number, REQUIRED identity parameter
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 addr 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 to true to enable offline query on this instance
pg_users PG_BUSINESS user[] C postgres business users
pg_databases PG_BUSINESS database[] C postgres business databases
pg_services PG_BUSINESS service[] C postgres business services
pg_hba_rules PG_BUSINESS hba[] C business hba rules for postgres
pgb_hba_rules PG_BUSINESS hba[] C business 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 directory, /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 be installed, 16 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 packages to be installed, ${pg_version} will be replaced
pg_extensions PG_INSTALL string[] C pg extensions to be installed, ${pg_version} will be replaced
pg_safeguard PG_BOOTSTRAP bool G/C/A prevent purging running postgres instance? false by default
pg_clean PG_BOOTSTRAP bool G/C/A purging existing postgres during pgsql init? true by default
pg_data PG_BOOTSTRAP path C postgres data directory, /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 addresses, 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 communications 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_primary_db PG_BOOTSTRAP string C primary database name, used by citus,etc… ,postgres by default
pg_parameters PG_BOOTSTRAP dict C extra parameters in postgresql.auto.conf
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 memory ratio, 0.25 by default, 0.1~0.4
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 libraries, 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 passwords encryption algorithm: md5,scram-sha-256
pg_encoding PG_BOOTSTRAP enum C database cluster encoding, UTF8 by default
pg_locale PG_BOOTSTRAP enum C database cluster local, C by default
pg_lc_collate PG_BOOTSTRAP enum C database cluster collate, C by default
pg_lc_ctype PG_BOOTSTRAP enum C database character type, en_US.UTF8 by default
pgbouncer_enabled PG_BOOTSTRAP bool C if disabled, pgbouncer will not be launched on pgsql host
pgbouncer_port PG_BOOTSTRAP port C pgbouncer listen port, 6432 by default
pgbouncer_log_dir PG_BOOTSTRAP path C pgbouncer log dir, /pg/log/pgbouncer by default
pgbouncer_auth_query PG_BOOTSTRAP bool C query postgres to retrieve unlisted business users?
pgbouncer_poolmode PG_BOOTSTRAP enum C pooling mode: transaction,session,statement, transaction by default
pgbouncer_sslmode PG_BOOTSTRAP enum C pgbouncer client ssl mode, disable by default
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 authentication rules
pgb_default_hba_rules PG_PROVISION hba[] G/C pgbouncer default host-based authentication 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_repo PG_BACKUP dict G/C pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
pg_weight PG_SERVICE int I relative load balance weight in service, 100 by default, 0-255
pg_service_provider PG_SERVICE enum G/C dedicate haproxy node group name, or empty string for local nodes by default
pg_default_service_dest PG_SERVICE enum G/C default service destination if svc.dest=‘default’
pg_default_services PG_SERVICE service[] G/C postgres default service definitions
pg_vip_enabled PG_SERVICE bool C enable a l2 vip for pgsql primary? false by default
pg_vip_address PG_SERVICE cidr4 C vip address in <ipv4>/<mask> format, require if vip is enabled
pg_vip_interface PG_SERVICE string C/I vip network interface to listen, eth0 by default
pg_dns_suffix PG_SERVICE string C pgsql dns suffix, ’’ by default
pg_dns_target PG_SERVICE enum C auto, primary, vip, none, or ad hoc ip
pg_exporter_enabled PG_EXPORTER bool C enable pg_exporter on pgsql hosts?
pg_exporter_config PG_EXPORTER string C pg_exporter configuration file name
pg_exporter_cache_ttls PG_EXPORTER string C pg_exporter collector ttl stage in seconds, ‘1,10,60,300’ by default
pg_exporter_port PG_EXPORTER port C pg_exporter listen port, 9630 by default
pg_exporter_params PG_EXPORTER string C extra url parameters for pg_exporter dsn
pg_exporter_url PG_EXPORTER pgurl C overwrite auto-generate pg dsn if specified
pg_exporter_auto_discovery PG_EXPORTER bool C enable auto database discovery? enabled by default
pg_exporter_exclude_database PG_EXPORTER string C csv of database that WILL NOT be monitored during auto-discovery
pg_exporter_include_database PG_EXPORTER string C csv of database that WILL BE monitored during auto-discovery
pg_exporter_connect_timeout PG_EXPORTER int C pg_exporter connect timeout in ms, 200 by default
pg_exporter_options PG_EXPORTER arg C overwrite extra options for pg_exporter
pgbouncer_exporter_enabled PG_EXPORTER bool C enable pgbouncer_exporter on pgsql hosts?
pgbouncer_exporter_port PG_EXPORTER port C pgbouncer_exporter listen port, 9631 by default
pgbouncer_exporter_url PG_EXPORTER pgurl C overwrite auto-generate pgbouncer dsn if specified
pgbouncer_exporter_options PG_EXPORTER arg C overwrite extra options for pgbouncer_exporter

Tutorials

  • 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 ?
  • Use PostgreSQL as prometheus backend storage ?
Architecture

PostgreSQL cluster architectures and implmenetation details.

Users

Define business users & roles in PostgreSQL, which is the object created by SQL CREATE USER/ROLE

Databases

Define business databases in PostgreSQL, which is the object create by SQL CREATE DATABASE

Services

Define and create new services, and expose them via haproxy

Extensions

Define, Create, Install, Enable Extensions in Pigsty

Authentication

Host-Based Authentication in Pigsty, how to manage HBA rules in Pigsty?

Configuration

Configure your PostgreSQL cluster & instances according to your needs

Playbook

How to manage PostgreSQL cluster with ansible playbooks

Administration

Administration standard operation procedures to manage PostgreSQL clusters in production environment.

Access Control

Built-in roles system, and battery-included access control model in Pigsty.

Backup & PITR

How to perform base backup & PITR with pgBackRest?

Migration

How to migrate existing postgres into Pigsty-managed cluster with mimial downtime? The blue-green online migration playbook

Monitoring

How PostgreSQL monitoring works, and how to monitor remote (existing) PostgreSQL instances?

Dashboards

Grafana dashboards provided by Pigsty

Metrics

Pigsty PGSQL module metric list

FAQ

Pigsty PGSQL module frequently asked questions


Last modified 2024-08-03: add new pg_primary_db pg_parameters (b39ee445)