PostgreSQL Master-Slave Replication Setup Tutorial: Complete Ubuntu VPS Configuration for Production High Availability in 2026

Setting Up PostgreSQL Streaming Replication Environment

PostgreSQL master-slave replication setup requires careful planning and precise configuration. This ensures reliable data synchronization between your primary and standby servers.

This guide walks through a complete streaming replication deployment on Ubuntu 24.04 VPS instances.

You'll need two VPS instances running Ubuntu 24.04 with PostgreSQL 16 installed. The master server handles all write operations. The slave maintains a real-time copy through continuous log shipping.

This configuration provides both read scaling and disaster recovery capabilities.

Prerequisites and Server Requirements

Both servers require PostgreSQL 16, with matching versions to prevent compatibility issues. Install PostgreSQL on each VPS:

sudo apt update
sudo apt install postgresql-16 postgresql-client-16
sudo systemctl start postgresql
sudo systemctl enable postgresql

Verify the installation and note the data directory location:

sudo -u postgres psql -c "SHOW data_directory;"
sudo -u postgres psql -c "SELECT version();"

Network connectivity between servers is essential. Test connection on port 5432 and configure firewall rules if needed.

Your HostMyCode VPS instances include flexible networking options for secure database replication.

Configuring the Master PostgreSQL Server

Master server configuration involves enabling WAL archiving, setting replication parameters, and creating replication users.

These changes require a PostgreSQL restart, so plan accordingly for production environments.

Modifying postgresql.conf for Replication

Edit the main configuration file at /etc/postgresql/16/main/postgresql.conf:

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

Add these replication settings:

# Connection settings
listen_addresses = '*'
port = 5432

# WAL settings
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5
wal_keep_size = 1GB

# Checkpoint settings
checkpoint_timeout = 300
max_wal_size = 2GB
min_wal_size = 100MB

# Hot standby settings
hot_standby = on
hot_standby_feedback = on

The wal_level = replica setting enables sufficient logging for streaming replication.

The max_wal_senders parameter defines how many concurrent replication connections the master can handle.

Setting Up Authentication and Access Control

Configure client authentication in /etc/postgresql/16/main/pg_hba.conf:

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

Add replication access rules (replace slave_ip with your slave server IP):

# Replication connections
host    replication     replica_user    slave_ip/32     md5
host    all             replica_user    slave_ip/32     md5

Create a dedicated replication user with appropriate privileges:

sudo -u postgres psql
CREATE USER replica_user WITH REPLICATION ENCRYPTED PASSWORD 'your_secure_password';
GRANT CONNECT ON DATABASE postgres TO replica_user;
\q

Restart PostgreSQL to apply configuration changes:

sudo systemctl restart postgresql
sudo systemctl status postgresql

Preparing the Slave Server Configuration

The slave server setup involves stopping PostgreSQL and removing existing data. Then create a base backup from the master.

This process establishes the initial synchronization point.

Creating Base Backup from Master

Stop PostgreSQL on the slave server first:

sudo systemctl stop postgresql

Remove the existing data directory and create a fresh backup:

sudo rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pg_basebackup -h master_ip -D /var/lib/postgresql/16/main -U replica_user -W -v -P -R

The -R flag automatically creates recovery configuration files. You'll be prompted for the replica_user password during this process.

Verify the backup completed successfully:

sudo ls -la /var/lib/postgresql/16/main/
sudo cat /var/lib/postgresql/16/main/postgresql.auto.conf

Configuring Slave-Specific Settings

Edit the slave's postgresql.conf for optimal standby performance:

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

Add standby-specific configurations:

# Hot standby settings
hot_standby = on
hot_standby_feedback = on
max_standby_streaming_delay = 30s
max_standby_archive_delay = 30s

# Recovery settings
restore_command = ''
archive_cleanup_command = 'pg_archivecleanup /var/lib/postgresql/16/main/pg_wal %r'

These settings enable read queries on the standby while maintaining reasonable lag tolerance.

For more advanced scenarios, consider implementing connection pooling with PgBouncer to handle increased read traffic.

Testing Replication Functionality

Start PostgreSQL on the slave server and verify replication status:

sudo systemctl start postgresql
sudo systemctl status postgresql

Check replication status from the master server:

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

This query shows active replication connections, current LSN positions, and lag information. A successful setup displays your slave server's IP in the client_addr column.

Verifying Data Synchronization

Test data replication by creating a test database on the master:

sudo -u postgres psql
CREATE DATABASE replication_test;
\c replication_test
CREATE TABLE test_sync (id SERIAL PRIMARY KEY, created_at TIMESTAMP DEFAULT NOW());
INSERT INTO test_sync VALUES (DEFAULT);
SELECT * FROM test_sync;
\q

Check if the data appears on the slave (remember it's read-only):

sudo -u postgres psql -h slave_ip
\c replication_test
SELECT * FROM test_sync;
\q

Monitor replication lag with this query on the slave:

sudo -u postgres psql -c "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()));"

Monitoring and Maintenance Best Practices

Regular monitoring prevents replication issues from becoming critical problems. Set up automated checks for replication lag, connection status, and disk space on both servers.

Create a monitoring script for replication health:

#!/bin/bash
# Check replication lag (run on slave)
LAG=$(sudo -u postgres psql -t -c "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()));")
if (( $(echo "$LAG > 60" | bc -l) )); then
    echo "WARNING: Replication lag is ${LAG} seconds"
    # Add alerting logic here
fi

# Check master connection status
sudo -u postgres psql -h master_ip -c "SELECT 1;" > /dev/null 2>&1
if [ $? -ne 0 ]; then
    echo "ERROR: Cannot connect to master server"
fi

Schedule this script via cron for regular health checks.

WAL file accumulation can consume significant disk space. Monitor the pg_wal directory size regularly.

Handling Common Replication Issues

Connection failures between master and slave typically appear in PostgreSQL logs. Check /var/log/postgresql/postgresql-16-main.log for replication errors.

If replication breaks, you can restart it without rebuilding from scratch:

# On slave, check current replay position
sudo -u postgres psql -c "SELECT pg_last_wal_replay_lsn();"

For persistent connection issues, verify network connectivity and authentication settings.

The database optimization techniques can help maintain optimal replication performance under heavy loads.

Frequently Asked Questions

How do I perform a manual failover to the slave server?

Stop PostgreSQL on the master, then promote the slave by running sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main. Update application connection strings to point to the new master. This process requires careful coordination to prevent data loss.

Can I run SELECT queries on the slave server during replication?

Yes, with hot_standby = on, the slave accepts read-only queries. This capability enables read scaling by distributing SELECT operations across multiple standby servers while maintaining write operations on the master.

What happens if the master server runs out of disk space?

PostgreSQL stops accepting new connections and transactions when disk space is exhausted. Monitor the pg_wal directory size and implement log rotation policies. Set up alerts when disk usage exceeds 80% to prevent this scenario.

How do I add a second slave server to the replication setup?

Follow the same slave configuration process using pg_basebackup from either the master or existing slave. Increase max_wal_senders on the master to accommodate additional connections. Each slave maintains independent replication streams.

What should I do if replication lag becomes too high?

Check network bandwidth between servers, review PostgreSQL configuration parameters like max_standby_streaming_delay, and ensure the slave server has sufficient CPU and I/O capacity. Consider implementing connection pooling to reduce master load.