Implementierung von Logical Replication in PostgreSQL: Multi-Master und selektive Datensynchronisation

Einführung

Logical Replication in PostgreSQL ermöglicht es Ihnen, spezifische Tabellen, Zeilen oder sogar Spalten zwischen Datenbanken zu replizieren, was ausgefeilte Datenverteilungsmuster ermöglicht. Im Gegensatz zur physischen Streaming-Replikation, die den gesamten Datenbankcluster kopiert, bietet Logical Replication eine fein abgestufte Kontrolle darüber, welche Daten repliziert werden und wohin.

Logical vs. Physical Replication

Physical Streaming Replication

• Repliziert den gesamten Datenbankcluster
• Binäre Replikation
• Nur lesbare Replikate
• Gleiche PostgreSQL-Version erforderlich
• Geringerer Overhead

Logical Replication

• Selektive Tabellen-/Zeilenreplikation
• Kompatibel mit verschiedenen Versionen
• Beschreibbare Abonnenten
• Höherer Overhead
• Flexible Datenverteilung

Anwendungsfälle für Logical Replication

1. Selektive Datenverteilung: Replizieren Sie spezifische Tabellen in verschiedene Regionen
2. Multi-Master-Setups: Mehrere beschreibbare Datenbanken mit bidirektionaler Synchronisation
3. Cross-Version-Upgrades: Replizieren von alten zu neuen PostgreSQL-Versionen
4. Datenaggregation: Konsolidieren von Daten aus mehreren Quellen
5. GDPR-Compliance: Replizieren Sie nur nicht-sensible Spalten

Voraussetzungen und Einrichtung

Konfigurationsanforderungen

Auf dem Publisher (Quelle):

# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Auf dem Subscriber (Ziel):

# postgresql.conf
max_replication_slots = 10
max_logical_replication_workers = 10
max_worker_processes = 16

PostgreSQL neu starten

sudo systemctl restart postgresql

Netzwerkkonfiguration

Stellen Sie sicher, dass die Datenbanken kommunizieren können:

# Testen Sie die Verbindung vom Subscriber zum Publisher
psql -h publisher.example.com -U replication_user -d source_db

Konfigurieren Sie pg_hba.conf auf dem Publisher:

# Erlauben Sie Replikationsverbindungen
host    all    replication_user    subscriber_ip/32    md5

Grundlegende Einrichtung der Logical Replication

Schritt 1: Replikationsbenutzer erstellen

Auf dem Publisher:

CREATE ROLE replication_user WITH REPLICATION LOGIN PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replication_user;

Schritt 2: Quelltabellen erstellen

Auf dem Publisher:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (username, email) VALUES 
    ('alice', '[email protected]'),
    ('bob', '[email protected]');

Schritt 3: Veröffentlichung erstellen

Auf dem Publisher:

-- Alle Tabellen veröffentlichen
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- Oder spezifische Tabellen veröffentlichen
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- Oder mit Zeilenfiltern (PostgreSQL 15+)
CREATE PUBLICATION active_users FOR TABLE users WHERE (status = 'active');

Veröffentlichungen anzeigen:

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

Schritt 4: Replikattabellen erstellen

Auf dem Subscriber:

-- Tabellen müssen identische Struktur haben
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW()
);

Schritt 5: Abonnement erstellen

Auf dem Subscriber:

CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher.example.com port=5432 dbname=source_db user=replication_user password=secure_password'
    PUBLICATION my_publication;

Schritt 6: Replikation überprüfen

Auf dem Publisher:

SELECT * FROM pg_stat_replication;
SELECT * FROM pg_replication_slots;

Auf dem Subscriber:

SELECT * FROM pg_stat_subscription;
SELECT * FROM users;  -- Sollte replizierte Daten anzeigen

Erweiterte Konfiguration

Spaltenebenen-Replikation (PostgreSQL 15+)

Replizieren Sie nur spezifische Spalten:

-- Auf dem Publisher: Replizieren Sie nur nicht-sensible Spalten
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    credit_card VARCHAR(20),  -- Wird nicht repliziert
    created_at TIMESTAMP
);

CREATE PUBLICATION customer_basic 
    FOR TABLE customers (id, name, email, created_at);

