Tutorials

• 1: Troubleshooting
• 2: Manual PITR Drill
• 3: Enabling HugePage for PostgreSQL
• 4: Clone and Side-Restore a PostgreSQL Instance
• 5: Accidental Deletion
• 6: HA Drill: Handling 2-of-3 Node Failure
• 7: Bind a L2 VIP to PostgreSQL Primary with VIP-Manager
• 8: Deploy HA Citus Cluster

1 - Troubleshooting

This document lists possible PostgreSQL and Pigsty failures. It gives standard procedures to locate, resolve, and analyze them.

Disk Space Exhausted

Disk space exhaustion is the most common type of failure.

Symptoms

If the database disk is full, PostgreSQL cannot operate normally. Logs repeatedly report “no space left on device”, and new writes fail. PostgreSQL may also report a PANIC and shut down.

The Pigsty NodeFsSpaceFull alert triggers when filesystem free space is less than 10%. To locate the problem, open the NODE Instance dashboard and inspect the FS metrics panel.

Diagnosis

You can also log in to the database node and run df -h. Identify the full partition. Then check these directories and their sizes to identify which files consume the space:

• Data directory (/pg/data/base): Stores data files for tables and indexes; pay attention to heavy writes and temporary files
• WAL directory (e.g., pg/data/pg_wal): Stores PG WAL; WAL accumulation/replication slot retention is a common cause of disk exhaustion.
• Database log directory (e.g., pg/log): If PG logs are not rotated in time and large amounts of errors are written, they may also consume significant space.
• Local backup directory (e.g., data/backups): When using pgBackRest or similar tools to save backups locally, this may also fill up the disk.

If the issue occurs on the Pigsty admin node or monitoring node, also consider:

• Monitoring data: VictoriaMetrics time-series metrics and VictoriaLogs log storage both consume disk space; check retention policies.
• Object storage data: Pigsty’s integrated Silo object storage may be used for PG backup storage.

After you identify the largest directory, use du -sh <directory> to locate large files or subdirectories.

Resolution

Disk exhaustion requires immediate action to free space and keep the database operating. If the data disk and system disk are the same, a full disk may prevent shell commands from running. In this case, delete the /pg/dummy placeholder file to free emergency space for shell commands. If accumulated pg_wal caused a database crash, free space and restart the database service. Then check data integrity carefully.

Transaction ID Wraparound

PostgreSQL reuses 32-bit transaction IDs (XIDs) in a cycle. Exhaustion causes a transaction ID wraparound failure (XID Wraparound).

Symptoms

The first typical sign is age saturation entering the warning zone in PGSQL Persist - Age Usage. Database logs begin to show messages such as WARNING: database "postgres" must be vacuumed within xxxxxxxx transactions.

If the condition gets worse, PostgreSQL enters protection mode. With about 1 million transaction IDs remaining, the database becomes read-only. At the limit of about 2.1 billion (2^31), it rejects new transactions and shuts down to prevent data corruption.

Diagnosis

PostgreSQL and Pigsty enable automatic garbage collection (AutoVacuum) by default. Thus, this failure usually indicates another underlying problem. Common causes include very long transactions (SAGE), incorrect Autovacuum settings, blocked replication slots, and insufficient resources. Storage engine or extension bugs and bad disk blocks can also cause it.

First, identify the database with the highest age. Then inspect table ages in the Pigsty PGCAT Database - Tables panel. Also inspect database error logs for evidence of the cause.

Resolution

1. Immediately freeze old transactions: If the database is not yet in read-only protection mode, immediately run a manual VACUUM FREEZE. For faster results, first freeze the oldest tables individually. Connect as a superuser and run VACUUM FREEZE table_name; on tables identified with the largest relfrozenxid. Prioritize tables with the highest XID age. This can quickly reclaim transaction ID space.
2. Single-user mode rescue: If the database rejects writes or has shut down for protection, start it in single-user mode. Run VACUUM FREEZE database_name; to freeze and clean the entire database. Then restart in multi-user mode. This can remove wraparound protection and permit writes again. Operate carefully in single-user mode. Make sure that enough transaction IDs remain to complete the freeze.
3. Standby node takeover: If problems such as hardware failure prevent vacuum, consider promoting a read-only standby to primary. This can provide a cleaner environment for the freeze. For example, if bad blocks prevent vacuum on the primary, manually fail over to the standby and run emergency vacuum freeze. Verify that the new primary has frozen old transactions before you switch the load back.

Connection Exhaustion

max_connections limits PostgreSQL connections. When clients exceed this limit, PostgreSQL rejects new connections. Applications cannot connect and report FATAL: remaining connection slots are reserved for non-replication superuser connections or too many clients already. Regular connection slots are exhausted. Only slots reserved for superusers or replication remain.

Diagnosis

Connection exhaustion usually results from many concurrent client requests. Inspect active sessions in PGCAT Instance, PGCAT Database, or PGCAT Locks. Identify the query types that occupy the connections. Look for many “Idle in Transaction” connections, long transactions, and slow queries.

Resolution

Kill queries: If connection exhaustion blocks application operations, usually use pg_terminate_backend(pid) for immediate relief. If connections use a pool, adjust its size settings and reload it to reduce database connections.

You can also increase max_connections. Restart the database to apply the change.

etcd Quota Exhausted

