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 PostgreSQL cluster, first define the cluster in the inventory. Then add nodes and initialize them:

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

Create the cluster on the managed nodes. Run the pgsql.yml playbook on the <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

demo/pgsql.cast — /demo/pgsql.cast

Expand Cluster

To add a replica to an existing PostgreSQL cluster, add its instance definition to the inventory. Use 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 }

Scale out with a procedure similar to cluster creation. First add the new node to Pigsty:

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 install the PGSQL module on the new node. Use the same pg_cluster value as the existing 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 you scale out, reload the service to add the new member to the load balancer.

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

demo/pgsql-append.cast — /demo/pgsql-append.cast

Shrink Cluster

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

By default, scaling in stops the instance and deletes its data directory. 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>. Run the removal only after confirmation.

First uninstall the PGSQL module from the target node. Run 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 you remove the PGSQL module, you can remove the node from Pigsty:

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 you scale in, remove the instance from the inventory. Then reload the service to remove the instance from the 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

demo/pgsql-shrink.cast — /demo/pgsql-shrink.cast

Remove Cluster

To destroy a cluster, uninstall the PGSQL module from all its nodes. Run pgsql-rm.yml on <cls>:

This operation permanently deletes data. Inspect pig pg list <cls> and pig pb info. Verify a recent backup and any independent copy that you must keep. Have the operator enter the exact cluster name. The following commands destroy that cluster 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 you destroy PGSQL, you can remove all its nodes from Pigsty. You can keep nodes that run other services:

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

demo/pgsql-rm.cast — /demo/pgsql-rm.cast

If pg_safeguard is true for the cluster or globally, pgsql-rm.yml stops to prevent accidental removal. To force removal, override this setting on the playbook command line. By default, removal also deletes the cluster backup repository. To keep backups, such as a centralized repository, 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 through HAProxy on host nodes. Reload services when service definitions, instance weights, or cluster membership change, including scale-out and scale-in. The reload updates the static HAProxy member configuration. The default Primary and Replica services detect current roles through Patroni REST API health checks. Thus, ordinary switchover or failover redirects traffic automatically. These role changes do not require a new HAProxy configuration.

To reload service configuration, run the pg_service subtask of pgsql.yml. Target <cls> for the whole cluster or <ip> for specific instances:

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

If you use a dedicated load balancer cluster, set pg_service_provider. In this configuration, only a reload on the cluster primary updates the load balancer configuration.

Example: Reload pg-test cluster service config

demo/pgsql-svc.cast — /demo/pgsql-svc.cast

Reload HBA

When HBA configuration changes, reload the pg_hba_rules and pgb_hba_rules rules. Some HBA rules depend on inventory roles or address ranges that refer to cluster member aliases. For these rules, reload HBA after you change pg_role labels or scale the cluster. Role selectors use static inventory variables. They do not change automatically after a Patroni switchover.

To reload PostgreSQL and Pgbouncer HBA rules, run the HBA subtasks of pgsql.yml. Target <cls> for the whole cluster or <ip> for specific instances:

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

demo/pgsql-hba.cast — /demo/pgsql-hba.cast

Config Cluster

Patroni manages PostgreSQL configuration parameters. The Patroni configuration template supplies the initial values. After cluster initialization, Patroni dynamically manages and synchronizes the configuration stored in Etcd. Use patronictl to change most Patroni configuration parameters. Use subtasks for other parameters, such as etcd DCS, logging, and REST API configuration. For example, refresh Patroni configuration after etcd cluster membership changes:

./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

You can clone a cluster with a Standby Cluster or Point-in-Time Recovery. A standby cluster is simple and does not require a backup repository. It requires a reachable replication upstream and can copy only the latest state. PITR requires a centralized backup repository, such as Silo. It can clone the cluster to any point within the retention period.

Clone via Standby Cluster

A standby cluster continuously synchronizes from the upstream cluster through streaming replication. This is the simplest cloning method. Set pg_upstream on the new cluster primary to pull data automatically from the 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

The standby cluster follows the upstream and keeps its data synchronized. You can promote it to an independent cluster at any time:

