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.