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

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

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

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

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

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

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.