$ 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) can recover to any point within the backup retention period. It requires a centralized backup repository, such as Silo or S3. It gives more recovery options than a standby cluster.

To clone with PITR, add pg_pitr and specify the 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 manages users declaratively. First define users in the inventory. Then run bin/pgsql-user <cls> <username> to create or modify them.

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.

pgsql-user.yml uses name to find a user definition. The playbook does not rename roles. To rename a role, create a replacement role. Migrate ownership, memberships, and client credentials, then validate the change before you remove the old role. Deletion followed by creation is not a lossless rename.

demo/pgsql-user.cast — /demo/pgsql-user.cast

Create User

During PostgreSQL cluster creation, the pg_user task automatically creates users defined in pg_users.

To create a user on an existing cluster, add its definition to all.children.<cls>.pg_users. Then run:

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: Pigsty creates dbuser_app on the primary, sets its password, and grants it the dbrole_readwrite role. It adds the user to the Pgbouncer pool and reloads Pgbouncer configuration on all instances.

Modify User

Use the same command as for creation. The playbook is idempotent. If the target user exists, Pigsty changes its properties to match the configuration.

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. Create a replacement role, then migrate ownership, privileges, and clients. Validate the change before you remove the old role.

All other properties can be modified. Common examples:

Modify password: Change the password field. Pigsty temporarily disables logging during the change to prevent password disclosure.

- 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 expiration after N days, or expire_at for a fixed date. expire_in takes priority. Each playbook run recalculates its expiration date. Use it for temporary users that need 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 the roles array with simple or extended definitions. Membership changes are additive: Pigsty keeps existing memberships that the array does not declare. To revoke a membership explicitly, use state: absent.

- 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 the parameters dictionary for user-level parameters. Pigsty generates ALTER USER ... SET commands. Use DEFAULT to reset a parameter.

- 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 the user to the pool. You can also set 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

User deletion terminates sessions, transfers object ownership, revokes grants, and runs DROP ROLE. This operation is irreversible. Confirm the exact cluster, role, successor owner, and a recent backup. Then set the user to state: absent and apply 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 and terminates active sessions. It transfers database and tablespace ownership, and objects in each connectable database, to postgres. It runs DROP OWNED to remove grants, revokes role memberships, then runs DROP ROLE. Before changes, it writes an audit snapshot to /tmp/pg_drop_role_<user>_<timestamp>.log.

Protection: The Ansible task skips postgres and the replication, administration, and monitoring usernames configured in the inventory. Direct calls to pg-drop-role protect only the fixed default names: postgres, replicator, dbuser_dba, and dbuser_monitor. The script does not recognize renamed system accounts automatically.

In v4.5, pgsql-user.yml reloads Pgbouncer. However, it does not reliably remove 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 the exact Pgbouncer entry remains, remove only that line through the change control procedure. Reload Pgbouncer and validate application connections. Do not delete entries with a broad pattern.

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.

Pigsty adds users with pgbouncer: true to /etc/pgbouncer/userlist.txt. It sets user-level pool_mode and pool_connlimit in /etc/pgbouncer/useropts.txt.

As the postgres operating system user, use the pgb alias to access the Pgbouncer administration database. For more operations, see Pgbouncer Management.

Manage Default-User Passwords

For a business user, follow Modify User. Save the new password in its pg_users definition, preview the playbook for that user, then apply it. The three default users need extra coordination because other services use their credentials.

These accounts belong to pg_default_roles, not pg_users. pgsql-user.yml searches only pg_users. Do not change a default password by overriding pg_users on the command line. This changes the business-user list for that run and exposes plaintext passwords in shell history.

Rotate one account at a time:

1. Save the new parameter in pigsty.yml or the active inventory. Never put the plaintext password on the command line.
2. On the current primary, open interactive psql as a superuser. Run \password <username> to enter the secret interactively.
3. Verify the -l cluster or node scope, then run the corresponding refresh playbooks below.
4. Keep the current administration session open. Verify direct PostgreSQL, Pgbouncer, replication, exporters, and Grafana data sources before you change 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

