PostgreSQL replication is a robust solution for ensuring database availability, load balancing, and failover readiness. This guide provides a step-by-step approach to setting up Master-Slave replication on Debian 12, using PostgreSQL 15, along with hostname-based configurations.

1. System Prerequisites

1.1 Server Environment

We will use four Debian 12 servers with PostgreSQL 15 installed:

• Master: postgre.master.x1.demo.internal (10.11.33.53)
• Slave 1: postgre.slave.x1.demo.internal (10.11.33.66)
• Slave 2: postgre.slave.x2.demo.internal (10.11.33.67)
• Slave 3: postgre.slave.x3.demo.internal (10.11.33.68)

1.2 Update Hostname Configuration

Ensure that your /etc/hosts file includes the following lines:

127.0.0.1   localhost
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
10.11.33.53 postgre.master.x1.demo.internal
10.11.33.66 postgre.slave.x1.demo.internal
10.11.33.67 postgre.slave.x2.demo.internal
10.11.33.68 postgre.slave.x3.demo.internal

2. Install PostgreSQL on All Servers

sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo apt install -y postgresql-15-postgis-3 postgresql-15-postgis-3-scripts

---

3. Configure the Master Server

3.1 Enable Replication Settings

Modify the PostgreSQL configuration file on the master:

sudo vi /etc/postgresql/15/main/postgresql.conf

Ensure these settings are applied:

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 512MB
max_wal_size = 4GB
min_wal_size = 1GB
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/15/archive/%f'

Create the archive directory:

mkdir -p /var/lib/postgresql/15/archive
chown -R postgres:postgres /var/lib/postgresql/15/archive
chmod 700 /var/lib/postgresql/15/archive

3.2 Configure Authentication

Modify the pg_hba.conf file to allow replication:

nano /etc/postgresql/15/main/pg_hba.conf

Add these lines:

# --- keep your existing local/loopback lines above ---

# 1) Replication: allow only the replicator from your subnet (MD5 as required)
host    replication     replicator      10.11.0.0/16           md5

# 2) App/client access: open to all (temporarily) but prefer SCRAM
host    all             all             0.0.0.0/0              scram-sha-256
host    all             all             ::/0                   scram-sha-256

# 3) Optional hardening even while “open”:
#    prevent remote superuser logins
host    all             postgres        0.0.0.0/0              reject
host    all             postgres        ::/0                   reject

3.3 Restart the PostgreSQL Service

systemctl restart postgresql

3.4 Create Replication User

Switch to the PostgreSQL user and execute:

su - postgres -c "psql -c \"CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'yourpassword';\""

---

4. Configure the Slave Servers

4.1 Stop PostgreSQL on the Slaves

systemctl stop postgresql

4.2 Remove Existing Data

rm -rf /var/lib/postgresql/15/main/*

4.3 Synchronize Data from Master

su - postgres -c "pg_basebackup -h postgre.master.x1.demo.internal -D /var/lib/postgresql/15/main -U replicator -Fp -Xs -P -R"

4.4 Create Standby Signal (If Needed)

touch /var/lib/postgresql/15/main/standby.signal

4.5 Configure Connection to Master

Edit postgresql.conf on each slave:

nano /etc/postgresql/15/main/postgresql.conf

primary_conninfo = 'host=postgre.master.x1.demo.internal port=5432 user=replicator password=yourpassword'

4.6 Restart PostgreSQL on the Slaves

systemctl start postgresql

---

5. Verify Replication

5.1 Check Master Replication Status

On the master, run:

su - postgres -c "psql -c \"SELECT * FROM pg_stat_replication;\""

5.2 Check Slave Replication Status

On each slave, run:

su - postgres -c "psql -c \"SELECT pg_is_in_recovery();\""

If replication is active, the output should be true.

5.3 Test Replication

On the master, create a test database and table:

su - postgres -c "psql -c \"CREATE DATABASE replication_test;\""
su - postgres -c "psql -d replication_test -c \"CREATE TABLE sample_table (id SERIAL PRIMARY KEY, value TEXT);\""
su - postgres -c "psql -d replication_test -c \"INSERT INTO sample_table (value) VALUES ('Replication Works!');\""

On each slave, verify the data exists:

su - postgres -c "psql -d replication_test -c \"SELECT * FROM sample_table;\""

6. Monitoring & Maintenance

6.1 Monitor Logs

journalctl -u postgresql

6.2 Set Up Automated Failover (Optional)

Consider using pg_auto_failover or Patroni for automated failover.

6.3 Schedule Regular Backups

Even with replication, backups are necessary. Use:

su - postgres -c "pg_dump -h postgre.master.x1.demo.internal -F c -b -v -f /backup/pg_backup.dump mydatabase"

6.4 Automate WAL Cleanup

To prevent excessive storage usage, set up a cron job to delete old WAL files:

crontab -e

Add the following line to remove WAL files older than 7 days:

0 3 * * * sudo find /var/lib/postgresql/15/archive -type f -mtime +7 -delete

This will run daily at 3 AM to free up space while ensuring replication stability.

7. Summary

This guide provides a host-based PostgreSQL Master-Slave replication setup on Debian 12. With real-time data replication, it ensures high availability and disaster recovery. By following these steps, your database cluster is resilient, scalable, and ready for production workloads. 🚀