Set Up Hot Standby Replication

When your primary PostgreSQL server goes down—whether from a hardware failure, a routine reboot, or a flipped breaker in the data center—every query that depended on it fails. Users see errors, dashboards go red, and your pager explodes. You need a safety net, and hot standby replication is exactly that: a live, continuously updated replica that can take over with minimal downtime. In this lesson, you'll set up hot standby replication from scratch—configuring a primary and a standby, streaming changes in real time, and promoting the standby when disaster strikes. By the end, you'll be able to protect your database with a high-availability setup you can trust.

The problem this lesson solves

Single-server databases are a single point of failure. If the server crashes, your data is stuck on a disk that may be corrupted or unreachable. Even with regular backups, recovery can take hours, and you lose any changes made since the last backup. Hot standby replication solves this by maintaining a second server that continuously applies every change made on the primary. This keeps the replica up-to-date, often within milliseconds, and ready to take over as the new primary with minimal downtime. It's not just about disaster recovery—it's about high availability and keeping your application responsive even when one server fails.

Core concept / mental model

Think of the primary server as the authoritative source of truth. Every insert, update, or delete is recorded in its write-ahead log (WAL)—a transaction log that PostgreSQL uses to ensure durability. Hot standby replication copies those WAL records to one or more standby servers, which replay them against their own data files. This keeps the standby's data nearly identical to the primary's, with a small lag that depends on network speed and transaction volume.

PostgreSQL supports two forms of standby: warm standby (which can only be queried in read-only mode) and hot standby (which allows read-only queries on the standby). Hot standby is what we want—it lets you offload read traffic from the primary and, in the event of a failure, promote it to primary almost instantly. The standby is not a full duplicate of the primary process; it runs in recovery mode, replaying WAL as soon as it arrives.

Key terms you'll encounter:

• WAL (Write-Ahead Log): The log of all transactions; the heartbeat of replication.
• Streaming replication: The mechanism by which WAL records are transferred from primary to standby over a TCP connection.
• Replication slot: A feature that ensures the primary retains WAL until the standby has received it, preventing data loss if the standby lags.
• Promotion: The process of turning a standby into a new primary, typically after the old primary fails.

How it works step by step

Setting up hot standby replication involves configuring both servers and starting the standby in recovery mode. Here's the high-level flow:

1. Configure the primary to allow replication connections and enable WAL archiving (optional but recommended for recovery from standby failure).
2. Create a base backup of the primary's data directory and copy it to the standby server.
3. Configure the standby with the necessary connection parameters to the primary.
4. Start the standby in recovery mode; it will connect to the primary and begin streaming WAL changes.
5. Verify replication by checking the standby's status and testing a read-only query.
6. Handle failover by promoting the standby when the primary fails.

Hands-on walkthrough

Let's get practical. We'll set up two PostgreSQL servers, primary and standby, on separate hosts (or separate local instances). The steps assume PostgreSQL 12 or newer, where the configuration is straightforward.

Step 1: Configure the primary

On the primary server, edit postgresql.conf to enable streaming replication:

# postgresql.conf (on primary)

listen_addresses = 'localhost, primary_ip'   # or '*' if you prefer
wal_level = replica                         # or 'logical' for logical replication
max_wal_senders = 10                        # number of standby connections
wal_keep_size = 128                        # WAL to keep for standby, in MB (optional)

Also set hot_standby = on on the primary, though it's on by default in hot standby configurations.

Then, configure pg_hba.conf to allow the standby to connect for replication:

# pg_hba.conf (on primary)

host    replication     all     <standby_ip>/32    scram-sha-256

Create a replication user or use a dedicated role with REPLICATION privilege:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

Restart the primary to apply changes.

Step 2: Create a base backup

On the standby server, create a base backup from the primary. You can use pg_basebackup for this:

pg_basebackup -h primary_ip -U replicator -D /var/lib/postgresql/16/main -R -P -X stream

This copies the data directory from the primary and sets up recovery configuration automatically. The -R option creates a standby.signal file in the data directory, which tells PostgreSQL to start in recovery mode. The -X stream streams WAL during the backup to avoid gaps.

Step 3: Configure the standby

