Skip to content

PGSQL

Jump to bottom
Feng Ruohang edited this page Feb 11, 2023 · 5 revisions

PGSQL

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

Playbooks

Materialize the cluster you described

  • pgsql.yml : Init HA PostgreSQL clusters or adding 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 postgres instance with local exporters
  • pgsql-migration.yml : Generate Migration manual & scripts for existing PostgreSQL

Administration

Admin your existing clusters

Parameters

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_VIP : Bind an optional VIP on primary
  • PG_DNS : Register DNS Record to Infra
  • PG_EXPORTER : Add Monitor for PG & PGBOUNCER
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 G set to true to enable offline query on this instance
pg_weight PG_ID int G relative load balance weight in service, 100 by default, 0-255
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 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, 15 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 C postgres listen address, 0.0.0.0 (all ipv4 addr) by default
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_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_shmem_ratio PG_BOOTSTRAP float C postgres shared 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, 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
pg_default_service_dest PG_PROVISION enum G/C default service destination if svc.dest='default'
pg_default_services PG_PROVISION service[] G/C postgres default service definitions
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_vip_enabled PG_VIP bool C enable a l2 vip for pgsql primary? false by default
pg_vip_address PG_VIP cidr4 C vip address in <ipv4>/<mask> format, require if vip is enabled
pg_vip_interface PG_VIP string C/I vip network interface to listen, eth0 by default
pg_dns_suffix PG_DNS string C pgsql dns suffix, '' by default
pg_dns_target PG_DNS 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 ?
Clone this wiki locally