Administration

• 1: Managing PostgreSQL Clusters
• 2: Managing PostgreSQL Users
• 3: Managing PostgreSQL Databases
• 4: Patroni HA Management
• 5: Managing PostgreSQL HBA Rules
• 6: Pgbouncer Connection Pooling
• 7: Managing PostgreSQL Component Services
• 8: Manage PostgreSQL Cron Jobs
• 9: Managing PostgreSQL Extensions
• 10: Upgrading PostgreSQL Major/Minor Versions

1 - Managing PostgreSQL Clusters

Quick Reference

For other management tasks, see: HA Management, Manage Users, Manage Databases.

Create Cluster

To create a new PostgreSQL cluster, first define the cluster in the inventory, then add nodes and initialize:

bin/node-add  <cls>     # Add nodes in group <cls>

./node.yml  -l <cls>    # Use Ansible playbook to add nodes in group <cls>

bin/node-add pg-test    # Add nodes in pg-test group, runs ./node.yml -l pg-test

On managed nodes, create the cluster with: (Execute pgsql.yml playbook on <cls> group)

bin/pgsql-add <cls>     # Create PostgreSQL cluster <cls>

./pgsql.yml -l <cls>    # Use Ansible playbook to create PostgreSQL cluster <cls>

bin/pgsql-add pg-test   # Create pg-test cluster

Example: Create 3-node PG cluster pg-test

Expand Cluster

To add a new replica to an existing PostgreSQL cluster, add the instance definition to inventory: all.children.<cls>.hosts.

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 }

Scaling out is similar to creating a cluster. First add the new node to Pigsty: Add Node:

bin/node-add <ip>       # Add node with IP <ip>

./node.yml -l <ip>      # Use Ansible playbook to add node <ip>

bin/node-add 10.10.10.13    # Add node 10.10.10.13, runs ./node.yml -l 10.10.10.13

Then run the following on the new node to scale out (Install PGSQL module on new node with same pg_cluster):

bin/pgsql-add <cls> <ip>  # Add node <ip> to cluster

./pgsql.yml -l <ip>       # Core: Use Ansible playbook to install PGSQL module on <ip>

bin/pgsql-add pg-test 10.10.10.13   # Scale out pg-test with node 10.10.10.13

After scaling, you should Reload Service to add the new member to load balancer.

Example: Add replica 10.10.10.13 to 2-node cluster pg-test

Shrink Cluster

To remove a replica from an existing PostgreSQL cluster, remove the instance definition from inventory all.children.<cls>.hosts.

Scale-in stops the instance and deletes its data directory by default. First run pig pg list <cls> and pig pb info, verify that the target is not the primary and that a recent restorable backup exists, then have the operator enter the exact <ip> and execute only after confirmation.

First uninstall PGSQL module from target node (Execute pgsql-rm.yml on <ip>):

bin/pgsql-rm <cls> <ip>   # Remove the PostgreSQL instance on <ip> from cluster <cls>

./pgsql-rm.yml -l <ip>    # Directly remove the PostgreSQL instance on <ip> with the Ansible playbook

bin/pgsql-rm pg-test 10.10.10.13  # Remove node 10.10.10.13 from pg-test

After removing PGSQL module, optionally remove the node from Pigsty: Remove Node:

bin/node-rm <ip>          # Remove <ip> from Pigsty management

./node-rm.yml -l <ip>     # Directly remove <ip> from Pigsty management with the Ansible playbook

bin/node-rm 10.10.10.13   # Remove 10.10.10.13 from Pigsty management

After scaling in, remove the instance from inventory, then Reload Service to remove it from load balancer.

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 after execution
  vars: { pg_cluster: pg-test }

Example: Remove replica 10.10.10.13 from 3-node cluster pg-test

Remove Cluster

To destroy a cluster, uninstall PGSQL module from all nodes (Execute pgsql-rm.yml on <cls>):

This is irreversible data deletion. Inspect pig pg list <cls> and pig pb info, verify a recent backup and any independent copy to retain, and have the operator enter the exact cluster name. The commands below perform the corresponding destruction directly.

bin/pgsql-rm <cls>        # Destroy the entire PostgreSQL cluster <cls>

./pgsql-rm.yml -l <cls>   # Directly destroy the entire PostgreSQL cluster <cls> with the Ansible playbook

bin/pgsql-rm pg-test      # Destroy cluster pg-test

After destroying PGSQL, optionally remove all nodes from Pigsty: Remove Node (optional if other services exist):

bin/node-rm <cls>         # Remove every node in group <cls> from Pigsty management

./node-rm.yml -l <cls>    # Directly remove the nodes in group <cls> from Pigsty management with the Ansible playbook

bin/node-rm pg-test       # Remove every node in group pg-test from Pigsty management

After removal, delete the entire cluster definition from inventory.

pg-test: # remove this cluster definition group
  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 }

Example: Destroy 3-node PG cluster pg-test

Note: If pg_safeguard is configured (or globally true), pgsql-rm.yml will abort to prevent accidental removal. Override with playbook command line to force removal. By default, cluster backup repo is deleted with the cluster. To preserve backups (e.g., with centralized repo), set pg_rm_backup=false:

./pgsql-rm.yml -l pg-meta -e pg_safeguard=false    # Force removal of protected pg-meta
./pgsql-rm.yml -l pg-meta -e pg_rm_backup=false    # Preserve its backup repository while removing the cluster

Reload Service

PostgreSQL clusters expose services via HAProxy on host nodes. When service definitions, instance weights, or cluster membership change (for example, scale out or scale in), reload services to update HAProxy’s static member configuration. The default Primary and Replica services detect the current role through Patroni REST API health checks, so ordinary switchover or failover reroutes automatically and does not require regenerating HAProxy configuration.

To reload service config on entire cluster or specific instances (Execute pg_service subtask of pgsql.yml on <cls> or <ip>):

bin/pgsql-svc <cls>           # Reload service config for entire cluster <cls>
bin/pgsql-svc <cls> <ip...>   # Reload service config for specific instances

./pgsql.yml -l <cls> -t pg_service -e pg_reload=true        # Reload entire cluster
./pgsql.yml -l <ip>  -t pg_service -e pg_reload=true        # Reload specific instance

bin/pgsql-svc pg-test                 # Reload pg-test cluster service config
bin/pgsql-svc pg-test 10.10.10.13     # Reload pg-test 10.10.10.13 instance service config

Example: Reload pg-test cluster service config

Reload HBA

When HBA configs change, reload HBA rules to apply. (pg_hba_rules / pgb_hba_rules) If you have inventory-role-specific HBA rules or address ranges that reference cluster member aliases, reload HBA after changing pg_role labels or scaling the cluster. Role selectors use static inventory variables and do not change automatically after a Patroni switchover.

To reload PG and Pgbouncer HBA rules on entire cluster or specific instances (Execute HBA subtasks of pgsql.yml on <cls> or <ip>):

bin/pgsql-hba <cls>           # Reload HBA rules for entire cluster <cls>
bin/pgsql-hba <cls> <ip...>   # Reload HBA rules for specific instances

./pgsql.yml -l <cls> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true   # Reload entire cluster
./pgsql.yml -l <ip>  -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true   # Reload specific instance

bin/pgsql-hba pg-test                 # Reload pg-test cluster HBA rules
bin/pgsql-hba pg-test 10.10.10.13     # Reload pg-test 10.10.10.13 instance HBA rules

Example: Reload pg-test cluster HBA rules

Config Cluster

PostgreSQL config params are managed by Patroni. Initial params are specified by Patroni config template. After cluster init, config is stored in Etcd, dynamically managed and synced by Patroni. Most Patroni config params can be modified via patronictl. Other params (e.g., etcd DCS config, log/RestAPI config) can be updated via subtasks. For example, when etcd cluster membership changes, refresh Patroni config:

./pgsql.yml -l pg-test -t pg_conf                   # Update Patroni config file
ansible pg-test -b -a 'systemctl reload patroni'    # Reload Patroni service

You can override Patroni-managed defaults at different levels: specify params per instance, specify params per user, or specify params per database.

Clone Cluster

Two ways to clone a cluster: use Standby Cluster, or use Point-in-Time Recovery. The former is simple and requires no backup repository, but it does require a reachable replication upstream and can clone only the latest state. The latter requires a centralized backup repository such as Silo and can clone to any point within the retention period.

Clone via Standby Cluster

Standby Cluster continuously syncs from upstream cluster via streaming replication - the simplest cloning method. Specify pg_upstream on the new cluster primary to auto-pull data from upstream.

# 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 standby cluster (clone) of pg-test
pg-test2:
  hosts:
    10.10.10.12: { pg_seq: 1, pg_role: primary, pg_upstream: 10.10.10.11 }  # specify upstream
    10.10.10.13: { pg_seq: 2, pg_role: replica }
  vars: { pg_cluster: pg-test2 }

Create standby cluster with:

bin/pgsql-add pg-test2    # Create standby cluster, auto-clone from upstream pg-test

