This guide walks you through setting up PostgreSQL streaming replication on Ubuntu 22.04, either on a single machine (for testing) or across two separate servers (for production). By following this tutorial, you will:

• Understand how primary and replica databases interact.
• Set up a replication user and configure WAL streaming.
• Verify that the replica automatically receives updates from the primary.
• Test read/write behavior and monitor replication lag in real time.

After completing this guide, the replica database will be read-only and asynchronously follow the primary. Any inserts, updates, or deletes on the primary will appear on the replica within seconds.

Key Concepts & FAQs

1. How does replication work?

   • The primary writes all changes to WAL (Write-Ahead Logs).
   • The replica maintains a persistent connection to the primary.
   • The replica pulls WAL entries continuously and replays them locally.
   • This is a pull-based streaming system — the primary does not actively push updates.

2. Can I replicate only a single database?

   • With physical streaming replication (used here), the entire cluster is replicated.
   • If you need to replicate specific databases or tables only, you must use logical replication.

3. What is WAL?

   • WAL stands for Write-Ahead Log.
   • Every change (INSERT, UPDATE, DELETE) is recorded in WAL before being applied.
   • Replicas use WAL to replay changes and stay in sync with the primary.

4. What does read-only mean on the replica?

   • Replicas cannot perform inserts, updates, or deletes.

   • You can only run SELECT queries on a replica.

   • Any attempt to write will produce an error:

     ERROR: cannot execute INSERT in a read-only transaction
     

5. How quickly does replication happen?

   • Asynchronous replication introduces a small lag (typically milliseconds to a few seconds).

   • You can monitor the lag with:

     SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;
     

6. How does the replica know there are updates?

   • The replica keeps a persistent TCP connection to the primary.
   • It tells the primary: "Send me all WAL changes since LSN X."
   • The primary streams WAL entries over this connection.
   • If the replica disconnects temporarily, it will catch up automatically when it reconnects.

7. Can I test replication locally?

   • Yes! You can simulate a production environment on a single server using two clusters with different ports:

     primary → port 5433
     
     replica → port 5434
     

This behaves identically to a multi-server setup, except the IPs in pg_hba.conf differ.

Setup Options

Option A: Production-style setup

• 2 separate servers (VMs or containers)

  primary → handles writes
  
  replica → handles reads
  

• Communicate over network (port 5433 & 5434)

Option B: Local demo (single-server, works fine for learning)

• 1 machine running two PostgreSQL clusters (different data directories and ports).

  primary  → port 5433
  
  replica  → port 5434
  

Let’s do Option B (single-server setup for tutorial). But no worries, the procedure for Option A remains identical, except some ip config in pg_hba.conf, which will be addressed in relevant section.

Note: For Option A, you don't need two clusters since each server already has its own cluster. So in that case, both server possibly will have same port (5432). My recommendation is to follow this tutorial. Because, by the time you complete the tutorial, you'll already learn yourself how to setup Option A.

Step 1: Create two clusters

sudo pg_createcluster 14 primary

sudo pg_createcluster 14 replica

This creates:

/var/lib/postgresql/14/primary

/var/lib/postgresql/14/replica

Each with its own configuration and port.

Note: You can check cluster status by running:

sudo pg_lsclusters

You should see three clusters (Main, Primary, Replica)

Ver Cluster  Port Status          Owner    Data directory                 Log file
14  main    5432 online          postgres /var/lib/postgresql/14/main    /var/log/postgresql/postgresql-14-main.log
14  primary 5433 down            postgres /var/lib/postgresql/14/primary /var/log/postgresql/postgresql-14-primary.log
14  replica 5434 down            postgres /var/lib/postgresql/14/replica /var/log/postgresql/postgresql-14-replica.log

At this point, both clusters exist but only the main cluster is running. The next step is to start the primary.

Step 2: Start only the primary for now

sudo pg_ctlcluster 14 primary start

Then check it’s running:

sudo -u postgres psql -p 5433 -c "SELECT version();"

Step 3: Prepare replication user (In Primary)

