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 potential failures in PostgreSQL and Pigsty, as well as SOPs for locating, handling, and analyzing issues.

Disk Space Exhausted

Disk space exhaustion is the most common type of failure.

Symptoms

When the disk space where the database resides is exhausted, PostgreSQL will not work normally and may exhibit the following symptoms: database logs repeatedly report “no space left on device” errors, new data cannot be written, and PostgreSQL may even trigger a PANIC and force shutdown.

Pigsty includes a NodeFsSpaceFull alert rule that triggers when filesystem available space is less than 10%. Use the monitoring system’s NODE Instance panel to review the FS metrics panel to locate the issue.

Diagnosis

You can also log into the database node and use df -h to view the usage of each mounted partition to determine which partition is full. For database nodes, focus on checking the following directories and their sizes to determine which category of files has filled up 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 identifying the directory consuming the most space, you can further use du -sh <directory> to drill down and find specific large files or subdirectories.

Resolution

Disk exhaustion is an emergency issue requiring immediate action to free up space and ensure the database continues to operate. When the data disk is not separated from the system disk, a full disk may prevent shell commands from executing. In this case, you can delete the /pg/dummy placeholder file to free up a small amount of emergency space so shell commands can work again. If the database has crashed due to pg_wal filling up, you need to restart the database service after clearing space and carefully check data integrity.

Transaction ID Wraparound

PostgreSQL cyclically uses 32-bit transaction IDs (XIDs), and when exhausted, a “transaction ID wraparound” failure occurs (XID Wraparound).

Symptoms

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

If the problem continues to worsen, PostgreSQL enters protection mode: when remaining transaction IDs drop to about 1 million, the database switches to read-only mode; when reaching the limit of about 2.1 billion (2^31), it refuses any new transactions and forces the server to shut down to avoid data corruption.

Diagnosis

PostgreSQL and Pigsty enable automatic garbage collection (AutoVacuum) by default, so the occurrence of this type of failure usually has deeper root causes. Common causes include: very long transactions (SAGE), misconfigured Autovacuum, replication slot blockage, insufficient resources, storage engine/extension bugs, disk bad blocks.

First identify the database with the highest age, then use the Pigsty PGCAT Database - Tables panel to confirm the age distribution of tables. Also review the database error logs, which usually contain clues to locate the root cause.

Resolution

1. Immediately freeze old transactions: If the database has not yet entered read-only protection mode, immediately execute a manual VACUUM FREEZE on the affected database. You can start by freezing the most severely aged tables one by one rather than doing the entire database at once to accelerate the effect. Connect to the database as a superuser and run VACUUM FREEZE table_name; on tables identified with the largest relfrozenxid, prioritizing tables with the highest XID age. This can quickly reclaim large amounts of transaction ID space.
2. Single-user mode rescue: If the database is already refusing writes or has crashed for protection, you need to start the database in single-user mode to perform freeze operations. In single-user mode, run VACUUM FREEZE database_name; to freeze and clean the entire database. After completion, restart the database in multi-user mode. This can lift the wraparound lock and make the database writable again. Be very careful when operating in single-user mode and ensure sufficient transaction ID margin to complete the freeze.
3. Standby node takeover: In some complex scenarios (e.g., when hardware issues prevent vacuum from completing), consider promoting a read-only standby node in the cluster to primary to obtain a relatively clean environment for handling the freeze. For example, if the primary cannot vacuum due to bad blocks, you can manually failover to promote the standby to the new primary, then perform emergency vacuum freeze on it. After ensuring the new primary has frozen old transactions, switch the load back.

Connection Exhaustion

PostgreSQL has a maximum connections configuration (max_connections). When client connections exceed this limit, new connection requests will be rejected. The typical symptom is that applications cannot connect to the database and report errors like FATAL: remaining connection slots are reserved for non-replication superuser connections or too many clients already. This indicates that regular connections are exhausted, leaving only slots reserved for superusers or replication.

Diagnosis

Connection exhaustion is usually caused by a large number of concurrent client requests. You can directly review the database’s current active sessions through PGCAT Instance / PGCAT Database / PGCAT Locks. Determine what types of queries are filling the system and proceed with further handling. Pay special attention to whether there are many connections in the “Idle in Transaction” state and long-running transactions (as well as slow queries).

Resolution

Kill queries: For situations where exhaustion has already blocked business operations, typically use pg_terminate_backend(pid) immediately for emergency pressure relief. For cases using connection pooling, you can adjust the connection pool size parameters and execute a reload to reduce the number of connections at the database level.