./pgsql.yml -l pg-test2   # Use Ansible playbook to create standby cluster

Standby cluster follows upstream, keeping data in sync. Promote to independent cluster anytime:

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

Apply these changes? [y/N]: y

$ pg edit-config pg-test2

 standby_cluster:
   create_replica_methods:
   - basebackup
-  host: 10.10.10.11     # <--- old upstream
+  host: 10.10.10.14     # <--- new upstream
   port: 5432

Apply these changes? [y/N]: y

Clone via PITR

Point-in-Time Recovery (PITR) allows recovery to any point within backup retention. Requires a centralized backup repository (Silo/S3), but is more powerful.

To clone via PITR, add pg_pitr param specifying recovery target:

# Clone new cluster pg-meta2 from pg-meta backup
pg-meta2:
  hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta2
    pg_pitr:
      cluster: pg-meta                    # Recover from pg-meta backup
      time: '2025-01-10 10:00:00+00'      # Recover to specific time
      archive: false                       # Disable archiving during the independent restore
      action: promote                      # Promote after replay in this one-shot example

Execute clone with pgsql-pitr.yml playbook:

./pgsql-pitr.yml -l pg-meta2    # Use the explicitly declared action: promote above

# Specify PITR options via command line
./pgsql-pitr.yml -l pg-meta2 -e '{"pg_pitr": {"cluster": "pg-meta", "time": "2025-01-10 10:00:00+00", "archive": false, "action": "promote"}}'

PITR supports multiple recovery target types:

psql -c 'ALTER SYSTEM RESET archive_mode;'
pg restart <cls>
pg-backup full    # Execute new full backup

For detailed PITR usage, see Restore Operations documentation.

2 - Managing PostgreSQL Users

Quick Start

Pigsty uses declarative management: first define users in the inventory, then use bin/pgsql-user <cls> <username> to create or modify.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_users: [{ name: dbuser_app, password: 'DBUser.App', pgbouncer: true }]  # <--- Define user list here!

bin/pgsql-user <cls> <username>    # Create/modify <username> user on <cls> cluster

./pgsql-user.yml -l pg-meta -e username=dbuser_app    # Use playbook to create/modify user

bin/pgsql-user pg-meta dbuser_app    # Create/modify dbuser_app user on pg-meta cluster

For the complete user definition reference, see User Configuration. See Access Control for roles and privileges, and Authentication for credential management.

name is the key used by pgsql-user.yml to look up a user definition; the playbook does not rename roles. For a rename, create the replacement role, migrate ownership, memberships, and client credentials, validate the cutover, and only then remove the old role. Do not treat delete-and-create as a lossless rename.

Create User

Users defined in pg_users are auto-created during PostgreSQL cluster creation in the pg_user task.

To create a new user on an existing cluster, add user definition to all.children.<cls>.pg_users, then execute:

bin/pgsql-user <cls> <username>   # Create user <username>

./pgsql-user.yml -l <cls> -e username=<username>   # Use Ansible playbook

bin/pgsql-user pg-meta dbuser_app    # Create dbuser_app user in pg-meta cluster

Example: Create business user dbuser_app

#all.children.pg-meta.vars.pg_users:
  - name: dbuser_app
    password: DBUser.App
    pgbouncer: true
    roles: [dbrole_readwrite]
    comment: application user for myapp

Result: Creates dbuser_app user on primary, sets password, grants dbrole_readwrite role, adds to Pgbouncer pool, reloads Pgbouncer config on all instances.

Modify User

Same command as create - playbook is idempotent. When target user exists, Pigsty modifies properties to match config.

bin/pgsql-user <cls> <user>   # Modify user <user> properties

./pgsql-user.yml -l <cls> -e username=<user>   # Idempotent, can repeat

bin/pgsql-user pg-meta dbuser_app    # Modify dbuser_app to match config

Not directly mutable: name is the identity key in the declarative definition. The playbook does not rename an existing role. Use a controlled create, ownership/privilege and client migration, validation, and old-role removal sequence.

All other properties can be modified. Common examples:

Modify password: Update password field. Logging is temporarily disabled during password change to prevent leakage.

- name: dbuser_app
  password: NewSecretPassword     # New password

Modify privilege attributes: Configure boolean flags for user privileges.

- name: dbuser_app
  superuser: false           # Superuser (use carefully!)
  createdb: true             # Allow CREATE DATABASE
  createrole: false          # Allow CREATE ROLE
  inherit: true              # Auto-inherit role privileges
  replication: false         # Allow streaming replication
  bypassrls: false           # Bypass row-level security
  connlimit: 50              # Connection limit, -1 unlimited

Modify expiration: Use expire_in for relative expiry (N days), or expire_at for absolute date. expire_in takes priority and recalculates on each playbook run - good for temp users needing periodic renewal.

- name: temp_user
  expire_in: 30                   # Expires in 30 days (relative)

- name: contractor_user
  expire_at: '2024-12-31'         # Expires on date (absolute)

- name: permanent_user
  expire_at: 'infinity'           # Never expires

Modify role membership: Use roles array with simple or extended format. Role membership is additive - won’t remove undeclared existing roles. Use state: absent to explicitly revoke.

- name: dbuser_app
  roles:
    - dbrole_readwrite                      # Simple form: grant role
    - { name: dbrole_admin, admin: true }   # With ADMIN OPTION
    - { name: pg_monitor, set: false }      # PG16+: disallow SET ROLE
    - { name: old_role, state: absent }     # Revoke role membership

Manage user parameters: Use parameters dict for user-level params, generates ALTER USER ... SET. Use DEFAULT to reset.

- name: dbuser_analyst
  parameters:
    work_mem: '256MB'
    statement_timeout: '5min'
    search_path: 'analytics,public'
    log_statement: DEFAULT        # Reset to default

Connection pool config: Set pgbouncer: true to add user to pool. Optional pool_mode and pool_connlimit.

- name: dbuser_app
  pgbouncer: true                 # Add to pool
  pool_mode: transaction          # Pool mode
  pool_connlimit: 50              # Max user connections

Delete User

Deleting a user terminates sessions, transfers object ownership, revokes grants, and runs DROP ROLE; it is irreversible. Confirm the exact cluster, role, successor owner, and a recent backup before setting the user to state: absent and applying the change.

bin/pgsql-user <cls> <user>   # Actual deletion after confirmation; config must say state: absent

./pgsql-user.yml -l <cls> -e username=<user>   # Apply the user definition with state: absent

bin/pgsql-user pg-meta dbuser_old    # Delete dbuser_old (config has state: absent)

Config example:

pg_users:
  - name: dbuser_old
    state: absent

Deletion process: On the primary, the task runs pg-drop-role <user> postgres --force. It disables login, terminates active sessions, transfers database and tablespace ownership plus objects in each connectable database to postgres, runs DROP OWNED to remove grants, revokes role memberships, and finally runs DROP ROLE. A pre-change audit snapshot is written to /tmp/pg_drop_role_<user>_<timestamp>.log.

Protection: The Ansible task skips postgres and the replication, admin, and monitor usernames configured in inventory. When invoked directly, pg-drop-role protects only the hard-coded default names postgres, replicator, dbuser_dba, and dbuser_monitor; renamed system accounts are not recognized automatically.

In v4.5, pgsql-user.yml reloads Pgbouncer but does not reliably prune a deleted role from /etc/pgbouncer/userlist.txt. Check every cluster instance after deletion:

sudo -iu postgres psql -AXtwc "SELECT 1 FROM pg_roles WHERE rolname = 'dbuser_old';"
grep -n '^"dbuser_old"[[:space:]]' /etc/pgbouncer/userlist.txt

If an exact Pgbouncer entry remains, remove that single line under change control, reload Pgbouncer, and validate application connections. Do not use a broad pattern to delete entries.

Manual Deletion

For manual user deletion, use pg-drop-role script directly:

# Check dependencies (read-only)
pg-drop-role dbuser_old --check

# Preview deletion (don't execute)
pg-drop-role dbuser_old --dry-run -v

# Only after confirming a recent backup, exact role, and successor owner
pg-drop-role dbuser_old dbuser_new

# Use --force only after explicitly approving session termination
pg-drop-role dbuser_old dbuser_new --force

Common Use Cases

Common user configuration examples:

Basic business user

- name: dbuser_app
  password: DBUser.App
  pgbouncer: true
  roles: [dbrole_readwrite]
  comment: application user

Read-only user

- name: dbuser_readonly
  password: DBUser.Readonly
  pgbouncer: true
  roles: [dbrole_readonly]

Admin user (can execute DDL)

- name: dbuser_admin
  password: DBUser.Admin
  pgbouncer: true
  pool_mode: session
  roles: [dbrole_admin]
  parameters:
    log_statement: 'all'

Temp user (expires in 30 days)

- name: temp_contractor
  password: TempPassword
  expire_in: 30
  roles: [dbrole_readonly]

Role (no login, for permission grouping)

- name: custom_role
  login: false
  comment: custom role for special permissions

