Disaster Recovery 101: Architecting Auto-Failovers for PostgreSQL and Python Deployments on Google Cloud
Establishing a High-Availability PostgreSQL Cluster on Google Cloud
Achieving true disaster recovery for a PostgreSQL database, especially in a cloud-native environment like Google Cloud Platform (GCP), necessitates an automated failover strategy. This isn’t about manual intervention during an outage; it’s about a system that detects failure and seamlessly promotes a replica to primary status with minimal data loss and downtime. We’ll focus on a robust setup using PostgreSQL’s built-in streaming replication, managed within GCP’s infrastructure.
Our architecture will involve a primary PostgreSQL instance and at least one synchronous or asynchronous replica. For high availability, we’ll leverage Google Cloud’s robust networking and compute capabilities, specifically Compute Engine instances and Cloud SQL for managed options, though this guide focuses on a self-managed approach for maximum control and understanding. The key to automated failover lies in a separate monitoring and orchestration layer.
Configuring PostgreSQL for Streaming Replication
First, ensure your primary PostgreSQL instance is configured to allow streaming replication. This involves modifying the postgresql.conf and pg_hba.conf files.
On the primary instance, edit postgresql.conf (typically located in /etc/postgresql/[version]/main/ or similar):
wal_level = replica max_wal_senders = 10 wal_keep_size = 1024 # Or a sufficient size to prevent replica lag from falling too far behind synchronous_commit = on # For synchronous replication, or 'remote_write' for higher throughput with slight risk synchronous_standby_names = 'replica1' # Replace 'replica1' with the actual name of your standby server
Next, edit pg_hba.conf to allow replication connections from your standby server(s). Add a line like this, replacing [standby_ip_address] with the private IP of your replica:
host replication replication_user [standby_ip_address]/32 md5
Restart the PostgreSQL service on the primary after these changes:
sudo systemctl restart postgresql
On the replica instance, you’ll need to prepare its data directory. It’s best to start with a clean data directory and then use pg_basebackup to copy the primary’s data. First, stop PostgreSQL on the replica if it’s running.
sudo systemctl stop postgresql sudo rm -rf /var/lib/postgresql/[version]/main/* # WARNING: This deletes existing data!
Now, perform the base backup. Replace [primary_ip_address] and [replication_user] accordingly. You’ll be prompted for the replication user’s password.
sudo -u postgres pg_basebackup -h [primary_ip_address] -U [replication_user] -D /var/lib/postgresql/[version]/main/ -P -v -R
The -R flag is crucial as it automatically creates the standby.signal file and a postgresql.auto.conf with the necessary primary_conninfo and primary_slot_name (if using replication slots, which is highly recommended for production).
If you are not using -R, you would manually create standby.signal in the data directory and configure primary_conninfo in postgresql.conf or postgresql.auto.conf:
# In postgresql.conf or postgresql.auto.conf on the replica primary_conninfo = 'host=[primary_ip_address] port=5432 user=[replication_user] password=your_password dbname=postgres' # If using replication slots (recommended): # primary_slot_name = 'replica1_slot'
Ensure the postgresql.conf on the replica has hot_standby = on to allow read queries on the replica.
Start the PostgreSQL service on the replica:
sudo systemctl start postgresql
You can verify replication status by querying the primary:
SELECT application_name, state, sync_state FROM pg_stat_replication;
And on the replica:
SELECT pg_is_in_recovery(); -- Should return 't' (true)
Implementing an Automated Failover Mechanism
PostgreSQL itself does not provide an automated failover mechanism out-of-the-box. We need an external tool to monitor the health of the primary and orchestrate the promotion of a replica. Patroni is a popular, robust, and widely adopted choice for this purpose. It leverages distributed configuration stores like etcd, Consul, or ZooKeeper for leader election and state management.
For this example, we’ll outline the setup using etcd, which is also readily available on GCP.
Setting up etcd for Cluster State Management
Deploy an etcd cluster. For production, a minimum of 3 nodes is recommended for fault tolerance. You can deploy etcd on separate Compute Engine instances or even on the same instances as PostgreSQL nodes if resource constraints are a concern (though not ideal for strict isolation).
Install etcd on your chosen nodes. Download the latest binary from the official etcd releases page.
Configure etcd for cluster communication. Here’s a sample configuration for a member node (/etc/etcd/etcd.conf.yml):
name: etcd-node-1 data-dir: /var/lib/etcd listen-client-urls: http://0.0.0.0:2379 advertise-client-urls: http://[node1_ip]:2379 listen-peer-urls: http://0.0.0.0:2380 initial-advertise-peer-urls: http://[node1_ip]:2380 initial-cluster: etcd-node-1=http://[node1_ip]:2380,etcd-node-2=http://[node2_ip]:2380,etcd-node-3=http://[node3_ip]:2380 initial-cluster-token: my-etcd-cluster-token initial-cluster-state: new # Use 'existing' for subsequent nodes joining an established cluster
Start the etcd service:
sudo systemctl enable etcd sudo systemctl start etcd
Verify the etcd cluster health:
ETCDCTL_API=3 etcdctl member list ETCDCTL_API=3 etcdctl endpoint health
Deploying and Configuring Patroni
Patroni needs to be installed on each PostgreSQL node (primary and replicas). It will manage the PostgreSQL lifecycle and interact with etcd.
Install Patroni and its dependencies (e.g., python3-pip, python3-dev, build-essential, libpq-dev). Then install Patroni via pip:
sudo apt update sudo apt install -y python3-pip python3-dev build-essential libpq-dev sudo pip3 install 'patroni[etcd]' psycopg2-binary
Create a Patroni configuration file (e.g., /etc/patroni/patroni.yml) on each PostgreSQL node. The configuration will be similar across all nodes, with minor differences in name and scope (which should be unique per cluster but the same for all nodes in a cluster).
scope: my_pg_cluster # Unique identifier for this PostgreSQL cluster
namespace: /service/ # Base path in etcd for this cluster's state
etcd:
host: "[etcd_node1_ip]:2379,[etcd_node2_ip]:2379,[etcd_node3_ip]:2379"
protocol: http # Use 'https' if etcd is configured with TLS
# PostgreSQL configuration
postgresql:
listen: 0.0.0.0:5432
connect_address: "[this_node_private_ip]:5432" # Crucial for Patroni to know how to connect
data_dir: /var/lib/postgresql/[version]/main
pg_hba:
- host all all 0.0.0.0/0 md5
parameters:
wal_level: replica
max_wal_senders: 10
hot_standby: "on"
synchronous_commit: "on"
synchronous_standby_names: "replica1" # This should match the 'name' of one of your Patroni nodes
# Patroni configuration
restapi:
listen: 0.0.0.0:8000
connect_address: "[this_node_private_ip]:8000" # Crucial for Patroni to know how to connect
# Replication configuration
replication:
name: replica1 # Unique name for this node within the cluster
synchronous_mode: true # Set to false if you prefer asynchronous replication
synchronous_node_name: replica1 # Must match the 'name' of a node in the cluster config
# Tags can be used for routing or identification
tags:
nofailover: false
clonefrom: false
Important Notes on Patroni Configuration:
- Replace
[this_node_private_ip]with the actual private IP of the Compute Engine instance. - The
connect_addressfor both PostgreSQL and the Patroni REST API is critical. Patroni uses this to tell other nodes how to connect to it. scopedefines the logical PostgreSQL cluster. All nodes in the same cluster must have the samescope.namespaceis the base path inetcdwhere Patroni stores its state.replication.namemust be unique for each node in the cluster.synchronous_node_namein thereplicationsection should match thenameof one of the nodes configured in Patroni.- The
postgresql.parameters.synchronous_standby_nameson the primary should also reflect thenameof a Patroni node.
Create a systemd service file for Patroni (e.g., /etc/systemd/system/patroni.service):
[Unit] Description=Patroni PostgreSQL High-Availability Manager After=network.target [Service] User=postgres Group=postgres ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml Restart=on-failure KillMode=process [Install] WantedBy=multi-user.target
Enable and start the Patroni service on all PostgreSQL nodes:
sudo systemctl daemon-reload sudo systemctl enable patroni sudo systemctl start patroni
Once Patroni starts on all nodes, it will elect a leader (the primary). You can check the leader status via the Patroni REST API or by inspecting etcd.
# On any node, query the leader status curl http://localhost:8000/primary # Or check etcd directly (replace with your etcdctl path and endpoints) ETCDCTL_API=3 etcdctl --endpoints=[etcd_endpoint1],[etcd_endpoint2] get /service/my_pg_cluster/leader --keys-only
Patroni automatically handles PostgreSQL startup, shutdown, and failover. If the primary node becomes unreachable, Patroni will detect this, remove the old leader from etcd, and initiate a leader election among the remaining healthy nodes. The elected node will then promote itself to primary.
Integrating with Python Applications
Your Python application needs to be aware of the PostgreSQL cluster’s primary. Hardcoding the primary’s IP address is a recipe for disaster. Instead, use a mechanism that can dynamically discover the current primary.
Dynamic Primary Discovery
The most robust way to achieve this is by querying the Patroni API. Patroni exposes an API endpoint (e.g., /primary) that returns the connection details of the current primary PostgreSQL instance.
Here’s a Python snippet demonstrating how to fetch the primary’s connection information:
import requests
import os
def get_postgres_primary_connection_info():
"""
Fetches the current PostgreSQL primary connection info from Patroni.
Assumes Patroni API is accessible on localhost:8000.
"""
patroni_api_url = os.environ.get("PATRONI_API_URL", "http://localhost:8000")
try:
response = requests.get(f"{patroni_api_url}/primary", timeout=5)
response.raise_for_status() # Raise an exception for bad status codes
data = response.json()
return {
"host": data["host"],
"port": data["port"],
"database": data.get("database", "postgres"), # Default to 'postgres' if not specified
"user": data.get("user", "postgres"), # Default to 'postgres' if not specified
"password": data.get("password", None) # Password might not always be in API response
}
except requests.exceptions.RequestException as e:
print(f"Error fetching primary info from Patroni: {e}")
return None
except Exception as e:
print(f"An unexpected error occurred: {e}")
return None
def get_db_connection_string():
"""
Constructs a database connection string using dynamic primary info.
"""
conn_info = get_postgres_primary_connection_info()
if conn_info:
# Constructing a DSN string for libraries like psycopg2
# Note: Password handling might need adjustment based on your security model
# and how Patroni is configured to expose credentials.
# Often, credentials are managed via environment variables or secrets managers.
password_part = f":{conn_info['password']}" if conn_info.get('password') else ""
return f"postgresql://{conn_info['user']}{password_part}@{conn_info['host']}:{conn_info['port']}/{conn_info['database']}"
return None
if __name__ == "__main__":
# Example usage:
# Set PATRONI_API_URL environment variable if Patroni is not on localhost:8000
# export PATRONI_API_URL="http://your-patroni-api-host:8000"
db_url = get_db_connection_string()
if db_url:
print(f"Successfully retrieved DB connection string: {db_url}")
# Now you can use this db_url with your ORM or DB driver (e.g., SQLAlchemy, psycopg2)
# Example with SQLAlchemy:
# from sqlalchemy import create_engine
# engine = create_engine(db_url)
# try:
# with engine.connect() as connection:
# result = connection.execute("SELECT 1")
# print(f"Database connection successful: {result.scalar()}")
# except Exception as e:
# print(f"Database connection failed: {e}")
else:
print("Failed to retrieve database connection string.")
Deployment Considerations:
- The Python application should run in an environment where it can reach the Patroni API endpoints. This might involve deploying your application on Compute Engine instances within the same VPC network as your PostgreSQL nodes, or configuring appropriate firewall rules.
- Consider setting the
PATRONI_API_URLas an environment variable for flexibility. - For applications that need to maintain connections, implement a reconnection strategy. When a failover occurs, existing connections to the old primary will break. The application should catch these connection errors, re-query the Patroni API for the new primary, and re-establish its connection.
- If your application performs write operations, it’s crucial that it can handle transient connection errors gracefully and retry the operation against the new primary.
Connection Pooling and Load Balancing
While dynamic discovery is essential for writes, read-heavy workloads can benefit from read replicas. Patroni can manage read replicas, but directing traffic to them requires a load balancer or a smart connection pooler.
HAProxy is an excellent choice for this. You can configure HAProxy to:
- Direct all write traffic to the current primary (discovered via Patroni API).
- Distribute read traffic across available read replicas.
A common pattern is to have a single VIP (Virtual IP) or DNS entry that points to HAProxy. Your application then connects to this VIP/DNS name. HAProxy, in turn, uses Patroni’s API to determine the current primary for writes and can also query for read replicas.
Example HAProxy Configuration Snippet:
# This is a simplified example. A full HAProxy setup for PostgreSQL with Patroni
# often involves a script to dynamically update HAProxy backend servers based on Patroni API calls.
global
log /dev/log local0
log /dev/log local1 notice
daemon
defaults
log global
mode tcp
option tcplog
option dontlognull
timeout connect 5000
timeout client 50000
timeout server 50000
listen pg_write
bind *:5433 # Port for writes
mode tcp
# This is where dynamic configuration is key.
# You'd typically use a script to update 'server' lines based on Patroni's /primary API.
# For demonstration, assuming a static primary IP for now:
# server pg_primary [current_primary_ip]:5432 check port 8000 # Check Patroni API health
# A more advanced setup would use a script to fetch /primary and update this.
listen pg_read
bind *:5434 # Port for reads
mode tcp
# Distribute reads across replicas. Again, dynamic updates are needed.
# server pg_replica1 [replica1_ip]:5432 check port 8000
# server pg_replica2 [replica2_ip]:5432 check port 8000
To make this dynamic, you would write a script (e.g., in Python or Bash) that periodically polls the Patroni API (/primary and potentially /replicas) and uses socat or HAProxy’s runtime API to update the backend server list. This script would run on the HAProxy server or a dedicated management node.
Testing and Validation
Thorough testing is paramount. Simulate failures to ensure your automated failover works as expected:
- Primary Node Failure: Stop the PostgreSQL process on the primary node. Observe Patroni’s logs and
etcdstate to confirm a replica is promoted. Verify your application can reconnect and write to the new primary. - Network Partition: Simulate a network partition between the primary and replicas, or between Patroni nodes and
etcd. - Instance Failure: Terminate the Compute Engine instance hosting the primary PostgreSQL node.
- Data Corruption: While harder to automate, test scenarios where data might become inconsistent (though streaming replication and Patroni’s checks mitigate many of these).
Monitor your cluster health using tools like Prometheus and Grafana, integrating PostgreSQL and Patroni metrics. Ensure alerts are configured for any anomalies or failures.