If replication credentials differ between the role and Patroni nodes, new replication connections fail. Change this credential during a maintenance window and validate it promptly. VIBE or another module may have generated an administration connection string in its workspace context. If so, generate that module’s files again.

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 you change it in the inventory, refresh the target PostgreSQL cluster and the Infra administration configuration 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 manages databases declaratively. First define databases in the inventory. Then run bin/pgsql-db <cls> <dbname> to create or modify them.

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.

You can set some parameters only at creation time. To change these parameters, recreate the database with state: recreate.

demo/pgsql-db.cast — /demo/pgsql-db.cast

Create Database

During PostgreSQL cluster creation, the pg_db task automatically creates databases defined in pg_databases.

To create a database on an existing cluster, add its definition to all.children.<cls>.pg_databases. Then run:

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: Pigsty creates myapp on the primary with dbuser_myapp as its owner. It creates the app schema and enables the pg_trgm and btree_gin extensions. It also adds the database to the Pgbouncer pool and registers a Grafana data source.

Modify Database

Use the same command as for creation. The playbook is idempotent when no baseline SQL is defined.

If the target database exists, Pigsty changes its properties to match the configuration. However, you can set some properties only 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: To change these properties after creation, use 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: Change the owner field. Pigsty runs 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: Set revokeconn: true to revoke the PUBLIC CONNECT privilege. Only the owner, DBA, monitor, and replication users can then connect.

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

Manage parameters: Use the parameters dictionary to set database-level parameters. Pigsty generates ALTER DATABASE ... SET commands. Use the special value DEFAULT to reset a parameter.

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

Manage schemas: Use the schemas array with simple or extended definitions. To drop a schema with CASCADE, set state: absent.

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

Manage extensions: Use the extensions array with simple or extended definitions. To uninstall an extension with CASCADE, set state: absent.

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

Connection pool config: By default, Pigsty adds all databases to Pgbouncer. Configure pooling with 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, use pool_reserve and pool_connlimit for database pool fields. These names replace the legacy aliases pool_size_reserve and pool_max_db_conn.

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, Pigsty first runs ALTER DATABASE ... IS_TEMPLATE false. It then uses DROP DATABASE ... WITH (FORCE) (PG13+) to delete the database and terminate all its connections. It removes the database from the Pgbouncer pool and unregisters its Grafana data source.

Protection: Pigsty cannot delete the system databases postgres, template0, and template1. Deletion runs only on the primary. Streaming replication sends the change 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: One command performs the operation. It preserves Pgbouncer and Grafana configuration automatically. It also loads the baseline initialization script automatically.

Clone Database

Use the PostgreSQL template mechanism to clone a database. The template database must have no active connections during cloning.

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+): For PostgreSQL 18+, Pigsty configures file_copy_method by default. With strategy: FILE_COPY, cloning takes about 200ms and does not copy data files. For example, a 30GB database takes 18s with normal cloning and 200ms with instant cloning.

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 cloning requires a supported filesystem: xfs, btrfs, zfs, or apfs. Do not use the postgres database as a template. In high-concurrency environments, clear all template connections within the cloning window of about 200ms.

Connection Pool Management

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

By default, Pigsty adds all databases to the Pgbouncer pool (pgbouncer: true). It adds their definitions to /etc/pgbouncer/database.txt. This file also holds database-level pool parameters: pool_auth_user, pool_mode, pool_size, pool_reserve, pool_size_min, and pool_connlimit.

As the postgres operating system user, use the pgb alias to access the Pgbouncer administration database. For more operations, see Pgbouncer Management.

4 - Patroni HA Management

Overview

Pigsty uses Patroni to manage PostgreSQL clusters. It handles configuration changes, status checks, switchovers, restarts, replica reinitialization, and other operations.

To manage clusters with Patroni, use one of these identities:

• On an INFRA node, use an administration user to manage all clusters in the environment.
• On a PGSQL node, use pg_dbsu, which defaults to postgres, to manage only the current cluster.

Patroni provides the patronictl administration CLI. Pigsty provides the pg wrapper alias to simplify its use.

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 edit cluster Patroni and PostgreSQL configuration interactively. The command opens an editor for configuration stored in DCS. It applies changes automatically to all members. You can change Patroni parameters, such as ttl, loop_wait, and synchronous_mode. You can also change PostgreSQL parameters 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)

