DevOps-Tools & Best Practices meistern - DevOps Knowledge Hub DevOps-Tools & Best Practices meistern - DevOps Knowledge Hub

Optimierung von PostgreSQL Foreign Data Wrappers für die Multi-Datenbank-Integration

Nutzen Sie PostgreSQL FDWs, um entfernte Datenbanken sicher abzufragen, Pushdown zu optimieren und häufige Performance-Fallen bei der Föderation zu vermeiden.

Optimierung von PostgreSQL Foreign Data Wrappers für die Multi-Datenbank-Integration

PostgreSQL Foreign Data Wrappers helfen, wenn Ihre Daten an mehr als einem Ort gespeichert sind und Sie diese abfragen möchten, ohne sie zuerst kopieren zu müssen. FDWs können entfernte Tabellen wie lokale erscheinen lassen, aber die Leistung und das Transaktionsverhalten hängen stark vom Wrapper, dem Netzwerk und der Abfrageform ab.

Was sind Foreign Data Wrappers?

FDW implementiert den SQL/MED-Standard (Management of External Data) und bietet:

  • Transparenter Zugriff: Abfragen von entfernten Daten mit standardmäßigem SQL
  • Virtuelle Tabellen: Keine Datenverdopplung erforderlich
  • Live-Auslesen aus der Ferne: Abfragen lesen von der entfernten Quelle statt einer lokalen Kopie
  • Join-Fähigkeit: Kombinieren von lokalen und entfernten Daten
  • Schreiboperationen: Einige Wrapper unterstützen INSERT, UPDATE und DELETE

Häufige Anwendungsfälle

  1. Datenbankmigration: Zugriff auf Legacy-Systeme während der Migration
  2. Multi-Tenant-Architektur: Abfragen über mehrere Tenant-Datenbanken hinweg
  3. Datenaggregation: Kombinieren von Daten aus mehreren Quellen
  4. Hybrid Cloud: Verbinden von lokalen und Cloud-Datenbanken
  5. Berichterstattung: Erstellen einer zentralisierten Berichtsdatenbank
  6. Microservices: Abfragen über Servicegrenzen hinweg

Verfügbare Foreign Data Wrappers

postgresql_fdw (Integriert)

  • Verbindung zu anderen PostgreSQL-Datenbanken
  • Vollständige Funktionsunterstützung
  • Beste Leistung

mysql_fdw

  • Verbindung zu MySQL/MariaDB
  • Lese- und Schreibunterstützung

oracle_fdw

  • Verbindung zu Oracle-Datenbanken
  • Häufig verwendet über die Open-Source-Erweiterung oracle_fdw, mit separat installierten Oracle-Client-Bibliotheken

mongodb_fdw

  • Zugriff auf MongoDB-Kollektionen
  • JSON-Datenintegration

file_fdw (Integriert)

  • Lesen von CSV- und Textdateien
  • Logdatei-Analyse

Andere

  • redis_fdw, sqlite_fdw, couchdb_fdw, etc.

Einrichten von postgres_fdw

Schritt 1: Erweiterung installieren

-- Auf der lokalen Datenbank
CREATE EXTENSION postgres_fdw;

Schritt 2: Fremdserver erstellen

CREATE SERVER remote_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote-db.example.com',
        port '5432',
        dbname 'production_db',
        fetch_size '10000'
    );

Schritt 3: Benutzerzuordnung erstellen

-- Lokale Benutzer zu entfernten Anmeldeinformationen zuordnen
CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (
        user 'remote_user',
        password 'secure_password'
    );

-- Alle lokalen Benutzer zuordnen
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (
        user 'readonly_user',
        password 'readonly_pass'
    );

Schritt 4: Fremde Tabellen erstellen

Manuelle Tabellendefinition:

