Ejemplo de postgresql postgres_fdw

F.33.1. Opciones FDW de postgres_fdw

F.33.2. Gestión de conexiones

F.33.3. Gestión de transacciones

F.33.4. Optimización de consultas remotas

F.33.5. Entorno de ejecución de consultas remotas

F.33.6. Compatibilidad entre versiones

F.33.7. Ejemplos de

F.33.8. Autor

los postgres_fdw El módulo proporciona el contenedor de datos externos. postgres_fdw, que se puede utilizar para acceder a datos almacenados en servidores PostgreSQL externos.

La funcionalidad proporcionada por este módulo se superpone sustancialmente con la funcionalidad del antiguo parpadear módulo. Pero postgres_fdw proporciona una sintaxis más transparente y compatible con los estándares para acceder a tablas remotas y puede ofrecer un mejor rendimiento en muchos casos.

Para prepararse para el acceso remoto usando postgres_fdw:

1. Instala el postgres_fdw extensión usando CREATE EXTENSION.

2. Cree un objeto de servidor externo, utilizando CREATE SERVER, para representar cada base de datos remota a la que desea conectarse. Especifique la información de conexión, excepto user y password, como opciones del objeto servidor.

3. Cree un mapeo de usuarios, utilizando CREAR ASIGNACIÓN DE USUARIOS, para cada usuario de la base de datos que desee permitir que acceda a cada servidor externo. Especifique el nombre de usuario remoto y la contraseña para usar como user y password opciones del mapeo de usuarios.

4. Cree una tabla externa, utilizando CREAR TABLA EXTRANJERA o IMPORTAR ESQUEMA EXTRANJERO, para cada tabla remota a la que desee acceder. Las columnas de la tabla externa deben coincidir con la tabla remota a la que se hace referencia. Sin embargo, puede usar nombres de tabla y / o columna diferentes de los de la tabla remota, si especifica los nombres remotos correctos como opciones del objeto de tabla externa.

Ahora solo necesitas SELECT desde una tabla externa para acceder a los datos almacenados en su tabla remota subyacente. También puede modificar la tabla remota usando INSERT, UPDATE, o DELETE. (Por supuesto, el usuario remoto que ha especificado en su mapeo de usuarios debe tener privilegios para hacer estas cosas).

Tenga en cuenta que postgres_fdw actualmente carece de soporte para INSERT declaraciones con un ON CONFLICT DO UPDATE cláusula. sin embargo, el ON CONFLICT DO NOTHING se admite la cláusula, siempre que se omita una especificación de inferencia de índice única. Tenga en cuenta también que postgres_fdw admite el movimiento de filas invocado por UPDATE sentencias ejecutadas en tablas particionadas, pero actualmente no maneja el caso donde una partición remota elegida para insertar una fila movida también es una UPDATE partición de destino que se actualizará más tarde.

En general, se recomienda que las columnas de una tabla foránea se declaren con exactamente los mismos tipos de datos y las mismas intercalaciones, si corresponde, que las columnas de referencia de la tabla remota. A pesar de que postgres_fdw Actualmente, es bastante indulgente con la realización de conversiones de tipos de datos cuando las necesita, pueden surgir anomalías semánticas sorprendentes cuando los tipos o las intercalaciones no coinciden, debido a la interpretación del servidor remoto. WHERE cláusulas ligeramente diferentes del servidor local.

Tenga en cuenta que una tabla foránea se puede declarar con menos columnas, o con un orden de columna diferente al que tiene su tabla remota subyacente. La coincidencia de columnas con la tabla remota es por nombre, no por posición.

F.33.1. Opciones FDW de postgres_fdw

F.33.1.1. Opciones de conexión

Un servidor externo que usa el postgres_fdw El contenedor de datos externos puede tener las mismas opciones que libpq acepta en cadenas de conexión, como se describe en Sección 33.1.2, excepto que estas opciones no están permitidas o tienen un manejo especial:

• user, password y sslpassword (especifíquelos en una asignación de usuario, en su lugar, o use un archivo de servicio)

• client_encoding (esto se establece automáticamente desde la codificación del servidor local)

• fallback_application_name (siempre establecido en postgres_fdw)

• sslkey y sslcert – estos pueden aparecer en cualquiera o los dos una conexión y un mapeo de usuarios. Si ambos están presentes, la configuración de asignación de usuario anula la configuración de conexión.

