PostgreSQL Streaming Replication for DR & HA

In today’s fast-paced digital landscape, the availability and integrity of data are not just desirable features, but critical requirements for any successful application or service. Database downtime can lead to significant financial losses, reputational damage, and a frustrated user base. This is where robust strategies for disaster recovery (DR) and high availability (HA) come into play, and for PostgreSQL users, streaming replication stands out as a powerful, built-in solution.

PostgreSQL streaming replication allows you to maintain one or more up-to-date copies of your primary database server, known as standbys. These standbys can take over operations swiftly in case of a primary server failure, or they can serve read-only queries to offload the primary, significantly enhancing both resilience and performance. This comprehensive guide will walk you through the essential concepts, setup procedures, and best practices for implementing PostgreSQL streaming replication effectively in a US-centric operational environment.

Understanding PostgreSQL Streaming Replication

At its core, PostgreSQL streaming replication is a method for continuously sending the Write-Ahead Log (WAL) records from a primary server to one or more standby servers. The standbys then replay these WAL records to keep their data synchronized with the primary.

What is Streaming Replication?

Think of the WAL as a journal of every change made to your database. When you commit a transaction, PostgreSQL first writes the changes to the WAL before applying them to the actual data files. This mechanism ensures data durability, even if the server crashes. Streaming replication leverages this by sending these WAL records across the network to standby servers as they are generated.

• Primary Server: The main, active database server that handles all read and write operations.
• Standby Server(s): One or more replica servers that continuously receive and apply WAL records from the primary, keeping their data synchronized. These are typically read-only.
• WAL Sender Process: A process on the primary server that sends WAL data to connected standbys.
• WAL Receiver Process: A process on the standby server that receives WAL data from the primary.

Why is it Crucial?

Implementing streaming replication offers several compelling benefits for your database infrastructure:

• Disaster Recovery (DR): In the event of a catastrophic failure of your primary server (e.g., hardware failure, data corruption, natural disaster), a standby server can be promoted to become the new primary. This minimizes data loss (measured by Recovery Point Objective, RPO) and reduces the time it takes to restore service (measured by Recovery Time Objective, RTO).
• High Availability (HA): Replication ensures that a hot standby is always ready to take over with minimal interruption, reducing downtime for your applications. Tools can automate this failover process for even greater availability.
• Read Scaling: Standby servers can be configured as ‘hot standbys,’ allowing them to serve read-only queries. This can significantly offload the primary server, improving performance for read-heavy applications.
• Data Migration & Upgrades: Replication can facilitate smoother database migrations or major version upgrades by allowing a standby to be upgraded independently and then promoted.

Key Concepts and Components

Before diving into the setup, it’s essential to grasp some fundamental PostgreSQL concepts that underpin streaming replication.

Write-Ahead Log (WAL)

The WAL is paramount. Every change to the database is first recorded in the WAL. This log is then shipped to the standby servers. The continuous nature of this log shipping is what makes streaming replication ‘streaming.’ Without the WAL, ensuring data durability and replication efficiency would be far more complex.

Primary Server

The primary server is the source of truth. All data modifications happen here. Its configuration dictates how WAL segments are generated and how many concurrent standbys it can support.

Standby Server(s)

Standby servers are passive replicas. They apply the WAL records received from the primary. A hot standby can process read-only queries while recovering, offering read-scaling benefits. A warm standby cannot accept connections while recovering.

Replication Slots

Replication slots are a critical feature introduced in PostgreSQL 9.4. They prevent the primary server from removing WAL segments that have not yet been consumed by all connected standbys. This is vital for robust replication, as it guards against a standby falling too far behind and losing its ability to recover from the primary. Without replication slots, a slow or disconnected standby could cause the primary to run out of disk space or delete necessary WAL files.

standby.signal File

In modern PostgreSQL versions (12 and later), the standstandby.signal file replaces the older recovery.conf. This empty file, placed in the data directory of the standby, signals to PostgreSQL that it should start in recovery mode as a standby server. Replication connection parameters are now typically configured directly in postgresql.conf.

