Documentation

• 1: Platform Support
• 2: Quick Start
• 3: Namespace Isolation
• 4: Backup
• 5: Restore
• 6: Replica (read-only standby)
• 7: HA Cluster
  • 7.1: Patroni HA
  • 7.2: Patroni Dynamic Configuration
  • 7.3: Patroni REST API
  • 7.4: Extensions in HA Clusters
  • 7.5: Patroni Cluster Backup
  • 7.6: Patroni Cluster Restore
  • 7.7: HA Exec / psql
  • 7.8: Example: HA Cluster with a Self-CA MinIO
  • 7.9: Generating a Certificate — pg cert
  • 7.10: S3 Storage High Availability
• 8: Failover: Replica Promotion
• 9: Self-Cert
• 10: Auto-start on Boot
• 11: Extensions
  • 11.1: Extension Catalog
  • 11.2: Popular Extensions
• 12: Addons
  • 12.1: PgBouncer
  • 12.2: Etcd
  • 12.3: PgDog
  • 12.4: Garage
  • 12.5: PostgREST
  • 12.6: SeaweedFS
  • 12.7: RustFS
  • 12.8: HAProxy
  • 12.9: Silo
  • 12.10: MinIO
  • 12.11: Redis
  • 12.12: Predixy
  • 12.13: pgAdmin 4
  • 12.14: Nginx
  • 12.15: Grafana
  • 12.16: Prometheus
  • 12.17: Alertmanager
  • 12.18: Gitea
  • 12.19: VictoriaMetrics
  • 12.20: BeeGFS
  • 12.21: VMCluster
  • 12.22: JuiceFS
  • 12.23: VictoriaLogs
  • 12.24: vlagent
  • 12.25: VLCluster
  • 12.26: ZeroFS
  • 12.27: DuckDB
  • 12.28: Node_exporter
  • 12.29: InfluxDB
  • 12.30: Pg_exporter
  • 12.31: influx2tsdb
  • 12.32: ClickHouse
  • 12.33: Kafka
  • 12.34: RabbitMQ
  • 12.35: RocketMQ
• 13: Clone
• 14: Data Import/Export
• 15: Health Inspection
• 16: Configuration Tuning
• 17: KV Cache
• 18: Toolbox
  • 18.1: ZFS Storage
  • 18.2: PGMQ Queues
  • 18.3: SQLite + Litestream
  • 18.4: TimescaleDB
• 19: SQL Commands
• 20: Destroy
• 21: Logs
• 22: Administration

1 - Platform Support

pgcli drives Podman containers on both Linux and macOS. The difference is how containers get networking:

• Linux runs Podman natively, so containers share the host network stack (--network host) and reach each other over 127.0.0.1. This is the zero-overhead path and the only one that supports cross-host topologies.
• macOS runs Podman inside a podman machine VM. Host networking there binds to the VM’s loopback, which the Mac cannot see, so pgcli instead joins a shared bridge network (pgcli-net) and publishes each port — the Mac reaches it on 127.0.0.1:<port> via gvproxy, and containers reach each other by container name over the bridge. This works for a single host (dev/test); it is not the cross-host HA path.

The table below is the current support matrix. “macOS (single-host)” means fully usable for one machine; features that must advertise a routable address across machines, or that depend on Linux-only container internals, stay Linux only.

Support matrix

Legend: ✅ supported · ✅ note supported with the stated caveat · ⚠️ present but unverified on that platform · ❌ not supported.

Instance features

Everything built on the managed PostgreSQL instance works on both platforms — lifecycle commands, pg psql / pg exec, logs, namespaces, import/export, clone, extensions, backup/restore (pgBackRest), physical replicas, and failover. The macOS bridge path (published ports + container-name DNS) was proven by the instance and replica/backup paths first; the proxy addons reuse it.

On macOS these are single-host: cross-host replicas and pools that span machines are a Linux --network host + LAN-addresses feature.

Auto-start on boot

Both platforms are supported, via different services:

• Linux — a systemd user unit.
• macOS — a launchd LaunchAgent. LaunchAgents run at user login, not at system boot, because rootless podman cannot start before someone logs in. Expect containers to come up after you log in, not before. See Auto-start on Boot.

Addons

Addon networking differs per component:

• PgBouncer and PgDog — pure proxies, supported on macOS for single-host dev/test. On macOS they join pgcli-net and publish their ports. Client connections are still 127.0.0.1:<port>. See each page’s Platform support section for the backend-address caveat (a remote/backend host must be reachable from the Mac, not 127.0.0.1).
• etcd — Linux only. Members run on host networking and advertise their client/peer URLs into the raft membership list as permanent cluster state; retrofitting the macOS bridge + container-name model would rewrite that state. Kept Linux-only until that is designed in.
• Patroni (pg ha) — Linux only. Members rely on podman host networking; both root and rootless podman are supported. The podman machine VM’s uid mapping does not align with host networking. The commands fail fast on macOS with a clear message. See Patroni HA.
• HAProxy — Linux only. It fronts Patroni members over host networking, which the podman machine VM does not provide to containers; the manager fails fast on macOS.
• MinIO — both platforms. Object storage, standalone or as a distributed erasure-coded cluster across hosts. On Linux it serves over host networking; on macOS it joins pgcli-net with its API and console ports published, so the Mac reaches both on 127.0.0.1:<port> (the same path the proxy addons use). The public image is dual-arch (amd64 + arm64).
• silo — both platforms. Pigsty’s MinIO fork, so it shares MinIO’s networking story exactly: host networking on Linux, the pgcli-net bridge with published ports on macOS. The public image is dual-arch (amd64 + arm64).
• rustfs — Linux only, in practice. pgcli runs a custom wrapper image (ghcr.io/mars-base/pgcli/pgcli-rustfs) whose entrypoint chowns the bind-mounted data/cert directories to rustfs’s fixed uid 10001 from inside the container, before dropping to the unprivileged user. The wrapper image is built dual-arch, and the Go manager has the same macOS bridge path (published ports) as MinIO/silo, so macOS is expected to work — but this addon’s e2e coverage has only run on Linux, so treat it as Linux only until it is exercised on a Mac.
• Redis — Linux, tested; macOS expected. A plain upstream docker.io/library/redis image with no wrapper and no fixed container uid, so it needs none of rustfs’s ownership machinery. On Linux it serves over host networking (the port lands on every interface or just loopback, per listen); on macOS it joins pgcli-net with the port published and pg redis-cli dials host.containers.internal — the same bridge path the object stores use, code-complete but not yet exercised on a Mac, so the e2e coverage is Linux-only for now.
• Predixy — Linux only. Like HAProxy, it needs host networking to dial its backend cluster nodes (single- and cross-host, both verified). The wrapper image is amd64-only — upstream ships an amd64 binary — so an arm64 host needs --platform linux/amd64 emulation, and the macOS podman machine path does not exist.
• pgAdmin 4 — Linux, tested; macOS expected. A plain upstream docker.io/dpage/pgadmin4 image with no wrapper — the image’s own entrypoint chowns /var/lib/pgadmin to uid 5050 and drops privileges, so pgcli passes --user 0 and never chowns on the host (unlike rustfs). On Linux it serves over host networking on pgadmin_start_port (loopback by default); on macOS it joins pgcli-net with the port published and its listen widened to 0.0.0.0 — the same bridge path redis/the object stores use, code-complete but not yet exercised on a Mac, so the e2e coverage is Linux-only for now.
• Grafana — Linux, tested; macOS expected. A plain upstream docker.io/grafana/grafana image with no wrapper. The image bakes USER 472 and its entrypoint does no ownership work at all, so pgcli chowns the host data dir to 472:0 itself before creating the container — via podman unshare chown under rootless podman, or a direct recursive chown when pgcli runs as root on a rootful host. On Linux it serves over host networking on grafana_start_port (on every interface by default); on macOS it joins pgcli-net with the port published and its listen widened to 0.0.0.0 — the same bridge path pgAdmin/redis use, code-complete but not yet exercised on a Mac, so the e2e coverage is Linux-only for now.
• Prometheus — Linux, tested; macOS expected. A plain upstream docker.io/prom/prometheus image with no wrapper, the same ownership shape as Grafana (baked USER nobody, uid/gid 65534, no entrypoint fixup) — pgcli pre-chowns the host TSDB dir via podman unshare chown 65534:65534 under rootless podman, or a direct recursive chown when pgcli runs as root on a rootful host. Unlike Grafana it is configured by a bind-mounted prometheus.yml (rendered, or copied from --conf-file) plus CLI flags after the image name, not env vars. On Linux it serves over host networking on prometheus_start_port (on every interface by default) — with no authentication behind that default, so unlike Grafana’s password-guarded 0.0.0.0, this exposure is guarded only by the network boundary. On macOS it joins pgcli-net with the port published — the same bridge path Grafana uses, code-complete but not yet exercised on a Mac, so the e2e coverage is Linux-only for now.
• Alertmanager — Linux; macOS code-complete, untested. Prometheus’s downstream alert router and its near-exact twin: a plain upstream docker.io/prom/alertmanager image, the same ownership shape (baked USER nobody, uid/gid 65534, no entrypoint fixup — pgcli pre-chowns the host state dir), configured by a bind-mounted alertmanager.yml (rendered, or copied from --conf-file) plus CLI flags after the image name. Two Alertmanager-specific facts: --cluster.listen-address= is always passed empty so a single instance never opens the HA gossip port (TCP+UDP 9094), and unlike Prometheus the unauthenticated POST /-/reload route cannot be disabled in the image — pgcli reloads via SIGHUP so it never depends on that route, but the route stays open regardless. That sits on top of a sharper exposure than Prometheus’s: Listen defaults to 0.0.0.0 with no authentication, and reaching the port lets anyone create silences — silently muting every notification the on-call expects. On macOS it joins pgcli-net with the port published — the same bridge path, code-complete but not yet exercised on a Mac.
• Gitea — Linux; macOS code-complete, untested. The self-hosted Git service runs from the plain upstream docker.io/gitea/gitea image (rootful build — the rootless variant’s data layout is incompatible and pgcli never selects it). Ownership is Grafana’s shape with a different number: the daemon drops to the baked git user, uid/gid 1000 (pinned via USER_UID/USER_GID so a host pre-chown cannot drift), no --user flag; pgcli pre-chowns the host /data dir the same way. Config is env-only (GITEA__* folded into the persisted app.ini on every boot) — which is exactly why SECRET_KEY/INTERNAL_TOKEN are never injected: a regenerated secret would silently re-key a surviving database on reinstall. The install is no-wizard (INSTALL_LOCK) and the admin is created afterwards through the in-container CLI. Two optional faces beyond the single-port norm: --dsn moves the metadata to an external PostgreSQL (never created or dropped by pgcli), and git-over-ssh rides the image’s OpenSSH sshd, which runs on every instance (the s6 service is unconditional). --ssh-port N makes that sshd the live clone endpoint on host port N; off by default it is just parked on a pool port — but it must be parked somewhere, because on the host network an sshd left on 22 would fight the host sshd forever. Listen defaults to 0.0.0.0 (a Git host is cloned from other machines), guarded by the generated admin password and closed self-registration. On macOS it joins pgcli-net with the web port — and, when SSH is enabled, the SSH port — published; code-complete but not yet exercised on a Mac.
• BeeGFS — Linux only; macOS installation is refused. The first multi-container addon: one config entry is one converged single-host filesystem — three containers (mgmtd + meta + storage) started in dependency order and managed as a unit. It is the only addon with no bridge path and no bridge fallback: BeeGFS speaks its own TCP/UDP protocol (not HTTP), so the daemons run on --network host and bind their stock protocol ports directly — and because 8.4’s meta (8005) and storage (8003) have no listen-port override at all, exactly one filesystem can exist per host (guarded, not pooled). No macOS equivalent exists to bridge to: without host networking there is no correct BeeGFS topology, so pg addon install beegfs fails fast on a Mac rather than half-working. Ownership is the inverse of Gitea/Grafana: the images run as root and own their bind-mounted dirs, so pgcli never chowns anything. The client side is a kernel module (DKMS) that cannot be containerised — pgcli deliberately never installs or mounts it and instead prints the exact client config via pg addon beegfs mount-hint.
• nginx — both platforms (macOS lightly tested). The HTTP reverse proxy joins pgcli-net on macOS with its ports published — the same bridge path the object stores use; it has been exercised there but not under load. The upstream image is multi-arch (amd64 + arm64).
• pg mc (MinIO client) — both platforms. It runs the mc binary from a throwaway container, with your ~/.mc/config.json mounted as its own default config path and local file operands (for cp/mirror/diff) mounted at their real paths — on macOS that means under the home directory, the only tree the podman machine shares. On Linux it uses host networking, so a 127.0.0.1 alias reaches a local addon instance; on macOS the container sits on the bridge, where a 127.0.0.1 alias is the container’s own loopback — point aliases at host.containers.internal:<port> for a local Mac addon, or a routable address for a remote store.

Confirming your platform

pg start prints the detected platform in its banner, so you can confirm which path you are on:

=== pg start ===
Platform: linux        # or: macOS

2 - Quick Start

Get pgcli up and running in minutes.

Installation

Install pgcli with a single command:

curl -fsSL https://raw.githubusercontent.com/mars-base/pgcli/main/scripts/install.sh | bash

This script will:

• Download the latest pgcli binary for your platform
• Install it to /usr/local/bin (or ~/.local/bin if no sudo)
• Add pgcli to your PATH

Initialize Configuration

# Create the base directory
mkdir -p ~/pg

# Initialize config with a default instance
pg config init --add default --base-dir ~/pg

# Separate data and backup onto different disks (production recommended)
pg config init --base-dir /data/pg --backup-dir /mnt/backup/pg --add default

This creates ~/.pgcli/pg.yaml with sensible defaults including:

• A default instance named default
• Data directory at ~/pg/default
• Auto-assigned ports starting from 35432

The --backup-dir flag is optional but recommended for production: it puts backup data and logs on a separate path from instance data, so a data-disk failure does not cascade into backup loss.

Start Instance

# Start the default instance
pg start

# Check status and connection info
pg status

The output shows the connection URL, admin password, and backup status.

Connect to Your Database

# Using pgcli's built-in psql wrapper
pg psql

# Or connect directly with the connection string shown in pg status
psql postgres://admin:<password>@localhost:35432/admin_db

# Execute SQL directly
pg exec "SELECT version()"

Basic Operations

# List all instances
pg list

# Stop an instance
pg stop

# Stop all instances, backup container, and PgBouncer
pg stop --all

# Start an instance
pg start

# View instance status
pg status

# Execute SQL
pg exec "SELECT version();"

Multi-Instance

# Create additional instances
pg create -i proj01 --base-dir /data/pg
pg create -i proj02 --base-dir /data/pg

# List all instances
pg list

# Start all instances
pg start --all

Multiple Config Files (Isolation)

For isolated testing environments or one config per project on the same host, generate a separate config file per environment with a distinct --namespace and disjoint port ranges.

# Environment "t1": containers prefixed pgcli-pg-t1-*, PG ports from 38000
pg config init -o ~/.pgcli-t1/pg.yaml --namespace t1 --pg-start-port 38000 --pg-ssh-port 43000 --add proj1

# Environment "t2": different namespace and disjoint ports, with separate backup disk
pg config init -o ~/.pgcli-t2/pg.yaml --namespace t2 --base-dir /data/t2 --backup-dir /mnt/backup/t2 --pg-start-port 38100 --pg-ssh-port 43100 --add proj2

# Manage each environment with -c
pg -c ~/.pgcli-t1/pg.yaml start -i proj1
pg -c ~/.pgcli-t2/pg.yaml list

Planning tips:

• Always pass an explicit --namespace to avoid container name clashes.
• Port ranges must not overlap between configs on one host.
• The namespace is baked into container names at creation time. Changing it later requires pg destroy and re-init.

Interactive psql Session

# Open interactive psql shell (default instance)
pg psql

# Open psql for specific instance
pg psql -i proj01

# Run SQL from stdin (non-interactive, for scripts)
echo "SELECT version();" | pg psql

# Execute a single SQL command
pg psql -- -c "SHOW work_mem"

# Connect to a different database
pg psql -- -d postgres

# Use psql meta-commands
pg psql -- -c "\dt"     # list tables
pg psql -- -c "\du"     # list users
pg psql -- -c "\l"      # list databases

# Connect to a remote database via connection string
pg psql --dsn postgres://user:pass@host:5432/db

Inside the interactive psql shell you get:

• SQL queries with history and tab completion
• psql meta-commands (\dt, \du, \l, etc.)
• \q to quit

Container Shell

# Open bash shell in container (default instance)
pg shell

# Open shell for specific instance
pg shell -i proj01

# Run a command directly
pg shell -- -c "ls -la /var/lib/postgresql/data"

# View logs
pg shell -- -c "tail -f /var/log/postgresql/postgresql-*.log"

The shell runs as root inside the container, giving full access to:

• PostgreSQL data directory (/var/lib/postgresql/data)
• Configuration files
• Log files (/var/log/postgresql/)
• All system tools and utilities

Next Steps

• Learn about backup and restore
• Set up replication for high availability
• Explore extensions management
• Configure auto-start on boot for production deployments
• Understand administration

3 - Namespace Isolation

Namespaces allow you to create completely isolated environments on a single host, perfect for separating production, development, and testing environments without container name conflicts or port collisions.