Solo los superusuarios pueden crear o modificar asignaciones de usuarios con la sslcert o sslkey ajustes.

Solo los superusuarios pueden conectarse a servidores externos sin autenticación de contraseña, por lo que siempre especifique la password opción para asignaciones de usuarios que pertenecen a no superusuarios.

Un superusuario puede anular esta verificación según el mapeo por usuario configurando la opción de mapeo de usuario password_required 'false', p.ej,

ALTERUSER MAPPING FOR some_non_superuser SERVER loopback_nopw
OPTIONS (ADD password_required 'false');

Para evitar que los usuarios sin privilegios exploten los derechos de autenticación del usuario de Unix que el servidor de Postgres está ejecutando para escalar a los derechos de superusuario, solo el superusuario puede establecer esta opción en una asignación de usuario.

Se requiere cuidado para asegurarse de que esto no permita al usuario mapeado la capacidad de conectarse como superusuario a la base de datos mapeada según CVE-2007-3278 y CVE-2007-6601. No establezcas password_required=false sobre el public papel. Tenga en cuenta que el usuario asignado puede utilizar potencialmente cualquier certificado de cliente, .pgpass, .pg_service.conf etc en el directorio de inicio de Unix del usuario del sistema con el que se ejecuta el servidor de Postgres. También pueden usar cualquier relación de confianza otorgada por modos de autenticación como peer o ident autenticación.

F.33.1.2. Opciones de nombre de objeto

Estas opciones se pueden utilizar para controlar los nombres utilizados en las sentencias SQL enviadas al servidor PostgreSQL remoto. Estas opciones son necesarias cuando se crea una tabla externa con nombres diferentes de los nombres de la tabla remota subyacente.

schema_name

Esta opción, que se puede especificar para una tabla externa, proporciona el nombre de esquema que se utilizará para la tabla externa en el servidor remoto. Si se omite esta opción, se utiliza el nombre del esquema de la tabla externa.

table_name

Esta opción, que se puede especificar para una tabla externa, proporciona el nombre de la tabla que se utilizará para la tabla externa en el servidor remoto. Si se omite esta opción, se utiliza el nombre de la tabla externa.

column_name

Esta opción, que se puede especificar para una columna de una tabla externa, proporciona el nombre de la columna que se utilizará para la columna en el servidor remoto. Si se omite esta opción, se utiliza el nombre de la columna.

F.33.1.3. Opciones de estimación de costos

postgres_fdw recupera datos remotos mediante la ejecución de consultas en servidores remotos, por lo que, idealmente, el costo estimado de escanear una tabla externa debería ser lo que cueste hacerlo en el servidor remoto, más algunos gastos generales de comunicación. La forma más confiable de obtener una estimación de este tipo es preguntar al servidor remoto y luego agregar algo para gastos generales, pero para consultas simples, puede que no valga la pena el costo de una consulta remota adicional para obtener una estimación de costos. Entonces postgres_fdw proporciona las siguientes opciones para controlar cómo se realiza la estimación de costos:

use_remote_estimate

Esta opción, que se puede especificar para una tabla externa o un servidor externo, controla si postgres_fdw problemas remotos EXPLAIN comandos para obtener estimaciones de costos. Una configuración para una tabla externa anula cualquier configuración para su servidor, pero solo para esa tabla. El valor predeterminado es false.

fdw_startup_cost

Esta opción, que se puede especificar para un servidor externo, es un valor numérico que se agrega al costo de inicio estimado de cualquier escaneo de tabla externa en ese servidor. Esto representa la sobrecarga adicional de establecer una conexión, analizar y planificar la consulta en el lado remoto, etc. El valor predeterminado es 100.

fdw_tuple_cost

Esta opción, que se puede especificar para un servidor externo, es un valor numérico que se utiliza como costo adicional por tupla para escaneos de tablas externas en ese servidor. Esto representa la sobrecarga adicional de la transferencia de datos entre servidores. Puede aumentar o disminuir este número para reflejar un retraso de red más alto o más bajo para el servidor remoto. El valor predeterminado es 0.01.

