This is a step-by-step walkthrough of setting up PostgreSQL streaming replication. I wrote it because every guide I found when I was doing this for the first time either skipped steps or assumed knowledge I didn't have yet. This one won't.
What you'll end up with: a primary PostgreSQL server accepting reads and writes, and a standby server that stays in sync with every change in near real-time. If the primary fails, you can promote the standby to take over.
What you need: two servers (or VMs) running the same Linux distro, PostgreSQL installed on both, network access between them. I'll use Ubuntu 22.04 and PostgreSQL 15 throughout.
A quick explanation of how replication works
PostgreSQL streaming replication works by having the primary server continuously send its write-ahead log (WAL) to the standby. The WAL is essentially a record of every change made to the database - every insert, update, delete. The standby replays those changes as they arrive, keeping itself in sync.
Think of it like a very fast, continuous backup that you can actually read from.
Step 1: Set up the primary server
First, check your PostgreSQL version and confirm it's running:
psql --version
sudo systemctl status postgresqlOpen the main PostgreSQL config file. The path depends on your version:
sudo nano /etc/postgresql/15/main/postgresql.confFind and set these values. They may be commented out - remove the # and update the value:
listen_addresses = '*'
wal_level = replica
max_wal_senders = 3
wal_keep_size = 512wallevel = replica tells PostgreSQL to include enough information in the WAL for replication. maxwalsenders is the maximum number of standby connections. walkeep_size is how much WAL to keep on disk (in MB) so a briefly-disconnected standby can catch up.
Step 2: Create a replication user
PostgreSQL requires a dedicated user for replication connections. Do not use your superuser.
sudo -u postgres psqlInside psql:
CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'choose-a-strong-password';
\qStep 3: Configure access control on the primary
Open pg_hba.conf - this file controls who can connect to PostgreSQL and how:
sudo nano /etc/postgresql/15/main/pg_hba.confAdd this line at the bottom, replacing STANDBY_IP with your standby server's IP address:
host replication replicator STANDBY_IP/32 md5This allows the replicator user to connect from the standby server specifically for replication.
Restart PostgreSQL to apply all config changes:
sudo systemctl restart postgresqlVerify it's listening on all interfaces:
sudo ss -tlnp | grep 5432You should see 0.0.0.0:5432 in the output.
Step 4: Take a base backup on the standby
Now move to the standby server.
Stop PostgreSQL if it's running, and clear the data directory. This is destructive - the standby's data directory gets replaced entirely with a copy from the primary.
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/15/main/*Use pgbasebackup to copy the primary's data to the standby. Replace PRIMARYIP with your primary's IP:
sudo -u postgres pg_basebackup \
-h PRIMARY_IP \
-U replicator \
-p 5432 \
-D /var/lib/postgresql/15/main \
-Fp \
-Xs \
-P \
-RWhat these flags mean:
- -Fp: plain format (copies files as-is, not a tar archive)
- -Xs: stream WAL during the backup so nothing is missed
- -P: show progress
- -R: write a standby.signal file and connection info automatically
You'll be prompted for the replicator password. The backup can take a few minutes depending on database size.
Step 5: Configure and start the standby
The -R flag in the previous step should have written the connection info into postgresql.auto.conf. Verify it:
sudo cat /var/lib/postgresql/15/main/postgresql.auto.confYou should see something like:
primary_conninfo = 'host=PRIMARY_IP port=5432 user=replicator password=your-password'Also confirm the standby signal file exists:
ls /var/lib/postgresql/15/main/standby.signalIf either is missing, create them manually. The standby won't replicate without standby.signal.
Start PostgreSQL on the standby:
sudo systemctl start postgresqlStep 6: Verify replication is working
On the primary, connect to PostgreSQL and check the replication status:
sudo -u postgres psql -c "SELECT client_addr, state, sent_lsn, write_lsn, replay_lsn, sync_state FROM pg_stat_replication;"You should see one row with your standby's IP address and state = streaming. If this is empty, replication isn't connected - check the PostgreSQL logs on the standby first:
sudo tail -50 /var/log/postgresql/postgresql-15-main.logOn the standby, confirm it's in recovery mode:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"This should return t (true). If it returns f, the standby thinks it's a primary, which means something went wrong.
Step 7: Test it
Create a test table on the primary:
sudo -u postgres psql -c "CREATE TABLE replication_test (id serial, val text, created_at timestamptz default now());"
sudo -u postgres psql -c "INSERT INTO replication_test (val) VALUES ('hello from primary');"Check for it on the standby (you can query the standby directly, it's in read-only mode):
sudo -u postgres psql -c "SELECT * FROM replication_test;"If you see the row, replication is working.
Common problems and fixes
Standby can't connect to primary
Check that port 5432 is open on the primary's firewall:
``bash``
# On the primary
sudo ufw allow from STANDBY_IP to any port 5432
pghba.conf rejection errors in the log
The pghba.conf entry needs to match exactly. Check that the standby's IP matches what's in the file, and that you're using md5 not scram-sha-256 unless you've configured SCRAM on both ends.
Replication lag is climbing
Check walkeepsize on the primary - if the standby falls behind and the WAL is recycled before the standby can read it, replication will break. Increase walkeepsize or set up a replication slot (more advanced, covered in a future post).
Standby shows state = startup instead of streaming
The standby is still replaying the base backup or initial WAL. Give it a minute. If it stays in startup for more than a few minutes, check the standby's logs.
---
That's it. You now have a working streaming replication setup. The next things to learn from here are replication slots (which prevent the primary from recycling WAL the standby hasn't read yet), synchronous replication (where the primary waits for the standby to confirm writes before acknowledging them to the application), and how to actually perform a failover when you need to promote the standby. I'll cover those separately.
