PostgreSQL streaming replication creates real-time copies of your database across multiple servers. This gives you both high availability and read scalability. This tutorial walks through setting up master-slave PostgreSQL replication on Ubuntu 24.04 VPS instances, complete with monitoring and basic failover procedures.
You'll configure one primary server that handles all writes. You'll also set up one or more standby servers that receive continuous updates. The standby can serve read queries while staying synchronized with the master.
Prerequisites and Server Requirements
You need at least two Ubuntu 24.04 VPS instances with PostgreSQL 16 installed. Both servers should have at least 2GB RAM and reliable network connectivity between them.
Install PostgreSQL on both servers:
sudo apt update
sudo apt install postgresql-16 postgresql-contrib-16
sudo systemctl enable postgresql
sudo systemctl start postgresqlSet a password for the postgres user on both systems:
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your_secure_password';"The HostMyCode VPS hosting platform provides the consistent network performance needed for reliable database replication between server instances.
Configure the Master Server
The master server handles all write operations and streams changes to standby servers.
Edit /etc/postgresql/16/main/postgresql.conf:
listen_addresses = '*'
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
wal_keep_size = 64MB
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/16/main/archive/%f && cp %p /var/lib/postgresql/16/main/archive/%f'
log_replication_commands = onCreate the archive directory:
sudo -u postgres mkdir -p /var/lib/postgresql/16/main/archive
sudo chmod 700 /var/lib/postgresql/16/main/archiveConfigure authentication in /etc/postgresql/16/main/pg_hba.conf. Add these lines for replication connections:
# Replication connections
host replication replicator 192.168.1.0/24 md5
host all all 192.168.1.0/24 md5Replace 192.168.1.0/24 with your actual network range.
Create a dedicated replication user:
sudo -u postgres createuser --replication --pwprompt replicatorRestart PostgreSQL to apply configuration changes:
sudo systemctl restart postgresqlSet Up the Standby Server
The standby server receives streaming updates from the master.
Stop PostgreSQL first:
sudo systemctl stop postgresqlRemove the existing data directory. Then create a base backup from the master:
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pg_basebackup -h MASTER_IP -D /var/lib/postgresql/16/main -U replicator -v -P -WReplace MASTER_IP with your master server's IP address. Enter the replicator password when prompted.
Create a standby.signal file to mark this as a standby server:
sudo -u postgres touch /var/lib/postgresql/16/main/standby.signalConfigure the standby connection in /var/lib/postgresql/16/main/postgresql.auto.conf:
primary_conninfo = 'host=MASTER_IP port=5432 user=replicator password=replicator_password'
restore_command = 'cp /var/lib/postgresql/16/main/archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /var/lib/postgresql/16/main/archive %r'Start PostgreSQL on the standby:
sudo systemctl start postgresqlVerify Replication Status
Check replication on the master server:
sudo -u postgres psql -c "SELECT client_addr, state, sync_state FROM pg_stat_replication;"This should show your standby server's IP address with streaming state.
On the standby server, verify it's receiving data:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"This should return t (true). This confirms the server is in recovery mode and receiving replication data.
Test data synchronization by creating a test database on the master:
sudo -u postgres createdb test_replication
sudo -u postgres psql -d test_replication -c "CREATE TABLE test_data (id SERIAL PRIMARY KEY, created_at TIMESTAMP DEFAULT NOW());"Check if the database appears on the standby within seconds:
sudo -u postgres psql -l | grep test_replicationConfigure Streaming Replication Security
Secure the replication connection with SSL.
Generate SSL certificates on the master server:
sudo -u postgres openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=postgres-master"
sudo -u postgres chmod og-rwx server.keyCopy the certificate to the standby server. Enable SSL in postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'Update the standby's connection string to use SSL:
primary_conninfo = 'host=MASTER_IP port=5432 user=replicator password=replicator_password sslmode=require'The database security hardening guide covers additional PostgreSQL security measures that complement replication setup.
Monitor Replication Lag and Performance
Track replication health with monitoring scripts.
Create /usr/local/bin/check_replication.sh:
#!/bin/bash
LAG_QUERY="SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))::int;"
LAG_SECONDS=$(sudo -u postgres psql -t -c "$LAG_QUERY" | xargs)
if [ "$LAG_SECONDS" -gt 60 ]; then
echo "WARNING: Replication lag is ${LAG_SECONDS} seconds"
exit 1
else
echo "OK: Replication lag is ${LAG_SECONDS} seconds"
exit 0
fiMake it executable and test:
sudo chmod +x /usr/local/bin/check_replication.sh
sudo /usr/local/bin/check_replication.shMonitor key replication metrics on the master:
sudo -u postgres psql -c "SELECT application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"Track WAL file generation rate:
sudo -u postgres psql -c "SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());"Handle Failover Scenarios
When the master fails, promote the standby to become the new primary:
sudo -u postgres pg_promoteThis immediately promotes the standby to accept write connections. Update your application connection strings to point to the new master.
To bring the old master back online as a standby, rebuild it from the new master using pg_basebackup. Follow the same steps used to set up the original standby.
For automatic failover, consider tools like Patroni or repmgr. The PostgreSQL high availability setup guide covers these advanced clustering solutions.
Optimize Replication Performance
Tune these parameters based on your network and workload:
# In postgresql.conf on master
max_wal_size = 2GB
min_wal_size = 80MB
wal_buffers = 16MB
synchronous_commit = off
wal_writer_delay = 200msFor high-traffic applications, consider asynchronous replication to reduce write latency. Set synchronous_standby_names = '' on the master.
Monitor network bandwidth usage between servers:
sudo iftop -i eth0 -f "host MASTER_IP or host STANDBY_IP"The database performance tuning tutorial includes additional optimization techniques that apply to PostgreSQL replication environments.
PostgreSQL replication requires reliable network connectivity and consistent server performance between your database nodes. HostMyCode VPS hosting provides the stable infrastructure needed for production database replication with predictable network latency and high availability.
Troubleshooting Common Issues
Check PostgreSQL logs when replication stops working:
sudo tail -f /var/log/postgresql/postgresql-16-main.logCommon problems and solutions:
Replication slot conflicts: List and drop unused slots on the master:
sudo -u postgres psql -c "SELECT slot_name, active FROM pg_replication_slots;"
sudo -u postgres psql -c "SELECT pg_drop_replication_slot('slot_name');"WAL file accumulation: Increase wal_keep_size or set up WAL archiving properly:
sudo du -sh /var/lib/postgresql/16/main/pg_wal/Connection timeouts: Adjust wal_sender_timeout and wal_receiver_timeout in postgresql.conf.
Reset replication from scratch if corruption occurs. Stop the standby, remove its data directory, and rebuild from a fresh pg_basebackup.
FAQ
Can I run read queries on the standby server?
Yes, standby servers support read-only queries by default. This is called "hot standby" mode and helps distribute read traffic across your database cluster.
How much replication lag is normal?
Typical replication lag should stay under 1 second on local networks. Lags over 10 seconds usually indicate network issues, high master load, or configuration problems.
What happens if the network connection between servers breaks?
The standby will fall behind but continue running read queries using cached data. When connectivity returns, it automatically resumes streaming replication from where it left off.
Can I set up multiple standby servers?
Yes, PostgreSQL supports multiple standbys connected to one master. Each standby connects independently and receives the same replication stream.
Do I need shared storage for PostgreSQL replication?
No, streaming replication uses separate storage on each server. The standby maintains its own copy of the database that stays synchronized through the replication stream.