demo/pgsql-config.cast — /demo/pgsql-config.cast

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 parameter changes require a PostgreSQL restart. Run pg list to find instances marked with *. These instances need a restart. Run pg restart to apply the changes. You can also call the Patroni REST API with curl or a program:

# 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 their status. The output shows each instance name, host, role, state, timeline, and replication lag. This is the most common command for cluster health checks.

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 |
+-----------+-------------+---------+---------+----+--------------+

The output columns have these meanings:

• Member: The instance name, formed from pg_cluster-pg_seq.
• Host: The instance IP address.
• Role: Leader (primary), Replica, Sync Standby, or Standby Leader (cascade primary).
• State: The running state, such as running, streaming, in archive recovery, starting, or stopped.
• TL: The timeline number, which increases after each switchover.
• Lag in MB: Replication lag in MB. This value is not shown for the 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 a planned primary-replica role change. Patroni first verifies that the replica is fully synchronized. It then demotes the primary and promotes the target replica. The operation takes seconds, with a short interruption to writes. Use it for primary host maintenance, upgrades, or migration of the primary to a better node.

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 a switchover, verify that all replicas are healthy (running or streaming). Verify that replication lag is acceptable, and notify stakeholders.

# 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 recovery when the primary is unavailable. It promotes a replica directly, without confirmation from the original primary. Replicas may not be fully synchronized, so failover may cause minor data loss. For operations that are not emergencies, use switchover.

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 supports planned maintenance and requires the original primary to be online. It waits for full synchronization and prevents data loss. Failover supports emergency recovery when the original primary can be offline. It promotes a replica directly and may lose data that has not replicated. Use Switchover for routine maintenance and upgrades. Use Failover only when the primary is completely down and cannot recover.

The built-in Patroni failover subcommand currently has no --leader option. Use switchover --leader ... to validate or name the old primary during a planned switchover. Failover accepts only the candidate replica.

Restart

Use restart to restart PostgreSQL instances, usually after changes to parameters that require a restart. For a whole cluster, patronictl submits each selected member in turn. It does not guarantee that replicas restart before the leader. If this order matters, restart replicas by role first. 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 configuration without a PostgreSQL restart. It reads configuration files again and applies parameters through pg_reload_conf(). It causes less disruption than restart: connections and running queries continue.

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 parameter changes apply through reload. Only parameters with postmaster context require a restart, such as shared_buffers, max_connections, shared_preload_libraries, and archive_mode.

# 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 replica data and rebuilds it in the order specified by Patroni create_replica_methods. By default, Pigsty tries basebackup (pg_basebackup) first. If a remote pgBackRest repository is enabled, Pigsty also configures pgbackrest as a fallback. Use reinitialization for corrupted replica data, missing WAL after excessive lag, or a required replica configuration 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

Warning: This operation deletes all data on the target instance. You can run it only on replicas, not on the primary.

# 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. While paused, Patroni does not promote replicas automatically, even if the primary fails. Pause during planned maintenance to prevent accidental failover. You can also pause for debugging to prevent cluster state changes, or to control manual switchover timing.

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

Warning: While paused, the cluster will not recover automatically if the primary fails. Run resume after maintenance.

# 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. Run it immediately after maintenance so the cluster can recover automatically from a 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 automatic or manual switchover creates a 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 |
+----+-----------+------------------------------+---------------------------+

The history columns have these meanings:

• TL: The timeline number. It increases after each switchover to distinguish primary histories.
• LSN: The Log Sequence Number at switchover, which identifies the WAL position.
• Reason: switchover to xxx for a manual operation, failover to xxx for a failure, or no recovery target specified for initialization.
• Timestamp: The time of the switchover.

Show Config

Use show-config to view the current cluster configuration in DCS. This command only reads configuration. To change it, use edit-config.

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 run SQL quickly on cluster members during debugging. For complex production queries, use psql or an application.

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. It shows primary-replica relationships more clearly than list, 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 |
+-------------+-------------+---------+---------+----+--------------+

