Aurora PostgreSQL-kompatible Integration mit Remote-PostgreSQL-Datenbanken

In diesem Abschnitt wird die Integration von Amazon Aurora PostgreSQL-Compatible Edition mit Remote-PostgreSQL-Datenbanken mithilfe der Erweiterung postgres_fdw (Foreign-Data Wrapper) oder der Funktion beschrieben. dblink Das postgres_fdw Modul bietet föderierte Abfragefunktionen für die Interaktion mit entfernten PostgreSQL-basierten Datenbanken. Die Remote-Datenbanken können bei Amazon oder vor Ort verwaltet EC2 oder selbst verwaltet werden. Die postgres_fdw Erweiterung ist in allen derzeit unterstützten Versionen von Amazon Relational Database Service (Amazon RDS) für PostgreSQL und Aurora PostgreSQL-kompatibel verfügbar.

Mit der postgres_fdw Erweiterung können Sie auf Daten aus entfernten PostgreSQL-Datenbanken zugreifen und diese abfragen, als ob es sich um lokale Tabellen handeln würde. Die postgres_fdw Erweiterung unterstützt auch Folgendes:

• Versionsübergreifende Kompatibilität für den Zugriff auf Daten von externen PostgreSQL-Servern, auf denen unterschiedliche Versionen ausgeführt werden.

• Transaktionsmanagement, das dazu beiträgt, Datenkonsistenz und Integrität sicherzustellen, wenn Sie Operationen auf lokalen und externen PostgreSQL-Servern ausführen.

• Verteilte Transaktionen, die Atomizität (eine Eigenschaft von ACID-Transaktionen) und Isolationsgarantien bieten, wenn Sie Operationen auf mehreren externen PostgreSQL-Servern ausführen. Auf diese Weise wird sichergestellt, dass entweder alle Operationen in einer Transaktion festgeschrieben werden oder dass keine festgeschrieben werden, wodurch Datenkonsistenz und Integrität gewahrt werden.

Obwohl das dblink Modul eine Möglichkeit bietet, mit entfernten PostgreSQL-Datenbanken zu interagieren, unterstützt es keine verteilten Transaktionen oder andere erweiterte Funktionen. Wenn Sie erweiterte Funktionen benötigen, sollten Sie stattdessen die postgres_fdw Erweiterung verwenden. Die postgres_fdw Erweiterung bietet mehr Integrations- und Optimierungsmöglichkeiten.

Anwendungsfälle und allgemeine Schritte für postgres_fdw

Die Verwendung der postgres_fdw Erweiterung mit Aurora PostgreSQL-kompatibel unterstützt die folgenden Anwendungsfälle und Szenarien:

• Föderierte Abfragen und Datenintegration ‒ Abfragen und Kombinieren von Daten aus mehreren PostgreSQL-Datenbanken in einer einzigen Aurora PostgreSQL-kompatiblen Instanz

• Auslagern von Lese-Workloads ‒ Verbindung zu Read Replicas externer PostgreSQL-Server herstellen, leseintensive Workloads auslagern und die Abfrageleistung verbessern

• Datenbankübergreifende Operationen ‒ Ausführen vonINSERT, UPDATEDELETE, und COPY Vorgängen in mehreren PostgreSQL-Datenbanken, wodurch datenbankübergreifende Datenmanipulations- und Wartungsaufgaben ermöglicht werden

Verwenden Sie zur Konfiguration postgres_fdw die folgenden allgemeinen Schritte:

1. Stellen Sie mithilfe eines PostgreSQL-Clients eine Connect zu Ihrem Aurora PostgreSQL-kompatiblen Cluster her und erstellen Sie die Erweiterung: postgres_fdw

   CREATE EXTENSION postgres_fdw;

   Diese Erweiterung bietet die Funktionalität, um eine Verbindung zu entfernten PostgreSQL-Datenbanken herzustellen.

2. Erstellen Sie einen fremden Server, der mit dem my_fdw_target CREATE SERVER Befehl benannt wird. Dieser Server stellt die entfernte PostgreSQL-Datenbank dar, zu der Sie eine Verbindung herstellen möchten. Geben Sie den Datenbanknamen, den Hostnamen und den SSL-Modus als Optionen für diesen Server an.