Replication Modes: Synchronous vs. Asynchronous

PostgreSQL offers flexibility in how transactions are committed in relation to their replication status, impacting both data integrity and performance.

Asynchronous Replication

In asynchronous replication, a transaction is considered committed on the primary as soon as its WAL records are written to the primary’s local WAL files. The primary does not wait for the standby to confirm receipt or application of these records.

• Pros: Excellent performance on the primary, as it doesn’t incur network latency waiting for standbys.
• Cons: Potential for data loss. If the primary fails before WAL records are replicated to the standby, any transactions committed but not yet replicated will be lost.
• Use Cases: Ideal for scenarios where a slight chance of data loss is acceptable, and primary performance is paramount. Many analytical or reporting databases use this mode.

Synchronous Replication

With synchronous replication, a transaction on the primary is not considered committed until its WAL records are not only written to the primary’s local WAL but also confirmed as received (or even applied) by at least one synchronous standby. This ensures that committed data exists on multiple servers simultaneously.

• Pros: Zero data loss guarantee (RPO=0). If the primary fails, all transactions that were reported as committed to the application are guaranteed to exist on at least one standby.
• Cons: Performance impact on the primary due to network latency and the standby’s processing time. The primary waits for acknowledgment from the standby.
• Use Cases: Critical applications where data integrity is the highest priority, such as financial systems or transactional databases where losing even a single transaction is unacceptable.

To configure synchronous replication, you would set synchronous_commit = on (or remote_write, remote_apply for stricter guarantees) and specify one or more standby names in synchronous_standby_names in the primary’s postgresql.conf.

Setting Up PostgreSQL Streaming Replication (US Context)

Let’s walk through a practical setup for streaming replication. We’ll assume you have two Linux servers (e.g., running CentOS or Ubuntu) in the US, one for the primary and one for the standby, with PostgreSQL 12+ installed on both.

Prerequisites

1. Two separate servers with PostgreSQL installed.
2. Network connectivity between the servers.
3. Firewall rules configured to allow PostgreSQL traffic (default port 5432) between the primary and standby.
4. A dedicated PostgreSQL user for replication (e.g., repl_user).

Step 1: Configure the Primary Server

First, we need to adjust the primary server’s configuration to enable WAL archiving and streaming. You’ll primarily edit postgresql.conf and pg_hba.conf.

Edit postgresql.conf

Locate your postgresql.conf file (often in /var/lib/pgsql/15/data/ or /etc/postgresql/15/main/) and make the following changes:

# Required for replication and recovery. 'replica' allows read-only queries on standbys. 'logical' for logical replication. wal_level = replica  # Maximum number of concurrent connections from standby servers or pg_basebackup. max_wal_senders = 10  # Amount of WAL segments to retain in pg_wal directory for standbys. # Use replication slots instead for robust retention. Set to 0 if using slots. wal_keep_size = 0  # Enable read-only queries on a hot standby. hot_standby = on  # The primary must listen on an external IP address, not just localhost. listen_addresses = '*' # Or specific IP, e.g., '192.168.1.100'  # Optional: For synchronous replication (uncomment and adjust if needed) # synchronous_commit = on # synchronous_standby_names = 'ANY 1 (my_standby_name)' 

Edit pg_hba.conf

This file controls client authentication. Add an entry to allow your standby server to connect for replication. Make sure to replace with the actual IP of your standby.

# TYPE  DATABASE        USER            ADDRESS                 METHOD host    replication     repl_user       /32 trust # Alternatively, use md5 for password authentication: # host    replication     repl_user       /32 md5 

If you choose md5, remember to create the repl_user and set a password:

CREATE USER repl_user REPLICATION LOGIN CONNECTION LIMIT -1 ENCRYPTED PASSWORD 'your_secure_password';

Restart Primary PostgreSQL

After modifying the configuration files, restart the PostgreSQL service on the primary server:

sudo systemctl restart postgresql