PostgreSQL 12 Streaming Replication Setup (Ubuntu 20.04)

At a glance#

• Purpose: Configure streaming replication from a PostgreSQL primary to a standby server.
• Applies to: PostgreSQL 12 on Ubuntu 20.04.
• Risk: High - requires a base backup and restart of both servers.
• Time: 1-2 hours.

Overview#

This guide explains how to set up PostgreSQL 12 streaming replication between two Ubuntu 20.04 servers.

Step 1: Install PostgreSQL on Both Servers#

sudo apt update
sudo apt install postgresql postgresql-contrib -y

Verify installation:

psql --version
sudo systemctl status postgresql

Both servers should have PostgreSQL 12.22.

Step 2: Configure the Master Server (198.51.100.142)#

2.1 Edit postgresql.conf#

File path:

/etc/postgresql/12/main/postgresql.conf

Uncomment or add the following lines:

listen_addresses = '*'
wal_level = replica
max_wal_senders = 5
wal_keep_segments = 64
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/12/main/archive/%f'

Create archive directory:

sudo mkdir -p /var/lib/postgresql/12/main/archive
sudo chown postgres:postgres /var/lib/postgresql/12/main/archive

2.2 Edit pg_hba.conf#

File path:

/etc/postgresql/12/main/pg_hba.conf

Add the following line at the end:

host    replication     replicator     198.51.100.143/32       md5

2.3 Create a Replication User#

sudo -u postgres psql

Then inside psql:

CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'StrongPassword';
\q

2.4 Restart PostgreSQL#

sudo systemctl restart postgresql

Verify configuration:

sudo -u postgres psql -c "show wal_level;"

Expected output:

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

Step 3: Configure the Slave Server (198.51.100.143)#

3.1 Stop PostgreSQL#

sudo systemctl stop postgresql

3.2 Remove Existing Cluster Data#

sudo -u postgres rm -rf /var/lib/postgresql/12/main/*

3.3 Take Base Backup from Master#

Run this on the slave:

sudo -u postgres pg_basebackup -h 198.51.100.142 -D /var/lib/postgresql/12/main -U replicator -P -R

Enter the password of user replicator when prompted.

✅ The -R flag automatically:

• Creates a standby.signal file.
• Configures replication connection in postgresql.auto.conf.

3.4 Verify Base Backup#

ls /var/lib/postgresql/12/main/

Ensure the file standby.signal exists.

3.5 Start PostgreSQL on Slave#

sudo systemctl start postgresql

Step 4: Verify Replication#

On Master:#

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

Expected output:

 client_addr  |   state   | sync_state
--------------+-----------+------------
 198.51.100.143 | streaming | async
(1 row)

Test Data Replication#

On Master:

sudo -u postgres psql

Then:

CREATE DATABASE testdb;
\c testdb
CREATE TABLE demo(id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO demo(name) VALUES ('hello from master');
\q

On Slave:

sudo -u postgres psql -d testdb -c "SELECT * FROM demo;"

Expected output:

 id |       name
----+-------------------
  1 | hello from master
(1 row)

Replication verified ✅

Step 5: Useful Notes#

Hide /root Permission Warning#

Before running sudo -u postgres ...:

cd /tmp

or start a full session as postgres:

sudo -i -u postgres

Promote Slave (Manual Failover)#

If the master fails:

sudo -u postgres pg_ctlcluster 12 main promote

The slave becomes the new master.

Check Replication Status Anytime#

On master:

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

Final Verification Checklist#

Optional (Advanced)#

• Synchronous replication:

Add to master’s postgresql.conf:

``` synchronous_standby_names = 'slave1'

```

Restart PostgreSQL.

• Automatic failover:

Use tools like repmgr or pg_auto_failover.

Overview#

This section simulates a real-world database workload on the master to test replication performance and stability.

pgbench is a built-in PostgreSQL tool that generates realistic read/write transactions, mimicking banking or e-commerce workloads.

Step 1: Install pgbench on Master#

sudo apt install postgresql-contrib -y

Step 2: Create the Benchmark Database#

sudo -u postgres createdb pgbenchdb

Step 3: Initialize Sample Schema and Data#

sudo -u postgres pgbench -i -s 10 pgbenchdb

Options explained:

• i → Initialize tables and load sample data
• s 10 → Scale factor (creates ~1 million rows total).

Increase to 50 or 100 for heavier load.

This creates 4 default tables:

pgbench_accounts
pgbench_branches
pgbench_tellers
pgbench_history

Step 4: Run the Simulation#

Run a 5-minute load test using 10 concurrent clients:

sudo -u postgres pgbench -c 10 -T 300 pgbenchdb

Options explained:

• c 10 → 10 concurrent clients
• T 300 → Run for 300 seconds (5 minutes)

Example output:

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
number of clients: 10
number of threads: 10
duration: 300 s
tps = 1550.345 (including connections establishing)

Step 5: Monitor Replication in Real Time (on Master)#

Run this command in another terminal to watch replication lag live:

watch -n 2 "sudo -u postgres psql -c \"SELECT client_addr, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;\""

Example output:

 client_addr  |   state   | sync_state | write_lag | flush_lag | replay_lag
--------------+-----------+------------+------------+------------+------------
 198.51.100.143 | streaming | async      | 0 ms       | 0 ms       | 3 ms

If lag values remain small, replication is performing well ✅

Step 6: Verify Data on the Slave#

After the test finishes, confirm data exists on the slave:

sudo -u postgres psql -d pgbenchdb -c "\dt"
sudo -u postgres psql -d pgbenchdb -c "SELECT count(*) FROM pgbench_accounts;"

You should see the same results on both master and slave.

Optional: Heavier Workloads#

Increase intensity:

sudo -u postgres pgbench -c 20 -T 600 -P 10 pgbenchdb

Options:

• P 10 → Prints progress every 10 seconds
• Increase c (clients) or T (duration) for more stress testing.

To reinitialize from scratch:

sudo -u postgres dropdb pgbenchdb
sudo -u postgres createdb pgbenchdb
sudo -u postgres pgbench -i -s 20 pgbenchdb

Verification Checklist#

Result#

You now have a realistic PostgreSQL workload simulation running on your master, continuously streaming transactions to your slave.

This confirms replication reliability under real operational load