Set Up Streaming Replication

Your primary database just lost a disk and you are staring at a restore script that will take hours. Streaming replication is the safety net that turns that nightmare into a quick failover. By the end of this lesson, you will have a standby PostgreSQL server that continuously mirrors your primary, ready to take over with minimal downtime.

The problem this lesson solves

If you run a single PostgreSQL instance, your database is a single point of failure. A hardware failure, a data-center outage, or even a botched DROP TABLE on the primary can leave you with hours of lost work. Traditional backups—even with PITR (point-in-time recovery)—require you to restore a base backup and replay WAL files. That recovery can take minutes or hours, and during that time your application is offline.

Streaming replication solves this by keeping a standby server continuously up to date with the primary. The standby applies the same WAL records as they are generated, so it is always a near-perfect copy of the primary. If the primary dies, you can promote the standby and be back online in seconds.

But setup is not just running pg_basebackup and hoping. You need to understand replication slots, WAL levels, and connection parameters. Get them wrong and your standby will silently fall behind—or never start at all.

Core concept / mental model

Think of streaming replication like a live mirror. The primary writes every change to a write-ahead log (WAL). A separate process on the standby connects to the primary and continuously pulls those WAL records, applying them to its own data files. The standby is in recovery mode the whole time—it accepts no writes, but it can serve read-only queries.

Here is the flow in words:

1. The primary is configured with wal_level = replica (or higher) so it emits enough WAL information for replication.
2. A base backup of the primary is taken and placed on the standby (usually with pg_basebackup).
3. The standby creates a standby.signal file and a primary_conninfo string so it knows how to reach the primary.
4. The standby starts, enters recovery mode, and connects to the primary, requesting WAL data.
5. From then on, every commit on the primary is shipped to the standby and replayed, keeping them in sync.

🧠 Mental model: The primary is the source of truth; the standby is a warm copy. The WAL stream is the "tape" that continuously records what the primary does, and the standby replays that tape in real time.

How it works step by step

The high-level steps for setting up streaming replication are:

1. Configure the primary — edit postgresql.conf to allow replication connections and set the WAL level.
2. Create a replication user — a dedicated role with REPLICATION privilege for the standby to authenticate as.
3. Take a base backup — copy the primary's data directory to the standby while ensuring consistency.
4. Configure the standby — set up primary_conninfo, create standby.signal, and make sure the data directory is correct.
5. Start the standby — it will begin streaming WAL from the primary.
6. Verify — check that the standby is receiving and applying WAL.

Each step has pitfalls: wrong pg_hba.conf entries, missing standby.signal, or a mismatch in PostgreSQL major versions will stop replication dead in its tracks.

Hands-on walkthrough

Let's do a complete setup on two servers: primary-host and standby-host. We'll assume you have PostgreSQL 15+ installed on both and can SSH between them.

Step 1: Configure the primary

Edit postgresql.conf on the primary and set:

wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

wal_level = replica is the default in modern PostgreSQL, but it is good to be explicit. max_wal_senders controls how many standby servers can connect; max_replication_slots prevents WAL from being discarded before a standby has consumed it.

Now edit pg_hba.conf to allow the standby to connect as the replication user:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replicator      192.168.1.0/24          scram-sha-256

Replace 192.168.1.0/24 with your standby's IP range. The method can be trust for testing but use scram-sha-256 in production.

Restart the primary:

sudo systemctl restart postgresql

Step 2: Create a replication user

Connect to the primary as a superuser and create a role:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_password';

The REPLICATION privilege is what allows the standby to request a base backup and stream WAL.

Step 3: Take a base backup

On the standby server, use pg_basebackup to copy the primary's data directory. First, stop the standby's PostgreSQL if it is running:

sudo systemctl stop postgresql

Remove or clear the standby's data directory (usually /var/lib/postgresql/16/main):

