High Availability

• 1: RPO Trade-offs
• 2: Failure Model
  • 2.1: Model of Patroni Passive Failure
  • 2.2: Model of Patroni Active Failure
  • 2.3: Network Partition
• 3: RTO Trade-offs
• 4: Service Access

---

Overview

Pigsty’s PostgreSQL clusters come with out-of-the-box high availability, with core capabilities provided by Patroni, Etcd, and HAProxy.

When your PostgreSQL cluster has two or more instances, you automatically have self-healing database high availability without any additional configuration — as long as any instance in the cluster survives, the cluster can provide complete service. Clients only need to connect to any node in the cluster to get full service without worrying about primary-replica topology changes.

The default norm mode targets an RTO under 45 seconds. With asynchronous replication, pg_rpo=1MiB is Patroni’s sampled lag threshold for failover candidates, not a hard upper bound on actual data loss. Strict synchronous mode with crit.yml keeps acknowledged transactions at RPO = 0 during failover. These behaviors can be configured for your hardware and reliability requirements.

Pigsty includes built-in HAProxy load balancers for automatic traffic switching, providing DNS/VIP/LVS and other access methods for clients. Failover and switchover are almost transparent to the business side except for brief interruptions - applications don’t need to modify connection strings or restart. The minimal maintenance window requirements bring great flexibility and convenience: you can perform rolling maintenance and upgrades on the entire cluster without application coordination. The feature that hardware failures can wait until the next day to handle lets developers, operations, and DBAs sleep well during incidents.

Many large organizations and core institutions have been using Pigsty in production for extended periods. The largest deployment has 25K CPU cores and 220+ PostgreSQL ultra-large instances (64c / 512g / 3TB NVMe SSD). In this deployment case, dozens of hardware failures and various incidents occurred over five years, yet overall availability of over 99.999% was maintained.

---

What problems does High Availability solve?

• Elevates availability in the data security C/IA model: RPO ≈ 0, RTO < 45s.
• Gains seamless rolling maintenance capability, minimizing maintenance window requirements and bringing great convenience.
• Hardware failures can self-heal immediately without human intervention, allowing operations and DBAs to sleep well.
• Replicas can handle read-only requests, offloading primary load and fully utilizing resources.

What are the costs of High Availability?

• Infrastructure dependency: HA requires DCS (etcd/zk/consul) for consensus.
• Higher starting threshold: A meaningful HA deployment requires at least three nodes.
• Extra resource consumption: Each new replica consumes additional resources, though this is usually not a major concern.
• Significantly increased complexity: Backup costs increase significantly, requiring tools to manage complexity.

Limitations of High Availability

Since replication happens in real-time, all changes are immediately applied to replicas. Therefore, streaming replication-based HA solutions cannot handle data deletion or modification caused by human errors and software defects. (e.g., DROP TABLE or DELETE data) Such failures require using delayed clusters or performing point-in-time recovery using previous base backups and WAL archives.

Configuration Strategy	RTO	RPO
Standalone + Nothing	Data permanently lost, unrecoverable	All data lost
Standalone + Base Backup	Depends on backup size and bandwidth (hours)	Lose data since last backup (hours to days)
Standalone + Base Backup + WAL Archive	Depends on backup size and bandwidth (hours)	Lose unarchived data (tens of MB)
Primary-Replica + Manual Failover	~10 minutes	Lose data in replication lag (~100KB)
Primary-Replica + Auto Failover	Within 1 minute	Lose data in replication lag (~100KB)
Primary-Replica + Auto Failover + Sync Commit	Within 1 minute	No data loss

---

How It Works

In Pigsty, the high availability architecture works as follows:

• PostgreSQL uses standard streaming replication to build physical replicas; replicas take over when the primary fails.
• Patroni manages PostgreSQL server processes and handles high availability matters.
• Etcd provides distributed configuration storage (DCS) capability and is used for leader election after failures.
• Patroni relies on Etcd to reach cluster leader consensus and provides health check interfaces externally.
• HAProxy exposes cluster services externally and uses Patroni health check interfaces to automatically distribute traffic to healthy nodes.
• vip-manager provides an optional Layer 2 VIP, retrieves leader information from Etcd, and binds the VIP to the node where the cluster primary resides.

When the primary fails, a new round of leader election is triggered. The healthiest replica in the cluster (highest LSN position, minimum data loss) wins and is promoted to the new primary. After the winning replica is promoted, read-write traffic is immediately routed to the new primary. The impact of primary failure is brief write service unavailability: write requests will be blocked or fail directly from primary failure until new primary promotion, with unavailability typically lasting 15 to 30 seconds, usually not exceeding 1 minute.

When a replica fails, read-only traffic is routed to other replicas. Only when all replicas fail will read-only traffic ultimately be handled by the primary. The impact of replica failure is partial read-only query interruption: queries currently running on that replica will abort due to connection reset and be immediately taken over by other available replicas.

Failure detection is performed jointly by Patroni and Etcd. The cluster leader holds a lease; if it fails to renew the lease within its TTL (30 seconds in the default norm mode), the lease expires, triggering a Failover and a new election.

Even without any failures, you can proactively change the cluster primary through Switchover. In this case, write queries on the primary will experience a brief interruption and be immediately routed to the new primary. This operation is typically used for rolling maintenance/upgrades of database servers.

1 - RPO Trade-offs

RPO (Recovery Point Objective) defines the maximum amount of data loss allowed when the primary fails.

For scenarios where data integrity is critical, such as financial transactions, RPO = 0 is typically required, meaning no data loss is allowed.

However, stricter RPO targets come at a cost: higher write latency, reduced system throughput, and the risk that replica failures may cause primary unavailability. For typical scenarios, some data loss is acceptable in exchange for higher availability and performance.

Trade-offs

In asynchronous replication scenarios, there is typically some replication lag between replicas and the primary (depending on network and throughput, normally in the range of 10KB-100KB / 100µs-10ms). This means when the primary fails, replicas may not have fully synchronized with the latest data. If a failover occurs, the new primary may lose some unreplicated data.

The pg_rpo parameter is written to Patroni’s maximum_lag_on_failover and defaults to 1048576 (1MiB). It is the sampled lag threshold that permits a replica to participate as a failover candidate, not a hard upper bound on actual data loss.

