Postgres Pro
Downloads
Home   >   mailing lists

Thread: [ADMIN] Foreign data wrappers for Oracle from Postgresql in RDS environment

[ADMIN] Foreign data wrappers for Oracle from Postgresql in RDS environment

From
Garry Chen
Date:

Hi All,

                I am testing a dblink connection from RDS postgresql database to a remote oracle instance but I am getting could not establish connection error.  Where did I do wrong and please help.

 

Thank you very much,

Garry

 

My setup as following:

 

  1. Security groups inbound has an entry for the oracle port and remote IP
  2. Psql statement as following

Create extension dblinnk;

                CREATE SERVER myOradb FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host ‘my remote host IP’, dbname ' myOradb ', port '1521');

               CREATE USER MAPPING FOR PGuser SERVER myOradb OPTIONS (user 'OraUser', password '’mypassword');

               SELECT dblink_connect('myOradb');

 

 

ERROR: could not establish connection

SQL state: 08001

Detail: server closed the connection unexpectedly

                This probably means the server terminated abnormally

                before or while processing the request.

Re: [ADMIN] Foreign data wrappers for Oracle from Postgresql in RDS environment

From
Tom Lane
Date:
Garry Chen <gc92@cornell.edu> writes:
>                 I am testing a dblink connection from RDS postgresql database to a remote oracle instance but I am
gettingcould not establish connection error.  Where did I do wrong and please help. 

As somebody already explained to you, dblink only does
postgres-to-postgres connections.

What you need for this is oracle_fdw.  I do not know how well that's
maintained, but it exists.
        regards, tom lane


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Foreign data wrappers for Oracle from Postgresql in RDS environment

From
"David G. Johnston"
Date:
On Mon, Sep 25, 2017 at 7:58 AM, Garry Chen <gc92@cornell.edu> wrote:

Where did I do wrong and please help.



​You didn't read the response to this topic on the email thread you started on the -novice list...

David J.
​
 

Re: [ADMIN] Foreign data wrappers for Oracle from Postgresql in RDS environment

From
"David G. Johnston"
Date:
On Mon, Sep 25, 2017 at 8:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Garry Chen <gc92@cornell.edu> writes:
>                 I am testing a dblink connection from RDS postgresql database to a remote oracle instance but I am getting could not establish connection error.  Where did I do wrong and please help.

As somebody already explained to you, dblink only does
postgres-to-postgres connections.

What you need for this is oracle_fdw.  I do not know how well that's
maintained, but it exists
​.

​Maintenance aside I'm not sure whether its been whitelisted for use in RDS.  The OP might need to run an EC2 instance so they can install custom extensions.

David J.
​