Manage PostgreSQL cluster HA with Patroni, including config changes, status check, switchover, restart, and reinit replica.
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:
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.
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 configcurl -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 statuspg 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)
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.
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.
# 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 clusterpg restart <cls> <member> # Restart specific instancepg restart <cls> --role leader # Restart primary onlypg restart <cls> --role replica # Restart all replicaspg restart <cls> --pending # Restart only instances marked for restartpg restart <cls> --scheduled <time> # Scheduled restartpg 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 replicapg restart pg-test pg-test-2
# Restart all cluster members (leader-last order is not guaranteed)pg restart pg-test --force
# Restart only pending instancespg restart pg-test --pending --force
# Explicitly restart replicas first, then the leaderpg 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 secondspg 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.
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.
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.
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 confirmationpg reinit pg-test pg-test-2 --force
# Reinitialize and wait for completionpg reinit pg-test pg-test-2 --force --wait
During rebuild, use pg list to check progress. Replica state shows creating replica:
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 failoverpg 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 failoverpg resume <cls> --wait # Resume and wait for all members to confirm
# Check primary connection countpg query pg-test -c "SELECT count(*) FROM pg_stat_activity"# Check PostgreSQL versionpg query pg-test -c "SELECT version()"# Check replication status on all replicaspg query pg-test -c "SELECT pg_is_in_recovery(), pg_last_wal_replay_lsn()" -r replica
# Execute on specific instancepg query pg-test -c "SELECT pg_is_in_recovery()" -m pg-test-2
# Use specific user and databasepg query pg-test -c "SELECT current_user, current_database()" -U postgres -d postgres
# Output as JSONpg 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.
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.
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