PostgreSQL Streaming Replication: High Availability Guide (2026)

When a primary PostgreSQL database goes down, operations halt across the entire organization. I vividly recall a critical alert at 3:15 AM one night: the primary database was down. Replication hadn’t been configured correctly, and restoring from backup consumed 4 hours, resulting in a 6-hour data loss. This was a preventable disaster. Such scenarios, unfortunately common, underscore the vital importance of robust high-availability configurations. PostgreSQL Streaming Replication isn’t just an advanced feature; it’s a fundamental requirement for any production environment with stringent SLAs. It ensures an up-to-date copy of your database on a standby server, ready to take over if the primary fails, drastically reducing downtime and minimizing data loss. In this comprehensive guide, I will walk you through configuring PostgreSQL Streaming Replication step-by-step, from basic architecture to standby promotion, providing all necessary commands for effective and secure implementation. Prepare to make your PostgreSQL database resilient against unexpected outages.

Prerequisites / Test Environment

To follow this guide, you will need two Linux servers (e.g., Ubuntu 24.04 or CentOS Stream 9) with PostgreSQL 16 installed on both. We will assume the servers have IP addresses primary_ip and standby_ip. All commands will be executed as the postgres user or root where specified.

Primary-Standby Architecture

The primary-standby architecture is the core of PostgreSQL Streaming Replication. One server, the primary, handles all write operations (INSERT, UPDATE, DELETE) and reads. The other server, the standby, receives a continuous stream of changes (WAL files, Write-Ahead Log) from the primary and applies these changes to maintain an exact, up-to-date copy of the database. This flow is unidirectional. Should the primary fail, the standby can be promoted to a new primary, ensuring operational continuity. While various replication modes exist (synchronous, asynchronous), asynchronous replication offers a good balance between performance and resilience for most scenarios.

Configure the Primary Server

The first phase involves preparing the primary server to send WAL files to the standby. This requires modifying the postgresql.conf and pg_hba.conf files.

1. Modify postgresql.conf: Open the configuration file (/etc/postgresql/16/main/postgresql.conf on Debian/Ubuntu or /var/lib/pgsql/16/data/postgresql.conf on CentOS/RHEL) and adjust the following parameters:

    # postgresql.conf (primary)
    wal_level = replica
    max_wal_senders = 3 # Number of concurrent replication connections. Increase if you have more standbys.
    archive_mode = on
    archive_command = 'cp %p /var/lib/postgresql/archive/%f' # Optional, for manual WAL archiving or external tools
    listen_addresses = '*' # Allows connections from any IP, or specify 'primary_ip'

wal_level = replica is essential to enable sending WAL files. max_wal_senders defines how many replication connections the primary can handle concurrently. A value of 3 is a good starting point for a single standby. WAL archiving is recommended for a better Recovery Point Objective (RPO) and for point-in-time recovery (PITR).

1. Modify pg_hba.conf: This file controls client authentication. We need to allow the standby server to connect to the primary for replication. Add the following line to the end of the file (/etc/postgresql/16/main/pg_hba.conf):

    # pg_hba.conf: host replication replicator standby_ip/32 md5
    host    replication     replicator      standby_ip/32            md5

This line permits the replicator user (which we will create shortly) to connect for replication purposes from the standby_ip address. The md5 method requires an encrypted password.

1. Restart PostgreSQL on the primary: To apply the changes, restart the PostgreSQL service.

    sudo systemctl restart postgresql

Create the Replication User

For security and management, it’s best practice to create a dedicated user with minimal necessary privileges for replication. This user will only have permission to read WAL files.

Connect to the PostgreSQL database on the primary server as the postgres user:

psql -U postgres

Then execute the SQL command to create the replicator user (replace 'pwd' with a strong password):

CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'pwd';

This user is now ready to be used by the standby server to connect to the primary and initiate the replication stream. Using a dedicated user improves auditability and permission management, a key principle of cybersecurity, as recommended by standards such as ISO 27001:2022.

Configure the Standby Server

On the standby server, you do not need to install an empty PostgreSQL cluster. We will use pg_basebackup to copy the entire data cluster from the primary.

1. Stop PostgreSQL on the standby: If PostgreSQL is running on the standby, it must be stopped to copy the data.

    sudo systemctl stop postgresql

1. Remove old data cluster (if present): If you have an existing PostgreSQL installation on the standby, it’s advisable to remove pre-existing data to avoid conflicts.

    sudo rm -rf /var/lib/postgresql/16/main # Or the correct path for your distribution

1. Create a base backup of the primary: Use pg_basebackup to clone the data cluster from the primary to the standby. Ensure the postgres user has write permissions in the destination directory.

    # Run as postgres user
    pg_basebackup -h primary_ip -U replicator -D /var/lib/postgresql/16/main -P -R -W

• -h primary_ip: IP address of the primary server.
• -U replicator: Replication user created earlier.
• -D /var/lib/postgresql/16/main: Destination directory for the cluster data on the standby.
• -P: Shows progress of the copy.
• -R: Automatically creates the standby.signal file and postgresql.conf with recovery settings, configuring the standby for replication. This is a new feature in version 12 and later, significantly simplifying configuration.
• -W: Prompts for the replicator user’s password (the one you set previously).