When the cluster primary fails, if any replica has replication lag within this threshold, Pigsty will automatically promote that replica to be the new primary. However, when all replicas exceed this threshold, Pigsty will refuse [automatic failover] to prevent data loss. Manual intervention is then required to decide whether to wait for the primary to recover (which may never happen) or accept the data loss and force-promote a replica.

Because the primary’s WAL position is not sampled continuously, the worst-case loss under asynchronous replication can also include WAL generated during the most recent ttl window (on average, roughly another loop_wait/2 of WAL). Configure this threshold with your workload’s write rate in mind. Increasing it improves the chance of automatic failover but also broadens candidate eligibility.

When you set pg_rpo = 0, Pigsty enables synchronous replication, ensuring the primary only returns write success after at least one replica has persisted the data. This configuration ensures zero replication lag but introduces significant write latency and reduces overall throughput.

flowchart LR
    A([Primary Failure]) --> B{Synchronous<br/>Replication?}

    B -->|No| C{Lag < RPO?}
    B -->|Yes| D{Sync Replica<br/>Available?}

    C -->|Yes| E[Lossy Auto Failover<br/>Sampled candidate lag is within threshold]
    C -->|No| F[Refuse Auto Failover<br/>Wait for Primary Recovery<br/>or Manual Intervention]

    D -->|Yes| G[Lossless Auto Failover<br/>RPO = 0]
    D -->|No| H{Strict Mode?}

    H -->|No| C
    H -->|Yes| F

    style A fill:#dc3545,stroke:#b02a37,color:#fff
    style E fill:#F0AD4E,stroke:#146c43,color:#fff
    style G fill:#198754,stroke:#146c43,color:#fff
    style F fill:#BE002F,stroke:#565e64,color:#fff

Protection Modes

Pigsty provides three protection modes to help users make trade-offs under different RPO requirements, similar to Oracle Data Guard protection modes.

Implementation

The three protection modes differ in how two core Patroni parameters are configured: synchronous_mode and synchronous_mode_strict:

• synchronous_mode: Whether Patroni enables synchronous replication. If enabled, check if synchronous_mode_strict enables strict synchronous mode.
• synchronous_mode_strict = false: Default configuration, allows degradation to async mode when replicas fail, primary continues service (Maximum Availability)
• synchronous_mode_strict = true: Degradation forbidden, primary stops writes until sync replica recovers (Maximum Protection)

Typically, you only need to set the pg_rpo parameter to 0 to enable the synchronous_mode switch, activating Maximum Availability mode. If you use pg_conf = crit.yml template, it additionally enables the synchronous_mode_strict strict mode switch, activating Maximum Protection mode. Additionally, you can enable watchdog to fence the primary directly during node/Patroni freeze scenarios instead of degrading, achieving behavior equivalent to Oracle Maximum Protection mode.

You can also directly configure these Patroni parameters as needed. Refer to Patroni and PostgreSQL documentation to achieve stronger data protection, such as:

• Specify the synchronous replica list, configure more sync replicas to improve disaster tolerance, use quorum synchronous commit, or even require all replicas to perform synchronous commit.
• Configure synchronous_commit: 'remote_apply' to strictly ensure primary-replica read-write consistency. (Oracle Maximum Protection mode is equivalent to remote_write)

Recommendations

Maximum Performance mode (asynchronous replication) is the default mode used by Pigsty and is sufficient for the vast majority of workloads. It tolerates some loss during a failure in exchange for higher throughput and availability. In this mode, pg_rpo adjusts the sampled lag threshold for failover candidates; actual worst-case loss also depends on write rate, ttl, and sampling timing.

Maximum Availability mode (synchronous replication) is suitable for scenarios with high data-integrity requirements. Acknowledged transactions have zero loss while a synchronous replica is healthy, but the cluster can degrade when all synchronous replicas are unavailable. In this mode, a minimum of two-node PostgreSQL cluster (one primary, one replica) is required. Set pg_rpo to 0 to enable this mode.

Maximum Protection mode (strict synchronous replication) is suitable for financial transactions, medical records, and other scenarios with extremely high data integrity requirements. We recommend using at least a three-node cluster (one primary, two replicas), because with only two nodes, if the replica fails, the primary will stop writes, causing service unavailability, which reduces overall system reliability. With three nodes, if only one replica fails, the primary can continue to serve.

2 - Failure Model

Patroni failures can be classified into 10 categories by failure target, and further consolidated into five categories based on detection path, which are detailed in this section.

However, for RTO calculation purposes, all failures ultimately converge to two paths. This section explores the upper bound, lower bound, and average RTO for these two scenarios.

• Passive election triggered after Patroni loses contact
• Patroni actively detects failure and triggers switchover

flowchart LR
    A([Primary Failure]) --> B{Patroni<br/>Detected?}

    B -->|PG Crash| C[Attempt Local Restart]
    B -->|Node Down| D[Wait TTL Expiration]

    C -->|Success| E([Local Recovery])
    C -->|Fail/Timeout| F[Release Leader Lock]

    D --> F
    F --> G[Replica Election]
    G --> H[Execute Promote]
    H --> I[HAProxy Detects]
    I --> J([Service Restored])

    style A fill:#dc3545,stroke:#b02a37,color:#fff
    style E fill:#198754,stroke:#146c43,color:#fff
    style J fill:#198754,stroke:#146c43,color:#fff

2.1 - Model of Patroni Passive Failure

infographic list-row-simple-horizontal-arrow
data

  desc Lease Expiration Stages
  items
    - label Lease Expiration
    - label Replica Detect
    - label Elect & Promote
    - label Haproxy Up
theme light
  palette antv

RTO Timeline