sudo -u postgres psql -p 5433

Then inside psql:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replicatorpass';

and exit \q.

Once you reach here, we’ll edit the config files on the primary to enable WAL streaming.

Step 4: Enable replication settings (In Primary)

Edit the main config file for your primary cluster:

sudo nano /etc/postgresql/14/primary/postgresql.conf

Find and update (or add) the following lines:

# Allow WAL (Write-Ahead Log) archiving for replicas
wal_level = replica

# Number of replication connections the primary can accept
max_wal_senders = 5

# How long to keep WAL files for standby to catch up
wal_keep_size = 256MB

# Recommended to prevent disk cleanup before replica catches up
max_wal_size = 1GB

Save and exit (Ctrl+O, Ctrl+X).

Step 5: Allow replication connections (In Primary)

Now open your pg_hba.conf file:

sudo nano /etc/postgresql/14/primary/pg_hba.conf

At the bottom, add this line:

# Allow the replica cluster to connect for replication
host    replication     replicator      127.0.0.1/32            md5

That lets your replica (which will connect from localhost) authenticate using the replicator user and password.

For Option A, replace 127.0.0.1 with your replica server ip.

Step 6: Restart the primary

sudo pg_ctlcluster 14 primary restart

Then check it’s working:

sudo -u postgres psql -p 5433 -c "SHOW wal_level;"

It should show:

 wal_level 
------------
 replica

At this point:

✅ Primary is ready for replication
✅ WAL streaming is enabled
✅ Connection for the replicator user is allowed

Next, we’ll set up the replica cluster.

Step 7: Stop the replica cluster

It was created earlier but not yet initialized as a replica.

sudo pg_ctlcluster 14 replica stop

Step 8: Wipe the replica’s data directory

Since it has its own fresh database, we’ll remove it. Because, replication needs a copy of the primary’s data.

