Disaster Recovery 101: Architecting Auto-Failovers for PostgreSQL and PHP Deployments on Linode

<?php
$host = 'your_haproxy_vip_or_dns_name'; // e.g., 'pg-cluster.yourdomain.com' or a VIP IP
$port = '5432'; // Or 5433 for read-only replicas
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';
$dsn = "pgsql:host={$host};port={$port};dbname={$db}";

try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
    echo "Connected successfully to PostgreSQL!\n";

    // Example of a read-only query if connecting to replica port
    // $stmt = $pdo->query("SELECT pg_is_in_recovery()");
    // $is_replica = $stmt->fetchColumn();
    // echo "Is in recovery: " . ($is_replica ? 'Yes' : 'No') . "\n";

} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// For write operations, ensure you are connected to the leader.
// If using HAProxy with a VIP, this connection string points to the VIP.
// If using DNS SRV, the application resolves the SRV record.
?>

If you are using HAProxy for read-only replicas on port 5433, your DSN would change accordingly:

$port = '5433'; // For read-only replicas
$dsn = "pgsql:host={$host};port={$port};dbname={$db}";

Simulating a Failover and Testing

To test the failover mechanism, you can simulate a failure of the current primary PostgreSQL node. The safest way to do this is to stop the Patroni process on the primary node.

# On the current primary node
sudo systemctl stop patroni

Within seconds, Patroni on the other nodes will detect the primary’s absence. etcd will facilitate a new leader election, and one of the replicas will be promoted to primary. Patroni will reconfigure the remaining replicas to follow the new primary.

Monitor the logs of your Patroni instances and check the Patroni REST API (e.g., `curl http://<REPLICA_IP>:8008/patroni`) to confirm the new leader. Your PHP application, if configured with a load balancer or DNS SRV records, should automatically connect to the new primary without manual intervention.

To bring the old primary back online, start the Patroni service again:

# On the former primary node
sudo systemctl start patroni

Patroni will re-join the cluster. Since it was the primary before, it will likely be configured as a replica of the current primary (unless you have specific `nofailover` tags set). You can verify its role via the Patroni API.

Production Considerations and Enhancements

This setup provides a solid foundation for PostgreSQL HA. For production environments, consider:

Monitoring: Implement comprehensive monitoring for Patroni, etcd, and PostgreSQL. Tools like Prometheus and Grafana are excellent for this.
Backups: Automate regular PostgreSQL backups (e.g., using pg_dump or pg_basebackup with tools like Barman or pgBackRest). Ensure backups are stored off-site.
Replication Slots: Monitor replication slot usage to prevent WAL files from filling up disk space if a replica is down for an extended period.
Connection Pooling: For high-traffic applications, use a connection pooler like PgBouncer. It can be configured to connect to the HAProxy endpoint or directly to the cluster, and it can help manage connections more efficiently.
Security: Harden your etcd and PostgreSQL instances. Use TLS for etcd communication and consider SSL for PostgreSQL connections. Restrict access to Patroni’s REST API.
Automated Deployment: Use configuration management tools (Ansible, Chef, Puppet) or container orchestration (Kubernetes) to automate the deployment and management of Patroni and PostgreSQL clusters.
Read-Only Traffic Management: For read-heavy workloads, fine-tune your HAProxy configuration or use dedicated read-replica management tools to ensure optimal distribution of read queries.
Disaster Recovery (DR) Site: For true disaster recovery, consider replicating your PostgreSQL cluster to a separate geographic region. Patroni can manage cross-region replication with appropriate network configuration.

By implementing Patroni and a robust connection strategy for your PHP application, you significantly enhance the resilience and availability of your PostgreSQL database, minimizing downtime and ensuring business continuity.

_postgresql._tcp.yourdomain.com.  300  IN  SRV  0 5 5432 pgnode1.yourdomain.com.
_postgresql._tcp.yourdomain.com.  300  IN  SRV  1 5 5432 pgnode2.yourdomain.com.
_postgresql._tcp.yourdomain.com.  300  IN  SRV  2 5 5432 pgnode3.yourdomain.com.

The priority and weight fields help DNS clients choose a server. A script would need to dynamically change the priority or remove/add records to point to the leader.

PHP PDO Connection Example

Assuming you’re using HAProxy with a VIP or DNS SRV records, your PHP PDO connection string would look something like this:

<?php
$host = 'your_haproxy_vip_or_dns_name'; // e.g., 'pg-cluster.yourdomain.com' or a VIP IP
$port = '5432'; // Or 5433 for read-only replicas
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';
$dsn = "pgsql:host={$host};port={$port};dbname={$db}";

