Entwicklung / Server & Infrastruktur / PostgreSQL / Streaming Replication

Praxis-Guide: PostgreSQL Streaming Replication & Read Replicas

Einrichtung von asynchroner Streaming-Replikation in PostgreSQL zur Erhöhung der Ausfallsicherheit (High Availability) und zur Leseskalierung (Read Replicas).

---

---

⚙️ 1. Primary Node Konfiguration (10.0.0.1)

In /etc/postgresql/16/main/postgresql.conf:

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on

In /etc/postgresql/16/main/pg_hba.conf Replikations-User erlauben:

host    replication     replicator      10.0.0.0/24             md5

-- Replikations-Benutzer anlegen
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'geheim_repl_123';

sudo systemctl restart postgresql

---

⚙️ 2. Standby / Read Replica Node Konfiguration (10.0.0.2)

# 1. Dienst stoppen & altes Datenverzeichnis leeren
sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*

# 2. Base Backup vom Primary laden
sudo -u postgres pg_basebackup -h 10.0.0.1 -D /var/lib/postgresql/16/main -U replicator -v -P -R

# 3. Replica starten
sudo systemctl start postgresql

---

🔍 3. Replikations-Status prüfen

Auf dem Primary Server:

SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication;

---

🔗 Verwandte Themen

• PostgreSQL Performance Tuning – Performance-Tuning
• PostgreSQL PgBouncer Connection Pooling – Connection Pooling
• PostgreSQL Backup & Recovery – Backup-Strategien