Ensuring Zero-Downtime Postgres Failover with Patroni and HAProxy

Introduction

In the world of database management, ensuring high availability is critical, especially for applications that require seamless operations. PostgreSQL, while robust and feature-rich, can pose challenges for single-instance setups, particularly in failover scenarios. This article guides you through implementing a zero-downtime failover solution utilizing Patroni and HAProxy on a Vultr instance. This setup is especially beneficial for solo developers or small teams that need a reliable database infrastructure without incurring high costs or complexities.

Understanding the Architecture

What is Patroni?

Patroni is an open-source tool that simplifies the management of PostgreSQL high-availability clusters. It automates the failover process, managing leader elections and database replication, reducing the overhead typically associated with these tasks.

What is HAProxy?

HAProxy is a high-performance TCP/HTTP load balancer that can route traffic between multiple PostgreSQL instances. It provides a reliable method to handle incoming database queries, offering a single endpoint to client applications while distributing requests to healthy backend databases.

The Combined Approach

When utilized together, Patroni and HAProxy can manage a high-availability cluster effectively. Patroni handles the PostgreSQL instance’s health and failover logic, while HAProxy ensures that client applications are always directed to the active database instance.

Setting Up the Environment

Prerequisites

Before getting started, ensure you have:

Install Necessary Packages

You will need to install PostgreSQL, Patroni, and HAProxy on your Vultr instance. Begin by updating your system and installing the required software:

sudo apt-get update
sudo apt-get install postgresql patroni haproxy

Configuring Patroni

Patroni Configuration File

You need to configure Patroni to manage your PostgreSQL instance. Create a configuration file (e.g., patroni.yml) with the following content:

scope: mycluster
namespace: /db/
name: postgresql-01

restapi:
  listen: 0.0.0.0:8008
  connect_address: postgresql-01:8008

etcd:
  host: 127.0.0.1:2379

bootstrap:
  dsn: postgres://postgres:yourpassword@localhost:5432/postgres?sslmode=disable
  initdb:
    - encoding: UTF8
    - locale: en_US.UTF-8

postgresql:
  listen: 0.0.0.0:5432
  connect_address: postgresql-01:5432
  data_dir: /var/lib/postgresql/data
  bin_dir: /usr/lib/postgresql/12/bin
  parameters:
    max_connections: 100
    shared_buffers: 256MB

Note: Modify yourpassword, postgresql, and version numbers as needed.

Starting Patroni

With your configuration file ready, you can start Patroni:

patroni /path/to/patroni.yml

Monitor the logs to ensure that Patroni starts without issues and can connect to your PostgreSQL instance.

Configuring HAProxy

HAProxy Configuration

Next, you need to configure HAProxy to direct traffic to the PostgreSQL instances controlled by Patroni. Open the HAProxy configuration file (typically located at /etc/haproxy/haproxy.cfg) and set it up as follows:

frontend postgres_front
    bind *:5432
    default_backend postgres_back

backend postgres_back
    option httpchk
    server postgresql-01 127.0.0.1:5432 check
    server postgresql-02 127.0.0.1:6432 check backup

Enabling and Starting HAProxy

After configuring HAProxy, enable it to start on boot and then start the service:

sudo systemctl enable haproxy
sudo systemctl start haproxy

Testing the Setup

With Patroni and HAProxy configured, it’s essential to test the setup:

Conclusion

Implementing a zero-downtime failover system for PostgreSQL on Vultr using Patroni and HAProxy can greatly enhance your application’s reliability. This approach mitigates the risk of downtime