Zeilenfilterung

Replizieren Sie nur aktive Datensätze:

CREATE PUBLICATION active_data 
    FOR TABLE orders WHERE (status IN ('pending', 'processing'));

Regionale Datenverteilung:

CREATE PUBLICATION us_customers 
    FOR TABLE customers WHERE (country = 'US');

CREATE PUBLICATION eu_customers 
    FOR TABLE customers WHERE (country IN ('UK', 'DE', 'FR'));

Mehrere Veröffentlichungen

-- Publisher: Erstellen Sie mehrere Veröffentlichungen
CREATE PUBLICATION oltp_data FOR TABLE users, orders;
CREATE PUBLICATION analytics_data FOR TABLE logs, metrics;

-- Subscriber: Abonnieren Sie mehrere Veröffentlichungen
CREATE SUBSCRIPTION multi_sub
    CONNECTION 'host=publisher port=5432 dbname=mydb user=repuser password=pass'
    PUBLICATION oltp_data, analytics_data;

Bidirektionale Replikation (Multi-Master)

Einrichtung der Zwei-Wege-Synchronisation

Datenbank A Konfiguration:

-- Veröffentlichung erstellen
CREATE PUBLICATION db_a_pub FOR TABLE shared_table;

-- Abonnement von Datenbank B
CREATE SUBSCRIPTION db_a_sub
    CONNECTION 'host=db-b.example.com dbname=mydb user=repuser'
    PUBLICATION db_b_pub
    WITH (origin = none);  -- Verhindert Replikationsschleifen

Datenbank B Konfiguration:

-- Veröffentlichung erstellen
CREATE PUBLICATION db_b_pub FOR TABLE shared_table;

-- Abonnement von Datenbank A
CREATE SUBSCRIPTION db_b_sub
    CONNECTION 'host=db-a.example.com dbname=mydb user=repuser'
    PUBLICATION db_a_pub
    WITH (origin = none);

Konfliktlösung

Logical Replication verwendet standardmäßig "last write wins":

-- Setzen Sie die Replica-Identität zur Konfliktverfolgung
ALTER TABLE shared_table REPLICA IDENTITY FULL;

Strategien zur Konflikterkennung:

1. Zeitstempelbasiert: Fügen Sie eine Spalte updated_at hinzu

CREATE TABLE shared_table (
    id SERIAL PRIMARY KEY,
    data TEXT,
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_shared_table_timestamp
    BEFORE UPDATE ON shared_table
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

2. Versionsnummerierung:

CREATE TABLE shared_table (
    id SERIAL PRIMARY KEY,
    data TEXT,
    version INTEGER DEFAULT 1
);

Optionen für die initiale Datensynchronisation

Option 1: Automatische Kopie (Standard)

-- Subscriber kopiert automatisch bestehende Daten
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = true);  -- Standard

Option 2: Manuelle initiale Synchronisation

Für große Datensätze pg_dump verwenden:

# Dump spezifischer Tabellen vom Publisher
pg_dump -h publisher.example.com -U postgres -d source_db \n    -t users -t orders --no-owner --no-acl > initial_data.sql

# Laden in den Subscriber
psql -h subscriber.example.com -U postgres -d target_db < initial_data.sql

# Erstellen Sie ein Abonnement ohne initiale Kopie
psql -h subscriber.example.com -U postgres -d target_db -c "
    CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=source_db user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = false);
"

Option 3: Parallele initiale Synchronisation

-- Verwenden Sie mehrere Worker für schnellere initiale Synchronisation
CREATE SUBSCRIPTION fast_sync
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (
        copy_data = true,
        streaming = on,
        synchronous_commit = off
    );

Überwachung der Logical Replication

Publisher-Überwachung

-- Anzeigen der Replikationsslots
SELECT 
    slot_name,
    plugin,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size
FROM pg_replication_slots;

-- Anzeigen aktiver Replikationsverbindungen
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_lag
FROM pg_stat_replication;

Subscriber-Überwachung

-- Anzeigen des Abonnementstatus
SELECT 
    subname,
    pid,
    received_lsn,
    latest_end_lsn,
    pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) AS lag
