Integración compatible con Aurora PostgreSQL con bases de datos PostgreSQL remotas

En esta sección, se describe la integración de la edición compatible con PostgreSQL de Amazon Aurora con bases de datos PostgreSQL remotas mediante la extensión postgres_fdw (contenedor de datos externos) o la función. dblink El postgres_fdw módulo proporciona la capacidad de consulta federada para interactuar con bases de datos remotas basadas en PostgreSQL. Las bases de datos remotas se pueden gestionar o autogestionar en Amazon EC2 o de forma local. La postgres_fdw extensión está disponible en todas las versiones compatibles actualmente de Amazon Relational Database Service (Amazon RDS) para PostgreSQL y Aurora compatibles con PostgreSQL.

Con la postgres_fdw extensión, puede acceder a los datos de bases de datos PostgreSQL remotas y consultarlos como si fueran tablas locales. La postgres_fdw extensión también admite lo siguiente:

• Compatibilidad entre versiones para acceder a los datos de servidores PostgreSQL externos que ejecutan versiones diferentes.

• Gestión de transacciones, que ayuda a garantizar la coherencia e integridad de los datos al realizar operaciones en servidores PostgreSQL locales y externos.

• Transacciones distribuidas, que proporcionan atomicidad (una propiedad de las transacciones ACID) y garantías de aislamiento cuando se realizan operaciones en varios servidores PostgreSQL externos. Esto ayuda a garantizar que todas las operaciones de una transacción se confirmen o que no se confirme ninguna, manteniendo la coherencia y la integridad de los datos.

Aunque el dblink módulo proporciona una forma de interactuar con bases de datos PostgreSQL remotas, no admite transacciones distribuidas ni otras funciones avanzadas. Si necesita una funcionalidad más avanzada, considere usar la postgres_fdw extensión en su lugar. La postgres_fdw extensión proporciona más capacidades de integración y optimización.

Casos de uso y pasos de alto nivel de postgres_fdw

El uso postgres_fdw de la extensión con Aurora compatible con PostgreSQL admite los siguientes casos de uso y escenarios:

• Consultas federadas e integración de datos ‒ Consulta y combinación de datos de varias bases de datos de PostgreSQL en una única instancia compatible con Aurora PostgreSQL

• Descarga de cargas de trabajo de lectura ‒ Conexión a réplicas de lectura de servidores PostgreSQL externos, descarga de cargas de trabajo de lectura intensiva y mejora del rendimiento de las consultas

• Operaciones entre bases de datos: ejecución y COPY operaciones en varias bases de datos PostgreSQL INSERT UPDATEDELETE, lo que permite realizar tareas de mantenimiento y manipulación de datos entre bases de datos

Para configurarlopostgres_fdw, siga los siguientes pasos generales:

1. Conéctese a su clúster compatible con PostgreSQL de Aurora mediante un cliente PostgreSQL y cree la extensión: postgres_fdw

   CREATE EXTENSION postgres_fdw;

   Esta extensión proporciona la funcionalidad para conectarse a bases de datos PostgreSQL remotas.

2. Cree un servidor externo con un nombre my_fdw_target mediante el CREATE SERVER comando. Este servidor representa la base de datos PostgreSQL remota a la que desea conectarse. Especifique el nombre de la base de datos, el nombre de host y el modo SSL como opciones para este servidor.

3. Asegúrese de que los grupos de seguridad y las configuraciones de red necesarios estén implementados para permitir que Aurora, compatible con PostgreSQL, se conecte a la base de datos remota de PostgreSQL.

   Si la base de datos remota está alojada en las instalaciones, es posible que deba configurar una red privada virtual (VPN) o una conexión. AWS Direct Connect

   Ejecuta el siguiente comando:

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

4. Cree un mapeo de usuarios para el dbuser usuario en el my_fdw_target servidor. Esta asignación asocia el dbuser usuario y la contraseña de la instancia local compatible con Aurora PostgreSQL con el usuario correspondiente de la base de datos remota.

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

   Este paso es necesario para autenticar y proporcionar acceso a la base de datos remota.