User with advanced role options (PG16+)

- name: dbuser_special
  password: DBUser.Special
  pgbouncer: true
  roles:
    - dbrole_readwrite
    - { name: dbrole_admin, admin: true }
    - { name: pg_monitor, set: false }
    - { name: pg_execute_server_program, inherit: false }

Query Users

Common SQL queries for user info:

List all users

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb,
       rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil
FROM pg_roles WHERE rolname NOT LIKE 'pg_%' ORDER BY rolname;

View user role membership

SELECT r.rolname AS member, g.rolname AS role, m.admin_option, m.set_option, m.inherit_option
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.member
JOIN pg_roles g ON g.oid = m.roleid
WHERE r.rolname = 'dbuser_app';

View user-level parameters

SELECT rolname, setconfig FROM pg_db_role_setting s
JOIN pg_roles r ON r.oid = s.setrole WHERE s.setdatabase = 0;

View expiring users

SELECT rolname, rolvaliduntil, rolvaliduntil - CURRENT_TIMESTAMP AS time_remaining
FROM pg_roles WHERE rolvaliduntil IS NOT NULL
  AND rolvaliduntil < CURRENT_TIMESTAMP + INTERVAL '30 days'
ORDER BY rolvaliduntil;

Connection Pool Management

Connection pool params in user definitions are applied to Pgbouncer when creating/modifying users.

Users with pgbouncer: true are added to /etc/pgbouncer/userlist.txt. User-level pool params (pool_mode, pool_connlimit) are configured via /etc/pgbouncer/useropts.txt.

Use postgres OS user with pgb alias to access Pgbouncer admin database. For more pool management, see Pgbouncer Management.

Manage Default-User Passwords

For a business user, follow Modify User: persist the new password in its pg_users definition, preview the scoped playbook, and then apply it. The three default users require extra coordination because other services consume their credentials.

These accounts belong to pg_default_roles, not pg_users. pgsql-user.yml looks up only pg_users, so do not rotate a default password by overriding pg_users on the command line: that changes the business-user list visible to that run and exposes plaintext in shell history.

Rotate one account at a time:

1. Persist the new parameter in pigsty.yml or the inventory actually in use; never put the plaintext password on the command line.
2. On the current primary, open interactive psql as a superuser and run \password <username>; the meta-command reads the secret interactively.
3. Run the corresponding refresh playbooks below after verifying the -l cluster/node scope.
4. Keep the current administration session open and verify direct PostgreSQL, Pgbouncer, replication, exporters, and Grafana data sources before rotating another account.

# On the current primary of the target cluster
sudo -iu postgres psql -d postgres
\password dbuser_dba       # or dbuser_monitor / replicator

Refresh every consumer for the account. Replace <cls> and constrain infra to the actual targets:

# dbuser_dba: PG-node .pgpass, Pgbouncer, Infra admin files, and pgAdmin files
./pgsql.yml -l <cls> -t pg_pass,pgbouncer_user,pgbouncer_reload -e pg_reload=true
./infra.yml -l infra -t env_pgpass,env_pgscv,env_pgadmin

# dbuser_monitor: PG-node .pgpass, Pgbouncer, exporters, and Grafana data sources
./pgsql.yml -l <cls> -t pg_pass,pgbouncer_user,pgbouncer_reload,pg_exporter,pgbouncer_exporter,add_ds -e pg_reload=true
./infra.yml -l infra -t env_pgpass

# replicator: Patroni configuration plus PostgreSQL-node and Infra .pgpass files
./pgsql.yml -l <cls> -t pg_conf,pg_pass,patroni_reload -e pg_reload=true
./infra.yml -l infra -t env_pgpass

A mismatch between the replication role and Patroni nodes prevents new replication connections, so rotate that credential in a maintenance window and validate promptly. If VIBE or another module has rendered an admin connection string into its workspace context, rerender that module’s files as well.

awk -F: '$4=="dbuser_dba" || $4=="dbuser_monitor" || $4=="replicator" {print NR, $4}' ~/.pgpass

patroni_password protects the Patroni REST API; it is not a PostgreSQL role password. After changing it in inventory, refresh the target PostgreSQL cluster and Infra management side separately:

./pgsql.yml -l <cls> -t pg_conf,patroni_reload -e pg_reload=true
./infra.yml -l infra -t env_patroni

Then validate authentication and cluster state with patronictl or pig pg list <cls>.

3 - Managing PostgreSQL Databases

Quick Start

Pigsty uses declarative management: first define databases in the inventory, then use bin/pgsql-db <cls> <dbname> to create or modify.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_databases: [{ name: some_db }]  # <--- Define database list here!

bin/pgsql-db <cls> <dbname>    # Create/modify <dbname> database on <cls> cluster

./pgsql-db.yml -l pg-meta -e dbname=some_db    # Use playbook to create/modify database

bin/pgsql-db pg-meta some_db    # Create/modify some_db database on pg-meta cluster

For the complete database definition reference, see Database Configuration. For database access permissions, see Access Control: Database Isolation.

Note: Some parameters can only be specified at creation time. Modifying these requires recreating the database (use state: recreate).

Create Database

Databases defined in pg_databases are auto-created during PostgreSQL cluster creation in the pg_db task.

To create a new database on an existing cluster, add database definition to all.children.<cls>.pg_databases, then execute:

bin/pgsql-db <cls> <dbname>   # Create database <dbname>

./pgsql-db.yml -l <cls> -e dbname=<dbname>   # Use Ansible playbook

bin/pgsql-db pg-meta myapp    # Create myapp database in pg-meta cluster

Example: Create business database myapp

#all.children.pg-meta.vars.pg_databases:
  - name: myapp
    owner: dbuser_myapp
    schemas: [app]
    extensions:
      - { name: pg_trgm }
      - { name: btree_gin }
    comment: my application database

Result: Creates myapp database on primary, sets owner to dbuser_myapp, creates app schema, enables pg_trgm and btree_gin extensions. Database is auto-added to Pgbouncer pool and registered as Grafana datasource.

Modify Database

Same command as create - playbook is idempotent when no baseline SQL is defined.

When target database exists, Pigsty modifies properties to match config. However, some properties can only be set at creation.

bin/pgsql-db <cls> <db>   # Modify database <db> properties

./pgsql-db.yml -l <cls> -e dbname=<db>   # Idempotent, can repeat

bin/pgsql-db pg-meta myapp    # Modify myapp database to match config

Immutable properties: These can’t be modified after creation, require state: recreate:

• name (database name), template, strategy (clone strategy)
• encoding, locale/lc_collate/lc_ctype, locale_provider/icu_locale/icu_rules/builtin_locale

All other properties can be modified. Common examples:

Modify owner: Update owner field, executes ALTER DATABASE ... OWNER TO and grants permissions.

- name: myapp
  owner: dbuser_new_owner     # New owner

Modify connection limit: Use connlimit to limit max connections.

- name: myapp
  connlimit: 100              # Max 100 connections

Revoke public connect: Setting revokeconn: true revokes PUBLIC CONNECT privilege, allowing only owner, DBA, monitor, and replication users.

- name: myapp
  owner: dbuser_myapp
  revokeconn: true            # Revoke PUBLIC CONNECT

Manage parameters: Use parameters dict for database-level params, generates ALTER DATABASE ... SET. Use special value DEFAULT to reset.

- name: myapp
  parameters:
    work_mem: '256MB'
    maintenance_work_mem: '512MB'
    statement_timeout: '30s'
    search_path: DEFAULT      # Reset to default

Manage schemas: Use schemas array with simple or extended format. Use state: absent to drop (CASCADE).

- name: myapp
  schemas:
    - app                                   # Simple form
    - { name: core, owner: dbuser_myapp }   # Specify owner
    - { name: deprecated, state: absent }   # Drop schema

Manage extensions: Use extensions array with simple or extended format. Use state: absent to uninstall (CASCADE).

- name: myapp
  extensions:
    - postgis                                 # Simple form
    - { name: vector, schema: public }        # Specify schema
    - { name: pg_trgm, state: absent }        # Uninstall extension

Connection pool config: By default all databases are added to Pgbouncer. Configure pgbouncer, pool_mode, pool_size, pool_reserve, pool_size_min, pool_connlimit, and pool_auth_user.

- name: myapp
  pgbouncer: true              # Add to pool (default true)
  pool_mode: transaction       # Pool mode: transaction/session/statement
  pool_size: 50                # Default pool size
  pool_reserve: 30             # Reserve pool size
  pool_size_min: 0             # Minimum pool size
  pool_connlimit: 100          # Max database connections
  pool_auth_user: dbuser_meta  # Auth query user (with pgbouncer_auth_query)

Since Pigsty v4.1.0, database pool fields are unified as pool_reserve and pool_connlimit; legacy aliases pool_size_reserve / pool_max_db_conn are converged.

Delete Database

To delete a database, set state to absent and execute:

bin/pgsql-db <cls> <db>   # Delete <db> (config must have state: absent)

