Strumenti per il failover automatico (Patroni)

Imagine your main PostgreSQL database suddenly stops working. Without a plan, your application goes down, leading to unhappy users and lost business.

Automatic failover is a strategy to prevent this. It quickly switches to a backup database (a 'standby') when the primary fails, often without any manual intervention.

This ensures your service stays available, minimizing downtime and improving reliability.

Meet Patroni – a powerful, open-source tool designed to manage highly available PostgreSQL clusters.

Patroni acts as a 'template' or 'framework' for PostgreSQL HA, handling complex tasks like primary election, failover, and standby management automatically.

It simplifies the setup and maintenance of robust, fault-tolerant PostgreSQL systems.

Patroni doesn't work alone. It orchestrates several components to achieve high availability:

• PostgreSQL Instances: The actual database servers (primary and standbys).
• Patroni Agents: A process running on each PostgreSQL node, monitoring its health and communicating with the cluster.
• Distributed Consensus Store (DCS): A reliable external service (like etcd, Consul, or ZooKeeper) that stores the cluster state and facilitates primary election.

These components work together to ensure a healthy primary is always available.

The Distributed Consensus Store (DCS) is Patroni's brain. It's a critical component for maintaining a consistent view of the cluster state across all nodes.

Popular DCS options include:

• etcd: A highly-available key-value store.
• Consul: A service networking solution with a key-value store.
• ZooKeeper: A centralized service for maintaining configuration information.

Patroni uses the DCS to store information like the current primary, replication status, and configuration, enabling robust primary election and failover.

Each PostgreSQL server in your cluster runs a Patroni agent. This agent is responsible for:

• Monitoring the health of its local PostgreSQL instance.
• Communicating its status to the DCS.
• Participating in primary election.
• Performing actions like promoting a standby to primary or configuring replication.

The Patroni agent ensures that the PostgreSQL instance it manages is always in the correct state, whether it's a primary or a standby.

A typical Patroni setup involves:

1. Installing Patroni and PostgreSQL on multiple servers.
2. Configuring a DCS (e.g., a 3-node etcd cluster).
3. Creating a patroni.yml configuration file for each PostgreSQL node.
4. Starting Patroni on all nodes, allowing it to initialize PostgreSQL and form the cluster.

Patroni then automatically handles the initial primary election and sets up streaming replication.

Patroni is configured using a YAML file, typically named patroni.yml. Here's a simplified example showing key sections:

This file defines the cluster name, REST API settings, and PostgreSQL-specific parameters.

You can check the health and status of your Patroni cluster using the patronictl command-line tool.

The patronictl list command provides a quick overview of all cluster members, their roles (primary/replica), status, and replication lag.

This helps you confirm that your HA setup is running as expected.

When the primary PostgreSQL server fails, Patroni initiates an automatic failover:

1. The Patroni agent on the failed primary stops updating its status in the DCS.
2. Other Patroni agents detect this absence through the DCS.
3. The remaining healthy standbys compete to become the new primary.
4. A new primary is elected by the DCS.
5. The new primary is promoted, and other standbys are reconfigured to follow it.

This entire process is automated, ensuring minimal disruption to your application.

In this lesson, we explored Patroni, a powerful tool for achieving automatic failover and high availability in PostgreSQL.

• Patroni orchestrates PostgreSQL instances, using Patroni agents on each node.
• It relies on a Distributed Consensus Store (DCS) like etcd for cluster state and primary election.
• This architecture ensures that if a primary fails, a new one is automatically promoted, minimizing downtime.

Understanding Patroni is key to building resilient PostgreSQL systems.