You can also modify the max_connections parameter to a larger value, but this parameter requires a database restart to take effect.

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) when implementing high availability. etcd itself has a storage quota (default is about 2GB). When etcd storage usage reaches the quota limit, etcd will refuse write operations and report “etcdserver: mvcc: database space exceeded”. In this case, Patroni cannot write heartbeats or update configuration to etcd, causing cluster management functions to fail.

Resolution

Versions between Pigsty v2.0.0 and v2.5.1 are affected by this issue by default. Pigsty v2.6.0 added auto-compaction configuration for deployed etcd. If you only use it for PG high availability leases, this issue will no longer occur in regular use cases.

Defective Storage Engine

Currently, TimescaleDB’s experimental storage engine Hypercore has been proven to have defects, with cases of VACUUM being unable to reclaim leading to XID wraparound failures. Users using this feature should migrate to PostgreSQL native tables or TimescaleDB’s default engine promptly.

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

2 - Manual PITR Drill

This tutorial drills PostgreSQL point-in-time recovery in Pigsty v4.5.0’s four-node sandbox. The main path runs pgsql-pitr.yml as down → pitr → up, giving the operator a separate validation gate before 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 the single-node pg-meta, three-node pg-test, and a Silo/pgBackRest repository. The rest of this tutorial uses the exact target pg-meta; do not copy that selector into another environment without resolving its inventory first.

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, and archived WAL must cover the intended target. check validates the current stanza and archive path, but it does not replace a real restore drill or independent-copy validation.

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.

The same object can be supplied temporarily with -e, but preflight and all three stages must repeat the exact same valid JSON to prevent variable drift.

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. Any change to target, backup, or variables requires a new 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, stops Patroni on every target member, and uses immediate shutdown if PostgreSQL remains running. Then verify every target node rather than trusting only the playbook result:

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

Expected results are inactive and “server is not running.” If any member remains active, stop and diagnose; do not enter 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 has readable control state; it does not prove that a time, XID, or application boundary is correct. With action: pause, confirm WAL 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 is not read-only validation and cannot be losslessly undone.

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

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

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

Post-Recovery Acceptance

Verify each layer; “service started” is not recovery completion:

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 the recovery explicitly used archive: false, it wrote archive-mode=off. Reset that override and perform a controlled restart only after validating recovery and confirming a maintenance window; 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

If you plan to enable HugePages, consider using node_hugepage_count and node_hugepage_ratio, and apply with ./node.yml -t node_tune.

HugePages have pros and cons for databases. The advantage is that memory is managed exclusively, eliminating concerns about being reallocated and reducing database OOM risk. The disadvantage is that it may negatively impact performance in certain scenarios.

Before PostgreSQL starts, you need to allocate enough huge pages. The wasted portion can be reclaimed using the pg-tune-hugepage script, but this script is only available for PostgreSQL 15+.

If your PostgreSQL is already running, you can enable huge pages using the following method (PG15+ only):

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.

They are useful for sandbox drills, side-channel investigation, and temporary testing. They are not complete Patroni-cluster recovery orchestrators. 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 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 equal normalized source and destination paths, but it cannot know whether a custom destination contains other important data. If the destination directory exists, it is recursively removed before copying.

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 cannot be reached on the selected port, the script automatically falls back to a cold copy instead of aborting. -s also forces a cold copy. A cold copy is safe only after you independently confirm that the source instance is fully stopped; a postmaster.pid warning is only a clue, not proof of process state.

On the same filesystem, the implementation recognizes these as fast CoW modes: XFS with reflink enabled, Btrfs, Bcachefs, and OCFS2. Other filesystems or cross-filesystem destinations still use cp --reflink=auto but may fall back to a full copy. The script’s help text mentions ZFS more broadly than its 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.

It does not check whether the destination port is free or resize memory settings. Before starting the copy, inspect at least:

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 when standard input is a terminal and -y was not used. Pipes, CI, cron, and other non-interactive invocations do not receive that prompt. Automation must therefore enforce a strict absolute-path allowlist and destination-existence check before invoking the script; do not add -y by default merely 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. Invoking the command without arguments only shows help:

-S/--set (with compatibility alias -b/--backup) only selects which backup set recovery starts from; it is not a stop target. For example, -S 20251225-120000F -d still replays to the end of WAL. Combine -S ... -i to stop as soon as that backup becomes consistent.

For time, name, lsn, xid, and immediate, pgBackRest’s effective default action is to pause at the target; -P/--promote changes it to automatic promotion. Use -X/--exclusive only with a precise boundary such as time, lsn, or xid.