For cascading replication, the topology shows the replication hierarchy. For example, pg-test-3 replicates from pg-test-2, which replicates from the 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 operation is dangerous. It removes only DCS metadata; it does not stop PostgreSQL or delete data files. Incorrect use can make cluster state inconsistent.

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

Normally, you do not need this command. To remove a cluster or instance, use bin/pgsql-rm or pgsql-rm.yml. Consider remove only for orphaned DCS metadata after physical node removal, or metadata left after cluster destruction by other means.

# 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 manages HBA rules declaratively. First define HBA rules in the inventory. Then run bin/pgsql-hba <cls> to refresh them.

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.

demo/pgsql-hba.cast — /demo/pgsql-hba.cast

Refresh HBA Rules

After you change HBA rules in pigsty.yml, generate the configuration files again and reload the 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: Pigsty generates PostgreSQL and Pgbouncer HBA configuration files from inventory definitions. It then reloads the services to apply the rules.

Config file locations

Related Tags

Verify HBA Rules

After you refresh HBA rules, verify that the configuration applies correctly.

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 the rule to pg_hba_rules in the cluster configuration. Then refresh the rules:

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

If you detect a malicious IP address, add a deny rule with high priority (order: 0):

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 a refresh, the instance pg_role determines which rules are enabled.

Refresh HBA After Expansion

After you add cluster instances, refresh rules that use addr: cluster. The refreshed rules include the 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 a Patroni failover, instance roles may differ from pg_role values in the configuration. If HBA rules filter by role, update the configuration and refresh the rules:

# 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 the first matching rule. If rules do not work 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. Manage rules in the inventory: Make all changes in pigsty.yml. Do not edit pg_hba.conf directly.
2. Test first: Verify HBA changes in a test environment first. Changes can cause connection problems.
3. Set rule priority: Use order: 0 for blocklist rules to match them first.
4. Refresh promptly: Refresh HBA after you add or remove instances, or after failover.
5. Grant minimum access: Allow only necessary access. Avoid addr: world with auth: trust.
6. Monitor authentication failures: Check pg_stat_activity for authentication failures.
7. Back up configuration: Back up 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 for PostgreSQL connection pooling. By default, it listens on port 6432 and proxies connections to local PostgreSQL on port 5432.

This component is optional. If you do not need many connections, transaction pooling, or query metrics, you can disable it. You can also connect directly to the database or leave Pgbouncer unused.

User & Database Management

Pigsty automatically manages Pgbouncer users and databases. When you create databases or users, it applies the database configuration and user configuration.

Database Management: By default, Pigsty adds databases from pg_databases to Pgbouncer. To exclude a database, set pgbouncer: false.

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: To add a user from pg_users to the pool user list, explicitly set pgbouncer: true.

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, use pool_reserve and pool_connlimit for database pool fields. These names replace the legacy aliases pool_size_reserve and pool_max_db_conn.

Service Management

By default, the PostgreSQL Primary Service and Replica Service connect to Pgbouncer on port 6432. To connect directly to PostgreSQL, customize pg_services. Alternatively, set pg_default_service_dest to postgres.

Config Management

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

Pigsty automatically manages database.txt and userlist.txt. It updates them when you create databases or users.

You can edit configuration manually, then run RELOAD to apply the changes:

# 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. The default is the postgres operating system user. Pigsty provides the pgb alias for administration:

alias pgb='psql -p6432 -dpgbouncer'

On database nodes, use pgb to connect to the Pgbouncer administration console. You can then run administration commands and monitoring queries.

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

PAUSE

Use PAUSE to pause database connections. Pgbouncer waits for the active transaction or session to finish, according to the pool mode. It then disconnects server connections. New client requests wait 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. This allows new connections and resumes 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 reject all new client connections for a database. Existing connections continue.

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 that DISABLE stopped. The database accepts 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 rebuild server connections gracefully. Pgbouncer closes connections when they return to the pool. It creates new connections as 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 disconnect all client and server connections for a database immediately. Unlike PAUSE, it does not wait for transactions to finish.

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. Get the client ID from SHOW CLIENTS.

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. It 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 stop the Pgbouncer process. The command supports several shutdown modes:

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 read the Pgbouncer configuration files again. It dynamically updates most parameters without a 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 until server connections finish closing. Usually, run it after RECONNECT or RELOAD to verify that old connections have closed.

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 these SHOW commands to monitor 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