try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
    echo "Connected successfully to PostgreSQL!\n";

    // Example of a read-only query if connecting to replica port
    // $stmt = $pdo->query("SELECT pg_is_in_recovery()");
    // $is_replica = $stmt->fetchColumn();
    // echo "Is in recovery: " . ($is_replica ? 'Yes' : 'No') . "\n";

} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// For write operations, ensure you are connected to the leader.
// If using HAProxy with a VIP, this connection string points to the VIP.
// If using DNS SRV, the application resolves the SRV record.
?>

If you are using HAProxy for read-only replicas on port 5433, your DSN would change accordingly:

$port = '5433'; // For read-only replicas
$dsn = "pgsql:host={$host};port={$port};dbname={$db}";

Simulating a Failover and Testing

To test the failover mechanism, you can simulate a failure of the current primary PostgreSQL node. The safest way to do this is to stop the Patroni process on the primary node.

# On the current primary node
sudo systemctl stop patroni

To bring the old primary back online, start the Patroni service again:

# On the former primary node
sudo systemctl start patroni

Production Considerations and Enhancements

This setup provides a solid foundation for PostgreSQL HA. For production environments, consider:

Monitoring: Implement comprehensive monitoring for Patroni, etcd, and PostgreSQL. Tools like Prometheus and Grafana are excellent for this.
Backups: Automate regular PostgreSQL backups (e.g., using pg_dump or pg_basebackup with tools like Barman or pgBackRest). Ensure backups are stored off-site.
Replication Slots: Monitor replication slot usage to prevent WAL files from filling up disk space if a replica is down for an extended period.
Connection Pooling: For high-traffic applications, use a connection pooler like PgBouncer. It can be configured to connect to the HAProxy endpoint or directly to the cluster, and it can help manage connections more efficiently.
Security: Harden your etcd and PostgreSQL instances. Use TLS for etcd communication and consider SSL for PostgreSQL connections. Restrict access to Patroni’s REST API.
Automated Deployment: Use configuration management tools (Ansible, Chef, Puppet) or container orchestration (Kubernetes) to automate the deployment and management of Patroni and PostgreSQL clusters.
Read-Only Traffic Management: For read-heavy workloads, fine-tune your HAProxy configuration or use dedicated read-replica management tools to ensure optimal distribution of read queries.
Disaster Recovery (DR) Site: For true disaster recovery, consider replicating your PostgreSQL cluster to a separate geographic region. Patroni can manage cross-region replication with appropriate network configuration.

# /etc/haproxy/haproxy.cfg

global
log /dev/log local0
log /dev/log local1 notice
chroot /var/lib/haproxy
stats socket /run/haproxy/admin.sock mode 660 level admin expose-fd listeners
stats timeout 30s
user haproxy
group haproxy
daemon

defaults
log global
mode tcp
option tcplog
option dontlognull
timeout connect 5000
timeout client 50000
timeout server 50000

listen patroni_api
bind *:8007
mode http
# This backend will query Patroni for the leader
# We assume Patroni API is accessible on port 8008 on each PG node
# HAProxy will cycle through these nodes to find the leader
balance roundrobin
server pgnode1 <PG_NODE_1_IP>:8008 check port 8008 inter 2s fall 3 rise 2
server pgnode2 <PG_NODE_2_IP>:8008 check port 8008 inter 2s fall 3 rise 2
server pgnode3 <PG_NODE_3_IP>:8008 check port 8008 inter 2s fall 3 rise 2

listen pg_cluster
bind *:5432
mode tcp
# This backend directs traffic to the current PostgreSQL leader
# It uses the Patroni API (via the patroni_api listener) to find the leader
# and then connects to the PostgreSQL port (5432) on that leader.
# This requires a bit of cleverness or a dedicated script/tool.
# A simpler approach is to have a DNS SRV record or a service discovery mechanism.
# For a direct HAProxy solution, we can use a script to update backend servers.
# A more robust approach is to use a dedicated tool like pg_auto_failover's proxy or a custom solution.

# --- Simplified HAProxy approach using a script to update backend ---
# This is a conceptual example. A real-world implementation might involve
# a script that periodically polls patroni_api and updates this backend.
# For simplicity, we'll point to the Patroni API listener and expect it to
# return the leader's IP. This is NOT how HAProxy typically works for TCP.
# A better HAProxy config would involve a frontend that queries the API
# and then dynamically sets the backend server.