This command will copy all data and create the necessary configuration files to start the standby in recovery mode.

Start Replication

After preparing the standby with pg_basebackup, you can start the PostgreSQL service on the standby.

sudo systemctl start postgresql

The standby will start in recovery mode and begin connecting to the primary to receive WAL files. Check the PostgreSQL logs (/var/log/postgresql/postgresql-16-main.log or similar) on the standby to verify that the replication connection has been successfully established. You should see messages like starting replication WAL receiver.

Verify Replication Status

To ensure replication is working correctly, you can run a query on the primary server. Connect to the primary with psql and use the pg_stat_replication view:

SELECT * FROM pg_stat_replication;

The output of this query will show a row for each active replication connection. You should see your standby server listed with a streaming state. Pay attention to the sync_state column (should be async for asynchronous replication and sync for synchronous) and write_lag, flush_lag, replay_lag which indicate the delay in bytes and time between primary and standby. High lag is a red flag and requires immediate investigation.

Standby Promotion (Manual Failover)

In the event of a primary server failure, you will need to manually promote the standby to a new primary. This process is critical and must be performed carefully to avoid split-brain scenarios.

1. Verify the primary is indeed down: Ensure the primary is not just temporarily inaccessible, but completely out of service. Double-checking is essential.

1. Promote the standby: On the standby server, execute the pg_ctl promote command or create the trigger_file.

    sudo pg_ctlcluster 16 main promote # For Debian/Ubuntu
    # Or more generically:
    # touch /var/lib/postgresql/16/main/trigger_file

After promotion, the standby will become an independent primary server, accepting both writes and reads. All other standbys (if any) will need to be reconfigured to replicate from this new primary. This operation marks a point of no return for the standby, transforming it into a fully operational instance.

Monitor Replication Lag

Monitoring replication lag is crucial to ensure your RPO (Recovery Point Objective) is met. Excessive lag means that in case of a failover, you could lose more data than expected. In addition to pg_stat_replication, you can use monitoring tools like Prometheus and Grafana to visualize lag over time.

Key metrics to monitor:

• pg_stat_replication.write_lag: time between WAL write on primary and its reception on standby.
• pg_stat_replication.flush_lag: time between WAL write on primary and its flush to disk on standby.
• pg_stat_replication.replay_lag: time between WAL write on primary and its application on standby.

A lower lag value is always preferable. If lag consistently increases, it may indicate network issues, I/O capacity problems on the standby, or excessive load on the primary.

Common Errors and Troubleshooting

• Connection refused: Check pg_hba.conf on the primary and the firewall. Ensure primary_ip and standby_ip are correct and that the PostgreSQL port (5432) is open.
• Replication user unauthorized: Verify that the replicator user was created with REPLICATION permission and that the password is correct.
• wal_level not set to replica: On the primary, ensure wal_level is set correctly and the PostgreSQL service has been restarted.
• High replication lag: Investigate network performance between primary and standby, I/O capabilities of the standby, or primary load. A slow disk on the standby can cause significant lag.
• Missing standby.signal file: If you did not use -R with pg_basebackup (or are configuring older PostgreSQL versions), you might need to manually create the standby.signal file in the standby’s data directory.

FAQ — Frequently Asked Questions

What is PostgreSQL Streaming Replication?

It’s a feature that allows you to maintain one or more up-to-date copies of a PostgreSQL database (standbys) by continuously receiving a stream of changes (WAL) from a primary server. This ensures high availability and protection against data loss in case of primary failure. It works by sending transaction logs in near real-time from the primary to the standby.

What is the difference between synchronous and asynchronous replication?

In asynchronous replication, the primary does not wait for confirmation from the standby that data has been written before considering the transaction complete. It offers better performance but can result in minimal data loss in the event of an abrupt primary failure. Synchronous replication requires confirmation from the standby before completing the transaction on the primary, ensuring zero data loss but with an impact on performance. The choice depends on the trade-off between RPO and performance.

Can I replicate to multiple standby servers?

Yes, PostgreSQL supports replication to multiple standby servers simultaneously. You can configure max_wal_senders on the primary to accommodate the desired number of connections. Each standby will receive its own WAL stream from the primary. This configuration further increases resilience and can be useful for distributing read load.

How can I automate failover?

Manual configuration is a great starting point, but for production environments, automating failover is advisable. Tools like repmgr, Patroni, or pg_auto_failover provide advanced features for monitoring, automatic failover, and cluster management, reducing manual intervention and recovery times. These tools also manage split-brain scenarios and node resynchronization.

Conclusions with Operational Takeaways

Configuring PostgreSQL Streaming Replication is a critical investment in your database infrastructure’s resilience. Don’t wait for a critical database to go offline to discover the importance of high availability. With careful planning and by following the steps outlined, you can implement a robust system that protects your data and ensures operational continuity. Always remember to test your failover process and consistently monitor replication lag. A well-replicated database is a secure database.

Read also: PostgreSQL Indexes: When to Use for Performance 2026

Read also: PostgreSQL pg_dump Backup: Complete Restore Guide 2026

Read also: PostgreSQL Slow Queries: Find and Optimize (2026)