Use Unix signals to control Pgbouncer when the administration 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 stores managed database routes in /etc/pgbouncer/database.txt. To move Pgbouncer traffic for one database to another node, edit this file and reload the configuration. Then drain and rebuild the 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 edits only /etc/pgbouncer/pgbouncer.ini. That file includes database.txt but does not contain the generated host= routes for each database. Thus, the function does not change managed database backends. Use the procedure above instead.

7 - Managing PostgreSQL Component Services

Overview

The Pigsty PGSQL module has multiple components. Each component runs as a systemd service on nodes, except pgBackRest.

To maintain production PostgreSQL clusters, learn how these components work and how to manage them.

Quick Reference

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

Patroni

Patroni manages PostgreSQL high availability. It controls startup, shutdown, failure detection, and automatic failover. It is the core component of the PGSQL module and manages the PostgreSQL process. Do not manage the postgres service directly with systemctl.

Start Patroni

systemctl start patroni     # Start Patroni (also starts PostgreSQL)

After startup, Patroni automatically starts PostgreSQL. On the first startup, the instance role determines the action:

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

Stop Patroni

systemctl stop patroni      # Stop Patroni (also stops PostgreSQL)

Stopping Patroni shuts down PostgreSQL gracefully. If the instance is the primary and automatic failover is active, this operation may trigger a failover.

Restart Patroni

systemctl restart patroni   # Restart Patroni (also restarts PostgreSQL)

A restart causes a short service interruption. For production clusters, use pg restart for a rolling restart.

Reload Patroni

systemctl reload patroni    # Reload Patroni config

A reload reads the configuration file again. It applies parameters that PostgreSQL can change without a restart.

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 provides lightweight PostgreSQL connection pooling. Business traffic usually goes through Pgbouncer on port 6432, rather than directly to PostgreSQL on port 5432. This reuses connections and protects the database.

Start Pgbouncer

systemctl start pgbouncer

Stop Pgbouncer

systemctl stop pgbouncer

Note: Stopping Pgbouncer disconnects all pooled business connections.

Restart Pgbouncer

systemctl restart pgbouncer

A restart disconnects all existing connections. To apply only configuration changes, use reload.

Reload Pgbouncer

systemctl reload pgbouncer

A reload reads configuration files, such as user lists and pool parameters, again. It does not disconnect 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. It routes traffic to the correct PostgreSQL instances. Pigsty uses HAProxy to expose services. HAProxy routes traffic by instance role (primary or 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 can reload without disconnecting existing connections. Use reload to apply configuration changes.

View Status & Logs

systemctl status haproxy
journalctl -u haproxy -f

Config files: The main configuration is /etc/haproxy/haproxy.cfg. Pigsty stores generated service fragments in /etc/haproxy/conf.d/.

Admin Interface

HAProxy provides a web admin interface, default port 9101:

http://<node_ip>:9101/haproxy

The default administration username is admin. Set its password with haproxy_admin_password.

pg_exporter

pg_exporter exports PostgreSQL performance metrics for Prometheus.

Start pg_exporter

systemctl start pg_exporter

Stop pg_exporter

systemctl stop pg_exporter

After you stop the exporter, Prometheus cannot 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 that moves an L2 VIP between nodes. When pg_vip_enabled is enabled, vip-manager binds the VIP to the current primary node.

Start vip-manager

systemctl start vip-manager

Stop vip-manager

systemctl stop vip-manager

After you stop vip-manager, it releases the VIP address from the 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 the components in reverse order. 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

HAProxy Won’t Start

Related Documentation

• Patroni Management: Manage PostgreSQL HA with patronictl
• Cluster Management: Create, scale, destroy clusters
• Service Configuration: HAProxy service definition and config
• Monitoring System: PostgreSQL monitoring and alerting

8 - Manage PostgreSQL Cron Jobs

Pigsty uses crontab to schedule routine backups, transaction freezing, and reorganization of bloated tables and indexes.

Quick Reference