# A more practical HAProxy setup for PostgreSQL HA often involves:
# 1. A frontend listening on 5432.
# 2. A backend that polls Patroni API (e.g., via a script or a dedicated tool).
# 3. The script/tool determines the leader IP and updates HAProxy's backend server list.

# Example using a script to manage backend servers (conceptual):
# Let's assume a script `update_pg_backend.sh` exists that updates
# /etc/haproxy/conf.d/pg_backends.cfg with the correct leader.
# include /etc/haproxy/conf.d/*.cfg

# --- Alternative: DNS SRV Records ---
# Configure your DNS to have SRV records for _postgresql._tcp.yourdomain.com
# pointing to your PostgreSQL nodes. Your PHP application's PDO connection
# string can then use this SRV record. This is often simpler.

# --- Direct connection to Patroni API for leader IP (less common for direct TCP) ---
# This is a simplified example and might not be robust.
# The patroni_api listener above should ideally return the leader's IP.
# We'll try to use that information.
# This requires a custom HAProxy configuration or a helper tool.

# For a robust HAProxy setup, consider using a tool like `patroni-vip` or
# a custom script that monitors Patroni and updates HAProxy's backend.

# --- A more direct, but less dynamic, HAProxy approach ---
# This assumes you know your nodes and their roles, and HAProxy will
# check their health. This doesn't dynamically find the leader.
# For dynamic leader discovery, a tool is usually needed.

# Let's assume a simple setup where we point to all nodes and rely on
# Patroni's API to tell us which one is the leader.
# This is still not ideal for TCP.

# --- Recommended approach: Use a dedicated tool or DNS ---
# For this example, we'll assume a DNS SRV record or a tool is used.
# If using a tool like `patroni-vip`, it would manage a virtual IP.

# If you must use HAProxy directly for TCP failover without external tools:
# You'd need a frontend that queries the patroni_api listener, gets the leader IP,
# and then routes traffic to that IP on port 5432. This is complex with standard HAProxy.

# Let's simplify and assume a DNS SRV record is used by the application.
# If HAProxy is used, it's typically for read-only replicas or a VIP.

# For a write endpoint, a VIP managed by `patroni-vip` or similar is often best.
# If using HAProxy as the *only* entry point:
# You'd need a frontend that queries the patroni_api listener and then
# dynamically sets the backend server. This is advanced.

# --- Simplified HAProxy for Read-Only Replicas ---
# This is a common use case for HAProxy with PostgreSQL HA.
# We'll configure a separate backend for read-only traffic.

listen pg_replicas
bind *:5433 # Port for read-only replicas
mode tcp
balance roundrobin
# Add all your replica nodes here. Patroni ensures they are in sync.
# You'll need to manually update this if nodes change.
server pgnode1_replica <PG_NODE_1_IP>:5432 check port 5432 inter 2s fall 3 rise 2
server pgnode2_replica <PG_NODE_2_IP>:5432 check port 5432 inter 2s fall 3 rise 2
# Add more replicas as needed

Explanation:

The patroni_api listener on port 8007 is a proxy to the Patroni REST API endpoints on your PostgreSQL nodes. HAProxy will try to reach each node’s API to find the current leader.
The pg_cluster listener (conceptually) should direct writes to the leader. A robust solution for this often involves a Virtual IP (VIP) managed by tools like patroni-vip or keepalived, which are updated by Patroni.
The pg_replicas listener on port 5433 is a standard HAProxy setup for read-only replicas. It distributes read traffic across your available replicas.

After configuring HAProxy, restart it:

sudo systemctl restart haproxy

Option 2: DNS SRV Records

A more modern and often simpler approach is to leverage DNS SRV records. Your application’s connection string can reference a service name, and DNS will resolve it to the current leader’s IP and port.

You’ll need a mechanism to update these SRV records when a failover occurs. This can be achieved by a script that monitors Patroni’s leader election and updates your DNS provider’s API (e.g., Linode API, AWS Route 53 API, etc.).

Example DNS SRV record setup (syntax varies by DNS provider):

_postgresql._tcp.yourdomain.com.  300  IN  SRV  0 5 5432 pgnode1.yourdomain.com.
_postgresql._tcp.yourdomain.com.  300  IN  SRV  1 5 5432 pgnode2.yourdomain.com.
_postgresql._tcp.yourdomain.com.  300  IN  SRV  2 5 5432 pgnode3.yourdomain.com.

