PostgreSQL Streaming Replikation einrichten

PostgreSQL Streaming Replication ermöglicht Echtzeit-Replikation von Daten auf Standby-Server. Dies erhöht Verfügbarkeit und ermöglicht Read-Scaling.

Architektur

Master-Standby Setup

Schreibzugriffe → Primary Server (Master)
                        │
                        │ WAL Streaming
                        ▼
                  Standby Server (Replica)
                        │
                        ▼
              Lesezugriffe (Hot Standby)

Vorteile

Voraussetzungen

Server-Setup

Primary:  192.168.1.10 (master)
Standby:  192.168.1.11 (replica)

PostgreSQL Version: 16

PostgreSQL installieren

# Auf beiden Servern
apt install postgresql-16

Primary Server konfigurieren

postgresql.conf

# /etc/postgresql/16/main/postgresql.conf

# Netzwerk
listen_addresses = '*'
port = 5432

# WAL-Einstellungen
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB

# Archivierung (optional für PITR)
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'

# Synchrone Replikation (optional)
# synchronous_commit = on
# synchronous_standby_names = 'standby1'

pg_hba.conf

# /etc/postgresql/16/main/pg_hba.conf

# Replikations-Verbindungen erlauben
host    replication     replicator      192.168.1.11/32         scram-sha-256
host    replication     replicator      192.168.1.0/24          scram-sha-256

Replikations-Benutzer erstellen

-- Als postgres-User
sudo -u postgres psql

CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'sicheres_passwort';

-- Prüfen
\du replicator

Primary neustarten

systemctl restart postgresql

Standby Server einrichten

PostgreSQL stoppen

systemctl stop postgresql

Daten-Verzeichnis leeren

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

Basis-Backup vom Primary

# Als postgres-User
sudo -u postgres pg_basebackup \
    -h 192.168.1.10 \
    -U replicator \
    -D /var/lib/postgresql/16/main \
    -Fp -Xs -P -R

# Optionen:
# -Fp: Plain format
# -Xs: WAL während Backup streamen
# -P: Progress anzeigen
# -R: Standby-Konfiguration erstellen

Standby-Signal prüfen

# pg_basebackup mit -R erstellt diese Datei
ls -la /var/lib/postgresql/16/main/standby.signal

# Und postgresql.auto.conf mit Verbindungsinfo
cat /var/lib/postgresql/16/main/postgresql.auto.conf
# primary_conninfo = 'host=192.168.1.10 user=replicator ...'

postgresql.conf (Standby)

# /etc/postgresql/16/main/postgresql.conf

# Hot Standby aktivieren
hot_standby = on

# Feedback an Primary
hot_standby_feedback = on

Standby starten

systemctl start postgresql

Replikation prüfen

Auf Primary

-- Replikations-Status
SELECT * FROM pg_stat_replication;

-- Detailliert
SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    sync_state
FROM pg_stat_replication;

Auf Standby

-- Replikations-Status
SELECT * FROM pg_stat_wal_receiver;

-- Ist Replica?
SELECT pg_is_in_recovery();
-- Sollte 't' (true) zurückgeben

-- Lag prüfen
SELECT
    now() - pg_last_xact_replay_timestamp() AS replication_lag;

Test

-- Auf Primary: Daten einfügen
CREATE TABLE test_replication (id SERIAL, data TEXT);
INSERT INTO test_replication (data) VALUES ('test data');

-- Auf Standby: Daten sollten erscheinen
SELECT * FROM test_replication;

Synchrone Replikation

Primary konfigurieren

# /etc/postgresql/16/main/postgresql.conf

synchronous_commit = on
synchronous_standby_names = 'standby1'  # Name des Standbys

Standby konfigurieren

# /etc/postgresql/16/main/postgresql.auto.conf

primary_conninfo = 'host=192.168.1.10 user=replicator password=xxx application_name=standby1'

Prüfen