An exhausted etcd quota will cause the PG high availability control plane to fail and prevent configuration changes.

Diagnosis

Pigsty uses etcd as the distributed configuration store (DCS) for high availability. etcd has a storage quota of about 2GB by default. At the limit, etcd rejects writes and reports “etcdserver: mvcc: database space exceeded”. Patroni then cannot write heartbeats or configuration changes to etcd, so cluster management fails.

Resolution

Pigsty v2.0.0 through v2.5.1 are affected by default. Pigsty v2.6.0 added automatic compaction for deployed etcd. If etcd stores only PostgreSQL high-availability leases, the problem no longer occurs in regular use.

Defective Storage Engine

TimescaleDB’s experimental Hypercore storage engine has confirmed defects. Reported cases include VACUUM failing to reclaim space and causing XID wraparound. If you use Hypercore, promptly migrate to native PostgreSQL tables or the default TimescaleDB engine.

Detailed introduction: PG New Storage Engine Failure Case (Chinese)

2 - Manual PITR Drill

This tutorial performs a PostgreSQL PITR drill in Pigsty v4.5.0’s four-node sandbox. It runs pgsql-pitr.yml in the sequence down → pitr → up. Separate validation steps precede data overwrite, timeline promotion, and HA reconstruction.

For one current node, use pig pitr. For direct pgBackRest control, see the low-level pg-pitr utility.

Prepare an Isolated Sandbox

Use Vagrant or another disposable four-node lab and select the ha/full template, which includes a Silo backup repository:

curl -fsSL https://repo.pigsty.io/get | bash
cd ~/pigsty
./configure -c ha/full
./deploy.yml

ha/full defines a single-node pg-meta, a three-node pg-test, and a Silo/pgBackRest repository. This tutorial uses the exact target pg-meta. Before you copy that selector to another environment, resolve its inventory.

Initial deployment and backup both change sandbox state. Production environments require their own deployment and backup approval process.

Establish Recovery Evidence

Start with read-only topology, backup-chain, and WAL-range checks:

pig pg list pg-meta
sudo -iu postgres pig pb info
sudo -iu postgres pig pb check

info must show at least one usable backup with status: ok. Archived WAL must cover the intended target. check validates the current stanza and archive path. It does not replace a real restore drill or validation of an independent copy.

In the sandbox, run Pigsty’s heartbeat helper to create an easy-to-verify time series:

sudo -iu postgres /pg/bin/pg-heartbeat

Record, then stop the workload:

• the timezone-qualified timestamp you intend to recover to;
• heartbeat, LSN, and transaction boundaries around that point;
• current primary, timeline, and backup label;
• target cluster pg-meta and target node.

Inspecting real application tables requires separate authorization. This tutorial uses only sandbox heartbeat data.

Declare the Recovery Task

Declare the target under pg-meta.vars in the sandbox inventory:

pg_pitr:
  cluster: pg-meta
  time: "2026-08-13 10:00:00+08"
  action: pause
  archive: true
  backup: false

• cluster is the source backup stanza and defaults to the target pg_cluster.
• action: pause pauses PostgreSQL at the target for a human validation gate.
• archive: true preserves archive settings.
• backup: true is not a safe-backup substitute: it deletes an existing <pg_data>-backup before moving current PGDATA, so this drill keeps it false.

You can also pass the object temporarily with -e. Preflight and all three stages must use exactly the same valid JSON to prevent variable differences.

Full Preflight

Before any stop or write action, check the complete workflow against the same target:

./pgsql-pitr.yml -l pg-meta --check

Confirm that Ansible resolves exactly pg-meta, then review the output for:

• source stanza, recovery type, time, timeline, and action;
• destination pg_data, port, and repository;
• tablespace/link mappings;
• archive and backup behavior.

--check validates inventory, variables, and task selection. It cannot prove that a pgBackRest backup is restorable. If the target, backup, or variables change, repeat preflight.

Stage One: Stop

Only after the operator reconfirms exact target pg-meta, recovery point, and maintenance window, run:

./pgsql-pitr.yml -l pg-meta -t down

down attempts to pause Patroni automatic failover and stops Patroni on every target member. If PostgreSQL remains running, it uses immediate shutdown. Then verify every target node independently of the playbook result:

sudo systemctl is-active patroni
sudo -iu postgres pg_ctl -D /pg/data status

The expected results are inactive and “server is not running.” If any member remains active, stop and diagnose the problem. Do not start the restore.

Stage Two: Restore and Validate

Recheck pg_pitr and the target nodes before running the destructive stage:

./pgsql-pitr.yml -l pg-meta -t pitr

This stage:

1. renders /pg/conf/pitr.conf and /pg/bin/pg-restore;
2. optionally moves old PGDATA according to backup;
3. creates the destination and runs pgBackRest restore with --force and delta=y;
4. starts PostgreSQL directly and waits for a consistent recovery state in the log;
5. prints a pg_controldata summary.

Control data proves only that the directory contains readable control state. It does not verify the time, XID, or application boundary. With action: pause, confirm that WAL replay reached and paused near the target:

sudo -iu postgres psql -p 5432 -Atqc \
  'SELECT pg_is_in_recovery(), pg_is_wal_replay_paused(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp()'

Then inspect only the smallest authorized data scope. In the sandbox, check heartbeat rows. If the target is wrong:

1. keep every Patroni member stopped;
2. stop the manually started PostgreSQL;
3. adjust the target and rerun the complete --check;
4. rerun the pitr stage.

Do not run up or allow replicas from the old timeline to rejoin.

Promote and Stage Three: Rebuild HA

Promote only after the operator accepts the recovered result and the creation of a new timeline:

sudo -iu postgres pg_ctl -D /pg/data promote
sudo -iu postgres psql -p 5432 -Atqc 'SELECT pg_is_in_recovery()'

The expected result is f. Promotion changes state, and you cannot undo it without loss.

With every Patroni member still stopped and exact target still pg-meta, run:

./pgsql-pitr.yml -l pg-meta -t up

For the primary, up deletes the /pg/pg-meta/ prefix from etcd. The effective prefix also depends on pg_namespace and Citus settings. It stops the manually started PostgreSQL and starts Patroni on the primary. It then starts replicas one at a time and resumes HA. The etcd deletion task tolerates errors. Thus, a successful playbook result does not prove that it removed stale DCS state correctly.

Post-Recovery Acceptance

Verify each layer. Service startup alone does not complete recovery:

pig pg list pg-meta
sudo -iu postgres psql -Atqc \
  "SELECT pg_is_in_recovery(), pg_current_wal_lsn(), current_setting('archive_mode')"
sudo -iu postgres pig pb check

Also confirm:

• exactly the intended member is primary and replicas stream from the new timeline;
• HAProxy/VIP/DNS and application traffic point only to accepted instances;
• data and event boundaries around the recovery target are correct;
• archive_mode, archive_command, and new-WAL archiving work;
• monitoring, alerts, and the backup repository contain no stale cluster state.

After the new timeline is stable, create and verify a new full backup under the applicable approval process:

sudo -iu postgres pg-backup full
sudo -iu postgres pig pb info

If recovery used archive: false, it wrote archive-mode=off. Validate recovery and confirm a maintenance window before you reset that override and perform a controlled restart. The default archive: true does not require this step.

Multi-Node and Cross-Cluster Recovery

• Old-timeline replicas must not rejoin without validation. up starts replicas one at a time and waits for clone/recovery; monitor them to completion.
• In cross-stanza recovery, pg_pitr.cluster is the source while -l is the destination being overwritten. Record and state both separately.
• Cross-cluster recovery should normally use archive: false so a test destination cannot write WAL into the source stanza. Enable its own archiving only after acceptance and post-clone stanza cleanup.
• link_map, data, port, and a temporary repo change the actual storage and data targets; include all of them in both --check and human review.

Related

• Complete Restore Operations Reference
• PITR Concepts and Target Semantics
• Backup Chains and Recovery Window
• Clone and Low-Level pg-pitr
• pig pitr

3 - Enabling HugePage for PostgreSQL

Use node_hugepage_count and node_hugepage_ratio or /pg/bin/pg-tune-hugepage

To enable HugePages, consider node_hugepage_count and node_hugepage_ratio. Apply these settings with ./node.yml -t node_tune.

HugePages have advantages and disadvantages for databases. Exclusive memory management prevents reallocation and reduces database OOM risk. However, HugePages can reduce performance in some conditions.

Before PostgreSQL starts, allocate enough huge pages. The pg-tune-hugepage script can reclaim the unused portion. This script is available only for PostgreSQL 15+.

If PostgreSQL is already running, use this method to enable huge pages. It requires PG15+:

sync; echo 3 > /proc/sys/vm/drop_caches   # Flush disk, release system cache (be prepared for database perf impact)
sudo /pg/bin/pg-tune-hugepage             # Write nr_hugepages to /etc/sysctl.d/hugepage.conf
pg restart <cls>                          # Restart postgres to use hugepage

4 - Clone and Side-Restore a PostgreSQL Instance

Pigsty v4.5.0 provides two local shell utilities:

• pg-fork copies a PostgreSQL data directory and gives the copy a separate port.
• pg-pitr invokes pgBackRest to restore a stopped data directory to a selected target.

Use these utilities for sandbox drills, side-channel investigation, and temporary testing. They do not orchestrate complete Patroni-cluster recovery. Prefer pig pitr for a managed instance and staged pgsql-pitr.yml for a multi-node cluster.

pg-fork

pg-fork copies a PostgreSQL data directory on the current node. Run it as the database OS user, normally postgres, or at least as a member of the postgres group:

pg-fork 1                         # /pg/data -> /pg/data1, destination port 15432
pg-fork 2 -d /pg/data1            # /pg/data1 -> /pg/data2, destination port 25432
pg-fork 3 -D /srv/pg-clone -P 55432

Parameters

pg-fork <FORK_ID> [options]

The script rejects identical normalized source and destination paths. It cannot determine whether a custom destination contains other important data. If the destination directory exists, the script removes it recursively before the copy.

Hot Backup and Cold Copy

By default, the script connects to the source port and uses one psql session to run:

1. CHECKPOINT;
2. pg_backup_start();
3. rm -rf <destination> followed by cp -a --reflink=auto;
4. pg_backup_stop(wait_for_archive => false).

If the source is unreachable on the selected port, the script automatically falls back to a cold copy. It does not abort. -s also forces a cold copy. A cold copy is safe only after you independently confirm that the source instance has fully stopped. A postmaster.pid warning alone does not prove the process state.

