Skip to main content

How to create a database link from EnterpriseDB PostgreSQL Advanced Server (EPAS) cluster to remote Oracle server

JINGJING LIU
  • Updated

Prerequisites

  • Created EnterpriseDB PostgreSQL Advanced Server (EPAS) cluster from BigAnimal. The document shows how to create the cluster.
  • The superuser privilege is granted. The document shows how to get the superuser privilege.
  • Oracle server port is 1521. If the port is not 1521, additional port mapping is required. 

Procedure

Step 1. Connect to BigAnimal cluster. The document shows how to connect to the cluster.

Step 2. Create the public database link, below is the synopsis.

CREATE [ PUBLIC ] DATABASE LINK <name>
  CONNECT TO { CURRENT_USER |
               <username> IDENTIFIED BY '<password>'}
  USING { '<oracle_connection_string>' }

The following example demonstrates using the 'CREATE DATABASE LINK' command to create a database link (named oralink) that connects an instance of Advanced Server to an Oracle server via an oci-dblink connection.

The connection information tells EPAS to log in to Oracle as user 'system' with password '<password>'. '//20.83.119.35:1521/XEPDB1' specifies the server address and name of the database.

CREATE PUBLIC DATABASE LINK oralink 
  CONNECT TO system IDENTIFIED BY '<password>'
  = USING '//20.83.119.35:1521/XEPDB1';

Step 3. Check the dblink and test connection.

edb_admin=# SELECT lnkname, lnkuser, lnkconnstr FROM pg_catalog.edb_dblink;

 lnkname | lnkuser |         lnkconnstr

---------+---------+----------------------------

 oralink | system  | //20.83.119.35:1521/XEPDB1

(1 row)
edb_admin=# select * from t1@oralink;
i
----
1
(1 rows)

Note

  • It doesn't require to install any extension. The EnterpriseDB Foreign Data Wrapper for Oracle extension 'edb_dblink_oci' is already installed.
edb_admin=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.2 (EnterpriseDB Advanced Server 14.2.1 (Debian 14.2.1-1+deb10)) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

edb_admin=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+------------------------------------------------------------------------
edb_dblink_libpq | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL
edb_dblink_oci | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle
edbspl | 1.0 | pg_catalog | EDB-SPL procedural language
pg_stat_statements | 1.9 | public | track planning and execution statistics of all SQL statements executed
pldbgapi | 1.1 | pg_catalog | server-side support for debugging PL/pgSQL functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(6 rows)
  • If the Oracle server is at the private network. It requires to setup the connectivity from BigAnimal subnet to the Oracle server network.