./pgsql-db.yml -l <cls> -e dbname=<db>   # Use Ansible playbook

bin/pgsql-db pg-meta olddb    # Delete olddb (config has state: absent)

Config example:

pg_databases:
  - name: olddb
    state: absent

Deletion process: If is_template: true, first executes ALTER DATABASE ... IS_TEMPLATE false; uses DROP DATABASE ... WITH (FORCE) (PG13+) to force drop and terminate all connections; removes from Pgbouncer pool; unregisters from Grafana datasource.

Protection: System databases postgres, template0, template1 cannot be deleted. Deletion only runs on primary - streaming replication syncs to replicas.

Rebuild Database

recreate state rebuilds database (drop then create):

bin/pgsql-db <cls> <db>   # Rebuild <db> (config must have state: recreate)

./pgsql-db.yml -l <cls> -e dbname=<db>   # Use Ansible playbook

bin/pgsql-db pg-meta testdb    # Rebuild testdb (config has state: recreate)

Config example:

pg_databases:
  - name: testdb
    state: recreate
    owner: dbuser_test
    baseline: test_init.sql    # Execute after rebuild

Use cases: Test environment reset, clear dev database, modify immutable properties (encoding, locale), restore to initial state.

Difference from manual DROP + CREATE: Single command; auto-preserves Pgbouncer and Grafana config; auto-loads baseline init script.

Clone Database

Clone PostgreSQL databases using PG template mechanism. During cloning, no active connections to template database are allowed.

bin/pgsql-db <cls> <db>   # Clone <db> (config must specify template)

./pgsql-db.yml -l <cls> -e dbname=<db>   # Use Ansible playbook

bin/pgsql-db pg-meta meta_dev    # Clone meta_dev (config has template: meta)

Config example:

pg_databases:
  - name: meta                   # Source database

  - name: meta_dev
    template: meta               # Use meta as template
    strategy: FILE_COPY          # PG15+ clone strategy, instant on PG18

Instant Clone (PG18+): If using PostgreSQL 18+, Pigsty defaults file_copy_method. With strategy: FILE_COPY, database clone completes in ~200ms without copying data files. E.g., cloning 30GB database: normal takes 18s, instant takes 200ms.

Manual clone: Ensure all connections to template are terminated:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'meta';
CREATE DATABASE meta_dev TEMPLATE meta STRATEGY FILE_COPY;

Limitations: Instant clone only available on supported filesystems (xfs, btrfs, zfs, apfs); don’t use postgres database as template; in high-concurrency environments, all template connections must be cleared within clone window (~200ms).

Connection Pool Management

Connection pool params in database definitions are applied to Pgbouncer when creating/modifying databases.

By default all databases are added to Pgbouncer pool (pgbouncer: true). Databases are added to /etc/pgbouncer/database.txt. Database-level pool params (pool_auth_user, pool_mode, pool_size, pool_reserve, pool_size_min, pool_connlimit) are configured via this file.

Use postgres OS user with pgb alias to access Pgbouncer admin database. For more pool management, see Pgbouncer Management.

4 - Patroni HA Management

Overview

Pigsty uses Patroni to manage PostgreSQL clusters. It handles config changes, status checks, switchover, restart, reinit replicas, and more.

To use Patroni for management, you need one of the following identities:

• From INFRA node as admin user, managing all clusters in the environment.
• From PGSQL node as pg_dbsu (default postgres), managing the current cluster only.

Patroni provides patronictl CLI for management. Pigsty provides a wrapper alias pg to simplify operations.

pg ()
{
    local patroni_conf="/infra/conf/patronictl.yml";
    if [ ! -r ${patroni_conf} ]; then
        patroni_conf="/etc/patroni/patroni.yml";
        if [ ! -r ${patroni_conf} ]; then
            echo "error: patronictl config not found";
            return 1;
        fi;
    fi;
    patronictl -c ${patroni_conf} "$@"
}

Available Commands

Edit Config

Use edit-config to interactively edit cluster Patroni and PostgreSQL config. This opens an editor to modify config stored in DCS, automatically applying changes to all members. You can change Patroni params (ttl, loop_wait, synchronous_mode, etc.) and PostgreSQL params in postgresql.parameters.

pg edit-config <cls>                  # Interactive edit cluster config
pg edit-config <cls> --force          # Skip confirmation and apply directly
pg edit-config <cls> -p <k>=<v>       # Modify PostgreSQL param (--pg shorthand)
pg edit-config <cls> -s <k>=<v>       # Modify Patroni param (--set shorthand)

Common config modification examples:

# Modify PostgreSQL param: slow query threshold (prompts for confirmation)
pg edit-config pg-test -p log_min_duration_statement=1000

# Modify PostgreSQL param, skip confirmation
pg edit-config pg-test -p log_min_duration_statement=1000 --force

# Modify multiple PostgreSQL params
pg edit-config pg-test -p work_mem=256MB -p maintenance_work_mem=1GB --force

# Modify Patroni params: increase failure detection window (increase RTO)
pg edit-config pg-test -s loop_wait=15 -s ttl=60 --force

# Modify Patroni param: enable synchronous replication mode
pg edit-config pg-test -s synchronous_mode=true --force

# Modify Patroni param: enable strict synchronous mode (require at least one sync replica for writes)
pg edit-config pg-test -s synchronous_mode_strict=true --force

# Modify restart-required params (need pg restart after)
pg edit-config pg-test -p shared_buffers=4GB --force
pg edit-config pg-test -p shared_preload_libraries='timescaledb, pg_stat_statements' --force
pg edit-config pg-test -p max_connections=200 --force

Some params require PostgreSQL restart to take effect. Use pg list to check - instances marked with * need restart. Then use pg restart to apply. You can also use curl or programs to call Patroni REST API:

# View current config
curl -s 10.10.10.11:8008/config | jq .

# Modify params via API (requires auth)
curl -u 'postgres:Patroni.API' \
     -d '{"postgresql":{"parameters": {"log_min_duration_statement":200}}}' \
     -s -X PATCH http://10.10.10.11:8008/config | jq .

List Status

Use list to view cluster members and status. Output shows each instance’s name, host, role, state, timeline, and replication lag. This is the most commonly used command for checking cluster health.

pg list <cls>                         # List specified cluster status
pg list                               # List all clusters (on admin node)
pg list <cls> -e                      # Show extended info (--extended)
pg list <cls> -t                      # Show timestamp (--timestamp)
pg list <cls> -f json                 # Output as JSON (--format)
pg list <cls> -W 5                    # Refresh every 5 seconds (--watch)

Example output:

+ Cluster: pg-test (7322261897169354773) -----+----+--------------+
| Member    | Host        | Role    | State   | TL | Lag in MB    |
+-----------+-------------+---------+---------+----+--------------+
| pg-test-1 | 10.10.10.11 | Leader  | running |  1 |              |
| pg-test-2 | 10.10.10.12 | Replica | running |  1 |            0 |
| pg-test-3 | 10.10.10.13 | Replica | running |  1 |            0 |
+-----------+-------------+---------+---------+----+--------------+

Column descriptions: Member is instance name, composed of pg_cluster-pg_seq; Host is instance IP; Role is role type - Leader (primary), Replica, Sync Standby, Standby Leader (cascade primary); State is running state - running, streaming, in archive recovery, starting, stopped, etc.; TL is timeline number, incremented after each switchover; Lag in MB is replication lag in MB (not shown for primary).

Instances requiring restart show * after the name:

+ Cluster: pg-test (7322261897169354773) -------+----+--------------+
| Member      | Host        | Role    | State   | TL | Lag in MB    |
+-------------+-------------+---------+---------+----+--------------+
| pg-test-1 * | 10.10.10.11 | Leader  | running |  1 |              |
| pg-test-2 * | 10.10.10.12 | Replica | running |  1 |            0 |
+-------------+-------------+---------+---------+----+--------------+

Switchover

Use switchover for planned primary-replica switchover. Switchover is graceful: Patroni ensures replica is fully synced, demotes primary, then promotes target replica. Takes seconds with brief write unavailability. Use for primary host maintenance, upgrades, or migrating primary to better nodes.

pg switchover <cls>                   # Interactive switchover, prompts for target replica
pg switchover <cls> --leader <old>    # Specify current primary name
pg switchover <cls> --candidate <new> # Specify target replica name
pg switchover <cls> --scheduled <time> # Scheduled switchover, format: 2024-12-01T03:00
pg switchover <cls> --force           # Skip confirmation

Before switchover, ensure all replicas are healthy (running or streaming), replication lag is acceptable, and stakeholders are notified.