Cuando use_remote_estimate es verdad, postgres_fdw obtiene el recuento de filas y estimaciones de costos del servidor remoto y luego agrega fdw_startup_cost y fdw_tuple_cost a las estimaciones de costos. Cuando use_remote_estimate Es falso, postgres_fdw realiza el recuento local de filas y la estimación de costos y luego agrega fdw_startup_cost y fdw_tuple_cost a las estimaciones de costos. Es poco probable que esta estimación local sea muy precisa a menos que se disponga de copias locales de las estadísticas de la tabla remota. Ejecutar ANALYZE en la tabla externa es la forma de actualizar las estadísticas locales; esto realizará un escaneo de la tabla remota y luego calculará y almacenará estadísticas como si la tabla fuera local. Mantener las estadísticas locales puede ser una forma útil de reducir la sobrecarga de planificación por consulta para una tabla remota, pero si la tabla remota se actualiza con frecuencia, las estadísticas locales pronto quedarán obsoletas.

F.33.1.4. Opciones de ejecución remota

Por defecto, solo WHERE Las cláusulas que utilizan operadores y funciones integradas se considerarán para su ejecución en el servidor remoto. Las cláusulas que involucran funciones no integradas se verifican localmente después de que se obtienen las filas. Si tales funciones están disponibles en el servidor remoto y se puede confiar en que producirán los mismos resultados que localmente, el rendimiento se puede mejorar enviando tales WHERE cláusulas de ejecución remota. Este comportamiento se puede controlar mediante la siguiente opción:

extensions

Esta opción es una lista separada por comas de nombres de extensiones de PostgreSQL que están instaladas, en versiones compatibles, tanto en el servidor local como en el remoto. Las funciones y los operadores que son inmutables y pertenecen a una extensión enumerada se considerarán enviables al servidor remoto. Esta opción solo se puede especificar para servidores externos, no por tabla.

Al usar el extensions opción, es responsabilidad del usuario que las extensiones enumeradas existen y se comportan de manera idéntica tanto en el servidor local como en el remoto. De lo contrario, las consultas remotas pueden fallar o comportarse de forma inesperada.

fetch_size

Esta opción especifica el número de filas postgres_fdw debe entrar en cada operación de recuperación. Se puede especificar para una tabla externa o un servidor externo. La opción especificada en una tabla anula una opción especificada para el servidor. El valor predeterminado es 100.

F.33.1.5. Opciones de actualización

De forma predeterminada, todas las tablas extranjeras que utilizan postgres_fdw se supone que son actualizables. Esto se puede anular usando la siguiente opción:

updatable

Esta opción controla si postgres_fdw permite modificar tablas externas usando INSERT, UPDATE y DELETE comandos. Se puede especificar para una tabla externa o un servidor externo. Una opción de nivel de tabla anula una opción de nivel de servidor. El valor predeterminado es true.

Por supuesto, si la tabla remota no es actualizable, se producirá un error de todos modos. El uso de esta opción permite principalmente que el error se lance localmente sin consultar al servidor remoto. Sin embargo, tenga en cuenta que el information_schema las vistas informarán un postgres_fdw tabla foránea para ser actualizable (o no) de acuerdo con la configuración de esta opción, sin ninguna verificación del servidor remoto.

F.33.1.6. Opciones de importación

postgres_fdw es capaz de importar definiciones de tablas externas usando IMPORT FOREIGN SCHEMA. Este comando crea definiciones de tablas externas en el servidor local que coinciden con las tablas o vistas presentes en el servidor remoto. Si las tablas remotas que se van a importar tienen columnas de tipos de datos definidos por el usuario, el servidor local debe tener tipos compatibles con los mismos nombres.

El comportamiento de importación se puede personalizar con las siguientes opciones (que se indican en el IMPORT FOREIGN SCHEMA mando):

import_collate

Esta opción controla si la columna COLLATE Las opciones se incluyen en las definiciones de tablas externas importadas de un servidor externo. El valor predeterminado es true. Es posible que deba desactivar esta opción si el servidor remoto tiene un conjunto de nombres de clasificación diferente al del servidor local, lo que probablemente sea el caso si se ejecuta en un sistema operativo diferente.

import_default

Esta opción controla si la columna DEFAULT Las expresiones se incluyen en las definiciones de tablas externas importadas de un servidor externo. El valor predeterminado es false. Si habilita esta opción, tenga cuidado con los valores predeterminados que podrían calcularse de manera diferente en el servidor local que en el servidor remoto; nextval() es una fuente común de problemas. los IMPORT fallará por completo si una expresión predeterminada importada utiliza una función u operador que no existe localmente.

import_not_null

Esta opción controla si la columna NOT NULL las restricciones se incluyen en las definiciones de tablas externas importadas de un servidor externo. El valor predeterminado es true.