CREATE FOREIGN TABLE remote_users (
    id INTEGER,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (schema_name 'public', table_name 'users');

Gesamtes Schema importieren:

IMPORT FOREIGN SCHEMA public
    FROM SERVER remote_db
    INTO local_schema;

Bestimmte Tabellen importieren:

IMPORT FOREIGN SCHEMA public
    LIMIT TO (users, orders, products)
    FROM SERVER remote_db
    INTO public;

Schritt 5: Fremde Daten abfragen

-- Einfache Abfrage
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '7 days';

-- Lokale und entfernte Daten verknüpfen
SELECT 
    l.order_id,
    l.amount,
    r.username,
    r.email
FROM local_orders l
JOIN remote_users r ON l.user_id = r.id
WHERE l.status = 'pending';

Erweiterte Konfiguration

Leistungsoptimierungsoptionen

CREATE SERVER fast_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'db.example.com',
        port '5432',
        dbname 'mydb',
        
        -- Fetch-Größe: Zeilen pro Roundtrip
        fetch_size '50000',
        
        -- Abfrage-Pushdown-Optimierungen aktivieren
        use_remote_estimate 'true',
        
        -- Fremdserververbindungen innerhalb von PostgreSQL-Sitzungen wiederverwenden
        keep_connections 'on',
        
        -- Parallele Abfrageausführung
        parallel_commit 'on',
        parallel_abort 'on'
    );

Tabellenebenen-Optionen

CREATE FOREIGN TABLE optimized_table (
    id INTEGER,
    data TEXT
)
SERVER remote_db
OPTIONS (
    schema_name 'public',
    table_name 'large_table',
    
    -- Fetch-Größe für diese Tabelle überschreiben
    fetch_size '100000',
    
    -- Entfernte Schätzungen für bessere Abfrageplanung verwenden
    use_remote_estimate 'true'
);

Spaltenebenen-Optionen

CREATE FOREIGN TABLE mapped_columns (
    local_id INTEGER OPTIONS (column_name 'remote_user_id'),
    local_name TEXT OPTIONS (column_name 'remote_username')
)
SERVER remote_db
OPTIONS (table_name 'users');

Schreiboperationen

Schreiboperationen aktivieren

-- Berechtigungen erteilen
GRANT INSERT, UPDATE, DELETE ON remote_users TO app_user;

-- Daten einfügen
INSERT INTO remote_users (username, email)
VALUES ('newuser', '[email protected]');

-- Daten aktualisieren
UPDATE remote_users
SET email = '[email protected]'
WHERE username = 'newuser';

-- Daten löschen
DELETE FROM remote_users WHERE username = 'newuser';

Transaktionen über Datenbanken hinweg

BEGIN;
    -- In lokale Tabelle einfügen
    INSERT INTO local_orders (user_id, amount) VALUES (123, 99.99);
    
    -- Entfernte Tabelle aktualisieren
    UPDATE remote_users SET last_order = NOW() WHERE id = 123;
    
    -- Beide Operationen gemeinsam committen
COMMIT;

PostgreSQL koordiniert entfernte Arbeit über den FDW, aber Sie sollten nicht davon ausgehen, dass jeder Wrapper vollständige verteilte Transaktionsgarantien bietet. Für postgres_fdw überprüfen Sie die Serveroption two_phase_commit, wenn Sie ein Zwei-Phasen-Commit-Verhalten benötigen, und testen Sie die Wiederherstellung nach Abstürzen, bevor Sie sich für kritische Schreibvorgänge darauf verlassen.

Multi-Datenbank-Föderation

Mehrere Datenbanken verbinden

-- Verbindung zur Produktionsdatenbank
CREATE SERVER prod_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'prod.example.com', dbname 'production');

CREATE USER MAPPING FOR PUBLIC SERVER prod_db
    OPTIONS (user 'readonly', password 'pass1');

-- Verbindung zur Analysedatenbank
CREATE SERVER analytics_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'analytics.example.com', dbname 'warehouse');

CREATE USER MAPPING FOR PUBLIC SERVER analytics_db
    OPTIONS (user 'readonly', password 'pass2');

-- Verbindung zur Archivdatenbank
CREATE SERVER archive_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'archive.example.com', dbname 'historical');

CREATE USER MAPPING FOR PUBLIC SERVER archive_db
    OPTIONS (user 'readonly', password 'pass3');

Einheitliche Ansichten über Datenbanken hinweg

-- Tabellen importieren
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (current_orders) 
    FROM SERVER prod_db INTO public;

IMPORT FOREIGN SCHEMA public 
    LIMIT TO (archived_orders) 
    FROM SERVER archive_db INTO public;

-- Einheitliche Ansicht erstellen
CREATE VIEW all_orders AS
SELECT *, 'current' AS source FROM current_orders
UNION ALL
SELECT *, 'archived' AS source FROM archived_orders;

-- Über alle Datenbanken hinweg abfragen
SELECT 
    source,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM all_orders
WHERE created_at > '2024-01-01'
GROUP BY source;