Other Options

-c is a command-rendering check. It does not prove that the backup/WAL is usable or that PostgreSQL and Patroni are stopped. The wrapper also does not filter conflicting native arguments; review the final command carefully when passing repository, tablespace, or link-mapping options.

Safe Execution Sequence

This example shows only the low-level flow for one isolated destination. Use the complete runbook for production cluster recovery:

# 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 refuses root and aborts whenever postmaster.pid exists in the destination. Even a stale PID file requires the operator to confirm that PostgreSQL is stopped before removing it. There is no y/N question: an interactive terminal gets only an interruptible five-second countdown; a non-interactive invocation skips the countdown and enters 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 the recovery target, authorized application data, timeline, and archive settings are all verified. Promotion creates a new timeline; it is not a reversible “inspect” operation. pg-pitr does not itself disable archiving. Do not mechanically follow its generic final “enable archive_mode” hint—inspect the effective value first and correct only an override introduced by this recovery.

Additional Side-Restore Risks

When restoring into a custom directory such as /pg/data1, pgBackRest can restore postgresql.auto.conf from the backup and overwrite the separate port written by pg-fork. Recheck port, archive_mode, sockets, logging, and memory settings before startup.

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

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, but still uses the same backup repository, consumes host resources, and may touch external tablespaces. It is not a risk-free sandbox.

Related

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

5 - Accidental Deletion

Accidental Data Deletion

If it’s a small-scale DELETE misoperation, you can consider using the pg_surgery or pg_dirtyread extension for in-place surgical recovery.

-- 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

When DROP/DELETE type misoperations occur, typically decide on a recovery plan according to the following process:

1. Confirm whether this data can be recovered from the business system or other data systems. If yes, recover directly from the business side.
2. Confirm whether there is a delayed replica. If yes, advance the delayed replica to the time point before deletion and query the data for recovery.
3. If the data has been confirmed deleted, confirm backup information and whether the backup range covers the deletion time point. If it does, start PITR.
4. Confirm whether to perform in-place cluster PITR rollback, or start a new server for replay, or use a replica for replay, and execute the recovery strategy.

Accidental Cluster Deletion

If an entire database cluster is accidentally deleted through Pigsty management commands, for example, incorrectly executing the pgsql-rm.yml playbook or the bin/pgsql-rm command. Unless you have set the pg_rm_backup parameter to false, the backup will be deleted along with the database cluster.

Warning: In this situation, your data will be unrecoverable! Please think three times before proceeding!

Recommendation: For production environments, you can globally configure this parameter to false in the configuration manifest to preserve backups when removing clusters.

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

If a classic 3-node HA deployment experiences simultaneous failure of two nodes (majority), the system typically cannot complete automatic failover and requires manual intervention.

First, assess the status of the other two servers. If they can be brought up quickly, prioritize recovering those two servers. Otherwise, enter the Emergency Recovery Procedure.

The Emergency Recovery Procedure assumes your admin node has failed and only a single regular database node survives. In this case, the fastest recovery process is:

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

Adjust HAProxy Configuration

If you access the cluster bypassing HAProxy, you can skip this step. If you access the database cluster through HAProxy, you need to adjust the load balancer configuration to manually direct read/write traffic to the primary.

• 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 adjusting the configuration, don’t rush to execute systemctl reload haproxy to reload. Wait until after promoting the primary, then execute together. The effect of this configuration is that HAProxy will no longer perform primary health checks (which by default use Patroni), but will directly direct write traffic to the current primary.

Manually Promote Replica

Log in to the target server, switch to the dbsu user, execute CHECKPOINT to flush to disk, stop Patroni, restart PostgreSQL, and execute Promote.

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 adjusted the HAProxy configuration above, you can now execute systemctl reload haproxy to reload the HAProxy configuration and direct 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 second priority is: Avoid Split Brain. Users should prevent the other two servers from coming back online and forming a split brain with the current primary, causing 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 be repaired to continue service, follow these steps for repair and rebuild:

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

After the ETCD cluster quorum is restored, it will resume work. Then start Patroni on the surviving server (current primary) to take over the existing PostgreSQL and regain cluster leadership. After Patroni starts, enter maintenance mode.

sudo systemctl restart patroni
pg pause <pg_cluster>

On the other two instances, create the touch /pg/data/standby.signal marker file as the postgres user to mark them as replicas, 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 the ~/pigsty directory on the admin node. The key files needed are pigsty.yml and files/pki/ca/ca.key.

