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
Code language: CSS (css)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'
Code language: JavaScript (javascript)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
Code language: JavaScript (javascript)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
Code language: PHP (php)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';\""
Code language: JavaScript (javascript)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/*
Code language: JavaScript (javascript)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"
Code language: JavaScript (javascript)4.4 Create Standby Signal (If Needed)
touch /var/lib/postgresql/15/main/standby.signal
Code language: JavaScript (javascript)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'
Code language: JavaScript (javascript)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;\""
Code language: JavaScript (javascript)5.2 Check Slave Replication Status
On each slave, run:
su - postgres -c "psql -c \"SELECT pg_is_in_recovery();\""
Code language: JavaScript (javascript)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!');\""
Code language: JavaScript (javascript)On each slave, verify the data exists:
su - postgres -c "psql -d replication_test -c \"SELECT * FROM sample_table;\""
Code language: JavaScript (javascript)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"
Code language: JavaScript (javascript)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
Code language: JavaScript (javascript)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. 🚀