sudo rm -rf /var/lib/postgresql/14/replica/*

Step 9: Take a base backup from the primary

Now, copy the primary’s data into the replica’s directory using PostgreSQL’s built-in backup tool:

sudo -u postgres pg_basebackup -h 127.0.0.1 -p 5433 -D /var/lib/postgresql/14/replica -U replicator -P -R

When prompted for password, enter: replicatorpass

Explanation:

Param	Details
-h 127.0.0.1	connect to the primary on localhost. For Option A, put the ip address of the primary server
-U replicator	use our replication user
-D .../replica	destination data directory
-P	show progress
-R	automatically create a standby signal (tells PostgreSQL this is a replica)

Note: If it shows error pg_basebackup: error: directory "/var/lib/postgresql/14/replica" exists but is not empty, then do the following:

Step 9.1: Remove the replica data folder:

sudo rm -rf /var/lib/postgresql/14/replica

Step 9.2: Re-create an empty directory for the new base backup:

sudo mkdir -p /var/lib/postgresql/14/replica
sudo chown -R postgres:postgres /var/lib/postgresql/14/replica
sudo chmod 700 /var/lib/postgresql/14/replica

Step 9.3: Verify folder permission:

ls -ld /var/lib/postgresql/14/replica

You should see something like:

drwx------ 19 postgres postgres 4096 Oct 19 17:32 /var/lib/postgresql/14/replica

Now run the command again and it'll copy the data folder.

(Optional) Step 10: Adjust replica’s port number

Each cluster must run on a different port. Edit:

sudo nano /etc/postgresql/14/replica/postgresql.conf

Find:

port = 5432

and change it to:

port = 5434

Save and exit.

If the port is already different, then no need to change anything.

Step 11: Start the replica

sudo pg_ctlcluster 14 replica start

Check that it’s running:

sudo -u postgres psql -p 5434 -c "SELECT pg_is_in_recovery();"

If it returns

 pg_is_in_recovery
-------------------
 t

Then it means replica is following the primary.

Step 12: Verify replication

Run this on the primary:

sudo -u postgres psql -p 5433 -c "SELECT client_addr, state, sent_lsn, write_lsn, replay_lsn FROM pg_stat_replication;"

You should see an entry for 127.0.0.1 (your replica). That means the replica is connected and receiving WAL data.

Check cluster status:

sudo pg_lsclusters

You should see:

Ver Cluster Port Status          Owner    Data directory                 Log file
14  main    5432 online          postgres /var/lib/postgresql/14/main    /var/log/postgresql/postgresql-14-main.log
14  primary 5433 online          postgres /var/lib/postgresql/14/primary /var/log/postgresql/postgresql-14-primary.log
14  replica 5434 online,recovery postgres /var/lib/postgresql/14/replica /var/log/postgresql/postgresql-14-replica.log

Verify both cluster is online. Once it’s online, we’ll verify replication with a quick test insert.

At this point, replication is fully set up and streaming

✅ Primary: port 5433 (read/write)
✅ Replica: port 5434 (read-only, async following)

Step 13: Test replication

Step 13.1: Connect to the primary (port 5433)

sudo -u postgres psql -p 5433

Inside psql, create a quick test table and insert a row:

CREATE DATABASE repl_test_db;

-- Switch to our newly created database
\c repl_test_db;

CREATE TABLE repl_test_table (id serial PRIMARY KEY, message text);
INSERT INTO repl_test_table (message) VALUES ('Hello from primary!');

Then check:

SELECT * FROM repl_test_table;

You should see:

 id |      message
----+-------------------
  1 | Hello from primary!

Step 13.2: Connect to the replica (port 5434)

sudo -u postgres psql -p 5434

Now check if the table and data exist:

-- Switch to our database
\c repl_test_db;

SELECT * FROM repl_test_table;

If replication is working, you should see the same row appear automatically:

 id |      message
----+-------------------
  1 | Hello from primary!

✅ That confirms your replica is receiving WAL changes in real time.

Step 13.3: (optional) Test read-only behavior

Try to insert something on the replica:

INSERT INTO repl_test_table (message) VALUES ('This should fail');

It should return:

ERROR:  cannot execute INSERT in a read-only transaction

That’s expected, because replicas only allow SELECT queries.

Congratulations, streaming replication is now working!

• Primary → port 5433 → read/write
• Replica → port 5434 → read-only follower

(Optional) Step 14: Monitoring

Let’s set up a replication monitoring workflow so you can see how your primary and replica are doing in real time.

Step 14.1: Check connected replicas

On the primary:

sudo -u postgres psql -p 5433

SELECT pid, client_addr, state, sent_lsn, write_lsn, replay_lsn
FROM pg_stat_replication;

 pid  | client_addr |   state   | sent_lsn  | write_lsn | replay_lsn
------+-------------+-----------+-----------+-----------+------------
 3254 | 127.0.0.1   | streaming | 0/3081FA0 | 0/3081FA0 | 0/3081FA0

Columns explained:

Column	Meaning
pid	Process ID of the replication connection
client_addr	IP of replica
state	Streaming state (streaming, catchup, etc.)
sent_lsn	WAL location sent to replica
write_lsn	WAL written by replica
replay_lsn	WAL replayed by replica

Step 14.2: Calculate replication lag

Lag = difference between sent WAL on primary and replayed WAL on replica: Run this on the replica (port 5434).

sudo -u postgres psql -p 5434

SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

Returns time lag (e.g., 00:00:02 = 2 seconds behind primary).

 replication_delay
-------------------
 00:00:02.219028

Step 14.3: Quick status queries

On the replica:

-- Is the replica in recovery mode?
SELECT pg_is_in_recovery();

-- Last replayed WAL location
SELECT pg_last_wal_replay_lsn();

-- Last WAL received from primary
SELECT pg_last_wal_receive_lsn();

These help you check if the replica is caught up.

(Optional) Step 14.4: watch in real-time

watch -n 1 "sudo -u postgres psql -p 5434 -c 'SELECT now() - pg_last_xact_replay_timestamp() AS lag;'"

• Refreshes every second
• Shows live replication delay