# Interactive switchover (recommended, shows topology and prompts for selection)
$ pg switchover pg-test
Current cluster topology
+ Cluster: pg-test (7322261897169354773) -----+----+--------------+
| Member    | Host        | Role    | State   | TL | Lag in MB    |
+-----------+-------------+---------+---------+----+--------------+
| pg-test-1 | 10.10.10.11 | Leader  | running |  1 |              |
| pg-test-2 | 10.10.10.12 | Replica | running |  1 |            0 |
| pg-test-3 | 10.10.10.13 | Replica | running |  1 |            0 |
+-----------+-------------+---------+---------+----+--------------+
Primary [pg-test-1]:
Candidate ['pg-test-2', 'pg-test-3'] []: pg-test-2
When should the switchover take place (e.g. 2024-01-01T12:00) [now]:
Are you sure you want to switchover cluster pg-test, demoting current leader pg-test-1? [y/N]: y

# Non-interactive switchover (specify primary and candidate)
pg switchover pg-test --leader pg-test-1 --candidate pg-test-2 --force

# Scheduled switchover (at 3 AM, for maintenance window)
pg switchover pg-test --leader pg-test-1 --candidate pg-test-2 --scheduled "2024-12-01T03:00"

After switchover, use pg list to confirm new cluster topology.

Failover

Use failover for emergency failover. Unlike switchover, failover is for when primary is unavailable. It directly promotes a replica without waiting for original primary confirmation. Since replicas may not be fully synced, failover may cause minor data loss. Use switchover for non-emergency situations.

pg failover <cls>                     # Interactive failover
pg failover <cls> --candidate <new>   # Specify replica to promote
pg failover <cls> --force             # Skip confirmation

Failover examples:

# Interactive failover
$ pg failover pg-test
Candidate ['pg-test-2', 'pg-test-3'] []: pg-test-2
Are you sure you want to failover cluster pg-test? [y/N]: y
Successfully failed over to "pg-test-2"

# Non-interactive failover (for emergencies)
pg failover pg-test --candidate pg-test-2 --force

Switchover vs Failover: Switchover is for planned maintenance, requires original primary online, ensures full sync before switching, no data loss; Failover is for emergency recovery, original primary can be offline, directly promotes replica, may lose unsynced data. Use Switchover for daily maintenance/upgrades; use Failover only when primary is completely down and unrecoverable.

The built-in Patroni failover subcommand currently has no --leader option. Use planned switchover --leader ... when you need to validate or name the old primary; failover accepts only the candidate replica.

Restart

Use restart to restart PostgreSQL instances, typically to apply restart-required parameter changes. When run against the whole cluster, patronictl submits each selected member in turn but does not guarantee a replica-first, leader-last order. If that order matters, restart replicas by role and then restart the leader separately.

pg restart <cls>                      # Restart all instances in cluster
pg restart <cls> <member>             # Restart specific instance
pg restart <cls> --role leader        # Restart primary only
pg restart <cls> --role replica       # Restart all replicas
pg restart <cls> --pending            # Restart only instances marked for restart
pg restart <cls> --scheduled <time>   # Scheduled restart
pg restart <cls> --timeout <sec>      # Set restart timeout (seconds)
pg restart <cls> --force              # Skip confirmation

After modifying restart-required params (shared_buffers, shared_preload_libraries, max_connections, max_worker_processes, etc.), use this command.

# Check which instances need restart (marked with *)
$ pg list pg-test
+ Cluster: pg-test (7322261897169354773) -------+----+--------------+
| Member      | Host        | Role    | State   | TL | Lag in MB    |
+-------------+-------------+---------+---------+----+--------------+
| pg-test-1 * | 10.10.10.11 | Leader  | running |  1 |              |
| pg-test-2 * | 10.10.10.12 | Replica | running |  1 |            0 |
+-------------+-------------+---------+---------+----+--------------+

# Restart single replica
pg restart pg-test pg-test-2

# Restart all cluster members (leader-last order is not guaranteed)
pg restart pg-test --force

# Restart only pending instances
pg restart pg-test --pending --force

# Explicitly restart replicas first, then the leader
pg restart pg-test --role replica --force
pg restart pg-test --role leader --force

# Scheduled restart (for maintenance window)
pg restart pg-test --scheduled "2024-12-01T03:00"

# Set restart timeout to 300 seconds
pg restart pg-test --timeout 300 --force

Reload

Use reload to reload Patroni config without restarting PostgreSQL. This re-reads config files and applies non-restart params via pg_reload_conf(). Lighter than restart - doesn’t interrupt connections or running queries.

pg reload <cls>                       # Reload entire cluster config
pg reload <cls> <member>              # Reload specific instance config
pg reload <cls> --role leader         # Reload primary only
pg reload <cls> --role replica        # Reload all replicas
pg reload <cls> --force               # Skip confirmation

Most PostgreSQL params work via reload. Only postmaster-context params (shared_buffers, max_connections, shared_preload_libraries, archive_mode, etc.) require restart.

# Reload entire cluster
pg reload pg-test

# Reload single instance
pg reload pg-test pg-test-1

# Force reload, skip confirmation
pg reload pg-test --force

Reinit Replica

Use reinit to reinitialize a replica. This deletes all data on the replica and rebuilds it according to Patroni’s create_replica_methods order. Pigsty tries basebackup (pg_basebackup) first by default; when a remote pgBackRest repository is enabled, pgbackrest is also configured as a fallback. Use this when replica data is corrupted, the replica is too far behind and required WAL has been removed, or replica configuration must be reset.

pg reinit <cls> <member>              # Reinitialize specified replica
pg reinit <cls> <member> --force      # Skip confirmation
pg reinit <cls> <member> --wait       # Wait for rebuild to complete

# Reinitialize replica (prompts for confirmation)
$ pg reinit pg-test pg-test-2
Are you sure you want to reinitialize members pg-test-2? [y/N]: y
Success: reinitialize for member pg-test-2

# Force reinitialize, skip confirmation
pg reinit pg-test pg-test-2 --force

# Reinitialize and wait for completion
pg reinit pg-test pg-test-2 --force --wait

During rebuild, use pg list to check progress. Replica state shows creating replica:

+ Cluster: pg-test (7322261897169354773) --------------+----+------+
| Member    | Host        | Role    | State            | TL | Lag  |
+-----------+-------------+---------+------------------+----+------+
| pg-test-1 | 10.10.10.11 | Leader  | running          |  2 |      |
| pg-test-2 | 10.10.10.12 | Replica | creating replica |    |    ? |
+-----------+-------------+---------+------------------+----+------+

Pause

Use pause to pause Patroni automatic failover. When paused, Patroni won’t auto-promote replicas even if primary fails. Use for planned maintenance windows (prevent accidental triggers), debugging (prevent cluster state changes), or manual switchover timing control.

pg pause <cls>                        # Pause automatic failover
pg pause <cls> --wait                 # Pause and wait for all members to confirm

# Pause automatic failover
$ pg pause pg-test
Success: cluster management is paused

# Check cluster status (shows Maintenance mode: on)
$ pg list pg-test
+ Cluster: pg-test (7322261897169354773) -----+----+--------------+
| Member    | Host        | Role    | State   | TL | Lag in MB    |
+-----------+-------------+---------+---------+----+--------------+
| pg-test-1 | 10.10.10.11 | Leader  | running |  1 |              |
| pg-test-2 | 10.10.10.12 | Replica | running |  1 |            0 |
+-----------+-------------+---------+---------+----+--------------+
 Maintenance mode: on

Resume

Use resume to resume Patroni automatic failover. Execute immediately after maintenance to ensure cluster auto-recovers on primary failure.

pg resume <cls>                       # Resume automatic failover
pg resume <cls> --wait                # Resume and wait for all members to confirm

# Resume automatic failover
$ pg resume pg-test
Success: cluster management is resumed

# Confirm resumed (Maintenance mode prompt disappears)
$ pg list pg-test

History

Use history to view cluster failover history. Each switchover (auto or manual) creates a new timeline record.

pg history <cls>                      # Show failover history
pg history <cls> -f json              # Output as JSON
pg history <cls> -f yaml              # Output as YAML

$ pg history pg-test
+----+-----------+------------------------------+---------------------------+
| TL |       LSN | Reason                       | Timestamp                 |
+----+-----------+------------------------------+---------------------------+
|  1 | 0/5000060 | no recovery target specified | 2024-01-15T10:30:00+08:00 |
|  2 | 0/6000000 | switchover to pg-test-2      | 2024-01-20T14:00:00+08:00 |
|  3 | 0/7000028 | failover to pg-test-1        | 2024-01-25T09:15:00+08:00 |
+----+-----------+------------------------------+---------------------------+

Column descriptions: TL is timeline number, incremented after each switchover, distinguishes primary histories; LSN is Log Sequence Number at switchover, marks WAL position; Reason is switchover reason - switchover to xxx (manual), failover to xxx (failure), or no recovery target specified (init); Timestamp is when switchover occurred.

Show Config

Use show-config to view current cluster config stored in DCS. This is read-only; use edit-config to modify.

pg show-config <cls>                  # Show cluster config

$ pg show-config pg-test
loop_wait: 10
maximum_lag_on_failover: 1048576
postgresql:
  parameters:
    archive_command: pgbackrest --stanza=pg-test archive-push %p
    max_connections: 100
    shared_buffers: 256MB
    log_min_duration_statement: 1000
  use_pg_rewind: true
  use_slots: true