The priority and weight fields help DNS clients choose a server. A script would need to dynamically change the priority or remove/add records to point to the leader.

PHP PDO Connection Example

Assuming you’re using HAProxy with a VIP or DNS SRV records, your PHP PDO connection string would look something like this:

<?php
$host = 'your_haproxy_vip_or_dns_name'; // e.g., 'pg-cluster.yourdomain.com' or a VIP IP
$port = '5432'; // Or 5433 for read-only replicas
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';
$dsn = "pgsql:host={$host};port={$port};dbname={$db}";

try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
    echo "Connected successfully to PostgreSQL!\n";

    // Example of a read-only query if connecting to replica port
    // $stmt = $pdo->query("SELECT pg_is_in_recovery()");
    // $is_replica = $stmt->fetchColumn();
    // echo "Is in recovery: " . ($is_replica ? 'Yes' : 'No') . "\n";

} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// For write operations, ensure you are connected to the leader.
// If using HAProxy with a VIP, this connection string points to the VIP.
// If using DNS SRV, the application resolves the SRV record.
?>

If you are using HAProxy for read-only replicas on port 5433, your DSN would change accordingly:

$port = '5433'; // For read-only replicas
$dsn = "pgsql:host={$host};port={$port};dbname={$db}";

Simulating a Failover and Testing

To test the failover mechanism, you can simulate a failure of the current primary PostgreSQL node. The safest way to do this is to stop the Patroni process on the primary node.

# On the current primary node
sudo systemctl stop patroni

To bring the old primary back online, start the Patroni service again:

# On the former primary node
sudo systemctl start patroni

Production Considerations and Enhancements

This setup provides a solid foundation for PostgreSQL HA. For production environments, consider:

Monitoring: Implement comprehensive monitoring for Patroni, etcd, and PostgreSQL. Tools like Prometheus and Grafana are excellent for this.
Backups: Automate regular PostgreSQL backups (e.g., using pg_dump or pg_basebackup with tools like Barman or pgBackRest). Ensure backups are stored off-site.
Replication Slots: Monitor replication slot usage to prevent WAL files from filling up disk space if a replica is down for an extended period.
Connection Pooling: For high-traffic applications, use a connection pooler like PgBouncer. It can be configured to connect to the HAProxy endpoint or directly to the cluster, and it can help manage connections more efficiently.
Security: Harden your etcd and PostgreSQL instances. Use TLS for etcd communication and consider SSL for PostgreSQL connections. Restrict access to Patroni’s REST API.
Automated Deployment: Use configuration management tools (Ansible, Chef, Puppet) or container orchestration (Kubernetes) to automate the deployment and management of Patroni and PostgreSQL clusters.
Read-Only Traffic Management: For read-heavy workloads, fine-tune your HAProxy configuration or use dedicated read-replica management tools to ensure optimal distribution of read queries.
Disaster Recovery (DR) Site: For true disaster recovery, consider replicating your PostgreSQL cluster to a separate geographic region. Patroni can manage cross-region replication with appropriate network configuration.

[patroni]
scope: my_pg_cluster
restapi:
  listen: 0.0.0.0:8008
  connect_address: <NODE_IP>:8008

etcd:
  host: <ETCD_HOST_1>:2379, <ETCD_HOST_2>:2379, <ETCD_HOST_3>:2379
  protocol: http

# PostgreSQL configuration
postgresql:
  listen: 0.0.0.0:5432
  connect_address: <NODE_IP>:5432
  data_dir: /var/lib/postgresql/14/main
  bin_dir: /usr/lib/postgresql/14/bin
  pg_hba:
    - host replication replicator 0.0.0.0/0 md5
    - host all all 0.0.0.0/0 md5
  replication:
    username: replicator
    password: <REPLICATION_PASSWORD>
  parameters:
    max_connections: 100
    shared_buffers: 128MB
    effective_cache_size: 384MB
    maintenance_work_mem: 64MB
    wal_level: replica
    wal_sync_method: fsync
    wal_writer_delay: 200ms
    checkpoint_timeout: 5min
    max_wal_senders: 10
    max_replication_slots: 10
    hot_standby: "on"
    default_transaction_isolation: read committed
    log_line_prefix: '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
    log_statement: 'ddl'
    log_replication_commands: 'on'

# Tags for node identification
tags:
  nofailover: false
  clone: false

Important Notes:

Replace <NODE_IP> with the actual IP address of the PostgreSQL node.
Replace <ETCD_HOST_x> with the IP addresses of your etcd cluster members.
Replace <REPLICATION_PASSWORD> with a strong password for the replication user.
Ensure the data_dir and bin_dir paths are correct for your PostgreSQL version and installation.
The pg_hba configuration allows replication and general access. Adjust as per your security requirements.
The tags section is crucial. nofailover: false means this node is eligible for failover.

Initializing PostgreSQL and Starting Patroni

On the *first* node you configure, you’ll need to initialize the PostgreSQL cluster. Patroni can do this for you. Ensure the PostgreSQL service is stopped or not running before proceeding.

# On the first node only
sudo patroni /etc/patroni/patroni.yml

This command will start Patroni. If it’s the first node to come online and `clone: false` is set, Patroni will initialize a new PostgreSQL cluster, create the replication user, and register itself as the leader in etcd. Subsequent nodes, when started with the same configuration, will detect the existing cluster in etcd, configure themselves as replicas using streaming replication, and join the cluster.

For subsequent nodes, simply run the same command:

# On all other nodes
sudo patroni /etc/patroni/patroni.yml

You can monitor the cluster status via Patroni’s REST API or by inspecting etcd. For example, to check the leader:

curl http://<NODE_IP>:8008/patroni

Integrating PHP Applications with the HA PostgreSQL Cluster

Your PHP application needs to be aware of the HA PostgreSQL setup. Directly connecting to a single IP address is no longer sufficient. The application should connect to the *leader* of the PostgreSQL cluster. Patroni provides a REST API endpoint that can return the current leader’s address. A common pattern is to use a load balancer or a DNS-based approach that queries Patroni’s API.

Option 1: Using a Load Balancer (HAProxy)

HAProxy can be configured to query Patroni’s API for the leader and direct traffic accordingly. This provides a stable endpoint for your application.

Install HAProxy on a dedicated server or one of your application servers:

sudo apt update
sudo apt install -y haproxy

Configure HAProxy. The key is the `check port 8008` and the `http-check expect status 200` which will query the Patroni API. We’ll also add a backend for read-only replicas.

# /etc/haproxy/haproxy.cfg

defaults
log global
mode tcp
option tcplog
option dontlognull
timeout connect 5000
timeout client 50000
timeout server 50000

# For a robust HAProxy setup, consider using a tool like `patroni-vip` or
# a custom script that monitors Patroni and updates HAProxy's backend.

# Let's assume a simple setup where we point to all nodes and rely on
# Patroni's API to tell us which one is the leader.
# This is still not ideal for TCP.

# --- Recommended approach: Use a dedicated tool or DNS ---
# For this example, we'll assume a DNS SRV record or a tool is used.
# If using a tool like `patroni-vip`, it would manage a virtual IP.

# Let's simplify and assume a DNS SRV record is used by the application.
# If HAProxy is used, it's typically for read-only replicas or a VIP.

# --- Simplified HAProxy for Read-Only Replicas ---
# This is a common use case for HAProxy with PostgreSQL HA.
# We'll configure a separate backend for read-only traffic.

Explanation:

The patroni_api listener on port 8007 is a proxy to the Patroni REST API endpoints on your PostgreSQL nodes. HAProxy will try to reach each node’s API to find the current leader.
The pg_cluster listener (conceptually) should direct writes to the leader. A robust solution for this often involves a Virtual IP (VIP) managed by tools like patroni-vip or keepalived, which are updated by Patroni.
The pg_replicas listener on port 5433 is a standard HAProxy setup for read-only replicas. It distributes read traffic across your available replicas.

After configuring HAProxy, restart it:

sudo systemctl restart haproxy

Option 2: DNS SRV Records

Example DNS SRV record setup (syntax varies by DNS provider):

_postgresql._tcp.yourdomain.com.  300  IN  SRV  0 5 5432 pgnode1.yourdomain.com.
_postgresql._tcp.yourdomain.com.  300  IN  SRV  1 5 5432 pgnode2.yourdomain.com.
_postgresql._tcp.yourdomain.com.  300  IN  SRV  2 5 5432 pgnode3.yourdomain.com.

The priority and weight fields help DNS clients choose a server. A script would need to dynamically change the priority or remove/add records to point to the leader.

PHP PDO Connection Example

Assuming you’re using HAProxy with a VIP or DNS SRV records, your PHP PDO connection string would look something like this:

<?php
$host = 'your_haproxy_vip_or_dns_name'; // e.g., 'pg-cluster.yourdomain.com' or a VIP IP
$port = '5432'; // Or 5433 for read-only replicas
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';
$dsn = "pgsql:host={$host};port={$port};dbname={$db}";

