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:
|
0 1 2 3 4 5 6 7 8 9 |
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
|
0 1 2 3 4 |
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:
|
0 1 2 |
sudo vi /etc/postgresql/15/main/postgresql.conf |
Ensure these settings are applied:
|
0 1 2 3 4 5 6 7 8 9 |
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:
|
0 1 2 3 4 |
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:
|
0 1 2 |
nano /etc/postgresql/15/main/pg_hba.conf |
Add these lines:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# --- 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
|
0 1 2 |
systemctl restart postgresql |
3.4 Create Replication User
Switch to the PostgreSQL user and execute:
|
0 1 2 |
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
|
0 1 2 |
systemctl stop postgresql |
4.2 Remove Existing Data
|
0 1 2 |
rm -rf /var/lib/postgresql/15/main/* |
4.3 Synchronize Data from Master
|
0 1 2 |
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)
|
0 1 2 |
touch /var/lib/postgresql/15/main/standby.signal |
4.5 Configure Connection to Master
Edit postgresql.conf on each slave:
|
0 1 2 |
nano /etc/postgresql/15/main/postgresql.conf |
|
0 1 2 |
primary_conninfo = 'host=postgre.master.x1.demo.internal port=5432 user=replicator password=yourpassword' |
4.6 Restart PostgreSQL on the Slaves
|
0 1 2 |
systemctl start postgresql |
5. Verify Replication
5.1 Check Master Replication Status
On the master, run:
|
0 1 2 |
su - postgres -c "psql -c \"SELECT * FROM pg_stat_replication;\"" |
5.2 Check Slave Replication Status
On each slave, run:
|
0 1 2 |
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:
|
0 1 2 3 4 |
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:
|
0 1 2 |
su - postgres -c "psql -d replication_test -c \"SELECT * FROM sample_table;\"" |
6. Monitoring & Maintenance
6.1 Monitor Logs
|
0 1 2 |
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:
|
0 1 2 |
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:
|
0 1 2 |
crontab -e |
Add the following line to remove WAL files older than 7 days:
|
0 1 2 |
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. 🚀