Tenga en cuenta que las restricciones distintas de NOT NULL nunca se importará de las tablas remotas. Aunque PostgreSQL admite CHECK restricciones en tablas externas, no existe ninguna disposición para importarlas automáticamente, debido al riesgo de que una expresión de restricción pueda evaluarse de manera diferente en los servidores locales y remotos. Cualquier inconsistencia en el comportamiento de un CHECK La restricción podría conducir a errores difíciles de detectar en la optimización de consultas. Entonces, si desea importar CHECK restricciones, debe hacerlo manualmente y debe verificar la semántica de cada una con cuidado. Para obtener más detalles sobre el tratamiento de CHECK restricciones en tablas externas, consulte CREAR TABLA EXTRANJERA.

Las tablas o tablas externas que son particiones de alguna otra tabla se excluyen automáticamente. Las tablas particionadas se importan, a menos que sean una partición de alguna otra tabla. Dado que se puede acceder a todos los datos a través de la tabla particionada, que es la raíz de la jerarquía de particiones, este enfoque debería permitir el acceso a todos los datos sin crear objetos adicionales.

F.33.2. Gestión de conexiones

postgres_fdw establece una conexión a un servidor externo durante la primera consulta que usa una tabla externa asociada con el servidor externo. Esta conexión se mantiene y se reutiliza para consultas posteriores en la misma sesión. Sin embargo, si se utilizan varias identidades de usuario (asignaciones de usuarios) para acceder al servidor externo, se establece una conexión para cada asignación de usuarios.

F.33.3. Gestión de transacciones

Durante una consulta que hace referencia a tablas remotas en un servidor externo, postgres_fdw abre una transacción en el servidor remoto si todavía no hay una que corresponda a la transacción local actual. La transacción remota se confirma o se cancela cuando la transacción local se confirma o cancela. Los puntos de guardado se gestionan de forma similar mediante la creación de los puntos de guardado remotos correspondientes.

La transacción remota utiliza SERIALIZABLE nivel de aislamiento cuando la transacción local ha SERIALIZABLE nivel de aislamiento; de lo contrario usa REPEATABLE READ nivel de aislamiento. Esta elección asegura que si una consulta realiza múltiples escaneos de tablas en el servidor remoto, obtendrá resultados consistentes con instantáneas para todos los escaneos. Una consecuencia es que las consultas sucesivas dentro de una sola transacción verán los mismos datos del servidor remoto, incluso si se están produciendo actualizaciones simultáneas en el servidor remoto debido a otras actividades. Ese comportamiento se esperaría de todos modos si la transacción local usa SERIALIZABLE o REPEATABLE READ nivel de aislamiento, pero podría ser sorprendente para un READ COMMITTED transacción local. Una versión futura de PostgreSQL podría modificar estas reglas.

Tenga en cuenta que actualmente no es compatible con postgres_fdw para preparar la transacción remota para la confirmación en dos fases.

F.33.4. Optimización de consultas remotas

postgres_fdw intenta optimizar las consultas remotas para reducir la cantidad de datos transferidos desde servidores externos. Esto se hace enviando una consulta WHERE cláusulas al servidor remoto para su ejecución, y al no recuperar columnas de la tabla que no son necesarias para la consulta actual. Para reducir el riesgo de ejecución incorrecta de consultas, WHERE las cláusulas no se envían al servidor remoto a menos que utilicen solo tipos de datos, operadores y funciones que están integradas o pertenecen a una extensión que se enumera en el servidor externo extensions opción. Los operadores y funciones en tales cláusulas deben ser IMMUTABLE así como. Por un UPDATE o DELETE consulta, postgres_fdw intenta optimizar la ejecución de la consulta enviando la consulta completa al servidor remoto si no hay ninguna consulta WHERE cláusulas que no se pueden enviar al servidor remoto, sin combinaciones locales para la consulta, sin local a nivel de fila BEFORE o AFTER desencadena o almacena columnas generadas en la tabla de destino, y no CHECK OPTION restricciones de las vistas de los padres. En UPDATE, las expresiones para asignar a las columnas de destino deben usar solo tipos de datos integrados, IMMUTABLE operadores, o IMMUTABLE funciones, para reducir el riesgo de ejecución incorrecta de la consulta.

