This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Module: PGSQL

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

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
patroni_citus_db PG_BOOTSTRAP string C citus database managed by patroni, postgres 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_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 ?

1 - Architecture

PostgreSQL cluster architectures and implmenetation details.

Component Overview

Here is how PostgreSQL module components and their interactions. From top to bottom:

  • Cluster DNS is resolved by DNSMASQ on infra nodes
  • Cluster VIP is manged by vip-manager, which will bind to cluster primary.
    • vip-manager will acquire cluster leader info written by patroni from etcd cluster directly
  • Cluster services are exposed by Haproxy on nodes, services are distinguished by node ports (543x).
    • Haproxy port 9101: monitoring metrics & stats & admin page
    • Haproxy port 5433: default service that routes to primary pgbouncer: primary
    • Haproxy port 5434: default service that routes to replica pgbouncer: replica
    • Haproxy port 5436: default service that routes to primary postgres: default
    • Haproxy port 5438: default service that routeroutesto offline postgres: offline
    • HAProxy will route traffic based on health check information provided by patroni.
  • Pgbouncer is a connection pool middleware that buffers connections, exposes extra metrics, and brings extra flexibility @ port 6432
    • Pgbouncer is stateless and deployed with the Postgres server in a 1:1 manner through a local unix socket.
    • Production traffic (Primary/Replica) will go through pgbouncer by default (can be skipped by pg_default_service_dest )
    • Default/Offline service will always bypass pgbouncer and connect to target Postgres directly.
  • Postgres provides relational database services @ port 5432
    • Install PGSQL module on multiple nodes will automatically form a HA cluster based on streaming replication
    • PostgreSQL is supervised by patroni by default.
  • Patroni will supervise PostgreSQL server @ port 8008 by default
    • Patroni spawn postgres servers as the child process
    • Patroni uses etcd as DCS: config storage, failure detection, and leader election.
    • Patroni will provide Postgres information through a health check. Which is used by HAProxy
    • Patroni metrics will be scraped by prometheus on infra nodes
  • PG Exporter will expose postgres metrics @ port 9630
    • PostgreSQL’s metrics will be scraped by prometheus on infra nodes
  • Pgbouncer Exporter will expose pgbouncer metrics @ port 9631
    • Pgbouncer’s metrics will be scraped by prometheus on infra nodes
  • pgBackRest will work on the local repo by default (pgbackrest_method)
    • If local (default) is used as the backup repo, pgBackRest will create local repo under the primary’s pg_fs_bkup
    • If minio is used as the backup repo, pgBackRest will create the repo on the dedicated MinIO cluster in pgbackrest_repo.minio
  • Postgres-related logs (postgres,pgbouncer,patroni,pgbackrest) are exposed by promtail @ port 9080
    • Promtail will send logs to Loki on infra nodes

pigsty-arch.jpg

2 - Users

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

In this context, the User refers to objects created by SQL CREATE USER/ROLE.


Define User

There are two parameters related to users:

  • pg_users : Define business users & roles at cluster level
  • pg_default_roles : Define system-wide roles & global users at global level

They are both arrays of user/role definition. You can define multiple users/roles in one cluster.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - {name: dbuser_meta     ,password: DBUser.Meta     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: pigsty admin user }
      - {name: dbuser_view     ,password: DBUser.Viewer   ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
      - {name: dbuser_grafana  ,password: DBUser.Grafana  ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for grafana database    }
      - {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for bytebase database   }
      - {name: dbuser_kong     ,password: DBUser.Kong     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for kong api gateway    }
      - {name: dbuser_gitea    ,password: DBUser.Gitea    ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for gitea service       }
      - {name: dbuser_wiki     ,password: DBUser.Wiki     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for wiki.js service     }
      - {name: dbuser_noco     ,password: DBUser.Noco     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for nocodb service      }

And each user definition may look like:

- name: dbuser_meta               # REQUIRED, `name` is the only mandatory field of a user definition
  password: DBUser.Meta           # optional, password, can be a scram-sha-256 hash string or plain text
  login: true                     # optional, can log in, true by default  (new biz ROLE should be false)
  superuser: false                # optional, is superuser? false by default
  createdb: false                 # optional, can create database? false by default
  createrole: false               # optional, can create role? false by default
  inherit: true                   # optional, can this role use inherited privileges? true by default
  replication: false              # optional, can this role do replication? false by default
  bypassrls: false                # optional, can this role bypass row level security? false by default
  pgbouncer: true                 # optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)
  connlimit: -1                   # optional, user connection limit, default -1 disable limit
  expire_in: 3650                 # optional, now + n days when this role is expired (OVERWRITE expire_at)
  expire_at: '2030-12-31'         # optional, YYYY-MM-DD 'timestamp' when this role is expired  (OVERWRITTEN by expire_in)
  comment: pigsty admin user      # optional, comment string for this user/role
  roles: [dbrole_admin]           # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}
  parameters: {}                  # optional, role level parameters with `ALTER ROLE SET`
  pool_mode: transaction          # optional, pgbouncer pool mode at user level, transaction by default
  pool_connlimit: -1              # optional, max database connections at user level, default -1 disable limit
  search_path: public             # key value config parameters according to postgresql documentation (e.g: use pigsty as default search_path)
  • The only required field is name, which should be a valid & unique username in PostgreSQL.
  • Roles don’t need a password, while it could be necessary for a login-able user.
  • The password can be plain text or a scram-sha-256 / md5 hash string.
  • User/Role are created one by one in array order. So make sure role/group definition is ahead of its members
  • login, superuser, createdb, createrole, inherit, replication, bypassrls are boolean flags
  • pgbouncer is disabled by default. To add a business user to the pgbouncer user-list, you should set it to true explicitly.

ACL System

Pigsty has a battery-included ACL system, which can be easily used by assigning roles to users:

  • dbrole_readonly : The role for global read-only access
  • dbrole_readwrite : The role for global read-write access
  • dbrole_admin : The role for object creation
  • dbrole_offline : The role for restricted read-only access (offline instance)

If you wish to re-design your ACL system, check the following parameters & templates.


Create User

Users & Roles defined in pg_default_roles and pg_users will be automatically created one by one during cluster bootstrap.

If you wish to create user on an existing cluster, the bin/pgsql-user util can be used.

Add new user definition to all.children.<cls>.pg_users, and create that database with:

bin/pgsql-user <cls> <username>    # pgsql-user.yml -l <cls> -e username=<username>

The playbook is idempotent, so it’s ok to run this multiple times on the existing cluster.


Pgbouncer User

Pgbouncer is enabled by default and serves as a connection pool middleware, and its user is managed by default.

Pigsty will add all users in pg_users with pgbouncer: true flag to the pgbouncer userlist by default.

The user is listed in /etc/pgbouncer/userlist.txt:

"postgres" ""
"dbuser_wiki" "SCRAM-SHA-256$4096:+77dyhrPeFDT/TptHs7/7Q==$KeatuohpKIYzHPCt/tqBu85vI11o9mar/by0hHYM2W8=:X9gig4JtjoS8Y/o1vQsIX/gY1Fns8ynTXkbWOjUfbRQ="
"dbuser_view" "SCRAM-SHA-256$4096:DFoZHU/DXsHL8MJ8regdEw==$gx9sUGgpVpdSM4o6A2R9PKAUkAsRPLhLoBDLBUYtKS0=:MujSgKe6rxcIUMv4GnyXJmV0YNbf39uFRZv724+X1FE="
"dbuser_monitor" "SCRAM-SHA-256$4096:fwU97ZMO/KR0ScHO5+UuBg==$CrNsmGrx1DkIGrtrD1Wjexb/aygzqQdirTO1oBZROPY=:L8+dJ+fqlMQh7y4PmVR/gbAOvYWOr+KINjeMZ8LlFww="
"dbuser_meta" "SCRAM-SHA-256$4096:leB2RQPcw1OIiRnPnOMUEg==$eyC+NIMKeoTxshJu314+BmbMFpCcspzI3UFZ1RYfNyU=:fJgXcykVPvOfro2MWNkl5q38oz21nSl1dTtM65uYR1Q="
"dbuser_kong" "SCRAM-SHA-256$4096:bK8sLXIieMwFDz67/0dqXQ==$P/tCRgyKx9MC9LH3ErnKsnlOqgNd/nn2RyvThyiK6e4=:CDM8QZNHBdPf97ztusgnE7olaKDNHBN0WeAbP/nzu5A="
"dbuser_grafana" "SCRAM-SHA-256$4096:HjLdGaGmeIAGdWyn2gDt/Q==$jgoyOB8ugoce+Wqjr0EwFf8NaIEMtiTuQTg1iEJs9BM=:ed4HUFqLyB4YpRr+y25FBT7KnlFDnan6JPVT9imxzA4="
"dbuser_gitea" "SCRAM-SHA-256$4096:l1DBGCc4dtircZ8O8Fbzkw==$tpmGwgLuWPDog8IEKdsaDGtiPAxD16z09slvu+rHE74=:pYuFOSDuWSofpD9OZhG7oWvyAR0PQjJBffgHZLpLHds="
"dbuser_dba" "SCRAM-SHA-256$4096:zH8niABU7xmtblVUo2QFew==$Zj7/pq+ICZx7fDcXikiN7GLqkKFA+X5NsvAX6CMshF0=:pqevR2WpizjRecPIQjMZOm+Ap+x0kgPL2Iv5zHZs0+g="
"dbuser_bytebase" "SCRAM-SHA-256$4096:OMoTM9Zf8QcCCMD0svK5gg==$kMchqbf4iLK1U67pVOfGrERa/fY818AwqfBPhsTShNQ=:6HqWteN+AadrUnrgC0byr5A72noqnPugItQjOLFw0Wk="

And user level parameters are listed in /etc/pgbouncer/useropts.txt:

dbuser_dba                  = pool_mode=session max_user_connections=16
dbuser_monitor              = pool_mode=session max_user_connections=8

The userlist & useropts file will be updated automatically when you add a new user with pgsql-user util, or pgsql-user.yml playbook.

You can use pgbouncer_auth_query to simplify pgbouncer user management (with the cost of reliability & security).

3 - Databases

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

In this context, Database refers to the object created by SQL CREATE DATABASE.

A PostgreSQL server can serve multiple databases simultaneously. And you can customize each database with Pigsty API.


Define Database

Business databases are defined by pg_databases, which is a cluster-level parameter.

For example, the default meta database is defined in the pg-meta cluster:

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: postgis, schema: public}, {name: timescaledb}]}
      - { name: grafana  ,owner: dbuser_grafana  ,revokeconn: true ,comment: grafana primary database }
      - { name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
      - { name: kong     ,owner: dbuser_kong     ,revokeconn: true ,comment: kong the api gateway database }
      - { name: gitea    ,owner: dbuser_gitea    ,revokeconn: true ,comment: gitea meta database }
      - { name: wiki     ,owner: dbuser_wiki     ,revokeconn: true ,comment: wiki meta database }
      - { name: noco     ,owner: dbuser_noco     ,revokeconn: true ,comment: nocodb database }

Each database definition is a dict with the following fields:

- name: meta                      # REQUIRED, `name` is the only mandatory field of a database definition
  baseline: cmdb.sql              # optional, database sql baseline path, (relative path among ansible search path, e.g files/)
  pgbouncer: true                 # optional, add this database to pgbouncer database list? true by default
  schemas: [pigsty]               # optional, additional schemas to be created, array of schema names
  extensions:                     # optional, additional extensions to be installed: array of `{name[,schema]}`
    - { name: postgis , schema: public }
    - { name: timescaledb }
  comment: pigsty meta database   # optional, comment string for this database
  owner: postgres                 # optional, database owner, postgres by default
  template: template1             # optional, which template to use, template1 by default
  encoding: UTF8                  # optional, database encoding, UTF8 by default. (MUST same as template database)
  locale: C                       # optional, database locale, C by default.  (MUST same as template database)
  lc_collate: C                   # optional, database collate, C by default. (MUST same as template database)
  lc_ctype: C                     # optional, database ctype, C by default.   (MUST same as template database)
  tablespace: pg_default          # optional, default tablespace, 'pg_default' by default.
  allowconn: true                 # optional, allow connection, true by default. false will disable connect at all
  revokeconn: false               # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)
  register_datasource: true       # optional, register this database to grafana datasources? true by default
  connlimit: -1                   # optional, database connection limit, default -1 disable limit
  pool_auth_user: dbuser_meta     # optional, all connection to this pgbouncer database will be authenticated by this user
  pool_mode: transaction          # optional, pgbouncer pool mode at database level, default transaction
  pool_size: 64                   # optional, pgbouncer pool size at database level, default 64
  pool_size_reserve: 32           # optional, pgbouncer pool size reserve at database level, default 32
  pool_size_min: 0                # optional, pgbouncer pool size min at database level, default 0
  pool_max_db_conn: 100           # optional, max database connections at database level, default 100

The only required field is name, which should be a valid and unique database name in PostgreSQL.

Newly created databases are forked from template1 database by default. which is customized by PG_PROVISION during cluster bootstrap.

Check ACL: Database Privilege for details about database-level privilege.


Create Database

Databases defined in pg_databases will be automatically created during cluster bootstrap.

If you wish to create database on an existing cluster, the bin/pgsql-db util can be used.

Add new database definition to all.children.<cls>.pg_databases, and create that database with:

bin/pgsql-db <cls> <dbname>    # pgsql-db.yml -l <cls> -e dbname=<dbname>

It’s usually not a good idea to execute this on the existing database again when a baseline script is used.

If you are using the default pgbouncer as the proxy middleware, YOU MUST create the new database with pgsql-db util or pgsql-db.yml playbook. Otherwise, the new database will not be added to the pgbouncer database list.

Remember, if your database definition has a non-trivial owner (dbsu postgres by default ), make sure the owner user exists. That is to say, always create the user before the database.


Pgbouncer Database

Pgbouncer is enabled by default and serves as a connection pool middleware.

Pigsty will add all databases in pg_databases to the pgbouncer database list by default. You can disable the pgbouncer proxy for a specific database by setting pgbouncer: false in the database definition.

The database is listed in /etc/pgbouncer/database.txt, with extra database-level parameters such as:

meta                        = host=/var/run/postgresql mode=session
grafana                     = host=/var/run/postgresql mode=transaction
bytebase                    = host=/var/run/postgresql auth_user=dbuser_meta
kong                        = host=/var/run/postgresql pool_size=32 reserve_pool=64
gitea                       = host=/var/run/postgresql min_pool_size=10
wiki                        = host=/var/run/postgresql
noco                        = host=/var/run/postgresql
mongo                       = host=/var/run/postgresql

The Pgbouncer database list will be updated when create database with Pigsty util & playbook.

To access pgbouncer administration functionality, you can use the pgb alias as dbsu.

There’s a util function defined in /etc/profile.d/pg-alias.sh, allowing you to reroute pgbouncer database traffic to a new host quickly, which can be used during zero-downtime migration.

# route pgbouncer traffic to another cluster member
function pgb-route(){
  local ip=${1-'\/var\/run\/postgresql'}
  sed -ie "s/host=[^[:space:]]\+/host=${ip}/g" /etc/pgbouncer/pgbouncer.ini
  cat /etc/pgbouncer/pgbouncer.ini
}

4 - Services

Define and create new services, and expose them via haproxy

Service Implementation

In Pigsty, services are implemented using haproxy on nodes, differentiated by different ports on the host node.

Every node has Haproxy enabled to expose services. From the database perspective, nodes in the cluster may be primary or replicas, but from the service perspective, all nodes are the same. This means even if you access a replica node, as long as you use the correct service port, you can still use the primary’s read-write service. This design seals the complexity: as long as you can access any instance on the PostgreSQL cluster, you can fully access all services.

This design is akin to the NodePort service in Kubernetes. Similarly, in Pigsty, every service includes these two core elements:

  1. Access endpoints exposed via NodePort (port number, from where to access?)
  2. Target instances chosen through Selectors (list of instances, who will handle it?)

The boundary of Pigsty’s service delivery stops at the cluster’s HAProxy. Users can access these load balancers in various ways. Please refer to Access Service.

All services are declared through configuration files. For instance, the default PostgreSQL service is defined by the pg_default_services parameter:

- { name: primary ,port: 5433 ,dest: default  ,check: /primary   ,selector: "[]" }
- { name: replica ,port: 5434 ,dest: default  ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" }
- { name: default ,port: 5436 ,dest: postgres ,check: /primary   ,selector: "[]" }
- { name: offline ,port: 5438 ,dest: postgres ,check: /replica   ,selector: "[? pg_role == `offline` || pg_offline_query ]" , backup: "[? pg_role == `replica` && !pg_offline_query]"}

You can also define new service in pg_services. And pg_default_servicespg_services are both array of Service Definition.


Define Service

The default services are defined in pg_default_services.

While you can define your extra PostgreSQL services with pg_services @ the global or cluster level.

These two parameters are both arrays of service objects. Each service definition will be rendered as a haproxy config in /etc/haproxy/<svcname>.cfg, check service.j2 for details.

Here is an example of an extra service definition: standby

- name: standby                   # required, service name, the actual svc name will be prefixed with `pg_cluster`, e.g: pg-meta-standby
  port: 5435                      # required, service exposed port (work as kubernetes service node port mode)
  ip: "*"                         # optional, service bind ip address, `*` for all ip by default
  selector: "[]"                  # required, service member selector, use JMESPath to filter inventory
  dest: default                   # optional, destination port, default|postgres|pgbouncer|<port_number>, 'default' by default
  check: /sync                    # optional, health check url path, / by default
  backup: "[? pg_role == `primary`]"  # backup server selector
  maxconn: 3000                   # optional, max allowed front-end connection
  balance: roundrobin             # optional, haproxy load balance algorithm (roundrobin by default, other: leastconn)
  options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'

And it will be translated to a haproxy config file /etc/haproxy/pg-test-standby.conf:

#---------------------------------------------------------------------
# service: pg-test-standby @ 10.10.10.11:5435
#---------------------------------------------------------------------
# service instances 10.10.10.11, 10.10.10.13, 10.10.10.12
# service backups   10.10.10.11
listen pg-test-standby
    bind *:5435
    mode tcp
    maxconn 5000
    balance roundrobin
    option httpchk
    option http-keep-alive
    http-check send meth OPTIONS uri /sync  # <--- true for primary & sync standby
    http-check expect status 200
    default-server inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100
    # servers
    server pg-test-1 10.10.10.11:6432 check port 8008 weight 100 backup   # the primary is used as backup server
    server pg-test-3 10.10.10.13:6432 check port 8008 weight 100
    server pg-test-2 10.10.10.12:6432 check port 8008 weight 100

Reload Service

When cluster membership has changed, such as append / remove replicas, switchover/failover, or adjust relative weight, You have to reload service to make the changes take effect.

bin/pgsql-svc <cls> [ip...]         # reload service for lb cluster or lb instance
# ./pgsql.yml -t pg_service         # the actual ansible task to reload service

Override Service

You can override default service configuration with several ways:

Bypass Pgbouncer

When defining a service, if svc.dest='default', this parameter pg_default_service_dest will be used as the default value. pgbouncer is used by default, you can use postgres instead, so the default primary & replica service will bypass pgbouncer and route traffic to postgres directly

If you don’t need connection pooling at all, you can change pg_default_service_dest to postgres, and remove default and offline services.

If you don’t need read-only replicas for online traffic, you can remove replica from pg_default_services too.

pg_default_services:
  - { name: primary ,port: 5433 ,dest: default  ,check: /primary   ,selector: "[]" }
  - { name: replica ,port: 5434 ,dest: default  ,check: /read-only ,selector: "[]" , backup: "[? pg_role == `primary` || pg_role == `offline` ]" }
  - { name: default ,port: 5436 ,dest: postgres ,check: /primary   ,selector: "[]" }
  - { name: offline ,port: 5438 ,dest: postgres ,check: /replica   ,selector: "[? pg_role == `offline` || pg_offline_query ]" , backup: "[? pg_role == `replica` && !pg_offline_query]"}

Delegate Service

Pigsty expose PostgreSQL services with haproxy on node. All haproxy instances among the cluster are configured with the same service definition.

However, you can delegate pg service to a specific node group (e.g. dedicate haproxy lb cluster) rather than cluster members.

To do so, you will have to override the default service definition with pg_default_services and set pg_service_provider to the proxy group name.

For example, this configuration will expose pg cluster primary service on haproxy node group proxy with port 10013.

pg_service_provider: proxy       # use load balancer on group `proxy` with port 10013
pg_default_services:  [{ name: primary ,port: 10013 ,dest: postgres  ,check: /primary   ,selector: "[]" }]

It’s user’s responsibility to make sure each delegate service port is unique among the proxy cluster.

5 - Extensions

Define, Create, Install, Enable Extensions in Pigsty

Extensions are the soul of PostgreSQL, and Pigsty deeply integrates the core extension plugins of the PostgreSQL ecosystem, providing you with battery-included distributed temporal, geospatial text, graph, and vector database capabilities! Check extension list for details.

Pigsty includes over 220+ PostgreSQL extension plugins and has compiled, packaged, integrated, and maintained many extensions not included in the official PGDG source. It also ensures through thorough testing that all these plugins can work together seamlessly. Including some potent extensions:

  • PostGIS: Add geospatial data support to PostgreSQL
  • TimescaleDB: Add time-series/continuous-aggregation support to PostgreSQL
  • PGVector: AI vector/embedding data type support, and ivfflat / hnsw index access method
  • Citus: Turn a standalone primary-replica postgres cluster into a horizontally scalable distributed cluster
  • Apache AGE: Add OpenCypher graph query language support to PostgreSQL, works like Neo4J
  • PG GraphQL: Add GraphQL language support to PostgreSQL
  • zhparser : Add Chinese word segmentation support to PostgreSQL, works like ElasticSearch
  • Supabase: Open-Source Firebase alternative based on PostgreSQL
  • FerretDB: Open-Source MongoDB alternative based on PostgreSQL
  • PostgresML: Use machine learning algorithms and pretrained models with SQL
  • ParadeDB: Open-Source ElasticSearch Alternative (based on PostgreSQL)

Plugins are already included and placed in the yum repo of the infra nodes, which can be directly enabled through PGSQL Cluster Config. Pigsty also introduces a complete compilation environment and infrastructure, allowing you to compile extensions not included in Pigsty & PGDG.

pigsty-extension.jpg

Some “database” are not actual PostgreSQL extensions, but also supported by pigsty, such as:

  • Supabase: Open-Source Firebase Alternative (based on PostgreSQL)
  • FerretDB: Open-Source MongoDB Alternative (based on PostgreSQL)
  • NocoDB: Open-Source Airtable Alternative (based on PostgreSQL)
  • DuckDB: Open-Source Analytical SQLite Alternative (PostgreSQL Compatible)

Install Extension

When you init a PostgreSQL cluster, the extensions listed in pg_packages & pg_extensions will be installed.

For default EL systems, the default values of pg_packages and pg_extensions are defined as follows:

pg_packages:     # these extensions are always installed by default : pg_repack, wal2json, passwordcheck_cracklib
  - pg_repack_${pg_version}* wal2json_${pg_version}* passwordcheck_cracklib_${pg_version}* # important extensions
pg_extensions:   # install postgis, timescaledb, pgvector by default
  - postgis34_${pg_version}* timescaledb-2-postgresql-${pg_version}* pgvector_${pg_version}*

For ubuntu / debian, package names are different, and passwordcheck_cracklib is not available.

pg_packages:    # these extensions are always installed by default : pg_repack, wal2json
  - postgresql-${pg_version}-repack postgresql-${pg_version}-wal2json
pg_extensions:  # these extensions are installed by default:
  - postgresql-${pg_version}-postgis* timescaledb-2-postgresql-${pg_version} postgresql-${pg_version}-pgvector postgresql-${pg_version}-citus-12.1

Here, ${pg_version} is a placeholder that will be replaced with the actual major version number pg_version of that PostgreSQL cluster Therefore, the default configuration will install these extensions:

  • pg_repack: Extension for online table bloat processing.
  • wal2json: Extracts changes in JSON format through logical decoding.
  • passwordcheck_cracklib: Enforce password policy. (EL only)
  • postgis: Geospatial database extension (postgis34, EL7: postgis33)
  • timescaledb: Time-series database extension
  • pgvector: Vector datatype and ivfflat/hnsw index
  • citus: Distributed/columnar storage extension, (citus is conflict with hydra, choose one of them on EL systems)

If you want to enable certain extensions in a target cluster that has not yet been created, you can directly declare them with the parameters:

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - name: test
        extensions:                 # <----- install these extensions for database `test`
          - { name: postgis, schema: public }
          - { name: timescaledb }
          - { name: pg_cron }
          - { name: vector }
          - { name: age }
    pg_libs: 'timescaledb, pg_cron, pg_stat_statements, auto_explain' # <- some extension require a share library to work
    pg_extensions:
      - postgis34_${pg_version}* timescaledb-2-postgresql-${pg_version}* pgvector_${pg_version}* hydra_${pg_version}*   # default extensions to be installed
      - pg_cron_${pg_version}*        # <---- new extension: pg_cron
      - apache-age_${pg_version}*     # <---- new extension: apache-age
      - zhparser_${pg_version}*       # <---- new extension: zhparser

You can run the pg_extension sub-task in pgsql.yml to add extensions to clusters that have already been created.

./pgsql.yml -l pg-meta -t pg_extension    # install specified extensions for cluster pg-v15

To install all available extensions in one pass, you can just specify pg_extensions: ['*${pg_version}*'], which is really a bold move.


Install Manually

After the PostgreSQL cluster is inited, you can manually install plugins via Ansible or Shell commands. For example, if you want to enable a specific extension on a cluster that has already been initialized:

cd ~/pigsty;    # enter pigsty home dir and install the apache age extension for the pg-test cluster
ansible pg-test -m yum -b -a 'name=apache-age_16*'     # The extension name usually has a suffix like `_<pgmajorversion>`

Most plugins are already included in the yum repository on the infrastructure node and can be installed directly using the yum command. If not included, you can consider downloading from the PGDG upstream source using the repotrack / apt download command or compiling source code into RPMs for distribution.

After the extension installation, you should be able to see them in the pg_available_extensions view of the target database cluster. Next, execute in the database where you want to install the extension:

CREATE EXTENSION age;          -- install the graph database extension

6 - Authentication

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

Host-Based Authentication in Pigsty

PostgreSQL has various authentication methods. You can use all of them, while pigsty’s battery-include ACL system focuses on HBA, password, and SSL authentication.


Client Authentication

To connect to a PostgreSQL database, the user has to be authenticated (with a password by default).

You can provide the password in the connection string (not secure) or use the PGPASSWORD env or .pgpass file. Check psql docs and PostgreSQL connection string for more details.

psql 'host=<host> port=<port> dbname=<dbname> user=<username> password=<password>'
psql postgres://<username>:<password>@<host>:<port>/<dbname>
PGPASSWORD=<password>; psql -U <username> -h <host> -p <port> -d <dbname>

The default connection string for the meta database:

psql 'host=10.10.10.10 port=5432 dbname=meta user=dbuser_dba password=DBUser.DBA'
psql postgres://dbuser_dba:DBUser.DBA@10.10.10.10:5432/meta
PGPASSWORD=DBUser.DBA; psql -U dbuser_dba -h 10.10.10.10 -p 5432 -d meta

To connect with the SSL certificate, you can use the PGSSLCERT and PGSSLKEY env or sslkey & sslcert parameters.

psql 'postgres://dbuser_dba:DBUser.DBA@10.10.10.10:5432/meta?sslkey=/path/to/dbuser_dba.key&sslcert=/path/to/dbuser_dba.crt'

While the client certificate (CN = username) can be issued with local CA & cert.yml.


Define HBA

There are four parameters for HBA Rules in Pigsty:

Which are array of hba rule objects, and each hba rule is one of the following forms:

1. Raw Form

- title: allow intranet password access
  role: common
  rules:
    - host   all  all  10.0.0.0/8      md5
    - host   all  all  172.16.0.0/12   md5
    - host   all  all  192.168.0.0/16  md5

In the form, the title will be rendered as a comment line, followed by the rules as hba string one by one.

An HBA Rule is installed when the instance’s pg_role is the same as the role.

HBA Rule with role: common will be installed on all instances.

HBA Rule with role: offline will be installed on instances with pg_role = offline or pg_offline_query = true.

2. Alias Form

The alias form, which replace rules with addr, auth, user, and db fields.

- addr: 'intra'    # world|intra|infra|admin|local|localhost|cluster|<cidr>
  auth: 'pwd'      # trust|pwd|ssl|cert|deny|<official auth method>
  user: 'all'      # all|${dbsu}|${repl}|${admin}|${monitor}|<user>|<group>
  db: 'all'        # all|replication|....
  rules: []        # raw hba string precedence over above all
  title: allow intranet password access
  • addr: where
    • world: all IP addresses
    • intra: all intranet cidr: '10.0.0.0/8', '172.16.0.0/12', '192.168.0.0/16'
    • infra: IP addresses of infra nodes
    • admin: admin_ip address
    • local: local unix socket
    • localhost: local unix socket + tcp 127.0.0.1/32
    • cluster: all IP addresses of pg cluster members
    • <cidr>: any standard CIDR blocks or IP addresses
  • auth: how
    • deny: reject access
    • trust: trust authentication
    • pwd: use md5 or scram-sha-256 password auth according to pg_pwd_enc
    • sha/scram-sha-256: enforce scram-sha-256 password authentication
    • md5: md5 password authentication
    • ssl: enforce host ssl in addition to pwd auth
    • ssl-md5: enforce host ssl in addition to md5 password auth
    • ssl-sha: enforce host ssl in addition to scram-sha-256 password auth
    • os/ident: use ident os user authentication
    • peer: use peer authentication
    • cert: use certificate-based client authentication
  • user: who
  • db: which
    • all: all databases
    • replication: replication database
    • ad hoc database name

3. Where to Define

Typically, global HBA is defined in all.vars. If you want to modify the global default HBA rules, you can copy from the full.yml template to all.vars for modification.

Cluster-specific HBA rules are defined in the cluster-level configuration of the database:

Here are some examples of cluster HBA rule definitions.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_hba_rules:
      - { user: dbuser_view ,db: all    ,addr: infra        ,auth: pwd  ,title: 'allow grafana dashboard access cmdb from infra nodes'}
      - { user: all         ,db: all    ,addr: 100.0.0.0/8  ,auth: pwd  ,title: 'all user access all db from kubernetes cluster' }
      - { user: '${admin}'  ,db: world  ,addr: 0.0.0.0/0    ,auth: cert ,title: 'all admin world access with client cert'        }

Reload HBA

To reload postgres/pgbouncer hba rules:

bin/pgsql-hba <cls>                 # reload hba rules of cluster `<cls>`
bin/pgsql-hba <cls> ip1 ip2...      # reload hba rules of specific instances

The underlying command: are:

./pgsql.yml -l <cls> -e pg_reload=true -t pg_hba
./pgsql.yml -l <cls> -e pg_reload=true -t pgbouncer_hba,pgbouncer_reload

Default HBA

Pigsty has a default set of HBA rules, which is pretty secure for most cases.

The rules are self-explained in alias form.

pg_default_hba_rules:             # postgres default host-based authentication rules
  - {user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu access via local os user ident'  }
  - {user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu replication from local os ident' }
  - {user: '${repl}'    ,db: replication ,addr: localhost ,auth: pwd   ,title: 'replicator replication from localhost'}
  - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: pwd   ,title: 'replicator replication from intranet' }
  - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: pwd   ,title: 'replicator postgres db from intranet' }
  - {user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor from localhost with password' }
  - {user: '${monitor}' ,db: all         ,addr: infra     ,auth: pwd   ,title: 'monitor from infra host with password'}
  - {user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin @ infra nodes with pwd & ssl'   }
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: ssl   ,title: 'admin @ everywhere with ssl & pwd'   }
  - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: pwd   ,title: 'pgbouncer read/write via local socket'}
  - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: pwd   ,title: 'read/write biz user via password'     }
  - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: pwd   ,title: 'allow etl offline tasks from intranet'}