FROM pg_stat_subscription;

-- Überprüfen auf Replikationsfehler
SELECT * FROM pg_stat_subscription WHERE last_msg_receipt_time < NOW() - INTERVAL '5 minutes';

Überwachungsskript

#!/bin/bash
# logical-replication-monitor.sh

echo "=== Publisher Status ==="
psql -h publisher -d mydb -c "
    SELECT slot_name, active, 
           pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
    FROM pg_replication_slots;"

echo ""
echo "=== Subscriber Status ==="
psql -h subscriber -d mydb -c "
    SELECT subname, pid, 
           pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) AS lag
    FROM pg_stat_subscription;"

Fehlerbehebung

Problem 1: Abonnement empfängt keine Daten

Abonnementstatus überprüfen:

SELECT subname, pid, subenabled, subconninfo FROM pg_subscription;

Abonnement aktivieren, falls deaktiviert:

ALTER SUBSCRIPTION my_sub ENABLE;

Auf Fehler überprüfen:

SELECT * FROM pg_stat_subscription;

Problem 2: Replikationsverzögerung wächst

Langsame Tabellen identifizieren:

SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY (n_tup_ins + n_tup_upd + n_tup_del) DESC;

Worker-Prozesse erhöhen:

# postgresql.conf auf dem Subscriber
max_logical_replication_workers = 20
max_worker_processes = 30

Problem 3: Replikationsslot-Bloat

Slot-Nutzung überprüfen:

SELECT 
    slot_name,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Inaktive Slots löschen:

SELECT pg_drop_replication_slot('inactive_slot_name');

Problem 4: Initialer Synchronisationsfehler

Initiale Synchronisation neu starten:

-- Abonnement löschen und neu erstellen
DROP SUBSCRIPTION my_sub;

CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = true);

Leistungsoptimierung

1. Verwenden Sie eine geeignete Replica-Identität

-- Standard: Nur PRIMARY KEY
ALTER TABLE my_table REPLICA IDENTITY DEFAULT;

-- Vollständig: Alle Spalten (höherer Overhead)
ALTER TABLE my_table REPLICA IDENTITY FULL;

-- Index: Verwenden Sie einen spezifischen eindeutigen Index
CREATE UNIQUE INDEX replica_idx ON my_table(col1, col2);
ALTER TABLE my_table REPLICA IDENTITY USING INDEX replica_idx;

2. Deaktivieren Sie Constraints während der initialen Synchronisation

-- Deaktivieren Sie Trigger vorübergehend für schnellere initiale Ladung
ALTER TABLE my_table DISABLE TRIGGER ALL;

-- Nach Abschluss der initialen Synchronisation
ALTER TABLE my_table ENABLE TRIGGER ALL;

3. Paralleles Anwenden (PostgreSQL 16+)

CREATE SUBSCRIPTION parallel_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (streaming = parallel);

Best Practices

1. Verwenden Sie immer eine Replica-Identität: Stellen Sie sicher, dass Tabellen einen PRIMARY KEY oder eine UNIQUE Constraint haben
2. Überwachen Sie die Replikationsverzögerung: Richten Sie Warnungen für Verzögerungen > 100MB oder 5 Minuten ein
3. Verwenden Sie Zeilenfilter sorgfältig: Filter werden auf dem Publisher ausgewertet und beeinflussen die Leistung
4. Planen Sie für Konflikte: Implementieren Sie Konflikterkennung in Multi-Master-Setups
5. Testen Sie Failover-Prozeduren: Üben Sie das Wechseln zwischen Datenbanken
6. Regelmäßige Wartung: Bereinigen Sie alte Replikationsslots

Fazit

Logical Replication bietet leistungsstarke Datenverteilungsfähigkeiten für PostgreSQL. Wichtige Vorteile:

• Selektive Tabellen- und Zeilenreplikation
• Kompatibilität mit verschiedenen Versionen
• Bidirektionale Multi-Master-Konfigurationen
• Fein abgestufte Kontrolle der Datenschutzbestimmungen

Beginnen Sie mit einfacher unidirektionaler Replikation, testen Sie gründlich und erweitern Sie dann zu komplexeren Topologien, falls erforderlich.