sudo rm -rf /var/lib/postgresql/16/main/*

Now run pg_basebackup as the postgres user:

sudo -u postgres pg_basebackup -h primary-host -U replicator -D /var/lib/postgresql/16/main -P -R -X stream

• -h primary-host — the primary's address.
• -U replicator — the replication user.
• -D ... — the destination directory on the standby.
• -P — show progress.
• -R — automatically write primary_conninfo and create standby.signal.
• -X stream — include WAL during the backup so the standby starts from a consistent point.

You will be prompted for the password. After it finishes, the standby data directory contains a copy of the primary's data plus the necessary configuration.

Step 4: Verify the standby configuration

Check that the standby has a standby.signal file and a primary_conninfo entry in postgresql.conf. If you used -R, they should be there automatically:

sudo ls /var/lib/postgresql/16/main/standby.signal
sudo grep primary_conninfo /var/lib/postgresql/16/main/postgresql.conf

If not, create the signal file and add:

primary_conninfo = 'host=primary-host port=5432 user=replicator password=strong_password application_name=standby1'

Step 5: Start the standby

Start PostgreSQL on the standby:

sudo systemctl start postgresql

Check the logs to see if it connected and began streaming. Look for something like:

LOG:  started streaming WAL from primary at 0/3000000 on timeline 1

Step 6: Verify replication is working

On the primary, run:

SELECT * FROM pg_stat_replication;

You should see a row for the standby, with state = 'streaming' and sent_lsn near write_lsn and flush_lsn. Also check the standby:

SELECT pg_is_in_recovery();
-- returns 't' for true, meaning it is in recovery mode

Now test it: create a table and insert a row on the primary, then query it on the standby (read-only) to confirm it appears:

-- On primary
CREATE TABLE test (id int);
INSERT INTO test VALUES (1);

-- On standby
SELECT * FROM test;  -- should return 1

💡 Pro tip: If you see sent_lsn lagging far behind write_lsn, check network latency or try increasing max_wal_senders and wal_keep_size.

Compare options / when to choose what

Streaming replication is not the only way to set up a standby. Here is a quick comparison:

When to choose what:

• Start with asynchronous streaming replication for most use cases — it balances performance and safety.
• If you cannot afford to lose any committed transaction on failover, switch to synchronous replication (set synchronous_standby_names).
• If your standby is in a different data center with flaky connectivity, consider file-based log shipping as a fallback.
• Use logical replication only when you need to replicate a subset of tables or mix PostgreSQL versions.

Troubleshooting & edge cases

The standby never connects

Check the standby logs for could not connect to the primary server. Common causes:

• pg_hba.conf on the primary does not allow the standby's IP.
• primary_conninfo has a wrong password or user.
• Firewall blocks port 5432 between the servers.
• max_wal_senders is set to 0 (the default is 10, but check).

WAL is not being retained

If the standby falls behind and the primary removes WAL segments before the standby applies them, replication will break. The standby will try to reconnect and fail. To fix:

• Increase wal_keep_size (e.g., wal_keep_size = 1024 for 1 GB).
• Use a replication slot to prevent WAL from being deleted. Create a slot on the primary:

SELECT * FROM pg_create_physical_replication_slot('standby1');

And set primary_slot_name = 'standby1' in primary_conninfo on the standby.

Replication lag is increasing

Possible reasons:

• Network bandwidth is insufficient for the write rate.
• The standby's disk is slow or under provisioned.
• The primary is generating more WAL than the standby can apply (e.g., huge bulk loads).

Monitor pg_stat_replication and pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) on the primary.

"The standby was promoted" — then cannot restart as standby

If you accidentally promoted a standby (by removing standby.signal or running pg_ctl promote), you cannot demote it back to standby without re-cloning from the primary. Use pg_rewind to make it catch up again.

Common mistakes summary

• Forgetting to set wal_level = replica (or leaving it at minimal).
• Using the same data directory for primary and standby.
• Not creating a replication slot and losing WAL.
• Running the standby with hot_standby = off when you want read-only queries.
• Mismatched PostgreSQL major versions between primary and standby.

What you learned & what's next

You now understand how streaming replication works, and you have a working standby server that receives and applies WAL in real time. You configured wal_level, created a replication user, took a base backup with pg_basebackup, and verified replication is streaming. You also know how to compare streaming with synchronous and logical replication, and how to troubleshoot common issues like stalled connections and WAL retention problems.

Next up: In the next lesson, you'll learn how to promote a standby and perform a failover. You'll see how to switch to the standby with minimal downtime, and how to rejoin the old primary as a new standby. That is where replication really pays off.