pgb_default_hba_rules:            # pgbouncer default host-based authentication rules
  - {user: '${dbsu}'    ,db: pgbouncer   ,addr: local     ,auth: peer  ,title: 'dbsu local admin access with os ident'}
  - {user: 'all'        ,db: all         ,addr: localhost ,auth: pwd   ,title: 'allow all user local access with pwd' }
  - {user: '${monitor}' ,db: pgbouncer   ,addr: intra     ,auth: pwd   ,title: 'monitor access via intranet with pwd' }
  - {user: '${monitor}' ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other monitor access addr' }
  - {user: '${admin}'   ,db: all         ,addr: intra     ,auth: pwd   ,title: 'admin access via intranet with pwd'   }
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other admin access addr'   }
  - {user: 'all'        ,db: all         ,addr: intra     ,auth: pwd   ,title: 'allow all user intra access with pwd' }
Example: Rendered pg_hba.conf
#==============================================================#
# File      :   pg_hba.conf
# Desc      :   Postgres HBA Rules for pg-meta-1 [primary]
# Time      :   2023-01-11 15:19
# Host      :   pg-meta-1 @ 10.10.10.10:5432
# Path      :   /pg/data/pg_hba.conf
# Note      :   ANSIBLE MANAGED, DO NOT CHANGE!
# Author    :   Ruohang Feng (rh@vonng.com)
# License   :   AGPLv3
#==============================================================#

# addr alias
# local     : /var/run/postgresql
# admin     : 10.10.10.10
# infra     : 10.10.10.10
# intra     : 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16

# user alias
# dbsu    :  postgres
# repl    :  replicator
# monitor :  dbuser_monitor
# admin   :  dbuser_dba

# dbsu access via local os user ident [default]
local    all                postgres                              ident

# dbsu replication from local os ident [default]
local    replication        postgres                              ident

# replicator replication from localhost [default]
local    replication        replicator                            scram-sha-256
host     replication        replicator         127.0.0.1/32       scram-sha-256

# replicator replication from intranet [default]
host     replication        replicator         10.0.0.0/8         scram-sha-256
host     replication        replicator         172.16.0.0/12      scram-sha-256
host     replication        replicator         192.168.0.0/16     scram-sha-256

# replicator postgres db from intranet [default]
host     postgres           replicator         10.0.0.0/8         scram-sha-256
host     postgres           replicator         172.16.0.0/12      scram-sha-256
host     postgres           replicator         192.168.0.0/16     scram-sha-256

# monitor from localhost with password [default]
local    all                dbuser_monitor                        scram-sha-256
host     all                dbuser_monitor     127.0.0.1/32       scram-sha-256

# monitor from infra host with password [default]
host     all                dbuser_monitor     10.10.10.10/32     scram-sha-256

# admin @ infra nodes with pwd & ssl [default]
hostssl  all                dbuser_dba         10.10.10.10/32     scram-sha-256

# admin @ everywhere with ssl & pwd [default]
hostssl  all                dbuser_dba         0.0.0.0/0          scram-sha-256

# pgbouncer read/write via local socket [default]
local    all                +dbrole_readonly                      scram-sha-256
host     all                +dbrole_readonly   127.0.0.1/32       scram-sha-256

# read/write biz user via password [default]
host     all                +dbrole_readonly   10.0.0.0/8         scram-sha-256
host     all                +dbrole_readonly   172.16.0.0/12      scram-sha-256
host     all                +dbrole_readonly   192.168.0.0/16     scram-sha-256

# allow etl offline tasks from intranet [default]
host     all                +dbrole_offline    10.0.0.0/8         scram-sha-256
host     all                +dbrole_offline    172.16.0.0/12      scram-sha-256
host     all                +dbrole_offline    192.168.0.0/16     scram-sha-256

# allow application database intranet access [common] [DISABLED]
#host    kong            dbuser_kong         10.0.0.0/8          md5
#host    bytebase        dbuser_bytebase     10.0.0.0/8          md5
#host    grafana         dbuser_grafana      10.0.0.0/8          md5
Example: Rendered pgb_hba.conf
#==============================================================#
# File      :   pgb_hba.conf
# Desc      :   Pgbouncer HBA Rules for pg-meta-1 [primary]
# Time      :   2023-01-11 15:28
# Host      :   pg-meta-1 @ 10.10.10.10:5432
# Path      :   /etc/pgbouncer/pgb_hba.conf
# Note      :   ANSIBLE MANAGED, DO NOT CHANGE!
# Author    :   Ruohang Feng (rh@vonng.com)
# License   :   AGPLv3
#==============================================================#

# PGBOUNCER HBA RULES FOR pg-meta-1 @ 10.10.10.10:6432
# ansible managed: 2023-01-11 14:30:58

# addr alias
# local     : /var/run/postgresql
# admin     : 10.10.10.10
# infra     : 10.10.10.10
# intra     : 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16

# user alias
# dbsu    :  postgres
# repl    :  replicator
# monitor :  dbuser_monitor
# admin   :  dbuser_dba

# dbsu local admin access with os ident [default]
local    pgbouncer          postgres                              peer

# allow all user local access with pwd [default]
local    all                all                                   scram-sha-256
host     all                all                127.0.0.1/32       scram-sha-256

# monitor access via intranet with pwd [default]
host     pgbouncer          dbuser_monitor     10.0.0.0/8         scram-sha-256
host     pgbouncer          dbuser_monitor     172.16.0.0/12      scram-sha-256
host     pgbouncer          dbuser_monitor     192.168.0.0/16     scram-sha-256

# reject all other monitor access addr [default]
host     all                dbuser_monitor     0.0.0.0/0          reject

# admin access via intranet with pwd [default]
host     all                dbuser_dba         10.0.0.0/8         scram-sha-256
host     all                dbuser_dba         172.16.0.0/12      scram-sha-256
host     all                dbuser_dba         192.168.0.0/16     scram-sha-256

# reject all other admin access addr [default]
host     all                dbuser_dba         0.0.0.0/0          reject

# allow all user intra access with pwd [default]
host     all                all                10.0.0.0/8         scram-sha-256
host     all                all                172.16.0.0/12      scram-sha-256
host     all                all                192.168.0.0/16     scram-sha-256

Security Enhancement

For those critical cases, we have a security.yml template with the following hba rule set as a reference:

pg_default_hba_rules:             # postgres host-based auth rules by default
  - {user: '${dbsu}'    ,db: all         ,addr: local     ,auth: ident ,title: 'dbsu access via local os user ident'  }
  - {user: '${dbsu}'    ,db: replication ,addr: local     ,auth: ident ,title: 'dbsu replication from local os ident' }
  - {user: '${repl}'    ,db: replication ,addr: localhost ,auth: ssl   ,title: 'replicator replication from localhost'}
  - {user: '${repl}'    ,db: replication ,addr: intra     ,auth: ssl   ,title: 'replicator replication from intranet' }
  - {user: '${repl}'    ,db: postgres    ,addr: intra     ,auth: ssl   ,title: 'replicator postgres db from intranet' }
  - {user: '${monitor}' ,db: all         ,addr: localhost ,auth: pwd   ,title: 'monitor from localhost with password' }
  - {user: '${monitor}' ,db: all         ,addr: infra     ,auth: ssl   ,title: 'monitor from infra host with password'}
  - {user: '${admin}'   ,db: all         ,addr: infra     ,auth: ssl   ,title: 'admin @ infra nodes with pwd & ssl'   }
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: cert  ,title: 'admin @ everywhere with ssl & cert'   }
  - {user: '+dbrole_readonly',db: all    ,addr: localhost ,auth: ssl   ,title: 'pgbouncer read/write via local socket'}
  - {user: '+dbrole_readonly',db: all    ,addr: intra     ,auth: ssl   ,title: 'read/write biz user via password'     }
  - {user: '+dbrole_offline' ,db: all    ,addr: intra     ,auth: ssl   ,title: 'allow etl offline tasks from intranet'}
pgb_default_hba_rules:            # pgbouncer host-based authentication rules
  - {user: '${dbsu}'    ,db: pgbouncer   ,addr: local     ,auth: peer  ,title: 'dbsu local admin access with os ident'}
  - {user: 'all'        ,db: all         ,addr: localhost ,auth: pwd   ,title: 'allow all user local access with pwd' }
  - {user: '${monitor}' ,db: pgbouncer   ,addr: intra     ,auth: ssl   ,title: 'monitor access via intranet with pwd' }
  - {user: '${monitor}' ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other monitor access addr' }
  - {user: '${admin}'   ,db: all         ,addr: intra     ,auth: ssl   ,title: 'admin access via intranet with pwd'   }
  - {user: '${admin}'   ,db: all         ,addr: world     ,auth: deny  ,title: 'reject all other admin access addr'   }
  - {user: 'all'        ,db: all         ,addr: intra     ,auth: ssl   ,title: 'allow all user intra access with pwd' }

7 - Configuration

Configure your PostgreSQL cluster & instances according to your needs

You can define different types of instances & clusters.

  • 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: Create postgres cluster with different major version

Primary

Let’s start with the simplest case, singleton meta:

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-test

Use the following command to create a primary database instance on the 10.10.10.11 node.

bin/pgsql-add pg-test

Replica

To add a physical replica, you can assign a new instance to pg-test with pg_role set to replica

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }  # <--- newly added
  vars:
    pg_cluster: pg-test

You can create an entire cluster or append a replica to the existing cluster:

bin/pgsql-add pg-test               # init entire cluster in one-pass
bin/pgsql-add pg-test 10.10.10.12   # add replica to existing cluster

Offline

The offline instance is a dedicated replica to serve slow queries, ETL, OLAP traffic and interactive queries, etc…

To add an offline instance, assign a new instance with pg_role set to offline.

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 2, pg_role: offline } # <--- newly added
  vars:
    pg_cluster: pg-test

Offline instance works like common replica instances, but it is used as a backup server in pg-test-replica service. That is to say, offline and primary instance serves only when all replica instances are down.

You can have ad hoc access control offline with pg_default_hba_rules and pg_hba_rules. It will apply to the offline instance and any instances with pg_offline_query flag.


Sync Standby

Pigsty uses asynchronous stream replication by default. Which may have a small replication lag. (10KB / 10ms). A small window of data loss may occur when the primary fails (can be controlled with pg_rpo.), but it is acceptable for most scenarios.

But in some critical scenarios (e.g. financial transactions), data loss is totally unacceptable or read-your-write consistency is required. In this case, you can enable synchronous commit to ensure that.

To enable sync standby mode, you can simply use crit.yml template in pg_conf

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: replica }
  vars:
    pg_cluster: pg-test
    pg_conf: crit.yml   # <--- use crit template

To enable sync standby on existing clusters, config the cluster and enable synchronous_mode:

$ pg edit-config pg-test    # run on admin node with admin user
+++
-synchronous_mode: false    # <--- old value
+synchronous_mode: true     # <--- new value
 synchronous_mode_strict: false

Apply these changes? [y/N]: y

If synchronous_mode: true, the synchronous_standby_names parameter will be managed by patroni. It will choose a sync standby from all available replicas and write its name to the primary’s configuration file.


Quorum Commit

When sync standby is enabled, PostgreSQL will pick one replica as the standby instance, and all other replicas as candidates. Primary will wait until the standby instance flushes to disk before a commit is confirmed, and the standby instance will always have the latest data without any lags.

However, you can achieve an even higher/lower consistency level with the quorum commit (trade-off with availability).

For example, to have all 2 replicas to confirm a commit:

synchronous_mode: true          # make sure synchronous mode is enabled
synchronous_node_count: 2       # at least 2 nodes to confirm a commit

If you have more replicas and wish to have more sync standby, increase synchronous_node_count accordingly. Beware of adjust synchronous_node_count accordingly when you append or remove replicas.

The postgres synchronous_standby_names parameter will be managed by patroni:

synchronous_standby_names = '2 ("pg-test-3","pg-test-2")'
Example: Multiple Sync Standby
$ pg edit-config pg-test
---
+++
@@ -82,10 +82,12 @@
     work_mem: 4MB
+    synchronous_standby_names: 'ANY 2 (pg-test-2, pg-test-3, pg-test-4)'
 
-synchronous_mode: false
+synchronous_mode: true
+synchronous_node_count: 2
 synchronous_mode_strict: false

Apply these changes? [y/N]: y

And we can see that the two replicas are selected as sync standby now.

+ Cluster: pg-test (7080814403632534854) +---------+----+-----------+-----------------+
| Member    | Host        | Role         | State   | TL | Lag in MB | Tags            |
+-----------+-------------+--------------+---------+----+-----------+-----------------+
| pg-test-1 | 10.10.10.10 | Leader       | running |  1 |           | clonefrom: true |
| pg-test-2 | 10.10.10.11 | Sync Standby | running |  1 |         0 | clonefrom: true |
| pg-test-3 | 10.10.10.12 | Sync Standby | running |  1 |         0 | clonefrom: true |
+-----------+-------------+--------------+---------+----+-----------+-----------------+

The classic quorum commit is to use majority of replicas to confirm a commit.

synchronous_mode: quorum        # use quorum commit
postgresql:
  parameters:                   # change the PostgreSQL parameter `synchronous_standby_names`, use the `ANY n ()` notion
    synchronous_standby_names: 'ANY 1 (*)'  # you can specify a list of standby names, or use `*` to match them all
Example: Enable Quorum Commit
$ pg edit-config pg-test

+    synchronous_standby_names: 'ANY 1 (*)' # You have to configure this manually
+ synchronous_mode: quorum        # use quorum commit mode, undocumented parameter
- synchronous_node_count: 2       # this parameter is no longer needed in quorum mode

Apply these changes? [y/N]: y

After applying the configuration, we can see that all replicas are no longer sync standby, but just normal replicas.

After that, when we can check pg_stat_replication.sync_state, it becomes quorum instead of sync or async.


Standby Cluster

You can clone an existing cluster and create a standby cluster, which can be used for migration, horizontal split, multi-az deployment, or disaster recovery.

A standby cluster’s definition is just the same as any other normal cluster, except there’s a pg_upstream defined on the primary instance.

For example, you have a pg-test cluster, to create a standby cluster pg-test2, the inventory may look like this:

# pg-test is the original cluster
pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars: { pg_cluster: pg-test }

# pg-test2 is a standby cluster of pg-test.
pg-test2:
  hosts:
    10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11 } # <--- pg_upstream is defined here
    10.10.10.13: { pg_seq: 2, pg_role: replica }
  vars: { pg_cluster: pg-test2 }

And pg-test2-1, the primary of pg-test2 will be a replica of pg-test and serve as a Standby Leader in pg-test2.

Just make sure that the pg_upstream parameter is configured on the primary of the backup cluster to pull backups from the original upstream automatically.

bin/pgsql-add pg-test     # Creating the original cluster
bin/pgsql-add pg-test2    # Creating a Backup Cluster
Example: Change Replication Upstream

You can change the replication upstream of the standby cluster when necessary (e.g. upstream failover).

To do so, just change the standby_cluster.host to the new upstream IP address and apply.

$ pg edit-config pg-test2

 standby_cluster:
   create_replica_methods:
   - basebackup
-  host: 10.10.10.13     # <--- The old upstream
+  host: 10.10.10.12     # <--- The new upstream
   port: 5432

 Apply these changes? [y/N]: y
Example: Promote Standby Cluster

You can promote the standby cluster to a standalone cluster at any time.

To do so, you have to config the cluster and wipe the entire standby_cluster section then apply.

$ pg edit-config pg-test2
-standby_cluster:
-  create_replica_methods:
-  - basebackup
-  host: 10.10.10.11
-  port: 5432

Apply these changes? [y/N]: y
Example: Cascade Replica

If the pg_upstream is specified for replica rather than primary, the replica will be configured as a cascade replica with the given upstream ip instead of the cluster primary

pg-test:
  hosts: # pg-test-1 ---> pg-test-2 ---> pg-test-3
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica } # <--- bridge instance
    10.10.10.13: { pg_seq: 2, pg_role: replica, pg_upstream: 10.10.10.12 } 
    # ^--- replicate from pg-test-2 (the bridge) instead of pg-test-1 (the primary) 
  vars: { pg_cluster: pg-test }

Delayed Cluster

A delayed cluster is a special type of standby cluster, which is used to recover “drop-by-accident” ASAP.

For example, if you wish to have a cluster pg-testdelay which has the same data as 1-day ago pg-test cluster:

# pg-test is the original cluster
pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars: { pg_cluster: pg-test }

# pg-testdelay is a delayed cluster of pg-test.
pg-testdelay:
  hosts:
    10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11, pg_delay: 1d }
    10.10.10.13: { pg_seq: 2, pg_role: replica }
  vars: { pg_cluster: pg-test2 }

You can also configure a replication delay on the existing standby cluster.

$ pg edit-config pg-testdelay
 standby_cluster:
   create_replica_methods:
   - basebackup
   host: 10.10.10.11
   port: 5432
+  recovery_min_apply_delay: 1h    # <--- add delay here

Apply these changes? [y/N]: y

When some tuples & tables are dropped by accident, you can advance this delayed cluster to a proper time point and select data from it.

It takes more resources, but can be much faster and have less impact than PITR


Citus Cluster

Pigsty has native citus support. Check files/pigsty/citus.yml & prod.yml for example.

To define a citus cluster, you have to specify the following parameters:

Besides, extra hba rules that allow ssl access from local & other data nodes are required. Which may looks like this

