PostgreSQL streaming replication keeps one or more standby servers continuously in sync with a primary by shipping Write-Ahead Log (WAL) records over a network connection. The standby replays those records, can serve read-only queries, and can be promoted to primary if the original server fails.

In this tutorial you will set up asynchronous streaming replication between two Ubuntu 24.04 servers running PostgreSQL 16, protect it with a replication slot, verify that data flows, measure replication lag, and practice a manual failover. An optional section shows how to switch to synchronous replication.

Prerequisites

To follow this tutorial, you will need:

  • Two servers running Ubuntu 24.04 LTS, for example two CubePath VPS in the same location. This guide calls them primary and standby.
  • A non-root user with sudo privileges on both servers.
  • A private network between the servers (recommended) or public IPs. The examples use primary_ip (10.0.0.10) and standby_ip (10.0.0.11); replace them with your own addresses.
  • Enough disk on the standby to hold a full copy of the primary's data directory.

Both servers must run the same PostgreSQL major version. Physical streaming replication does not work across major versions (for example 15 to 16).

Step 1 - Installing PostgreSQL on both servers

Ubuntu 24.04 ships PostgreSQL 16 in its main repository. Run the following on both servers:

sudo apt update
sudo apt install -y postgresql

Check that the cluster is running:

pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

On Ubuntu, configuration files live in /etc/postgresql/16/main/ and data lives in /var/lib/postgresql/16/main/. Keep that split in mind: you will edit files in /etc and replace the data directory on the standby.

Step 2 - Configuring the primary server

All steps in this section run on the primary.

PostgreSQL 16 already has sensible replication defaults (wal_level = replica, max_wal_senders = 10, max_replication_slots = 10), so the only mandatory change is making PostgreSQL listen on an address the standby can reach. Open the main configuration file:

sudo nano /etc/postgresql/16/main/postgresql.conf

Find listen_addresses, uncomment it and set it to localhost plus the primary's private IP:

listen_addresses = 'localhost,10.0.0.10'

While the file is open, confirm the replication settings. You do not need to change them, but make sure nobody has lowered them:

wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on

Save and close the file.

Creating the replication role

Create a dedicated role that can only replicate, not log in to databases. The \password command prompts for the password so it never ends up in your shell history:

sudo -u postgres psql

CREATE ROLE replicator WITH REPLICATION LOGIN;
\password replicator
\q

Choose a strong password and keep it at hand; you will use it on the standby.

Allowing the standby to connect

Replication connections are matched against the special replication database name in pg_hba.conf. Open the file:

sudo nano /etc/postgresql/16/main/pg_hba.conf

Add this line at the end, restricting access to the standby's IP:

host    replication     replicator      10.0.0.11/32            scram-sha-256

Restart PostgreSQL to apply listen_addresses (a reload is not enough for that setting):

sudo systemctl restart postgresql

Confirm PostgreSQL now listens on the private IP:

sudo ss -ltnp | grep 5432

LISTEN 0      200        10.0.0.10:5432      0.0.0.0:*    users:(("postgres",pid=4121,fd=7))
LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=4121,fd=6))

Opening the firewall

If UFW is active, allow port 5432 only from the standby:

sudo ufw allow from 10.0.0.11 to any port 5432 proto tcp
sudo ufw status

Step 3 - Preparing the standby

The following steps run on the standby.

A standby must start from an exact copy of the primary's data directory, so the empty cluster created by the package has to go. Stop PostgreSQL and remove its data directory:

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main

Store the replication password in a .pgpass file owned by the postgres user, so neither pg_basebackup nor the running standby needs the password in a config file:

sudo -u postgres bash -c 'echo "10.0.0.10:5432:replication:replicator:your_strong_password" > ~/.pgpass && chmod 600 ~/.pgpass'

Replace your_strong_password with the password you set in Step 2. Test the connection before copying anything:

sudo -u postgres psql "host=10.0.0.10 user=replicator dbname=replication replication=true" -c "IDENTIFY_SYSTEM;"

      systemid       | timeline |  xlogpos  | dbname
---------------------+----------+-----------+--------
 7412345678901234567 |        1 | 0/3000148 |
(1 row)

If this fails, fix connectivity, pg_hba.conf or the firewall before continuing (see Troubleshooting).

Step 4 - Cloning the primary with pg_basebackup

pg_basebackup copies the primary's data directory over a replication connection. The flags used here do the rest of the standby setup for you:

  • -X stream streams the WAL generated during the copy, so the backup is consistent.
  • -C -S standby1 creates a physical replication slot named standby1 on the primary. The slot makes the primary keep WAL until this standby has received it, so the standby can never fall too far behind to recover.
  • -R writes standby.signal and the primary_conninfo connection string into the data directory, which turns the copy into a standby.

Run it as the postgres user:

sudo -u postgres pg_basebackup -h 10.0.0.10 -U replicator \
  -D /var/lib/postgresql/16/main \
  -X stream -C -S standby1 -R -P

30832/30832 kB (100%), 1/1 tablespace

Check that the standby files were created:

sudo ls /var/lib/postgresql/16/main/standby.signal
sudo cat /var/lib/postgresql/16/main/postgresql.auto.conf

/var/lib/postgresql/16/main/standby.signal
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replicator passfile=''/var/lib/postgresql/.pgpass'' channel_binding=prefer host=10.0.0.10 port=5432 sslmode=prefer ...'
primary_slot_name = 'standby1'

Naming the standby

The primary identifies each standby by its application_name, which defaults to the standby's cluster_name. Ubuntu sets that to 16/main on every server, so give the standby a meaningful name. Open its configuration:

sudo nano /etc/postgresql/16/main/postgresql.conf

Set:

cluster_name = 'standby1'

This makes monitoring easier and is required if you later enable synchronous replication.

Step 5 - Starting the standby and verifying replication

Start PostgreSQL on the standby:

sudo systemctl start postgresql

Look at the log to confirm it connected to the primary:

sudo tail -n 5 /var/log/postgresql/postgresql-16-main.log

LOG:  entering standby mode
LOG:  redo starts at 0/4000028
LOG:  consistent recovery state reached at 0/4000100
LOG:  database system is ready to accept read-only connections
LOG:  started streaming WAL from primary at 0/5000000 on timeline 1

On the primary, pg_stat_replication shows one row per connected standby:

sudo -u postgres psql -x -c "SELECT application_name, client_addr, state, sync_state, replay_lag FROM pg_stat_replication;"

-[ RECORD 1 ]----+----------
application_name | standby1
client_addr      | 10.0.0.11
state            | streaming
sync_state       | async
replay_lag       | 00:00:00.000912

state = streaming means the standby is receiving WAL in real time. On the standby, confirm it is in recovery mode:

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

 pg_is_in_recovery
-------------------
 t
(1 row)

Step 6 - Testing that data replicates

Create a test database and table on the primary:

sudo -u postgres psql -c "CREATE DATABASE repl_test;"
sudo -u postgres psql -d repl_test -c "CREATE TABLE t (id serial PRIMARY KEY, created_at timestamptz DEFAULT now()); INSERT INTO t DEFAULT VALUES;"

Read it from the standby:

sudo -u postgres psql -d repl_test -c "SELECT * FROM t;"

 id |          created_at
----+-------------------------------
  1 | 2026-09-25 10:14:03.512391+00
(1 row)

Writes on the standby are rejected, which confirms it is read-only:

sudo -u postgres psql -d repl_test -c "INSERT INTO t DEFAULT VALUES;"

ERROR:  cannot execute INSERT in a read-only transaction

Drop the test database on the primary when you are done; the drop replicates too:

sudo -u postgres psql -c "DROP DATABASE repl_test;"

Step 7 - Monitoring replication lag and slots

Two queries on the primary cover most day-to-day monitoring. The first shows how many bytes of WAL each standby still has to replay:

sudo -u postgres psql -c "SELECT application_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes, replay_lag FROM pg_stat_replication;"

 application_name | replay_lag_bytes |   replay_lag
------------------+------------------+-----------------
 standby1         | 0 bytes          | 00:00:00.000874

The second shows how much WAL each replication slot is holding back. A slot whose standby is gone keeps WAL forever and can fill the primary's disk, so watch this value:

sudo -u postgres psql -c "SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots;"

 slot_name | active | retained_wal
-----------+--------+--------------
 standby1  | t      | 56 bytes

To put an upper bound on that risk, set max_slot_wal_keep_size in /etc/postgresql/16/main/postgresql.conf on the primary (for example max_slot_wal_keep_size = 20GB) and reload with sudo systemctl reload postgresql. If a standby falls behind by more than that, its slot is invalidated and the standby must be rebuilt with pg_basebackup, but the primary stays up.

Feed these two queries into your existing monitoring system (Prometheus postgres_exporter, Zabbix, or similar) and alert when active is false or the lag keeps growing.

Step 8 - Promoting the standby (manual failover)

If the primary fails, promote the standby so it accepts writes. Run this on the standby:

sudo -u postgres psql -c "SELECT pg_promote();"

 pg_promote
------------
 t
(1 row)

Verify it left recovery mode:

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

 pg_is_in_recovery
-------------------
 f
(1 row)

Then point your applications to the new primary. Two points matter after a failover:

  • Never let the old primary come back as a writable server. If both accept writes, the data diverges. Keep it stopped until you rebuild it.
  • Rebuild the old primary as a standby of the new one by repeating Steps 3 to 5 with the roles reversed (update pg_hba.conf, the firewall and .pgpass accordingly). pg_rewind can speed this up for large databases, but it requires wal_log_hints = on or data checksums to have been enabled beforehand.

For automatic failover, use a cluster manager such as Patroni instead of scripting promotion yourself.

Optional - Enabling synchronous replication

With asynchronous replication, a transaction committed on the primary can be lost if the primary crashes before the standby receives it. Synchronous replication makes the primary wait for the standby to confirm each commit, at the cost of higher write latency and of writes blocking if the standby is down.

On the primary, edit /etc/postgresql/16/main/postgresql.conf:

synchronous_standby_names = 'FIRST 1 (standby1)'
synchronous_commit = on

Reload PostgreSQL:

sudo systemctl reload postgresql

Check that the standby is now synchronous:

sudo -u postgres psql -c "SELECT application_name, sync_state FROM pg_stat_replication;"

 application_name | sync_state
------------------+------------
 standby1         | sync

Troubleshooting

no pg_hba.conf entry for replication connection: the pg_hba.conf line on the primary is missing, uses the wrong IP, or was added without a reload. Check it and run sudo systemctl reload postgresql.

connection refused or timeout from the standby: PostgreSQL is not listening on the private IP (check listen_addresses and restart) or UFW blocks the port (sudo ufw status).

password authentication failed for user "replicator": the password in ~postgres/.pgpass on the standby does not match, or the file is not 600. PostgreSQL ignores a .pgpass with looser permissions.

requested WAL segment ... has already been removed: the standby fell behind and the WAL it needs is gone, usually because no slot was used or the slot was invalidated by max_slot_wal_keep_size. Rebuild the standby from Step 3.

Standby log shows database system identifier differs: the standby's data directory did not come from this primary. Remove it and run pg_basebackup again.

Conclusion

You now have a PostgreSQL 16 primary streaming WAL to a read-only hot standby through a replication slot, with queries to monitor lag and retained WAL and a tested promotion procedure. The standby can already take read-only traffic such as reports and analytics.

As next steps, consider adding a second standby for synchronous durability, managing failover automatically with Patroni, and encrypting replication traffic with TLS (ssl = on on the primary and hostssl entries in pg_hba.conf) if it crosses an untrusted network.