Cuando postgres_fdw encuentra una combinación entre tablas externas en el mismo servidor externo, envía la combinación completa al servidor externo, a menos que por alguna razón crea que será más eficiente buscar filas de cada tabla individualmente, oa menos que las referencias de la tabla involucradas estén sujetas a diferentes asignaciones de usuarios. Al enviar el JOIN cláusulas, toma las mismas precauciones mencionadas anteriormente para la WHERE cláusulas.

La consulta que se envía realmente al servidor remoto para su ejecución se puede examinar utilizando EXPLAIN VERBOSE.

F.33.5. Entorno de ejecución de consultas remotas

En las sesiones remotas abiertas por postgres_fdw, el parámetro search_path se establece en solo pg_catalog, de modo que solo los objetos integrados sean visibles sin calificación de esquema. Este no es un problema para las consultas generadas por postgres_fdw sí mismo, porque siempre proporciona tal calificación. Sin embargo, esto puede representar un peligro para las funciones que se ejecutan en el servidor remoto a través de disparadores o reglas en tablas remotas. Por ejemplo, si una tabla remota es en realidad una vista, cualquier función utilizada en esa vista se ejecutará con la ruta de búsqueda restringida. Se recomienda calificar por esquema todos los nombres en dichas funciones, o adjuntar SET search_path opciones (ver CREAR FUNCIÓN) a dichas funciones para establecer su entorno de ruta de búsqueda esperado.

postgres_fdw Asimismo, establece la configuración de la sesión remota para varios parámetros:

• TimeZone está configurado en UTC

• DateStyle está establecido en ISO

• IntervalStyle está establecido en postgres

• extra_float_digits está configurado en 3 para servidores remotos 9.0 y posteriores y está configurado para 2 para versiones anteriores

Es menos probable que sean problemáticos que search_path, pero se puede manejar con la función SET opciones si surge la necesidad.

Está no recomendó que anule este comportamiento cambiando la configuración de nivel de sesión de estos parámetros; que es probable que cause postgres_fdw al mal funcionamiento.

F.33.6. Compatibilidad entre versiones

postgres_fdw se puede utilizar con servidores remotos que se remontan a PostgreSQL 8.3. La capacidad de solo lectura está disponible desde 8.1. Sin embargo, una limitación es que postgres_fdw generalmente asume que las funciones integradas inmutables y los operadores son seguros para enviar al servidor remoto para su ejecución, si aparecen en un WHERE cláusula para una mesa extranjera. Por lo tanto, una función incorporada que se agregó desde la versión del servidor remoto podría enviarse para su ejecución, lo que resulta en “la función no existe“ o un error similar. Este tipo de falla se puede solucionar reescribiendo la consulta, por ejemplo, incrustando la referencia de la tabla externa en una sub-SELECT con OFFSET 0 como una valla de optimización, y colocando la función u operador problemático fuera del sub-SELECT.

F.33.7. Ejemplos de

A continuación se muestra un ejemplo de creación de una tabla externa con postgres_fdw. Primero instale la extensión:

CREATE EXTENSION postgres_fdw;

Luego cree un servidor externo usando CREATE SERVER. En este ejemplo, deseamos conectarnos a un servidor PostgreSQL en el host 192.83.123.89 escuchando en el puerto 5432. La base de datos a la que se realiza la conexión se denomina foreign_db en el servidor remoto:

CREATE SERVER foreign_server
        FOREIGNDATA WRAPPER postgres_fdw
        OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db');

También se necesita un mapeo de usuario, definido con CREAR MAPEO DE USUARIO, para identificar el rol que se utilizará en el servidor remoto:

CREATEUSER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user'foreign_user', password 'password');

Ahora es posible crear una tabla foránea con CREATE FOREIGN TABLE. En este ejemplo deseamos acceder a la tabla llamada some_schema.some_table en el servidor remoto. El nombre local será foreign_table:

CREATEFOREIGNTABLE foreign_table (
        id integerNOTNULL,datatext)
        SERVER foreign_server
        OPTIONS (schema_name 'some_schema', table_name 'some_table');

Es esencial que los tipos de datos y otras propiedades de las columnas declaradas en CREATE FOREIGN TABLE coincidir con la tabla remota real. Los nombres de las columnas también deben coincidir, a menos que adjunte column_name opciones a las columnas individuales para mostrar cómo se nombran en la tabla remota. En muchos casos, el uso de IMPORT FOREIGN SCHEMA es preferible a la construcción manual de definiciones de tablas foráneas.

F.33.8. Autor

Shigeru Hanada <[email protected]>