try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
    echo "Connected successfully to PostgreSQL!\n";

    // Example of a read-only query if connecting to replica port
    // $stmt = $pdo->query("SELECT pg_is_in_recovery()");
    // $is_replica = $stmt->fetchColumn();
    // echo "Is in recovery: " . ($is_replica ? 'Yes' : 'No') . "\n";

} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// For write operations, ensure you are connected to the leader.
// If using HAProxy with a VIP, this connection string points to the VIP.
// If using DNS SRV, the application resolves the SRV record.
?>

If you are using HAProxy for read-only replicas on port 5433, your DSN would change accordingly:

$port = '5433'; // For read-only replicas
$dsn = "pgsql:host={$host};port={$port};dbname={$db}";

Simulating a Failover and Testing

To test the failover mechanism, you can simulate a failure of the current primary PostgreSQL node. The safest way to do this is to stop the Patroni process on the primary node.

# On the current primary node
sudo systemctl stop patroni

To bring the old primary back online, start the Patroni service again:

# On the former primary node
sudo systemctl start patroni

Production Considerations and Enhancements

This setup provides a solid foundation for PostgreSQL HA. For production environments, consider:

Monitoring: Implement comprehensive monitoring for Patroni, etcd, and PostgreSQL. Tools like Prometheus and Grafana are excellent for this.
Backups: Automate regular PostgreSQL backups (e.g., using pg_dump or pg_basebackup with tools like Barman or pgBackRest). Ensure backups are stored off-site.
Replication Slots: Monitor replication slot usage to prevent WAL files from filling up disk space if a replica is down for an extended period.
Connection Pooling: For high-traffic applications, use a connection pooler like PgBouncer. It can be configured to connect to the HAProxy endpoint or directly to the cluster, and it can help manage connections more efficiently.
Security: Harden your etcd and PostgreSQL instances. Use TLS for etcd communication and consider SSL for PostgreSQL connections. Restrict access to Patroni’s REST API.
Automated Deployment: Use configuration management tools (Ansible, Chef, Puppet) or container orchestration (Kubernetes) to automate the deployment and management of Patroni and PostgreSQL clusters.
Read-Only Traffic Management: For read-heavy workloads, fine-tune your HAProxy configuration or use dedicated read-replica management tools to ensure optimal distribution of read queries.
Disaster Recovery (DR) Site: For true disaster recovery, consider replicating your PostgreSQL cluster to a separate geographic region. Patroni can manage cross-region replication with appropriate network configuration.

Establishing a Highly Available PostgreSQL Cluster with Patroni

For mission-critical applications, a single PostgreSQL instance is a single point of failure. Architecting for high availability (HA) necessitates a robust failover strategy. We’ll leverage Patroni, a template for PostgreSQL HA, which uses distributed configuration stores like etcd or Consul for leader election and cluster management. This example focuses on etcd for its simplicity and widespread adoption.

Our setup will involve at least two PostgreSQL nodes configured for streaming replication, with one acting as the primary and the others as replicas. Patroni will monitor the health of the primary and orchestrate a failover to a replica if the primary becomes unavailable.

Prerequisites: etcd Cluster Setup

Before deploying Patroni, ensure you have a functional etcd cluster. For a production environment, a minimum of three etcd nodes is recommended for quorum. For demonstration purposes, a single etcd instance can suffice, but it defeats the HA purpose of etcd itself.

Installing Patroni and Dependencies

On each PostgreSQL node, install Patroni and its dependencies. This typically includes Python, pip, and the PostgreSQL client libraries.

sudo apt update
sudo apt install -y python3 python3-pip postgresql-client
pip3 install --upgrade pip
pip3 install "patroni[etcd]"

Configuring Patroni

Patroni’s configuration is managed via a YAML file. This file defines how Patroni interacts with PostgreSQL, etcd, and defines the cluster’s behavior. Create a `patroni.yml` file on each PostgreSQL node.

sudo mkdir -p /etc/patroni
sudo nano /etc/patroni/patroni.yml

Here’s a sample configuration for a node that will act as a PostgreSQL primary or replica. Ensure the `etcd` section points to your etcd cluster endpoint(s). The `scope` should be unique for each PostgreSQL cluster.

[patroni]
scope: my_pg_cluster
restapi:
  listen: 0.0.0.0:8008
  connect_address: <NODE_IP>:8008

etcd:
  host: <ETCD_HOST_1>:2379, <ETCD_HOST_2>:2379, <ETCD_HOST_3>:2379
  protocol: http