Verbindung zu MySQL

mysql_fdw installieren

# Paketnamen variieren je nach PostgreSQL-Version und Repository.
# Auf Debian/Ubuntu zuerst suchen:
apt search mysql-fdw

# Dann das Paket installieren, das Ihrer PostgreSQL-Hauptversion entspricht.
sudo apt install postgresql-16-mysql-fdw

MySQL-Verbindung konfigurieren

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (
        host 'mysql-db.example.com',
        port '3306'
    );

CREATE USER MAPPING FOR PUBLIC
    SERVER mysql_server
    OPTIONS (
        username 'mysql_user',
        password 'mysql_pass'
    );

CREATE FOREIGN TABLE mysql_products (
    id INTEGER,
    name VARCHAR(100),
    price DECIMAL(10,2)
)
SERVER mysql_server
OPTIONS (dbname 'ecommerce', table_name 'products');

-- MySQL-Daten von PostgreSQL abfragen
SELECT * FROM mysql_products WHERE price > 100;

Leistungsoptimierung

1. WHERE-Klausel-Pushdown verwenden

PostgreSQL schiebt Filter an den entfernten Server:

-- Gut: Filter wird entfernt angewendet
SELECT * FROM remote_users WHERE country = 'US';

-- Abfrageplan prüfen
EXPLAIN (VERBOSE) SELECT * FROM remote_users WHERE country = 'US';
-- Suchen nach: "Remote SQL: SELECT ... WHERE country = 'US'"

2. Datenübertragung begrenzen

-- Schlecht: Holt alle Spalten
SELECT * FROM remote_large_table;

-- Gut: Nur benötigte Spalten
SELECT id, username FROM remote_large_table;

-- Gut: LIMIT verwenden
SELECT * FROM remote_large_table LIMIT 1000;

3. Fetch-Größe optimieren

-- Für große Ergebnismengen
ALTER SERVER remote_db OPTIONS (SET fetch_size '50000');

-- Für kleine, häufige Abfragen
ALTER SERVER remote_db OPTIONS (SET fetch_size '1000');

4. Entfernte Schätzungen verwenden

ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- PostgreSQL fragt entferntes EXPLAIN für genaue Kostenschätzungen ab

5. Indizes auf entfernten Tabellen

-- Sicherstellen, dass entfernte Tabellen geeignete Indizes haben
-- Direkt mit der entfernten Datenbank verbinden:

CREATE INDEX idx_users_country ON users(country);
CREATE INDEX idx_orders_created_at ON orders(created_at);

6. Materialisierte Ansichten für häufig abgerufene Daten

-- Entfernte Daten lokal zwischenspeichern
CREATE MATERIALIZED VIEW cached_remote_data AS
SELECT * FROM remote_users WHERE active = true;

-- Regelmäßig aktualisieren
REFRESH MATERIALIZED VIEW cached_remote_data;

-- Automatische Aktualisierung mit pg_cron, falls diese Erweiterung installiert ist
CREATE EXTENSION pg_cron;
SELECT cron.schedule('refresh-cache', '0 */6 * * *', 
    'REFRESH MATERIALIZED VIEW cached_remote_data');

Überwachung und Fehlerbehebung

Fremdserver anzeigen

SELECT 
    srvname AS server_name,
    srvoptions AS options
FROM pg_foreign_server;

Fremde Tabellen anzeigen

SELECT 
    foreign_table_schema,
    foreign_table_name,
    foreign_server_name
FROM information_schema.foreign_tables;

Benutzerzuordnungen prüfen

SELECT 
    um.umuser::regrole AS local_user,
    fs.srvname AS foreign_server,
    um.umoptions AS options
FROM pg_user_mapping um
JOIN pg_foreign_server fs ON um.umserver = fs.oid;

Abfrageleistung analysieren

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '1 day';

Verbindungsprüfung

Verwenden Sie EXPLAIN (ANALYZE, VERBOSE), um entferntes SQL und Timing zu bestätigen. Für PostgreSQL-zu-PostgreSQL-Verbindungen überprüfen Sie auch die pg_stat_activity auf dem entfernten Server, während eine lange FDW-Abfrage läuft.

Häufige Probleme und Lösungen

Problem 1: Verbindungszeitüberschreitung

Fehler: could not connect to server

Lösungen:

-- Verbindungszeitüberschreitung hinzufügen
ALTER SERVER remote_db OPTIONS (ADD connect_timeout '10');

