Skip to content

Patroni HA Management

Manage PostgreSQL cluster HA with Patroni, including config changes, status check, switchover, restart, and reinit replica.
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:

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

Using patronictl via pg alias
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

CommandFunctionDescription
edit-configEdit ConfigInteractively edit cluster Patroni/PostgreSQL config
listList StatusList cluster members and their status
switchoverSwitchoverSwitch primary role to specified replica (planned)
failoverFailoverForce failover to specified replica (emergency)
restartRestartRestart PostgreSQL instance to apply restart-required params
reloadReloadReload Patroni config (no restart needed)
reinitReinit ReplicaReinitialize replica (wipe data and re-clone)
pausePause Auto-FailoverPause Patroni automatic failover
resumeResume Auto-FailoverResume Patroni automatic failover
historyView HistoryShow cluster failover history
show-configShow ConfigDisplay current cluster config (read-only)
queryExecute QueryExecute SQL query on cluster members
topologyView TopologyDisplay cluster replication topology
versionView VersionDisplay Patroni version info
removeRemove MemberRemove cluster member from DCS (dangerous)

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