# PostgreSQL configuration
postgresql:
  listen: 0.0.0.0:5432
  connect_address: <NODE_IP>:5432
  data_dir: /var/lib/postgresql/14/main
  bin_dir: /usr/lib/postgresql/14/bin
  pg_hba:
    - host replication replicator 0.0.0.0/0 md5
    - host all all 0.0.0.0/0 md5
  replication:
    username: replicator
    password: <REPLICATION_PASSWORD>
  parameters:
    max_connections: 100
    shared_buffers: 128MB
    effective_cache_size: 384MB
    maintenance_work_mem: 64MB
    wal_level: replica
    wal_sync_method: fsync
    wal_writer_delay: 200ms
    checkpoint_timeout: 5min
    max_wal_senders: 10
    max_replication_slots: 10
    hot_standby: "on"
    default_transaction_isolation: read committed
    log_line_prefix: '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
    log_statement: 'ddl'
    log_replication_commands: 'on'

# Tags for node identification
tags:
  nofailover: false
  clone: false

Important Notes:

Replace <NODE_IP> with the actual IP address of the PostgreSQL node.
Replace <ETCD_HOST_x> with the IP addresses of your etcd cluster members.
Replace <REPLICATION_PASSWORD> with a strong password for the replication user.
Ensure the data_dir and bin_dir paths are correct for your PostgreSQL version and installation.
The pg_hba configuration allows replication and general access. Adjust as per your security requirements.
The tags section is crucial. nofailover: false means this node is eligible for failover.

Initializing PostgreSQL and Starting Patroni

On the *first* node you configure, you’ll need to initialize the PostgreSQL cluster. Patroni can do this for you. Ensure the PostgreSQL service is stopped or not running before proceeding.

# On the first node only
sudo patroni /etc/patroni/patroni.yml

For subsequent nodes, simply run the same command:

# On all other nodes
sudo patroni /etc/patroni/patroni.yml

You can monitor the cluster status via Patroni’s REST API or by inspecting etcd. For example, to check the leader:

curl http://<NODE_IP>:8008/patroni

Integrating PHP Applications with the HA PostgreSQL Cluster

Option 1: Using a Load Balancer (HAProxy)

HAProxy can be configured to query Patroni’s API for the leader and direct traffic accordingly. This provides a stable endpoint for your application.

Install HAProxy on a dedicated server or one of your application servers:

sudo apt update
sudo apt install -y haproxy

Configure HAProxy. The key is the `check port 8008` and the `http-check expect status 200` which will query the Patroni API. We’ll also add a backend for read-only replicas.

# /etc/haproxy/haproxy.cfg

defaults
log global
mode tcp
option tcplog
option dontlognull
timeout connect 5000
timeout client 50000
timeout server 50000

# For a robust HAProxy setup, consider using a tool like `patroni-vip` or
# a custom script that monitors Patroni and updates HAProxy's backend.

# Let's assume a simple setup where we point to all nodes and rely on
# Patroni's API to tell us which one is the leader.
# This is still not ideal for TCP.

# --- Recommended approach: Use a dedicated tool or DNS ---
# For this example, we'll assume a DNS SRV record or a tool is used.
# If using a tool like `patroni-vip`, it would manage a virtual IP.

# Let's simplify and assume a DNS SRV record is used by the application.
# If HAProxy is used, it's typically for read-only replicas or a VIP.

# --- Simplified HAProxy for Read-Only Replicas ---
# This is a common use case for HAProxy with PostgreSQL HA.
# We'll configure a separate backend for read-only traffic.

Explanation:

The patroni_api listener on port 8007 is a proxy to the Patroni REST API endpoints on your PostgreSQL nodes. HAProxy will try to reach each node’s API to find the current leader.
The pg_cluster listener (conceptually) should direct writes to the leader. A robust solution for this often involves a Virtual IP (VIP) managed by tools like patroni-vip or keepalived, which are updated by Patroni.
The pg_replicas listener on port 5433 is a standard HAProxy setup for read-only replicas. It distributes read traffic across your available replicas.

After configuring HAProxy, restart it:

sudo systemctl restart haproxy

Option 2: DNS SRV Records

Example DNS SRV record setup (syntax varies by DNS provider):

_postgresql._tcp.yourdomain.com.  300  IN  SRV  0 5 5432 pgnode1.yourdomain.com.
_postgresql._tcp.yourdomain.com.  300  IN  SRV  1 5 5432 pgnode2.yourdomain.com.
_postgresql._tcp.yourdomain.com.  300  IN  SRV  2 5 5432 pgnode3.yourdomain.com.

