Integrazione compatibile con Aurora PostgreSQL con database PostgreSQL remoti

Questa sezione illustra l'integrazione dell'edizione compatibile con Amazon Aurora PostgreSQL con database PostgreSQL remoti utilizzando l'estensione (foreign-data wrapper) o la funzionalità. postgres_fdw dblink Il postgres_fdw modulo fornisce funzionalità di query federate per interagire con database remoti basati su PostgreSQL. I database remoti possono essere gestiti o autogestiti su Amazon EC2 o in locale. L'postgres_fdwestensione è disponibile in tutte le versioni attualmente supportate di Amazon Relational Database Service (Amazon RDS) per PostgreSQL e Aurora PostgreSQL compatibili.

Utilizzando l'postgres_fdwestensione, è possibile accedere e interrogare i dati dai database PostgreSQL remoti come se fossero tabelle locali. L'postgres_fdwestensione supporta anche quanto segue:

• Compatibilità tra versioni per l'accesso ai dati da server PostgreSQL esterni che eseguono versioni diverse.

• Gestione delle transazioni, che aiuta a garantire la coerenza e l'integrità dei dati quando si eseguono operazioni su server PostgreSQL locali ed esterni.

• Transazioni distribuite, che forniscono atomicità (una proprietà delle transazioni ACID) e garanzie di isolamento quando si eseguono operazioni su più server PostgreSQL esterni. Questo aiuta a garantire che tutte le operazioni di una transazione vengano eseguite o nessuna, mantenendo la coerenza e l'integrità dei dati.

Sebbene il dblink modulo fornisca un modo per interagire con i database PostgreSQL remoti, non supporta transazioni distribuite o altre funzionalità avanzate. Se hai bisogno di funzionalità più avanzate, prendi in considerazione l'utilizzo dell'postgres_fdwestensione. L'postgres_fdwestensione offre maggiori funzionalità di integrazione e ottimizzazione.

casi d'uso e passaggi di alto livello di postgres_fdw

L'utilizzo dell'postgres_fdwestensione con Aurora PostgreSQL Compatible supporta i seguenti casi d'uso e scenari:

• Interrogazioni federate e integrazione dei dati ‒ Interrogazione e combinazione di dati da più database PostgreSQL all'interno di una singola istanza compatibile con Aurora PostgreSQL

• Scaricamento dei carichi di lavoro di lettura ‒ Connessione alle repliche di lettura di server PostgreSQL esterni, ripartizione dei carichi di lavoro con elevata intensità di lettura e miglioramento delle prestazioni delle query

• Operazioni tra database ‒ Esecuzione di INSERT UPDATEDELETE, e COPY operazioni su più database PostgreSQL, che consentono la manipolazione e la manutenzione dei dati tra database

Per configurare, utilizza i seguenti passaggi di alto postgres_fdw livello:

1. Connect al cluster Aurora compatibile con PostgreSQL utilizzando un client PostgreSQL e crea l'estensione: postgres_fdw

   CREATE EXTENSION postgres_fdw;

   Questa estensione offre la funzionalità per connettersi a database PostgreSQL remoti.

2. Crea un server esterno denominato my_fdw_target utilizzando il comando. CREATE SERVER Questo server rappresenta il database PostgreSQL remoto a cui vuoi connetterti. Specificate il nome del database, il nome host e la modalità SSL come opzioni per questo server.

3. Assicurati che siano presenti i gruppi di sicurezza e le configurazioni di rete necessari per consentire ad Aurora PostgreSQL Compatible di connettersi al database PostgreSQL remoto.

   Se il database remoto è ospitato in locale, potrebbe essere necessario configurare una rete privata virtuale (VPN) o una connessione. AWS Direct Connect

   Esegui il comando seguente:

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

4. Crea una mappatura utente per l'dbuserutente sul my_fdw_target server. Questa mappatura associa l'dbuserutente e la password sull'istanza locale compatibile con Aurora PostgreSQL all'utente corrispondente nel database remoto.

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

   Questo passaggio è necessario per autenticare e fornire l'accesso al database remoto.

5. Crea una tabella esterna denominata customer_fdw con la mappatura del my_fdw_target server e degli utenti che hai impostato in precedenza:

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

   La customer_fdw tabella viene mappata alla customers tabella nel database remoto specificato dal my_fdw_target server. La tabella esterna ha la stessa struttura della tabella remota, in modo da poter interagire con i dati remoti come se fosse una tabella locale.