-- Netzwerkkonnektivität prüfen
-- Von der Shell aus:
psql -h remote-db.example.com -U user -d dbname

Problem 2: Langsame Abfragen

Lösungen:

-- Entfernte Schätzungen aktivieren
ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- Fetch-Größe erhöhen
ALTER SERVER remote_db OPTIONS (SET fetch_size '100000');

-- Prüfen, ob WHERE-Klausel verschoben wird
EXPLAIN VERBOSE SELECT * FROM remote_table WHERE id > 1000;

Problem 3: Authentifizierungsfehler

Fehler: password authentication failed

Lösungen:

-- Benutzerzuordnung aktualisieren
ALTER USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (SET password 'new_password');

-- pg_hba.conf auf dem entfernten Server prüfen
-- Sicherstellen, dass Verbindungen vom lokalen Server erlaubt sind

Problem 4: Spaltentyp-Konflikt

Fehler: column type mismatch

Lösung:

-- Spalten explizit casten
CREATE FOREIGN TABLE remote_data (
    id INTEGER,
    data TEXT,
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (table_name 'source_table');

-- Oder mit korrekten Typen importieren
DROP FOREIGN TABLE remote_data;
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (source_table) 
    FROM SERVER remote_db INTO public;

Sicherheitsüberlegungen

1. Schreibgeschützte Konten verwenden

-- Auf der entfernten Datenbank einen eingeschränkten Benutzer erstellen
CREATE ROLE fdw_readonly;
GRANT CONNECT ON DATABASE production_db TO fdw_readonly;
GRANT USAGE ON SCHEMA public TO fdw_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fdw_readonly;

-- In der Benutzerzuordnung verwenden
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (user 'fdw_readonly', password 'safe_pass');

2. Sichere Passwortspeicherung

-- Sie können eine .pgpass-Datei für libpq-Verbindungen verwenden, anstatt das Passwort
-- in der Benutzerzuordnung zu speichern. Dateibesitz und -modus bestätigen.
-- ~/.pgpass-Format:
-- hostname:port:database:username:password

CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (user 'remote_user');
    -- Passwort aus .pgpass gelesen

3. Netzwerksicherheit

# SSL-Verbindungen verwenden
# In postgresql.conf auf dem entfernten Server:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

-- SSL in der Benutzerzuordnung erzwingen
CREATE SERVER secure_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote.example.com',
        dbname 'mydb',
        sslmode 'require'
    );

Best Practices

  1. Datenübertragung minimieren: Nur benötigte Spalten auswählen
  2. Indizes verwenden: Sicherstellen, dass entfernte Tabellen richtig indiziert sind
  3. Häufig abgerufene Daten zwischenspeichern: Materialisierte Ansichten verwenden
  4. Leistung überwachen: Regelmäßiges EXPLAIN ANALYZE
  5. Schreiboperationen einschränken: Für die meisten FDW-Nutzungen schreibgeschützt bevorzugen
  6. Verbindungspooling verwenden: keep_connections aktivieren
  7. Angemessene fetch_size einstellen: Basierend auf Abfragemustern anpassen
  8. Failover-Szenarien testen: Für Ausfälle der entfernten Datenbank planen
  9. Fremde Abhängigkeiten dokumentieren: Klare Dokumentation pflegen
  10. Regelmäßige Sicherheitsaudits: Benutzerzuordnungen und Berechtigungen überprüfen

Fazit

PostgreSQL Foreign Data Wrappers sind nützlich für die Datenbankföderation, insbesondere wenn Sie Folgendes benötigen:

  • Nahtlose Integration: Abfragen von entfernten Daten mit standardmäßigem SQL
  • Echtzeitzugriff: Keine Datenverdopplung oder Synchronisationsverzögerungen
  • Multi-Datenbank-Unterstützung: PostgreSQL, MySQL, Oracle und mehr
  • Leistung: Abfrage-Pushdown, wenn der Wrapper und die Abfrage dies unterstützen
  • Flexibilität: Zuerst Lesezugriff, dann Schreibzugriff, nachdem das Verhalten des Wrappers überprüft wurde

Beginnen Sie mit schreibgeschützten Abfragen an entfernte Datenbanken, optimieren Sie die Leistung mit geeigneten Indizes und Fetch-Größen, und erweitern Sie dann auf komplexere Integrationen nach Bedarf.