On the same filesystem, the script recognizes XFS with reflink enabled, Btrfs, Bcachefs, and OCFS2 as fast CoW modes. Other filesystems or cross-filesystem destinations still use cp --reflink=auto, but may require a full copy. The help text describes ZFS support more broadly than the detector. The v4.5.0 implementation does not classify ZFS as a confirmed fast-CoW mode.

Clone Configuration

After a successful copy, pg-fork:

• removes postmaster.pid, postmaster.opts, and standby.signal from the destination;
• clears physical replication-slot files in the destination;
• writes a separate port, archive_mode=off, and a local log_directory to the destination’s postgresql.auto.conf;
• removes primary_conninfo, primary_slot_name, and old recovery_target* overrides.

The script does not check destination port availability or adjust memory settings. Before you start the copy, inspect at least these settings:

postgres -D /pg/data1 -C port
postgres -D /pg/data1 -C archive_mode
postgres -D /pg/data1 -C shared_buffers
pg_ctl -D /pg/data1 status

Interaction Boundary

The script asks Proceed with fork? [y/N] only if standard input is a terminal and you did not use -y. Pipes, CI, cron, and other non-interactive calls do not get this prompt. Automation must enforce a strict absolute-path allowlist and check whether the destination exists before it calls the script. Do not add -y by default only for convenience.

pg-pitr

pg-pitr is a low-level pgBackRest restore wrapper. It does not pause or start Patroni, stop or start PostgreSQL, clear DCS state, or rebuild replicas.

Recovery Targets

Understand and select a recovery target before execution. Without arguments, the command only shows help:

-S/--set, with compatibility alias -b/--backup, selects which backup set recovery starts from. It does not select a stop target. For example, -S 20251225-120000F -d still replays to the end of WAL. Use -S ... -i to stop when that backup first becomes consistent.

For time, name, lsn, xid, and immediate, pgBackRest pauses at the target by default. -P/--promote changes this action to automatic promotion. Use -X/--exclusive only with a precise target such as time, lsn, or xid.

Other Options

-c checks the generated command. It does not prove that the backup/WAL is usable or that PostgreSQL and Patroni have stopped. The wrapper does not filter conflicting native arguments. If you pass repository, tablespace, or link-mapping options, carefully review the final command.

Safe Execution Sequence

This example shows the low-level procedure for one isolated destination. For production cluster recovery, use the complete runbook:

# 1. Read-only verification of backup sets and the recovery window
pig pb info

# 2. Confirm the exact target is stopped; stop Patroni first for a managed instance
pg_ctl -D /pg/data1 status

# 3. Render and review the exact command without writing data
pg-pitr -D /pg/data1 -t "2026-08-13 10:00:00+08" -c

# 4. Remove -c only after the operator reconfirms destination, backup, and stopped state
pg-pitr -D /pg/data1 -t "2026-08-13 10:00:00+08"

Real execution rejects root and aborts if postmaster.pid exists in the destination. Before removing even a stale PID file, the operator must confirm that PostgreSQL has stopped. There is no y/N question. An interactive terminal gets only an interruptible five-second countdown. A non-interactive call skips the countdown and starts the restore immediately.

After restore, the operator starts and validates the instance:

pg_ctl -D /pg/data1 start
psql -p 15432 -Atqc \
  'SELECT pg_is_in_recovery(), pg_is_wal_replay_paused(), pg_last_xact_replay_timestamp()'

Promote only after you verify the recovery target, authorized application data, timeline, and archive settings. Promotion creates a new timeline and is not a reversible inspection operation. pg-pitr does not disable archiving itself. Before you follow its generic final “enable archive_mode” hint, inspect the effective setting. Correct only an override introduced by this recovery.

Additional Side-Restore Risks

For a custom destination such as /pg/data1, pgBackRest can restore postgresql.auto.conf and overwrite the separate port set by pg-fork. Before startup, recheck port, archive_mode, sockets, logging, and memory settings.

If the backup contains external tablespaces or links, a side restore can use the original paths. For isolation, pass reviewed pgBackRest --tablespace-map, --link-map, or related arguments after --. Inspect the generated command. Otherwise, do not start the restored copy on the production host.

Recommended Clone-Validation Flow

1. Verify the source instance, destination absolute path, destination port, tablespaces, and independent backup.
2. Run pg-fork <id> in an interactive terminal and confirm that the plan shows hot backup rather than an unintended cold-copy fallback.
3. Before starting the clone, run pg-pitr -D <clone> ... -c and inspect the recovery command.
4. Execute restore only after explicit destination confirmation, then recheck the clone’s port and every external path.
5. Start the clone on the isolated port and verify recovery state and only the application data you are authorized to inspect.
6. Promote only if the clone is intentionally becoming a new primary; otherwise stop it and clean up only the exact, verified path.

Side validation reduces direct writes to the current PGDATA. It still uses the same backup repository, consumes host resources, and may access external tablespaces. The sandbox therefore still has risks.

Related

• Manual PITR Drill
• Restore Operations and pgsql-pitr.yml
• pig pitr
• pig pb restore

5 - Accidental Deletion

Accidental Data Deletion

For a small accidental DELETE, consider pg_surgery or pg_dirtyread for selective recovery in place.

-- Immediately disable Auto Vacuum on this table and abort Auto Vacuum worker processes for this table
ALTER TABLE public.some_table SET (autovacuum_enabled = off, toast.autovacuum_enabled = off);