If you cannot retrieve or don’t have backups of these two files, you can deploy a new Pigsty and migrate the existing cluster to the new deployment via Backup Cluster.

Please regularly backup the pigsty directory (e.g., using Git for version control). Learn from this and avoid such mistakes in the future.

Configuration Repair

You can use the surviving node as the new admin node, copy the ~/pigsty directory to the new admin node, then start adjusting the configuration. For example, replace the original default admin 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 doesn’t have the INFRA module, configure and install a new INFRA module on the current node. Execute the following command to deploy the INFRA module to the surviving node:

./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 repairing each module, you can follow the standard expansion process to add new nodes to the cluster and restore cluster high availability.

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

You can define an OPTIONAL L2 VIP on a PostgreSQL cluster, provided that all nodes in the cluster are in the same L2 network.

This VIP works on Master-Backup mode and always points to the node where the primary instance of the database cluster is located.

This VIP is managed by the VIP-Manager, which reads the Leader Key written by Patroni from DCS (etcd) to determine whether it is the master.

Enable VIP

Define pg_vip_enabled parameter as true in the cluster level to enable the VIP component on the cluster. You can also enable this configuration in the global configuration.

# 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

Beware that pg_vip_address must be a valid IP address with subnet and available in the current L2 network.

pg_vip_interface defaults to auto, in which case Pigsty detects the interface for each instance from the IPv4 address in the inventory.

If auto-detection is unsuitable for non-standard or policy-routing environments, explicitly 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 transforms PostgreSQL into a distributed database, enabling horizontal scaling across multiple nodes to handle large amounts of data and queries.

Patroni v3.0+ provides native high-availability support for Citus, simplifying the setup of Citus clusters. Pigsty also provides native support for this.

• What is Citus
• Patroni Citus Support

Citus Cluster

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

The simplified four-node topology below illustrates the key parameters: a two-node coordinator cluster pg-citus0 and two single-node Worker clusters, pg-citus1 and pg-citus2. It is not a line-for-line 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'  }

Compared to standard PostgreSQL clusters, Citus cluster configuration has some special requirements. First, you need to ensure the Citus extension is downloaded, installed, loaded, and enabled, which involves the following 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.

Second, you need to ensure the Citus cluster is configured correctly:

• 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: Must specify a shard number, integers starting from zero. 0 represents the coordinator cluster, others are Worker clusters.
• pg_cluster: Must be unique among physical PostgreSQL clusters. Using pg_shard plus a sequence number is the usual naming convention, but the current role does not require it to equal a string 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

Using any member’s DBSU (postgres) user, you can list the Citus cluster status with patronictl (alias: 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'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+

You can treat each horizontal shard cluster as an independent PGSQL cluster and manage them with the pg (patronictl) command. Note that when using the pg command to manage Citus clusters, you need to use the --group parameter to specify the cluster shard number:

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

Citus has a system table called pg_dist_node that records Citus cluster node information. Patroni automatically maintains this table.

PGURL=postgres://postgres:DBUser.Postgres@10.10.10.10/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)

Then you can use a regular business user (e.g., dbuser_citus with DDL privileges) to access the Citus cluster:

psql postgres://dbuser_citus:DBUser.Citus@10.10.10.10/citus -c 'SELECT * FROM pg_dist_node;'

Using Citus Cluster

When using Citus clusters, we strongly recommend reading the Citus official documentation to understand its architecture and core concepts.

The key is understanding the five types of tables in Citus and their characteristics and use cases:

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

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

We can use pgbench to create some tables and distribute the main table (pgbench_accounts) across nodes, then use other small tables as reference tables:

PGURL=postgres://dbuser_citus:DBUser.Citus@10.10.10.10/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:DBUser.Citus@10.10.10.10/citus      # Direct connect to coordinator port 5432
pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:DBUser.Citus@10.10.10.10:6432/citus # Through connection pool, reduce client connection pressure
pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:DBUser.Citus@10.10.10.13/citus      # Any primary node can act as coordinator
pgbench --select-only -nv -P1 -c10 -T500 postgres://dbuser_citus:DBUser.Citus@10.10.10.11/citus # Read-only queries

Production Deployment

For production use of Citus, you typically need to set up streaming replication physical replicas for the Coordinator and each Worker cluster.

The current conf/ha/citus.yml defines one pg-meta instance plus 12 Citus instances across 13 hosts: six two-node physical clusters with pg_group values 0-5. The 10-node fragment below is a separate production-topology example, 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