5. Cree una tabla externa customer_fdw con el nombre del mapeo de my_fdw_target servidores y usuarios que configuró anteriormente:

   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 tabla se asigna a la customers tabla de la base de datos remota especificada por el my_fdw_target servidor. La tabla externa tiene la misma estructura que la tabla remota, por lo que puede interactuar con los datos remotos como si se tratara de una tabla local.

6. Puede realizar diversas operaciones de manipulación de datos en la tabla customer_fdw externaINSERT, comoUPDATE, y SELECT consultas. El script muestra cómo insertar una nueva fila y actualizar una fila existente, eliminar un registro y truncar una tabla de la tabla remota a través de la customers tabla customer_fdw externa:

   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. Puede validar un plan de consultas SQL utilizando la EXPLAIN instrucción para analizar el plan de consultas de una SELECT consulta de la customer_fdw tabla:

   EXPLAIN select * from customer_fdw where id =1;

   Esto puede ayudarle a entender cómo se ejecuta la consulta y cómo optimizarla. Para obtener más información sobre el uso de la EXPLAIN declaración, consulte Optimización del rendimiento AWS de las consultas de PostgreSQL en Prescriptive Guidance.

8. Para importar varias tablas de la base de datos remota a un esquema local, utilice el comando: IMPORT FOREIGN SCHEMA

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

   Esto crea tablas externas locales para las tablas especificadas en el public_fdw esquema. En este ejemplo, las tablas específicas son empleados y departamentos.

9. Para conceder los permisos necesarios a un usuario de base de datos específico para que pueda acceder y utilizar el FDW y el servidor externo asociado, ejecute los siguientes comandos:

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

   Este paso puede resultar beneficioso cuando varios usuarios necesitan acceder a las tablas externas facilitadas por el contenedor de datos externo.

Cuando utilice tablas externas, tenga en cuenta las siguientes limitaciones:

• El acceso a los datos desde una fuente remota puede generar costes de transferencia de datos y sobrecargar el rendimiento debido a la latencia de la red. Los problemas de rendimiento pueden notarse en consultas o conjuntos de datos de gran tamaño que requieren una transferencia de datos significativa entre la instancia compatible con Aurora PostgreSQL y la fuente de datos remota.

• En consultas complejas que incluyen características como funciones de ventana, es posible que las consultas recursivas no funcionen según lo esperado o que no sean compatibles.

• Actualmente, no se admite el cifrado por contraseña. Implemente controles para garantizar que solo los usuarios autorizados puedan acceder a las bases de datos remotas FDWs y recuperarlos de ellas.

• Las restricciones clave principales no se pueden definir en tablas externas, como lo demuestra el siguiente intento de guion de creación de tablas:

  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 cláusula para INSERT las declaraciones no se admite en las tablas externas, como se muestra en el siguiente ejemplo:

  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.

Limpieza

Para limpiar los objetos creados, incluida la eliminación de la postgres_fdw extensión, el my_fdw_target servidor, las asignaciones de usuarios y las tablas externas, ejecute los siguientes comandos:

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

Uso de dblink para crear conexiones

Las funciones del dblink módulo proporcionan una forma alternativa de crear conexiones y ejecutar sentencias SQL en bases de datos PostgreSQL remotas. La dblink solución es una forma más sencilla y flexible de ejecutar consultas u operaciones únicas en bases de datos remotas. Para escenarios más complejos que impliquen requisitos de integración de datos a gran escala, optimización del rendimiento e integridad de los datos, recomendamos postgres_fdw utilizarla.

dblinkEl uso implica los siguientes pasos de alto nivel:

1. Cree la dblink extensión:

   CREATE EXTENSION dblink;

   Esta extensión proporciona la funcionalidad para conectarse a bases de datos PostgreSQL remotas.

2. Para establecer una conexión a una base de datos PostgreSQL remota, utilice la función: dblink_connect

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

3. Tras conectarse a la base de datos PostgreSQL remota, ejecute las sentencias SQL en la base de datos remota mediante las funciones: dblink

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

   Esta consulta ejecuta la SELECT * FROM remote_table sentencia en la base de datos remota mediante la myconn conexión. La consulta recupera los resultados en una tabla temporal local con columnas col1 ycol2.

4. También puede ejecutar sentencias que no sean de consulta, comoINSERT, o UPDATEDELETE, en la base de datos remota mediante la dblink_exec función:

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