CREATE EXTENSION pg_dirtyread;
SELECT * FROM pg_dirtyread('tablename') AS t(col1 type1, col2 type2, ...);

If the deleted data has already been reclaimed by VACUUM, then use the general accidental deletion recovery process.

Accidental Object Deletion

For accidental DROP/DELETE operations, usually select a recovery method in this order:

1. Check whether the application or another data system can supply the data. If so, recover it from that system.
2. Check whether a delayed replica exists. If so, advance it to the time before deletion and query the data for recovery.
3. If deletion is confirmed, check the backups and their recovery window. If the window covers the deletion time, start PITR.
4. Select where to replay: an in-place cluster PITR rollback, a new server, or a replica. Then perform recovery.

Accidental Cluster Deletion

Pigsty management commands can delete an entire cluster, for example pgsql-rm.yml or bin/pgsql-rm. Unless pg_rm_backup is false, these commands also delete the cluster backups.

Warning: In this situation, you cannot recover the data. Review the target and the deletion consequences carefully before you proceed.

For production, we recommend that you set this parameter to false globally in the inventory. This preserves backups when you remove clusters.

6 - HA Drill: Handling 2-of-3 Node Failure

In a typical 3-node HA deployment, simultaneous failure of two nodes removes the majority. The system usually cannot complete automatic failover and needs manual intervention.

First, assess the two failed servers. If they can restart quickly, prioritize their recovery. Otherwise, use the Emergency Recovery Procedure.

The Emergency Recovery Procedure assumes that the administration node has failed and only one regular database node survives. In this case, the fastest recovery procedure is:

• Adjust HAProxy configuration to direct traffic to the primary.
• Stop Patroni and manually promote the PostgreSQL replica to primary.

Adjust HAProxy Configuration

If connections bypass HAProxy, skip this step. If connections use HAProxy, change its configuration to direct read/write traffic to the primary manually.

• Edit /etc/haproxy/conf.d/<pg_cluster>-primary.cfg, where <pg_cluster> is your PostgreSQL cluster name, e.g., pg-meta.
• Comment out the health check configuration options to stop health checks.
• Comment out the other two failed machines in the server list, keeping only the current primary server.

listen pg-meta-primary
    bind *:5433
    mode tcp
    maxconn 5000
    balance roundrobin

    # Comment out the following four health check lines
    #option httpchk                               # <---- remove this
    #option http-keep-alive                       # <---- remove this
    #http-check send meth OPTIONS uri /primary    # <---- remove this
    #http-check expect status 200                 # <---- remove this

    default-server inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100
    server pg-meta-1 10.10.10.10:6432 check port 8008 weight 100

    # Comment out the other two failed machines
    #server pg-meta-2 10.10.10.11:6432 check port 8008 weight 100 <---- comment this
    #server pg-meta-3 10.10.10.12:6432 check port 8008 weight 100 <---- comment this

After you change the configuration, wait until primary promotion before you run systemctl reload haproxy. The new configuration disables HAProxy primary health checks, which use Patroni by default. HAProxy then directs write traffic to the current primary.

Manually Promote Replica

Log in to the target server and switch to the dbsu user. Run CHECKPOINT to flush data to disk. Stop Patroni, restart PostgreSQL, then promote the replica:

sudo su - postgres                     # Switch to database dbsu user
psql -c 'checkpoint; checkpoint;'      # Two Checkpoints to flush dirty pages, avoid long PG restart
sudo systemctl stop patroni            # Stop Patroni
pg-restart                             # Restart PostgreSQL
pg-promote                             # Promote PostgreSQL replica to primary
psql -c 'SELECT pg_is_in_recovery();'  # If result is f, it has been promoted to primary

If you changed the HAProxy configuration, now run systemctl reload haproxy. This applies the configuration and directs traffic to the new primary.

systemctl reload haproxy                # Reload HAProxy configuration to direct write traffic to current instance

Avoid Split Brain

After emergency recovery, the next priority is to Avoid Split Brain. Prevent the other two servers from returning online and becoming competing primaries. Such a split brain can cause data inconsistency.

Simple approaches:

• Power off/disconnect network the other two servers to ensure they don’t come online uncontrollably.
• Adjust the database connection string used by applications to point directly to the surviving server’s primary.

Then decide the next steps based on the specific situation:

• A: The two servers have temporary failures (e.g., network/power outage) and can be repaired in place to continue service.
• B: The two failed servers have permanent failures (e.g., hardware damage) and will be removed and decommissioned.

Recovery After Temporary Failure

If the other two servers have temporary failures and can return to service, use these repair and rebuild steps:

• Handle one failed server at a time, prioritize the admin node / INFRA node.
• Start the failed server and stop Patroni after startup.

When ETCD regains quorum, it resumes operation. Start Patroni on the surviving server, which is the current primary. Patroni takes control of the existing PostgreSQL instance and regains cluster leadership. Then enter maintenance mode:

sudo systemctl restart patroni
pg pause <pg_cluster>

On each of the other two instances, use the postgres account to run touch /pg/data/standby.signal. The marker identifies the instance as a replica. Then start Patroni:

sudo -iu postgres touch /pg/data/standby.signal
sudo systemctl restart patroni

After confirming Patroni cluster identity/roles are correct, exit maintenance mode:

pg resume <pg_cluster>

Recovery After Permanent Failure

After permanent failure, first recover ~/pigsty on the administration node. The essential files are pigsty.yml and files/pki/ca/ca.key.

If you cannot retrieve these two files or their backups, deploy a new Pigsty installation. Migrate the existing cluster through Backup Cluster.

Back up the pigsty directory regularly, for example with Git version control.

Configuration Repair

You can use the surviving node as the new administration node. Copy ~/pigsty to it, then adjust the configuration. For example, replace the original administration node 10.10.10.10 with the surviving node 10.10.10.12:

all:
  vars:
    admin_ip: 10.10.10.12               # Use new admin node address
    node_etc_hosts: [10.10.10.12 h.pigsty a.pigsty p.pigsty g.pigsty sss.pigsty]
    infra_portal: {}                    # Also modify other configs referencing old admin IP (10.10.10.10)

  children:

    infra:                              # Adjust Infra cluster
      hosts:
        # 10.10.10.10: { infra_seq: 1 } # Old Infra node
        10.10.10.12: { infra_seq: 3 }   # New Infra node

    etcd:                               # Adjust ETCD cluster
      hosts:
        #10.10.10.10: { etcd_seq: 1 }   # Comment out this failed node
        #10.10.10.11: { etcd_seq: 2 }   # Comment out this failed node
        10.10.10.12: { etcd_seq: 3 }    # Keep surviving node
      vars:
        etcd_cluster: etcd

    pg-meta:                            # Adjust PGSQL cluster configuration
      hosts:
        #10.10.10.10: { pg_seq: 1, pg_role: primary }
        #10.10.10.11: { pg_seq: 2, pg_role: replica }
        #10.10.10.12: { pg_seq: 3, pg_role: replica , pg_offline_query: true }
        10.10.10.12: { pg_seq: 3, pg_role: primary , pg_offline_query: true }
      vars:
        pg_cluster: pg-meta

ETCD Repair

Then execute the following command to reset ETCD to a single-node cluster:

./etcd.yml -e etcd_safeguard=false -e etcd_clean=true

Follow the instructions in ETCD Reload Configuration to adjust ETCD Endpoint references.

INFRA Repair

If the surviving node does not have the INFRA module, configure and install it on that node. Run this command:

./infra.yml -l 10.10.10.12

Repair monitoring on the current node:

./node.yml -t monitor

PGSQL Repair

./pgsql.yml -t pg_conf                            # Regenerate PG configuration files
systemctl reload patroni                          # Reload Patroni configuration on surviving node

After you repair each module, use the standard expansion procedure to add nodes and restore cluster high availability.

7 - Bind a L2 VIP to PostgreSQL Primary with VIP-Manager

If all cluster nodes share one L2 network, you can optionally define an L2 VIP for the PostgreSQL cluster.

The VIP uses Master-Backup mode. It always points to the node that runs the cluster primary instance.

VIP-Manager manages the VIP. It reads the Leader Key that Patroni writes to DCS (etcd) to determine whether the local node is primary.

Enable VIP

To enable VIP for a cluster, set pg_vip_enabled to true at cluster level. You can also enable it globally.

# pgsql 3 node ha cluster: pg-test
pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }   # primary instance, leader of cluster
    10.10.10.12: { pg_seq: 2, pg_role: replica }   # replica instance, follower of leader
    10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # replica with offline access
  vars:
    pg_cluster: pg-test           # define pgsql cluster name
    pg_users:  [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
    pg_databases: [{ name: test }]

    # Enable L2 VIP
    pg_vip_enabled: true
    pg_vip_address: 10.10.10.3/24
    #pg_vip_interface: auto

pg_vip_address must be an available, valid IP address with a subnet in the current L2 network.

pg_vip_interface defaults to auto. With this setting, Pigsty detects each instance interface from its inventory IPv4 address.

If automatic detection is unsuitable for a non-standard or policy-routing environment, specify a valid interface name for each instance. For example:

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary , pg_vip_interface: eth0  }
    10.10.10.12: { pg_seq: 2, pg_role: replica , pg_vip_interface: eth1  }
    10.10.10.13: { pg_seq: 3, pg_role: replica , pg_vip_interface: ens33 }
  vars:
    pg_cluster: pg-test           # define pgsql cluster name
    pg_users:  [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
    pg_databases: [{ name: test }]

    # Enable L2 VIP
    pg_vip_enabled: true
    pg_vip_address: 10.10.10.3/24

To refresh the VIP configuration and restart the VIP-Manager, use the following command:

./pgsql.yml -t pg_vip

8 - Deploy HA Citus Cluster

Citus is a PostgreSQL extension that turns PostgreSQL into a distributed database. It scales horizontally across nodes to process large data volumes and query workloads.

Patroni v3.0+ provides native high-availability support for Citus. This simplifies Citus cluster setup. Pigsty also provides native support.

• What is Citus
• Patroni Citus Support

Note: Citus 13.x supports PostgreSQL 18, 17, 16, 15, and 14. Pigsty extension repo provides Citus ARM64 packages.

Citus Cluster

Pigsty natively supports Citus. The current complete configuration template is conf/ha/citus.yml.

The following four-node example shows the key parameters. It has a two-node coordinator cluster, pg-citus0, and two single-node Worker clusters, pg-citus1 and pg-citus2. It is not an exact excerpt from the current complete template.

pg-citus:
  hosts:
    10.10.10.10: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.2/24 ,pg_seq: 1, pg_role: primary }
    10.10.10.11: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.2/24 ,pg_seq: 2, pg_role: replica }
    10.10.10.12: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.3/24 ,pg_seq: 1, pg_role: primary }
    10.10.10.13: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.4/24 ,pg_seq: 1, pg_role: primary }
  vars:
    pg_mode: citus                            # pgsql cluster mode: citus
    pg_version: 18                            # citus 13.x supports PG 14-18
    pg_shard: pg-citus                        # citus shard name: pg-citus
    pg_primary_db: citus                      # primary database used by citus
    pg_vip_enabled: true                      # enable vip for citus cluster
    pg_vip_interface: auto                    # auto detect vip interface for every member
    pg_dbsu_password: DBUser.Postgres         # all dbsu password access for citus cluster
    pg_extensions: [ citus, postgis, pgvector, topn, pg_cron, hll ]  # install these extensions
    pg_libs: 'citus, pg_cron, pg_stat_statements' # explicitly preload citus and keep it first
    pg_users: [{ name: dbuser_citus ,password: DBUser.Citus ,pgbouncer: true ,roles: [ dbrole_admin ]    }]
    pg_databases: [{ name: citus ,owner: dbuser_citus ,extensions: [ citus, vector, topn, pg_cron, hll ] }]
    pg_parameters:
      cron.database_name: citus
      citus.node_conninfo: 'sslrootcert=/pg/cert/ca.crt sslmode=verify-full'
    pg_hba_rules:
      - { user: 'all' ,db: all  ,addr: 127.0.0.1/32  ,auth: ssl   ,title: 'all user ssl access from localhost' }
      - { user: 'all' ,db: all  ,addr: intra         ,auth: ssl   ,title: 'all user ssl access from intranet'  }