What is a Namespace?

A namespace is a prefix applied to all container names within a configuration file. This isolation mechanism ensures that:

• Container names don’t clash: Each namespace gets its own container prefix
• Port ranges are separate: Each config file allocates ports from its own range
• Backup containers are isolated: Each namespace has its own pgBackRest container
• Configuration files are independent: Each environment uses a separate config file

Use Case: Production and Development Environments

A common scenario is running production and development environments on the same server:

# Create production environment
pg config init \
  --namespace prod \
  --pg-start-port 35432 \
  --pg-ssh-port 42201 \
  --add app-db \
  -o ~/.pgcli-prod/pg.yaml

# Create development environment
pg config init \
  --namespace dev \
  --pg-start-port 38000 \
  --pg-ssh-port 43000 \
  --add app-db \
  -o ~/.pgcli-dev/pg.yaml

This creates two completely isolated environments:

Managing Multiple Environments

Use the -c flag to specify which configuration file to use:

# Start production database
pg -c ~/.pgcli-prod/pg.yaml start -i app-db

# Start development database
pg -c ~/.pgcli-dev/pg.yaml start -i app-db

# List instances in production
pg -c ~/.pgcli-prod/pg.yaml list

# List instances in development
pg -c ~/.pgcli-dev/pg.yaml list

How It Works

Container Naming

With namespace prod and instance app-db:

• Instance container: pgcli-pg-prod-app-db
• Backup container: pgcli-backup-prod
• Network: pgcli-net-prod (if using separate networks)

Without namespace (or --namespace ""):

• Instance container: pgcli-pg-default-app-db
• Backup container: pgcli-backup-default

Port Allocation

Each instance in a config file gets sequential ports:

• First instance: pg_start_port (e.g., 35432)
• Second instance: pg_start_port + 1 (e.g., 35433)
• And so on…

Same for SSH ports used by pgBackRest.

Configuration Persistence

The namespace and port ranges are saved in the config file:

namespace: prod
pg_start_port: 35432
pg_ssh_port: 42201

Best Practices

1. Always Use Explicit Namespaces

Never rely on the default namespace when running multiple configs on one host:

# Bad: both configs would use "default" namespace and clash
pg config init --add app -o ~/.pgcli-prod/pg.yaml
pg config init --add app -o ~/.pgcli-dev/pg.yaml  # Conflict!

# Good: explicit namespaces
pg config init --namespace prod --add app -o ~/.pgcli-prod/pg.yaml
pg config init --namespace dev --add app -o ~/.pgcli-dev/pg.yaml

2. Use Disjoint Port Ranges

Ensure port ranges don’t overlap between configs:

# Production: 35432-35999, 42201-42999
pg config init --namespace prod \
  --pg-start-port 35432 \
  --pg-ssh-port 42201 \
  --add app -o ~/.pgcli-prod/pg.yaml

# Development: 38000-38999, 43000-43999
pg config init --namespace dev \
  --pg-start-port 38000 \
  --pg-ssh-port 43000 \
  --add app -o ~/.pgcli-dev/pg.yaml

# Testing: 40000-40999, 44000-44999
pg config init --namespace test \
  --pg-start-port 40000 \
  --pg-ssh-port 44000 \
  --add app -o ~/.pgcli-test/pg.yaml

Leave headroom for multiple instances within each environment.

3. Namespace is Baked into Container Names

The namespace is embedded in container names at creation time. Changing it later breaks the link:

# Create with namespace "prod"
pg -c ~/.pgcli-prod/pg.yaml start -i app-db
# Container: pgcli-pg-prod-app-db

# Edit config to change namespace to "production"
# This WON'T work - container name mismatch!

# Instead: destroy and recreate
pg -c ~/.pgcli-prod/pg.yaml destroy -i app-db --clean-data
pg -c ~/.pgcli-prod/pg.yaml create -i app-db
pg -c ~/.pgcli-prod/pg.yaml start -i app-db

4. Use Shell Aliases for Convenience

Create aliases to avoid typing -c repeatedly:

# Add to ~/.bashrc or ~/.zshrc
alias pg-prod='pg -c ~/.pgcli-prod/pg.yaml'
alias pg-dev='pg -c ~/.pgcli-dev/pg.yaml'
alias pg-test='pg -c ~/.pgcli-test/pg.yaml'

# Now use:
pg-prod start -i app-db
pg-dev list
pg-test destroy -i test-db --force

Advanced: Multiple Environments with Replicas

You can even set up isolated replication environments:

# Production: primary + replica
pg -c ~/.pgcli-prod/pg.yaml create -i primary --base-dir /data/prod
pg -c ~/.pgcli-prod/pg.yaml replica create replica -i primary

# Development: separate primary + replica
pg -c ~/.pgcli-dev/pg.yaml create -i primary --base-dir /data/dev
pg -c ~/.pgcli-dev/pg.yaml replica create replica -i primary

Each environment maintains its own replication slots, backup stanzas, and data directories.

Troubleshooting

Container Name Conflicts

Error: container "pgcli-pg-default-app-db" already exists

Cause: Two configs using the same namespace.

Solution: Use different namespaces or destroy the conflicting instance first.

Port Already in Use

Error: listen tcp 0.0.0.0:35432: bind: address already in use

Cause: Port ranges overlap between configs.

Solution: Use disjoint port ranges with sufficient spacing.

Instance Not Found After Namespace Change

Error: instance "app-db" exists in config but container not found

Cause: Changed namespace in config file after creating instances.

Solution: Either destroy and recreate instances, or revert the namespace change.

Summary

Namespaces provide complete isolation for multiple environments on a single host:

• Separate configs: Each environment gets its own pg.yaml
• Distinct namespaces: Prevents container name collisions
• Disjoint ports: Avoids port conflicts
• Independent operation: Each environment managed separately with -c

Perfect for running production, development, testing, and staging environments on the same server without interference.

4 - Backup

Snapshots

# Create snapshot (full backup)
pg snapshot create -i proj01

# Create differential backup (recommended)
pg snapshot create --type diff -i proj01

# Stream backup container logs during snapshot
pg snapshot create --tail-logs -i proj01

# List snapshots
pg snapshot list -i proj01

# Limit the number of snapshots displayed
pg snapshot list --limit 5 -i proj01

# Delete snapshot
pg snapshot delete 20260826-073712F -i proj01

Snapshot types:

• full — Complete backup (default, self-contained)
• diff — Changes since last full backup
• incr — Changes since last backup

Shared Backup Container

All instances share a single pgbackrest container; each instance gets its own stanza in the repository.

One repository per config file. The container serves exactly one repository — the local directory under data_dir, or the single backup.repo.s3 endpoint when configured — and every stanza in the environment (regular instances and Patroni clusters alike) pushes to it. There is no per-instance or per-cluster repo choice: one pgcli environment therefore backs up to one store. To aim a second store (e.g. a second pg addon install minio) at a subset of clusters, run a second config (pg config init --namespace …) with its own backup container — worked out end to end in Example: HA Cluster with a Self-CA MinIO.

# Initialize the shared pgbackrest container (build image, create dirs, generate config)
pg backup setup

# Use a custom base directory for backup data and logs
pg backup setup --base-dir /mnt/backup

# Start / stop the backup container
pg backup start
pg backup stop

# Remove the backup container and its generated configs (pgbackrest.conf,
# ssh_config). Backup data and the SSH key pair are kept, so a later
# `pg backup setup` re-attaches to the same repository; --clean-data also
# deletes the local repository, logs and credentials.
pg backup remove
pg backup remove --clean-data

# Show backup container status
pg backup status

# List every stanza the backup container manages (one per PITR instance,
# one per Patroni cluster). These names feed stanza-upgrade below — they are
# repository labels, not container names.
pg backup list-stanza

# Refresh stanza metadata after a data directory was rebuilt (restore,
# recreate, reinit). Backups then fail with "[051] system-id ... do not match
# stanza"; stanza-upgrade re-syncs it without deleting existing backups.
# Pass the stanza name(s) explicitly (upgrade-all is intentionally not offered).
pg backup stanza-upgrade pgcli_default
pg backup stanza-upgrade pgcli_default pgcli_app-default

Backup infrastructure (network, image, directories, config, container) is prepared automatically on pg start; run pg backup setup manually to reinitialize, e.g. after changing the base directory.

S3 Object Storage Repository

Patroni cluster backups and WAL archiving can push to any S3-compatible store (MinIO, silo, rustfs, garage, AWS S3, …). Configure it under backup.repo.s3 in pg.yaml:

backup:
  repo:
    s3:
      endpoint: 10.0.0.9:9000     # host:port, no scheme
      bucket: pgbackrest
      access_key: admin
      secret_key: <password>       # stored in pg.yaml like every pgcli secret
      ca_file: /home/you/.pgcli/tls/minio/store/ca.crt   # required for a private CA

Then re-run setup — it performs the archiving orchestration:

pg backup setup

• HTTPS is mandatory. pgBackRest refuses plaintext S3 (upstream won’t implement it), so the endpoint must serve TLS. pgcli’s own MinIO does via pg addon install minio --tls — pgcli generates a self-signed CA whose ca.crt path you put in ca_file above. (silo and rustfs take --tls the same way; garage has no native TLS, so front it with the nginx addon — see Garage.) Publicly-caught endpoints (real AWS S3) just omit ca_file; if you truly cannot supply a CA, verify_tls: false is the escape hatch (no certificate verification).
• Fetching the CA from a remote store — no scp. When the MinIO lives on another machine, get its trust anchor with one TLS handshake instead of copying files: pg backup fetch-ca <store-host>:9002 pulls it out of the endpoint’s served chain, saves it under <base-dir>/backup/repo-ca/ and prints a SHA-256 fingerprint — trust-on-first-use, so cross-check the fingerprint against the store host before feeding the file to pg backup setup --s3-ca-file <path>. It covers exactly the two TLS shapes pgcli serves: a MinIO with --tls (the generated leaf+CA chain — the signing root is pulled from the chain) and a MinIO with a pg cert-minted BYO self-signed leaf (the leaf is the anchor, and is saved as-is). Publicly-trusted endpoints need no ca_file at all, so this command is pointless there.
• Cross-host HA needs no key/CA hand-copy. For a Patroni cluster spread over several hosts, ca_file and the backup SSH public key only have to be set up on one host: pg backup setup publishes the CA to the cluster’s etcd registry and a joiner with ca_file empty pulls it; each host’s backup public key is likewise published on pg ha create and merged into every member’s authorized_keys by setup. Only public material (the CA certificate, SSH public keys) enters the registry — never private keys or the S3 secret_key. See the HA doc, “Backing up a cross-host leader”.
• Archiving is automated. When setup finds a Patroni member whose archiving config is stale, it pauses the cluster, recreates members replicas-first (the recreate restarts PostgreSQL, which postmaster-level archive_mode needs), then resumes. Each member’s patroni.yml gets the same archive_command — one stanza per cluster, members listed as pg*-host so pgBackRest finds the primary itself — and WAL streams to S3 from then on.
• Blast radius. The S3 repo applies only to Patroni cluster stanzas; pg-managed regular instances keep backing up to the local repo, unchanged.

One-shot flag equivalent (secret_key is best edited into pg.yaml so it never lands in shell history):

pg backup setup --s3-endpoint 10.0.0.9:9000 --s3-bucket pgbackrest \
    --s3-access-key admin --s3-ca-file ~/.pgcli/tls/minio/store/ca.crt

5 - Restore

Point-in-Time Recovery (PITR)

Restore to any point in time after the first backup.

# Restore (read-only, inspect before committing)
pg restore --time "2026-08-26 15:30:00+00"

# Preview what would be restored without executing (dry run)
pg restore --time "2026-08-26 15:30:00+00" --dry-run

# Stream restore container logs during recovery
pg restore --time "2026-08-26 15:30:00+00" --tail-logs

# Try different time if needed
pg restore --time "2026-08-26 15:25:00+00"

# Promote to read-write (switches timeline)
pg restore --time "2026-08-26 15:30:00+00" --promote

# Skip confirmation
pg restore --time "2026-08-26 15:30:00+00" --promote --force

Time formats:

• 2026-08-26 15:30:00+08:00 — with timezone offset
• 2026-08-26 15:30:00+08 — timezone hour only
• 2026-08-26 15:30:00Z — UTC
• 2026-08-26 15:30:00 — assumed UTC

Recovery workflow: Stop → Restore → Start → WAL replay to target time

Note: After --promote, create a new full snapshot before further PITR.

Patroni cluster

A Patroni HA cluster restores with pg ha restore, the cluster-side twin of the single-instance command above. It takes the same --time (all formats), and supports --dry-run, --tail-logs, and --force.

# Preview the recovery plan without touching the cluster
pg ha restore app --time "2026-08-26 15:30:00+00" --dry-run

# Restore, streaming the bootstrap member's recovery logs
pg ha restore app --time "2026-08-26 15:30:00+00" --tail-logs

# Choose which local member carries the bootstrap (default: first local member)
pg ha restore app --time "2026-08-26 15:30:00+00" --member node1

# Skip the confirmation prompt
pg ha restore app --time "2026-08-26 15:30:00+00" --force

How it works: the cluster is first paused (freeze failover, so no remote replica is promoted while this host stops its local leader), then its DCS identity is removed, and one local member re-bootstraps its (emptied) data directory from the pgBackRest repo at the target time via Patroni’s custom-bootstrap method. That member comes up on a new timeline and promotes itself to the writable leader; the other members — this host’s and cross-host ones alike — rejoin it through the DCS. This needs a stanza with WAL archiving, provisioned by pg backup setup with an S3 repo (see the HA backup addon).

Differences from single-instance restore:

• It always promotes. There is no read-only “pause, inspect, retry another time” two-step — the cluster comes up writable at the target. Use --dry-run to confirm the target before executing.
• Leader-locality pre-check. Before touching anything, pg ha restore reads the current leader from the DCS and checks whether it is one of this host’s local members. On a real run it refuses if the leader is not local (a remote member, or a cross-host one absent from this host’s view); --dry-run only warns. The reason: the destructive path clears the DCS identity and re-bootstraps a local member, so a leader this host cannot stop would race the bootstrap to re-claim the DCS on the old timeline. Move leadership local first (pg ha switchover/failover) or run on the leader’s host. An empty/unreadable leader does not block the run (it covers the first bootstrap).
• No --promote flag — promotion is automatic.

Recovery workflow: Pre-flight (leader must be a local member) → pause the cluster → stop local members → remove DCS → wipe + bootstrap one member from the repo → it promotes to leader on a new timeline → remaining members rejoin as replicas.

After the restore: take a fresh full snapshot to re-baseline the new timeline — pg ha snapshot create app --type full. This usually succeeds directly: a same-stanza pgBackRest restore keeps the system-id across the timeline switch, so the first post-restore snapshot does not hit [051] system-id ... do not match stanza and no stanza-upgrade is needed. If a replica does not rejoin on its own, rebuild it with pg ha ctl app -- reinit app-<ns> <member> --force.

6 - Replica (read-only standby)

Create a read-only physical replica of an existing instance. The replica continuously streams WAL from its primary via PostgreSQL physical replication and serves read-only queries — useful for read/write splitting, reporting, or as a warm standby.

# Create a replica of the default instance
pg replica create ro1

# Create a replica of a specific instance
pg replica create ro1 -i proj01

# List replicas and replication lag
pg replica list

What happens

1. Pre-flight check — the primary must be running (verified before any config is written; a stopped primary fails with no side effects)
2. Register — a new instance entry is added with the primary’s database name and password (see Notes), PITR disabled, and replica_of set to the primary
3. Replication setup — on the primary:
   • pg_hba.conf gains host replication entries for loopback and RFC1918 ranges (idempotent)
   • a physical replication slot pgcli_r_<name> is created, reserving WAL so the replica can never fall behind WAL recycling
4. Base backup — pg_basebackup -R copies the primary’s data directory into the replica’s data dir, writing primary_conninfo (with password) and standby.signal so the replica boots in standby mode
5. Start — the replica container starts and streams WAL continuously

Verify

# Read-only standby?
pg exec -i ro1 "SELECT pg_is_in_recovery()"      # t

# Writes are rejected
pg exec -i ro1 "INSERT INTO t VALUES (1)"         # read-only transaction error

# Streaming is live
pg exec -i ro1 "SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp()"

# Slot is active on the primary
pg exec -i primary "SELECT slot_name, active FROM pg_replication_slots"

# Overview
pg list                              # ROLE/PRIMARY columns
pg replica list                      # NAME/PRIMARY/STATUS/LAG
pg status -i ro1                     # Role: standby (replica of ...)

Destroy

Destroying a replica is a 2-step process:

# Step 1: Destroy the replica instance (removes container + data + config entry)
pg destroy -i ro1 --clean-data --force

# Step 2: Drop the replication slot on the primary (no-op if already gone)
pg replica drop ro1 -i <primary>

Step 1 must run before step 2: PostgreSQL refuses to drop a slot that is still being streamed (replication slot is active), so the replica must be destroyed first to close its streaming connection.

Note: If the primary is a local pgcli-managed instance (same host), Step 2 can be skipped — destroy automatically cleans up the slot. Step 2 is required when the primary is not accessible from the replica host.

Cross-network replicas