6. È possibile eseguire diverse operazioni di manipolazione dei dati sulla tabella customer_fdw esterna, ad esempio INSERTUPDATE, e SELECT interrogazioni. Lo script dimostra l'inserimento di una nuova riga e l'aggiornamento di una riga esistente, l'eliminazione di un record e il tronco di una tabella nella tabella remota tramite la tabella esterna: customers customer_fdw

   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. È possibile convalidare un piano di query SQL utilizzando l'EXPLAINistruzione per analizzare il piano di query per una query sulla tabella: SELECT customer_fdw

   EXPLAIN select * from customer_fdw where id =1;

   Questo può aiutarti a capire come viene eseguita la query e come ottimizzarla. Per ulteriori informazioni sull'utilizzo dell'EXPLAINistruzione, vedere Ottimizzazione delle prestazioni delle query PostgreSQL in Prescriptive Guidance. AWS

8. Per importare più tabelle dal database remoto in uno schema locale, usa il comando: IMPORT FOREIGN SCHEMA

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

   Questo crea tabelle esterne locali per le tabelle specificate nello public_fdw schema. In questo esempio, le tabelle specifiche sono dipendenti e reparti.

9. Per concedere le autorizzazioni necessarie a uno specifico utente del database in modo che possa accedere e utilizzare FDW e il server esterno associato, esegui i seguenti comandi:

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

   Questo passaggio può essere utile quando più utenti richiedono l'accesso alle tabelle esterne facilitato dal wrapper di dati esterni.

Quando utilizzate tabelle esterne, tenete presente le seguenti limitazioni:

• L'accesso ai dati da una fonte remota può comportare costi di trasferimento dei dati e un sovraccarico di prestazioni causato dalla latenza della rete. I problemi di prestazioni possono essere evidenti per set di dati o query di grandi dimensioni che richiedono un trasferimento significativo di dati tra l'istanza compatibile con Aurora PostgreSQL e l'origine dati remota.

• Nelle query complesse che coinvolgono funzionalità come le funzioni delle finestre, le query ricorsive potrebbero non funzionare come previsto o potrebbero non essere supportate.

• Attualmente, la crittografia delle password non è supportata. Implementa controlli per garantire che solo gli utenti autorizzati possano accedere FDWs e recuperare i dati dai database remoti.

• I vincoli della chiave primaria non possono essere definiti su tabelle esterne, come dimostra il seguente tentativo di script di creazione di tabelle:

  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

• La ON CONFLICT clausola per INSERT le istruzioni non è supportata nelle tabelle esterne, come illustrato nell'esempio seguente:

  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.

Rimozione

Per ripulire gli oggetti creati, inclusa l'eliminazione dell'postgres_fdwestensione, del my_fdw_target server, delle mappature degli utenti e delle tabelle esterne, esegui i comandi seguenti:

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

Usare dblink per creare connessioni

Le funzioni del dblink modulo forniscono un modo alternativo per creare connessioni ed eseguire istruzioni SQL su database PostgreSQL remoti. La dblink soluzione è un modo più semplice e flessibile per eseguire query o operazioni una tantum su database remoti. Per scenari più complessi che coinvolgono l'integrazione dei dati su larga scala, l'ottimizzazione delle prestazioni e i requisiti di integrità dei dati, si consiglia di utilizzare. postgres_fdw

L'utilizzo dblink prevede i seguenti passaggi di alto livello:

1. Crea l'dblinkestensione:

   CREATE EXTENSION dblink;

   Questa estensione offre la funzionalità per connettersi a database PostgreSQL remoti.

2. Per stabilire una connessione a un database PostgreSQL remoto, usa la funzione: dblink_connect

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

3. Dopo esserti connesso al database PostgreSQL remoto, esegui le istruzioni SQL sul database remoto utilizzando le funzioni: dblink

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

   Questa query esegue l'SELECT * FROM remote_tableistruzione sul database remoto utilizzando la connessione. myconn La query recupera i risultati in una tabella temporanea locale con colonne col1 ecol2.

4. È inoltre possibile eseguire istruzioni non di query, ad esempio INSERTUPDATE, orDELETE, sul database remoto utilizzando la dblink_exec funzione:

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