Citus has additional configuration requirements beyond those for a standard PostgreSQL cluster. First, download, install, load, and enable the Citus extension. Use these four parameters:

• repo_packages: Must include the citus extension, or you need to use a PostgreSQL offline package that includes Citus.
• pg_extensions: Must include the citus extension, i.e., you must install the citus extension on each node.
• pg_libs: Must explicitly include citus in the first position; current Patroni templates use this parameter directly for shared_preload_libraries.
• pg_databases: Define a primary database that must have the citus extension installed.

Then configure the Citus cluster:

• pg_mode: Must be set to citus to tell Patroni to use Citus mode.
• pg_primary_db: Must specify the name of the primary database with citus extension, named citus here.
• pg_shard: Must specify a unified name as the cluster name prefix for all horizontal shard PG clusters, pg-citus here.
• pg_group: Set an integer shard number, starting from zero. 0 identifies the coordinator cluster. Other values identify Worker clusters.
• pg_cluster: Must be unique among physical PostgreSQL clusters. The usual name combines pg_shard with a sequence number. The current role does not require the name to equal the concatenation of pg_shard and pg_group.
• pg_dbsu_password: Must be set to a non-empty plaintext password, otherwise Citus will not work properly.
• pg_parameters: Recommended to set citus.node_conninfo to enforce SSL access and require node-to-node client certificate verification.

After configuration, you can deploy the Citus cluster using pgsql.yml just like a regular PostgreSQL cluster.

Manage Citus Cluster

After defining the Citus cluster, deploy it using the pgsql.yml playbook:

./pgsql.yml -l pg-citus    # Deploy Citus cluster pg-citus

On any member, use the DBSU (postgres) account to show Citus cluster status with patronictl, whose alias is pg:

$ pg list
+ Citus cluster: pg-citus ----------+---------+-----------+----+-----------+--------------------+
| Group | Member      | Host        | Role    | State     | TL | Lag in MB | Tags               |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     0 | pg-citus0-1 | 10.10.10.10 | Leader  | running   |  1 |           | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 20C.40G.125G |
|       |             |             |         |           |    |           | version: '16'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     1 | pg-citus1-1 | 10.10.10.11 | Leader  | running   |  1 |           | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 10C.20G.125G |
|       |             |             |         |           |    |           | version: '16'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     2 | pg-citus2-1 | 10.10.10.12 | Leader  | running   |  1 |           | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 10C.20G.125G |
|       |             |             |         |           |    |           | version: '16'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     2 | pg-citus2-2 | 10.10.10.13 | Replica | streaming |  1 |         0 | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 10C.20G.125G |
|       |             |             |         |           |    |           | version: '16'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+

Manage each horizontal shard as an independent PGSQL cluster with pg (patronictl). For Citus, specify the shard number with --group:

pg list pg-citus --group 0   # Use --group 0 to specify cluster shard number

The Citus system table pg_dist_node records cluster node information. Patroni maintains this table automatically.

PGURL=postgres://postgres:[email protected]/citus

psql $PGURL -c 'SELECT * FROM pg_dist_node;'       # View node information
 nodeid | groupid |  nodename   | nodeport | noderack | hasmetadata | isactive | noderole  | nodecluster | metadatasynced | shouldhaveshards
