Skip to content
logo
Percona Product Documentation
High availability
Initializing search
    percona/postgresql-docs
    percona/postgresql-docs
    • Home
      • Release notes index
      • Percona Distribution for PostgreSQL 14.9 (2023-08-30)
      • Percona Distribution for PostgreSQL 14.8 (2023-06-28)
      • Percona Distribution for PostgreSQL 14.7 Update (2023-05-22)
      • Percona Distribution for PostgreSQL 14.7 (2023-03-23)
      • Percona Distribution for PostgreSQL 14.6 (2022-11-23)
      • Percona Distribution for PostgreSQL 14.5 (2022-09-05)
      • Percona Distribution for PostgreSQL 14.4 (2022-06-27)
      • Percona Distribution for PostgreSQL 14.3 (2022-06-01)
      • Percona Distribution for PostgreSQL 14.2 Second Update (2022-05-05)
      • Percona Distribution for PostgreSQL 14.2 Update (2022-04-14)
      • Percona Distribution for PostgreSQL 14.2 (2022-03-16)
      • Percona Distribution for PostgreSQL 14.1 Second Update (2021-12-07)
      • Percona Distribution for PostgreSQL 14.1 Update (2021-12-02)
      • Percona Distribution for PostgreSQL 14.1 (2021-11-22)
        • Overview
        • Install on Debian and Ubuntu
        • Install on RHEL and derivatives
        • Enable Percona Distribution for PostgreSQL extensions
        • Repositories overview
      • Migrate from PostgreSQL to Percona Distribution for PostgreSQL
      • Upgrading Percona Distribution for PostgreSQL from 13 to 14
      • Minor Upgrade of Percona Distribution for PostgreSQL
      • pg-stat-monitor
        • High availability
          • Streaming replication
            • Why native streaming replication is not enough
          • Patroni
            • Key benefits of Patroni:
          • Architecture layout
            • Components
            • How components work together
          • Deployment
          • Testing
        • Deploying on Debian or Ubuntu
        • Deploying on RHEL or CentOS
        • Testing the Patroni PostgreSQL Cluster
        • Backup and disaster recovery
        • Deploying backup and disaster recovery solution in Percona Distribution for PostgreSQL
        • Overview
        • Deployment
        • Query spatial data
        • Upgrade spatial database
      • LDAP authentication
    • Uninstall
    • Licensing
    • Trademark policy

    • Streaming replication
      • Why native streaming replication is not enough
    • Patroni
      • Key benefits of Patroni:
    • Architecture layout
      • Components
      • How components work together
    • Deployment
    • Testing

    High Availability in PostgreSQL with Patroni¶

    Summary

    • Solution overview
    • Cluster deployment
    • Testing the cluster

    PostgreSQL has been widely adopted as a modern, high-performance transactional database. A highly available PostgreSQL cluster can withstand failures caused by network outages, resource saturation, hardware failures, operating system crashes or unexpected reboots. Such cluster is often a critical component of the enterprise application landscape, where four nines of availability is a minimum requirement.

    There are several methods to achieve high availability in PostgreSQL. In this description we use Patroni - the open-source extension to facilitate and manage the deployment of high availability in PostgreSQL.

    High availability methods

    There are a few methods for achieving high availability with PostgreSQL:

    • shared disk failover,
    • file system replication,
    • trigger-based replication,
    • statement-based replication,
    • logical replication,
    • Write-Ahead Log (WAL) shipping, and
    • streaming replication

    Streaming replication¶

    Streaming replication is part of Write-Ahead Log shipping, where changes to the WALs are immediately made available to standby replicas. With this approach, a standby instance is always up-to-date with changes from the primary node and can assume the role of primary in case of a failover.

    Why native streaming replication is not enough¶

    Although the native streaming replication in PostgreSQL supports failing over to the primary node, it lacks some key features expected from a truly highly-available solution. These include:

    • No consensus-based promotion of a “leader” node during a failover
    • No decent capability for monitoring cluster status
    • No automated way to bring back the failed primary node to the cluster
    • A manual or scheduled switchover is not easy to manage

    To address these shortcomings, there are a multitude of third-party, open-source extensions for PostgreSQL. The challenge for a database administrator here is to select the right utility for the current scenario.

    Percona Distribution for PostgreSQL solves this challenge by providing the Patroni extension for achieving PostgreSQL high availability.

    Patroni¶

    Patroni provides a template-based approach to create highly available PostgreSQL clusters. Running atop the PostgreSQL streaming replication process, it integrates with watchdog functionality to detect failed primary nodes and take corrective actions to prevent outages. Patroni also relies on a pluggable configuration store to manage distributed, multi-node cluster configuration and store the information about the cluster health there. Patroni comes with REST APIs to monitor and manage the cluster and has a command-line utility called patronictl that helps manage switchovers and failure scenarios.

    Key benefits of Patroni:¶

    • Continuous monitoring and automatic failover
    • Manual/scheduled switchover with a single command
    • Built-in automation for bringing back a failed node to cluster again.
    • REST APIs for entire cluster configuration and further tooling.
    • Provides infrastructure for transparent application failover
    • Distributed consensus for every action and configuration.
    • Integration with Linux watchdog for avoiding split-brain syndrome.

    Architecture layout¶

    The following diagram shows the architecture of a three-node PostgreSQL cluster with a single-leader node.

    Architecture of the three-node, single primary PostgreSQL cluster

    Components¶

    The components in this architecture are:

    • PostgreSQL nodes
    • Patroni provides a template for configuring a highly available PostgreSQL cluster.

    • ETCD is a Distributed Configuration store that stores the state of the PostgreSQL cluster.

    • HAProxy is the load balancer for the cluster and is the single point of entry to client applications.

    • Softdog - a watchdog utility which is used by Patroni to check the nodes’ health. Watchdog resets the whole system when it doesn’t receive a keepalive heartbeat within a specified time.

    How components work together¶

    Each PostgreSQL instance in the cluster maintains consistency with other members through streaming replication. Each instance hosts Patroni - a cluster manager that monitors the cluster health. Patroni relies on the operational ETCD cluster to store the cluster configuration and sensitive data about the cluster health there.

    Patroni periodically sends heartbeat requests with the cluster status to ETCD. ETCD writes this information to disk and sends the response back to Patroni. If the current primary fails to renew its status as leader within the specified timeout, Patroni updates the state change in ETCD, which uses this information to elect the new primary and keep the cluster up and running.

    The connections to the cluster do not happen directly to the database nodes but are routed via a connection proxy like HAProxy. This proxy determines the active node by querying the Patroni REST API.

    Deployment¶

    Use the following links to navigate to the setup instructions relevant to your operating system:

    • Deploy on Debian or Ubuntu
    • Deploy on Red Hat Enterprise Linux or CentOS

    Testing¶

    See the Testing PostgreSQL cluster for the guidelines on how to test your PostgreSQL cluster for replication, failure, switchover.

    Contact Us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.


    Last update: July 26, 2023
    Created: December 15, 2021
    Percona LLC, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better. Read more about Percona Cookie Policy.