3. Stellen Sie sicher, dass die erforderlichen Sicherheitsgruppen und Netzwerkkonfigurationen vorhanden sind, damit Aurora PostgreSQL-Compatible eine Verbindung zur Remote-PostgreSQL-Datenbank herstellen kann.

   Wenn die Remote-Datenbank lokal gehostet wird, müssen Sie möglicherweise ein virtuelles privates Netzwerk (VPN) oder eine Verbindung konfigurieren. AWS Direct Connect

   Führen Sie den folgenden Befehl aus:

   CREATE SERVER my_fdw_target Foreign Data Wrapper postgres_fdw OPTIONS (DBNAME 'postgres', HOST 'SOURCE_HOSTNAME', SSLMODE 'require');

4. Erstellen Sie eine Benutzerzuordnung für den dbuser Benutzer auf dem my_fdw_target Server. Diese Zuordnung ordnet den dbuser Benutzer und das Passwort auf der lokalen Aurora PostgreSQL-kompatiblen Instance dem entsprechenden Benutzer in der Remote-Datenbank zu.

   CREATE USER MAPPING FOR dbuser SERVER my_fdw_target OPTIONS (user 'DBUSER', password 'PASSWORD');

   Dieser Schritt ist notwendig, um sich zu authentifizieren und Zugriff auf die Remote-Datenbank zu gewähren.