all:
  children:
    pg-citus0: # citus data node 0
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus0 , pg_group: 0 }
    pg-citus1: # citus data node 1
      hosts: { 10.10.10.11: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus1 , pg_group: 1 }
    pg-citus2: # citus data node 2
      hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } }
      vars: { pg_cluster: pg-citus2 , pg_group: 2 }
    pg-citus3: # citus data node 3, with an extra replica
      hosts:
        10.10.10.13: { pg_seq: 1, pg_role: primary }
        10.10.10.14: { pg_seq: 2, pg_role: replica }
      vars: { pg_cluster: pg-citus3 , pg_group: 3 }
  vars:                               # global parameters for all citus clusters
    pg_mode: citus                    # pgsql cluster mode: citus
    pg_shard: pg-citus                # citus shard name: pg-citus
    patroni_citus_db: meta            # citus distributed database name
    pg_dbsu_password: DBUser.Postgres # all dbsu password access for citus cluster
    pg_users: [ { name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [ dbrole_admin ] } ]
    pg_databases: [ { name: meta ,extensions: [ { name: citus }, { name: postgis }, { name: timescaledb } ] } ]
    pg_hba_rules:
      - { user: 'all' ,db: all  ,addr: 127.0.0.1/32 ,auth: ssl ,title: 'all user ssl access from localhost' }
      - { user: 'all' ,db: all  ,addr: intra        ,auth: ssl ,title: 'all user ssl access from intranet'  }

And you can create distributed table & reference table on the coordinator node. Any data node can be used as the coordinator node since citus 11.2.

SELECT create_distributed_table('pgbench_accounts', 'aid'); SELECT truncate_local_data_after_distributing_table($$public.pgbench_accounts$$);
SELECT create_reference_table('pgbench_branches')         ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_branches$$);
SELECT create_reference_table('pgbench_history')          ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_history$$);
SELECT create_reference_table('pgbench_tellers')          ; SELECT truncate_local_data_after_distributing_table($$public.pgbench_tellers$$);

Major Version

Pigsty works on PostgreSQL 10+. While the pre-packaged packages only includes 12 - 16 for now.

version Comment Packages
16 The latest version with important extensions Core, L1 L2
15 The stable major version, with full extension support (default) Core, L1,L2,L3
14 The old stable major version, ith L1 extension support only Core, L1
13 Older major version, with L1 extension support only Core, L1
12 Older major version, with L1 extension support only Core, L1
  • Core: postgresql*, available on PG 12 - 16
  • L1 extensions: wal2json, pg_repack, passwordcheck_cracklib (PG 12 - 16)
  • L2 extensions: postgis, citus, timescaledb, pgvector (PG15, PG16)
  • L3 extensions: Other miscellaneous extensions (PG15 only)

Since some extensions are not available on PG 12,13,14,16, you may have to change pg_extensions and pg_libs to fit your needs.

Here are some example cluster definition with different major versions.

