Set Up PostgreSQL Streaming Replication on a Linux VPS

PostgreSQL streaming replication setup gives you a standby server that continuously replays WAL from a primary, so you have a live copy of your data ready for failover or read-only queries. In this guide you will configure the primary, create a replication role and a physical replication slot, clone the primary to a second Linux VPS with pg_basebackup, and confirm the standby is streaming.

The commands below assume PostgreSQL 16 on Debian 12 or Ubuntu 24.04 and a second server running the same PostgreSQL major version. Paths on AlmaLinux and Rocky differ: the data directory is usually /var/lib/pgsql/16/data and the service is postgresql-16, so adjust accordingly.

Before you start

• Two Linux VPSs on the same private network or with a firewall rule allowing TCP 5432 between them. The primary in the examples is 203.0.113.10, the standby 203.0.113.20.
• PostgreSQL installed and running on both, with the same major version. Check with psql --version and SHOW server_version;.
• Root or sudo access on both servers.
• The standby's data directory must be empty. Everything currently in it will be destroyed.

Set up PostgreSQL streaming replication on the primary

Enable the replication settings

Edit postgresql.conf (on Debian and Ubuntu the live file is in /etc/postgresql/16/main/, not inside the data directory):

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 512MB

wal_level = replica is enough for streaming replication. wal_keep_size is a safety net for standbys that fall behind; the replication slot below is what actually prevents WAL from being removed early.

Restart PostgreSQL to apply wal_level:

sudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW wal_level;"

Expect replica.

Create the replication role and slot

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'use-a-long-random-password';
SELECT pg_create_physical_replication_slot('standby1');

Use a physical slot, not a logical one. The slot stops the primary from recycling WAL the standby still needs. The trade-off: if the standby stays down, WAL piles up on the primary and can fill the disk. Monitor free space or drop the slot when you decommission the standby:

SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained FROM pg_replication_slots;

Allow the standby in pg_hba.conf

Add one line near the top of pg_hba.conf, above any broad host all all rule:

host    replication     replicator      203.0.113.20/32        scram-sha-256

Reload, and open the firewall on the primary:

sudo systemctl reload postgresql
sudo ufw allow from 203.0.113.20 to any port 5432 proto tcp

Clone the primary to the standby

On the standby, stop PostgreSQL, empty the data directory, and run pg_basebackup. Find the directory first, because it is not always the path you expect:

psql --version
sudo systemctl stop postgresql
sudo -u postgres psql -c "SHOW data_directory;" 2>/dev/null || ls /var/lib/postgresql/

Then, replacing 16 with your major version:

sudo -u postgres rm -rf /var/lib/postgresql/16/main
sudo -u postgres pg_basebackup \
  -h 203.0.113.10 -p 5432 -U replicator \
  -D /var/lib/postgresql/16/main \
  -Fp -Xs -R -S standby1 -P

What the flags do:

• -Fp writes a plain file copy, which is fine for a local directory.
• -Xs streams the WAL generated during the backup so the copy is consistent.
• -R creates standby.signal and appends primary_conninfo to postgresql.auto.conf. This is what makes the server a standby.
• -S standby1 uses the replication slot you created.
• -P prints progress.

When asked, enter the replicator password. It is not stored, so add it to primary_conninfo in /var/lib/postgresql/16/main/postgresql.auto.conf:

primary_conninfo = 'host=203.0.113.10 port=5432 user=replicator password=your-password application_name=standby1'

Lock the file down so the password is not world-readable:

sudo chmod 600 /var/lib/postgresql/16/main/postgresql.auto.conf
sudo chown postgres:postgres /var/lib/postgresql/16/main/postgresql.auto.conf

Start the standby and confirm streaming

sudo systemctl start postgresql
sudo -u postgres psql -c "SELECT status, sender_host, latest_end_lsn FROM pg_stat_wal_receiver;"

status should read streaming. The standby is now read-only and will refuse writes with cannot execute INSERT in a read-only transaction.

Check it works

On the primary, list connected standbys:

SELECT client_addr, application_name, state, sync_state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

You want one row with state = streaming and a lag of a few kilobytes or less.

Now test end to end. On the primary:

sudo -u postgres psql -c "CREATE TABLE repl_test (id int primary key, note text); INSERT INTO repl_test VALUES (1, 'hello');"

On the standby:

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

The row appears within a second. If it does, replication is working. Drop the test table on the primary when you are done.

Troubleshooting

• FATAL: no pg_hba.conf entry for replication connection from host — the host replication line is missing, in the wrong order, or PostgreSQL was not reloaded. Check sudo -u postgres psql -c "SELECT pg_reload_conf;" and confirm the standby's exact source IP.
• could not connect to the primary server: Connection refused — listen_addresses does not include the standby-facing address, or the firewall is blocking 5432. Test from the standby with nc -vz 203.0.113.10 5432.
• requested WAL segment... has already been removed — the standby was offline long enough for WAL to be recycled (no slot, or the slot was dropped). Re-run pg_basebackup; you cannot recover by copying individual segments here.
• Standby starts but pg_stat_wal_receiver is empty — standby.signal or primary_conninfo is missing. pg_basebackup -R creates both; verify the files exist in the data directory.
• Primary disk filling up — an inactive slot is retaining WAL. Check pg_replication_slots for active = false, then either fix the standby or SELECT pg_drop_replication_slot('standby1');.

Next steps

Streaming replication is the base layer. For a complete recovery plan, add PostgreSQL point-in-time recovery with WAL archiving, which lets you restore to a specific moment rather than to the latest replayed LSN. If you plan to send read queries to the standby, tune autovacuum and VACUUM settings on both servers, and read PostgreSQL remote access with pg_hba.conf before opening the standby to other hosts.