The same-host flow above assumes primary and replica share one server (one podman daemon, one network). For a replica on another host, pgcli runs one command on each side — no SSH, the only cross-machine information is what you pass as parameters:

# ---- on the PRIMARY host: prepare the primary (run first) ----
pg replica create ro1 -i pg01 --replica-host 10.0.0.21

# ---- on the REPLICA host: copy data and start the replica (run second) ----
pg replica create ro1 --primary-dsn "postgres://admin:<password>@10.0.0.20:35432/pg01_db" --primary-name pg01

Getting the primary DSN

If the primary is a pgcli-managed instance, get its connection info with pg status:

pg status -i pg01
# ...
#   Connection: postgres://admin:<password>@127.0.0.1:35432/pg01_db

Then replace 127.0.0.1 with the primary host’s IP as seen from the replica host (e.g. 10.0.0.20) — the user, password and database are used as-is. Note the primary host must accept TCP connections on that port from the replica host (firewall/security group).

What each side does:

• Primary side (--replica-host <ip|hostname>): only prepares the primary — nothing is created locally. It appends a host replication all <addr> entry to pg_hba.conf and creates the physical slot pgcli_r_<name>, then prints the exact --primary-dsn command to run on the replica host. IPs get a /32 (/128 for IPv6) mask; hostnames are written as-is. An IP already inside a managed RFC1918 range (10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16) is skipped as redundant. Idempotent — re-running adds no duplicate lines.
• Replica side (--primary-dsn): first verifies the slot exists on the primary (validates connectivity and ordering — running before the primary side fails with an actionable message and no side effects), then registers the instance, runs pg_basebackup from the DSN over the network (host networking), and starts the standby.

The replica side runs only on the replica host: user, database and password of the replica instance come from the DSN (physical replication copies pg_authid, so the local password must equal the primary’s). The primary name is given with --primary-name and recorded as replica_of; it does not have to exist in the replica host’s config, and -i is not used for the remote primary — if given, it keeps its strict meaning and must reference a real local instance.

Destroy is symmetric, one command per host — in this order:

# 1. on the REPLICA host: removes container + config, also drops the slot
#    on the remote primary via DSN (automatic if primary is reachable)
pg destroy -i ro1

# 2. on the PRIMARY host (only if step 1's DSN connection failed):
#    drop the slot manually
pg replica drop ro1 -i pg01

Step 1 must run before step 2: PostgreSQL refuses to drop a slot that is still being streamed (replication slot is active), so the replica must be destroyed first to close its streaming connection.

Automatic slot cleanup: When a cross-host replica has PrimaryDSN set, destroy automatically attempts to drop the replication slot on the remote primary via DSN. If the primary is reachable, no manual step 2 is needed. replica drop is idempotent — re-running when the slot is already gone succeeds as a no-op.

Non-pgcli primary

The primary does not have to be managed by pgcli — the replica side works against any PostgreSQL server, as long as the primary side has been prepared manually (the slot check only verifies the slot exists, not who created it):

1. Allow replication from the replica host in pg_hba.conf, then reload (SELECT pg_reload_conf()):

   host replication <replica user> <replica ip>/32 scram-sha-256

2. Create the physical slot with the exact name pgcli_r_<replica-name> (the replica side checks this name):

   SELECT pg_create_physical_replication_slot('pgcli_r_ro1');

   Requires wal_level = replica (or logical) and a user with REPLICATION privilege — the DSN user.

Then the replica-side command is unchanged:

pg replica create ro1 --primary-dsn "postgres://<user>:<pass>@<primary ip>:5432/<db>" --primary-name pg01

On destroy, there is no pgcli on the primary side — after pg destroy -i ro1, drop the slot manually:

SELECT pg_drop_replication_slot('pgcli_r_ro1');

If a base backup fails (e.g. network hiccup), destroy the replica and re-run the replica-side command — the slot and hba entry on the primary side remain valid.

Notes

• Read-only — the replica rejects all writes (cannot execute INSERT in a read-only transaction). To make it writable you would promote it (pg_ctl promote), which is not exposed as a pgcli command yet
• Same data, same password — physical replication is a byte-for-byte copy of the primary, including pg_authid. The replica’s admin password and database name are therefore identical to the primary’s; only container name, port and data directory differ. With --dsn-style connections use the replica’s port
• PITR disabled on replicas — a standby archives nothing and is not registered with the pgBackRest backup container; backups run on the primary
• Primary must be running — both for initial creation (pg_basebackup) and for continuous streaming; if the primary restarts, the replica reconnects automatically (slot shows active)
• Lag display — replica list lag (now() - pg_last_xact_replay_timestamp()) grows while the primary is idle; it drops back to zero on the next replicated transaction. This is expected idle behavior, not drift
• Idempotent start — repeated pg start -i ro1 skips the base backup when the data directory is already initialized

7 - HA Cluster

Patroni is the de-facto standard for PostgreSQL high availability: it owns each postmaster’s lifecycle, streams replication between members, and performs automatic failover when the leader is lost. pgcli exposes Patroni as its own top-level command — pg ha — a distinct mode rather than a plain pg instance or an addon installed with pg addon install.

Note on placement. Patroni pages used to live under Addons. They are listed here as a standalone HA Cluster section because pg ha is not installed via the addon system — the addon index only mentions it for discovery. The etcd DCS and the HAProxy load balancer remain addon pages (etcd, HAProxy); this section links to them where relevant.

Pages

The typical path

# one host: bootstrap a cluster (on an installed etcd addon member)
pg ha create app --member node1 --etcd m1
pg ha create app --member node2 --etcd m1

# other hosts register their members with the same password set
pg ha passwords app --file app-passwd.yml
ssh other-host
pg ha create app --member node3 --advertise-host 10.0.0.12 \
    --etcd-endpoints 10.0.0.9:2379 --passwords-file app-passwd.yml

# run SQL directly — the leader is resolved from the DCS
pg ha exec app "SELECT version()"
pg ha psql app

# back it up and be able to go back in time
pg backup setup --s3-endpoint ...
pg ha snapshot create app --type full
pg ha restore app --time "2026-08-26 15:30:00+00"

Related

• Addons: etcd (the DCS), HAProxy (a stable client endpoint with optional read/write split), MinIO (the S3 repo the backups live in)
• Restore → Patroni cluster for PITR basics shared with single-instance restores
• Failover: Replica Promotion for the non-Patroni, single-replica promotion path (pg replica)
• Namespace Isolation — scopes, stanzas, and container names are namespace-prefixed

7.1 - Patroni HA

Patroni is the de-facto standard for PostgreSQL high availability: it manages each postmaster’s lifecycle, streams replication between members, and performs automatic failover when the leader is lost. pgcli exposes Patroni as a distinct mode — pg ha — rather than folding it into the plain pg instance path.

This is a separate mode, not an addon subcommand. Patroni (not pgcli) owns the PostgreSQL process. pg ha members do not appear in cfg.Instances, share no instance lifecycle code, and are not created by pg create. The one thing it borrows from the addon system is the etcd DCS.

Linux only, like the etcd addon: Patroni members rely on podman host networking. Both root and rootless podman are supported. On macOS the commands fail fast with a clear message.

Ownership boundary

The single most important thing to internalize is who owns what:

pgcli never edits a running PostgreSQL’s config directly, and never runs initdb — Patroni does both. This is also why the plain PG image’s docker-entrypoint-initdb.d convention (the admin role / default database) does not apply here: Patroni bootstraps its own cluster, so the role system is postgres (superuser), replicator, and rewind_user instead.

Container lifecycle ≠ safe PG restart

Because Patroni is PID 1 inside its container:

• pg ha create is re-install semantics (stop + recreate the container). Recreating the leader takes that node fully offline and triggers a failover. Changing a member’s config by re-running create is fine; for dynamic settings prefer pg ha edit-config, which never touches the container.
• pg ha start / pg ha stop are raw container start/stop. Stopping the leader’s container is exactly as disruptive as the node crashing — Patroni will fail over to a replica. For planned maintenance, pg ha pause first (it disables auto-failover), then stop the container.
• A paused cluster has no automatic failover until resumed.

How It Works

pg ha runs one Patroni container per member. All members of a scope point at the same DCS (a etcd cluster) which holds the cluster’s dynamic configuration and leader lock:

1. The first pg ha create for a scope bootstraps: Patroni runs initdb, wins the leader race, and becomes the leader.
2. Every later member automatically pg_basebackups from the current leader and begins streaming — no flag distinguishes “add” from “join”.
3. pg ha control commands (switchover, pause, …) run patronictl in an ephemeral container against the DCS, so they work even when no member container is running locally.

The DCS is the source of truth: Patroni re-renders each member’s postgresql.conf/pg_hba.conf from it every loop, so hand-edits on disk are lost — use pg ha edit-config instead.

Scope and the DCS layout

The <scope> in pg ha create <scope> is the Patroni cluster name — the identity of one HA cluster. Every command that takes a scope (status, switchover, failover, pause, ctl, …) names the same cluster; the first create for a scope bootstraps it, later creates with the same scope add members. (--member is the per-host node name inside the cluster — one member per machine.)

In pg.yaml the cluster is keyed by that scope under addons.patroni.<scope>.

What actually lands in etcd

Patroni stores everything under a fixed etcd namespace plus the scope:

• namespace: /service/ — Patroni’s top-level key, not changed by pgcli.
• scope: pgcli writes PatroniScope(scope) into patroni.yml, which is your scope plus the pgcli namespace suffix. Patroni has no namespace concept of its own (its etcd prefix is the raw scope), so pgcli bakes the suffix into the scope to keep two pgcli namespaces sharing one etcd from cross-talking.

So the full etcd prefix for a cluster is:

/service/<scope>[-<namespace>]/          # e.g. /service/app/  (no namespace)
                                         #      /service/app-prod/  (namespace: prod)
├── initialize       # bootstrap marker (written once)
├── leader           # current leader; value = member name
├── members/<member> # per-member registration (conn_url, api_url, state)
├── status           # cluster LSN / state
├── config           # dynamic config (pause lives here too)
├── history          # config revision history
├── failover         # manual failover request
└── sync             # synchronous-replication state

The keys below the prefix are Patroni’s own DCS layout. To inspect them, point pg etcdctl at a running etcd member of the same host/config:

ETCDCTL_ENDPOINTS=http://127.0.0.1:2379 pg etcdctl get /service/ -- --prefix --keys-only

Note the scope shown there carries the namespace suffix, even if you configured the cluster with the bare name.

Install

Prerequisite: a DCS. Either reuse local etcd addon members or point at an external etcd.

# 1. a DCS — here, the etcd addon (see the etcd page for multi-node setups)
pg addon install etcd --name m1

# 2. the first member bootstraps the cluster (becomes leader)
pg ha create app --member node1 --etcd m1

# 3. further members join automatically as replicas
pg ha create app --member node2 --etcd m1
pg ha create app --member node3 --etcd m1

# 4. watch it
pg ha status app

pg ha status app renders patronictl list — exactly one Leader row and the rest Replica … streaming:

+ Cluster: app (7683433951661608987) +-----------+----+-------------+-----+------------+-----+
| Member | Host            | Role    | State     | TL | Receive LSN | Lag | Replay LSN | Lag |
+--------+-----------------+---------+-----------+----+-------------+-----+------------+-----+
| node1  | 127.0.0.1:5432  | Leader  | running   |  1 |             |     |            |     |
| node2  | 127.0.0.1:5433  | Replica | streaming |  1 |   0/3000060 |   0 |  0/3000060 |   0 |
| node3  | 127.0.0.1:5434  | Replica | streaming |  1 |   0/3000060 |   0 |  0/3000060 |   0 |
+--------+-----------------+---------+-----------+----+-------------+-----+------------+-----+

With no argument, pg ha status rolls up every cluster: container state, member count, and each member’s pg=/rest= ports.

Choosing a DCS

Patroni only needs a reachable etcd cluster, so there are two layouts:

• Co-located — run the etcd members as addons on the same hosts as the Patroni members and pass --etcd m1,m2,m3. Simplest: one machine per role, no extra hosts. Good for a starter 3-node HA cluster.

• Dedicated DCS hosts — run the etcd cluster on its own (virtual) machines and point every Patroni member at it with --etcd-endpoints. Each line below runs on a different machine (pgcli is per-host):

  # host E1 (10.0.0.20) — bootstrap the etcd cluster
  pg addon install etcd --name e1 --cluster prod \
      --advertise-host 10.0.0.20 --client-port 2379 --peer-port 2380
  
  # host E2 (10.0.0.21) — join
  pg addon install etcd --name e2 --cluster prod \
      --advertise-host 10.0.0.21 --client-port 2379 --peer-port 2380 \
      --join http://10.0.0.20:2379
  
  # host E3 (10.0.0.22) — join
  pg addon install etcd --name e3 --cluster prod \
      --advertise-host 10.0.0.22 --client-port 2379 --peer-port 2380 \
      --join http://10.0.0.20:2379
  
  # hosts A / B / C — one Patroni member each; same endpoint list everywhere
  pg ha create app --member node1 --advertise-host 10.0.0.11 \
      --etcd-endpoints 10.0.0.20:2379,10.0.0.21:2379,10.0.0.22:2379

  This is the more available topology: etcd’s quorum survives losing a PG host, and re-imaging a database machine never takes the DCS down with it. The PG hosts only ever talk to the DCS; --etcd-endpoints lists all member client URLs, so Patroni falls through to the next endpoint if one etcd host is down (writes still need the etcd quorum itself — 2 of 3). Keep the endpoint list identical on every PG host.

  A single endpoint (--etcd-endpoints 10.0.0.20:2379) does work — that one etcd member serves the whole cluster, and the other two are hidden behind it. But it re-introduces a single point of failure: if E1 goes down, Patroni cannot reach the DCS even though the etcd quorum is healthy, and a leader that fails to renew its lock demotes itself. List every endpoint you actually have.

A dedicated odd-sized etcd cluster (3 or 5) is the production recommendation; co-locating is fine for dev and small footprints.

Commands

Flags after -- reach patronictl verbatim (cobra strips the --), so pg ha ctl app -- show-config, pg ha ctl app -- topology, and pg ha edit-config app -- -s synchronous_mode=true --force all work. edit-config --show is a convenience alias for ctl … -- show-config.

Cross-host members

Each host runs its own pgcli managing only that host’s members; the cluster reassembles through the shared DCS, so two hosts’ pg.yaml files each hold a partial view of one scope.

# host A (10.0.0.11) — bootstrap (its own etcd, or an external DCS)
pg ha create app --member node1 --advertise-host 10.0.0.11 \
    --etcd-endpoints 10.0.0.9:2379,10.0.0.10:2379

# host A — export the generated password set for other hosts
pg ha passwords app --file app-passwd.yml

# host B (10.0.0.12) — join, sharing the SAME DCS and password set
pg ha create app --member node2 --advertise-host 10.0.0.12 \
    --etcd-endpoints 10.0.0.9:2379,10.0.0.10:2379 \
    --passwords-file app-passwd.yml

Cross-host checklist (all three must line up on every host):

1. Ports — auto-assignment is per-host, but connect_address is stored in the DCS cluster-wide. Pass --host-port / --restapi-port with the same value on every host, or replicas can’t reach each other.
2. Passwords — Patroni’s replication / rewind / REST-API auth is cluster-wide. Export the first host’s generated set with pg ha passwords app --file app-passwd.yml and pass --passwords-file app-passwd.yml to every other pg ha create.
3. --advertise-host — required for cross-host members; it flips the listen address to 0.0.0.0 and puts a reachable IP in connect_address. Left empty, a member is loopback-only. This applies to the first (bootstrap) member too: it becomes the leader, and its loopback connect_address is what every later host would try to pg_basebackup from — cross-host joins fail forever once the cluster was bootstrapped with the default. If you may ever add a member on another host, pass your LAN IPv4 from the very first create.
4. Firewall — allow the two ports (PG + REST API) pairwise between members.

pgcli does not validate the peers’ config; pg ha status shows each member’s connect_address so you can self-check reachability.

Backing up a cross-host leader

Each host’s pg.yaml records only its own members, so this host cannot see a leader running on another host — yet pgBackRest’s stanza-create and full backups must connect to the leader.

This now happens automatically: after pg ha create installs a member, it publishes that member’s pgcli-private ports (SSH / REST API) into an etcd registry at /pgcli/ha/<scope>/<member> (the topology itself — member name + host:pgport — Patroni already stores in the DCS). The backup config generators then run patronictl list for the cluster-wide topology and look up each remote member’s SSH port in the registry, so pgbackrest.conf / ssh_config include members on every host, with pg1-host and the SSH HostName pointing at the remote IP (local members still go via 127.0.0.1). pg ha start/stop/remove, autostart, and pg ha extension skip members that aren’t local — they belong to their own host’s pgcli.

Just refresh the backup config:

pg backup setup

Cross-host backup trust is now automatic. A cluster-wide stanza lists every member as a pg*-host, so a full backup / check from any host SSH-probes the members on all hosts to find the primary — each of those member sshd processes must therefore accept the initiating host’s backup key, and its --advertise-host already flipped the listener to 0.0.0.0 (see above) so cross-host SSH reaches it. pg backup setup handles this: each host’s create publishes its backup public key into the same /pgcli/ha/<scope>/<member> registry, setup merges every member’s key into a per-cluster authorized_keys file, and each member container bind-mounts that file as an extra AuthorizedKeysFile. Because sshd re-reads it on every login and the merge rewrites the file in place, a host that joins later is trusted by the running members without a restart (the file only needs the one-time recreate to add the mount, which setup does inside the pause window alongside the archive-config recreate). A member removed with pg ha remove drops its registry key, so the next merge stops trusting it.