The priority and weight fields help DNS clients choose a server. A script would need to dynamically change the priority or remove/add records to point to the leader.

PHP PDO Connection Example

Assuming you’re using HAProxy with a VIP or DNS SRV records, your PHP PDO connection string would look something like this:

<?php
$host = 'your_haproxy_vip_or_dns_name'; // e.g., 'pg-cluster.yourdomain.com' or a VIP IP
$port = '5432'; // Or 5433 for read-only replicas
$db   = 'your_database';
$user = 'your_user';
$pass = 'your_password';
$dsn = "pgsql:host={$host};port={$port};dbname={$db}";

try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
    echo "Connected successfully to PostgreSQL!\n";

    // Example of a read-only query if connecting to replica port
    // $stmt = $pdo->query("SELECT pg_is_in_recovery()");
    // $is_replica = $stmt->fetchColumn();
    // echo "Is in recovery: " . ($is_replica ? 'Yes' : 'No') . "\n";

} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

// For write operations, ensure you are connected to the leader.
// If using HAProxy with a VIP, this connection string points to the VIP.
// If using DNS SRV, the application resolves the SRV record.
?>

If you are using HAProxy for read-only replicas on port 5433, your DSN would change accordingly:

$port = '5433'; // For read-only replicas
$dsn = "pgsql:host={$host};port={$port};dbname={$db}";

Simulating a Failover and Testing

To test the failover mechanism, you can simulate a failure of the current primary PostgreSQL node. The safest way to do this is to stop the Patroni process on the primary node.

# On the current primary node
sudo systemctl stop patroni

To bring the old primary back online, start the Patroni service again:

# On the former primary node
sudo systemctl start patroni

Production Considerations and Enhancements

This setup provides a solid foundation for PostgreSQL HA. For production environments, consider:

Monitoring: Implement comprehensive monitoring for Patroni, etcd, and PostgreSQL. Tools like Prometheus and Grafana are excellent for this.
Backups: Automate regular PostgreSQL backups (e.g., using pg_dump or pg_basebackup with tools like Barman or pgBackRest). Ensure backups are stored off-site.
Replication Slots: Monitor replication slot usage to prevent WAL files from filling up disk space if a replica is down for an extended period.
Connection Pooling: For high-traffic applications, use a connection pooler like PgBouncer. It can be configured to connect to the HAProxy endpoint or directly to the cluster, and it can help manage connections more efficiently.
Security: Harden your etcd and PostgreSQL instances. Use TLS for etcd communication and consider SSL for PostgreSQL connections. Restrict access to Patroni’s REST API.
Automated Deployment: Use configuration management tools (Ansible, Chef, Puppet) or container orchestration (Kubernetes) to automate the deployment and management of Patroni and PostgreSQL clusters.
Read-Only Traffic Management: For read-heavy workloads, fine-tune your HAProxy configuration or use dedicated read-replica management tools to ensure optimal distribution of read queries.
Disaster Recovery (DR) Site: For true disaster recovery, consider replicating your PostgreSQL cluster to a separate geographic region. Patroni can manage cross-region replication with appropriate network configuration.

Disaster Recovery 101: Architecting Auto-Failovers for PostgreSQL and PHP Deployments on Linode

Simulating a Failover and Testing

Production Considerations and Enhancements

PHP PDO Connection Example

Simulating a Failover and Testing

Production Considerations and Enhancements

Option 2: DNS SRV Records

PHP PDO Connection Example

Simulating a Failover and Testing

Production Considerations and Enhancements

Initializing PostgreSQL and Starting Patroni

Integrating PHP Applications with the HA PostgreSQL Cluster

Option 1: Using a Load Balancer (HAProxy)

Option 2: DNS SRV Records

PHP PDO Connection Example

Simulating a Failover and Testing

Production Considerations and Enhancements

Establishing a Highly Available PostgreSQL Cluster with Patroni

Prerequisites: etcd Cluster Setup

Installing Patroni and Dependencies

Configuring Patroni

Initializing PostgreSQL and Starting Patroni

Integrating PHP Applications with the HA PostgreSQL Cluster

Option 1: Using a Load Balancer (HAProxy)

Option 2: DNS SRV Records

PHP PDO Connection Example

Simulating a Failover and Testing

Production Considerations and Enhancements

Recent Posts

Top Categories

Our Products

Our Services