If pg_basebackup with -R was used, the primary_conninfo is already set in postgresql.auto.conf. If not, manually create a standby.signal file and add the connection settings to postgresql.conf:

# standby.signal (in data directory)

# empty file to indicate standby mode

In postgresql.conf (or postgresql.auto.conf), set:

primary_conninfo = 'host=primary_ip port=5432 user=replicator password=secure_password sslmode=require'

Also ensure hot_standby = on is set (default is 'on' in many distributions).

Step 4: Start the standby

Start the PostgreSQL service on the standby:

sudo systemctl start postgresql

Check the logs to confirm it's in recovery mode:

tail -f /var/log/postgresql/postgresql-16-main.log

You should see something like:

LOG:  database system is ready to accept connections
LOG:  database system is ready to accept read only connections

The last line indicates hot standby mode is enabled.

Step 5: Verify replication

On the primary, check the replication status:

SELECT client_addr, state, sync_state FROM pg_stat_replication;

You should see the standby's IP and state streaming. On the standby, query to confirm it's read-only and up-to-date:

-- On standby
SELECT pg_is_in_recovery(); -- returns true
SELECT * FROM my_table; -- returns the same data as primary

Try inserting a new row on the primary and see it appear on the standby a moment later.

Step 6: Promote the standby (failover)

When the primary fails, promote the standby to become the new primary:

# On standby
pg_ctl promote  # or systemctl, or use the function

Or from a SQL session:

-- From a hot standby
SELECT pg_promote();

After promotion, the standby becomes a primary and accepts writes. Point your application to the new primary—just don't forget to update connection strings.

Compare options / when to choose what

Hot standby is not the only replication option in PostgreSQL. Here's a quick comparison to help you choose:

When to choose hot standby:

• You need automatic failover (with an external tool like Patroni) or can tolerate manual promotion.
• You want to offload read queries from the primary.
• You need near-real-time replication with minimal data loss.

Synchronous replication is better if you can't afford to lose any transaction (e.g., financial apps), but it comes with latency overhead. Logical replication is useful when you want to replicate only a subset of tables or move data to a different PostgreSQL version.

Troubleshooting & edge cases

Working with hot standby can have hiccups. Here are common issues and how to fix them.

Standby not starting

If the standby fails to start, check:

• standby.signal file presence — it must exist in the data directory.
• primary_conninfo correct — host, port, user, password must be right.
• pg_hba.conf on primary — replication connection must be allowed.
• Check the PostgreSQL log on the standby for specific errors.

Replication lag growing

If your standby lags behind the primary, possible causes:

• Network latency — improving bandwidth reduces lag.
• High write volume on primary — consider using synchronous replication for critical transactions.
• WAL retention too low — ensure wal_keep_size is adequate or use replication slots.

Pro tip: Use replication slots to prevent the primary from discarding WAL that the standby hasn't consumed. In postgresql.conf set max_replication_slots = 5 and create a slot on the primary with SELECT * FROM pg_create_physical_replication_slot('standby_slot');.

Promotion fails

If pg_promote() or pg_ctl promote doesn't work, ensure you're running on the standby (not the primary) and that the standby is in recovery mode. Also, check for active connections—terminate them if needed.

Data inconsistency after failover

After promoting a standby, you might see slightly different data if there was lag. To minimize data loss, use synchronous replication or more frequent WAL archiving.

Security concerns

Always secure replication connections with SSL and strong authentication. Never expose replication credentials in plain text—use environment variables or a secrets manager.

What you learned & what's next

You've set up hot standby replication, turning a single PostgreSQL server into a resilient pair. You understand the core concepts of WAL streaming, recovery mode, and failover, and you can now replicate your database in real time. You've also learned the trade-offs between hot standby and other replication options, and you know how to troubleshoot common pitfalls.

Next, consider automating failover with a tool like Patroni or repmgr to make your system self-healing. You can also explore synchronous replication for zero data loss scenarios, or dive into logical replication for more flexible data movement. With hot standby under your belt, you're ready to build highly available database systems that keep your applications online.

Now go ahead and practice: set up a test cluster on your local machine with two instances, simulate a failure, and promote the standby. You'll build muscle memory that will save you when it really matters.