retry_timeout: 10
ttl: 30
synchronous_mode: false

Query

Use query to quickly execute SQL on cluster members. Convenient for debugging - for complex production queries, use psql or applications.

pg query <cls> -c "<sql>"             # Execute on primary
pg query <cls> -c "<sql>" -m <member> # Execute on specific instance (--member)
pg query <cls> -c "<sql>" -r leader   # Execute on primary (--role)
pg query <cls> -c "<sql>" -r replica  # Execute on all replicas
pg query <cls> -f <file>              # Execute SQL from file
pg query <cls> -c "<sql>" -U <user>   # Specify username (--username)
pg query <cls> -c "<sql>" -d <db>     # Specify database (--dbname)
pg query <cls> -c "<sql>" --format json  # Output as JSON

# Check primary connection count
pg query pg-test -c "SELECT count(*) FROM pg_stat_activity"

# Check PostgreSQL version
pg query pg-test -c "SELECT version()"

# Check replication status on all replicas
pg query pg-test -c "SELECT pg_is_in_recovery(), pg_last_wal_replay_lsn()" -r replica

# Execute on specific instance
pg query pg-test -c "SELECT pg_is_in_recovery()" -m pg-test-2

# Use specific user and database
pg query pg-test -c "SELECT current_user, current_database()" -U postgres -d postgres

# Output as JSON
pg query pg-test -c "SELECT * FROM pg_stat_replication" --format json

Topology

Use topology to view cluster replication topology as a tree. More intuitive than list for showing primary-replica relationships, especially for cascading replication.

pg topology <cls>                     # Show replication topology

$ pg topology pg-test
+ Cluster: pg-test (7322261897169354773) -------+----+--------------+
| Member      | Host        | Role    | State   | TL | Lag in MB    |
+-------------+-------------+---------+---------+----+--------------+
| pg-test-1   | 10.10.10.11 | Leader  | running |  1 |              |
| + pg-test-2 | 10.10.10.12 | Replica | running |  1 |            0 |
| + pg-test-3 | 10.10.10.13 | Replica | running |  1 |            0 |
+-------------+-------------+---------+---------+----+--------------+

In cascading replication, topology clearly shows replication hierarchy - e.g., pg-test-3 replicates from pg-test-2, which replicates from primary pg-test-1.

Version

Use version to view patronictl version.

pg version                            # Show patronictl version

$ pg version
patronictl version 4.1.0

Remove

Use remove to remove cluster or member metadata from DCS. This is dangerous - only removes DCS metadata, doesn’t stop PostgreSQL or delete data files. Misuse may cause cluster state inconsistency.

pg remove <cls>                       # Remove entire cluster metadata from DCS

Normally you don’t need this command. To properly remove clusters/instances, use Pigsty’s bin/pgsql-rm script or pgsql-rm.yml playbook. Only consider remove for: orphaned DCS metadata (node physically removed but metadata remains), or cluster destroyed via other means requiring metadata cleanup.

# Remove entire cluster metadata (requires multiple confirmations)
$ pg remove pg-test
Please confirm the cluster name to remove: pg-test
You are about to remove all information in DCS for pg-test, please type: "Yes I am aware": Yes I am aware

5 - Managing PostgreSQL HBA Rules

Quick Start

Pigsty uses declarative management: first define HBA rules in the inventory, then use bin/pgsql-hba <cls> to refresh.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_hba_rules:                            # <--- Define HBA rules here!
      - {user: dbuser_app, db: app, addr: intra, auth: pwd, title: 'app access'}
      - {user: dbuser_api, db: all, addr: world, auth: ssl, title: 'api ssl access'}

bin/pgsql-hba <cls>              # Refresh PostgreSQL and Pgbouncer HBA rules for cluster
bin/pgsql-hba <cls> <ip>...      # Refresh HBA rules for specific instances

./pgsql.yml -l <cls> -t pg_hba,pg_reload                 # Refresh PostgreSQL HBA only
./pgsql.yml -l <cls> -t pgbouncer_hba,pgbouncer_reload   # Refresh Pgbouncer HBA only
./pgsql.yml -l <cls> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload  # Refresh both

bin/pgsql-hba pg-meta                      # Refresh pg-meta cluster HBA rules
bin/pgsql-hba pg-meta 10.10.10.10          # Refresh specific instance only
bin/pgsql-hba pg-meta 10.10.10.11 10.10.10.12  # Refresh multiple instances

For rule syntax, see HBA Configuration. For authentication methods, default boundaries, and credential management, see Authentication.

Refresh HBA Rules

After modifying HBA rules in pigsty.yml, re-render config files and reload services.

bin/pgsql-hba <cls>              # Refresh entire cluster HBA (PostgreSQL + Pgbouncer)
bin/pgsql-hba <cls> <ip>...      # Refresh specific instances (multiple IPs space-separated)

./pgsql.yml -l <cls> -t pg_hba,pg_reload                 # Refresh PostgreSQL HBA only
./pgsql.yml -l <cls> -t pgbouncer_hba,pgbouncer_reload   # Refresh Pgbouncer HBA only
./pgsql.yml -l <cls> -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload  # Refresh both

bin/pgsql-hba pg-meta                      # Refresh pg-meta cluster
bin/pgsql-hba pg-meta 10.10.10.10          # Refresh 10.10.10.10 instance only

Result: Renders PostgreSQL and Pgbouncer HBA config files based on inventory definitions, then reloads services to apply.

Config file locations

Related Tags

Verify HBA Rules

After refreshing HBA rules, verify config is correctly applied.

View current HBA rules

-- View PostgreSQL HBA rules (recommended)
TABLE pg_hba_file_rules;

-- View matching rules for specific database
SELECT * FROM pg_hba_file_rules WHERE database @> ARRAY['mydb']::text[];

# View PostgreSQL HBA config file
cat /pg/data/pg_hba.conf

# View Pgbouncer HBA config file
cat /etc/pgbouncer/pgb_hba.conf

# View config file header (confirm if updated)
head -20 /pg/data/pg_hba.conf

# Test connection for specific user from specific address
psql -h <host> -p 5432 -U <user> -d <database> -c "SELECT 1"

# Test connection through Pgbouncer
psql -h <host> -p 6432 -U <user> -d <database> -c "SELECT 1"

Check HBA config syntax

# Reload config (validates syntax)
psql -c "SELECT pg_reload_conf()"

# If syntax errors, check logs
tail -f /pg/log/postgresql-*.log

Common Scenarios

Add New HBA Rule

Add rule to cluster config’s pg_hba_rules, then refresh:

pg-meta:
  vars:
    pg_hba_rules:
      - {user: new_user, db: new_db, addr: '192.168.1.0/24', auth: pwd, title: 'new app access'}

bin/pgsql-hba pg-meta

Emergency IP Block

When detecting malicious IP, add high-priority (order: 0) deny rule:

pg_hba_rules:
  - {user: all, db: all, addr: '10.1.1.100/32', auth: deny, order: 0, title: 'emergency block'}

bin/pgsql-hba pg-meta    # Refresh immediately

Role-Based Rules

Configure different HBA rules for primary and replica using role parameter:

pg_hba_rules:
  # Only primary allows write users
  - {user: writer, db: all, addr: intra, auth: pwd, role: primary, title: 'writer on primary'}
  # Replicas allow read-only users
  - {user: reader, db: all, addr: world, auth: ssl, role: replica, title: 'reader on replica'}

After refresh, rules auto-enable/disable based on instance’s pg_role.

Refresh HBA After Expansion

When cluster adds new instances, rules using addr: cluster need refresh to include new members:

./pgsql.yml -l 10.10.10.14       # Add new instance
bin/pgsql-hba pg-meta            # Refresh all instances' HBA (includes new member IPs)

Refresh HBA After Failover

After Patroni failover, instance pg_role may not match config. If HBA rules use role filtering, update config and refresh:

# Update role definitions in pigsty.yml then refresh
bin/pgsql-hba pg-meta

Troubleshooting

Connection Rejected

Symptom: FATAL: no pg_hba.conf entry for host "x.x.x.x", user "xxx", database "xxx"

Steps:

1. Check current HBA rules, confirm if matching rule exists:

psql -c "TABLE pg_hba_file_rules"

2. Confirm client IP, username, database matches any rule

3. Check rule order (HBA uses first-match-wins)

4. Add corresponding rule and refresh:

bin/pgsql-hba <cls>

Authentication Failed

Symptom: FATAL: password authentication failed for user "xxx"

Steps:

1. Confirm password is correct
2. Check password encryption method (pg_pwd_enc) compatibility with client
3. Check if user exists:

SELECT * FROM pg_roles WHERE rolname = 'xxx';

HBA Rules Not Applied

Steps:

1. Confirm refresh command was executed
2. Check if Ansible execution succeeded
3. Confirm PostgreSQL reloaded:

psql -c "SELECT pg_reload_conf()"

4. Check if config file was updated:

head -20 /pg/data/pg_hba.conf

Rule Order Issues

HBA uses first-match-wins. If rules not working as expected:

1. Check order values in rule definitions
2. Use psql -c "TABLE pg_hba_file_rules" to view actual order
3. Adjust order values (lower numbers = higher priority)

Pgbouncer HBA

Pgbouncer HBA management is similar to PostgreSQL, with some differences.

Config differences

Refresh Pgbouncer HBA

bin/pgsql-hba <cls>    # Refresh both PostgreSQL and Pgbouncer

./pgsql.yml -l <cls> -t pgbouncer_hba,pgbouncer_reload   # Refresh Pgbouncer HBA only

cat /etc/pgbouncer/pgb_hba.conf    # View Pgbouncer HBA rules

Best Practices

1. Always manage in config files: Don’t edit pg_hba.conf directly - all changes through pigsty.yml
2. Test environment first: HBA changes can cause connection issues - verify in test env first
3. Use order for priority: Blocklist rules use order: 0 to ensure priority matching
4. Refresh promptly: Refresh HBA after adding/removing instances or failover
5. Principle of least privilege: Only open necessary access - avoid addr: world + auth: trust
6. Monitor auth failures: Watch for auth failures in pg_stat_activity
7. Backup config: Backup pigsty.yml before important changes

Related Documentation

• HBA Configuration: HBA rule config syntax and parameter details
• User Management: User and role management operations
• Access Control: Role system and permission model
• Authentication: Authentication methods, default boundaries, and credential management
• Encrypted Communication: TLS and client certificate verification

6 - Pgbouncer Connection Pooling

Overview

Pigsty uses Pgbouncer as PostgreSQL connection pooling middleware, listening on port 6432 by default, proxying access to local PostgreSQL on port 5432.

This is an optional component. If you don’t have massive connections or need transaction pooling and query metrics, you can disable it, connect directly to the database, or keep it unused.

User & Database Management

Pgbouncer users and databases are auto-managed by Pigsty, applying database config and user config when creating databases and creating users.

Database Management: Databases defined in pg_databases are auto-added to Pgbouncer by default. Set pgbouncer: false to exclude specific databases.

pg_databases:
  - name: mydb                # Added to connection pool by default
    pool_auth_user: dbuser_meta # Optional, auth query user (with pgbouncer_auth_query)
    pool_mode: transaction    # Database-level pool mode
    pool_size: 50             # Default pool size
    pool_reserve: 30          # Reserve pool size
    pool_size_min: 0          # Minimum pool size
    pool_connlimit: 100       # Max database connections
  - name: internal
    pgbouncer: false          # Excluded from connection pool

User Management: Users defined in pg_users need explicit pgbouncer: true to be added to connection pool user list.

pg_users:
  - name: dbuser_app
    password: DBUser.App
    pgbouncer: true           # Add to connection pool user list
    pool_mode: transaction    # User-level pool mode
    pool_connlimit: 50        # User-level max connections

Since Pigsty v4.1.0, database pool fields are unified as pool_reserve and pool_connlimit; legacy aliases pool_size_reserve / pool_max_db_conn are converged.

Service Management

In Pigsty, PostgreSQL cluster Primary Service and Replica Service default to Pgbouncer port 6432. To bypass connection pool and access PostgreSQL directly, customize pg_services, or set pg_default_service_dest to postgres.

Config Management

Pgbouncer config files are in /etc/pgbouncer/, generated and managed by Pigsty:

Pigsty auto-manages database.txt and userlist.txt, updating them when creating databases or creating users.

You can manually edit config then RELOAD to apply:

# Edit config
$ vim /etc/pgbouncer/pgbouncer.ini

# Reload via systemctl
$ sudo systemctl reload pgbouncer

# Reload as pg_dbsu / postgres user
$ pgb -c "RELOAD;"

Pool Management

Pgbouncer runs as the same dbsu as PostgreSQL, default postgres OS user. Pigsty provides pgb alias for easy management:

alias pgb='psql -p6432 -dpgbouncer'

Use pgb on database nodes to connect to Pgbouncer admin console for management commands and monitoring queries.

$ pgb
pgbouncer=# SHOW POOLS;
pgbouncer=# SHOW CLIENTS;
pgbouncer=# SHOW SERVERS;

PAUSE

Use PAUSE to pause database connections. Pgbouncer waits for active txn/session to complete based on pool mode, then disconnects server connections. New client requests are blocked until RESUME.

PAUSE [db];           -- Pause specified database, or all if not specified

Typical use cases:

• Online backend database switch (e.g., update connection target after switchover)
• Maintenance operations requiring all connections disconnected
• Combined with SUSPEND for Pgbouncer online restart

$ pgb -c "PAUSE mydb;"        # Pause mydb database
$ pgb -c "PAUSE;"             # Pause all databases

After pause, SHOW DATABASES shows paused status:

pgbouncer=# SHOW DATABASES;
   name   |   host    | port | database | ... | paused | disabled
----------+-----------+------+----------+-----+--------+----------
 mydb     | /var/run  | 5432 | mydb     | ... |      1 |        0

RESUME

Use RESUME to restore databases paused by PAUSE, KILL, or SUSPEND, allowing new connections and resuming normal service.

RESUME [db];          -- Resume specified database, or all if not specified

$ pgb -c "RESUME mydb;"       # Resume mydb database
$ pgb -c "RESUME;"            # Resume all databases

DISABLE

Use DISABLE to disable a database, rejecting all new client connection requests. Existing connections are unaffected.

DISABLE db;           -- Disable specified database (database name required)

Typical use cases:

• Temporarily offline a database for maintenance
• Block new connections for safe database migration
• Gradually decommission a database being removed

$ pgb -c "DISABLE mydb;"      # Disable mydb, new connections rejected

ENABLE

Use ENABLE to enable a database previously disabled by DISABLE, accepting new client connections again.

ENABLE db;            -- Enable specified database (database name required)

$ pgb -c "ENABLE mydb;"       # Enable mydb, allow new connections

RECONNECT

Use RECONNECT to gracefully rebuild server connections. Pgbouncer closes connections when released back to pool, creating new ones when needed.

RECONNECT [db];       -- Rebuild server connections for database, or all if not specified

Typical use cases:

• Refresh connections after backend database IP change
• Reroute traffic after switchover
• Rebuild connections after DNS update

$ pgb -c "RECONNECT mydb;"    # Rebuild mydb server connections
$ pgb -c "RECONNECT;"         # Rebuild all server connections

After RECONNECT, use WAIT_CLOSE to wait for old connections to fully release.

KILL

Use KILL to immediately disconnect all client and server connections for a database. Unlike PAUSE, KILL doesn’t wait for transaction completion - forces immediate disconnect.

KILL [db];            -- Kill all connections for database, or all (except admin) if not specified

$ pgb -c "KILL mydb;"         # Force disconnect all mydb connections
$ pgb -c "KILL;"              # Force disconnect all database connections (except admin)

After KILL, new connections are blocked until RESUME.

KILL_CLIENT

Use KILL_CLIENT to terminate a specific client connection. Client ID can be obtained from SHOW CLIENTS output.

KILL_CLIENT id;       -- Terminate client connection with specified ID

# View client connections
$ pgb -c "SHOW CLIENTS;"

# Terminate specific client (assuming ptr column shows ID 0x1234567890)
$ pgb -c "KILL_CLIENT 0x1234567890;"

SUSPEND

Use SUSPEND to suspend Pgbouncer. Flushes all socket buffers and stops listening until RESUME.

SUSPEND;              -- Suspend Pgbouncer

SUSPEND is mainly for Pgbouncer online restart (zero-downtime upgrade):

# 1. Suspend current Pgbouncer
$ pgb -c "SUSPEND;"

# 2. Start new Pgbouncer process (with -R option to take over sockets)
$ pgbouncer -R /etc/pgbouncer/pgbouncer.ini

# 3. New process takes over, old process exits automatically

SHUTDOWN

Use SHUTDOWN to shut down Pgbouncer process. Multiple shutdown modes supported:

SHUTDOWN;                      -- Immediate shutdown
SHUTDOWN WAIT_FOR_SERVERS;     -- Wait for server connections to release
SHUTDOWN WAIT_FOR_CLIENTS;     -- Wait for clients to disconnect (zero-downtime rolling restart)

$ pgb -c "SHUTDOWN WAIT_FOR_CLIENTS;"   # Graceful shutdown, wait for clients

RELOAD

Use RELOAD to reload Pgbouncer config files. Dynamically updates most config params without process restart.

RELOAD;               -- Reload config files

$ pgb -c "RELOAD;"              # Reload via admin console
$ systemctl reload pgbouncer    # Reload via systemd
$ kill -SIGHUP $(cat /run/postgresql/pgbouncer.pid)  # Reload via signal

Pigsty provides playbook task to reload Pgbouncer config:

./pgsql.yml -l <cls> -t pgbouncer_reload    # Reload cluster Pgbouncer config

WAIT_CLOSE

Use WAIT_CLOSE to wait for server connections to finish closing. Typically used after RECONNECT or RELOAD to ensure old connections are fully released.

WAIT_CLOSE [db];      -- Wait for server connections to close, or all if not specified

# Complete connection rebuild flow
$ pgb -c "RECONNECT mydb;"
$ pgb -c "WAIT_CLOSE mydb;"    # Wait for old connections to release

Monitoring

Pgbouncer provides rich SHOW commands for monitoring pool status:

Common monitoring examples:

# View pool status
$ pgb -c "SHOW POOLS;"

# View client connections
$ pgb -c "SHOW CLIENTS;"

# View server connections
$ pgb -c "SHOW SERVERS;"

# View statistics
$ pgb -c "SHOW STATS;"

# View database status
$ pgb -c "SHOW DATABASES;"

For more monitoring command details, see Pgbouncer official docs.

Unix Signals

Pgbouncer supports Unix signal control, useful when admin console is unavailable:

# Reload config via signal
$ kill -SIGHUP $(cat /run/postgresql/pgbouncer.pid)

# Graceful shutdown via signal
$ kill -SIGTERM $(cat /run/postgresql/pgbouncer.pid)

# Pause via signal
$ kill -SIGUSR1 $(cat /run/postgresql/pgbouncer.pid)

# Resume via signal
$ kill -SIGUSR2 $(cat /run/postgresql/pgbouncer.pid)

Traffic Switching

Pigsty-managed database routes live in /etc/pgbouncer/database.txt. To move one database’s Pgbouncer traffic to another node, edit that file, reload the configuration, then drain and rebuild existing server connections:

# 1. Change only mydb's backend target to 10.10.10.12
$ sed -i -E '/^mydb[[:space:]]*=/ s#host=[^[:space:]]+#host=10.10.10.12#' /etc/pgbouncer/database.txt

# 2. Reload config
$ pgb -c "RELOAD;"

# 3. Rebuild this database's connections and wait for old connections to close
$ pgb -c "RECONNECT mydb;"
$ pgb -c "WAIT_CLOSE mydb;"

The pgb-route function currently shipped in the source only edits /etc/pgbouncer/pgbouncer.ini. That file merely includes database.txt and does not contain the generated per-database host= routes, so the function does not change managed database backends. Do not use it in place of the procedure above.

7 - Managing PostgreSQL Component Services

Overview

Pigsty’s PGSQL module consists of multiple components, each running as a systemd service on nodes. (pgbackrest is an exception)

Understanding these components and their management is essential for maintaining production PostgreSQL clusters.

Quick Reference

Common service names: patroni, pgbouncer, haproxy, pg_exporter, pgbouncer_exporter, vip-manager

Patroni

Patroni is PostgreSQL’s HA manager, handling startup, shutdown, failure detection, and automatic failover. It’s the core PGSQL module component. PostgreSQL process is managed by Patroni - don’t use systemctl to manage postgres service directly.

Start Patroni

systemctl start patroni     # Start Patroni (also starts PostgreSQL)

After starting, Patroni auto-launches PostgreSQL. On first start, behavior depends on role:

• Primary: Initialize or recover data directory
• Replica: Clone data from primary and establish replication

Stop Patroni

systemctl stop patroni      # Stop Patroni (also stops PostgreSQL)

Stopping Patroni gracefully shuts down PostgreSQL. Note: If this is primary and auto-failover isn’t paused, may trigger failover.

Restart Patroni

systemctl restart patroni   # Restart Patroni (also restarts PostgreSQL)

Restart causes brief service interruption. For production, use pg restart for rolling restart.

Reload Patroni

systemctl reload patroni    # Reload Patroni config

Reload re-reads config file and applies hot-reloadable params to PostgreSQL.

View Status & Logs

systemctl status patroni    # View Patroni service status
journalctl -u patroni -f    # Real-time Patroni logs
journalctl -u patroni -n 100 --no-pager  # Last 100 lines

Config file: /etc/patroni/patroni.yml

Best Practice: Use patronictl instead of systemctl to manage PostgreSQL clusters.

Pgbouncer

Pgbouncer is a lightweight PostgreSQL connection pooling middleware. Business traffic typically goes through Pgbouncer (6432) rather than directly to PostgreSQL (5432) for connection reuse and database protection.

Start Pgbouncer

systemctl start pgbouncer

Stop Pgbouncer

systemctl stop pgbouncer

Note: Stopping Pgbouncer disconnects all pooled business connections.

Restart Pgbouncer

systemctl restart pgbouncer

Restart disconnects all existing connections. For config changes only, use reload.

Reload Pgbouncer

systemctl reload pgbouncer

Reload re-reads config files (user list, pool params, etc.) without disconnecting existing connections.

View Status & Logs

systemctl status pgbouncer
journalctl -u pgbouncer -f

Config files:

• Main config: /etc/pgbouncer/pgbouncer.ini
• HBA rules: /etc/pgbouncer/pgb_hba.conf
• User list: /etc/pgbouncer/userlist.txt
• Database list: /etc/pgbouncer/database.txt

Admin Console

psql -p 6432 -U postgres -d pgbouncer  # Connect to Pgbouncer admin console

Common admin commands:

SHOW POOLS;      -- View pool status
SHOW CLIENTS;    -- View client connections
SHOW SERVERS;    -- View backend server connections
SHOW STATS;      -- View statistics
RELOAD;          -- Reload config
PAUSE;           -- Pause all pools
RESUME;          -- Resume all pools

HAProxy

HAProxy is a high-performance load balancer that routes traffic to correct PostgreSQL instances. Pigsty uses HAProxy to expose services, routing traffic based on role (primary/replica) and health status.

Start HAProxy

systemctl start haproxy

Stop HAProxy

systemctl stop haproxy

Note: Stopping HAProxy disconnects all load-balanced connections.

Restart HAProxy

systemctl restart haproxy

Reload HAProxy

systemctl reload haproxy

HAProxy supports graceful reload without disconnecting existing connections. Use reload for config changes.

View Status & Logs

systemctl status haproxy
journalctl -u haproxy -f

Config files: the main configuration is /etc/haproxy/haproxy.cfg; Pigsty-generated service fragments are stored under /etc/haproxy/conf.d/.

Admin Interface

HAProxy provides a web admin interface, default port 9101:

http://<node_ip>:9101/haproxy

Default auth: username admin, password configured by haproxy_admin_password.

pg_exporter

pg_exporter is PostgreSQL’s Prometheus metrics exporter for collecting database performance metrics.

Start pg_exporter

systemctl start pg_exporter

Stop pg_exporter

systemctl stop pg_exporter

After stopping, Prometheus can’t collect PostgreSQL metrics from this instance.

Restart pg_exporter

systemctl restart pg_exporter

View Status & Logs

systemctl status pg_exporter
journalctl -u pg_exporter -f

Config file: /etc/pg_exporter.yml

Verify Metrics

curl -s localhost:9630/metrics | head -20

pgbouncer_exporter

pgbouncer_exporter is Pgbouncer’s Prometheus metrics exporter.

Start/Stop/Restart

systemctl start pgbouncer_exporter
systemctl stop pgbouncer_exporter
systemctl restart pgbouncer_exporter

View Status & Logs

systemctl status pgbouncer_exporter
journalctl -u pgbouncer_exporter -f

Verify Metrics

curl -s localhost:9631/metrics | head -20

vip-manager

vip-manager is an optional component for managing L2 VIP address floating. When pg_vip_enabled is enabled, vip-manager binds VIP to current primary node.

Start vip-manager

systemctl start vip-manager

Stop vip-manager

systemctl stop vip-manager

After stopping, VIP address is released from current node.

Restart vip-manager

systemctl restart vip-manager

View Status & Logs

systemctl status vip-manager
journalctl -u vip-manager -f

Config file: /etc/default/vip-manager

Verify VIP Binding

ip addr show           # Check network interfaces, verify VIP binding
pg list <cls>          # Confirm primary location

Startup Order & Dependencies

Recommended PGSQL module component startup order:

1. patroni          # Start Patroni first (auto-starts PostgreSQL)
2. pgbouncer        # Then start connection pool
3. haproxy          # Start load balancer
4. pg_exporter      # Start metrics exporters
5. pgbouncer_exporter
6. vip-manager      # Finally start VIP manager (if enabled)

Stop order should be reversed. Pigsty playbooks handle these dependencies automatically.

Batch Start All Services

systemctl start patroni pgbouncer haproxy pg_exporter pgbouncer_exporter

Batch Stop All Services

systemctl stop pgbouncer_exporter pg_exporter haproxy pgbouncer patroni

Common Troubleshooting

Service Startup Failure

systemctl status <service>        # View service status
journalctl -u <service> -n 50     # View recent logs
journalctl -u <service> --since "5 min ago"  # Last 5 minutes logs

Patroni Won’t Start

Pgbouncer Won’t Start