The S3 repository CA is distributed the same way: the host that configured ca_file publishes the certificate to /pgcli/ha/<scope>/.repo/ca, and a joiner that leaves ca_file empty pulls it and points its own ca_file at the pulled copy. When the store itself lives on yet another machine, the first host gets the CA without any file copy too — pg backup fetch-ca <store>:<port> pulls it out of the endpoint’s TLS chain (see the MinIO doc). Only public material — SSH public keys and the self-signed CA — ever enters the registry; private keys, passwords, and the S3 secret_key never do (the DCS link is unauthenticated).

Fallback: a remote member created on its host before that host upgraded pgcli has no registry entry, so the generators fall back to this scope’s base SSH port for it (every host starts at patroni_ssh_start_port, so the first member is usually right). When it isn’t, register just that member manually to override (creates no container):

pg ha remote app --member node3 --ssh-port 42301   # writes members.<m>.remote_host
pg ha remote remove app --member node3             # undo the registration

S3 backups and WAL archiving

Patroni clusters ship with archiving off — full backups alone can never reach a point-in-time. Enabling the S3 repository (backup docs, English; 中文) flips it on. Each cluster is one stanza — pgcli_<scope>, matching the namespaced DCS scope, so two pgcli namespaces sharing one S3 bucket never collide — whose backup-side config lists every member as a pg*-host, so pgBackRest finds the primary itself and keeps working across failovers. pg backup setup renders the identical archive_command into each member’s local patroni.yml (pgcli never writes archive GUCs into DCS — that is edit-config’s territory, and Patroni applies a local value whenever DCS does not manage the key), recreates stale member containers replicas-first inside a pause window (the recreate is also the postmaster restart archive_mode needs), and runs stanza-create + check for the cluster. From then on WAL streams to S3 continuously, from whichever member holds the leader lock.

Expect a short per-member offline window and a planned leader demotion at the end — the same recreate semantics as pg ha create.

Passwords

The first pg ha create for a scope generates four credentials — superuser, replication, rewind, and the restapi basic-auth pair (restapi_user / restapi_password) — and stores them in pg.yaml under the cluster. You never pass passwords on the command line: the first member generates them, and pg ha passwords exports the stored set:

pg ha passwords app                          # print the stored set (YAML) to stdout
pg ha passwords app --file app-passwd.yml    # write it to a file (mode 0600) instead

(Without --file the YAML goes to stdout — prefer --file so the secrets stay out of shell history and scrollback.)

The four roles and their default usernames (fixed by pgcli — you only ever set the passwords, which default to a random 16-char string per role). Override the length with --password-length on pg ha create / pg create (8–64; it only applies to the generate path, not to a --passwords-file or an already-stored set):

The postgres / replicator / rewind_user usernames are baked into the rendered patroni.yml (postgresql.authentication); only the passwords are the generated / --passwords-file part. restapi_user is the one username you can override, via the passwords file.

For full control (or when you’d rather author the file yourself) use the same format with --passwords-file:

# app-passwd.yml
superuser: <postgres superuser password>
replication: <replicator password>
rewind: <rewind_user password>
restapi_user: postgres          # optional, defaults to postgres
restapi_password: <REST API basic-auth password>

pg ha create app --member node1 --etcd m1 --passwords-file app-passwd.yml

pg ha create prints where each password came from (generated-and-stored vs. a file path). The rendered patroni.yml is written mode 0600 because it embeds all of them.

Auto-start on Boot

Like the other infra addons, a Patroni member’s container can be brought up after a host reboot — but it only starts the existing container, reading the patroni.yml already on disk; it never re-renders config or re-creates data.

pg autostart enable --ha --scope app --name node1

Members are toggled one at a time (--ha --scope <scope> --name <member>). Start order relative to the DCS doesn’t matter: Patroni retries until etcd answers, then re-elects normally. See the autostart page for the boot-service mechanics.

Logs

pg logs addon patroni --scope app --name node1          # last 50 lines
pg logs addon patroni --scope app --name node1 -f       # follow

Container name is pgcli-patroni-<scope>-<member> (namespace-prefixed when a namespace is set).

Connecting

pg ha exec and pg ha psql are the zero-config path: pgcli resolves the leader from the DCS itself, authenticates with the stored superuser password, and runs psql in a throwaway container — no dsn to assemble, no member container to enter, and remote members are reachable (plain TCP + scram, so a replica on another host works too; --member aims at a specific node).

pg ha exec app "SELECT version()"
pg ha exec app --member node2 "SELECT pg_is_in_recovery()"   # read-only, on a replica
pg ha psql app                                               # interactive

For clients outside pgcli, connect to the leader’s PostgreSQL port. Find the leader with pg ha status app (the Leader row’s Host is its connect_address), then point pg psql at it. On a single host with default loopback-only members, that is 127.0.0.1:<host_port>.

pg psql --dsn postgres://postgres@127.0.0.1:<leader_port>/postgres

After a failover the leader changes, so a fixed connection string should be avoided unless fronted by a pooler or the Patroni REST API’s leader redirect.

For a stable endpoint that survives failover — and optional read/write separation — put HAProxy in front of the cluster.

Planned leader changes

Patroni owns failover; pg ha wraps the patronictl verbs that move the leader on purpose. All three take just the scope:

pg ha switchover app                     # patronictl prompts for the candidate
pg ha switchover app --candidate node2   # name it up front
pg ha switchover app --candidate node2 --yes   # skip prompts (scripted)

pg ha failover   app --candidate node2 --yes   # promote now, no handoff
pg ha pause      app                          # stop auto-failover
pg ha resume     app                          # re-enable it

• switchover is the planned, graceful one: the current leader is demoted first, so nothing is in flight when the candidate is promoted. Both members must be healthy and caught up. The old leader automatically rejoins as a streaming replica a few seconds later (you may briefly see it as stopped while Patroni restarts its postmaster). A new timeline is opened — this is normal, not a split-brain.
• failover force-promotes a replica immediately without a clean handoff from the leader. Use it only when the leader is already gone or you are deliberately discarding it; on a healthy cluster it just causes an unnecessary blip. Reach for switchover for anything planned.
• pause turns off automatic failover cluster-wide. Do this before any planned container surgery — pg ha stop --all, a pg ha create recreate, or pg ha extension — so Patroni doesn’t promote a replica out from under you. pg ha resume puts auto-failover back. (A paused cluster has no automatic failover until resumed.)

These are pure DCS operations — they run patronictl in a throwaway container, touch no member’s container or data, and work even when some members live on other hosts. Like every pg ha control command, the scope is resolved to the namespaced DCS key (app → app-default), so a single-host or cross-host leader is switched the same way.

Because clients dial the leader’s port, the connection target moves after a switchover — see Connecting and put HAProxy in front if you need one stable endpoint.

pg ha vs. pg replica — which to pick

pgcli has two ways to get a standby:

If you need a standby you control by hand, use pg replica. If you need the cluster to survive a node crash without a human, use pg ha.

Configuration

The rendered patroni.yml per member is derived entirely from pg.yaml under addons.patroni.<scope>. A typical entry:

addons:
  patroni:
    app:
      name: app
      etcd_members: [m1]                # or etcd_endpoints for an external DCS
      passwords:
        superuser: <superuser-password>
        replication: <replication-password>
        rewind: <rewind-password>
        restapi_user: postgres
        restapi_password: <restapi-password>
      members:
        node1:
          container_name: pgcli-patroni-app-node1
          image_tag: ghcr.io/mars-base/pgcli/pgcli-patroni:18-4.1.5
          host_port: 35590
          restapi_port: 39090
          data_dir: /home/you/.pgcli/addon/patroni/app/node1
          autostart: false

Ports come from two independent pools — patroni_start_port (default 35532) for PostgreSQL and patroni_restapi_start_port (default 39060) for the REST API — so Patroni members never collide with plain-instance or addon ports.

The DCS-scope key is the scope plus the namespace suffix (Patroni has no namespace concept of its own, so the prefix is baked into the scope to keep two pgcli namespaces sharing one etcd from cross-talking).

Default patroni.yml

The file below is what pgcli renders and mounts read-only into each member’s container. Passwords are auto-generated at bootstrap time; --passwords-file lets you supply your own set.

scope: app-default
namespace: /service/
name: node1

etcd3:
    hosts: 10.0.0.11:2379       # from --etcd-endpoints or local etcd member
    protocol: http

restapi:
    listen: 0.0.0.0:8009
    connect_address: 10.0.0.11:8009
    authentication:
        username: postgres
        password: <auto-generated>

bootstrap:
    dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
            use_pg_rewind: true
            use_slots: true
            parameters:
                wal_level: replica
                hot_standby: "on"
    initdb:
        - encoding: UTF8
        - data-checksums
    pg_hba:
        - local all all trust
        - local replication all trust
        - host all all all scram-sha-256
        - host replication all all scram-sha-256

postgresql:
    listen: 0.0.0.0:35532
    connect_address: 10.0.0.11:35532
    data_dir: /var/lib/postgresql/data
    bin_dir: /usr/lib/postgresql/18/bin
    pgpass: /patroni/.pgpass
    use_unix_socket: true
    use_unix_socket_repl: true
    authentication:
        superuser:
            username: postgres
            password: <auto-generated>
        replication:
            username: replicator
            password: <auto-generated>
        rewind:
            username: rewind_user
            password: <auto-generated>
    parameters:
        unix_socket_directories: /var/lib/postgresql

Key points:

• scope = Patroni cluster name. Combined with namespace, it forms the etcd key prefix.
• etcd3.hosts = host:port only (no http:// scheme). Multiple endpoints are comma-separated.
• bootstrap.dcs values are defaults only — they take effect on first bootstrap; afterwards use pg ha edit-config.
• postgresql.listen: 0.0.0.0 is set when --advertise-host is used (cross-host). Without it, listen is 127.0.0.1.
• Passwords are generated once and stored in pg.yaml; exported via pg ha passwords.

Dynamic Configuration

Patroni’s dynamic configuration lives in the DCS (etcd) under /service/<scope>/config. Every member reads it on each loop (every loop_wait seconds) and applies changes live — so pg ha edit-config is the right way to tune runtime parameters without restarting containers.

bootstrap.dcs is one-time. The bootstrap.dcs block in patroni.yml only takes effect when the first member of a scope runs pg ha create (i.e. the cluster bootstrap). Once Patroni writes the config into the DCS, subsequent changes to bootstrap.dcs in the YAML file are completely ignored — even on re-install (pg ha create). To change dynamic configuration after bootstrap, use pg ha edit-config.

The common pitfall: re-running pg ha create does not re-read bootstrap.dcs from the YAML — Patroni sees the existing config key in the DCS and uses it directly.

Method	Description
pg ha edit-config app -- -s key=value	Recommended — pgcli’s standard way
pg ha ctl app -- edit-config	Passthrough to patronictl, same effect
Patroni REST API (PATCH /config)	Requires access to a member’s REST API port

Persistence: changes are written directly to etcd, not to container files. Container restarts, pg ha start/stop, or even pg ha create (re-install) do not lose these settings — new members automatically pick up the latest config from the DCS.

Viewing the current config

pg ha edit-config app --show

This is a convenience alias for pg ha ctl app -- show-config. The output is the full JSON blob stored in /service/<scope>/config.

Modifying parameters

# Set a single parameter
pg ha edit-config app -- -s loop_wait=5

# Set multiple parameters
pg ha edit-config app -- -s loop_wait=5 -s retry_timeout=3

# Apply without confirmation (useful in scripts)
pg ha edit-config app -- -s loop_wait=5 --force

# Interactive edit (opens $EDITOR with the current config)
pg ha edit-config app

After a change, all members apply it on their next loop (within loop_wait seconds). No restart needed.

Common tunable parameters

PostgreSQL runtime parameters can also be set under postgresql.parameters:

pg ha edit-config app -- -s 'postgresql.parameters.max_connections=200'
pg ha edit-config app -- -s 'postgresql.parameters.work_mem=64MB'

These trigger a PostgreSQL reload (or restart, depending on the parameter’s context). Check pg_hba.conf and postgresql.conf parameter documentation for which settings require a restart.

What not to edit

• Do not edit patroni.yml on disk — it is regenerated from pg.yaml on every pg ha create, and Patroni reads dynamic config from the DCS anyway.
• Do not edit postgresql.conf directly — Patroni overwrites it each loop from the DCS config.
• Do not change scope or namespace — these are baked into the DCS key at bootstrap time and cannot be changed without recreating the cluster.

Full parameter reference: Patroni Dynamic Configuration covers every DCS-tunable parameter with defaults, constraints, and examples.

Extensions

pg ha extension install app pg_stat_statements,pg_cron   # install
pg ha extension list app                                  # list
pg ha extension remove app pg_cron                        # remove

Full reference: Extensions in HA Clusters covers the orchestration order, cross-host workflow, shared_preload_libraries ordering, and builtin-only fast path.

Notes

• Linux only (root or rootless). Rootless members run as the host user via --userns=keep-id; root members chown config and data dirs to postgres (uid 999) so the container can read 0600 config and write its data dir.
• pg_hba.conf is permissive by design (host all all all scram-sha-256
  • a replication line, plus local trust lines). Rootless podman’s pasta rewrites loopback sources, and Patroni’s own replace_pg_hba step only ever grants its resolved loopback TCP address during custom bootstrap — so postgresql.use_unix_socket/use_unix_socket_repl are set to make Patroni connect to its own instance over the unix socket instead, which is unaffected by the rewrite and keeps bootstrap from deadlocking on its own pg_hba. Tightening the allow-list to a fixed set is a planned future refinement — do not expose these ports to untrusted networks yet.
• The DCS (etcd) has its own security caveats — see the etcd page: pgcli-managed etcd runs without TLS or auth.
• No init.sh / docker-entrypoint-initdb.d. Patroni bootstraps the cluster itself, so the admin/default-db convention of plain instances does not exist here; use postgres (superuser) to connect and create roles.
• use_slots / use_pg_rewind are enabled: Patroni owns replication slots and can rejoin a crashed leader via pg_rewind instead of a full rebase.

7.2 - Patroni Dynamic Configuration

These parameters are stored in the DCS (etcd) under /service/<scope>/config and applied to every member of the cluster. Modify them with pg ha edit-config:

pg ha edit-config app -- -s loop_wait=5
pg ha edit-config app -- -s 'postgresql.parameters.max_connections=200'
pg ha edit-config app --show          # view current config

Reference: Patroni official docs, Pigsty dynamic config.

bootstrap.dcs is one-time. The bootstrap.dcs block in patroni.yml only takes effect when the first member of a scope bootstraps the cluster. Once Patroni writes the config into the DCS, subsequent changes to bootstrap.dcs in the YAML file are completely ignored — even on re-install (pg ha create). Re-running pg ha create does not re-read bootstrap.dcs — Patroni sees the existing config key in the DCS and uses it directly.

Core timing parameters

Constraint when changing loop_wait, retry_timeout, or ttl:

loop_wait + 2 * retry_timeout <= ttl

Failover parameters

Replication mode

PostgreSQL settings

Set PostgreSQL parameters via the postgresql.parameters key:

pg ha edit-config app -- -s 'postgresql.parameters.max_connections=200'
pg ha edit-config app -- -s 'postgresql.parameters.work_mem=64MB'
pg ha edit-config app -- -s 'postgresql.parameters.wal_log_hints=on'

Standby cluster

If defined, the cluster bootstraps as a standby cluster that streams from a remote primary.

Replication slots

Permanent slots example

slots:
  permanent_physical_slot:
    type: physical
  permanent_logical_slot:
    type: logical
    database: my_db
    plugin: pgoutput

ignore_slots:
  - name: externally_managed_slot
    type: physical

Node-pinned physical slots

For a fixed cluster topology, define a permanent physical slot per node to prevent slot deletion during temporary outages:

slots:
  node1:
    type: physical
  node2:
    type: physical
  node3:
    type: physical

Warning: Permanent replication slots are synced from the primary/standby_leader to replicas only. Applications should use them on the leader node. Using permanent slots on replicas causes unbounded pg_wal growth across the cluster. Exception: physical slots matching a Patroni member name (created and maintained by Patroni) are synced across all nodes for inter-node replication.

Viewing the current configuration

pg ha edit-config app --show

Or read directly from etcd:

ETCDCTL_ENDPOINTS=http://10.0.0.1:2379 pg etcdctl get /service/app-default/config -- --prefix

7.3 - Patroni REST API

Patroni exposes an HTTP REST API on each member, providing health check endpoints (for load balancers and Kubernetes probes), monitoring data (including Prometheus metrics), and cluster management operations.

Reference: Patroni official docs, Pigsty REST API.

Finding the REST API

Each member’s REST API port is assigned automatically by pgcli and stored in pg.yaml under addons.patroni.<scope>.members.<name>.restapi_port. The port is also embedded in the rendered patroni.yml as restapi.connect_address.

# Find REST API ports
pg ha status app          # shows pg= and rest= ports for each member

Authentication

pgcli enables basic-auth on the REST API (username postgres, auto-generated password). But authentication is method-based, not blanket:

This is by design: read-only health checks stay open so a load balancer or Prometheus can poll them without credentials, while operations that change cluster state (POST /failover, PATCH /config) require authentication. The credentials exist so patronictl and other Patroni members can perform writes.

So a load balancer health check needs no auth:

# No -u needed — returns 200 on leader, 503 on replica
curl -s http://<host>:<port>/primary -w "%{http_code}"

Write operations do require auth. The password is the same restapi_password used in patroni.yml’s restapi.authentication section. Export it with:

# Export passwords, then extract the restapi_password
pg ha passwords app --file app-passwd.yml
grep restapi_password app-passwd.yml
# restapi_password: <password>

# Example: a write operation with auth
curl -u postgres:<restapi-password> -X PATCH http://<host>:<port>/config -d '{"ttl": 60}'

Health Check Endpoints

All health check endpoints respond with GET requests. Patroni returns a JSON document describing the node state, along with an HTTP status code. Use HEAD or OPTIONS instead of GET when only the status code is needed (no response body).

Primary-only endpoints (200 only on leader)

These return HTTP 200 only when the node is the current leader holding the leader lock:

# Returns 200 on leader, 503 on replica
curl -s -u postgres:<pw> http://<leader-host>:<port>/primary -w "%{http_code}"

These endpoints are useful for load balancer health checks that should route writes only to the current primary.

Replica-only endpoints (200 only on replica)

# Streaming replica only
curl -s -u postgres:<pw> "http://<replica-host>:<port>/replica?replication_state=streaming"

# Replica with lag < 1 MB
curl -s -u postgres:<pw> "http://<replica-host>:<port>/replica?lag=1048576"

Read-only endpoints (200 on both primary and replica)

PostgreSQL health

Kubernetes Probes

# Example Kubernetes probes
livenessProbe:
  httpGet:
    scheme: HTTP
    path: /liveness
    port: 8008          # REST API port
  initialDelaySeconds: 3
  periodSeconds: 10
  timeoutSeconds: 5
  failureThreshold: 3

readinessProbe:
  httpGet:
    scheme: HTTP
    path: /readiness
    port: 8008
  initialDelaySeconds: 3
  periodSeconds: 10
  timeoutSeconds: 5
  failureThreshold: 3

Monitoring Endpoints

GET /patroni

Returns detailed node status as JSON. Used internally by Patroni during leader election and also useful for monitoring:

curl -s -u postgres:<pw> http://<host>:<port>/patroni | jq .

Response fields:

GET /cluster

Returns the full cluster topology — all members with their roles, states, and replication status:

curl -s -u postgres:<pw> http://<host>:<port>/cluster | jq .

{
  "members": [
    {
      "name": "node1",
      "role": "leader",
      "state": "running",
      "api_url": "http://10.0.0.11:8008/patroni",
      "host": "10.0.0.11",
      "port": 35532,
      "timeline": 1
    },
    {
      "name": "node2",
      "role": "replica",
      "state": "streaming",
      "host": "10.0.0.11",
      "port": 35533,
      "timeline": 1,
      "receive_lag": 0,
      "receive_lsn": "0/3000168",
      "replay_lag": 0,
      "replay_lsn": "0/3000168"
    }
  ],
  "scope": "app-default"
}

GET /config

Returns the current dynamic configuration stored in the DCS:

curl -s -u postgres:<pw> http://<host>:<port>/config | jq .

This is equivalent to pg ha edit-config <scope> --show.

GET /history

Returns the timeline history. Empty array [] when no timeline switch has occurred (fresh cluster).

GET /metrics

Returns monitoring data in Prometheus exposition format, suitable for scraping by Prometheus or compatible systems:

curl -s -u postgres:<pw> http://<host>:<port>/metrics

Key metrics:

Tag-based Filtering

Health check endpoints accept query parameters to filter by custom tags defined in the member’s patroni.yml tags section:

# Only replicas with tag dc=us-east
curl -u postgres:<pw> "http://<host>:<port>/replica?dc=us-east"

# Only leader with tag region=primary
curl -u postgres:<pw> "http://<host>:<port>/leader?region=primary"

Practical Patterns

Load balancer routing

Because GET health checks need no auth, a load balancer can poll the REST API endpoints directly. The pattern (from Patroni’s example haproxy.cfg) is: the backend TCP port is the PostgreSQL port, but the health check hits the REST API port with GET /, which returns 200 only on the leader.

Single read-write endpoint (all traffic → current leader):

global
    maxconn 100

defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /

listen app
    bind *:5000
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server node1 <ip>:35532 maxconn 100 check port 8008
    server node2 <ip>:35533 maxconn 100 check port 8009

• check port 8008 / 8009 — the HTTP health check goes to the REST API port, not the PG port.
• GET / returns 200 only on the leader → HAProxy marks only the leader as UP.
• on-marked-down shutdown-sessions — on failover, sessions to the old leader are killed so clients reconnect to the new leader.
• fall 3 / rise 2 with inter 3s — ~9s to mark down, ~6s to mark up.

Read/write split — add a second listener that uses GET /replica (200 only on replicas) for read traffic:

listen app_rw
    bind *:5000
    mode tcp
    option httpchk GET /
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server node1 <ip>:35532 maxconn 100 check port 8008
    server node2 <ip>:35533 maxconn 100 check port 8009

listen app_ro
    bind *:5001
    mode tcp
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2
    server node1 <ip>:35532 maxconn 100 check port 8008
    server node2 <ip>:35533 maxconn 100 check port 8009

• app_rw (:5000) — GET / → only the leader is UP → writes land on the leader.
• app_ro (:5001) — GET /replica → only replicas are UP → reads spread across replicas.

The same GET /replica?lag=1MB refinement (from the replica endpoints above) keeps a lagging replica out of the read pool.

Monitoring with curl

Quick health check script:

#!/bin/bash
# Check all members — no auth needed for GET
for port in 8008 8009; do
  code=$(curl -s http://10.0.0.11:$port/health -o /dev/null -w "%{http_code}")
  echo "Port $port: HTTP $code"
done

Prometheus scrape config

GET /metrics needs no auth, so the scrape config works without credentials:

scrape_configs:
  - job_name: patroni
    static_configs:
      - targets:
        - '10.0.0.11:8008'   # node1
        - '10.0.0.11:8009'   # node2
    metrics_path: /metrics

7.4 - Extensions in HA Clusters

Installing PostgreSQL extensions in a Patroni-managed HA cluster is fundamentally different from a single-node instance. This page explains why, and walks through the pg ha extension command subtree that handles it.

Why not pg extension install?

The single-node flow (pg extension install <instance> <ext>) works by:

1. Building a -ext derived image with Pigsty packages
2. Stopping and recreating the container from the new image
3. Editing postgresql.conf to set shared_preload_libraries
4. Running CREATE EXTENSION inside the container

In a Patroni cluster, steps 2-4 all break:

• Patroni is PID 1. Recreating a container takes that node offline. If it’s the leader, Patroni triggers a failover — the cluster reshuffles while you’re mid-install.
• Patroni regenerates postgresql.conf every loop from the DCS. Any direct edit to the file is overwritten within seconds. shared_preload_libraries must be set via patronictl edit-config (which writes to the DCS).
• CREATE EXTENSION must run on the leader, which may be on a remote host — not inside any local container.

pg ha extension orchestrates all of this correctly.

How It Works

The install flow follows a specific order to avoid the pitfalls above:

1. Validate extension names (IsExtensionKnown)
2. Build -ext image with Pigsty packages
3. patronictl pause <scope> --wait            ← disable auto-failover
4. Recreate each member container (one at a time, wait for rejoin)
5. patronictl resume <scope> --wait           ← ⚠ must come BEFORE edit-config
6. patronictl edit-config (set shared_preload_libraries)
   → Patroni triggers a rolling restart (replicas first, leader last)
7. Wait for rolling restart to complete
8. CREATE EXTENSION on leader
9. Save extensions list to pg.yaml

The critical ordering is resume before edit-config: a paused Patroni cluster does not apply edit-config changes. If you edit-config while paused, the shared_preload_libraries update is silently lost.

Builtin-only fast path

If all requested extensions are builtin (contrib extensions like hstore, uuid-ossp that ship with PostgreSQL), steps 2-4 are skipped entirely — no image build, no container recreate, no pause/resume. The flow goes straight to edit-config + CREATE EXTENSION.

Commands

Install

pg ha extension install <scope> <extension>[,<extension>...] [flags]

Builds the -ext image, pauses the cluster, and recreates local member containers.

• Single-host cluster (all members local): automatically runs apply (resume + edit-config + CREATE EXTENSION)
• Cross-host cluster: only performs pause + recreate, cluster stays paused. Run install on each host separately, then run pg ha extension apply on any host to complete the installation.

Extensions are passed as a comma-separated list:

# Install a single extension
pg ha extension install app pg_stat_statements

# Install multiple extensions to a specific database
pg ha extension install app pg_cron,pg_stat_statements --database mydb

# Skip the rolling restart confirmation prompt
pg ha extension install app pgvector --auto-restart

Flags:

⚠ --database affects all extensions. When you specify --database, the apply phase runs CREATE EXTENSION IF NOT EXISTS for all installed extensions (not just the newly added ones) in the target database. For example, if the cluster has [pg_cron, pg_stat_statements, pgvector] installed, running pg ha extension install app hstore --database mydb will create all four extensions in mydb. The extension .so files are already in the image and won’t be reinstalled — only the SQL objects (functions, types, etc.) are registered in the target database.

If you only want to enable an existing extension in a specific database, there’s no need to reinstall. Simply connect to that database with psql and run CREATE EXTENSION IF NOT EXISTS directly.

Remove

pg ha extension remove <scope> <extension>[,<extension>...] [flags]

Runs DROP EXTENSION on the leader, then updates shared_preload_libraries via patronictl edit-config (triggers a rolling restart). Does not rebuild the image or recreate containers — the -ext image only grows; disk reclamation is rare and manual.

Extensions are passed as a comma-separated list:

pg ha extension remove app pg_cron
pg ha extension remove app pg_stat_statements,pg_cron --auto-restart

List

pg ha extension list <scope>

Shows three views of the cluster’s extensions:

• Config (pg.yaml): the extensions list stored in pg.yaml
• DCS (preload): the shared_preload_libraries value from patronictl show-config
• Leader (installed): actual extensions from pg_extension on the leader

$ pg ha extension list app
Cluster "app" extensions:

  Config (pg.yaml):  [pg_stat_statements pg_cron]
  DCS (preload):     shared_preload_libraries: pg_stat_statements,pg_cron
  Leader (installed): [pg_cron pg_stat_statements]

Apply

pg ha extension apply <scope> [flags]

Manually triggers the second half of the install flow: resume the cluster, run patronictl edit-config, and execute CREATE EXTENSION on the leader.

Used in cross-host clusters — after running install on each host (each builds the image and recreates its local members), run apply once on any host to complete the DCS update and extension creation.

pg ha extension apply app
pg ha extension apply app --database mydb --auto-restart

⚠ --database affects all installed extensions. apply runs CREATE EXTENSION IF NOT EXISTS for all extensions in the cluster in the specified database, not just the newly added ones.

Cross-Host Workflow

In a cross-host cluster, each host manages only its own members. The extension installation workflow splits into two phases with global pause/resume — the cluster is paused once and resumed once, not per-host:

Phase 1 — per host (sequentially): Run pg ha extension install on each host. Each host:

• Builds the -ext image locally (merging DCS’s existing preload list to include all packages)
• Pauses the cluster (idempotent — second host won’t fail on “already paused”)
• Recreates its own local members from the new image (replicas first, leader last)
• Saves config — cluster stays paused, no resume

Recommended order: start from replica hosts. If the host with the leader runs install first, recreating the leader triggers a failover (leader moves to another host). Starting from replica hosts keeps the leader in place until the very end, minimizing failover-related data sync overhead.

# host A (replicas only — run first) — cluster enters paused state
pg ha extension install app pg_stat_statements,pg_cron

# host B (has the leader — run last) — cluster stays paused
pg ha extension install app pg_stat_statements,pg_cron

Phase 2 — once, on any host: Run pg ha extension apply to:

• Resume the cluster (idempotent — tolerates “not paused”)
• Update shared_preload_libraries via patronictl edit-config
• Wait for the rolling restart
• Run CREATE EXTENSION on the leader

pg ha extension apply app --auto-restart

Note: After install, the cluster is in paused state (auto-failover disabled). If you forget to run apply, run pg ha extension apply or manually patronictl resume <scope> to re-enable failover.

For single-host clusters (all members local), install automatically runs the apply step — no separate command needed.

About leader drift: Leader drift cannot be completely avoided at this time. When the container on the host where the leader resides is recreated, the Patroni process stops. After the leader key in the DCS expires (TTL), even if the cluster is paused, other replicas will still detect the expired key and trigger a new election. The replicas-first recreation order ensures that the host with the leader is the last one to run install, but it cannot prevent the leader drift itself. This is an inherent limitation of Patroni + container recreation.

The extensions Config Field

Extensions are tracked at the cluster level in pg.yaml, under the Patroni cluster config:

addons:
  patroni:
    app:
      name: app
      extensions:
        - pg_stat_statements
        - pg_cron
      passwords:
        superuser: ...
      members:
        node1: { ... }
        node2: { ... }

This list is the source of truth for what pg ha extension list reports and what apply installs. Both install and remove update it automatically.

shared_preload_libraries Ordering

Some extensions must appear at position 0 in shared_preload_libraries (PostgreSQL fatals if they’re not first). pgcli tracks this as a catalog attribute (PreloadFirst) — currently set on:

• citus — distributed PostgreSQL, must be loaded before anything else
• timescaledb — time-series engine, same constraint

Additional extensions may require companion DCS parameters:

• pg_cron needs cron.database_name

pg ha extension handles all of this automatically:

• Extensions with PreloadFirst are always placed at the start of the CSV, regardless of input order
• When pg_cron is present, cron.database_name is set to the --database value (default postgres)
• When pg_cron is removed, cron.database_name is cleared

Examples

Install pg_stat_statements and pg_cron

pg ha extension install app pg_stat_statements,pg_cron --database mydb --auto-restart

This builds an -ext image, recreates all members (with pause/resume), sets shared_preload_libraries=pg_stat_statements,pg_cron and cron.database_name=mydb in the DCS, then creates both extensions in mydb.

Install Citus (must be first in preload)

pg ha extension install app pg_stat_statements,citus

Even though citus is listed second, the preload CSV is generated as citus,pg_stat_statements — extensions with PreloadFirst are always placed at position 0.

Remove an extension

pg ha extension remove app pg_cron

Drops pg_cron from the leader, removes it from shared_preload_libraries, and clears cron.database_name. Triggers a rolling restart.

Check what’s installed

pg ha extension list app

Limitations

• Image rebuild is additive. Removing an extension does not rebuild the -ext image or shrink it. To reclaim disk, manually prune old images with podman image prune.
• No per-member extension list. Extensions are cluster-wide — all members share the same -ext image and the same shared_preload_libraries.
• CREATE EXTENSION targets one database. PostgreSQL extensions are per-database. To install in multiple databases, re-run with --database pointing at each one.
• Cross-host requires manual coordination. Each host must run install before apply is run once. pgcli does not SSH into remote hosts.

See Also

• Patroni HA — cluster setup, commands, and architecture
• Extensions — single-node extension management and the Pigsty catalog
• Patroni Dynamic Configuration — DCS parameters reference

7.5 - Patroni Cluster Backup

This page walks the complete procedure for backing up a Patroni HA cluster: getting the backup infrastructure up with pg backup setup, taking full/incr/diff backups with pg ha snapshot, and one operational pitfall — when a replica’s Replay LSN stalls and pg ha status shows members disagreeing on LSN, how to pull it back with patronictl reinit. Command and mechanism details live in Patroni HA and Backup; this page strings them into a path you can run end to end.

Model: one stanza per cluster, the backup container finds the primary

A Patroni cluster (scope) maps to one pgBackRest stanza: pgcli_<scope> (matching the namespace-qualified DCS scope, so two pgcli namespaces sharing one S3 bucket never collide). The stanza is cluster-wide — the backup-side config lists every member as a pg*-host, and pgBackRest SSH-probes them, locates the current primary itself, and keeps following it across failovers with no config change. Therefore:

• There is no per-member backup. full / incr / diff all operate on the one cluster stanza; pgBackRest snapshots whatever host is the leader at that moment.
• Backups run from the shared backup container, reaching the primary over SSH. Cross-host members are reachable too (trust is wired up automatically by setup).
• pg ha snapshot commands never need you to name the leader — pass the scope; pgBackRest resolves the primary.

Prerequisite: pg backup setup

One idempotent run brings up the whole backup path and gives the cluster archiving:

# simplest (backup data lands under the default base-dir)
pg backup setup

# S3 object repository (the prerequisite for archiving to MinIO / AWS S3).
# When the store is on another host and you have no ca.crt yet, pull it with
# one TLS handshake first — no scp:
pg backup fetch-ca 10.0.0.9:9000          # prints a SHA-256 fingerprint to cross-check
pg backup setup --s3-endpoint 10.0.0.9:9000 --s3-bucket pgbackrest \
    --s3-access-key admin --s3-ca-file ~/.pgcli/backup/repo-ca/ca-10.0.0.9-9000.crt

What setup does (when an S3 repo is configured, it first runs an endpoint preflight — a plain TCP dial that fails the run immediately on a mistyped host or a store that is down, rather than after the image pull / container start buries the real cause):

1. Build/pull the pgbackrest image, create the network and dirs, generate the backup-container pgbackrest.conf and the member-local archive view pgbackrest-archive.conf.
2. Start the shared backup container, then verify repository connectivity: a pgbackrest repo-ls proves the whole stack (TLS, credentials, bucket) and maps a failure to an actionable hint — a cert error points at pg backup fetch-ca, access-denied at the credentials, a refused/timeout connection at the endpoint.
3. Wire up cross-host backup trust: each host publishes its backup public key into the cluster’s etcd registry at pg ha create; setup merges every member’s key into one per-cluster authorized_keys bind-mounted into each member container. sshd re-reads that file on every login and the merge rewrites it in place, so hosts that join later are trusted without a restart. The S3 repo CA follows the same path: a host with ca_file publishes it into the registry, joiners with it empty pull it. (Details: HA → Backing up a cross-host leader.)
4. Enable WAL archiving: when a member’s archive config is stale, pause the cluster, recreate stale members replicas-first / leader last (a recreate is exactly the postmaster restart archive_mode needs), resume, and render the same archive_command into every member’s patroni.yml.
5. Run stanza-create + check for every cluster stanza.

HTTPS is mandatory. pgBackRest rejects plaintext S3. A MinIO meant to receive cluster archives must serve TLS — install it with pg addon install minio --tls (pgcli’s self-signed CA; point --s3-ca-file at its ca.crt). When the MinIO is on another machine, pull its CA with one TLS handshake — pg backup fetch-ca <store-host>:<port> — no scp needed (see MinIO → Getting the CA onto a remote host).

Confirm after setup:

pg backup status

Once the backup container is Up and the stanzas are ready, start backing up.

Create members after setup, on every host. A member’s archiving is fixed at creation time: the container only mounts pgbackrest-archive.conf if that file already exists, and the rendered patroni.yml only carries the archive GUCs if an S3 repo is configured. A member pg ha created before pg backup setup on its host therefore starts without WAL archiving — pg ha snapshot/pg ha restore cannot cover it until a later setup flags it stale and recreates it inside a pause window. pg ha create warns about this up front; run pg backup setup first on each host for a backup-ready create.

Snapshot operations: pg ha snapshot

These act on a cluster (scope), running pgBackRest against the current primary from the shared backup container. This is the Patroni-cluster counterpart of pg snapshot (single instance).

# full backup (default --type full); --tail-logs streams pgBackRest output
pg ha snapshot create app --tail-logs

# incremental (changes since the last backup)
pg ha snapshot create app --type incr

# differential (changes since the last full)
pg ha snapshot create app --type diff

# list every snapshot for the cluster (Start / Stop / Name / Type)
pg ha snapshot list app
pg ha snapshot list app --limit 5

# delete a specific snapshot (label from list) — prompts unless --force
pg ha snapshot delete app 20260916-140131F_20260917-015154I
pg ha snapshot delete app <label> --force

Notes:

• Before each command, pg ha snapshot re-renders the backup-container pgbackrest.conf from the current topology, so member adds/removes and port changes are reflected in the stanza immediately — the file is bind-mounted, no recreate.
• You can only delete a non-unique full: pgBackRest keeps at least one full, so deleting the only one is refused (take a new full first).
• create connects to whatever host is leader at the time; after a failover it still succeeds — that is the point of the cluster-wide stanza.

Pitfall: a stuck replica LSN → patronictl reinit

Symptom. In pg ha status app one replica’s Replay LSN trails its own Receive LSN (or visibly differs from another replica) and never converges over time. Note Patroni’s Lag column is relative to the leader, so every number moves when the leader’s LSN advances; to spot a real stall compare a replica’s own recv vs replay, or watch whether its Replay LSN stays frozen across checks.

Confirm. Three signals point to the same conclusion — the replica’s WAL replay is at a hole and streaming has actually stopped:

# 1) leader has no replication clients, slots inactive
pg exec --dsn "postgres://<user>@<leader_host>:<port>/postgres" \
  "SELECT client_addr, state FROM pg_stat_replication"
pg exec --dsn "postgres://<user>@<leader_host>:<port>/postgres" \
  "SELECT slot_name, active, confirmed_flush_lsn FROM pg_replication_slots"

# 2) the stuck replica has no walreceiver
pg exec --dsn "postgres://<user>@<replica_host>:<port>/postgres" \
  "SELECT status FROM pg_stat_wal_receiver"

# 3) the replica container log loops the same two lines (pg logs ha fetches by
#    scope + member name; -f follows, -n takes more lines):
pg logs ha <scope> -m <member> -n 50 | grep -iE "prev-link|waiting for WAL"
#   LOG: record with incorrect prev-link ... at 0/20000060
#   LOG: waiting for WAL to become available at 0/20000078

record with incorrect prev-link + a repeating waiting for WAL to become available means the WAL stream has a hole/discontinuity at a segment boundary and the startup process is dead-waiting for the next segment. Patroni often keeps logging “no action. I am … a secondary, and following a leader” each cycle — it believes it is following while the postmaster’s walreceiver has actually stopped, so it will not self-heal. This is a replication runtime issue, unrelated to backups.

Fix: reinit the replica. Have Patroni take a fresh pg_basebackup from the leader and restart replication. This destructively rebuilds that replica’s data directory (the leader and other replicas are untouched), so Patroni requires --force:

# CLUSTER_NAME is the namespace-qualified scope (the name after "Cluster:" in pg ha status)
pg ha ctl <scope> -- reinit <scope>-<ns-suffix> <member> --force
# example (namespace default -> -default suffix):
pg ha ctl app -- reinit app-default node1 --force

-f does not work: patronictl reinit only accepts the full --force (unlike switchover/failover’s --yes). Success: reinitialize for member node1 means it was dispatched.

Verify recovery. reinit wipes and rebuilds the data dir, then basebackups + replays. Poll until it is recv == replay and status=streaming:

pg exec --dsn "postgres://<user>@<replica_host>:<port>/postgres" \
  "SELECT pg_last_wal_receive_lsn() recv, pg_last_wal_replay_lsn() replay,
          (SELECT status FROM pg_stat_wal_receiver) wr"
#   expected: recv == replay, wr = streaming

pg ha status app    # the replica's Replay LSN catches up, Lag converges

After reinit completes, run pg ha snapshot create app --type full once to realign the snapshot baseline with the healthy topology.

7.6 - Patroni Cluster Restore

This page is the restore-side companion to Patroni Cluster Backup. Once a cluster has a stanza with WAL archiving (pg backup setup + pg ha snapshot), pg ha restore brings the whole cluster back to a point in time — the Patroni counterpart of the single-instance pg restore. It walks the mechanism (why a cluster PITR is not just pgbackrest restore), the safety pre-check on where the leader lives, an end-to-end runbook, and the re-baseline steps that must follow. Command/flag details also live in Restore → Patroni cluster; this page strings them into a procedure you can run.

Prerequisite: a stanza with WAL archiving, on this host

PITR can only replay up to what was archived. Before restoring, the cluster must already have:

• a cluster stanza provisioned against an S3 repo — pg backup setup --s3-endpoint ... (see Backup → S3 repository and HA backup);
• at least one full snapshot, plus the WAL segments covering the target time (taken with pg ha snapshot create <scope> --type full).

The target time is bounded by that history: earlier than the newest full backup’s stop time, or later than the last archived WAL, and the recovery cannot land there.

pg backup setup must have run on this host, and the backup container must be up. This is stricter than it sounds for a cross-host cluster:

• The member that carries the bootstrap restores from S3 using the repo config mounted into its container — pgbackrest-archive.conf at /etc/pgbackrest.conf plus the S3 CA at /etc/pgbackrest/ca.crt. Both are generated by pg backup setup, and the container only mounts them when the files exist. A host that never ran setup has no repo config to mount, so the pgbackrest restore bootstrap cannot reach S3 and fails to start.
• The leader-locality and stop-time pre-checks run pgbackrest ... info inside the backup container. pg ha restore refreshes the shared pgbackrest.conf best-effort but does not create the member archive config and does not start the backup container — so bring it up first (pg backup setup, or pg backup status to confirm it is Up). If the backup container is down, the pre-checks are skipped and the real failure surfaces only during the bootstrap; the post-restore fresh snapshot needs it running anyway.

In short: run pg ha restore on a host where pg backup setup has provisioned the S3 repo and the backup container is Up.

Mechanism: custom-bootstrap PITR, not a bare pgbackrest restore

A Patroni cluster cannot be PITR’d by running pgbackrest restore under a live postmaster — Patroni owns the data directory, the timeline, and the DCS identity. pg ha restore instead follows Patroni’s custom-bootstrap recipe:

1. Pause the cluster (patronictl pause). This freezes failover before anything is stopped: stopping the local leader while the cluster is live lets Patroni promote a remote replica this host cannot stop, and that new remote leader then races the local bootstrap to re-claim the DCS on the old timeline — defeating the leader-locality pre-check from inside the destructive path. A restore re-run against a cluster a previous attempt already tore down (DCS cleared, members stopped) has nothing live to pause; that is the already-frozen state, so the pause failure on an empty DCS is tolerated and the run continues.

2. Clear the cluster’s DCS identity (patronictl remove). Patroni’s bootstrap.dcs block only runs when the DCS has no config key, so removing it is what re-arms the bootstrap path.

3. Pick one LOCAL member and rewrite its patroni.yml so the default initdb bootstrap is replaced by a pgbackrest restore method targeting the point in time:

   bootstrap:
     method: pgbackrest
     pgbackrest:
       command: 'pgbackrest --stanza=pgcli_<scope> --type=time --target="<time>" --target-action=promote --delta restore'
       no_params: true
       keep_existing_recovery_conf: true

   no_params: true stops Patroni appending --scope/--datadir (which pgbackrest rejects with [031] invalid option); keep_existing_recovery_conf: true preserves the recovery.signal + restore_command + recovery_target_* that pgbackrest restore writes itself. The method/pgbackrest keys are siblings of bootstrap.dcs, never nested inside it — recovery GUCs leaked into the DCS config would apply as live settings on the promoted leader.

4. Wipe that member’s PGDATA and recreate its container. Patroni sees an empty data dir with no DCS config, runs the method, recovers to the target on a new timeline, and — via --target-action=promote — promotes itself to the writable leader.

5. The remaining members rejoin the new leader: this host’s other local members are wiped and restarted as standard replicas (they pg_basebackup the new leader); cross-host members rejoin through the DCS (or are reinitialized).

The leader-locality pre-check

pg ha restore refuses to run unless the current leader is a local member of this host. Before touching anything it reads the leader from the DCS and checks it is one of the members this host can control.

Why. Step 1 clears the DCS and step 3 re-bootstraps a local member on a new timeline. A leader that lives on another host keeps its Patroni daemon running — this host cannot stop it — so after the DCS key is removed that remote leader races to re-claim leadership on the old timeline, colliding with the local bootstrap. Requiring leadership to be local (so it can be stopped) removes the race.

What you see.

# dry-run: a loud warning, no refusal — you can still inspect the plan
pg ha restore app --time "2026-08-26 15:30:00+00" --dry-run
#   [!!] current leader "node3" is NOT a local member — the real run REFUSES until
#        leadership is on this host...

# real run on a host whose leader is remote: hard error, nothing is touched
pg ha restore app --time "2026-08-26 15:30:00+00"
#   current leader "node3" is not a local member of scope "app" ...
#   Move leadership here first (pg ha switchover app ...) or run on the leader's host

Fix. Move leadership onto a member of the host you are standing on, then retry:

pg ha switchover app    # or: pg ha failover app ...
pg ha status app        # confirm the leader is now a local member

Or run pg ha restore on the leader’s own host instead. An empty / unreadable leader (e.g. the very first bootstrap, before any member has published itself) does not block the run.

Differences from single-instance pg restore

• It always promotes. There is no read-only “hold, inspect, retry another time” two-step — the bootstrap recovery ends on a writable leader on a new timeline. Confirm the target with --dry-run first. (The patronictl pause in step 1 of the mechanism is unrelated: it freezes failover during the restore, it is not a read-only inspection window and the cluster is resumed implicitly when the new bootstrap republishes the DCS config.)
• There is no --promote flag — promotion is automatic (it is baked into the bootstrap command).
• It must run on a host that owns a member, and the leader must be local (see the pre-check above).

Runbook: restoring a cluster to a point in time

The full flag set is --time (required, all the formats in Restore), --member, --dry-run, --tail-logs, --force.

1. Confirm the target is safe — dry-run. Nothing is touched; you see the stanza, the chosen bootstrap member, the exact pgbackrest command, and (if applicable) the leader-locality warning.

pg ha restore app --time "2026-08-26 15:30:00+00" --dry-run

2. Make sure the leader is local (only if the dry-run warned). Switchover, then re-check status.

pg ha switchover app
pg ha status app

3. Restore. The default prompts for confirmation, listing scope, stanza, target, bootstrap member, and the “PERMANENTLY LOST” warning. Stream the recovery logs with --tail-logs; skip the prompt with --force for automation.

pg ha restore app --time "2026-08-26 15:30:00+00" --tail-logs
# or, picking the bootstrap member explicitly:
pg ha restore app --time "2026-08-26 15:30:00+00" --member node1 --force

4. Verify the new cluster comes up. waitForLeader polls the DCS until a leader appears (15-minute ceiling), then rejoin members start as replicas.

pg ha status app                     # new leader on a switched timeline, replicas streaming
pg exec --dsn "postgres://<user>@<leader_host>:<port>/postgres" \
  "SELECT ... FROM your_table"       # data stops at the target time; later commits gone

After the restore: re-baseline the new timeline

The restore is not the last step. The data directory was rebuilt, so two follow-ups are required before the cluster is backup-ready again:

1. Take a fresh full snapshot so future PITR has a base on the new timeline:

   pg ha snapshot create app --type full

   This usually succeeds directly: a pgBackRest restore of the same stanza preserves the PostgreSQL system-id across the timeline switch (verified), so the post-restore snapshot does not hit [051] system-id ... do not match stanza. You do not need pg backup stanza-upgrade after a cluster restore. (A real system-id change — a fresh initdb into the same stanza, e.g. a brand-new cluster reusing an old stanza name — is what [051] and stanza-upgrade are for.)

2. Bring back any member that did not auto-rejoin — typically a cross-host member that lost the old leader. Reinitialize it from the new leader (destructive to that replica’s data dir only):

   pg ha ctl app -- reinit app-<ns> <member> --force

   (Full details in HA backup → stuck replica reinit.)

Troubleshooting

• “no local member on this host” — you ran it on a host that owns none of the scope’s members. pg ha restore can only rebuild a local data dir + container. Run it on a host that owns a member, or on the leader’s host.
• “target time is before the latest backup stop time” — the earliest usable point is the newest full backup’s stop time; the error prints it and a suggested --time. Take a fresher full snapshot if you need a later base.
• Recovery exceeds the last archived WAL — you cannot restore past what was archived. Reduce the target time, or ensure archive_command is flowing (pg backup status) before retrying.
• First post-restore snapshot fails [051] — not expected: a same-stanza restore keeps the system-id (see the re-baseline section). If you do hit it, a pg backup stanza-upgrade fixes it non-destructively.
• FATAL: recovery ended before configured recovery target was reached on a repeat PITR into the same repo — the stanza already hosts a previously promoted timeline, and the default recovery_target_timeline=latest jumps onto that branch, which has no commits before your target. Restore again with an explicit older timeline pinned on the command, e.g. --recovery-option=recovery_target_timeline=<old-tl> (added to the bootstrap pgbackrest ... restore command in the member’s patroni.yml). A first PITR after the target — no branch crossing — never hits this.

7.7 - HA Exec / psql

pg ha exec and pg ha psql are the direct way to run SQL against a Patroni cluster. They are the cluster-side twins of the single-instance pg exec and pg psql: you name a scope, they resolve the target and connect. No --dsn to hand-assemble, no member container to podman exec into, and — because the connection is plain TCP — a leader or replica on another host is just as reachable as a local one.

Why not pg exec --dsn or podman exec

Before these commands, ad-hoc SQL on a cluster meant one of two awkward paths:

• pg exec --dsn postgres://postgres:<pass>@<leader>:<port>/db — correct, but you assemble it by hand: read the leader’s connect_address out of pg ha status, copy the superuser password out of the config, plug in the port. A fixed dsn also goes stale the moment the leader moves.
• podman exec -it <member> psql ... — reaches only a local member, so a leader on another host is out of reach; and you are inside a container the tool is meant to abstract away.

pg ha exec/psql fold both away: pgcli resolves the leader from the cluster’s own state, authenticates with the stored password, and runs psql in a short-lived container. You never see it.

How the target is resolved

The DCS roster (patronictl list -f json) is the only membership view that spans the whole cluster. Each host’s pg.yaml registers only its own members, so this host’s config cannot even see a node running elsewhere — but the DCS knows every member and its advertised connect_address. Both commands go through it:

• Default (no --member) → the current leader. If leadership has moved since you last looked, you still hit the right node — there is no stale dsn.
• --member <name> → that named member, wherever it lives. Aim it at a replica for a read-only query (SELECT off a standby, pg_is_in_recovery() returns t), or at a specific remote node.

Authentication is the cluster’s superuser over scram; pg_hba (host all all all scram-sha-256) accepts any source, so a remote member is reachable across hosts exactly the way replication traffic already is.

Usage

# One-shot SQL against the leader
pg ha exec app "SELECT version()"
pg ha exec app "SELECT count(*) FROM pg_stat_activity"

# A different database on the leader
pg ha exec app --database mydb "SELECT * FROM t LIMIT 5"

# Read-only: target a replica instead of the leader
pg ha exec app --member node2 "SELECT pg_is_in_recovery()"

# Interactive psql against the leader
pg ha psql app

# Interactive against a specific (even cross-host) member
pg ha psql app --member node3

# Pass extra psql arguments after --
pg ha psql app -- -c "SELECT 1"
pg ha psql app --database mydb -- -x

pg ha exec takes the SQL as trailing words (so you normally quote one string); pg ha psql opens an interactive shell and forwards anything after -- straight to psql. --database selects the target database on both (default postgres). There is no --user — the managed superuser is the only role pgcli holds credentials for, so that is what connects.

Interactive vs. scripted

pg ha psql allocates a TTY when your stdin is one; when it is not (piping a script, running under CI) it turns the pager off so the session terminates instead of blocking in less. That makes both of these behave as expected:

pg ha psql app < migrations.sql      # pipe a file, no pager, exits when done
printf '\conninfo\n' | pg ha psql app   # scripted one-liner, output streams back

Reading the output

pg ha exec streams psql’s own formatting — column headers, alignment, row counts, and errors go straight to your terminal, exactly as psql -c would print them. It is not a machine-parseable dump; if you need one, pipe through your own tooling or use pg ha exec app "..." --csv-style psql flags via pg ha psql app -- -c "...".

Where these fit

For a stable client endpoint that survives failover independently of pgcli, put HAProxy in front of the cluster and connect through it. pg ha exec/psql are for operators and scripts driving a cluster directly, not a substitute for a pooler in front of an application.

The pg ha status / pg ha ctl verbs remain the way to inspect cluster state and reach unwrapped patronictl commands; these two are specifically about running SQL.

7.8 - Example: HA Cluster with a Self-CA MinIO

This page is a worked example, run end to end and verified: create a Patroni HA cluster, stand up a MinIO addon that serves a bring-your-own domain certificate (a self-signed CA of our own making — the same shape as a public-CA or corporate-CA cert, just without buying one), and point the cluster’s pgBackRest backups and WAL archiving at it. Every command below is a real one from the run; only hostnames, ports, passwords, and the certificate material are genericized.

Two ideas make the example worth reading rather than skimming:

• BYO TLS on the store. The MinIO addon does not have to serve pgcli’s generated self-signed pair — --tls-cert / --tls-key let it serve a cert you already hold. A client that trusts the issuing CA needs no extra material; with a private (here: self-signed) CA you hand that CA to the backup stack via --s3-ca-file, exactly as with the generated one. See MinIO → Bring your own certificate.
• Isolation via a second config file. backup.repo.s3 is a single, global repository shared by every stanza in one pgcli environment. Repointing it at a new store would silently redirect the backups of every existing cluster on that host too. The clean way to try something new against a live estate is a separate config file with its own base_dir, namespace, and port ranges — which is what this example does.

The environment we built

Prerequisite — the etcd DCS

The example reuses the etcd that already serves this host’s production clusters. If you are starting from scratch, that first piece to install is the same addon, one member at a time — a lone member is a healthy one-node etcd cluster, plenty for HA dev/test:

pg addon install etcd --name m1
# pgcli-etcd-default-m1, client 127.0.0.1:2379, peer 2380, cluster "pgcli-etcd"

(For members other hosts will join as a DCS, give it a reachable address at install time — --advertise-host <host-ip> — so its peer/client URLs announce that IP instead of loopback; and grow it to a real 3-node ensemble with --name m2/--name m3 the same way. None of that matters for this example: the cluster lives on the same host and dials 127.0.0.1:2379.)

Step 0 — a domain certificate

Any PEM leaf + key works. For the demo we minted a self-signed one with pgcli’s own pg cert (validity via --valid-duration, SANs covering the name and IPs clients will dial):

mkdir -p ~/.pgcli-app1/certs
pg cert --host "minio1.test,127.0.0.1,<host-ip>" --valid-duration 8760h \
    --cert-file ~/.pgcli-app1/certs/store1.crt --key-file ~/.pgcli-app1/certs/store1.key
chmod 600 ~/.pgcli-app1/certs/store1.key

openssl x509 -in ~/.pgcli-app1/certs/store1.crt -noout -subject -issuer -ext subjectAltName -dates
# subject=...
# issuer=...                         <- self-signed: leaf and root are one and the same
# DNS:minio1.test, IP Address:127.0.0.1, IP Address:<host-ip>

pg cert writes a single self-signed leaf (CA:FALSE, server-auth) that is its own trust anchor — exactly the shape a public- or corporate-CA cert has, minus having bought one. With a public-CA (or corporate-CA) cert this step is simply “you already have the files” — point the flags at your fullchain.pem (leaf first, then intermediates) and key, and the client-side story gets easier: chains rooted in a trusted CA need no --s3-ca-file at all.

Step 1 — an isolated environment

mkdir -p ~/.pgcli-app1
pg config init -o ~/.pgcli-app1/pg.yaml \
    --base-dir /home/fish/bucket/pgcli-data-app1 \
    --namespace app1 \
    --pg-start-port 36500 --pg-ssh-port 43500

--namespace makes every container name this config creates distinct, so two environments live side by side in one podman. The config file is passed with -c on every subsequent command — from here on, every pg in this page means pg -c ~/.pgcli-app1/pg.yaml.

Port ranges. The default port pools (etcd 2379, MinIO 9000, Patroni 35532, …) start at the same values as the production config, and podman publishes the loopback ports of a host-networked container — so pass explicit, disjoint ports everywhere rather than letting both environments auto-assign the same numbers.

Step 2 — MinIO with the BYO cert

pg addon install minio --name store1 \
    --api-port 9010 --console-port 9011 --listen 0.0.0.0 \
    --tls-cert ~/.pgcli-app1/certs/store1.crt \
    --tls-key  ~/.pgcli-app1/certs/store1.key

-> MinIO BYO cert: CN="" issuer="" valid 2026-09-18 → 2027-09-18
  [OK] TLS certs (BYO: /home/…/.pgcli-app1/certs/store1.crt, valid 2026-09-18 → 2027-09-18)
         self-signed: clients pin the issuing CA (pg backup setup --s3-ca-file <ca.pem>)
  [OK] MinIO container started

Install-time validation pairs the cert and key (a mismatched, expired, CA-instead-of-leaf, or non-server-auth pair fails right here), prints the validity window, and — because the cert is self-signed — tells you clients will pin it as their trust anchor. --listen 0.0.0.0 rather than loopback: the backup container reaches the store over host networking via the host IP, and that IP is one of the SANs. A quick proof it serves your certificate:

curl -s --cacert ~/.pgcli-app1/certs/store1.crt \
     https://127.0.0.1:9010/minio/health/live -o /dev/null -w "%{http_code}\n"
# 200  — and curl verified the chain against exactly the cert we supplied

Create the repository bucket (pgBackRest does not create it):

pg mc alias set store1 https://127.0.0.1:9010 admin '<root-password>' -- --insecure
pg mc mb store1/pgbackrest

Step 3 — the Patroni cluster

pg ha create app1 --member nodea \
    --etcd-endpoints 127.0.0.1:2379 \
    --host-port 35632 --restapi-port 8028 \
    --advertise-host <host-ip>

!  No S3 backup repo configured on this host (backup.repo.s3): the member will
   be created WITHOUT WAL archiving ... Configure a repo and run `pg backup
   setup` to wire archiving in (it recreates members as needed).
  [OK] Patroni member nodea started
✓ Patroni member "nodea" installed in scope "app1"
  scope (DCS):  app1-app1        # namespace-qualified: "app1-" + scope "app1"
  pg:           <host-ip>:35632

The warning is the designed order of operations, not a problem: at create time no repo exists yet, so the member comes up without archiving; the backup setup in the next step configures the repo and recreates the member with archiving enabled. Note the DCS scope is app1-app1 (namespace prefix + scope) — pass the plain scope (app1) to every pg ha command and pgcli resolves the qualified form; it also keeps the stanza distinct from any app1 another namespace might run on the same bucket.

Seed something worth backing up (this also proves client auth over scram from the host):

pg ha exec app1 "CREATE TABLE IF NOT EXISTS byo_probe(id serial primary key,
    note text, ts timestamptz default now());
    INSERT INTO byo_probe(note) SELECT 'row-'||g FROM generate_series(1,500) g;"
pg ha exec app1 "SELECT count(*) FROM byo_probe"   # 500

Step 4 — point the backup stack at the BYO store

pg backup setup \
    --s3-endpoint <host-ip>:9010 --s3-bucket pgbackrest \
    --s3-access-key admin --s3-secret-key '<root-password>' \
    --s3-ca-file ~/.pgcli-app1/certs/store1.crt

--s3-ca-file takes our own cert as the trust anchor — for a self-signed cert the leaf embeds its own root, so the file you passed to --tls-cert is the CA file. pgBackRest passes the bundle to its verifier with no content inspection, so a public-CA cert needs no flag at all and a private-CA one just gets its chain here. The run then proves the full stack and rewires the member:

  [OK] <host-ip>:9010 reachable
  [OK] repo CA published to 1 cluster registry/registries (from …/store1.crt)
  [OK] repository reachable                      # a repo-ls over TLS: creds + CA + bucket all good
  [stale] app1/nodea: mounts the backup-side pgbackrest.conf (pg1-host breaks archive-push)
-> Pausing cluster app1 (no auto-failover during recreate)
  -> Recreating member nodea...                  # now with archive-push enabled
  [OK] app1: archive-ready after recreating 1 member(s)
  [OK] stanza pgcli_app1-app1 created
  [OK] check pgcli_app1-app1

The “repo CA published to the cluster registry” line is the etcd-distribution path: every other member of this scope (or future ones, like a second host) picks the CA up from the DCS with no manual scp and no extra flags.

Step 5 — back it up, and prove it landed

pg ha snapshot create app1 --type full
#   Name:    20260918-091519F
#   Type:    full
pg ha snapshot list app1                          # pgBackRest sees the backup in the repo

The independent proof is that the objects are physically in the bucket, written over a TLS session terminated by our certificate:

pg mc ls store1/pgbackrest -- --recursive
# …/backup/pgcli_app1-app1/20260918-091519F/backup.manifest
# …/backup/pgcli_app1-app1/20260918-091519F/pg_data/base/…  (.zst files)
# …/archive/pgcli_app1-app1/18-1/0000000200000000/000000020000000000000005-….zst
# …/archive/pgcli_app1-app1/18-1/0000000200000000/000000020000000000000005.00000028.backup
# …/archive/pgcli_app1-app1/archive.info

archive/ is continuous WAL archiving (the archive-push the recreated member runs), backup/ is the full snapshot. Confirm the archiver is healthy:

pg ha exec app1 "SELECT archived_count, failed_count, last_archived_wal
                 FROM pg_stat_archiver"

One subtlety from our run, worth knowing so you don’t chase a ghost: failed_count was 1, for 00000002.history — the timeline-history file, archived one second later successfully. It raced the brief window in which backup setup was pausing and recreating the member. A lone recent failure during a setup/recreate is self-healing; a growing count with the same WAL name is a real repo problem.

What the example demonstrates

• A MinIO addon serving a bring-your-own certificate works as a pgBackRest repository end to end: stanza-create, check, full backup, and continuous WAL archiving all flow over TLS verified against the supplied cert.
• Self-signed is not a lesser path: the same --s3-ca-file slot carries it (leaf==root), a public CA’s chain, or an internal CA bundle — the transport and the trust plumbing are identical.
• backup.repo.s3 is one global repo per config file. To experiment beside a running estate, don’t repoint it — init a second config (pg config init --namespace … --base-dir …) and run the whole experiment there. Namespace-qualified container names, DCS scopes, and stanzas keep the two environments from ever seeing each other.

Teardown

Everything the example created lives under the app1 config, so teardown is ordered through that same -c (data directories deleted explicitly):

pg -c ~/.pgcli-app1/pg.yaml ha remove app1 --scope-all --clean-data
pg -c ~/.pgcli-app1/pg.yaml backup remove --clean-data
pg -c ~/.pgcli-app1/pg.yaml addon remove minio --name store1 --clean-data
rm -rf ~/.pgcli-app1 /home/fish/bucket/pgcli-data-app1

--scope-all (not --member) is what clears the scope’s DCS keys in the shared etcd — patronictl remove plus the pgcli member registry. Removing members one by one leaves both behind.

The etcd m1 belonged to the production environment — it is untouched by all of the above, which is exactly the point of the isolation.

Related

• Patroni Cluster Backup — the backup machinery in depth
• MinIO — the addon, and its BYO certificate section
• Namespace Isolation — how --namespace scopes names
• Patroni HA — the pg ha command set

7.9 - Generating a Certificate — pg cert

pg cert mints a self-signed certificate — its SubjectAltNames covering any mix of DNS names and IP addresses you ask for — for any place you need TLS without going through a CA: a dev or test server, an internal endpoint, a service whose clients you control. The certificate it writes is a normal TLS server leaf, usable anywhere.

The use this docs tree exercises is pgBackRest’s S3 path: a Patroni cluster’s backups push to an S3 store (MinIO or silo, typically) that must serve TLS — pgBackRest refuses plaintext S3 — and pgcli’s MinIO/silo addons can serve a bring-your-own certificate (pg addon install minio --tls-cert ... --tls-key ...). The quickest way to get a cert to try that with is pg cert:

pg cert --host "minio.test,127.0.0.1,10.0.0.9" \
  --cert-file minio.crt --key-file minio.key

Nothing is registered with pgcli — it touches no pg.yaml and starts no container; it just writes the two files you name and prints the SANs. See MinIO → Bring your own certificate for the serving side, and the worked HA + self-CA MinIO example for it end to end.

What it produces

A single self-signed leaf certificate, not a chain:

• one CERTIFICATE block in --cert-file — the cert signs itself;
• CA:FALSE, extended key usage serverAuth — a proper TLS server leaf;
• SANs split automatically: any --host entry that parses as an IP lands in the IP SANs, the rest in the DNS SANs, so "minio.test,10.0.0.9" needs no special syntax (and *.wild.test works as a wildcard DNS entry).

Because it is self-signed, the leaf is its own trust anchor — hand the same .crt to any TLS client that lets you point at a CA file or bundle (curl --cacert, a browser’s import, an app’s SSL_CERT_FILE …). For pgBackRest’s S3 path specifically that means backup.repo.s3.ca_file / pg backup setup --s3-ca-file minio.crt. A remote host that never saw the file needs no scp either: pg backup fetch-ca <endpoint> recognizes a self-signed leaf in the TLS handshake and saves it back as the anchor. This is not a workaround pgBackRest merely tolerates: OpenSSL treats whatever you hand its trust store as an anchor regardless of CA:TRUE, and pgBackRest’s S3 path (curl over OpenSSL) is that mechanism — verified directly, openssl verify -CAfile minio.crt minio.crt returns OK.

With a real public- or private-CA certificate none of the self-anchor machinery applies: a chain rooted in a trusted CA needs no ca_file at all, and you already hold the issuing CA for a private one.

Flags

Selecting no key type at all (--rsa 0 --ecdsa "") is an error: exactly one of them must produce the key.

The --ca mode is not for --tls-cert

--ca makes the cert its own Certificate Authority — for when you want a private root to go sign further certs with. It is not something to hand to MinIO’s --tls-cert: ValidateBYOCert inspects the file’s first certificate and rejects one that is a CA, because a CA’s private key is a signing key, not a server key. The default (no --ca) is the server leaf you do want.

Standalone binary: gencert

The same generator is built as a standalone program for use outside pg:

make gencert          # -> bin/gencert
bin/gencert -host "minio.test,10.0.0.9" -cert-file minio.crt -key-file minio.key

Identical flag set, single-dash form (-host, -cert-file, …). Both front the one internal/certgen package, so their output is the same kind of certificate — pg cert is just the path that keeps you inside the CLI.

Example: full BYO chain in one host

# 1. mint a cert the MinIO will serve (SANs cover how clients dial it)
mkdir -p ~/.pgcli/certs
pg cert --host "minio.test,127.0.0.1,<host-ip>" --valid-duration 8760h \
  --cert-file ~/.pgcli/certs/store.crt --key-file ~/.pgcli/certs/store.key
chmod 600 ~/.pgcli/certs/store.key

# 2. MinIO serves it over HTTPS
pg addon install minio --name store --listen 0.0.0.0 \
  --tls-cert ~/.pgcli/certs/store.crt --tls-key ~/.pgcli/certs/store.key

# 3. the cluster's backups point at it; the served leaf is its own CA file
pg backup setup --s3-endpoint <host-ip>:9000 --s3-bucket pgbackrest \
  --s3-access-key admin --s3-ca-file ~/.pgcli/certs/store.crt

Step 3 references the .crt you minted, because on this host you already hold it. A remote host that never saw the file pulls it back with one handshake instead of an scp — fetch-ca recognizes the self-signed leaf MinIO serves and saves it as the anchor:

# on another host, instead of copying ~/.pgcli/certs/store.crt over:
pg backup fetch-ca <host-ip>:9000
#   [OK] CA fetched from <host-ip>:9000
#        saved:    <base-dir>/backup/repo-ca/ca-<host-ip>-9000.crt
#        SHA-256:  d4df…81e2   (cross-check against the store host's sha256sum)
pg backup setup --s3-ca-file <base-dir>/backup/repo-ca/ca-<host-ip>-9000.crt

Related

• Silo — the same BYO TLS surface on Pigsty’s MinIO fork
• MinIO — the addon itself, and its bring-your-own certificate and generating a test certificate sections
• Example: HA + self-CA MinIO — the whole chain, run end to end in an isolated environment
• Cluster Backup — the S3 repository and WAL archiving that consume this certificate
• Patroni HA — the pg ha command set

7.10 - S3 Storage High Availability

A Patroni cluster survives a node loss; the S3 repository its WAL and backups stream into must survive one too, or “high availability” quietly ends at the backup path. The store is a MinIO or silo addon (the two are interchangeable — silo is Pigsty’s MinIO fork with the same feature surface). Two fault domains matter, and they are orthogonal:

This page records the shapes pgcli supports for combining the two, and how to pick among them.

The deployment modes

MinIO classifies its layouts by node count and drive count per node (SNSD / SNMD / MNSD / MNMD). pgcli exposes all four:

SNMD is native: pg addon install minio --drive /mnt/disk1 --drive ... (one flag per drive) starts one MinIO process that erasure-codes across the drives. What it buys, measured on a live 4-drive set: 2 parity shards by default, so it tolerates 2 drive failures; with 1 drive down reads and writes continue, with 2 down reads still succeed but writes are refused (the quorum boundary); usable capacity is about half the raw total; a drive that returns is healed by MinIO itself. The same --drive works on silo.

MNMD is native too: keep --drive for this node’s drives and add the whole cluster’s host×drive endpoint matrix with --endpoint — one URL per drive on every node, each naming that node’s /data1../dataN slot. Measured on a live 4-node × 4-drive set (both minio and silo): 16 drives online report EC:4 in a single erasure set of stripe size 16; losing one whole node (12/16) keeps reads and writes working; losing a second node (8/16) refuses writes and fails reads, and the set self-heals to 16/16 on restart. The full walkthrough is Addons → MinIO → Multi-Node Multi-Drive (MNMD).

SNMD vs ZFS: two ways to survive a disk

Both protect a single host’s data against disk loss; they differ in where the redundancy lives and what that costs.

The honest tradeoff: native multi-drive is simpler — one command, no filesystem to provision, and it gets you disk redundancy with zero ZFS setup. ZFS is more flexible — the layout stays changeable underneath, it saves more capacity on the same disks (raidz1 keeps 3 of 4 usable where SNMD’s EC:2 keeps 2 of 4), and it tolerates nodes that are not identical. So:

• single host, want disk redundancy without touching ZFS → SNMD
• single host, want the most usable capacity / a layout you can change later → SNSD + ZFS
• distributed cluster, disk and node failure → both paths are first-class now: native MNMD, or MNSD + per-node ZFS — see “HA with 4 hosts, each with several disks” below

Neither is wrong on a single box — SNMD’s 50% capacity is the price of not managing a pool, and ZFS’s flexibility is the price of provisioning one. The rest of this page documents the ZFS path and the MNMD alternative for multi-host sets.

ZFS: the flexible disk layer

The recipe is identical under SNSD and MNSD: build a zpool from the host’s data disks, create one dataset for the store, and point --data-dir at its mount point. MinIO sees “one big reliable drive”; it never learns how many physical disks are under it.

# one pool from the host's spare disks (mount point defaults to /minio-pool)
sudo zpool create minio-pool raidz1 /dev/sdb /dev/sdc /dev/sdd

# one dataset for the store: 1M recordsize suits object blobs, no atime churn
sudo zfs create -o recordsize=1M -o atime=off minio-pool/store

pg addon install minio --name store --data-dir /minio-pool/store   # or: silo

The pool must sit on separate devices from the root filesystem — which is also what MinIO’s drive check and pgcli’s install-time advisory want (drive is part of root drive, will not be used). A ZFS mount on its own disks satisfies that trivially.

Layout by disk count

The two columns differ in one line of reasoning: under SNSD ZFS must survive the disk and whatever happens during its rebuild, so parity depth buys safety outright; under MNSD, ZFS only has to keep a disk failure from escalating into a node loss, so one parity plus quick local resilver is the job — and beyond that, prefer more nodes over deeper local RAID.

For a 4-disk host under SNSD — the most common single-box question — the usual choices are:

Under SNSD ZFS is the store’s only line of defense, so the default leans conservative: raidz2 unless the disks are fast enough to rebuild quickly and the capacity is worth the thinner margin.

MinIO’s own guidance to avoid RAID underneath its EC mode targets the double-redundancy of RAID + cross-node EC. Under SNSD there is no EC — ZFS is the only protection the data has — so the guidance does not apply, and following it literally (“single drive, no ZFS”) on a 4-disk host would mean losing the whole store to one dead disk.

Co-locating with PostgreSQL: ZFS caches aggressively (ARC, by default up to half of RAM). On a host that also runs the database, cap it — e.g. options zfs:zfs_arc_max=8589934592 in /etc/modprobe.d/zfs.conf — so the two workloads do not fight over memory.

HA with 4 hosts, each with several disks

This is the hybrid the layout table above points at, and there are two first-class ways to build it: native MNMD — MinIO erasure-codes across every disk of every node — or MNSD + a ZFS pool under each node’s data directory — MinIO sees one drive per node and the pools absorb disk loss. They fail differently, so the choice is real either way.

Native MNMD

One matrix, no filesystem layer: each node passes its own drives with --drive and every node carries the identical host×drive endpoint list. For TLS, generate one shared leaf and install the same pair on every node (see the TLS note after the measurements below).

# once, anywhere — one self-signed leaf whose SANs cover every node address:
pg cert --host 10.0.0.11,10.0.0.12,10.0.0.20,10.0.0.21 \
        --cert-file grid.crt --key-file grid.key
# copy grid.crt + grid.key to every node — byte-identical files everywhere

# node 1 (10.0.0.11) — four data disks mounted; nodes 2–4: same command, the
# SAME grid.crt/grid.key, own --drive paths, SAME 16-endpoint matrix, SAME
# --root-password:
pg addon install minio --name store \
  --tls-cert grid.crt --tls-key grid.key \
  --listen 0.0.0.0 \
  --drive /mnt/minio/disk1 --drive /mnt/minio/disk2 \
  --drive /mnt/minio/disk3 --drive /mnt/minio/disk4 \
  --root-password '<shared-secret>' \
  --endpoint https://10.0.0.11:9000/data1 --endpoint https://10.0.0.11:9000/data2 \
  --endpoint https://10.0.0.11:9000/data3 --endpoint https://10.0.0.11:9000/data4 \
  --endpoint https://10.0.0.12:9000/data1 --endpoint https://10.0.0.12:9000/data2 \
  --endpoint https://10.0.0.12:9000/data3 --endpoint https://10.0.0.12:9000/data4 \
  --endpoint https://10.0.0.20:9000/data1 --endpoint https://10.0.0.20:9000/data2 \
  --endpoint https://10.0.0.20:9000/data3 --endpoint https://10.0.0.20:9000/data4 \
  --endpoint https://10.0.0.21:9000/data1 --endpoint https://10.0.0.21:9000/data2 \
  --endpoint https://10.0.0.21:9000/data3 --endpoint https://10.0.0.21:9000/data4

Measured on a live 4-node × 4-drive set (the same on minio and silo): the 16-drive set reports EC:4 in one erasure set of stripe size 16 — losing 4 drives of 16 stays healthy.

Usable capacity follows the parity ratio: EC:4 over 16 drives keeps 12/16 of raw bytes. The costs: the layout is fixed at install (the matrix is the EC set), every node must contribute the same number of drives, and pgcli cannot detect a matrix typo on another node — keeping the N pg.yaml files identical is the operator’s job.

For TLS across the grid, serve one shared certificate: mint a single self- signed leaf with pg cert --host <every node address, comma-separated> and install it on every node with --tls-cert/--tls-key (the same two files byte-identical everywhere, the endpoint matrix all https://). The grid then forms with no per-node CA to reconcile — verified on a live 4-node set: the ring came up Network: 4/4 OK with an empty CAs/ dir, because that shared leaf is its own trust anchor and every node already holds it. The fallback is the generated mode: if you let each node run a bare --tls, every host mints its own CA and the first cross-node handshake dies with x509: certificate signed by unknown authority until you seed one shared CA into every node’s cert dir by hand — which the shared-pg cert path avoids entirely.

MNSD + per-node ZFS

MNSD across the hosts, ZFS under each node’s data directory. Each node exposes exactly one endpoint (its ZFS-backed /data); disk failures are healed by the local pool and never reach MinIO; node failures are absorbed by EC quorum.

# on EACH of the 4 nodes: build the local pool (layout per node — see below)
sudo zpool create minio-pool raidz1 /dev/sdb /dev/sdc /dev/sdd
sudo zfs create -o recordsize=1M -o atime=off minio-pool/store

# node 1 (10.0.0.11):
pg addon install minio --name store \
  --listen 10.0.0.11 \
  --data-dir /minio-pool/store \
  --root-password '<shared-secret>' --tls \
  --endpoint http://10.0.0.11:9000/data \
  --endpoint http://10.0.0.12:9000/data \
  --endpoint http://10.0.0.20:9000/data \
  --endpoint http://10.0.0.21:9000/data

# nodes 2–4: same command, own --listen, identical endpoint list and password

What each layer then tolerates on a 4-node cluster:

Two properties make the scheme flexible:

• Endpoint count = node count, not disk count. A node with 2 disks and a node with 6 disks look identical to MinIO. The four hosts may run different layouts — raidz1 ×4 disks here, 2-way mirror there, raidz2 ×6 over there — heterogeneity costs nothing.
• Pool capacities should be roughly aligned. EC sets the usable capacity of the whole cluster to the weakest member’s free space (3 × 12T pools + 1 × 4T pool → you get 4T × striping, not 40T). If the disks genuinely differ, thin provisioning (zvol-based sparse datasets, or simply zfs set refquota) hides the mismatch from MinIO — the quota caps the big pools at the small one’s size, and nothing wastes a rebuild.

Why not stripe the per-node pool to reclaim capacity. MinIO’s “no RAID under EC” advice is about capacity, and the arithmetic is real: a plain 4-node MNSD EC set already halves the raw total, so an unmirrored raidz_none pool on each node (all disks striped, zero local redundancy) does squeeze out roughly a third more cluster capacity than raidz1 would. But EC counts a failure in nodes, and a striped pool turns a single dead disk into a whole offline node — one ordinary disk failure burns a slot of the budget EC set aside for losing an entire machine, forcing a network-wide rebuild of that node’s whole pool and leaving zero margin until it finishes. The capacity is only “free” because you quietly downgraded disk fault-tolerance to node fault-tolerance. raidz1 per node is the point where the two layers stop stealing from each other: the pool absorbs disk loss invisibly, MinIO’s EC budget stays reserved for node loss. (If the reclaim-everything answer truly fits, the shape that maximizes it is plain MNSD on one big disk per node — no ZFS at all — not a striped pool that hides the same single-point risk one layer down.)

Which of the two

Pick MNMD when the nodes are identical, the disk plan is settled, and you want disk and node redundancy from MinIO alone with nothing to provision underneath. Pick MNSD + ZFS when nodes differ in disk count or size, the layout may change later, or you want to reason about failures in whole nodes instead of in drives.

Choosing a shape

The store’s TLS story is independent of the topology: whichever shape you pick, --tls (or pg cert-minted BYO certificates) works the same — see Generating a Certificate.

Related

• Addons → MinIO — install, --tls, distributed mode constraints (the hard rules this page builds on), and the MNMD walkthrough
• Addons → Silo — the drop-in fork; identical modes
• Cluster Backup — how the repo is wired into Patroni (pg backup setup)
• Example: HA Cluster with a Self-CA MinIO — an end-to-end verified walkthrough of the single-node variant

8 - Failover: Replica Promotion

Promote a replica to become the new primary when the current primary fails. pgcli provides a 3-step manual failover workflow — each step runs on its respective host, no auto-detection of same-host vs cross-host topology.

Overview

                    ┌─────────────┐
                    │   primary   │  ← crashes / becomes unavailable
                    │  (pg01)     │
                    └──────┬──────┘
                           │ WAL streaming
               ┌───────────┼───────────┐
               ▼           ▼           ▼
          ┌─────────┐ ┌─────────┐ ┌─────────┐
          │  ro1    │ │  ro2    │ │  ro3    │
          │ replica │ │ replica │ │ replica │
          └─────────┘ └─────────┘ └─────────┘

After failover (promote ro1 → new primary):

                    ┌─────────────┐
                    │   ro1       │  ← new primary (promoted)
                    │  (primary)  │
                    └──────┬──────┘
                           │ WAL streaming
               ┌───────────┼───────────┐
               ▼           ▼           ▼
          ┌─────────┐ ┌─────────┐ ┌─────────┐
          │  ro2    │ │  ro3    │ │  pg01   │
          │ replica │ │ replica │ │ replica │  ← demoted old primary
          └─────────┘ └─────────┘ └─────────┘

3-Step Failover

Each step is an independent command. Run them in order, on their respective hosts.

# Step 1: On the replica being promoted
pg replica promote ro1

# Step 2: On the old primary host (when it recovers)
pg replica drop ro1 -i pg01

# Step 3: On each remaining replica host
pg replica repoint ro2 --primary-dsn "postgres://admin:<pw>@<new-primary-ip>:<port>/<db>" --primary-name ro1
pg replica repoint ro3 --primary-dsn "postgres://admin:<pw>@<new-primary-ip>:<port>/<db>" --primary-name ro1

Step 1: pg replica promote <name>

Run on the host of the replica being promoted to primary.

pg replica promote ro1

What happens:

1. Validates the instance is a replica (ReplicaOf is set) and the container is running
2. Calls pg_promote() (PostgreSQL 12+ native promotion — no container restart)
3. Waits for recovery to end (usually sub-second)
4. Cleans up primary_conninfo from postgresql.auto.conf via ALTER SYSTEM RESET
5. Updates config: clears ReplicaOf and PrimaryDSN, enables PITR
6. Automatically initializes PITR:
   • pgBackRest stanza creation
   • archive_mode / archive_command configuration
   • PostgreSQL restart to apply postmaster-level parameters
7. Prints next-step instructions

Idempotent: If the replica is already promoted (e.g. from a manual pg_ctl promote), the command skips to config update.

Step 2: pg replica drop <name> -i <old-primary>

Run on the old primary host to clean up the replication slot. This step is only needed in specific scenarios.

pg replica drop ro1 -i pg01

This drops the physical replication slot pgcli_r_ro1 on the old primary. Without cleanup, the slot would hold WAL indefinitely until the primary runs out of disk space.

When to run:

Keep the old primary as-is? If you want to preserve the old primary with its original data (e.g. for forensic analysis or as a read-only archive), you can simply leave it alone — do not run drop or repoint on it. The old primary keeps running as an independent instance with stale data. Just be aware that the replication slot for the promoted replica still exists and will accumulate WAL; you may want to drop just that specific slot (pg replica drop ro1 -i pg01) while leaving everything else untouched.

Step 3: pg replica repoint <name> --primary-dsn <dsn> --primary-name <name>

Run on each remaining replica host to re-point it to the new primary.

pg replica repoint ro2 \
  --primary-dsn "postgres://admin:<password>@10.0.0.11:35439/pg01_db" \
  --primary-name ro1

What happens:

1. Queries the new primary’s extensions via DSN (pg_extension catalog)
2. If non-builtin extensions exist (e.g. pg_cron, timescaledb), builds a local -ext image with matching packages
3. Stops the old replica container and destroys its data directory
4. Creates a replication slot on the new primary via DSN
5. Updates config: ReplicaOf, PrimaryDSN, ImageTag, Extensions, disables PITR
6. Re-initializes via pg_basebackup -R from the new primary
7. Starts the replica container in standby mode

Why destroy + rebuild instead of ALTER SYSTEM SET?

After promotion, the new primary advances to a new timeline. Other replicas on the old timeline cannot simply change primary_conninfo — PostgreSQL rejects the connection with:

FATAL: requested starting point on timeline 1 is not in this server's history

The only safe approach is a full pg_basebackup from the new primary.

Getting the Primary DSN

Get the new primary’s connection string from pg status on the promoted replica’s host:

pg status -i ro1
# Connection: postgres://admin:<password>@127.0.0.1:35439/pg01_db

Replace 127.0.0.1 with the new primary host’s IP reachable from the replica host (e.g. 10.0.0.11).

Demoting the Old Primary

When the old primary recovers, you can rejoin it as a replica of the new primary using the same repoint command:

# On the old primary host
pg replica repoint pg01 \
  --primary-dsn "postgres://admin:<password>@10.0.0.11:35439/pg01_db" \
  --primary-name ro1