tooltip: { trigger: axis, axisPointer: { type: shadow }, formatter: $fn:fmt }
legend: { top: 0, itemGap: 12, data: [Lease Expiration, Replica Detection, Lock Contest & Promote, Health Check] }
grid: { left: 64, right: 24, bottom: 32, top: 40 }
xAxis: { type: value, name: Seconds, nameLocation: end, max: 160, axisLine: { show: true }, axisTick: { show: true }, splitLine: { show: true, lineStyle: { type: dashed, opacity: 0.5 } }, minorTick: { show: true, splitNumber: 5 }, minorSplitLine: { show: true, lineStyle: { type: dotted, opacity: 0.2 } } }
yAxis: { type: category, axisLine: { show: true }, axisTick: { show: true }, splitLine: { show: false }, axisLabel: { fontSize: 10, fontFamily: monospace }, data: [wide-max, wide-avg, wide-min, "", safe-max, safe-avg, safe-min, "", norm-max, norm-avg, norm-min, "", fast-max, fast-avg, fast-min] }
series:
  - { name: Lease Expire, type: bar, stack: main, barWidth: 20, z: 2, emphasis: { focus: series }, itemStyle: { color: "#e15759" }, data: [120, 110, 100, "-", 60, 55, 50, "-", 30, 27, 25, "-", 20, 17, 15] }
  - { name: Replica Detect, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#edc949" }, data: [20, 10, 0, "-", 10, 5, 0, "-", 5, 3, 0, "-", 5, 3, 0] }
  - { name: Elect & Promote, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#59a14f" }, data: [2, 1, 0, "-", 2, 1, 0, "-", 2, 1, 0, "-", 2, 1, 0] }
  - { name: HAProxy Check, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#4e79a7" }, data: [8, 6, 4, "-", 6, 5, 3, "-", 4, 3, 2, "-", 2, 2, 1] }
  - { name: Total RTO, type: bar, barGap: "-100%", barWidth: 20, z: 1, itemStyle: { color: "#888", opacity: 0 }, emphasis: { itemStyle: { opacity: 0 } }, data: [150, 127, 104, "-", 78, 66, 53, "-", 41, 34, 27, "-", 29, 23, 16] }
  - { name: RTO Budget, type: bar, barGap: "-100%", barWidth: 20, z: 0, itemStyle: { color: "rgba(0,0,0,0.08)" }, emphasis: { itemStyle: { color: "rgba(0,0,0,0.12)" } }, data: [150, 150, 150, "-", 90, 90, 90, "-", 45, 45, 45, "-", 30, 30, 30] }

Failure Model

Key Difference Between Passive and Active Failover:

In passive failover scenarios, Patroni dies along with the node and cannot actively release the Leader Key. The lease in DCS can only trigger cluster election after TTL naturally expires.

Timeline Analysis

Phase 1: Lease Expiration

The Patroni primary refreshes the Leader Key every loop_wait cycle, resetting TTL to the configured value.

Timeline:
     t-loop        t          t+ttl-loop    t+ttl
       |           |              |           |
    Last Refresh  Failure      Best Case   Worst Case
       |←── loop ──→|              |           |
       |←──────────── ttl ─────────────────────→|

• Best case: Failure occurs just before lease refresh (elapsed loop since last refresh), remaining TTL = ttl - loop
• Worst case: Failure occurs right after lease refresh, must wait full ttl
• Average case: ttl - loop/2

Phase 2: Replica Detection

Replicas wake up on loop_wait cycles and check the Leader Key status in DCS.

Timeline:
    Lease Expired   Replica Wakes
       |            |
       |←── 0~loop ─→|

• Best case: Replica happens to wake when lease expires, wait 0
• Worst case: Replica just entered sleep when lease expires, wait loop
• Average case: loop/2

Phase 3: Lock Contest & Promote

When replicas detect Leader Key expiration, they start the election process. The replica that acquires the Leader Key executes pg_ctl promote to become the new primary.

1. Via REST API, parallel queries to check each replica’s replication position, typically 10ms, hardcoded 2s timeout.
2. Compare WAL positions to determine the best candidate, replicas attempt to create Leader Key (CAS atomic operation)
3. Execute pg_ctl promote to become primary (very fast, typically negligible)

Election Flow:
  ReplicaA ──→ Query replication position ──→ Compare ──→ Contest lock ──→ Success
  ReplicaB ──→ Query replication position ──→ Compare ──→ Contest lock ──→ Fail

• Best case: Single replica or immediate lock acquisition and promotion, constant overhead 0.1s
• Worst case: DCS API call timeout: 2s
• Average case: 1s constant overhead

Phase 4: Health Check

HAProxy detects the new primary online, requiring rise consecutive successful health checks.

Detection Timeline:
  New Primary    First Check   Second Check  Third Check (UP)
     |          |           |           |
     |←─ 0~inter ─→|←─ fast ─→|←─ fast ─→|

• Best case: New primary promoted just before check, (rise-1) × fastinter
• Worst case: New primary promoted right after check, (rise-1) × fastinter + inter
• Average case: (rise-1) × fastinter + inter/2

RTO Formula

Sum all phase times to get total RTO:

Best Case

Average Case

Worst Case

Model Calculation

Substitute the four RTO model parameters into the formulas above:

pg_rto_plan:  # [ttl, loop, retry, start, margin, inter, fastinter, downinter, rise, fall]
  fast: [ 20  ,5  ,5  ,15 ,5  ,'1s' ,'0.5s' ,'1s' ,3 ,3 ]  # rto < 30s
  norm: [ 30  ,5  ,10 ,25 ,5  ,'2s' ,'1s'   ,'2s' ,3 ,3 ]  # rto < 45s
  safe: [ 60  ,10 ,20 ,45 ,10 ,'3s' ,'1.5s' ,'3s' ,3 ,3 ]  # rto < 90s
  wide: [ 120 ,20 ,30 ,95 ,15 ,'4s' ,'2s'   ,'4s' ,3 ,3 ]  # rto < 150s

Four Mode Calculation Results (unit: seconds, format: min / avg / max)

2.2 - Model of Patroni Active Failure

infographic list-row-simple-horizontal-arrow
data
  desc When Patroni is healthy but PostgreSQL crashes
  items
    - label Crash Found
    - label Restart Timeout
    - label Replica Detect
    - label Elect Promote
    - label HAProxy Check
theme light
  palette antv

RTO Timeline

tooltip: { trigger: axis, axisPointer: { type: shadow }, formatter: $fn:fmt }
legend: { top: 0, itemGap: 12, data: [ Crash Found, Restart Timeout, Replica Detection, Elect Promote, HAProxy Check] }
grid: { left: 64, right: 24, bottom: 32, top: 40 }
xAxis: { type: value, name: Seconds, nameLocation: end, max: 160, axisLine: { show: true }, axisTick: { show: true }, splitLine: { show: true, lineStyle: { type: dashed, opacity: 0.5 } }, minorTick: { show: true, splitNumber: 5 }, minorSplitLine: { show: true, lineStyle: { type: dotted, opacity: 0.2 } } }
yAxis: { type: category, axisLine: { show: true }, axisTick: { show: true }, splitLine: { show: false }, axisLabel: { fontSize: 10, fontFamily: monospace }, data: [wide-max, wide-avg, wide-min, "", safe-max, safe-avg, safe-min, "", norm-max, norm-avg, norm-min, "", fast-max, fast-avg, fast-min] }
series:
  - { name: Crash Found, type: bar, stack: main, barWidth: 20, z: 2, emphasis: { focus: series }, itemStyle: { color: "#b07aa1" }, data: [20, 10, 0, "-", 10, 5, 0, "-", 5, 3, 0, "-", 5, 3, 0] }
  - { name: Restart Timeout, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#f28e2c" }, data: [95, 95, 0, "-", 45, 45, 0, "-", 25, 25, 0, "-", 15, 15, 0] }
  - { name: Replica Detect, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#edc949" }, data: [20, 10, 0, "-", 10, 5, 0, "-", 5, 3, 0, "-", 5, 3, 0] }
  - { name: Elect Promote, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#59a14f" }, data: [2, 1, 0, "-", 2, 1, 0, "-", 2, 1, 0, "-", 2, 1, 0] }
  - { name: HAProxy Check, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#4e79a7" }, data: [8, 6, 4, "-", 6, 5, 3, "-", 4, 3, 2, "-", 2, 2, 1] }
  - { name: RTO Total, type: bar, barGap: "-100%", barWidth: 20, z: 1, itemStyle: { color: "#888", opacity: 0 }, emphasis: { itemStyle: { opacity: 0 } }, data: [145, 122, 4, "-", 73, 61, 3, "-", 41, 35, 2, "-", 29, 24, 1] }
  - { name: RTO Budget, type: bar, barGap: "-100%", barWidth: 20, z: 0, itemStyle: { color: "rgba(0,0,0,0.08)" }, emphasis: { itemStyle: { color: "rgba(0,0,0,0.12)" } }, data: [150, 150, 150, "-", 90, 90, 90, "-", 45, 45, 45, "-", 30, 30, 30] }

Failure Model

Key Difference Between Active and Passive Failure:

In active failure scenarios, Patroni remains alive and can actively detect PG crash and attempt restart. If restart succeeds, service self-heals; if timeout expires without recovery, Patroni actively releases the Leader Key, triggering cluster election.

Timing Analysis

Phase 1: Failure Detection

Patroni checks PostgreSQL status every loop_wait cycle (via pg_isready or process check).

Timeline:
    Last check      PG crash      Next check
       |              |              |
       |←── 0~loop ──→|              |

• Best case: PG crashes right before Patroni check, detected immediately, wait 0
• Worst case: PG crashes right after check, wait for next cycle, wait loop
• Average case: loop/2

Phase 2: Restart Timeout

After Patroni detects PG crash, it attempts to restart PostgreSQL. This phase has two possible outcomes:

Timeline:
  Crash detected     Restart attempt     Success/Timeout
      |                  |                    |
      |←──── 0 ~ start ─────────────────────→|

Path A: Self-healing Success (Best case)

• PG restarts successfully, service recovers
• No failover triggered, extremely short RTO
• Wait time: 0 (relative to Failover path)

Path B: Failover Required (Average/Worst case)

• PG still not recovered after primary_start_timeout
• Patroni actively releases Leader Key
• Wait time: start

Note: Average case assumes failover is required. If PG can quickly self-heal, overall RTO will be significantly lower.

Phase 3: Standby Detection

Standbys wake up on loop_wait cycle and check Leader Key status in DCS. When primary Patroni releases the Leader Key, standbys discover this and begin election.

Timeline:
    Lease released    Standby wakes
       |                  |
       |←── 0~loop ──────→|

• Best case: Standby wakes right when lease is released, wait 0
• Worst case: Standby just went to sleep when lease released, wait loop
• Average case: loop/2

Phase 4: Lock & Promote

After standbys discover Leader Key vacancy, election begins. The standby that acquires the Leader Key executes pg_ctl promote to become the new primary.

1. Via REST API, parallel queries to check each standby’s replication position, typically 10ms, hardcoded 2s timeout.
2. Compare WAL positions to determine best candidate, standbys attempt to create Leader Key (CAS atomic operation)
3. Execute pg_ctl promote to become primary (very fast, typically negligible)

Election process:
  StandbyA ──→ Query replication position ──→ Compare ──→ Try lock ──→ Success
  StandbyB ──→ Query replication position ──→ Compare ──→ Try lock ──→ Fail

• Best case: Single standby or direct lock acquisition and promote, constant overhead 0.1s
• Worst case: DCS API call timeout: 2s
• Average case: 1s constant overhead

Phase 5: Health Check

HAProxy detects new primary online, requires rise consecutive successful health checks.

Check timeline:
  New primary    First check    Second check   Third check (UP)
     |              |               |               |
     |←─ 0~inter ──→|←─── fast ────→|←─── fast ────→|

• Best case: New primary comes up right at check time, (rise-1) × fastinter
• Worst case: New primary comes up right after check, (rise-1) × fastinter + inter
• Average case: (rise-1) × fastinter + inter/2

RTO Formula

Sum all phase times to get total RTO:

Best Case (PG instant self-healing)

Average Case (Failover required)

Worst Case

Model Calculation

Substituting the four RTO model parameters into the formulas above:

pg_rto_plan:  # [ttl, loop, retry, start, margin, inter, fastinter, downinter, rise, fall]
  fast: [ 20  ,5  ,5  ,15 ,5  ,'1s' ,'0.5s' ,'1s' ,3 ,3 ]  # rto < 30s
  norm: [ 30  ,5  ,10 ,25 ,5  ,'2s' ,'1s'   ,'2s' ,3 ,3 ]  # rto < 45s
  safe: [ 60  ,10 ,20 ,45 ,10 ,'3s' ,'1.5s' ,'3s' ,3 ,3 ]  # rto < 90s
  wide: [ 120 ,20 ,30 ,95 ,15 ,'4s' ,'2s'   ,'4s' ,3 ,3 ]  # rto < 150s

Calculation Results for Four Modes (unit: seconds, format: min / avg / max)

Comparison with Passive Failure

RTO Comparison (Average case):

Analysis: In fast and norm modes, active failure RTO is slightly higher than passive failure because it waits for primary_start_timeout (start); but in safe and wide modes, since start < ttl - loop, active failure is actually faster. However, active failure has the possibility of self-healing, with potentially extremely short RTO in best case scenarios.

2.3 - Network Partition

infographic list-row-simple-horizontal-arrow
data
  title Network Partition Failover Flow
  desc Primary partitioned from DCS, Patroni proactively demotes to prevent split-brain, waits for TTL expiration before switchover
  items
    - label Primary Demote
      desc Patroni demotes PG after retry timeout
      icon mingcute/shield-fill
    - label Lease Expiration
      desc Leader Key TTL expires
      icon mingcute/close-circle-fill
    - label Replica Detection
      desc Replica detects lease expiration, starts election
      icon mingcute/key-2-fill
    - label Lock & Promote
      desc Replica acquires lock and promotes to new primary
      icon mingcute/radar-fill
    - label Health Check
      desc HAProxy detects new primary online
      icon mingcute/arrow-up-circle-fill
theme light
  palette antv

RTO Timeline

tooltip: { trigger: axis, axisPointer: { type: shadow }, formatter: $fn:fmt }
legend: { top: 0, itemGap: 12, data: [Primary Demote, Lease Expiration, Replica Detection, Lock & Promote, Health Check] }
grid: { left: 64, right: 24, bottom: 32, top: 40 }
xAxis: { type: value, name: sec, nameLocation: end, max: 160, axisLine: { show: true }, axisTick: { show: true }, splitLine: { show: true, lineStyle: { type: dashed, opacity: 0.5 } }, minorTick: { show: true, splitNumber: 5 }, minorSplitLine: { show: true, lineStyle: { type: dotted, opacity: 0.2 } } }
yAxis: { type: category, axisLine: { show: true }, axisTick: { show: true }, splitLine: { show: false }, axisLabel: { fontSize: 10, fontFamily: monospace }, data: [wide-max, wide-avg, wide-min, "", safe-max, safe-avg, safe-min, "", norm-max, norm-avg, norm-min, "", fast-max, fast-avg, fast-min] }
series:
  - { name: Primary Demote, type: bar, stack: main, barWidth: 20, z: 2, emphasis: { focus: series }, itemStyle: { color: "#76b7b2" }, data: [50, 40, 30, "-", 30, 25, 20, "-", 15, 13, 10, "-", 10, 8, 5] }
  - { name: Lease Expiration, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#e15759" }, data: [70, 70, 70, "-", 30, 30, 30, "-", 15, 15, 15, "-", 10, 10, 10] }
  - { name: Replica Detection, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#edc949" }, data: [20, 10, 0, "-", 10, 5, 0, "-", 5, 3, 0, "-", 5, 3, 0] }
  - { name: Lock & Promote, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#59a14f" }, data: [2, 1, 0, "-", 2, 1, 0, "-", 2, 1, 0, "-", 2, 1, 0] }
  - { name: Health Check, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#4e79a7" }, data: [8, 6, 4, "-", 6, 5, 3, "-", 4, 3, 2, "-", 2, 2, 1] }
  - { name: RTO Total, type: bar, barGap: "-100%", barWidth: 20, z: 1, itemStyle: { color: "#888", opacity: 0 }, emphasis: { itemStyle: { opacity: 0 } }, data: [150, 127, 104, "-", 78, 66, 53, "-", 41, 34, 27, "-", 29, 23, 16] }
  - { name: RTO Budget, type: bar, barGap: "-100%", barWidth: 20, z: 0, itemStyle: { color: "rgba(0,0,0,0.08)" }, emphasis: { itemStyle: { color: "rgba(0,0,0,0.12)" } }, data: [150, 150, 150, "-", 90, 90, 90, "-", 45, 45, 45, "-", 30, 30, 30] }

Failure Model

Key difference between network partition and node crash:

In network partition scenarios, the primary PostgreSQL may still be running and accepting writes, causing split-brain issues. Patroni solves this through active demotion: when unable to refresh Leader Key, proactively demotes PostgreSQL to read-only or shuts it down.

Timeline Analysis

Phase 1: Primary Demotion

When primary Patroni is network-partitioned from DCS, it cannot refresh Leader Key and starts retrying.

Timeline:
  Partition      Detect partition      Retry timeout      Primary demotes
     |               |                    |                    |
     |←── loop ──→|←── retry ──→|

• Detection delay: After partition occurs, must wait for next loop_wait cycle to detect
• Retry phase: Patroni continuously retries DCS operations during retry_timeout
• Active demotion: After retry timeout, Patroni proactively demotes PostgreSQL (prevents split-brain)

Key design: Patroni requires constraint loop_wait + 2 × retry_timeout ≤ ttl to ensure primary demotes before TTL expires.

Phase 2: Lease Expiration

After primary demotion, Leader Key still exists in DCS, must wait for TTL to naturally expire.

Timeline:
  Primary demoted                   TTL expires
     |                                 |
     |←── ttl - (loop + retry) ──→|

Since the primary has demoted, waiting time during this phase is the remaining TTL time. Since partition detection and remaining TTL are negatively correlated (earlier partition means slower detection but longer remaining TTL), their sum is constant:

Note: Primary demotion + lease expiration total time still approximately equals ttl, same as expire failure.

Phase 3: Replica Detection

Replica wakes up in loop_wait cycle and checks Leader Key status in DCS.

Timeline:
    Lease expired      Replica wakes
       |                  |
       |←── 0~loop ─→|

• Best case: Replica wakes right when lease expires, wait 0
• Worst case: Replica just entered sleep when lease expires, wait loop
• Average case: loop/2

Phase 4: Lock & Promote

After replica discovers Leader Key expired, it starts the election process.

Election flow:
  ReplicaA ──→ Query replication position ──→ Compare ──→ Try lock ──→ Success
  ReplicaB ──→ Query replication position ──→ Compare ──→ Try lock ──→ Fail

• Best case: Single replica or directly acquires lock and promotes, ≈ 0
• Worst case: DCS API call timeout, 2s
• Average case: 1s

Phase 5: Health Check

HAProxy detects new primary coming online, requires rise consecutive successful health checks.

Detection timeline:
  New primary    First check    Second check   Third check (UP)
     |              |               |               |
     |←─ 0~inter ─→|←─ fast ─→|←─ fast ─→|

• Best case: (rise-1) × fastinter
• Worst case: (rise-1) × fastinter + inter
• Average case: (rise-1) × fastinter + inter/2

RTO Formula

Sum all phase times to get total RTO.

Since primary demotion + lease expiration ≈ ttl, network partition RTO formula is same as expire failure:

Best Case

Average Case

Worst Case

Model Calculation

Substituting the four RTO model parameters into the formulas:

pg_rto_plan:  # [ttl, loop, retry, start, margin, inter, fastinter, downinter, rise, fall]
  fast: [ 20  ,5  ,5  ,15 ,5  ,'1s' ,'0.5s' ,'1s' ,3 ,3 ]  # rto < 30s
  norm: [ 30  ,5  ,10 ,25 ,5  ,'2s' ,'1s'   ,'2s' ,3 ,3 ]  # rto < 45s
  safe: [ 60  ,10 ,20 ,45 ,10 ,'3s' ,'1.5s' ,'3s' ,3 ,3 ]  # rto < 90s
  wide: [ 120 ,20 ,30 ,95 ,15 ,'4s' ,'2s'   ,'4s' ,3 ,3 ]  # rto < 150s

Patroni constraint validation (loop + 2×retry ≤ ttl):

Four mode calculation results (seconds, format: min / avg / max)

Conclusion: Network partition RTO is same as expire failure (node crash), as the bottleneck is TTL expiration time.

Split-brain Protection

The biggest risk of network partition is split-brain: old primary may still be running and accepting writes. Patroni provides multiple protection mechanisms:

1. Primary Self-Demotion

Patroni’s core protection mechanism: when unable to refresh Leader Key, proactively demotes PostgreSQL.

# Patroni pseudo-code logic
if not can_refresh_leader_key():
    retry_until(retry_timeout)
    if still_cannot_refresh():
        demote_postgresql()  # Demote to read-only or shut down

2. Linux Watchdog

If Patroni process hangs and cannot execute demotion, Linux watchdog will force system restart.

# patroni.yml configuration
watchdog:
  mode: required  # Require watchdog available
  device: /dev/watchdog
  safety_margin: 5

3. Fencing Mechanism

Can configure fencing scripts to forcibly isolate old primary (e.g., disable network interface, stop service, etc.).

Special Scenarios

Scenario A: Primary partitioned from DCS, replicas normal

This is the most common network partition scenario, the main focus of this article.

┌─────────┐         ╳         ┌─────────┐
│ Primary │ ←── Partition ──→ │   DCS   │
│ Patroni │                   │  etcd   │
└─────────┘                   └─────────┘
                                  ↑
                              Normal connection
                                  ↓
                              ┌─────────┐
                              │ Replica │
                              │ Patroni │
                              └─────────┘

• Primary Patroni cannot refresh Leader Key → Active demotion
• Replica normally detects TTL expiration → Elected as new primary
• RTO ≈ Expire failure RTO

Scenario B: Primary normal, replica partitioned from DCS

┌─────────┐                   ┌─────────┐
│ Primary │ ←── Normal ──→    │   DCS   │
│ Patroni │                   │  etcd   │
└─────────┘                   └─────────┘
                                  ╳
                              Partition
                                  ╳
                              ┌─────────┐
                              │ Replica │
                              │ Patroni │
                              └─────────┘

• Primary normally refreshes Leader Key
• Replica cannot participate in election (but replication can continue)
• No failover triggered, service continues normally

Scenario C: All nodes partitioned from DCS

┌─────────┐         ╳         ┌─────────┐
│ Primary │ ←── Partition ──→ │   DCS   │
│ Patroni │                   │  etcd   │
└─────────┘                   └─────────┘
                                  ╳
┌─────────┐         ╳             │
│ Replica │ ←── Partition ────────┘
│ Patroni │
└─────────┘

• Primary demotes, replica cannot elect
• Cluster completely unavailable
• Requires manual intervention to restore DCS connectivity

Comparison with Other Failures

Key Insight: Network partition RTO is same as expire failure, but requires additional split-brain protection mechanisms. Ensuring loop_wait + 2 × retry_timeout ≤ ttl constraint is the key design to prevent split-brain.

3 - RTO Trade-offs

RTO (Recovery Time Objective) defines the maximum time required for the system to restore write capability when the primary fails.

For critical transaction systems where availability is paramount, the shortest possible RTO is typically required, such as under one minute.

However, shorter RTO comes at a cost: increased false failover risk. Network jitter may be misinterpreted as a failure, leading to unnecessary failovers. For cross-datacenter/cross-region deployments, RTO requirements are typically relaxed (e.g., 1-2 minutes) to reduce false failover risk.

Trade-offs

The upper limit of unavailability during failover is controlled by the pg_rto parameter. Pigsty provides four preset RTO modes: fast, norm, safe, wide, each optimized for different network conditions and deployment scenarios. The default is norm mode (~45 seconds).

When the primary fails, the entire recovery process involves multiple phases: Patroni detects the failure, DCS lock expires, new primary election, promote execution, HAProxy detects the new primary. Reducing RTO means shortening the timeout for each phase, which makes the cluster more sensitive to network jitter, thereby increasing false failover risk.

You need to choose the appropriate mode based on actual network conditions, balancing recovery speed and false failover risk. The worse the network quality, the more conservative mode you should choose; the better the network quality, the more aggressive mode you can choose.

flowchart LR
    A([Primary Failure]) --> B{Patroni<br/>Detected?}

    B -->|PG Crash| C[Attempt Local Restart]
    B -->|Node Down| D[Wait TTL Expiration]

    C -->|Success| E([Local Recovery])
    C -->|Fail/Timeout| F[Release Leader Lock]

    D --> F
    F --> G[Replica Election]
    G --> H[Execute Promote]
    H --> I[HAProxy Detects]
    I --> J([Service Restored])

    style A fill:#dc3545,stroke:#b02a37,color:#fff
    style E fill:#198754,stroke:#146c43,color:#fff
    style J fill:#198754,stroke:#146c43,color:#fff

Four Modes

Pigsty provides four RTO modes to help users make trade-offs under different network conditions.

RTO Timeline

Patroni / PG HA has two key failure paths: active failure detection (Patroni detects a PG crash and attempts restart) and passive lease expiration (node down waits for TTL expiration to trigger election).

tooltip: { trigger: axis, axisPointer: { type: shadow }, formatter: $fn:fmt }
legend: { top: 0, itemGap: 10, data: [Lease Expiration, Failure Detection, Restart Timeout, Replica Detection, Lock & Promote, Health Check] }
grid: { left: 110, right: 24, bottom: 32, top: 40 }
xAxis: { type: value, name: Seconds, nameLocation: end, max: 160, axisLine: { show: true }, axisTick: { show: true }, splitLine: { show: true, lineStyle: { type: dashed, opacity: 0.5 } }, minorTick: { show: true, splitNumber: 5 }, minorSplitLine: { show: true, lineStyle: { type: dotted, opacity: 0.2 } } }
yAxis: { type: category, axisLine: { show: true }, axisTick: { show: true }, splitLine: { show: false }, axisLabel: { fontSize: 9, fontFamily: monospace }, data: [wide-passive-max, wide-passive-avg, wide-passive-min, wide-active-max, wide-active-avg, wide-active-min, "", safe-passive-max, safe-passive-avg, safe-passive-min, safe-active-max, safe-active-avg, safe-active-min, "", norm-passive-max, norm-passive-avg, norm-passive-min, norm-active-max, norm-active-avg, norm-active-min, "", fast-passive-max, fast-passive-avg, fast-passive-min, fast-active-max, fast-active-avg, fast-active-min] }
series:
  - { name: Lease Expiration, type: bar, stack: main, barWidth: 16, z: 2, emphasis: { focus: series }, itemStyle: { color: "#e15759" }, data: [120, 110, 100, "-", "-", "-", "-", 60, 55, 50, "-", "-", "-", "-", 30, 27, 25, "-", "-", "-", "-", 20, 17, 15, "-", "-", "-"] }
  - { name: Failure Detection, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#b07aa1" }, data: ["-", "-", "-", 20, 10, 0, "-", "-", "-", "-", 10, 5, 0, "-", "-", "-", "-", 5, 3, 0, "-", "-", "-", "-", 5, 3, 0] }
  - { name: Restart Timeout, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#f28e2c" }, data: ["-", "-", "-", 95, 95, 0, "-", "-", "-", "-", 45, 45, 0, "-", "-", "-", "-", 25, 25, 0, "-", "-", "-", "-", 15, 15, 0] }
  - { name: Replica Detection, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#edc949" }, data: [20, 10, 0, 20, 10, 0, "-", 10, 5, 0, 10, 5, 0, "-", 5, 3, 0, 5, 3, 0, "-", 5, 3, 0, 5, 3, 0] }
  - { name: Lock & Promote, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#59a14f" }, data: [2, 1, 0, 2, 1, 0, "-", 2, 1, 0, 2, 1, 0, "-", 2, 1, 0, 2, 1, 0, "-", 2, 1, 0, 2, 1, 0] }
  - { name: Health Check, type: bar, stack: main, z: 2, emphasis: { focus: series }, itemStyle: { color: "#4e79a7" }, data: [8, 6, 4, 8, 6, 4, "-", 6, 5, 3, 6, 5, 3, "-", 4, 3, 2, 4, 3, 2, "-", 2, 2, 1, 2, 2, 1] }
  - { name: RTO Total, type: bar, barGap: "-100%", barWidth: 16, z: 1, itemStyle: { color: "#888", opacity: 0 }, emphasis: { itemStyle: { opacity: 0 } }, data: [150, 127, 104, 145, 122, 4, "-", 78, 66, 53, 73, 61, 3, "-", 41, 34, 27, 41, 35, 2, "-", 29, 23, 16, 29, 24, 1] }
  - { name: RTO Budget, type: bar, barGap: "-100%", barWidth: 16, z: 0, itemStyle: { color: "rgba(0,0,0,0.08)" }, emphasis: { itemStyle: { color: "rgba(0,0,0,0.12)" } }, data: [150, 150, 150, 150, 150, 150, "-", 90, 90, 90, 90, 90, 90, "-", 45, 45, 45, 45, 45, 45, "-", 30, 30, 30, 30, 30, 30] }

Implementation

The four RTO modes differ in how the following 10 Patroni and HAProxy HA-related parameters are configured.

Patroni Parameters

• ttl: Leader lock TTL. Primary must renew within this time, otherwise lock expires and triggers election. Directly determines passive failure detection delay.
• loop_wait: Patroni main loop interval. Each loop performs one health check and state sync, affects failure discovery timeliness.
• retry_timeout: DCS operation retry timeout. During network partition, Patroni retries continuously within this period; after timeout, primary actively demotes to prevent split-brain.
• primary_start_timeout: Wait time for Patroni to attempt local restart after PG crash. After timeout, releases Leader lock and triggers failover.
• safety_margin: Watchdog safety margin. Ensures sufficient time to trigger system restart during failures, avoiding split-brain.

HAProxy Parameters

• inter: Health check interval in normal state, used when service status is stable.
• fastinter: Check interval during state transition, uses shorter interval to accelerate confirmation when state change detected.
• downinter: Check interval in DOWN state, uses this interval to probe recovery after service marked DOWN.
• rise: Consecutive successes required to mark UP. After new primary comes online, must pass rise consecutive checks before receiving traffic.
• fall: Consecutive failures required to mark DOWN. Service must fail fall consecutive times before being marked DOWN.

Key Constraint

Patroni core constraint: Ensures primary can complete demotion before TTL expires, preventing split-brain.

Data Summary

Recommendations

fast mode is suitable for scenarios with extremely high RTO requirements, but requires sufficiently good network quality (latency < 1ms, very low packet loss). Recommended only for same-rack or same-switch deployments, and should be thoroughly tested in production before enabling.

norm mode (default) is Pigsty’s default configuration, sufficient for the vast majority of same-datacenter deployments. In the model used by this page, the passive and active paths average about 34 and 35 seconds, while still providing a reasonable tolerance window against false failovers caused by network jitter.

safe mode is suitable for same-city cross-datacenter deployments with higher network latency or occasional jitter. The longer tolerance window effectively prevents false failovers from network jitter, making it the recommended configuration for cross-datacenter disaster recovery.

wide mode is suitable for cross-region or even cross-continent deployments with high network latency and possible public-network-level packet loss. In such scenarios, stability is more important than recovery speed, so an extremely wide tolerance window ensures very low false failover rate.

Typically you only need to set pg_rto to the mode name, and Pigsty will automatically configure Patroni and HAProxy parameters. The current template looks up pg_rto with pg_rto in pg_rto_plan; a numeric or unknown key falls back directly to norm. Do not treat that fallback as a supported “RTO in seconds” configuration.

The mode configuration actually loads the corresponding parameter set from pg_rto_plan. You can modify or override this configuration to implement custom RTO strategies.

pg_rto_plan:  # [ttl, loop, retry, start, margin, inter, fastinter, downinter, rise, fall]
  fast: [ 20  ,5  ,5  ,15 ,5  ,'1s' ,'0.5s' ,'1s' ,3 ,3 ]  # rto < 30s
  norm: [ 30  ,5  ,10 ,25 ,5  ,'2s' ,'1s'   ,'2s' ,3 ,3 ]  # rto < 45s
  safe: [ 60  ,10 ,20 ,45 ,10 ,'3s' ,'1.5s' ,'3s' ,3 ,3 ]  # rto < 90s
  wide: [ 120 ,20 ,30 ,95 ,15 ,'4s' ,'2s'   ,'4s' ,3 ,3 ]  # rto < 150s

4 - Service Access

Split read and write operations, route traffic correctly, and deliver PostgreSQL cluster capabilities reliably.

Service is an abstraction: it represents the form in which database clusters expose their capabilities externally, encapsulating underlying cluster details.

Services are crucial for stable access in production environments, showing their value during automatic failover in high availability clusters. Personal users typically don’t need to worry about this concept.

Personal Users

The concept of “service” is for production environments. Personal users with single-node clusters can skip the complexity and directly use instance names or IP addresses to access the database.

For example, Pigsty’s default single-node pg-meta.meta database can be connected directly using three different users:

psql postgres://dbuser_dba:[email protected]/meta     # Connect directly with DBA superuser
psql postgres://dbuser_meta:[email protected]/meta   # Connect with default business admin user
psql postgres://dbuser_view:DBUser.Viewer@pg-meta/meta     # Connect with default read-only user via instance domain name

Service Overview

In real-world production environments, we use primary-replica database clusters based on replication. Within a cluster, one and only one instance serves as the leader (primary) that can accept writes. Other instances (replicas) continuously fetch change logs from the cluster leader to stay synchronized. Replicas can also handle read-only requests, significantly offloading the primary in read-heavy, write-light scenarios. Therefore, distinguishing write requests from read-only requests is a common practice.

Additionally, for production environments with high-frequency, short-lived connections, we pool requests through connection pool middleware (Pgbouncer) to reduce connection and backend process creation overhead. However, for scenarios like ETL and change execution, we need to bypass the connection pool and directly access the database. Meanwhile, high-availability clusters may undergo failover during failures, causing cluster leadership changes. Therefore, high-availability database solutions require write traffic to automatically adapt to cluster leadership changes. These varying access needs (read-write separation, pooled vs. direct connections, failover auto-adaptation) ultimately lead to the abstraction of the Service concept.

Typically, database clusters must provide this most basic service:

• Read-write service (primary): Can read from and write to the database

For production database clusters, at least these two services should be provided:

• Read-write service (primary): Write data: Can only be served by the primary.
• Read-only service (replica): Read data: Can be served by replicas; falls back to primary when no replicas are available

Additionally, depending on specific business scenarios, there may be other services, such as:

• Default direct service (default): Allows (admin) users to bypass the connection pool and directly access the database
• Offline replica service (offline): Dedicated replica not serving online read traffic, used for ETL and analytical queries
• Sync replica service (standby): Read-only service with no replication delay, handled by synchronous standby/primary for read queries
• Delayed replica service (delayed): Access data from the same cluster as it was some time ago, handled by delayed replicas

Access Services

Pigsty’s service delivery boundary stops at the cluster’s HAProxy. Users can access these load balancers through various means.

The typical approach is to use DNS or VIP access, binding them to all or any number of load balancers in the cluster.

You can use different host & port combinations, which provide PostgreSQL service in different ways.

Host

Port

Pigsty uses different ports to distinguish pg services

Combinations

# Access via cluster domain (this example assumes a cluster VIP; without one, DNS resolves to the primary IP by default)
postgres://test@pg-test:5432/test # DNS -> L2 VIP -> primary direct connection
postgres://test@pg-test:6432/test # DNS -> L2 VIP -> primary connection pool -> primary
postgres://test@pg-test:5433/test # DNS -> L2 VIP -> HAProxy -> primary connection pool -> primary
postgres://test@pg-test:5434/test # DNS -> L2 VIP -> HAProxy -> replica connection pool -> replica
postgres://dbuser_dba@pg-test:5436/test # DNS -> L2 VIP -> HAProxy -> primary direct connection (for admin)
postgres://dbuser_stats@pg-test:5438/test # DNS -> L2 VIP -> HAProxy -> offline direct connection (for ETL/personal queries)

# Access via cluster VIP directly
postgres://[email protected]:5432/test # L2 VIP -> primary direct access
postgres://[email protected]:6432/test # L2 VIP -> primary connection pool -> primary
postgres://[email protected]:5433/test # L2 VIP -> HAProxy -> primary connection pool -> primary
postgres://[email protected]:5434/test # L2 VIP -> HAProxy -> replica connection pool -> replica
postgres://[email protected]:5436/test # L2 VIP -> HAProxy -> primary direct connection (for admin)
postgres://[email protected]:5438/test # L2 VIP -> HAProxy -> offline direct connection (for ETL/personal queries)

# Directly specify any cluster instance name
postgres://test@pg-test-1:5432/test # DNS -> database instance direct connection (singleton access)
postgres://test@pg-test-1:6432/test # DNS -> connection pool -> database
postgres://test@pg-test-1:5433/test # DNS -> HAProxy -> connection pool -> database read/write
postgres://test@pg-test-1:5434/test # DNS -> HAProxy -> connection pool -> database read-only
postgres://dbuser_dba@pg-test-1:5436/test # DNS -> HAProxy -> database direct connection
postgres://dbuser_stats@pg-test-1:5438/test # DNS -> HAProxy -> database offline read/write

# Directly specify any cluster instance IP access
postgres://[email protected]:5432/test # Database instance direct connection (directly specify instance, no automatic traffic distribution)
postgres://[email protected]:6432/test # Connection pool -> database
postgres://[email protected]:5433/test # HAProxy -> connection pool -> database read/write
postgres://[email protected]:5434/test # HAProxy -> connection pool -> database read-only
postgres://[email protected]:5436/test # HAProxy -> database direct connection
postgres://[email protected]:5438/test # HAProxy -> database offline read-write

# Smart client: read/write separation via URL
postgres://[email protected]:6432,10.10.10.12:6432,10.10.10.13:6432/test?target_session_attrs=primary
postgres://[email protected]:6432,10.10.10.12:6432,10.10.10.13:6432/test?target_session_attrs=prefer-standby