-- Auf Primary
SELECT application_name, sync_state FROM pg_stat_replication;
-- sync_state sollte 'sync' sein

Manueller Failover

Standby zum Primary promoten

# Auf Standby
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main

# Oder
sudo -u postgres psql -c "SELECT pg_promote();"

Status prüfen

-- Sollte 'f' (false) sein nach Promotion
SELECT pg_is_in_recovery();

Alten Primary als Standby einrichten

# 1. PostgreSQL stoppen
systemctl stop postgresql

# 2. Daten-Verzeichnis löschen
rm -rf /var/lib/postgresql/16/main/*

# 3. Basis-Backup vom neuen Primary
sudo -u postgres pg_basebackup \
    -h 192.168.1.11 \
    -U replicator \
    -D /var/lib/postgresql/16/main \
    -Fp -Xs -P -R

# 4. Starten
systemctl start postgresql

Automatischer Failover mit Patroni

Installation

apt install patroni python3-etcd

Konfiguration

# /etc/patroni/patroni.yml

scope: postgres-cluster
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.10:8008

etcd:
  hosts: 192.168.1.20:2379,192.168.1.21:2379,192.168.1.22:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      parameters:
        max_connections: 100
        shared_buffers: 256MB

  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.10:5432
  data_dir: /var/lib/postgresql/16/main
  authentication:
    replication:
      username: replicator
      password: sicheres_passwort
    superuser:
      username: postgres
      password: postgres_passwort

Service

systemctl enable patroni
systemctl start patroni

# Status
patronictl -c /etc/patroni/patroni.yml list

pg_rewind für schnellen Resync

Nach Failover

# Alter Primary als Standby
sudo -u postgres pg_rewind \
    --target-pgdata=/var/lib/postgresql/16/main \
    --source-server='host=192.168.1.11 user=postgres dbname=postgres'

Voraussetzungen

# Auf allen Servern
wal_log_hints = on
# Oder
# initdb mit --data-checksums

Monitoring

Replikations-Lag

-- Auf Primary
SELECT
    application_name,
    client_addr,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_lag,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag
FROM pg_stat_replication;

Monitoring-Skript

#!/bin/bash
# check_replication.sh

LAG=$(sudo -u postgres psql -t -c "
    SELECT COALESCE(
        EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())),
        0
    )::INT
")

if [ "$LAG" -gt 300 ]; then
    echo "CRITICAL: Replication lag is ${LAG} seconds"
    exit 2
elif [ "$LAG" -gt 60 ]; then
    echo "WARNING: Replication lag is ${LAG} seconds"
    exit 1
else
    echo "OK: Replication lag is ${LAG} seconds"
    exit 0
fi

Prometheus

-- pg_stat_replication für Prometheus exportieren
SELECT
    'pg_replication_lag_seconds{application_name="' || application_name || '"} ' ||
    COALESCE(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn), 0)
FROM pg_stat_replication;

Read Replicas nutzen

Connection Pooling mit PgBouncer

# /etc/pgbouncer/pgbouncer.ini

[databases]
myapp_primary = host=192.168.1.10 dbname=myapp
myapp_replica = host=192.168.1.11 dbname=myapp

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Anwendungs-Konfiguration

# Django settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': '192.168.1.10',  # Primary für Schreiben
        'NAME': 'myapp',
    },
    'replica': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': '192.168.1.11',  # Replica für Lesen
        'NAME': 'myapp',
    }
}

Zusammenfassung

Fazit

PostgreSQL Streaming Replication ist robust und performant. Hot Standby ermöglicht Lesezugriffe auf Replicas. Für automatischen Failover empfiehlt sich Patroni mit etcd. Synchrone Replikation garantiert Datenkonsistenz bei Failover. Read Replicas mit PgBouncer verbessern die Skalierbarkeit. Die native Replikation ist für die meisten Hochverfügbarkeits-Anforderungen ausreichend.