pg-v12:
  hosts: { 10.10.10.12: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-v12
    pg_version: 12
    pg_libs: 'pg_stat_statements, auto_explain'
    pg_extensions: [ 'wal2json_12* pg_repack_12* passwordcheck_cracklib_12*' ]

pg-v13:
  hosts: { 10.10.10.13: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-v13
    pg_version: 13
    pg_libs: 'pg_stat_statements, auto_explain'
    pg_extensions: [ 'wal2json_13* pg_repack_13* passwordcheck_cracklib_13*' ]

pg-v14:
  hosts: { 10.10.10.14: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-v14
    pg_version: 14

pg-v15:
  hosts: { 10.10.10.15: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-v15
    pg_version: 15

pg-v16:
  hosts: { 10.10.10.16: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-v16
    pg_version: 16

Beware that these extensions are just not included in Pigsty’s default repo. You can have these extensions on older pg version with proper configuration.

8 - Administration

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

How to maintain an existing PostgreSQL cluster with Pigsty?

Here are some SOP for common pgsql admin tasks


Cheatsheet

PGSQL playbooks and shortcuts:

bin/pgsql-add   <cls>                   # create pgsql cluster <cls>
bin/pgsql-user  <cls> <username>        # create pg user <username> on <cls>
bin/pgsql-db    <cls> <dbname>          # create pg database <dbname> on <cls>
bin/pgsql-svc   <cls> [...ip]           # reload pg service of cluster <cls>
bin/pgsql-hba   <cls> [...ip]           # reload postgres/pgbouncer HBA rules of cluster <cls>
bin/pgsql-add   <cls> [...ip]           # append replicas for cluster <cls>
bin/pgsql-rm    <cls> [...ip]           # remove replicas from cluster <cls>
bin/pgsql-rm    <cls>                   # remove pgsql cluster <cls>

Patroni admin command and shortcuts:

pg list        <cls>                    # print cluster info
pg edit-config <cls>                    # edit cluster config 
pg reload      <cls> [ins]              # reload cluster config
pg restart     <cls> [ins]              # restart pgsql cluster
pg reinit      <cls> [ins]              # reinit cluster members
pg pause       <cls>                    # entering maintenance mode (no auto failover)
pg resume      <cls>                    # exiting maintenance mode
pg switchover  <cls>                    # switchover on cluster <cls>
pg failover    <cls>                    # failover on cluster <cls>

pgBackRest backup & restore command and shortcuts:

pb info                                 # print pgbackrest repo info
pg-backup                               # make a backup, incr, or full backup if necessary
pg-backup full                          # make a full backup
pg-backup diff                          # make a differential backup
pg-backup incr                          # make a incremental backup
pg-pitr -i                              # restore to the time of latest backup complete (not often used)
pg-pitr --time="2022-12-30 14:44:44+08" # restore to specific time point (in case of drop db, drop table)
pg-pitr --name="my-restore-point"       # restore TO a named restore point create by pg_create_restore_point
pg-pitr --lsn="0/7C82CB8" -X            # restore right BEFORE a LSN
pg-pitr --xid="1234567" -X -P           # restore right BEFORE a specific transaction id, then promote
pg-pitr --backup=latest                 # restore to latest backup set
pg-pitr --backup=20221108-105325        # restore to a specific backup set, which can be checked with pgbackrest info

Systemd components quick reference

systemctl stop patroni                  # start stop restart reload
systemctl stop pgbouncer                # start stop restart reload
systemctl stop pg_exporter              # start stop restart reload
systemctl stop pgbouncer_exporter       # start stop restart reload
systemctl stop node_exporter            # start stop restart
systemctl stop haproxy                  # start stop restart reload
systemctl stop vip-manager              # start stop restart reload
systemctl stop postgres                 # only when patroni_mode == 'remove'

Create Cluster

To create a new Postgres cluster, define it in the inventory first, then init with:

bin/node-add <cls>                # init nodes for cluster <cls>           # ./node.yml  -l <cls> 
bin/pgsql-add <cls>               # init pgsql instances of cluster <cls>  # ./pgsql.yml -l <cls>

Beware, perform bin/node-add first, then bin/pgsql-add, PGSQL works on managed nodes only.

Example: Create Cluster

asciicast


Create User

To create a new business user on the existing Postgres cluster, add user definition to all.children.<cls>.pg_users, then create the user as follows:

bin/pgsql-user <cls> <username>   # ./pgsql-user.yml -l <cls> -e username=<username>
Example: Create Business User

asciicast


Create Database

To create a new database user on the existing Postgres cluster, add database definition to all.children.<cls>.pg_databases, then create the database as follows:

bin/pgsql-db <cls> <dbname>       # ./pgsql-db.yml -l <cls> -e dbname=<dbname>

Note: If the database has specified an owner, the user should already exist, or you’ll have to Create User first.

Example: Create Business Database

asciicast


Reload Service

Services are exposed access point served by HAProxy.

This task is used when cluster membership has changed, e.g., append/remove replicas, switchover/failover / exposing new service or updating existing service’s config (e.g., LB Weight)

To create new services or reload existing services on entire proxy cluster or specific instances:

bin/pgsql-svc <cls>               # pgsql.yml -l <cls> -t pg_service -e pg_reload=true
bin/pgsql-svc <cls> [ip...]       # pgsql.yml -l ip... -t pg_service -e pg_reload=true
Example: Reload PG Service to Kick one Instance

asciicast


Reload HBARule

This task is used when your Postgres/Pgbouncer HBA rules have changed, you may have to reload hba to apply changes.

If you have any role-specific HBA rules, you may have to reload hba after a switchover/failover, too.

To reload postgres & pgbouncer HBA rules on entire cluster or specific instances:

bin/pgsql-hba <cls>               # pgsql.yml -l <cls> -t pg_hba,pgbouncer_hba,pgbouncer_reload -e pg_reload=true
bin/pgsql-hba <cls> [ip...]       # pgsql.yml -l ip... -t pg_hba,pgbouncer_hba,pgbouncer_reload -e pg_reload=true
Example: Reload Cluster HBA Rules

asciicast


Config Cluster

To change the config of a existing Postgres cluster, you have to initiate control command on admin node with admin user:

pg edit-config <cls>              # interactive config a cluster with patronictl

Change patroni parameters & postgresql.parameters, save & apply changes with the wizard.

Example: Config Cluster in Non-Interactive Manner

You can skip interactive mode and use -p option to override postgres parameters, for example:

pg edit-config -p log_min_duration_statement=1000 pg-test
pg edit-config --force -p shared_preload_libraries='timescaledb, pg_cron, pg_stat_statements, auto_explain'
Example: Change Cluster Config with Patroni REST API

You can also use Patroni REST API to change the config in a non-interactive mode, for example:

$ curl -s 10.10.10.11:8008/config | jq .  # get current config
$ curl -u 'postgres:Patroni.API' \
        -d '{"postgresql":{"parameters": {"log_min_duration_statement":200}}}' \
        -s -X PATCH http://10.10.10.11:8008/config | jq .

Note: patroni unsafe RestAPI access is limit from infra/admin nodes and protected with an HTTP basic auth username/password and an optional HTTPS mode.

Example: Config Cluster with patronictl

asciicast


Append Replica

To add a new replica to the existing Postgres cluster, you have to add its definition to the inventory: all.children.<cls>.hosts, then:

bin/node-add <ip>                 # init node <ip> for the new replica               
bin/pgsql-add <cls> <ip>          # init pgsql instances on <ip> for cluster <cls>  

It will add node <ip> to pigsty and init it as a replica of the cluster <cls>.

Cluster services will be reloaded to adopt the new member

Example: Add replica to pg-test

asciicast

For example, if you want to add a pg-test-3 / 10.10.10.13 to the existing cluster pg-test, you’ll have to update the inventory first:

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary } # existing member
    10.10.10.12: { pg_seq: 2, pg_role: replica } # existing member
    10.10.10.13: { pg_seq: 3, pg_role: replica } # <--- new member
  vars: { pg_cluster: pg-test }

then apply the change as follows:

bin/node-add          10.10.10.13   # add node to pigsty
bin/pgsql-add pg-test 10.10.10.13   # init new replica on 10.10.10.13 for cluster pg-test

which is similar to cluster init but only works on single instance。

[ OK ] init instances  10.10.10.11 to pgsql cluster 'pg-test':
[WARN]   reminder: add nodes to pigsty, then install additional module 'pgsql'
[HINT]     $ bin/node-add  10.10.10.11  # run this ahead, except infra nodes
[WARN]   init instances from cluster:
[ OK ]     $ ./pgsql.yml -l '10.10.10.11,&pg-test'
[WARN]   reload pg_service on existing instances:
[ OK ]     $ ./pgsql.yml -l 'pg-test,!10.10.10.11' -t pg_service

Remove Replica

To remove a replica from the existing PostgreSQL cluster:

bin/pgsql-rm <cls> <ip...>        # ./pgsql-rm.yml -l <ip>

It will remove instance <ip> from cluster <cls>. Cluster services will be reloaded to kick the removed instance from load balancer.

Example: Remove replica from pg-test

asciicast

For example, if you want to remove pg-test-3 / 10.10.10.13 from the existing cluster pg-test:

bin/pgsql-rm pg-test 10.10.10.13  # remove pgsql instance 10.10.10.13 from pg-test
bin/node-rm  10.10.10.13          # remove that node from pigsty (optional)
vi pigsty.yml                     # remove instance definition from inventory
bin/pgsql-svc pg-test             # refresh pg_service on existing instances to kick removed instance from load balancer
[ OK ] remove pgsql instances from  10.10.10.13 of 'pg-test':
[WARN]   remove instances from cluster:
[ OK ]     $ ./pgsql-rm.yml -l '10.10.10.13,&pg-test'

And remove instance definition from the inventory:

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
    10.10.10.13: { pg_seq: 3, pg_role: replica } # <--- remove this after execution
  vars: { pg_cluster: pg-test }

Finally, you can update pg service and kick the removed instance from load balancer:

bin/pgsql-svc pg-test             # reload pg service on pg-test

Remove Cluster

To remove the entire Postgres cluster, just run:

bin/pgsql-rm <cls>                # ./pgsql-rm.yml -l <cls>
Example: Remove Cluster

asciicast

Example: Force removing a cluster

Note: if pg_safeguard is configured for this cluster (or globally configured to true), pgsql-rm.yml will abort to avoid removing a cluster by accident.

You can use playbook command line args to explicitly overwrite it to force the purge:

./pgsql-rm.yml -l pg-meta -e pg_safeguard=false    # force removing pg cluster pg-meta

Switchover

You can perform a PostgreSQL cluster switchover with patroni cmd.

pg switchover <cls>   # interactive mode, you can skip that with following options
pg switchover --leader pg-test-1 --candidate=pg-test-2 --scheduled=now --force pg-test
Example: Switchover pg-test

asciicast

$ pg switchover pg-test
Master [pg-test-1]:
Candidate ['pg-test-2', 'pg-test-3'] []: pg-test-2
When should the switchover take place (e.g. 2022-12-26T07:39 )  [now]: now
Current cluster topology
+ Cluster: pg-test (7181325041648035869) -----+----+-----------+-----------------+
| Member    | Host        | Role    | State   | TL | Lag in MB | Tags            |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-1 | 10.10.10.11 | Leader  | running |  1 |           | clonefrom: true |
|           |             |         |         |    |           | conf: tiny.yml  |
|           |             |         |         |    |           | spec: 1C.2G.50G |
|           |             |         |         |    |           | version: '15'   |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-2 | 10.10.10.12 | Replica | running |  1 |         0 | clonefrom: true |
|           |             |         |         |    |           | conf: tiny.yml  |
|           |             |         |         |    |           | spec: 1C.2G.50G |
|           |             |         |         |    |           | version: '15'   |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-3 | 10.10.10.13 | Replica | running |  1 |         0 | clonefrom: true |
|           |             |         |         |    |           | conf: tiny.yml  |
|           |             |         |         |    |           | spec: 1C.2G.50G |
|           |             |         |         |    |           | version: '15'   |
+-----------+-------------+---------+---------+----+-----------+-----------------+
Are you sure you want to switchover cluster pg-test, demoting current master pg-test-1? [y/N]: y
2022-12-26 06:39:58.02468 Successfully switched over to "pg-test-2"
+ Cluster: pg-test (7181325041648035869) -----+----+-----------+-----------------+
| Member    | Host        | Role    | State   | TL | Lag in MB | Tags            |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-1 | 10.10.10.11 | Replica | stopped |    |   unknown | clonefrom: true |
|           |             |         |         |    |           | conf: tiny.yml  |
|           |             |         |         |    |           | spec: 1C.2G.50G |
|           |             |         |         |    |           | version: '15'   |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-2 | 10.10.10.12 | Leader  | running |  1 |           | clonefrom: true |
|           |             |         |         |    |           | conf: tiny.yml  |
|           |             |         |         |    |           | spec: 1C.2G.50G |
|           |             |         |         |    |           | version: '15'   |
+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-3 | 10.10.10.13 | Replica | running |  1 |         0 | clonefrom: true |
|           |             |         |         |    |           | conf: tiny.yml  |
|           |             |         |         |    |           | spec: 1C.2G.50G |
|           |             |         |         |    |           | version: '15'   |
+-----------+-------------+---------+---------+----+-----------+-----------------+

To do so with Patroni API (schedule a switchover from 2 to 1 at a specific time):

curl -u 'postgres:Patroni.API' \
  -d '{"leader":"pg-test-2", "candidate": "pg-test-1","scheduled_at":"2022-12-26T14:47+08"}' \
  -s -X POST http://10.10.10.11:8008/switchover

Backup Cluster

To create a backup with pgBackRest, run as local dbsu:

pg-backup                         # make a postgres base backup
pg-backup full                    # make a full backup
pg-backup diff                    # make a differential backup
pg-backup incr                    # make a incremental backup
pb info                           # check backup information

Check Backup & PITR for details.

Example: Make Backups

asciicast

Example: Create routine backup crontab

You can add crontab to node_crontab to specify your backup policy.

# make a full backup 1 am everyday
- '00 01 * * * postgres /pg/bin/pg-backup full'

# rotate backup: make a full backup on monday 1am, and an incremental backup during weekdays
- '00 01 * * 1 postgres /pg/bin/pg-backup full'
- '00 01 * * 2,3,4,5,6,7 postgres /pg/bin/pg-backup'

Restore Cluster

To restore a cluster to a previous time point (PITR), run as local dbsu:

pg-pitr -i                              # restore to the time of latest backup complete (not often used)
pg-pitr --time="2022-12-30 14:44:44+08" # restore to specific time point (in case of drop db, drop table)
pg-pitr --name="my-restore-point"       # restore TO a named restore point create by pg_create_restore_point
pg-pitr --lsn="0/7C82CB8" -X            # restore right BEFORE a LSN
pg-pitr --xid="1234567" -X -P           # restore right BEFORE a specific transaction id, then promote
pg-pitr --backup=latest                 # restore to latest backup set
pg-pitr --backup=20221108-105325        # restore to a specific backup set, which can be checked with pgbackrest info

And follow the instructions wizard, Check Backup & PITR for details.

Example: PITR with raw pgBackRest Command
# restore to the latest available point (e.g. hardware failure)
pgbackrest --stanza=pg-meta restore

# PITR to specific time point (e.g. drop table by accident)
pgbackrest --stanza=pg-meta --type=time --target="2022-11-08 10:58:48" \
   --target-action=promote restore

# restore specific backup point and then promote (or pause|shutdown)
pgbackrest --stanza=pg-meta --type=immediate --target-action=promote \
  --set=20221108-105325F_20221108-105938I restore

Adding Packages

To add newer version of RPM packages, you have to add them to repo_packages and repo_url_packages

Then rebuild repo on infra nodes with ./infra.yml -t repo_build subtask, Then you can install these packages with ansible module package:

ansible pg-test -b -m package -a "name=pg_cron_15,topn_15,pg_stat_monitor_15*"  # install some packages
Update Packages Manually
# add repo upstream on admin node, then download them manually
cd ~/pigsty; ./infra.yml -t repo_upstream,repo_cache # add upstream repo (internet)
cd /www/pigsty;  repotrack "some_new_package_name"   # download the latest RPMs

# re-create local repo on admin node, then refresh yum/apt cache on all nodes
cd ~/pigsty; ./infra.yml -t repo_create              # recreate local repo on admin node
./node.yml -t node_repo                              # refresh yum/apt cache on all nodes

# alternatives: clean and remake cache on all nodes with ansible command
ansible all -b -a 'yum clean all'                         # clean node repo cache
ansible all -b -a 'yum makecache'                         # remake cache from the new repo
ansible all -b -a 'apt clean'                             # clean node repo cache (Ubuntu/Debian)
ansible all -b -a 'apt update'                            # remake cache from the new repo (Ubuntu/Debian)

For example, you can then install or upgrade packages with:

ansible pg-test -b -m package -a "name=postgresql15* state=latest"

Install Extension

If you want to install extension on pg clusters, Add them to pg_extensions and make sure them installed with:

./pgsql.yml -t pg_extension     # install extensions

Some extension needs to be loaded in shared_preload_libraries, You can add them to pg_libs, or Config an existing cluster.

Finally, CREATE EXTENSION <extname>; on the cluster primary instance to install it.

Example: Install pg_cron on pg-test cluster
ansible pg-test -b -m package -a "name=pg_cron_15"          # install pg_cron packages on all nodes
# add pg_cron to shared_preload_libraries
pg edit-config --force -p shared_preload_libraries='timescaledb, pg_cron, pg_stat_statements, auto_explain'
pg restart --force pg-test                                  # restart cluster
psql -h pg-test -d postgres -c 'CREATE EXTENSION pg_cron;'  # install pg_cron on primary

Check PGSQL Extensions: Install for details.


Minor Upgrade

To perform a minor server version upgrade/downgrade, you have to add packages to yum/apt repo first.

Then perform a rolling upgrade/downgrade from all replicas, then switchover the cluster to upgrade the leader.

ansible <cls> -b -a "yum upgrade/downgrade -y <pkg>"    # upgrade/downgrade packages
pg restart --force <cls>                                # restart cluster
Example: Downgrade PostgreSQL 15.2 to 15.1

Add 15.1 packages to yum/apt repo and refresh node package manager cache:

cd ~/pigsty; ./infra.yml -t repo_upstream               # add upstream repo backup
cd /www/pigsty; repotrack postgresql15-*-15.1           # add 15.1 packages to yum repo
cd ~/pigsty; ./infra.yml -t repo_create                 # re-create repo
ansible pg-test -b -a 'yum clean all'                   # clean node repo cache (use apt in debian/ubuntu)
ansible pg-test -b -a 'yum makecache'                   # remake yum cache from the new repo

Perform a downgrade and restart the cluster:

ansible pg-test -b -a "yum downgrade -y postgresql15*"  # downgrade packages
pg restart --force pg-test                              # restart entire cluster to finish upgrade
Example: Upgrade PostgreSQL 15.1 back to 15.2

This time we upgrade in a rolling fashion:

ansible pg-test -b -a "yum upgrade -y postgresql15*"    # upgrade packages
ansible pg-test -b -a '/usr/pgsql/bin/pg_ctl --version' # check binary version is 15.2
pg restart --role replica --force pg-test               # restart replicas
pg switchover --leader pg-test-1 --candidate=pg-test-2 --scheduled=now --force pg-test    # switchover
pg restart --role primary --force pg-test               # restart primary

Major Upgrade

The simplest way to achieve a major version upgrade is to create a new cluster with the new version, then migration with logical replication & green/blue deployment.

You can also perform an in-place major upgrade, which is not recommended especially when certain extensions are installed. But it is possible.

Assume you want to upgrade PostgreSQL 14 to 15, you have to add packages to yum/apt repo, and guarantee the extensions has exact same version too.

./pgsql.yml -t pg_pkg -e pg_version=15                         # install packages for pg 15
sudo su - postgres; mkdir -p /data/postgres/pg-meta-15/data/   # prepare directories for 15
pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/postgres/pg-meta-14/data/ -D /data/postgres/pg-meta-15/data/ -v -c # preflight
pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/postgres/pg-meta-14/data/ -D /data/postgres/pg-meta-15/data/ --link -j8 -v -c
rm -rf /usr/pgsql; ln -s /usr/pgsql-15 /usr/pgsql;             # fix binary links 
mv /data/postgres/pg-meta-14 /data/postgres/pg-meta-15         # rename data directory
rm -rf /pg; ln -s /data/postgres/pg-meta-15 /pg                # fix data dir links

9 - Access Control

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

Pigsty has a battery-included access control model based on Role System and Privileges.


Role System

Pigsty has a default role system consist of four default roles and four default users

Role name Attributes Member of Description
dbrole_readonly NOLOGIN role for global read-only access
dbrole_readwrite NOLOGIN dbrole_readonly role for global read-write access
dbrole_admin NOLOGIN pg_monitor,dbrole_readwrite role for object creation
dbrole_offline NOLOGIN role for restricted read-only access
postgres SUPERUSER system superuser
replicator REPLICATION pg_monitor,dbrole_readonly system replicator
dbuser_dba SUPERUSER dbrole_admin pgsql admin user
dbuser_monitor pg_monitor pgsql monitor user
pg_default_roles:                 # default roles and users in postgres cluster
  - { name: dbrole_readonly  ,login: false ,comment: role for global read-only access     }
  - { name: dbrole_offline   ,login: false ,comment: role for restricted read-only access }
  - { name: dbrole_readwrite ,login: false ,roles: [dbrole_readonly] ,comment: role for global read-write access }
  - { name: dbrole_admin     ,login: false ,roles: [pg_monitor, dbrole_readwrite] ,comment: role for object creation }
  - { name: postgres     ,superuser: true  ,comment: system superuser }
  - { name: replicator ,replication: true  ,roles: [pg_monitor, dbrole_readonly] ,comment: system replicator }
  - { name: dbuser_dba   ,superuser: true  ,roles: [dbrole_admin]  ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 ,comment: pgsql admin user }
  - { name: dbuser_monitor ,roles: [pg_monitor] ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user }

Default Roles

There are four default roles in pigsty:

  • Read Only (dbrole_readonly): Role for global read-only access
  • Read Write (dbrole_readwrite): Role for global read-write access, inherits dbrole_readonly.
  • Admin (dbrole_admin): Role for DDL commands, inherits dbrole_readwrite.
  • Offline (dbrole_offline): Role for restricted read-only access (offline instance)

Default roles are defined in pg_default_roles, change default roles is not recommended.

- { name: dbrole_readonly  , login: false , comment: role for global read-only access  }                            # production read-only role
- { name: dbrole_offline ,   login: false , comment: role for restricted read-only access (offline instance) }      # restricted-read-only role
- { name: dbrole_readwrite , login: false , roles: [dbrole_readonly], comment: role for global read-write access }  # production read-write role
- { name: dbrole_admin , login: false , roles: [pg_monitor, dbrole_readwrite] , comment: role for object creation } # production DDL change role

Default Users

There are four default users in pigsty, too.

  • Superuser (postgres), the owner and creator of the cluster, same as the OS dbsu.
  • Replication user (replicator), the system user used for primary-replica.
  • Monitor user (dbuser_monitor), a user used to monitor database and connection pool metrics.
  • Admin user (dbuser_dba), the admin user who performs daily operations and database changes.

Default users’ username/password are defined with dedicate parameters (except for dbsu password):

!> Remember to change these password in production deployment !

pg_dbsu: postgres                             # os user for the database
pg_replication_username: replicator           # system replication user
pg_replication_password: DBUser.Replicator    # system replication password
pg_monitor_username: dbuser_monitor           # system monitor user
pg_monitor_password: DBUser.Monitor           # system monitor password
pg_admin_username: dbuser_dba                 # system admin user
pg_admin_password: DBUser.DBA                 # system admin password

To define extra options, specify them in pg_default_roles:

- { name: postgres     ,superuser: true                                          ,comment: system superuser }
- { name: replicator ,replication: true  ,roles: [pg_monitor, dbrole_readonly]   ,comment: system replicator }
- { name: dbuser_dba   ,superuser: true  ,roles: [dbrole_admin]  ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 , comment: pgsql admin user }
- { name: dbuser_monitor   ,roles: [pg_monitor, dbrole_readonly] ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user }

Privileges

Pigsty has a battery-included privilege model that works with default roles.

  • All users have access to all schemas.
  • Read-Only user can read from all tables. (SELECT, EXECUTE)
  • Read-Write user can write to all tables run DML. (INSERT, UPDATE, DELETE).
  • Admin user can create object and run DDL (CREATE, USAGE, TRUNCATE, REFERENCES, TRIGGER).
  • Offline user is Read-Only user with limited access on offline instance (pg_role = 'offline' or pg_offline_query = true)
  • Object created by admin users will have correct privilege.
  • Default privileges are installed on all databases, including template database.
  • Database connect privilege is covered by database definition
  • CREATE privileges of database & public schema are revoked from PUBLIC by default

Object Privilege

Default object privileges are defined in pg_default_privileges.

- GRANT USAGE      ON SCHEMAS   TO dbrole_readonly
- GRANT SELECT     ON TABLES    TO dbrole_readonly
- GRANT SELECT     ON SEQUENCES TO dbrole_readonly
- GRANT EXECUTE    ON FUNCTIONS TO dbrole_readonly
- GRANT USAGE      ON SCHEMAS   TO dbrole_offline
- GRANT SELECT     ON TABLES    TO dbrole_offline
- GRANT SELECT     ON SEQUENCES TO dbrole_offline
- GRANT EXECUTE    ON FUNCTIONS TO dbrole_offline
- GRANT INSERT     ON TABLES    TO dbrole_readwrite
- GRANT UPDATE     ON TABLES    TO dbrole_readwrite
- GRANT DELETE     ON TABLES    TO dbrole_readwrite
- GRANT USAGE      ON SEQUENCES TO dbrole_readwrite
- GRANT UPDATE     ON SEQUENCES TO dbrole_readwrite
- GRANT TRUNCATE   ON TABLES    TO dbrole_admin
- GRANT REFERENCES ON TABLES    TO dbrole_admin
- GRANT TRIGGER    ON TABLES    TO dbrole_admin
- GRANT CREATE     ON SCHEMAS   TO dbrole_admin

Newly created objects will have corresponding privileges when it is created by admin users

The \ddp+ may looks like:

Type Access privileges
function =X
dbrole_readonly=X
dbrole_offline=X
dbrole_admin=X
schema dbrole_readonly=U
dbrole_offline=U
dbrole_admin=UC
sequence dbrole_readonly=r
dbrole_offline=r
dbrole_readwrite=wU
dbrole_admin=rwU
table dbrole_readonly=r
dbrole_offline=r
dbrole_readwrite=awd
dbrole_admin=arwdDxt

Default Privilege

ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future. It does not affect privileges assigned to already-existing objects, and objects created by non-admin users.

Pigsty will use the following default privileges:

{% for priv in pg_default_privileges %}
ALTER DEFAULT PRIVILEGES FOR ROLE {{ pg_dbsu }} {{ priv }};
{% endfor %}

{% for priv in pg_default_privileges %}
ALTER DEFAULT PRIVILEGES FOR ROLE {{ pg_admin_username }} {{ priv }};
{% endfor %}

-- for additional business admin, they can SET ROLE to dbrole_admin
{% for priv in pg_default_privileges %}
ALTER DEFAULT PRIVILEGES FOR ROLE "dbrole_admin" {{ priv }};
{% endfor %}

Which will be rendered in pg-init-template.sql alone with ALTER DEFAULT PRIVILEGES statement for admin users.

These SQL command will be executed on postgres & template1 during cluster bootstrap, and newly created database will inherit it from tempalte1 by default.

That is to say, to maintain the correct object privilege, you have to run DDL with admin users, which could be:

  1. {{ pg_dbsu }}, postgres by default
  2. {{ pg_admin_username }}, dbuser_dba by default
  3. Business admin user granted with dbrole_admin

It’s wise to use postgres as global object owner to perform DDL changes. If you wish to create objects with business admin user, YOU MUST USE SET ROLE dbrole_admin before running that DDL to maintain the correct privileges.

You can also ALTER DEFAULT PRIVILEGE FOR ROLE <some_biz_admin> XXX to grant default privilege to business admin user, too.


Database Privilege

Database privilege is covered by database definition.

There are 3 database level privileges: CONNECT, CREATE, TEMP, and a special ‘privilege’: OWNERSHIP.

- name: meta         # required, `name` is the only mandatory field of a database definition
  owner: postgres    # optional, specify a database owner, {{ pg_dbsu }} by default
  allowconn: true    # optional, allow connection, true by default. false will disable connect at all
  revokeconn: false  # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)
  • If owner exists, it will be used as database owner instead of default {{ pg_dbsu }}
  • If revokeconn is false, all users have the CONNECT privilege of the database, this is the default behavior.
  • If revokeconn is set to true explicitly:
    • CONNECT privilege of the database will be revoked from PUBLIC
    • CONNECT privilege will be granted to {{ pg_replication_username }}, {{ pg_monitor_username }} and {{ pg_admin_username }}
    • CONNECT privilege will be granted to database owner with GRANT OPTION

revokeconn flag can be used for database access isolation, you can create different business users as the owners for each database and set the revokeconn option for all of them.

Example: Database Isolation
pg-infra:
  hosts:
    10.10.10.40: { pg_seq: 1, pg_role: primary }
    10.10.10.41: { pg_seq: 2, pg_role: replica , pg_offline_query: true }
  vars:
    pg_cluster: pg-infra
    pg_users:
      - { name: dbuser_confluence, password: mc2iohos , pgbouncer: true, roles: [ dbrole_admin ] }
      - { name: dbuser_gitlab, password: sdf23g22sfdd , pgbouncer: true, roles: [ dbrole_readwrite ] }
      - { name: dbuser_jira, password: sdpijfsfdsfdfs , pgbouncer: true, roles: [ dbrole_admin ] }
    pg_databases:
      - { name: confluence , revokeconn: true, owner: dbuser_confluence , connlimit: 100 }
      - { name: gitlab , revokeconn: true, owner: dbuser_gitlab, connlimit: 100 }
      - { name: jira , revokeconn: true, owner: dbuser_jira , connlimit: 100 }

Create Privilege

Pigsty revokes the CREATE privilege on database from PUBLIC by default, for security consideration. And this is the default behavior since PostgreSQL 15.

The database owner have the full capability to adjust these privileges as they see fit.

10 - Backup & PITR

How to perform base backup & PITR with pgBackRest?

Pigsty uses pgBackRest for PITR backup & restore.

In the case of a hardware failure, a physical replica failover could be the best choice. Whereas for data corruption scenarios (whether machine or human errors), Point-in-Time Recovery (PITR) is often more appropriate.

Backup

Use the following command to perform the backup:

# stanza name = {{ pg_cluster }} by default
pgbackrest --stanza=${stanza} --type=full|diff|incr backup

# you can also use the following command in pigsty (/pg/bin/pg-backup)
pg-backup       # make a backup, incr, or full backup if necessary
pg-backup full  # make a full backup
pg-backup diff  # make a differential backup
pg-backup incr  # make a incremental backup

Use the following command to print backup info:

pb info  # print backup info

You can also acquire backup info from the monitoring system: PGCAT Instance - Backup

Backup Info Example
$ pb info
stanza: pg-meta
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000010000000000000001/000000010000000000000023

        full backup: 20221108-105325F
            timestamp start/stop: 2022-11-08 10:53:25 / 2022-11-08 10:53:29
            wal start/stop: 000000010000000000000004 / 000000010000000000000004
            database size: 96.6MB, database backup size: 96.6MB
            repo1: backup set size: 18.9MB, backup size: 18.9MB

        incr backup: 20221108-105325F_20221108-105938I
            timestamp start/stop: 2022-11-08 10:59:38 / 2022-11-08 10:59:41
            wal start/stop: 00000001000000000000000F / 00000001000000000000000F
            database size: 246.7MB, database backup size: 167.3MB
            repo1: backup set size: 35.4MB, backup size: 20.4MB
            backup reference list: 20221108-105325F

Restore

Use the following command to perform restore

pg-pitr                                 # restore to wal archive stream end (e.g. used in case of entire DC failure)
pg-pitr -i                              # restore to the time of latest backup complete (not often used)
pg-pitr --time="2022-12-30 14:44:44+08" # restore to specific time point (in case of drop db, drop table)
pg-pitr --name="my-restore-point"       # restore TO a named restore point create by pg_create_restore_point
pg-pitr --lsn="0/7C82CB8" -X            # restore right BEFORE a LSN
pg-pitr --xid="1234567" -X -P           # restore right BEFORE a specific transaction id, then promote
pg-pitr --backup=latest                 # restore to latest backup set
pg-pitr --backup=20221108-105325        # restore to a specific backup set, which can be checked with pgbackrest info

pg-pitr                                 # pgbackrest --stanza=pg-meta restore
pg-pitr -i                              # pgbackrest --stanza=pg-meta --type=immediate restore
pg-pitr -t "2022-12-30 14:44:44+08"     # pgbackrest --stanza=pg-meta --type=time --target="2022-12-30 14:44:44+08" restore
pg-pitr -n "my-restore-point"           # pgbackrest --stanza=pg-meta --type=name --target=my-restore-point restore
pg-pitr -b 20221108-105325F             # pgbackrest --stanza=pg-meta --type=name --set=20221230-120101F restore
pg-pitr -l "0/7C82CB8" -X               # pgbackrest --stanza=pg-meta --type=lsn --target="0/7C82CB8" --target-exclusive restore
pg-pitr -x 1234567 -X -P                # pgbackrest --stanza=pg-meta --type=xid --target="0/7C82CB8" --target-exclusive --target-action=promote restore

The pg-pitr script will generate instructions for you to perform PITR.

For example, if you wish to rollback current cluster status back to "2023-02-07 12:38:00+08":

$ pg-pitr -t "2023-02-07 12:38:00+08"
pgbackrest --stanza=pg-meta --type=time --target='2023-02-07 12:38:00+08' restore
Perform time PITR on pg-meta
[1. Stop PostgreSQL] ===========================================
   1.1 Pause Patroni (if there are any replicas)
       $ pg pause <cls>  # pause patroni auto failover
   1.2 Shutdown Patroni
       $ pt-stop         # sudo systemctl stop patroni
   1.3 Shutdown Postgres
       $ pg-stop         # pg_ctl -D /pg/data stop -m fast

[2. Perform PITR] ===========================================
   2.1 Restore Backup
       $ pgbackrest --stanza=pg-meta --type=time --target='2023-02-07 12:38:00+08' restore
   2.2 Start PG to Replay WAL
       $ pg-start        # pg_ctl -D /pg/data start
   2.3 Validate and Promote
     - If database content is ok, promote it to finish recovery, otherwise goto 2.1
       $ pg-promote      # pg_ctl -D /pg/data promote

[3. Restart Patroni] ===========================================
   3.1 Start Patroni
       $ pt-start;        # sudo systemctl start patroni
   3.2 Enable Archive Again
       $ psql -c 'ALTER SYSTEM SET archive_mode = on; SELECT pg_reload_conf();'
   3.3 Restart Patroni
       $ pt-restart      # sudo systemctl start patroni

[4. Restore Cluster] ===========================================
   3.1 Re-Init All Replicas (if any replicas)
       $ pg reinit <cls> <ins>
   3.2 Resume Patroni
       $ pg resume <cls> # resume patroni auto failover
   3.2 Make Full Backup (optional)
       $ pg-backup full  # pgbackrest --stanza=pg-meta backup --type=full

Policy

You can customize your backup policy with node_crontab and pgbackrest_repo

local repo

For example, the default pg-meta will take a full backup every day at 1 am.

node_crontab:  # make a full backup 1 am everyday
  - '00 01 * * * postgres /pg/bin/pg-backup full'

With the default local repo retention policy, it will keep at most two full backups and temporarily allow three during backup.

pgbackrest_repo:                  # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
  local:                          # default pgbackrest repo with local posix fs
    path: /pg/backup              # local backup directory, `/pg/backup` by default
    retention_full_type: count    # retention full backups by count
    retention_full: 2             # keep 2, at most 3 full backup when using local fs repo

Your backup disk storage should be at least three x database file size + WAL archive in 3 days.

MinIO repo

When using MinIO, storage capacity is usually not a problem. You can keep backups as long as you want.

For example, the default pg-test will take a full backup on Monday and incr backup on other weekdays.

node_crontab:  # make a full backup 1 am everyday
  - '00 01 * * 1 postgres /pg/bin/pg-backup full'
  - '00 01 * * 2,3,4,5,6,7 postgres /pg/bin/pg-backup'

And with a 14-day time retention policy, backup in the last two weeks will be kept. But beware, this guarantees a week’s PITR period only.

pgbackrest_repo:                  # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository=
  minio:                          # optional minio repo for pgbackrest
    type: s3                      # minio is s3-compatible, so s3 is used
    s3_endpoint: sss.pigsty       # minio endpoint domain name, `sss.pigsty` by default
    s3_region: us-east-1          # minio region, us-east-1 by default, useless for minio
    s3_bucket: pgsql              # minio bucket name, `pgsql` by default
    s3_key: pgbackrest            # minio user access key for pgbackrest
    s3_key_secret: S3User.Backup  # minio user secret key for pgbackrest
    s3_uri_style: path            # use path style uri for minio rather than host style
    path: /pgbackrest             # minio backup path, default is `/pgbackrest`
    storage_port: 9000            # minio port, 9000 by default
    storage_ca_file: /etc/pki/ca.crt  # minio ca file path, `/etc/pki/ca.crt` by default
    bundle: y                     # bundle small files into a single file
    cipher_type: aes-256-cbc      # enable AES encryption for remote backup repo
    cipher_pass: pgBackRest       # AES encryption password, default is 'pgBackRest'
    retention_full_type: time     # retention full backup by time on minio repo
    retention_full: 14            # keep full backup for last 14 days

11 - Migration

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

Pigsty has a built-in playbook pgsql-migration.yml to perform online database migration based on logical replication.

With proper automation, the downtime could be minimized to several seconds. But beware that logical replication requires PostgreSQL 10+ to work. You can still use the facility here and use a pg_dump | psql instead of logical replication.


Define a Migration Task

You have to create a migration task definition file to use this playbook.

Check files/migration/pg-meta.yml for example.

It will try to migrate the pg-meta.meta to pg-test.test.

pg-meta-1	10.10.10.10  --> pg-test-1	10.10.10.11 (10.10.10.12,10.10.10.13)

You have to tell pigsty where is the source cluster and destination cluster. The database to be migrated, and the primary IP address.

You should have superuser privileges on both sides to proceed

You can overwrite the superuser connection to the source cluster with src_pg, and logical replication connection string with sub_conn, Otherwise, pigsty default admin & replicator credentials will be used.

---
#-----------------------------------------------------------------
# PG_MIGRATION
#-----------------------------------------------------------------
context_dir: ~/migration           # migration manuals & scripts
#-----------------------------------------------------------------
# SRC Cluster (The OLD Cluster)
#-----------------------------------------------------------------
src_cls: pg-meta      # src cluster name         <REQUIRED>
src_db: meta          # src database name        <REQUIRED>
src_ip: 10.10.10.10   # src cluster primary ip   <REQUIRED>
#src_pg: ''            # if defined, use this as src dbsu pgurl instead of:
#                      # postgres://{{ pg_admin_username }}@{{ src_ip }}/{{ src_db }}
#                      # e.g. 'postgres://dbuser_dba:DBUser.DBA@10.10.10.10:5432/meta'
#sub_conn: ''          # if defined, use this as subscription connstr instead of:
#                      # host={{ src_ip }} dbname={{ src_db }} user={{ pg_replication_username }}'
#                      # e.g. 'host=10.10.10.10 dbname=meta user=replicator password=DBUser.Replicator'
#-----------------------------------------------------------------
# DST Cluster (The New Cluster)
#-----------------------------------------------------------------
dst_cls: pg-test      # dst cluster name         <REQUIRED>
dst_db: test          # dst database name        <REQUIRED>
dst_ip: 10.10.10.11   # dst cluster primary ip   <REQUIRED>
#dst_pg: ''            # if defined, use this as dst dbsu pgurl instead of:
#                      # postgres://{{ pg_admin_username }}@{{ dst_ip }}/{{ dst_db }}
#                      # e.g. 'postgres://dbuser_dba:DBUser.DBA@10.10.10.11:5432/test'
#-----------------------------------------------------------------
# PGSQL
#-----------------------------------------------------------------
pg_dbsu: postgres
pg_replication_username: replicator
pg_replication_password: DBUser.Replicator
pg_admin_username: dbuser_dba
pg_admin_password: DBUser.DBA
pg_monitor_username: dbuser_monitor
pg_monitor_password: DBUser.Monitor
#-----------------------------------------------------------------
...

Generate Migration Plan

The playbook does not migrate src to dst, but it will generate everything your need to do so.

After the execution, you will find migration context dir under ~/migration/pg-meta.meta by default

Following the README.md and executing these scripts one by one, you will do the trick!

# this script will setup migration context with env vars
. ~/migration/pg-meta.meta/activate

# these scripts are used for check src cluster status
# and help generating new cluster definition in pigsty
./check-user     # check src users
./check-db       # check src databases
./check-hba      # check src hba rules
./check-repl     # check src replica identities
./check-misc     # check src special objects

# these scripts are used for building logical replication
# between existing src cluster and pigsty managed dst cluster
# schema, data will be synced in realtime, except for sequences
./copy-schema    # copy schema to dest
./create-pub     # create publication on src
./create-sub     # create subscription on dst
./copy-progress  # print logical replication progress
./copy-diff      # quick src & dst diff by counting tables

# these scripts will run in an online migration, which will
# stop src cluster, copy sequence numbers (which is not synced with logical replication)
# you have to reroute you app traffic according to your access method (dns,vip,haproxy,pgbouncer,etc...)
# then perform cleanup to drop subscription and publication
./copy-seq [n]   # sync sequence numbers, if n is given, an additional shift will applied
#./disable-src   # restrict src cluster access to admin node & new cluster (YOUR IMPLEMENTATION)
#./re-routing    # ROUTING APPLICATION TRAFFIC FROM SRC TO DST!            (YOUR IMPLEMENTATION)
./drop-sub       # drop subscription on dst after migration
./drop-pub       # drop publication on src after migration

Caveats

You can use ./copy-seq 1000 to advance all sequences by a number (e.g. 1000) after syncing sequences. Which may prevent potential serial primary key conflict in new clusters.

You have to implement your own ./re-routing script to route your application traffic from src to dst. Since we don’t know how your traffic is routed (e.g dns, VIP, haproxy, or pgbouncer). Of course, you can always do that by hand…

You have to implement your own ./disable-src script to restrict the src cluster. You can do that by changing HBA rules & reload (recommended), or just shutting down postgres, pgbouncer, or haproxy…

12 - Monitoring

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

Overview

Pigsty uses the modern observability stack for PostgreSQL monitoring:

  • Grafana for metrics visualization and PostgreSQL datasource.
  • Prometheus for PostgreSQL / Pgbouncer / Patroni / HAProxy / Node metrics
  • Loki for PostgreSQL / Pgbouncer / Patroni / pgBackRest logs
  • Battery-Include dashboards for PostgreSQL and everything else

Metrics

PostgreSQL’s metrics are defined by collector files: pg_exporter.yml. Prometheus record rules and alert evaluation will further process it: files/prometheus/rules/pgsql.yml

There are three identity labels: cls, ins, ip, which will be attached to all metrics & logs. node & haproxy will try to reuse the same identity to provide consistent metrics & logs.

{ cls: pg-meta, ins: pg-meta-1, ip: 10.10.10.10 }
{ cls: pg-meta, ins: pg-test-1, ip: 10.10.10.11 }
{ cls: pg-meta, ins: pg-test-2, ip: 10.10.10.12 }
{ cls: pg-meta, ins: pg-test-3, ip: 10.10.10.13 }

Logs

PostgreSQL-related logs are collected by promtail and sent to Loki on infra nodes by default.

Targets

Prometheus monitoring targets are defined in static files under /etc/prometheus/targets/pgsql/. Each instance will have a corresponding file. Take pg-meta-1 as an example:

# pg-meta-1 [primary] @ 10.10.10.10
- labels: { cls: pg-meta, ins: pg-meta-1, ip: 10.10.10.10 }
  targets:
    - 10.10.10.10:9630    # <--- pg_exporter for PostgreSQL metrics
    - 10.10.10.10:9631    # <--- pg_exporter for Pgbouncer metrics
    - 10.10.10.10:8008    # <--- patroni metrics

When the global flag patroni_ssl_enabled is set, the patroni target will be managed as /etc/prometheus/targets/patroni/<ins>.yml because it requires a different scrape endpoint (https).

Prometheus monitoring target will be removed when a cluster is removed by bin/pgsql-rm or pgsql-rm.yml. You can use playbook subtasks, or remove them manually:

bin/pgmon-rm <ins>      # remove prometheus targets from all infra nodes

Remote RDS targets are managed as /etc/prometheus/targets/pgrds/<cls>.yml. It will be created by the pgsql-monitor.yml playbook or bin/pgmon-add script.


Monitor Mode

There are three ways to monitor PostgreSQL instances in Pigsty:

Item \ Level L1 L2 L3
Name Remote Database Service Existing Deployment Fully Managed Deployment
Abbr RDS MANAGED FULL
Scenes connect string URL only ssh-sudo-able Instances created by Pigsty
PGCAT Functionality ✅ Full Availability ✅ Full Availability ✅ Full Availability
PGSQL Functionality ✅ PG metrics only ✅ PG and node metrics ✅ Full Support
Connection Pool Metrics ❌ Not available ⚠️ Optional ✅ Pre-Configured
Load Balancer Metrics ❌ Not available ⚠️ Optional ✅ Pre-Configured
PGLOG Functionality ❌ Not Available ⚠️ Optional ⚠️ Optional
PG Exporter ⚠️ On infra nodes ✅ On DB nodes ✅ On DB nodes
Node Exporter ❌ Not Deployed ✅ On DB nodes ✅ On DB nodes
Intrusion into DB nodes ✅ Non-Intrusive ⚠️ Installing Exporter ⚠️ Fully Managed by Pigsty
Instance Already Exists ✅ Yes ✅ Yes ⚠️ Created by Pigsty
Monitoring users and views ⚠️Manually Setup ⚠️Manually Setup ✅ Auto configured
Deployment Usage Playbook bin/pgmon-add <cls> subtasks of pgsql.ym/node.yml pgsql.yml
Required Privileges connectable PGURL from infra nodes DB node ssh and sudo privileges DB node ssh and sudo privileges
Function Overview PGCAT + PGRDS Most Functionality Full Functionality

Monitor Existing Cluster

Suppose the target DB node can be managed by Pigsty (accessible via ssh and sudo is available). In that case, you can use the pg_exporter task in the pgsql.yml playbook to deploy the monitoring component PG Exporter on the target node in the same manner as a standard deployment.

You can also deploy the connection pool and its monitoring on existing instance nodes using the pgbouncer and pgbouncer_exporter tasks from the same playbook. Additionally, you can deploy host monitoring, load balancing, and log collection components using the node_exporter, haproxy, and promtail tasks from the node.yml playbook, achieving a similar user experience with the native Pigsty cluster.

The definition method for existing clusters is very similar to the normal clusters managed by Pigsty. Selectively run certain tasks from the pgsql.yml playbook instead of running the entire playbook.

./node.yml  -l <cls> -t node_repo,node_pkg           # Add YUM sources for INFRA nodes on host nodes and install packages.
./node.yml  -l <cls> -t node_exporter,node_register  # Configure host monitoring and add to Prometheus.
./node.yml  -l <cls> -t promtail                     # Configure host log collection and send to Loki.
./pgsql.yml -l <cls> -t pg_exporter,pg_register      # Configure PostgreSQL monitoring and register with Prometheus/Grafana.

Since the target database cluster already exists, you must manually setup monitoring users, schemas, and extensions on the target database cluster.


Monitor RDS

If you can only access the target database via PGURL (database connection string), you can refer to the instructions here for configuration. In this mode, Pigsty deploys the corresponding PG Exporter on the INFRA node to fetch metrics from the remote database, as shown below:

------ infra ------
|                 |
|   prometheus    |            v---- pg-foo-1 ----v
|       ^         |  metrics   |         ^        |
|   pg_exporter <-|------------|----  postgres    |
|   (port: 20001) |            | 10.10.10.10:5432 |
|       ^         |            ^------------------^
|       ^         |                      ^
|       ^         |            v---- pg-foo-2 ----v
|       ^         |  metrics   |         ^        |
|   pg_exporter <-|------------|----  postgres    |
|   (port: 20002) |            | 10.10.10.11:5433 |
-------------------            ^------------------^

The monitoring system will no longer have host/pooler/load balancer metrics. But the PostgreSQL metrics & catalog info are still available. Pigsty has two dedicated dashboards for that: PGRDS Cluster and PGRDS Instance. Overview and Database level dashboards are reused. Since Pigsty cannot manage your RDS, you have to setup monitor on the target database in advance.

Below, we use a sandbox environment as an example: now we assume that the pg-meta cluster is an RDS instance pg-foo-1 to be monitored, and the pg-test cluster is an RDS cluster pg-bar to be monitored:

  1. Create monitoring schemas, users, and permissions on the target. Refer to Monitoring Object Configuration for details.

  2. Declare the cluster in the configuration list. For example, suppose we want to monitor the “remote” pg-meta & pg-test clusters:

    infra:            # Infra cluster for proxies, monitoring, alerts, etc.
      hosts: { 10.10.10.10: { infra_seq: 1 } }
      vars:           # Install pg_exporter on 'infra' group for remote postgres RDS
        pg_exporters: # List all remote instances here, assign a unique unused local port for k
          20001: { pg_cluster: pg-foo, pg_seq: 1, pg_host: 10.10.10.10 , pg_databases: [{ name: meta }] } # Register meta database as Grafana data source
    
          20002: { pg_cluster: pg-bar, pg_seq: 1, pg_host: 10.10.10.11 , pg_port: 5432 } # Several different connection string concatenation methods
          20003: { pg_cluster: pg-bar, pg_seq: 2, pg_host: 10.10.10.12 , pg_exporter_url: 'postgres://dbuser_monitor:DBUser.Monitor@10.10.10.12:5432/postgres?sslmode=disable'}
          20004: { pg_cluster: pg-bar, pg_seq: 3, pg_host: 10.10.10.13 , pg_monitor_username: dbuser_monitor, pg_monitor_password: DBUser.Monitor }
    

    The databases listed in the pg_databases field will be registered in Grafana as a PostgreSQL data source, providing data support for the PGCAT monitoring panel. If you don’t want to use PGCAT and register the database in Grafana, set pg_databases to an empty array or leave it blank.

    pigsty-monitor.jpg

  3. Execute the command to add monitoring: bin/pgmon-add <clsname>

    bin/pgmon-add pg-foo  # Bring the pg-foo cluster into monitoring
    bin/pgmon-add pg-bar  # Bring the pg-bar cluster into monitoring
    
  4. To remove a remote cluster from monitoring, use bin/pgmon-rm <clsname>

    bin/pgmon-rm pg-foo  # Remove pg-foo from Pigsty monitoring
    bin/pgmon-rm pg-bar  # Remove pg-bar from Pigsty monitoring
    

You can use more parameters to override the default pg_exporter options. Here is an example for monitoring Aliyun RDS and PolarDB with Pigsty:

Example: Monitor Aliyun RDS PG & PolarDB

Check remote.yml config for details.

infra:            # infra cluster for proxy, monitor, alert, etc..
  hosts: { 10.10.10.10: { infra_seq: 1 } }
  vars:           # install pg_exporter for remote postgres RDS on a group 'infra'
    pg_exporters: # list all remote instances here, alloc a unique unused local port as k
      20001: { pg_cluster: pg-foo, pg_seq: 1, pg_host: 10.10.10.10 }
      20002: { pg_cluster: pg-bar, pg_seq: 1, pg_host: 10.10.10.11 , pg_port: 5432 }
      20003: { pg_cluster: pg-bar, pg_seq: 2, pg_host: 10.10.10.12 , pg_exporter_url: 'postgres://dbuser_monitor:DBUser.Monitor@10.10.10.12:5432/postgres?sslmode=disable'}
      20004: { pg_cluster: pg-bar, pg_seq: 3, pg_host: 10.10.10.13 , pg_monitor_username: dbuser_monitor, pg_monitor_password: DBUser.Monitor }

      20011:
        pg_cluster: pg-polar                        # RDS Cluster Name (Identity, Explicitly Assigned, used as 'cls')
        pg_seq: 1                                   # RDS Instance Seq (Identity, Explicitly Assigned, used as part of 'ins')
        pg_host: pxx.polardbpg.rds.aliyuncs.com     # RDS Host Address
        pg_port: 1921                               # RDS Port
        pg_exporter_include_database: 'test'        # Only monitoring database in this list
        pg_monitor_username: dbuser_monitor         # monitor username, overwrite default
        pg_monitor_password: DBUser_Monitor         # monitor password, overwrite default
        pg_databases: [{ name: test }]              # database to be added to grafana datasource

      20012:
        pg_cluster: pg-polar                        # RDS Cluster Name (Identity, Explicitly Assigned, used as 'cls')
        pg_seq: 2                                   # RDS Instance Seq (Identity, Explicitly Assigned, used as part of 'ins')
        pg_host: pe-xx.polarpgmxs.rds.aliyuncs.com  # RDS Host Address
        pg_port: 1521                               # RDS Port
        pg_databases: [{ name: test }]              # database to be added to grafana datasource

      20014:
        pg_cluster: pg-rds
        pg_seq: 1
        pg_host: pgm-xx.pg.rds.aliyuncs.com
        pg_port: 5432
        pg_exporter_auto_discovery: true
        pg_exporter_include_database: 'rds'
        pg_monitor_username: dbuser_monitor
        pg_monitor_password: DBUser_Monitor
        pg_databases: [ { name: rds } ]

      20015:
        pg_cluster: pg-rdsha
        pg_seq: 1
        pg_host: pgm-2xx8wu.pg.rds.aliyuncs.com
        pg_port: 5432
        pg_exporter_auto_discovery: true
        pg_exporter_include_database: 'rds'
        pg_databases: [{ name: test }, {name: rds}]

      20016:
        pg_cluster: pg-rdsha
        pg_seq: 2
        pg_host: pgr-xx.pg.rds.aliyuncs.com
        pg_exporter_auto_discovery: true
        pg_exporter_include_database: 'rds'
        pg_databases: [{ name: test }, {name: rds}]

Monitor Setup

When you want to monitor existing instances, whether it’s RDS or a self-built PostgreSQL instance, you need to make some configurations on the target database so that Pigsty can access them.

To bring an external existing PostgreSQL instance into monitoring, you need a connection string that can access that instance/cluster. Any accessible connection string (business user, superuser) can be used, but we recommend using a dedicated monitoring user to avoid permission leaks.

  • Monitor User: The default username used is dbuser_monitor. This user belongs to the pg_monitor group, or ensure it has the necessary view permissions.
  • Monitor HBA: Default password is DBUser.Monitor. You need to ensure that the HBA policy allows the monitoring user to access the database from the infra nodes.
  • Monitor Schema: It’s optional but recommended to create a dedicate schema monitor for monitoring views and extensions.
  • Monitor Extension:It is strongly recommended to enable the built-in extension pg_stat_statements.
  • Monitor View: Monitoring views are optional but can provide additional metrics. Which is recommended.

Monitor User

Create a monitor user on the target database cluster. For example, dbuser_monitor is used by default in Pigsty.

CREATE USER dbuser_monitor;                                       -- create the monitor user
COMMENT ON ROLE dbuser_monitor IS 'system monitor user';          -- comment the monitor user
GRANT pg_monitor TO dbuser_monitor;                               -- grant system role pg_monitor to monitor user

ALTER USER dbuser_monitor PASSWORD 'DBUser.Monitor';              -- set password for monitor user
ALTER USER dbuser_monitor SET log_min_duration_statement = 1000;  -- set this to avoid log flooding
ALTER USER dbuser_monitor SET search_path = monitor,public;       -- set this to avoid pg_stat_statements extension not working

The monitor user here should have consistent pg_monitor_username and pg_monitor_password with Pigsty config inventory.


Monitor HBA

You also need to configure pg_hba.conf to allow monitoring user access from infra/admin nodes.

# allow local role monitor with password
local   all  dbuser_monitor                    md5
host    all  dbuser_monitor  127.0.0.1/32      md5
host    all  dbuser_monitor  <admin_ip>/32     md5
host    all  dbuser_monitor  <infra_ip>/32     md5

If your RDS does not support the RAW HBA format, add admin/infra node IP to the whitelist.


Monitor Schema

Monitor schema is optional, but we strongly recommend creating one.

CREATE SCHEMA IF NOT EXISTS monitor;               -- create dedicate monitor schema
GRANT USAGE ON SCHEMA monitor TO dbuser_monitor;   -- allow monitor user to use this schema

Monitor Extension

Monitor extension is optional, but we strongly recommend enabling pg_stat_statements extension.

Note that this extension must be listed in shared_preload_libraries to take effect, and changing this parameter requires a database restart.

CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "monitor";

You should create this extension inside the admin database: postgres. If your RDS does not grant CREATE on the database postgres. You can create that extension in the default public schema:

CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";
ALTER USER dbuser_monitor SET search_path = monitor,public;

As long as your monitor user can access pg_stat_statements view without schema qualification, it should be fine.


Monitor View

It’s recommended to create the monitor views in all databases that need to be monitored.

Monitor Schema & View Definition
----------------------------------------------------------------------
-- Table bloat estimate : monitor.pg_table_bloat
----------------------------------------------------------------------
DROP VIEW IF EXISTS monitor.pg_table_bloat CASCADE;
CREATE OR REPLACE VIEW monitor.pg_table_bloat AS
SELECT CURRENT_CATALOG AS datname, nspname, relname , tblid , bs * tblpages AS size,
       CASE WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff)/tblpages::FLOAT ELSE 0 END AS ratio
FROM (
         SELECT ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
                tblpages, fillfactor, bs, tblid, nspname, relname, is_na
         FROM (
                  SELECT
                      ( 4 + tpl_hdr_size + tpl_data_size + (2 * ma)
                          - CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END
                          - CASE WHEN ceil(tpl_data_size)::INT % ma = 0 THEN ma ELSE ceil(tpl_data_size)::INT % ma END
                          ) AS tpl_size, (heappages + toastpages) AS tblpages, heappages,
                      toastpages, reltuples, toasttuples, bs, page_hdr, tblid, nspname, relname, fillfactor, is_na
                  FROM (
                           SELECT
                               tbl.oid AS tblid, ns.nspname , tbl.relname, tbl.reltuples,
                               tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
                               coalesce(toast.reltuples, 0) AS toasttuples,
                               coalesce(substring(array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
                               current_setting('block_size')::numeric AS bs,
                               CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
                               24 AS page_hdr,
                               23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END
                                   + CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
                               sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size,
                               bool_or(att.atttypid = 'pg_catalog.name'::regtype)
                                   OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na
                           FROM pg_attribute AS att
                                    JOIN pg_class AS tbl ON att.attrelid = tbl.oid
                                    JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
                                    LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
                                    LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
                           WHERE NOT att.attisdropped AND tbl.relkind = 'r' AND nspname NOT IN ('pg_catalog','information_schema')
                           GROUP BY 1,2,3,4,5,6,7,8,9,10
                       ) AS s
              ) AS s2
     ) AS s3
WHERE NOT is_na;
COMMENT ON VIEW monitor.pg_table_bloat IS 'postgres table bloat estimate';

GRANT SELECT ON monitor.pg_table_bloat TO pg_monitor;

----------------------------------------------------------------------
-- Index bloat estimate : monitor.pg_index_bloat
----------------------------------------------------------------------
DROP VIEW IF EXISTS monitor.pg_index_bloat CASCADE;
CREATE OR REPLACE VIEW monitor.pg_index_bloat AS
SELECT CURRENT_CATALOG AS datname, nspname, idxname AS relname, tblid, idxid, relpages::BIGINT * bs AS size,
       COALESCE((relpages - ( reltuples * (6 + ma - (CASE WHEN index_tuple_hdr % ma = 0 THEN ma ELSE index_tuple_hdr % ma END)
                                               + nulldatawidth + ma - (CASE WHEN nulldatawidth % ma = 0 THEN ma ELSE nulldatawidth % ma END))
                                  / (bs - pagehdr)::FLOAT  + 1 )), 0) / relpages::FLOAT AS ratio
FROM (
         SELECT nspname,idxname,indrelid AS tblid,indexrelid AS idxid,
                reltuples,relpages,
                current_setting('block_size')::INTEGER                                                               AS bs,
                (CASE WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END)  AS ma,
                24                                                                                                   AS pagehdr,
                (CASE WHEN max(COALESCE(pg_stats.null_frac, 0)) = 0 THEN 2 ELSE 6 END)                               AS index_tuple_hdr,
                sum((1.0 - COALESCE(pg_stats.null_frac, 0.0)) *
                    COALESCE(pg_stats.avg_width, 1024))::INTEGER                                                     AS nulldatawidth
         FROM pg_attribute
                  JOIN (
             SELECT pg_namespace.nspname,
                    ic.relname                                                   AS idxname,
                    ic.reltuples,
                    ic.relpages,
                    pg_index.indrelid,
                    pg_index.indexrelid,
                    tc.relname                                                   AS tablename,
                    regexp_split_to_table(pg_index.indkey::TEXT, ' ') :: INTEGER AS attnum,
                    pg_index.indexrelid                                          AS index_oid
             FROM pg_index
                      JOIN pg_class ic ON pg_index.indexrelid = ic.oid
                      JOIN pg_class tc ON pg_index.indrelid = tc.oid
                      JOIN pg_namespace ON pg_namespace.oid = ic.relnamespace
                      JOIN pg_am ON ic.relam = pg_am.oid
             WHERE pg_am.amname = 'btree' AND ic.relpages > 0 AND nspname NOT IN ('pg_catalog', 'information_schema')
         ) ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum
                  JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname
             AND ((pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE))
                 OR (pg_stats.tablename = ind_atts.idxname AND pg_stats.attname = pg_attribute.attname))
         WHERE pg_attribute.attnum > 0
         GROUP BY 1, 2, 3, 4, 5, 6
     ) est;
COMMENT ON VIEW monitor.pg_index_bloat IS 'postgres index bloat estimate (btree-only)';

GRANT SELECT ON monitor.pg_index_bloat TO pg_monitor;

----------------------------------------------------------------------
-- Relation Bloat : monitor.pg_bloat
----------------------------------------------------------------------
DROP VIEW IF EXISTS monitor.pg_bloat CASCADE;
CREATE OR REPLACE VIEW monitor.pg_bloat AS
SELECT coalesce(ib.datname, tb.datname)                                                   AS datname,
       coalesce(ib.nspname, tb.nspname)                                                   AS nspname,
       coalesce(ib.tblid, tb.tblid)                                                       AS tblid,
       coalesce(tb.nspname || '.' || tb.relname, ib.nspname || '.' || ib.tblid::RegClass) AS tblname,
       tb.size                                                                            AS tbl_size,
       CASE WHEN tb.ratio < 0 THEN 0 ELSE round(tb.ratio::NUMERIC, 6) END                 AS tbl_ratio,
       (tb.size * (CASE WHEN tb.ratio < 0 THEN 0 ELSE tb.ratio::NUMERIC END)) ::BIGINT    AS tbl_wasted,
       ib.idxid,
       ib.nspname || '.' || ib.relname                                                    AS idxname,
       ib.size                                                                            AS idx_size,
       CASE WHEN ib.ratio < 0 THEN 0 ELSE round(ib.ratio::NUMERIC, 5) END                 AS idx_ratio,
       (ib.size * (CASE WHEN ib.ratio < 0 THEN 0 ELSE ib.ratio::NUMERIC END)) ::BIGINT    AS idx_wasted
FROM monitor.pg_index_bloat ib
         FULL OUTER JOIN monitor.pg_table_bloat tb ON ib.tblid = tb.tblid;

COMMENT ON VIEW monitor.pg_bloat IS 'postgres relation bloat detail';
GRANT SELECT ON monitor.pg_bloat TO pg_monitor;

----------------------------------------------------------------------
-- monitor.pg_index_bloat_human
----------------------------------------------------------------------
DROP VIEW IF EXISTS monitor.pg_index_bloat_human CASCADE;
CREATE OR REPLACE VIEW monitor.pg_index_bloat_human AS
SELECT idxname                            AS name,
       tblname,
       idx_wasted                         AS wasted,
       pg_size_pretty(idx_size)           AS idx_size,
       round(100 * idx_ratio::NUMERIC, 2) AS idx_ratio,
       pg_size_pretty(idx_wasted)         AS idx_wasted,
       pg_size_pretty(tbl_size)           AS tbl_size,
       round(100 * tbl_ratio::NUMERIC, 2) AS tbl_ratio,
       pg_size_pretty(tbl_wasted)         AS tbl_wasted
FROM monitor.pg_bloat
WHERE idxname IS NOT NULL;
COMMENT ON VIEW monitor.pg_index_bloat_human IS 'postgres index bloat info in human-readable format';
GRANT SELECT ON monitor.pg_index_bloat_human TO pg_monitor;


----------------------------------------------------------------------
-- monitor.pg_table_bloat_human
----------------------------------------------------------------------
DROP VIEW IF EXISTS monitor.pg_table_bloat_human CASCADE;
CREATE OR REPLACE VIEW monitor.pg_table_bloat_human AS
SELECT tblname                                          AS name,
       idx_wasted + tbl_wasted                          AS wasted,
       pg_size_pretty(idx_wasted + tbl_wasted)          AS all_wasted,
       pg_size_pretty(tbl_wasted)                       AS tbl_wasted,
       pg_size_pretty(tbl_size)                         AS tbl_size,
       tbl_ratio,
       pg_size_pretty(idx_wasted)                       AS idx_wasted,
       pg_size_pretty(idx_size)                         AS idx_size,
       round(idx_wasted::NUMERIC * 100.0 / idx_size, 2) AS idx_ratio
FROM (SELECT datname,
             nspname,
             tblname,
             coalesce(max(tbl_wasted), 0)                         AS tbl_wasted,
             coalesce(max(tbl_size), 1)                           AS tbl_size,
             round(100 * coalesce(max(tbl_ratio), 0)::NUMERIC, 2) AS tbl_ratio,
             coalesce(sum(idx_wasted), 0)                         AS idx_wasted,
             coalesce(sum(idx_size), 1)                           AS idx_size
      FROM monitor.pg_bloat
      WHERE tblname IS NOT NULL
      GROUP BY 1, 2, 3
     ) d;
COMMENT ON VIEW monitor.pg_table_bloat_human IS 'postgres table bloat info in human-readable format';
GRANT SELECT ON monitor.pg_table_bloat_human TO pg_monitor;


----------------------------------------------------------------------
-- Activity Overview: monitor.pg_session
----------------------------------------------------------------------
DROP VIEW IF EXISTS monitor.pg_session CASCADE;
CREATE OR REPLACE VIEW monitor.pg_session AS
SELECT coalesce(datname, 'all') AS datname, numbackends, active, idle, ixact, max_duration, max_tx_duration, max_conn_duration
FROM (
         SELECT datname,
                count(*)                                         AS numbackends,
                count(*) FILTER ( WHERE state = 'active' )       AS active,
                count(*) FILTER ( WHERE state = 'idle' )         AS idle,
                count(*) FILTER ( WHERE state = 'idle in transaction'
                    OR state = 'idle in transaction (aborted)' ) AS ixact,
                max(extract(epoch from now() - state_change))
                FILTER ( WHERE state = 'active' )                AS max_duration,
                max(extract(epoch from now() - xact_start))      AS max_tx_duration,
                max(extract(epoch from now() - backend_start))   AS max_conn_duration
         FROM pg_stat_activity
         WHERE backend_type = 'client backend'
           AND pid <> pg_backend_pid()
         GROUP BY ROLLUP (1)
         ORDER BY 1 NULLS FIRST
     ) t;
COMMENT ON VIEW monitor.pg_session IS 'postgres activity group by session';
GRANT SELECT ON monitor.pg_session TO pg_monitor;


----------------------------------------------------------------------
-- Sequential Scan: monitor.pg_seq_scan
----------------------------------------------------------------------
DROP VIEW IF EXISTS monitor.pg_seq_scan CASCADE;
CREATE OR REPLACE VIEW monitor.pg_seq_scan AS
SELECT schemaname                                                        AS nspname,
       relname,
       seq_scan,
       seq_tup_read,
       seq_tup_read / seq_scan                                           AS seq_tup_avg,
       idx_scan,
       n_live_tup + n_dead_tup                                           AS tuples,
       round(n_live_tup * 100.0::NUMERIC / (n_live_tup + n_dead_tup), 2) AS live_ratio
FROM pg_stat_user_tables
WHERE seq_scan > 0
  and (n_live_tup + n_dead_tup) > 0
ORDER BY seq_scan DESC;
COMMENT ON VIEW monitor.pg_seq_scan IS 'table that have seq scan';
GRANT SELECT ON monitor.pg_seq_scan TO pg_monitor;
Shmem allocation for PostgreSQL 13+
DROP FUNCTION IF EXISTS monitor.pg_shmem() CASCADE;
CREATE OR REPLACE FUNCTION monitor.pg_shmem() RETURNS SETOF
    pg_shmem_allocations AS $$ SELECT * FROM pg_shmem_allocations;$$ LANGUAGE SQL SECURITY DEFINER;
COMMENT ON FUNCTION monitor.pg_shmem() IS 'security wrapper for system view pg_shmem';
REVOKE ALL ON FUNCTION monitor.pg_shmem() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION monitor.pg_shmem() TO pg_monitor;

13 - Dashboards

Grafana dashboards provided by Pigsty

Grafana Dashboards for PostgreSQL clusters: Demo & Gallery.

pigsty-dashboard.jpg

There are 26 default grafana dashboards about PostgreSQL and categorized into 4 levels. and categorized into PGSQL, PGCAT & PGLOG by datasource.

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

Overview

  • pgsql-overview : The main dashboard for PGSQL module
  • pgsql-alert : Global PGSQL key metrics and alerting events
  • pgsql-shard : Overview of a horizontal sharded PGSQL cluster, e.g. citus / gpsql cluster

Cluster

  • pgsql-cluster: The main dashboard for a PGSQL cluster
  • pgrds-cluster: The PGSQL Cluster dashboard for RDS, focus on all postgres metrics only.
  • pgsql-activity: Cares about the Session/Load/QPS/TPS/Locks of a PGSQL cluster
  • pgsql-replication: Cares about PGSQL cluster replication, slots, and pub/sub.
  • pgsql-service: Cares about PGSQL cluster services, proxies, routes, and load balancers.
  • pgsql-databases: Cares about database CRUD, slow queries, and table statistics cross all instances.
  • pgsql-patroni: Cares about cluster HA agent: patroni status.
  • pgsql-pitr: Cares about context of cluster status during PITR procedure

Instance

  • pgsql-instance: The main dashboard for a single PGSQL instance
  • pgrds-instance: The PGSQL Instance dashboard for RDS, focus on all postgres metrics only.
  • pgcat-instance: Instance information from database catalog directly
  • pgsql-persist: Metrics about persistence: WAL, XID, Checkpoint, Archive, IO
  • pgsql-proxy: Metrics about haproxy the service provider
  • pgsql-queries: Overview of all queries in a single instance
  • pgsql-session: Metrics about sessions and active/idle time in a single instance
  • pgsql-xacts: Metrics about transactions, locks, queries, etc…
  • pgsql-exporter: Postgres & Pgbouncer exporter self monitoring metrics

Database

  • pgsql-database: The main dashboard for a single PGSQL database
  • pgcat-database: Database information from database catalog directly
  • pgsql-tables : Table/Index access metrics inside a single database
  • pgsql-table: Detailed information (QPS/RT/Index/Seq…) about a single table
  • pgcat-table: Detailed information (Stats/Bloat/…) about a single table from database catalog directly
  • pgsql-query: Detailed information (QPS/RT) about a single query
  • pgcat-query: Detailed information (SQL/Stats) about a single query from database catalog directly

Overview

PGSQL Overview : The main dashboard for PGSQL module

PGSQL Overview

pgsql-overview.jpg

PGSQL Alert : Global PGSQL key metrics and alerting events

PGSQL Alert

pgsql-alert.jpg

PGSQL Shard : Overview of a horizontal sharded PGSQL cluster, e.g. CITUS / GPSQL cluster

PGSQL Shard

pgsql-shard.jpg


Cluster

PGSQL Cluster: The main dashboard for a PGSQL cluster

PGSQL Cluster

pgsql-cluster.jpg

PGRDS Cluster: The PGSQL Cluster dashboard for RDS, focus on all postgres metrics only.

PGRDS Cluster

pgrds-cluster.jpg

PGSQL Service: Cares about PGSQL cluster services, proxies, routes, and load balancers.

PGSQL Service

pgsql-service.jpg

PGSQL Activity: Cares about the Session/Load/QPS/TPS/Locks of a PGSQL cluster

PGSQL Activity

pgsql-activity.jpg

PGSQL Replication: Cares about PGSQL cluster replication, slots, and pub/sub.

PGSQL Replication

pgsql-replication.jpg

PGSQL Databases: Cares about database CRUD, slow queries, and table statistics cross all instances.

PGSQL Databases

pgsql-databases.jpg

PGSQL Patroni: Cares about cluster HA agent: patroni status.

PGSQL Patroni

pgsql-patroni.jpg

PGSQL PITR: Cares about context of cluster status during PITR procedure

PGSQL PITR

pgsql-patroni.jpg


Instance

PGSQL Instance: The main dashboard for a single PGSQL instance

PGSQL Instance

pgsql-instance.jpg

PGRDS Instance: The PGSQL Instance dashboard for RDS, focus on all postgres metrics only.

PGRDS Instance

pgrds-instance.jpg

PGSQL Proxy: Metrics about haproxy the service provider

PGSQL Proxy

pgsql-proxy.jpg

PGSQL Pgbouncer: Metrics about one single pgbouncer connection pool instance

PGSQL Pgbouncer

pgsql-pgbouncer.jpg

PGSQL Persist: Metrics about persistence: WAL, XID, Checkpoint, Archive, IO

PGSQL Persist

pgsql-persist.jpg

PGSQL Xacts: Metrics about transactions, locks, queries, etc…

PGSQL Xacts

pgsql-xacts.jpg

PGSQL Session: Metrics about sessions and active/idle time in a single instance

PGSQL Session

pgsql-session.jpg

PGSQL Exporter: Postgres & Pgbouncer exporter self monitoring metrics

PGSQL Exporter

pgsql-exporter.jpg


Database

PGSQL Database: The main dashboard for a single PGSQL database

PGSQL Database

pgsql-database.jpg

PGSQL Tables : Table/Index access metrics inside a single database

PGSQL Tables

pgsql-tables.jpg

PGSQL Table: Detailed information (QPS/RT/Index/Seq…) about a single table

PGSQL Table

pgsql-table.jpg

PGSQL Query: Detailed information (QPS/RT) about a single query

PGSQL Query

pgsql-query.jpg


PGCAT

PGCAT Instance: Instance information from database catalog directly

PGCAT Instance

pgcat-instance.jpg

PGCAT Database: Database information from database catalog directly

PGCAT Database

pgcat-database.jpg

PGCAT Schema: Detailed information about one single schema from database catalog directly

PGCAT Schema

pgcat-schema.jpg

PGCAT Table: Detailed information about one single table from database catalog directly

PGCAT Table

pgcat-table.jpg

PGCAT Query: Detailed information about one single type of query from database catalog directly

PGCAT Query

pgcat-query.jpg

PGCAT Locks: Detailed information about live locks & activity from database catalog directly

PGCAT Locks

pgcat-locks.jpg


PGLOG

PGLOG Overview: Overview of csv log sample in pigsty meta database

PGLOG Overview

pglog-overview.jpg

PGLOG Overview: Detail of one single session of csv log sample in pigsty meta database

PGLOG Session

pglog-session.jpg


PGSQL Shard

pgsql-shard.jpg

PGSQL Cluster

pgsql-cluster.jpg

PGSQL Service

pgsql-service.jpg

PGSQL Activity

pgsql-activity.jpg

PGSQL Replication

pgsql-replication.jpg

PGSQL Databases

pgsql-databases.jpg

PGSQL Instance

pgsql-instance.jpg

PGSQL Proxy

pgsql-proxy.jpg

PGSQL Pgbouncer

pgsql-pgbouncer.jpg

PGSQL Session

pgsql-session.jpg

PGSQL Xacts

pgsql-xacts.jpg

PGSQL Persist

pgsql-persist.jpg

PGSQL Database

pgsql-database.jpg

PGSQL Tables

pgsql-tables.jpg

PGSQL Table

pgsql-table.jpg

PGSQL Query

pgsql-query.jpg

PGCAT Instance

pgcat-instance.jpg

PGCAT Database

pgcat-database.jpg

PGCAT Schema

pgcat-schema.jpg

PGCAT Table

pgcat-table.jpg

PGCAT Lock

pgcat-locks.jpg

PGCAT Query

pgcat-query.jpg

PGLOG Overview

pglog-overview.jpg

PGLOG Session

pglog-session.jpg

14 - Metrics

Pigsty PGSQL module metric list

PGSQL module has 638 available metrics

Metric Name Type Labels Description
ALERTS Unknown category, job, level, ins, severity, ip, alertname, alertstate, instance, cls N/A
ALERTS_FOR_STATE Unknown category, job, level, ins, severity, ip, alertname, instance, cls N/A
cls:pressure1 Unknown job, cls N/A
cls:pressure15 Unknown job, cls N/A
cls:pressure5 Unknown job, cls N/A
go_gc_duration_seconds summary job, ins, ip, instance, quantile, cls A summary of the pause duration of garbage collection cycles.
go_gc_duration_seconds_count Unknown job, ins, ip, instance, cls N/A
go_gc_duration_seconds_sum Unknown job, ins, ip, instance, cls N/A
go_goroutines gauge job, ins, ip, instance, cls Number of goroutines that currently exist.
go_info gauge version, job, ins, ip, instance, cls Information about the Go environment.
go_memstats_alloc_bytes gauge job, ins, ip, instance, cls Number of bytes allocated and still in use.
go_memstats_alloc_bytes_total counter job, ins, ip, instance, cls Total number of bytes allocated, even if freed.
go_memstats_buck_hash_sys_bytes gauge job, ins, ip, instance, cls Number of bytes used by the profiling bucket hash table.
go_memstats_frees_total counter job, ins, ip, instance, cls Total number of frees.
go_memstats_gc_sys_bytes gauge job, ins, ip, instance, cls Number of bytes used for garbage collection system metadata.
go_memstats_heap_alloc_bytes gauge job, ins, ip, instance, cls Number of heap bytes allocated and still in use.
go_memstats_heap_idle_bytes gauge job, ins, ip, instance, cls Number of heap bytes waiting to be used.
go_memstats_heap_inuse_bytes gauge job, ins, ip, instance, cls Number of heap bytes that are in use.
go_memstats_heap_objects gauge job, ins, ip, instance, cls Number of allocated objects.
go_memstats_heap_released_bytes gauge job, ins, ip, instance, cls Number of heap bytes released to OS.
go_memstats_heap_sys_bytes gauge job, ins, ip, instance, cls Number of heap bytes obtained from system.
go_memstats_last_gc_time_seconds gauge job, ins, ip, instance, cls Number of seconds since 1970 of last garbage collection.
go_memstats_lookups_total counter job, ins, ip, instance, cls Total number of pointer lookups.
go_memstats_mallocs_total counter job, ins, ip, instance, cls Total number of mallocs.
go_memstats_mcache_inuse_bytes gauge job, ins, ip, instance, cls Number of bytes in use by mcache structures.
go_memstats_mcache_sys_bytes gauge job, ins, ip, instance, cls Number of bytes used for mcache structures obtained from system.
go_memstats_mspan_inuse_bytes gauge job, ins, ip, instance, cls Number of bytes in use by mspan structures.
go_memstats_mspan_sys_bytes gauge job, ins, ip, instance, cls Number of bytes used for mspan structures obtained from system.
go_memstats_next_gc_bytes gauge job, ins, ip, instance, cls Number of heap bytes when next garbage collection will take place.
go_memstats_other_sys_bytes gauge job, ins, ip, instance, cls Number of bytes used for other system allocations.
go_memstats_stack_inuse_bytes gauge job, ins, ip, instance, cls Number of bytes in use by the stack allocator.
go_memstats_stack_sys_bytes gauge job, ins, ip, instance, cls Number of bytes obtained from system for stack allocator.
go_memstats_sys_bytes gauge job, ins, ip, instance, cls Number of bytes obtained from system.
go_threads gauge job, ins, ip, instance, cls Number of OS threads created.
ins:pressure1 Unknown job, ins, ip, cls N/A
ins:pressure15 Unknown job, ins, ip, cls N/A
ins:pressure5 Unknown job, ins, ip, cls N/A
patroni_cluster_unlocked gauge job, ins, ip, instance, cls, scope Value is 1 if the cluster is unlocked, 0 if locked.
patroni_dcs_last_seen gauge job, ins, ip, instance, cls, scope Epoch timestamp when DCS was last contacted successfully by Patroni.
patroni_failsafe_mode_is_active gauge job, ins, ip, instance, cls, scope Value is 1 if failsafe mode is active, 0 if inactive.
patroni_is_paused gauge job, ins, ip, instance, cls, scope Value is 1 if auto failover is disabled, 0 otherwise.
patroni_master gauge job, ins, ip, instance, cls, scope Value is 1 if this node is the leader, 0 otherwise.
patroni_pending_restart gauge job, ins, ip, instance, cls, scope Value is 1 if the node needs a restart, 0 otherwise.
patroni_postgres_in_archive_recovery gauge job, ins, ip, instance, cls, scope Value is 1 if Postgres is replicating from archive, 0 otherwise.
patroni_postgres_running gauge job, ins, ip, instance, cls, scope Value is 1 if Postgres is running, 0 otherwise.
patroni_postgres_server_version gauge job, ins, ip, instance, cls, scope Version of Postgres (if running), 0 otherwise.
patroni_postgres_streaming gauge job, ins, ip, instance, cls, scope Value is 1 if Postgres is streaming, 0 otherwise.
patroni_postgres_timeline counter job, ins, ip, instance, cls, scope Postgres timeline of this node (if running), 0 otherwise.
patroni_postmaster_start_time gauge job, ins, ip, instance, cls, scope Epoch seconds since Postgres started.
patroni_primary gauge job, ins, ip, instance, cls, scope Value is 1 if this node is the leader, 0 otherwise.
patroni_replica gauge job, ins, ip, instance, cls, scope Value is 1 if this node is a replica, 0 otherwise.
patroni_standby_leader gauge job, ins, ip, instance, cls, scope Value is 1 if this node is the standby_leader, 0 otherwise.
patroni_sync_standby gauge job, ins, ip, instance, cls, scope Value is 1 if this node is a sync standby replica, 0 otherwise.
patroni_up Unknown job, ins, ip, instance, cls N/A
patroni_version gauge job, ins, ip, instance, cls, scope Patroni semver without periods.
patroni_xlog_location counter job, ins, ip, instance, cls, scope Current location of the Postgres transaction log, 0 if this node is not the leader.
patroni_xlog_paused gauge job, ins, ip, instance, cls, scope Value is 1 if the Postgres xlog is paused, 0 otherwise.
patroni_xlog_received_location counter job, ins, ip, instance, cls, scope Current location of the received Postgres transaction log, 0 if this node is not a replica.
patroni_xlog_replayed_location counter job, ins, ip, instance, cls, scope Current location of the replayed Postgres transaction log, 0 if this node is not a replica.
patroni_xlog_replayed_timestamp gauge job, ins, ip, instance, cls, scope Current timestamp of the replayed Postgres transaction log, 0 if null.
pg:cls:active_backends Unknown job, cls N/A
pg:cls:active_time_rate15m Unknown job, cls N/A
pg:cls:active_time_rate1m Unknown job, cls N/A
pg:cls:active_time_rate5m Unknown job, cls N/A
pg:cls:age Unknown job, cls N/A
pg:cls:buf_alloc_rate1m Unknown job, cls N/A
pg:cls:buf_clean_rate1m Unknown job, cls N/A
pg:cls:buf_flush_backend_rate1m Unknown job, cls N/A
pg:cls:buf_flush_checkpoint_rate1m Unknown job, cls N/A
pg:cls:cpu_count Unknown job, cls N/A
pg:cls:cpu_usage Unknown job, cls N/A
pg:cls:cpu_usage_15m Unknown job, cls N/A
pg:cls:cpu_usage_1m Unknown job, cls N/A
pg:cls:cpu_usage_5m Unknown job, cls N/A
pg:cls:db_size Unknown job, cls N/A
pg:cls:file_size Unknown job, cls N/A
pg:cls:ixact_backends Unknown job, cls N/A
pg:cls:ixact_time_rate1m Unknown job, cls N/A
pg:cls:lag_bytes Unknown job, cls N/A
pg:cls:lag_seconds Unknown job, cls N/A
pg:cls:leader Unknown job, ins, ip, instance, cls N/A
pg:cls:load1 Unknown job, cls N/A
pg:cls:load15 Unknown job, cls N/A
pg:cls:load5 Unknown job, cls N/A
pg:cls:lock_count Unknown job, cls N/A
pg:cls:locks Unknown job, cls, mode N/A
pg:cls:log_size Unknown job, cls N/A
pg:cls:lsn_rate1m Unknown job, cls N/A
pg:cls:members Unknown job, ins, ip, cls N/A
pg:cls:num_backends Unknown job, cls N/A
pg:cls:partition Unknown job, cls N/A
pg:cls:receiver Unknown state, slot_name, job, appname, ip, cls, sender_host, sender_port N/A
pg:cls:rlock_count Unknown job, cls N/A
pg:cls:saturation1 Unknown job, cls N/A
pg:cls:saturation15 Unknown job, cls N/A
pg:cls:saturation5 Unknown job, cls N/A
pg:cls:sender Unknown pid, usename, address, job, ins, appname, ip, cls N/A
pg:cls:session_time_rate1m Unknown job, cls N/A
pg:cls:size Unknown job, cls N/A
pg:cls:slot_count Unknown job, cls N/A
pg:cls:slot_retained_bytes Unknown job, cls N/A
pg:cls:standby_count Unknown job, cls N/A
pg:cls:sync_state Unknown job, cls N/A
pg:cls:timeline Unknown job, cls N/A
pg:cls:tup_deleted_rate1m Unknown job, cls N/A
pg:cls:tup_fetched_rate1m Unknown job, cls N/A
pg:cls:tup_inserted_rate1m Unknown job, cls N/A
pg:cls:tup_modified_rate1m Unknown job, cls N/A
pg:cls:tup_returned_rate1m Unknown job, cls N/A
pg:cls:wal_size Unknown job, cls N/A
pg:cls:xact_commit_rate15m Unknown job, cls N/A
pg:cls:xact_commit_rate1m Unknown job, cls N/A
pg:cls:xact_commit_rate5m Unknown job, cls N/A
pg:cls:xact_rollback_rate15m Unknown job, cls N/A
pg:cls:xact_rollback_rate1m Unknown job, cls N/A
pg:cls:xact_rollback_rate5m Unknown job, cls N/A
pg:cls:xact_total_rate15m Unknown job, cls N/A
pg:cls:xact_total_rate1m Unknown job, cls N/A
pg:cls:xact_total_sigma15m Unknown job, cls N/A
pg:cls:xlock_count Unknown job, cls N/A
pg:db:active_backends Unknown datname, job, ins, ip, instance, cls N/A
pg:db:active_time_rate15m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:active_time_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:active_time_rate5m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:age Unknown datname, job, ins, ip, instance, cls N/A
pg:db:age_deriv1h Unknown datname, job, ins, ip, instance, cls N/A
pg:db:age_exhaust Unknown datname, job, ins, ip, instance, cls N/A
pg:db:blk_io_time_seconds_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:blk_read_time_seconds_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:blk_write_time_seconds_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:blks_access_1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:blks_hit_1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:blks_hit_ratio1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:blks_read_1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:conn_limit Unknown datname, job, ins, ip, instance, cls N/A
pg:db:conn_usage Unknown datname, job, ins, ip, instance, cls N/A
pg:db:db_size Unknown datname, job, ins, ip, instance, cls N/A
pg:db:ixact_backends Unknown datname, job, ins, ip, instance, cls N/A
pg:db:ixact_time_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:lock_count Unknown datname, job, ins, ip, instance, cls N/A
pg:db:num_backends Unknown datname, job, ins, ip, instance, cls N/A
pg:db:rlock_count Unknown datname, job, ins, ip, instance, cls N/A
pg:db:session_time_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:temp_bytes_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:temp_files_1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:tup_deleted_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:tup_fetched_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:tup_inserted_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:tup_modified_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:tup_returned_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:wlock_count Unknown datname, job, ins, ip, instance, cls N/A
pg:db:xact_commit_rate15m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:xact_commit_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:xact_commit_rate5m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:xact_rollback_rate15m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:xact_rollback_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:xact_rollback_rate5m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:xact_total_rate15m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:xact_total_rate1m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:xact_total_rate5m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:xact_total_sigma15m Unknown datname, job, ins, ip, instance, cls N/A
pg:db:xlock_count Unknown datname, job, ins, ip, instance, cls N/A
pg:env:active_backends Unknown job N/A
pg:env:active_time_rate15m Unknown job N/A
pg:env:active_time_rate1m Unknown job N/A
pg:env:active_time_rate5m Unknown job N/A
pg:env:age Unknown job N/A
pg:env:cpu_count Unknown job N/A
pg:env:cpu_usage Unknown job N/A
pg:env:cpu_usage_15m Unknown job N/A
pg:env:cpu_usage_1m Unknown job N/A
pg:env:cpu_usage_5m Unknown job N/A
pg:env:ixact_backends Unknown job N/A
pg:env:ixact_time_rate1m Unknown job N/A
pg:env:lag_bytes Unknown job N/A
pg:env:lag_seconds Unknown job N/A
pg:env:lsn_rate1m Unknown job N/A
pg:env:session_time_rate1m Unknown job N/A
pg:env:tup_deleted_rate1m Unknown job N/A
pg:env:tup_fetched_rate1m Unknown job N/A
pg:env:tup_inserted_rate1m Unknown job N/A
pg:env:tup_modified_rate1m Unknown job N/A
pg:env:tup_returned_rate1m Unknown job N/A
pg:env:xact_commit_rate15m Unknown job N/A
pg:env:xact_commit_rate1m Unknown job N/A
pg:env:xact_commit_rate5m Unknown job N/A
pg:env:xact_rollback_rate15m Unknown job N/A
pg:env:xact_rollback_rate1m Unknown job N/A
pg:env:xact_rollback_rate5m Unknown job N/A
pg:env:xact_total_rate15m Unknown job N/A
pg:env:xact_total_rate1m Unknown job N/A
pg:env:xact_total_sigma15m Unknown job N/A
pg:ins:active_backends Unknown job, ins, ip, instance, cls N/A
pg:ins:active_time_rate15m Unknown job, ins, ip, instance, cls N/A
pg:ins:active_time_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:active_time_rate5m Unknown job, ins, ip, instance, cls N/A
pg:ins:age Unknown job, ins, ip, instance, cls N/A
pg:ins:blks_hit_ratio1m Unknown job, ins, ip, instance, cls N/A
pg:ins:buf_alloc_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:buf_clean_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:buf_flush_backend_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:buf_flush_checkpoint_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:ckpt_1h Unknown job, ins, ip, instance, cls N/A
pg:ins:ckpt_req_1m Unknown job, ins, ip, instance, cls N/A
pg:ins:ckpt_timed_1m Unknown job, ins, ip, instance, cls N/A
pg:ins:conn_limit Unknown job, ins, ip, instance, cls N/A
pg:ins:conn_usage Unknown job, ins, ip, instance, cls N/A
pg:ins:cpu_count Unknown job, ins, ip, instance, cls N/A
pg:ins:cpu_usage Unknown job, ins, ip, instance, cls N/A
pg:ins:cpu_usage_15m Unknown job, ins, ip, instance, cls N/A
pg:ins:cpu_usage_1m Unknown job, ins, ip, instance, cls N/A
pg:ins:cpu_usage_5m Unknown job, ins, ip, instance, cls N/A
pg:ins:db_size Unknown job, ins, ip, instance, cls N/A
pg:ins:file_size Unknown job, ins, ip, instance, cls N/A
pg:ins:fs_size Unknown job, ins, ip, instance, cls N/A
pg:ins:is_leader Unknown job, ins, ip, instance, cls N/A
pg:ins:ixact_backends Unknown job, ins, ip, instance, cls N/A
pg:ins:ixact_time_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:lag_bytes Unknown job, ins, ip, instance, cls N/A
pg:ins:lag_seconds Unknown job, ins, ip, instance, cls N/A
pg:ins:load1 Unknown job, ins, ip, instance, cls N/A
pg:ins:load15 Unknown job, ins, ip, instance, cls N/A
pg:ins:load5 Unknown job, ins, ip, instance, cls N/A
pg:ins:lock_count Unknown job, ins, ip, instance, cls N/A
pg:ins:locks Unknown job, ins, ip, mode, instance, cls N/A
pg:ins:log_size Unknown job, ins, ip, instance, cls N/A
pg:ins:lsn_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:mem_size Unknown job, ins, ip, instance, cls N/A
pg:ins:num_backends Unknown job, ins, ip, instance, cls N/A
pg:ins:rlock_count Unknown job, ins, ip, instance, cls N/A
pg:ins:saturation1 Unknown job, ins, ip, cls N/A
pg:ins:saturation15 Unknown job, ins, ip, cls N/A
pg:ins:saturation5 Unknown job, ins, ip, cls N/A
pg:ins:session_time_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:slot_retained_bytes Unknown job, ins, ip, instance, cls N/A
pg:ins:space_usage Unknown job, ins, ip, instance, cls N/A
pg:ins:status Unknown job, ins, ip, instance, cls N/A
pg:ins:sync_state Unknown job, ins, instance, cls N/A
pg:ins:target_count Unknown job, cls, ins N/A
pg:ins:timeline Unknown job, ins, ip, instance, cls N/A
pg:ins:tup_deleted_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:tup_fetched_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:tup_inserted_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:tup_modified_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:tup_returned_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:wal_size Unknown job, ins, ip, instance, cls N/A
pg:ins:wlock_count Unknown job, ins, ip, instance, cls N/A
pg:ins:xact_commit_rate15m Unknown job, ins, ip, instance, cls N/A
pg:ins:xact_commit_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:xact_commit_rate5m Unknown job, ins, ip, instance, cls N/A
pg:ins:xact_rollback_rate15m Unknown job, ins, ip, instance, cls N/A
pg:ins:xact_rollback_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:xact_rollback_rate5m Unknown job, ins, ip, instance, cls N/A
pg:ins:xact_total_rate15m Unknown job, ins, ip, instance, cls N/A
pg:ins:xact_total_rate1m Unknown job, ins, ip, instance, cls N/A
pg:ins:xact_total_rate5m Unknown job, ins, ip, instance, cls N/A
pg:ins:xact_total_sigma15m Unknown job, ins, ip, instance, cls N/A
pg:ins:xlock_count Unknown job, ins, ip, instance, cls N/A
pg:query:call_rate1m Unknown datname, query, job, ins, ip, instance, cls N/A
pg:query:rt_1m Unknown datname, query, job, ins, ip, instance, cls N/A
pg:table:scan_rate1m Unknown datname, relname, job, ins, ip, instance, cls N/A
pg_activity_count gauge datname, state, job, ins, ip, instance, cls Count of connection among (datname,state)
pg_activity_max_conn_duration gauge datname, state, job, ins, ip, instance, cls Max backend session duration since state change among (datname, state)
pg_activity_max_duration gauge datname, state, job, ins, ip, instance, cls Max duration since last state change among (datname, state)
pg_activity_max_tx_duration gauge datname, state, job, ins, ip, instance, cls Max transaction duration since state change among (datname, state)
pg_archiver_failed_count counter job, ins, ip, instance, cls Number of failed attempts for archiving WAL files
pg_archiver_finish_count counter job, ins, ip, instance, cls Number of WAL files that have been successfully archived
pg_archiver_last_failed_time counter job, ins, ip, instance, cls Time of the last failed archival operation
pg_archiver_last_finish_time counter job, ins, ip, instance, cls Time of the last successful archive operation
pg_archiver_reset_time gauge job, ins, ip, instance, cls Time at which archive statistics were last reset
pg_backend_count gauge type, job, ins, ip, instance, cls Database backend process count by backend_type
pg_bgwriter_buffers_alloc counter job, ins, ip, instance, cls Number of buffers allocated
pg_bgwriter_buffers_backend counter job, ins, ip, instance, cls Number of buffers written directly by a backend
pg_bgwriter_buffers_backend_fsync counter job, ins, ip, instance, cls Number of times a backend had to execute its own fsync call
pg_bgwriter_buffers_checkpoint counter job, ins, ip, instance, cls Number of buffers written during checkpoints
pg_bgwriter_buffers_clean counter job, ins, ip, instance, cls Number of buffers written by the background writer
pg_bgwriter_checkpoint_sync_time counter job, ins, ip, instance, cls Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in seconds
pg_bgwriter_checkpoint_write_time counter job, ins, ip, instance, cls Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in seconds
pg_bgwriter_checkpoints_req counter job, ins, ip, instance, cls Number of requested checkpoints that have been performed
pg_bgwriter_checkpoints_timed counter job, ins, ip, instance, cls Number of scheduled checkpoints that have been performed
pg_bgwriter_maxwritten_clean counter job, ins, ip, instance, cls Number of times the background writer stopped a cleaning scan because it had written too many buffers
pg_bgwriter_reset_time counter job, ins, ip, instance, cls Time at which bgwriter statistics were last reset
pg_boot_time gauge job, ins, ip, instance, cls unix timestamp when postmaster boot
pg_checkpoint_checkpoint_lsn counter job, ins, ip, instance, cls Latest checkpoint location
pg_checkpoint_elapse gauge job, ins, ip, instance, cls Seconds elapsed since latest checkpoint in seconds
pg_checkpoint_full_page_writes gauge job, ins, ip, instance, cls Latest checkpoint’s full_page_writes enabled
pg_checkpoint_newest_commit_ts_xid counter job, ins, ip, instance, cls Latest checkpoint’s newestCommitTsXid
pg_checkpoint_next_multi_offset counter job, ins, ip, instance, cls Latest checkpoint’s NextMultiOffset
pg_checkpoint_next_multixact_id counter job, ins, ip, instance, cls Latest checkpoint’s NextMultiXactId
pg_checkpoint_next_oid counter job, ins, ip, instance, cls Latest checkpoint’s NextOID
pg_checkpoint_next_xid counter job, ins, ip, instance, cls Latest checkpoint’s NextXID xid
pg_checkpoint_next_xid_epoch counter job, ins, ip, instance, cls Latest checkpoint’s NextXID epoch
pg_checkpoint_oldest_active_xid counter job, ins, ip, instance, cls Latest checkpoint’s oldestActiveXID
pg_checkpoint_oldest_commit_ts_xid counter job, ins, ip, instance, cls Latest checkpoint’s oldestCommitTsXid
pg_checkpoint_oldest_multi_dbid gauge job, ins, ip, instance, cls Latest checkpoint’s oldestMulti’s DB OID
pg_checkpoint_oldest_multi_xid counter job, ins, ip, instance, cls Latest checkpoint’s oldestMultiXid
pg_checkpoint_oldest_xid counter job, ins, ip, instance, cls Latest checkpoint’s oldestXID
pg_checkpoint_oldest_xid_dbid gauge job, ins, ip, instance, cls Latest checkpoint’s oldestXID’s DB OID
pg_checkpoint_prev_tli counter job, ins, ip, instance, cls Latest checkpoint’s PrevTimeLineID
pg_checkpoint_redo_lsn counter job, ins, ip, instance, cls Latest checkpoint’s REDO location
pg_checkpoint_time counter job, ins, ip, instance, cls Time of latest checkpoint
pg_checkpoint_tli counter job, ins, ip, instance, cls Latest checkpoint’s TimeLineID
pg_conf_reload_time gauge job, ins, ip, instance, cls seconds since last configuration reload
pg_db_active_time counter datname, job, ins, ip, instance, cls Time spent executing SQL statements in this database, in seconds
pg_db_age gauge datname, job, ins, ip, instance, cls Age of database calculated from datfrozenxid
pg_db_allow_conn gauge datname, job, ins, ip, instance, cls If false(0) then no one can connect to this database.
pg_db_blk_read_time counter datname, job, ins, ip, instance, cls Time spent reading data file blocks by backends in this database, in seconds
pg_db_blk_write_time counter datname, job, ins, ip, instance, cls Time spent writing data file blocks by backends in this database, in seconds
pg_db_blks_access counter datname, job, ins, ip, instance, cls Number of times disk blocks that accessed read+hit
pg_db_blks_hit counter datname, job, ins, ip, instance, cls Number of times disk blocks were found already in the buffer cache
pg_db_blks_read counter datname, job, ins, ip, instance, cls Number of disk blocks read in this database
pg_db_cks_fail_time gauge datname, job, ins, ip, instance, cls Time at which the last data page checksum failure was detected in this database
pg_db_cks_fails counter datname, job, ins, ip, instance, cls Number of data page checksum failures detected in this database, -1 for not enabled
pg_db_confl_confl_bufferpin counter datname, job, ins, ip, instance, cls Number of queries in this database that have been canceled due to pinned buffers
pg_db_confl_confl_deadlock counter datname, job, ins, ip, instance, cls Number of queries in this database that have been canceled due to deadlocks
pg_db_confl_confl_lock counter datname, job, ins, ip, instance, cls Number of queries in this database that have been canceled due to lock timeouts
pg_db_confl_confl_snapshot counter datname, job, ins, ip, instance, cls Number of queries in this database that have been canceled due to old snapshots
pg_db_confl_confl_tablespace counter datname, job, ins, ip, instance, cls Number of queries in this database that have been canceled due to dropped tablespaces
pg_db_conflicts counter datname, job, ins, ip, instance, cls Number of queries canceled due to conflicts with recovery in this database
pg_db_conn_limit gauge datname, job, ins, ip, instance, cls Sets maximum number of concurrent connections that can be made to this database. -1 means no limit.
pg_db_datid gauge datname, job, ins, ip, instance, cls OID of the database
pg_db_deadlocks counter datname, job, ins, ip, instance, cls Number of deadlocks detected in this database
pg_db_frozen_xid gauge datname, job, ins, ip, instance, cls All transaction IDs before this one have been frozened
pg_db_is_template gauge datname, job, ins, ip, instance, cls If true(1), then this database can be cloned by any user with CREATEDB privileges
pg_db_ixact_time counter datname, job, ins, ip, instance, cls Time spent idling while in a transaction in this database, in seconds
pg_db_numbackends gauge datname, job, ins, ip, instance, cls Number of backends currently connected to this database
pg_db_reset_time counter datname, job, ins, ip, instance, cls Time at which database statistics were last reset
pg_db_session_time counter datname, job, ins, ip, instance, cls Time spent by database sessions in this database, in seconds
pg_db_sessions counter datname, job, ins, ip, instance, cls Total number of sessions established to this database
pg_db_sessions_abandoned counter datname, job, ins, ip, instance, cls Number of database sessions to this database that were terminated because connection to the client was lost
pg_db_sessions_fatal counter datname, job, ins, ip, instance, cls Number of database sessions to this database that were terminated by fatal errors
pg_db_sessions_killed counter datname, job, ins, ip, instance, cls Number of database sessions to this database that were terminated by operator intervention
pg_db_temp_bytes counter datname, job, ins, ip, instance, cls Total amount of data written to temporary files by queries in this database.
pg_db_temp_files counter datname, job, ins, ip, instance, cls Number of temporary files created by queries in this database
pg_db_tup_deleted counter datname, job, ins, ip, instance, cls Number of rows deleted by queries in this database
pg_db_tup_fetched counter datname, job, ins, ip, instance, cls Number of rows fetched by queries in this database
pg_db_tup_inserted counter datname, job, ins, ip, instance, cls Number of rows inserted by queries in this database
pg_db_tup_modified counter datname, job, ins, ip, instance, cls Number of rows modified by queries in this database
pg_db_tup_returned counter datname, job, ins, ip, instance, cls Number of rows returned by queries in this database
pg_db_tup_updated counter datname, job, ins, ip, instance, cls Number of rows updated by queries in this database
pg_db_xact_commit counter datname, job, ins, ip, instance, cls Number of transactions in this database that have been committed
pg_db_xact_rollback counter datname, job, ins, ip, instance, cls Number of transactions in this database that have been rolled back
pg_db_xact_total counter datname, job, ins, ip, instance, cls Number of transactions in this database
pg_downstream_count gauge state, job, ins, ip, instance, cls Count of corresponding state
pg_exporter_agent_up Unknown job, ins, ip, instance, cls N/A
pg_exporter_last_scrape_time gauge job, ins, ip, instance, cls seconds exporter spending on scrapping
pg_exporter_query_cache_ttl gauge datname, query, job, ins, ip, instance, cls times to live of query cache
pg_exporter_query_scrape_duration gauge datname, query, job, ins, ip, instance, cls seconds query spending on scrapping
pg_exporter_query_scrape_error_count gauge datname, query, job, ins, ip, instance, cls times the query failed
pg_exporter_query_scrape_hit_count gauge datname, query, job, ins, ip, instance, cls numbers been scrapped from this query
pg_exporter_query_scrape_metric_count gauge datname, query, job, ins, ip, instance, cls numbers of metrics been scrapped from this query
pg_exporter_query_scrape_total_count gauge datname, query, job, ins, ip, instance, cls times exporter server was scraped for metrics
pg_exporter_scrape_duration gauge job, ins, ip, instance, cls seconds exporter spending on scrapping
pg_exporter_scrape_error_count counter job, ins, ip, instance, cls times exporter was scraped for metrics and failed
pg_exporter_scrape_total_count counter job, ins, ip, instance, cls times exporter was scraped for metrics
pg_exporter_server_scrape_duration gauge datname, job, ins, ip, instance, cls seconds exporter server spending on scrapping
pg_exporter_server_scrape_error_count Unknown datname, job, ins, ip, instance, cls N/A
pg_exporter_server_scrape_total_count gauge datname, job, ins, ip, instance, cls times exporter server was scraped for metrics
pg_exporter_server_scrape_total_seconds gauge datname, job, ins, ip, instance, cls seconds exporter server spending on scrapping
pg_exporter_up gauge job, ins, ip, instance, cls always be 1 if your could retrieve metrics
pg_exporter_uptime gauge job, ins, ip, instance, cls seconds since exporter primary server inited
pg_flush_lsn counter job, ins, ip, instance, cls primary only, location of current wal syncing
pg_func_calls counter datname, funcname, job, ins, ip, instance, cls Number of times this function has been called
pg_func_self_time counter datname, funcname, job, ins, ip, instance, cls Total time spent in this function itself, not including other functions called by it, in ms
pg_func_total_time counter datname, funcname, job, ins, ip, instance, cls Total time spent in this function and all other functions called by it, in ms
pg_in_recovery gauge job, ins, ip, instance, cls server is in recovery mode? 1 for yes 0 for no
pg_index_idx_blks_hit counter datname, relname, job, ins, relid, ip, instance, cls, idxname Number of buffer hits in this index
pg_index_idx_blks_read counter datname, relname, job, ins, relid, ip, instance, cls, idxname Number of disk blocks read from this index
pg_index_idx_scan counter datname, relname, job, ins, relid, ip, instance, cls, idxname Number of index scans initiated on this index
pg_index_idx_tup_fetch counter datname, relname, job, ins, relid, ip, instance, cls, idxname Number of live table rows fetched by simple index scans using this index
pg_index_idx_tup_read counter datname, relname, job, ins, relid, ip, instance, cls, idxname Number of index entries returned by scans on this index
pg_index_relpages gauge datname, relname, job, ins, relid, ip, instance, cls, idxname Size of the on-disk representation of this index in pages
pg_index_reltuples gauge datname, relname, job, ins, relid, ip, instance, cls, idxname Estimate relation tuples
pg_insert_lsn counter job, ins, ip, instance, cls primary only, location of current wal inserting
pg_io_evictions counter type, job, ins, object, ip, context, instance, cls Number of times a block has been written out from a shared or local buffer
pg_io_extend_time counter type, job, ins, object, ip, context, instance, cls Time spent in extend operations in seconds
pg_io_extends counter type, job, ins, object, ip, context, instance, cls Number of relation extend operations, each of the size specified in op_bytes.
pg_io_fsync_time counter type, job, ins, object, ip, context, instance, cls Time spent in fsync operations in seconds
pg_io_fsyncs counter type, job, ins, object, ip, context, instance, cls Number of fsync calls. These are only tracked in context normal
pg_io_hits counter type, job, ins, object, ip, context, instance, cls The number of times a desired block was found in a shared buffer.
pg_io_op_bytes gauge type, job, ins, object, ip, context, instance, cls The number of bytes per unit of I/O read, written, or extended. 8192 by default
pg_io_read_time counter type, job, ins, object, ip, context, instance, cls Time spent in read operations in seconds
pg_io_reads counter type, job, ins, object, ip, context, instance, cls Number of read operations, each of the size specified in op_bytes.
pg_io_reset_time gauge type, job, ins, object, ip, context, instance, cls Timestamp at which these statistics were last reset
pg_io_reuses counter type, job, ins, object, ip, context, instance, cls The number of times an existing buffer in reused
pg_io_write_time counter type, job, ins, object, ip, context, instance, cls Time spent in write operations in seconds
pg_io_writeback_time counter type, job, ins, object, ip, context, instance, cls Time spent in writeback operations in seconds
pg_io_writebacks counter type, job, ins, object, ip, context, instance, cls Number of units of size op_bytes which the process requested the kernel write out to permanent storage.
pg_io_writes counter type, job, ins, object, ip, context, instance, cls Number of write operations, each of the size specified in op_bytes.
pg_is_in_recovery gauge job, ins, ip, instance, cls 1 if in recovery mode
pg_is_wal_replay_paused gauge job, ins, ip, instance, cls 1 if wal play paused
pg_lag gauge job, ins, ip, instance, cls replica only, replication lag in seconds
pg_last_replay_time gauge job, ins, ip, instance, cls time when last transaction been replayed
pg_lock_count gauge datname, job, ins, ip, mode, instance, cls Number of locks of corresponding mode and database
pg_lsn counter job, ins, ip, instance, cls log sequence number, current write location
pg_meta_info gauge cls, extensions, version, job, ins, primary_conninfo, conf_path, hba_path, ip, cluster_id, instance, listen_port, wal_level, ver_num, cluster_name, data_dir constant 1
pg_query_calls counter datname, query, job, ins, ip, instance, cls Number of times the statement was executed
pg_query_exec_time counter datname, query, job, ins, ip, instance, cls Total time spent executing the statement, in seconds
pg_query_io_time counter datname, query, job, ins, ip, instance, cls Total time the statement spent reading and writing blocks, in seconds
pg_query_rows counter datname, query, job, ins, ip, instance, cls Total number of rows retrieved or affected by the statement
pg_query_sblk_dirtied counter datname, query, job, ins, ip, instance, cls Total number of shared blocks dirtied by the statement
pg_query_sblk_hit counter datname, query, job, ins, ip, instance, cls Total number of shared block cache hits by the statement
pg_query_sblk_read counter datname, query, job, ins, ip, instance, cls Total number of shared blocks read by the statement
pg_query_sblk_written counter datname, query, job, ins, ip, instance, cls Total number of shared blocks written by the statement
pg_query_wal_bytes counter datname, query, job, ins, ip, instance, cls Total amount of WAL bytes generated by the statement
pg_receive_lsn counter job, ins, ip, instance, cls replica only, location of wal synced to disk
pg_recovery_backup_end_lsn counter job, ins, ip, instance, cls Backup end location
pg_recovery_backup_start_lsn counter job, ins, ip, instance, cls Backup start location
pg_recovery_min_lsn counter job, ins, ip, instance, cls Minimum recovery ending location
pg_recovery_min_timeline counter job, ins, ip, instance, cls Min recovery ending loc’s timeline
pg_recovery_prefetch_block_distance gauge job, ins, ip, instance, cls How many blocks ahead the prefetcher is looking
pg_recovery_prefetch_hit counter job, ins, ip, instance, cls Number of blocks not prefetched because they were already in the buffer pool
pg_recovery_prefetch_io_depth gauge job, ins, ip, instance, cls How many prefetches have been initiated but are not yet known to have completed
pg_recovery_prefetch_prefetch counter job, ins, ip, instance, cls Number of blocks prefetched because they were not in the buffer pool
pg_recovery_prefetch_reset_time counter job, ins, ip, instance, cls Time at which these recovery prefetch statistics were last reset
pg_recovery_prefetch_skip_fpw gauge job, ins, ip, instance, cls Number of blocks not prefetched because a full page image was included in the WAL
pg_recovery_prefetch_skip_init counter job, ins, ip, instance, cls Number of blocks not prefetched because they would be zero-initialized
pg_recovery_prefetch_skip_new counter job, ins, ip, instance, cls Number of blocks not prefetched because they didn’t exist yet
pg_recovery_prefetch_skip_rep counter job, ins, ip, instance, cls Number of blocks not prefetched because they were already recently prefetched
pg_recovery_prefetch_wal_distance gauge job, ins, ip, instance, cls How many bytes ahead the prefetcher is looking
pg_recovery_require_record gauge job, ins, ip, instance, cls End-of-backup record required
pg_recv_flush_lsn counter state, slot_name, job, ins, ip, instance, cls, sender_host, sender_port Last write-ahead log location already received and flushed to disk
pg_recv_flush_tli counter state, slot_name, job, ins, ip, instance, cls, sender_host, sender_port Timeline number of last write-ahead log location received and flushed to disk
pg_recv_init_lsn counter state, slot_name, job, ins, ip, instance, cls, sender_host, sender_port First write-ahead log location used when WAL receiver is started
pg_recv_init_tli counter state, slot_name, job, ins, ip, instance, cls, sender_host, sender_port First timeline number used when WAL receiver is started
pg_recv_msg_recv_time gauge state, slot_name, job, ins, ip, instance, cls, sender_host, sender_port Receipt time of last message received from origin WAL sender
pg_recv_msg_send_time gauge state, slot_name, job, ins, ip, instance, cls, sender_host, sender_port Send time of last message received from origin WAL sender
pg_recv_pid gauge state, slot_name, job, ins, ip, instance, cls, sender_host, sender_port Process ID of the WAL receiver process
pg_recv_reported_lsn counter state, slot_name, job, ins, ip, instance, cls, sender_host, sender_port Last write-ahead log location reported to origin WAL sender
pg_recv_reported_time gauge state, slot_name, job, ins, ip, instance, cls, sender_host, sender_port Time of last write-ahead log location reported to origin WAL sender
pg_recv_time gauge state, slot_name, job, ins, ip, instance, cls, sender_host, sender_port Time of current snapshot
pg_recv_write_lsn counter state, slot_name, job, ins, ip, instance, cls, sender_host, sender_port Last write-ahead log location already received and written to disk, but not flushed.
pg_relkind_count gauge datname, job, ins, ip, instance, cls, relkind Number of relations of corresponding relkind
pg_repl_backend_xmin counter pid, usename, address, job, ins, appname, ip, instance, cls This standby’s xmin horizon reported by hot_standby_feedback.
pg_repl_client_port gauge pid, usename, address, job, ins, appname, ip, instance, cls TCP port number that the client is using for communication with this WAL sender, or -1 if a Unix socket is used
pg_repl_flush_diff gauge pid, usename, address, job, ins, appname, ip, instance, cls Last log position flushed to disk by this standby server diff with current lsn
pg_repl_flush_lag gauge pid, usename, address, job, ins, appname, ip, instance, cls Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it
pg_repl_flush_lsn counter pid, usename, address, job, ins, appname, ip, instance, cls Last write-ahead log location flushed to disk by this standby server
pg_repl_launch_time counter pid, usename, address, job, ins, appname, ip, instance, cls Time when this process was started, i.e., when the client connected to this WAL sender
pg_repl_lsn counter pid, usename, address, job, ins, appname, ip, instance, cls Current log position on this server
pg_repl_replay_diff gauge pid, usename, address, job, ins, appname, ip, instance, cls Last log position replayed into the database on this standby server diff with current lsn
pg_repl_replay_lag gauge pid, usename, address, job, ins, appname, ip, instance, cls Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written, flushed and applied it
pg_repl_replay_lsn counter pid, usename, address, job, ins, appname, ip, instance, cls Last write-ahead log location replayed into the database on this standby server
pg_repl_reply_time gauge pid, usename, address, job, ins, appname, ip, instance, cls Send time of last reply message received from standby server
pg_repl_sent_diff gauge pid, usename, address, job, ins, appname, ip, instance, cls Last log position sent to this standby server diff with current lsn
pg_repl_sent_lsn counter pid, usename, address, job, ins, appname, ip, instance, cls Last write-ahead log location sent on this connection
pg_repl_state gauge pid, usename, address, job, ins, appname, ip, instance, cls Current WAL sender encoded state 0-4 for streaming startup catchup backup stopping
pg_repl_sync_priority gauge pid, usename, address, job, ins, appname, ip, instance, cls Priority of this standby server for being chosen as the synchronous standby
pg_repl_sync_state gauge pid, usename, address, job, ins, appname, ip, instance, cls Encoded synchronous state of this standby server, 0-3 for async potential sync quorum
pg_repl_time counter pid, usename, address, job, ins, appname, ip, instance, cls Current timestamp in unix epoch
pg_repl_write_diff gauge pid, usename, address, job, ins, appname, ip, instance, cls Last log position written to disk by this standby server diff with current lsn
pg_repl_write_lag gauge pid, usename, address, job, ins, appname, ip, instance, cls Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written it
pg_repl_write_lsn counter pid, usename, address, job, ins, appname, ip, instance, cls Last write-ahead log location written to disk by this standby server
pg_replay_lsn counter job, ins, ip, instance, cls replica only, location of wal applied
pg_seq_blks_hit counter datname, job, ins, ip, instance, cls, seqname Number of buffer hits in this sequence
pg_seq_blks_read counter datname, job, ins, ip, instance, cls, seqname Number of disk blocks read from this sequence
pg_seq_last_value counter datname, job, ins, ip, instance, cls, seqname The last sequence value written to disk
pg_setting_block_size gauge job, ins, ip, instance, cls pg page block size, 8192 by default
pg_setting_data_checksums gauge job, ins, ip, instance, cls whether data checksum is enabled, 1 enabled 0 disabled
pg_setting_max_connections gauge job, ins, ip, instance, cls number of concurrent connections to the database server
pg_setting_max_locks_per_transaction gauge job, ins, ip, instance, cls no more than this many distinct objects can be locked at any one time
pg_setting_max_prepared_transactions gauge job, ins, ip, instance, cls maximum number of transactions that can be in the prepared state simultaneously
pg_setting_max_replication_slots gauge job, ins, ip, instance, cls maximum number of replication slots
pg_setting_max_wal_senders gauge job, ins, ip, instance, cls maximum number of concurrent connections from standby servers
pg_setting_max_worker_processes gauge job, ins, ip, instance, cls maximum number of background processes that the system can support
pg_setting_wal_log_hints gauge job, ins, ip, instance, cls whether wal_log_hints is enabled, 1 enabled 0 disabled
pg_size_bytes gauge datname, job, ins, ip, instance, cls File size in bytes
pg_slot_active gauge slot_name, job, ins, ip, instance, cls True(1) if this slot is currently actively being used
pg_slot_catalog_xmin counter slot_name, job, ins, ip, instance, cls The oldest transaction affecting the system catalogs that this slot needs the database to retain.
pg_slot_confirm_lsn counter slot_name, job, ins, ip, instance, cls The address (LSN) up to which the logical slot’s consumer has confirmed receiving data.
pg_slot_reset_time counter slot_name, job, ins, ip, instance, cls When statistics were last reset
pg_slot_restart_lsn counter slot_name, job, ins, ip, instance, cls The address (LSN) of oldest WAL which still might be required by the consumer of this slot
pg_slot_retained_bytes gauge slot_name, job, ins, ip, instance, cls Size of bytes that retained for this slot
pg_slot_safe_wal_size gauge slot_name, job, ins, ip, instance, cls bytes that can be written to WAL which will not make slot into lost
pg_slot_spill_bytes counter slot_name, job, ins, ip, instance, cls Bytes that spilled to disk due to logical decode mem exceeding
pg_slot_spill_count counter slot_name, job, ins, ip, instance, cls Xacts that spilled to disk due to logical decode mem exceeding (a xact can be spilled multiple times)
pg_slot_spill_txns counter slot_name, job, ins, ip, instance, cls Xacts that spilled to disk due to logical decode mem exceeding (subtrans included)
pg_slot_stream_bytes counter slot_name, job, ins, ip, instance, cls Bytes that streamed to decoding output plugin after mem exceed
pg_slot_stream_count counter slot_name, job, ins, ip, instance, cls Xacts that streamed to decoding output plugin after mem exceed (a xact can be streamed multiple times)
pg_slot_stream_txns counter slot_name, job, ins, ip, instance, cls Xacts that streamed to decoding output plugin after mem exceed
pg_slot_temporary gauge slot_name, job, ins, ip, instance, cls True(1) if this is a temporary replication slot.
pg_slot_total_bytes counter slot_name, job, ins, ip, instance, cls Number of decoded bytes sent to the decoding output plugin for this slot
pg_slot_total_txns counter slot_name, job, ins, ip, instance, cls Number of decoded xacts sent to the decoding output plugin for this slot
pg_slot_wal_status gauge slot_name, job, ins, ip, instance, cls WAL reserve status 0-3 means reserved,extended,unreserved,lost, -1 means other
pg_slot_xmin counter slot_name, job, ins, ip, instance, cls The oldest transaction that this slot needs the database to retain.
pg_slru_blks_exists counter job, ins, ip, instance, cls Number of blocks checked for existence for this SLRU
pg_slru_blks_hit counter job, ins, ip, instance, cls Number of times disk blocks were found already in the SLRU, so that a read was not necessary
pg_slru_blks_read counter job, ins, ip, instance, cls Number of disk blocks read for this SLRU
pg_slru_blks_written counter job, ins, ip, instance, cls Number of disk blocks written for this SLRU
pg_slru_blks_zeroed counter job, ins, ip, instance, cls Number of blocks zeroed during initializations
pg_slru_flushes counter job, ins, ip, instance, cls Number of flushes of dirty data for this SLRU
pg_slru_reset_time counter job, ins, ip, instance, cls Time at which these statistics were last reset
pg_slru_truncates counter job, ins, ip, instance, cls Number of truncates for this SLRU
pg_ssl_disabled gauge job, ins, ip, instance, cls Number of client connection that does not use ssl
pg_ssl_enabled gauge job, ins, ip, instance, cls Number of client connection that use ssl
pg_sync_standby_enabled gauge job, ins, ip, names, instance, cls Synchronous commit enabled, 1 if enabled, 0 if disabled
pg_table_age gauge datname, relname, job, ins, ip, instance, cls Age of this table in vacuum cycles
pg_table_analyze_count counter datname, relname, job, ins, ip, instance, cls Number of times this table has been manually analyzed
pg_table_autoanalyze_count counter datname, relname, job, ins, ip, instance, cls Number of times this table has been analyzed by the autovacuum daemon
pg_table_autovacuum_count counter datname, relname, job, ins, ip, instance, cls Number of times this table has been vacuumed by the autovacuum daemon
pg_table_frozenxid counter datname, relname, job, ins, ip, instance, cls All txid before this have been frozen on this table
pg_table_heap_blks_hit counter datname, relname, job, ins, ip, instance, cls Number of buffer hits in this table
pg_table_heap_blks_read counter datname, relname, job, ins, ip, instance, cls Number of disk blocks read from this table
pg_table_idx_blks_hit counter datname, relname, job, ins, ip, instance, cls Number of buffer hits in all indexes on this table
pg_table_idx_blks_read counter datname, relname, job, ins, ip, instance, cls Number of disk blocks read from all indexes on this table
pg_table_idx_scan counter datname, relname, job, ins, ip, instance, cls Number of index scans initiated on this table
pg_table_idx_tup_fetch counter datname, relname, job, ins, ip, instance, cls Number of live rows fetched by index scans
pg_table_kind gauge datname, relname, job, ins, ip, instance, cls Relation kind r/table/114
pg_table_n_dead_tup gauge datname, relname, job, ins, ip, instance, cls Estimated number of dead rows
pg_table_n_ins_since_vacuum gauge datname, relname, job, ins, ip, instance, cls Estimated number of rows inserted since this table was last vacuumed
pg_table_n_live_tup gauge datname, relname, job, ins, ip, instance, cls Estimated number of live rows
pg_table_n_mod_since_analyze gauge datname, relname, job, ins, ip, instance, cls Estimated number of rows modified since this table was last analyzed
pg_table_n_tup_del counter datname, relname, job, ins, ip, instance, cls Number of rows deleted
pg_table_n_tup_hot_upd counter datname, relname, job, ins, ip, instance, cls Number of rows HOT updated (i.e with no separate index update required)
pg_table_n_tup_ins counter datname, relname, job, ins, ip, instance, cls Number of rows inserted
pg_table_n_tup_mod counter datname, relname, job, ins, ip, instance, cls Number of rows modified (insert + update + delete)
pg_table_n_tup_newpage_upd counter datname, relname, job, ins, ip, instance, cls Number of rows updated where the successor version goes onto a new heap page
pg_table_n_tup_upd counter datname, relname, job, ins, ip, instance, cls Number of rows updated (includes HOT updated rows)
pg_table_ncols gauge datname, relname, job, ins, ip, instance, cls Number of columns in the table
pg_table_pages gauge datname, relname, job, ins, ip, instance, cls Size of the on-disk representation of this table in pages
pg_table_relid gauge datname, relname, job, ins, ip, instance, cls Relation oid of this table
pg_table_seq_scan counter datname, relname, job, ins, ip, instance, cls Number of sequential scans initiated on this table
pg_table_seq_tup_read counter datname, relname, job, ins, ip, instance, cls Number of live rows fetched by sequential scans
pg_table_size_bytes gauge datname, relname, job, ins, ip, instance, cls Total bytes of this table (including toast, index, toast index)
pg_table_size_indexsize gauge datname, relname, job, ins, ip, instance, cls Bytes of all related indexes of this table
pg_table_size_relsize gauge datname, relname, job, ins, ip, instance, cls Bytes of this table itself (main, vm, fsm)
pg_table_size_toastsize gauge datname, relname, job, ins, ip, instance, cls Bytes of toast tables of this table
pg_table_tbl_scan counter datname, relname, job, ins, ip, instance, cls Number of scans initiated on this table
pg_table_tup_read counter datname, relname, job, ins, ip, instance, cls Number of live rows fetched by scans
pg_table_tuples counter datname, relname, job, ins, ip, instance, cls All txid before this have been frozen on this table
pg_table_vacuum_count counter datname, relname, job, ins, ip, instance, cls Number of times this table has been manually vacuumed (not counting VACUUM FULL)
pg_timestamp gauge job, ins, ip, instance, cls database current timestamp
pg_up gauge job, ins, ip, instance, cls last scrape was able to connect to the server: 1 for yes, 0 for no
pg_uptime gauge job, ins, ip, instance, cls seconds since postmaster start
pg_version gauge job, ins, ip, instance, cls server version number
pg_wait_count gauge datname, job, ins, event, ip, instance, cls Count of WaitEvent on target database
pg_wal_buffers_full counter job, ins, ip, instance, cls Number of times WAL data was written to disk because WAL buffers became full
pg_wal_bytes counter job, ins, ip, instance, cls Total amount of WAL generated in bytes
pg_wal_fpi counter job, ins, ip, instance, cls Total number of WAL full page images generated
pg_wal_records counter job, ins, ip, instance, cls Total number of WAL records generated
pg_wal_reset_time counter job, ins, ip, instance, cls When statistics were last reset
pg_wal_sync counter job, ins, ip, instance, cls Number of times WAL files were synced to disk via issue_xlog_fsync request
pg_wal_sync_time counter job, ins, ip, instance, cls Total amount of time spent syncing WAL files to disk via issue_xlog_fsync request, in seconds
pg_wal_write counter job, ins, ip, instance, cls Number of times WAL buffers were written out to disk via XLogWrite request.
pg_wal_write_time counter job, ins, ip, instance, cls Total amount of time spent writing WAL buffers to disk via XLogWrite request in seconds
pg_write_lsn counter job, ins, ip, instance, cls primary only, location of current wal writing
pg_xact_xmax counter job, ins, ip, instance, cls First as-yet-unassigned txid. txid >= this are invisible.
pg_xact_xmin counter job, ins, ip, instance, cls Earliest txid that is still active
pg_xact_xnum gauge job, ins, ip, instance, cls Current active transaction count
pgbouncer:cls:load1 Unknown job, cls N/A
pgbouncer:cls:load15 Unknown job, cls N/A
pgbouncer:cls:load5 Unknown job, cls N/A
pgbouncer:db:conn_usage Unknown datname, job, ins, ip, instance, host, cls, real_datname, port N/A
pgbouncer:db:conn_usage_reserve Unknown datname, job, ins, ip, instance, host, cls, real_datname, port N/A
pgbouncer:db:pool_current_conn Unknown datname, job, ins, ip, instance, host, cls, real_datname, port N/A
pgbouncer:db:pool_disabled Unknown datname, job, ins, ip, instance, host, cls, real_datname, port N/A
pgbouncer:db:pool_max_conn Unknown datname, job, ins, ip, instance, host, cls, real_datname, port N/A
pgbouncer:db:pool_paused Unknown datname, job, ins, ip, instance, host, cls, real_datname, port N/A
pgbouncer:db:pool_reserve_size Unknown datname, job, ins, ip, instance, host, cls, real_datname, port N/A
pgbouncer:db:pool_size Unknown datname, job, ins, ip, instance, host, cls, real_datname, port N/A
pgbouncer:ins:free_clients Unknown job, ins, ip, instance, cls N/A
pgbouncer:ins:free_servers Unknown job, ins, ip, instance, cls N/A
pgbouncer:ins:load1 Unknown job, ins, ip, instance, cls N/A
pgbouncer:ins:load15 Unknown job, ins, ip, instance, cls N/A
pgbouncer:ins:load5 Unknown job, ins, ip, instance, cls N/A
pgbouncer:ins:login_clients Unknown job, ins, ip, instance, cls N/A
pgbouncer:ins:pool_databases Unknown job, ins, ip, instance, cls N/A
pgbouncer:ins:pool_users Unknown job, ins, ip, instance, cls N/A
pgbouncer:ins:pools Unknown job, ins, ip, instance, cls N/A
pgbouncer:ins:used_clients Unknown job, ins, ip, instance, cls N/A
pgbouncer_database_current_connections gauge datname, job, ins, ip, instance, host, cls, real_datname, port Current number of connections for this database
pgbouncer_database_disabled gauge datname, job, ins, ip, instance, host, cls, real_datname, port True(1) if this database is currently disabled, else 0
pgbouncer_database_max_connections gauge datname, job, ins, ip, instance, host, cls, real_datname, port Maximum number of allowed connections for this database
pgbouncer_database_min_pool_size gauge datname, job, ins, ip, instance, host, cls, real_datname, port Minimum number of server connections
pgbouncer_database_paused gauge datname, job, ins, ip, instance, host, cls, real_datname, port True(1) if this database is currently paused, else 0
pgbouncer_database_pool_size gauge datname, job, ins, ip, instance, host, cls, real_datname, port Maximum number of server connections
pgbouncer_database_reserve_pool gauge datname, job, ins, ip, instance, host, cls, real_datname, port Maximum number of additional connections for this database
pgbouncer_exporter_agent_up Unknown job, ins, ip, instance, cls N/A
pgbouncer_exporter_last_scrape_time gauge job, ins, ip, instance, cls seconds exporter spending on scrapping
pgbouncer_exporter_query_cache_ttl gauge datname, query, job, ins, ip, instance, cls times to live of query cache
pgbouncer_exporter_query_scrape_duration gauge datname, query, job, ins, ip, instance, cls seconds query spending on scrapping
pgbouncer_exporter_query_scrape_error_count gauge datname, query, job, ins, ip, instance, cls times the query failed
pgbouncer_exporter_query_scrape_hit_count gauge datname, query, job, ins, ip, instance, cls numbers been scrapped from this query
pgbouncer_exporter_query_scrape_metric_count gauge datname, query, job, ins, ip, instance, cls numbers of metrics been scrapped from this query
pgbouncer_exporter_query_scrape_total_count gauge datname, query, job, ins, ip, instance, cls times exporter server was scraped for metrics
pgbouncer_exporter_scrape_duration gauge job, ins, ip, instance, cls seconds exporter spending on scrapping
pgbouncer_exporter_scrape_error_count counter job, ins, ip, instance, cls times exporter was scraped for metrics and failed
pgbouncer_exporter_scrape_total_count counter job, ins, ip, instance, cls times exporter was scraped for metrics
pgbouncer_exporter_server_scrape_duration gauge datname, job, ins, ip, instance, cls seconds exporter server spending on scrapping
pgbouncer_exporter_server_scrape_total_count gauge datname, job, ins, ip, instance, cls times exporter server was scraped for metrics
pgbouncer_exporter_server_scrape_total_seconds gauge datname, job, ins, ip, instance, cls seconds exporter server spending on scrapping
pgbouncer_exporter_up gauge job, ins, ip, instance, cls always be 1 if your could retrieve metrics
pgbouncer_exporter_uptime gauge job, ins, ip, instance, cls seconds since exporter primary server inited
pgbouncer_in_recovery gauge job, ins, ip, instance, cls server is in recovery mode? 1 for yes 0 for no
pgbouncer_list_items gauge job, ins, ip, instance, list, cls Number of corresponding pgbouncer object
pgbouncer_pool_active_cancel_clients gauge datname, job, ins, ip, instance, user, cls, pool_mode Client connections that have forwarded query cancellations to the server and are waiting for the server response.
pgbouncer_pool_active_cancel_servers gauge datname, job, ins, ip, instance, user, cls, pool_mode Server connections that are currently forwarding a cancel request
pgbouncer_pool_active_clients gauge datname, job, ins, ip, instance, user, cls, pool_mode Client connections that are linked to server connection and can process queries
pgbouncer_pool_active_servers gauge datname, job, ins, ip, instance, user, cls, pool_mode Server connections that are linked to a client
pgbouncer_pool_cancel_clients gauge datname, job, ins, ip, instance, user, cls, pool_mode Client connections that have not forwarded query cancellations to the server yet.
pgbouncer_pool_cancel_servers gauge datname, job, ins, ip, instance, user, cls, pool_mode cancel requests have completed that were sent to cancel a query on this server
pgbouncer_pool_idle_servers gauge datname, job, ins, ip, instance, user, cls, pool_mode Server connections that are unused and immediately usable for client queries
pgbouncer_pool_login_servers gauge datname, job, ins, ip, instance, user, cls, pool_mode Server connections currently in the process of logging in
pgbouncer_pool_maxwait gauge datname, job, ins, ip, instance, user, cls, pool_mode How long the first(oldest) client in the queue has waited, in seconds, key metric
pgbouncer_pool_maxwait_us gauge datname, job, ins, ip, instance, user, cls, pool_mode Microsecond part of the maximum waiting time.
pgbouncer_pool_tested_servers gauge datname, job, ins, ip, instance, user, cls, pool_mode Server connections that are currently running reset or check query
pgbouncer_pool_used_servers gauge datname, job, ins, ip, instance, user, cls, pool_mode Server connections that have been idle for more than server_check_delay (means have to run check query)
pgbouncer_pool_waiting_clients gauge datname, job, ins, ip, instance, user, cls, pool_mode Client connections that have sent queries but have not yet got a server connection
pgbouncer_stat_avg_query_count gauge datname, job, ins, ip, instance, cls Average queries per second in last stat period
pgbouncer_stat_avg_query_time gauge datname, job, ins, ip, instance, cls Average query duration, in seconds
pgbouncer_stat_avg_recv gauge datname, job, ins, ip, instance, cls Average received (from clients) bytes per second
pgbouncer_stat_avg_sent gauge datname, job, ins, ip, instance, cls Average sent (to clients) bytes per second
pgbouncer_stat_avg_wait_time gauge datname, job, ins, ip, instance, cls Time spent by clients waiting for a server, in seconds (average per second).
pgbouncer_stat_avg_xact_count gauge datname, job, ins, ip, instance, cls Average transactions per second in last stat period
pgbouncer_stat_avg_xact_time gauge datname, job, ins, ip, instance, cls Average transaction duration, in seconds
pgbouncer_stat_total_query_count gauge datname, job, ins, ip, instance, cls Total number of SQL queries pooled by pgbouncer
pgbouncer_stat_total_query_time counter datname, job, ins, ip, instance, cls Total number of seconds spent when executing queries
pgbouncer_stat_total_received counter datname, job, ins, ip, instance, cls Total volume in bytes of network traffic received by pgbouncer
pgbouncer_stat_total_sent counter datname, job, ins, ip, instance, cls Total volume in bytes of network traffic sent by pgbouncer
pgbouncer_stat_total_wait_time counter datname, job, ins, ip, instance, cls Time spent by clients waiting for a server, in seconds
pgbouncer_stat_total_xact_count gauge datname, job, ins, ip, instance, cls Total number of SQL transactions pooled by pgbouncer
pgbouncer_stat_total_xact_time counter datname, job, ins, ip, instance, cls Total number of seconds spent when in a transaction
pgbouncer_up gauge job, ins, ip, instance, cls last scrape was able to connect to the server: 1 for yes, 0 for no
pgbouncer_version gauge job, ins, ip, instance, cls server version number
process_cpu_seconds_total counter job, ins, ip, instance, cls Total user and system CPU time spent in seconds.
process_max_fds gauge job, ins, ip, instance, cls Maximum number of open file descriptors.
process_open_fds gauge job, ins, ip, instance, cls Number of open file descriptors.
process_resident_memory_bytes gauge job, ins, ip, instance, cls Resident memory size in bytes.
process_start_time_seconds gauge job, ins, ip, instance, cls Start time of the process since unix epoch in seconds.
process_virtual_memory_bytes gauge job, ins, ip, instance, cls Virtual memory size in bytes.
process_virtual_memory_max_bytes gauge job, ins, ip, instance, cls Maximum amount of virtual memory available in bytes.
promhttp_metric_handler_requests_in_flight gauge job, ins, ip, instance, cls Current number of scrapes being served.
promhttp_metric_handler_requests_total counter code, job, ins, ip, instance, cls Total number of scrapes by HTTP status code.
scrape_duration_seconds Unknown job, ins, ip, instance, cls N/A
scrape_samples_post_metric_relabeling Unknown job, ins, ip, instance, cls N/A
scrape_samples_scraped Unknown job, ins, ip, instance, cls N/A
scrape_series_added Unknown job, ins, ip, instance, cls N/A
up Unknown job, ins, ip, instance, cls N/A

15 - FAQ

Pigsty PGSQL module frequently asked questions

ABORT due to postgres exists

Set pg_clean = true and pg_safeguard = false to force clean postgres data during pgsql.yml

This happens when you run pgsql.yml on a node with postgres running, and pg_clean is set to false.

If pg_clean is true (and the pg_safeguard is false, too), the pgsql.yml playbook will remove the existing pgsql data and re-init it as a new one, which makes this playbook fully idempotent.

You can still purge the existing PostgreSQL data by using a special task tag pg_purge

./pgsql.yml -t pg_clean      # honor pg_clean and pg_safeguard
./pgsql.yml -t pg_purge      # ignore pg_clean and pg_safeguard

ABORT due to pg_safeguard enabled

Disable pg_safeguard to remove the Postgres instance.

If pg_safeguard is enabled, you can not remove the running pgsql instance with bin/pgsql-rm and pgsql-rm.yml playbook.

To disable pg_safeguard, you can set pg_safeguard to false in the inventory or pass -e pg_safeguard=false as cli arg to the playbook:

./pgsql-rm.yml -e pg_safeguard=false -l <cls_to_remove>    # force override pg_safeguard

Fail to wait for postgres/patroni primary

There are several possible reasons for this error, and you need to check the system logs to determine the actual cause.

This usually happens when the cluster is misconfigured, or the previous primary is improperly removed. (e.g., trash metadata in DCS with the same cluster name).

You must check /pg/log/* to find the reason.

To delete trash meta from etcd, you can use etcdctl del --prefix /pg/<cls>, do with caution!

  • 1: Misconfiguration. Identify the incorrect parameters, modify them, and apply the changes.
  • 2: Another cluster with the same cls name already exists in the deployment
  • 3: The previous cluster on the node, or previous cluster with same name was not correctly removed.
    • To remove obsolete cluster metadata, you can use etcdctl del --prefix /pg/<cls> to manually delete the residual data.
  • 4: The RPM packages related to your PostgreSQL or node were not successfully installed.
  • 5: Your Watchdog kernel module was not correctly enabled or loaded, but required.
  • 6: The locale or ctype specified pg_lc_collate and pg_lc_ctype does not exist in OS

Feel free to submit an issue or seek help from the community.


Fail to wait for postgres/patroni replica

Failed Immediately: Usually, this happens because of misconfiguration, network issues, broken DCS metadata, etc…, you have to inspect /pg/log to find out the actual reason.

Failed After a While: This may be due to source instance data corruption. Check PGSQL FAQ: How to create replicas when data is corrupted?

Timeout: If the wait for postgres replica task takes 30min or more and fails due to timeout, This is common for a huge cluster (e.g., 1TB+, which may take hours to create a replica). In this case, the underlying creating replica procedure is still proceeding. You can check cluster status with pg list <cls> and wait until the replica catches up with the primary. Then continue the following tasks:

./pgsql.yml -t pg_hba,pg_backup,pgbouncer,pg_vip,pg_dns,pg_service,pg_exporter,pg_register -l <problematic_replica>

Install PostgreSQL 12 - 15

To install PostgreSQL 12 - 15, you have to set pg_version to 12, 13, 14, or 15 in the inventory. (usually at cluster level)

pg_version: 16                    # install pg 16 in this template
pg_libs: 'pg_stat_statements, auto_explain' # remove timescaledb from pg 16 beta
pg_extensions: []                 # missing pg16 extensions for now

How enable hugepage for PostgreSQL?

use node_hugepage_count and node_hugepage_ratio or /pg/bin/pg-tune-hugepage

If you plan to enable hugepage, consider using node_hugepage_count and node_hugepage_ratio and apply with ./node.yml -t node_tune .

It’s good to allocate enough hugepage before postgres start, and use pg_tune_hugepage to shrink them later.

If your postgres is already running, you can use /pg/bin/pg-tune-hugepage to enable hugepage on the fly. Note that this only works on PostgreSQL 15+

sync; echo 3 > /proc/sys/vm/drop_caches   # drop system cache (ready for performance impact)
sudo /pg/bin/pg-tune-hugepage             # write nr_hugepages to /etc/sysctl.d/hugepage.conf
pg restart <cls>                          # restart postgres to use hugepage

How to guarantee zero data loss during failover?

Use crit.yml template, or setting pg_rpo to 0, or config cluster with synchronous mode.

Consider using Sync Standby and Quorum Comit to guarantee 0 data loss during failover.


How to survive from disk full?

rm -rf /pg/dummy will free some emergency space.

The pg_dummy_filesize is set to 64MB by default. Consider increasing it to 8GB or larger in the production environment.

It will be placed on /pg/dummy same disk as the PGSQL main data disk. You can remove that file to free some emergency space. At least you can run some shell scripts on that node.


How to create replicas when data is corrupted?

Disable clonefrom on bad instances and reload patroni config.

Pigsty sets the cloneform: true tag on all instances’ patroni config, which marks the instance available for cloning replica.

If this instance has corrupt data files, you can set clonefrom: false to avoid pulling data from the evil instance. To do so:

$ vi /pg/bin/patroni.yml

tags:
  nofailover: false
  clonefrom: true      # ----------> change to false
  noloadbalance: false
  nosync: false
  version:  '15'
  spec: '4C.8G.50G'
  conf: 'oltp.yml'
  
$ systemctl reload patroni

How to create replicas when data is corrupted?

Disable clonefrom on bad instances and reload patroni config.

Pigsty sets the cloneform: true tag on all instances’ patroni config, which marks the instance available for cloning replica.

If this instance has corrupt data files, you can set clonefrom: false to avoid pulling data from the evil instance. To do so:

$ vi /pg/bin/patroni.yml

tags:
  nofailover: false
  clonefrom: true      # ----------> change to false
  noloadbalance: false
  nosync: false
  version:  '15'
  spec: '4C.8G.50G'
  conf: 'oltp.yml'
  
$ systemctl reload patroni

Performance impact of monitoring exporter

Not very much, 200ms per 10 ~ 15 seconds, won’t affect the database performance.

The default scrape interval for prometheus is 10s in pigsty, make sure the exporter can finish the scrape within that period.


How to monitor an existing PostgreSQL instance?

Check PGSQL Monitor for details.


How to remove monitor targets from prometheus?

./pgsql-rm.yml -t prometheus -l <cls>     # remove prometheus targets of cluster 'cls'

Or

bin/pgmon-rm <ins>     # shortcut for removing prometheus targets of pgsql instance 'ins'