Como crear un DBLink entre PostgreSQL 11 y Oracle 10g

Necesitaba consultar datos en una base de datos Oracle 10g, desde Postgres y encontré que existe Oracle_FDW, una extensión que permite un acceso a un base de datos Oracle desde PostgreSQL.

Primero que nada necesitamos tener instalado el cliente Oracle en nuestro servidor con el usuario postgres. Para esto sigan esta ayuda.

Una vez el cliente Oracle funciona correctamente, debemos proceder a la instalación del Oracle_FDW.

Instalación

Descargamos los fuentes desde el sitio https://github.com/laurenz/oracle_fdw/releases/tag/ORACLE_FDW_2_1_0 en el directorio /opt/

Luego chequeamos que ‘pg_config’ esté en PATH (se puede testear con el comando ‘pg_config –pgxs’).
También tenemos que tener la variable ORACLE_HOME en la ubicación de la instalación de Oracle.

Decomprimimos el .tar.gz en /opt/ con el comando

# tar -xvf ORACLE_FDW_2_1_0.tar.gz

Chequeamos que el paquete devel de PostgreSQL esté instalado, de lo contrario lo instalamos.

# rpm -qa | grep postgresql11-devel

Chequeamos que gcc, clang esté instalado, de lo contrario lo instalamos.
Debemos instalar también llvm-toolset-7 y lo hacemos así:

# yum install centos-release-scl
# yum install llvm-toolset-7

nos loggeamos con el usuario en cuestión (postgres) y ejecutamos:

$ scl enable llvm-toolset-7 bash

Para verificar que haya quedado bien instalado hacemos:

$ clang --version

nos paramos dentro del directorio extraido y ejecutamos:
(debemos enter en cuenta que para el comando make install debemos darle permisos de escritura en el directorio de instalación de PostgreSQL)

$ make
$ make install

Una vez se haya generado bien el binario, procedemos a crear la extensión.
Primero que nada debemos configurar las variables de entorno para el proceso de PostgreSQL. Esto lo debemos hacer creando o editando el archivo /etc/systemd/system/postgresql-11.service con las siguientes variables:

.include /usr/lib/systemd/system/postgresql-11.service
[Service]
Environment=ORACLE_HOME=<Directorio de Oracle Home>
Environment=LD_LIBRARY_PATH=<Directorio lib de Oracle Home>
Environment=TNS_ADMIN=<Directorio admin de Oracle Home>
Environment=PATH=$PATH:<pgsql-11/bin>

Reiniciamos el servicio de PostgreSQL:

# systemctl restart postgresql-11

Para chequear si está todo ok ejecutamos:

# systemctl status postgresql-11

De ahí obtenemos el PID del servicio postmaster de PostgreSQL para verificar que las variables de entorno estén ok.

# strings /proc/<PID>/environ

Esto nos debería mostrar las variables de entorno configuradas anteriormente.

Crear la extensión en PostgreSQL

postgres=# create extension oracle_fdw ;

Esto crea todas las funciones para el dblink.

Luego configuramos oracle_fdw con el usuario postgres:

pgdb=# CREATE EXTENSION oracle_fdw;
pgdb=# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//dbserver.mydomain.com:1521/ORADB');
pgdb=# GRANT USAGE ON FOREIGN SERVER oradb TO pguser;

Creamos el mapeo para el usuario que utilizará el link

pgdb=> CREATE USER MAPPING FOR pguser SERVER oradb
OPTIONS (user 'orauser', password 'orapwd');

A continuación creamos los mapeos de las tablas.

pgdb=> CREATE FOREIGN TABLE oratab (
id integer OPTIONS (key 'true') NOT NULL,
text character varying(30),
floating double precision NOT NULL
) SERVER oradb OPTIONS (schema 'ORAUSER', table 'ORATAB');

Por lo general el nombre del schema y la tabla deben ir mayúsculas.

Finalmente se puede utilizar la tabla como si fuera de PostgreSQL.

Algunas puntuaciones

  • El idioma de fecha la conexión debe ser en Inglés. NLS_DATE_LANG = American.  En el caso de que sea español y no exista posibilidad de cambiarlo, una solución puede ser forzar el formato (YYYY-MM-DD) con un trigger en en logon del usuario del dblink en Oracle. Luego en PostgreSQL las consultas con fechas las hacen con to_date(‘2019-02-19’, ‘YYY-MM-DD’).
  • Para analizar si los índices de Oracle son tomados se debe utilizar la sentencia EXPLAIN VERBOSE <sql query>. Si no toma el índice quizás el mapeo de los campos no es el correcto.
  • Los tipos de datos que maneja automáticamente son:

  • Cualquier problema con esto pueden contactarme e intentaré ayudarlos.
  • Cualquier problema, también pueden crear un ticket a Laurenz Albe (el desarrollador) en Github quién contesta sin problemas. De hecho me ayudo con el problema de la fecha que tuve.