--------+---------+-------------+----------+----------+-------------+----------+-----------+-------------+----------------+------------------
      1 |       0 | 10.10.10.10 |     5432 | default  | t           | t        | primary   | default     | t              | f
      4 |       1 | 10.10.10.12 |     5432 | default  | t           | t        | primary   | default     | t              | t
      5 |       2 | 10.10.10.13 |     5432 | default  | t           | t        | primary   | default     | t              | t
      6 |       0 | 10.10.10.11 |     5432 | default  | t           | t        | secondary | default     | t              | f

You can also view user authentication information (superuser access only):

$ psql $PGURL -c 'SELECT * FROM pg_dist_authinfo;'   # View node auth info (superuser only)

You can then connect with a regular application user, such as dbuser_citus with DDL privileges:

psql postgres://dbuser_citus:[email protected]/citus -c 'SELECT * FROM pg_dist_node;'

Using Citus Cluster

We strongly recommend the Citus official documentation for its architecture and core concepts.

Understand the characteristics and uses of the five Citus table types:

• Distributed Table
• Reference Table
• Local Table
• Local Management Table
• Schema Table

On the coordinator, create distributed tables and reference tables. You can query them from any data node. From Citus 11.2, any Citus database node can act as coordinator.

Use pgbench to create tables. Distribute the main table, pgbench_accounts, across nodes. Use the other small tables as reference tables:

PGURL=postgres://dbuser_citus:[email protected]/citus
pgbench -i $PGURL

psql $PGURL <<-EOF
SELECT create_distributed_table('pgbench_accounts', 'aid'); SELECT truncate_local_data_after_distributing_table('public.pgbench_accounts');
SELECT create_reference_table('pgbench_branches')         ; SELECT truncate_local_data_after_distributing_table('public.pgbench_branches');
SELECT create_reference_table('pgbench_history')          ; SELECT truncate_local_data_after_distributing_table('public.pgbench_history');
SELECT create_reference_table('pgbench_tellers')          ; SELECT truncate_local_data_after_distributing_table('public.pgbench_tellers');
EOF

Run read/write tests:

pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus      # Direct connect to coordinator port 5432
pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]:6432/citus # Through connection pool, reduce client connection pressure
pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus      # Any primary node can act as coordinator
pgbench --select-only -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus # Read-only queries

Production Deployment

For production, you usually need physical streaming-replication replicas for the Coordinator and each Worker cluster.

The current conf/ha/citus.yml defines one pg-meta instance and 12 Citus instances across 13 hosts. The Citus instances form six two-node physical clusters with pg_group values 0-5. The following 10-node example shows a separate production topology. It is not the current template.

pg-citus: # citus group
  hosts:
    10.10.10.50: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.60/24 ,pg_seq: 0, pg_role: primary }
    10.10.10.51: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.60/24 ,pg_seq: 1, pg_role: replica }
    10.10.10.52: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.61/24 ,pg_seq: 0, pg_role: primary }
    10.10.10.53: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.61/24 ,pg_seq: 1, pg_role: replica }
    10.10.10.54: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.62/24 ,pg_seq: 0, pg_role: primary }
    10.10.10.55: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.62/24 ,pg_seq: 1, pg_role: replica }
    10.10.10.56: { pg_group: 3, pg_cluster: pg-citus3 ,pg_vip_address: 10.10.10.63/24 ,pg_seq: 0, pg_role: primary }
    10.10.10.57: { pg_group: 3, pg_cluster: pg-citus3 ,pg_vip_address: 10.10.10.63/24 ,pg_seq: 1, pg_role: replica }
    10.10.10.58: { pg_group: 4, pg_cluster: pg-citus4 ,pg_vip_address: 10.10.10.64/24 ,pg_seq: 0, pg_role: primary }
    10.10.10.59: { pg_group: 4, pg_cluster: pg-citus4 ,pg_vip_address: 10.10.10.64/24 ,pg_seq: 1, pg_role: replica }
  vars:
    pg_mode: citus                            # pgsql cluster mode: citus
    pg_version: 18                            # citus 13.x supports PG 14-18
    pg_shard: pg-citus                        # citus shard name: pg-citus
    pg_primary_db: citus                      # primary database used by citus
    pg_vip_enabled: true                      # enable vip for citus cluster
    pg_vip_interface: auto                    # auto detect vip interface for every member
    pg_dbsu_password: DBUser.Postgres         # enable dbsu password access for citus
    pg_extensions: [ citus, postgis, pgvector, topn, pg_cron, hll ]  # install these extensions
    pg_libs: 'citus, pg_cron, pg_stat_statements' # citus will be added by patroni automatically
    pg_users: [{ name: dbuser_citus ,password: DBUser.Citus ,pgbouncer: true ,roles: [ dbrole_admin ]    }]
    pg_databases: [{ name: citus ,owner: dbuser_citus ,extensions: [ citus, vector, topn, pg_cron, hll ] }]
    pg_parameters:
      cron.database_name: citus
      citus.node_conninfo: 'sslrootcert=/pg/cert/ca.crt sslmode=verify-full'
    pg_hba_rules:
      - { user: 'all' ,db: all  ,addr: 127.0.0.1/32  ,auth: ssl   ,title: 'all user ssl access from localhost' }
      - { user: 'all' ,db: all  ,addr: intra         ,auth: ssl   ,title: 'all user ssl access from intranet'  }

We will cover a series of advanced Citus topics in subsequent tutorials:

• Read/write separation
• Failure handling
• Consistent backup and recovery
• Advanced monitoring and diagnostics
• Connection pooling