5. Erstellen Sie eine Fremdtabelle customer_fdw mit dem Namen der my_fdw_target Server- und Benutzerzuordnung, die Sie zuvor eingerichtet haben:

   CREATE FOREIGN TABLE customer_fdw( id int, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');

   Die customer_fdw Tabelle ist der customers Tabelle in der vom my_fdw_target Server angegebenen entfernten Datenbank zugeordnet. Die Fremdtabelle hat dieselbe Struktur wie die entfernte Tabelle, sodass Sie mit den entfernten Daten interagieren können, als ob es sich um eine lokale Tabelle handeln würde.

6. Sie können verschiedene Datenmanipulationsoperationen an der customer_fdw Fremdtabelle durchführen, z. B. INSERTUPDATE, und SELECT Abfragen. Das Skript veranschaulicht das Einfügen einer neuen Zeile und das Aktualisieren einer vorhandenen Zeile, das Löschen eines Datensatzes und das Kürzen einer Tabelle in der entfernten Tabelle mithilfe der customers customer_fdw Fremdtabelle:

   INSERT INTO customer_fdw values ( 1, 'Test1', '[e-mail]', 'LMS1', '888888888');
   INSERT INTO customer_fdw values ( 2, 'Test2', '[e-mail]', 'LMS2', '999999999');
   INSERT INTO customer_fdw values ( 3, 'Test3', '[e-mail]', 'LMS3', '111111111');
   UPDATE customer_fdw set contactnumber = '123456789' where id = 2;
   DELETE FROM customer_fdw where id = 1;
   TRUNCATE TABLE customer_fdw;

7. Sie können einen SQL-Abfrageplan validieren, indem Sie die EXPLAIN Anweisung verwenden, um den Abfrageplan für eine SELECT Abfrage in der Tabelle zu analysieren: customer_fdw

   EXPLAIN select * from customer_fdw where id =1;

   Auf diese Weise können Sie besser verstehen, wie die Abfrage ausgeführt wird und wie Sie sie optimieren können. Weitere Informationen zur Verwendung der EXPLAIN Anweisung finden Sie unter Optimieren der PostgreSQL-Abfrageleistung in AWS Prescriptive Guidance.

8. Verwenden Sie den folgenden Befehl, um mehrere Tabellen aus der entfernten Datenbank in ein lokales Schema zu importieren: IMPORT FOREIGN SCHEMA

   CREATE SCHEMA public_fdw;
   IMPORT FOREIGN SCHEMA public LIMIT TO (employees, departments)
       FROM SERVER my_fdw_target INTO public_fdw;

   Dadurch werden lokale Fremdtabellen für die angegebenen Tabellen im public_fdw Schema erstellt. In diesem Beispiel handelt es sich bei den spezifischen Tabellen um Mitarbeiter und Abteilungen.

9. Führen Sie die folgenden Befehle aus, um einem bestimmten Datenbankbenutzer die erforderlichen Berechtigungen zu gewähren, damit er auf die FDW und den zugehörigen Fremdserver zugreifen und diese verwenden kann:

   GRANT USAGE ON FOREIGN SERVER my_fdw_target TO targetdbuser;
   GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO targetdbuser;

   Dieser Schritt kann nützlich sein, wenn mehrere Benutzer Zugriff auf die Fremdtabellen benötigen, die durch den Foreign Data Wrapper ermöglicht werden.

Beachten Sie bei der Verwendung von Fremdtabellen die folgenden Einschränkungen:

• Der Zugriff auf Daten aus einer Remotequelle kann zu Datenübertragungskosten und Leistungseinbußen führen, die durch Netzwerklatenz verursacht werden. Leistungsprobleme können sich bei großen Datensätzen oder Abfragen bemerkbar machen, die eine umfangreiche Datenübertragung zwischen der Aurora PostgreSQL-kompatiblen Instance und der Remote-Datenquelle erfordern.

• Bei komplexen Abfragen, die Funktionen wie Fensterfunktionen beinhalten, funktionieren rekursive Abfragen möglicherweise nicht wie erwartet oder werden möglicherweise nicht unterstützt.

• Derzeit wird die Kennwortverschlüsselung nicht unterstützt. Implementieren Sie Kontrollen, um sicherzustellen, dass nur autorisierte Benutzer auf die entfernten Datenbanken zugreifen FDWs und Daten aus diesen abrufen können.

• Primärschlüsseleinschränkungen können nicht für Fremdtabellen definiert werden, wie der folgende Versuch eines Skripts zur Tabellenerstellung zeigt:

  CREATE FOREIGN TABLE customer_fdw2( id int primary key, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');
  Primary keys cannot be defined on Foreign table

• Die ON CONFLICT Klausel für INSERT Anweisungen wird in Fremdtabellen nicht unterstützt, wie im folgenden Beispiel gezeigt:

  INSERT INTO customer_fdw (id, name, emailid, projectname, contactnumber) VALUES
  (1, 'test1', '[e-mail]', 'LMS', 11111111 ),
  (3, 'test3', '[e-mail]', 'LMS', 22222222 )
  ON CONFLICT (id) DO UPDATE 
  SET name = EXCLUDED.name;
  On Conflict option doesnot work.

Bereinigen

Führen Sie die folgenden Befehle aus, um die erstellten Objekte zu bereinigen, einschließlich des my_fdw_target Löschens der postgres_fdw Erweiterung, des Servers, der Benutzerzuordnungen und der Fremdtabellen:

DROP FOREIGN TABLE customer_fdw;
DROP USER MAPPING for postgres;
DROP SERVER  my_fdw_target;
DROP EXTENSION postgres_fdw cascade;

Verwenden Sie dblink, um Verbindungen herzustellen

Die dblink Modulfunktionen bieten eine alternative Möglichkeit, Verbindungen herzustellen und SQL-Anweisungen in entfernten PostgreSQL-Datenbanken auszuführen. Die dblink Lösung ist eine einfachere und flexiblere Möglichkeit, einmalige Abfragen oder Operationen in entfernten Datenbanken auszuführen. Für komplexere Szenarien, die umfangreiche Anforderungen an Datenintegration, Leistungsoptimierung und Datenintegrität beinhalten, empfehlen wir die Verwendung vonpostgres_fdw.

Die Verwendung dblink umfasst die folgenden allgemeinen Schritte:

1. Erstellen Sie die dblink Erweiterung:

   CREATE EXTENSION dblink;

   Diese Erweiterung bietet die Funktionalität, um eine Verbindung zu entfernten PostgreSQL-Datenbanken herzustellen.

2. Verwenden Sie die Funktion, um eine Verbindung zu einer entfernten PostgreSQL-Datenbank herzustellen: dblink_connect

   SELECT dblink_connect('myconn', 'dbname=postgres port=5432 host=SOURCE_HOSTNAME user=postgres password=postgres');

3. Nachdem Sie eine Verbindung mit der Remote-PostgreSQL-Datenbank hergestellt haben, führen Sie SQL-Anweisungen in der entfernten Datenbank mithilfe dblink von Funktionen aus:

   SELECT  FROM dblink('myconn', 'SELECT col1, col2 FROM remote_table') AS remote_data(col1 int, col2 text);

   Diese Abfrage führt die SELECT * FROM remote_table Anweisung in der entfernten Datenbank mithilfe der myconn Verbindung aus. Die Abfrage ruft die Ergebnisse in eine lokale temporäre Tabelle mit den Spalten col1 und col2 ab.

4. Sie können auch Anweisungen, die keine Abfragen sind, wie, oderINSERT,UPDATE, in der entfernten Datenbank ausführenDELETE, indem Sie die dblink_exec folgende Funktion verwenden:

   SELECT dblink_exec('myconn', 'INSERT INTO remote_table VALUES (1, ''value'')');