Oracle linux postgresql odbc

Accessing PostgreSQL databases using psqlODBC in Oracle

SUMMARY: This article demonstrates how to use the PostgreSQL client interface psqlODBC driver in Oracle to access PostgreSQL data.

1. Server and software version details

Oracle Database Gateways have the ability to transparently access non-Oracle system data from within the Oracle environment. Oracle has tailored gateways to many systems, and they are specifically coded for the target non-Oracle systems. As part of a generic technology solution to connect to a non-Oracle system, it offers Database Gateway for ODBC. This generic solution addresses the need for accessing data in other database systems for which Oracle does not have a tailored solution. Oracle Database Gateway for ODBC makes it possible to integrate with other databases such as MySQL, PostgreSQL, and even non-relational targets like Excel. In this post, we will configure an Oracle Database Gateway for ODBC (Heterogeneous Gateway) and make a connection to PostgreSQL v12 using PostgreSQL ODBC Connector.

From the diagram above, we can understand the Oracle gateway process flow:

• Client sends a query to the Oracle database via the Oracle Database Link created using Heterogeneous ODBC Data source.
• Oracle HS Agent translates the SQL dialect to an SQL Statement that the non-Oracle database (PostgreSQL) understands and sends it via ODBC DSN.
• Gateway retrieves the data from PostgreSQL using the translated SQL Statement and returns the data to the Oracle Database.
• Finally, the Oracle Database passes the query results to the client application.

Let’s configure the above setup and try to retrieve data from PostgreSQL via psqlODBC.

Note: This blog uses PostgreSQL ODBC connector in the configuration, but some steps can be used for other ODBC drivers (including EDB ODBC and others).

Servers and software version details

The following server and software versions were used in the configuration. Software versions are not specific to this setup, however. The same steps can be used with the latest versions of the database/drivers.

• Oracle Server
  • OS: CentOS 7.x
  • Host: 172.16.210.133
  • Oracle DB/Port: MIGDB/1521
  • User/Password: miguser/miguser
  • Software:
    • Oracle 11g
    • Latest unixODBC Driver Manager
    • PostgreSQL ODBC Driver (psqlODBC)
    • PostgreSQL Server
      • OS: CentOS 7.x
      • Host: 172.16.210.134
      • PostgreSQL DB/Port: postgres/5432
      • User/Password: postgres/admin
      • Software:
        • PostgreSQL v12

        Configuration

        Installation of Oracle and PostgreSQL servers is not covered here. If you have not installed these yet, please refer to the documentation below.

        On PostgreSQL server

        • At this point, the PostgreSQL v12 database should be up and running. For installation and configuration, please refer to PostgreSQL Quick Installation Guide.

        On Oracle server

        1. At this point, the Oracle 11g database should be up and running. For installation and configuration, please refer to Oracle Installation Guide.

        2. Install the latest unixODBC Driver Manager:

        [root@master ~]# yum install unixODBC*

        3. Install the PostgreSQL ODBC Driver (psqlODBC):

        [root@master ~]# yum install postgresql12-odbc*

        4. Create an ODBC Data Source Name (DSN) for the Driver.

        The unixODBC Driver Manager provides the odbcinst, odbc_config, and isql command line utilities used to configure and test the driver. Using odbcinst or odbc_config utilities, we can locate the unixODBC Driver Manager files location to pass driver information to create the DSN:

        [root@master ~]# odbcinst -j unixODBC 2.3.1 DRIVERS. /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES. /etc/ODBCDataSources USER DATA SOURCES. /root/.odbc.ini … [root@master ~]# odbc_config --odbcini --odbcinstini /etc/odbc.ini /etc/odbcinst.ini

        From the above output, we can see two files (odbcinst.ini / odbc.ini) that we need to modify to enable the drivers we want to use. Basically, odbcinst.ini is a registry and configuration file for ODBC drivers in an environment, while odbc.ini is a registry and configuration file for ODBC DSNs (Data Source Names).

        5. Configure the psqlODBC driver libraries in ODBC Driver file odbcinst.ini (create it if it doesn’t exist) and add the lines below to the bottom of the file. These lines makes an entry for the driver:

        [pgodbc] Description = ODBC for PostgreSQL Driver = /usr/lib/psqlodbcw.so Setup = /usr/lib/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbcw.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1

        6. Create a Data Source Name (DSN) in the odbc.ini file; that driver manager reads this file to determine how to connect to the database using driver details specified in odbcinst.ini:

        [pgdsn] Driver = pgodbc Description = PostgreSQL ODBC Driver Database = postgres Servername = 172.16.210.134 Username = postgres Password = admin Port = 5432 UseDeclareFetch = 1 CommLog = /tmp/pgodbclink.log Debug = 1 LowerCaseIdentifier = 1

        7. Using the isql utility, test the ODBC connection (psqlODBC) to the PostgreSQL Database DSN created:

        [root@master ~]# isql pgdsn +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select current_database(),inet_server_port(); +-----------------------------------------------------+-----------------+ | current_database | inet_server_port| +-----------------------------------------------------+-----------------+ | postgres | 5432 | +-----------------------------------------------------+-----------------+ SQLRowCount returns -1 1 rows fetched

        8. Using the DSN, create the Oracle Database Gateway for the ODBC (HS) service handler

        • Create file initDSN.ora in location $ORACLE_HOME/hs/admin. In our case, pgdsn is the DSN, and we need to create a file called initpgdsn.ora:

        [oracle@rgvt admin]$ more initpgdsn.ora # This is a sample agent init file that contains the HS parameters that are # needed for the Database Gateway for ODBC # # HS init parameters # HS_FDS_CONNECT_INFO = pgdsn HS_FDS_TRACE_LEVEL = DEBUG HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # # ODBC specific environment variables # set ODBCINI=/etc/odbc.ini

        • Adjust the listener ($ORACLE_HOME/network/admin/listener.ora) by adding the DSN entry in SID_LIST_LISTENER:

        SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) ) (SID_DESC= (SID_NAME = pgdsn) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/11.2.0/db_1") (PROGRAM=dg4odbc) ) )

        pgdsn = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA=(SID=pgdsn)) (HS=OK) )

        • Restart the Oracle Listener so the DSN related entries made to the networking files can take effect:

         $ lsnrctl stop $ lsnrctl start

        • After restarting the Oracle listener, it will create an Oracle HS handler with a DSN name (pgdsn):

        $ lsnrctl status | grep pgdsn Service "pgdsn" has 1 instance(s). Instance "pgdsn", status UNKNOWN, has 1 handler(s) for this service. 

        9. Now, create an Oracle database link using the DSN to access the PostgreSQL database:

        SQL> create database link pglink connect to "postgres" identified by "admin" using 'pgdsn'; Database link created.

        10. Access PostgreSQL data using Oracle Database Link created:

        SQL> select count(*) from "pg_class"@pglink; COUNT(*) ---------- 894

        Finally, consolidation of data in one database system is required for business, and it increases the mobility of the application. Today, most of the RDBMS support data access to different databases via extensions, modules, gateways and so on. This post highlights one of the application requirements to access PostgreSQL databases in Oracle via ODBC Driver. There are a variety of PostgreSQL ODBC Drivers available in the market, the steps demonstrated in this blog can be used to access PostgreSQL database with other ODBC drivers.

        Relevant Blogs

        

        EDB Tutorial: How to Configure for SSL with EDB JDBC on FIPS Enabled Server

        If you’re running into issues working with your java based applications with SSL enabled EPAS with respect to FIPS mode enabled, this blog post can help! Simply follow the instructions.

        

        EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes

        The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake.

        

        EDB Tutorial: How To Run a Complex Postgres Benchmark Easily — Master TPC-C in 3 Short Steps

        Benchmarking is one of the best ways to ensure the ongoing performance of your Postgres database, providing vital insights that can prove incredibly useful when growing and scaling. Luckily there.

        More Blogs

        

        EDB Tutorial: Achieving High Availability Using Enterprise Failover Manager

        High availability is one of the key drivers of Postgres adoption. However, taking full advantage of its benefits requires ensuring that your Postgres database can safely and efficiently failover in.

        

        EDB Tutorial: How to Backup Your PostgreSQL to Azure Blob Storage with Barman

        This tutorial is designed to guide you through how to setup a Barman backup to Azure Blob Storage for a PostgreSQL instance and store it in the cloud, using barman-cloud-backup.

        Using Foreign Data Wrappers to access remote PostgreSQL and Oracle databases

        SUMMARY: This article explains how to use Foreign Data Wrappers to access data in remote PostgreSQL and Oracle databases. 1. postgres_fdw 2. oracle_fdw There are many.

        Источник

        How Do I Connect Oracle To PostgreSQL over ODBC using the Oracle ODBC Gateway?

        I would like to select from a PostgreSQL instance on a server running openSUSE from an Oracle instance on a server running Oracle’s custom Linux flavor. I would like to do this using the Oracle ODBC gateway. I have done this successfully in the past and continue to do it using the same Oracle box and other SUSE/Postgres boxes. my ODBC manager on the SUSE (Postgres) side is: unixODBC My odbc.ini is:

        [postgresql] Description = Test to Postgres Driver = /usr/lib64/psqlodbcw.so Trace = Yes TraceFile = sql.log Database = host Servername = localhost UserName = ***** Password = ***** Port = 5432 Protocol = ReadOnly = Yes RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings = 

        [postgresql] Description = Postgresql driver for Linux Driver = /usr/lib64/psqlodbcw.so UsageCount = 1 

        # tnsnames.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/tnsnames.ora # Generated by Oracle configuration tools. LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 1522)) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) ODBC_SERVER123= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=dbs1) (PORT=1522) ) (CONNECT_DATA= (SID=server123) ) (HS=OK) ) 

        # listener.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522)) (ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 1522)) ) ) ) ADR_BASE_LISTENER = /opt/oracle SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=server123) (ORACLE_HOME=/opt/oracle/product/11gR1/db) (PROGRAM=dg4odbc) (ENVS=LD_LIBRARY_PATH=/usr/lib64:/opt/oracle/product/11gR1/db/lib) ) ) TRACE_LEVEL_LISTENER = 0 LOGGING_LISTENER = off 

        # # HS init parameters # HS_FDS_CONNECT_INFO = server123 HS_FDS_TRACE_LEVEL = 0 #HS_FDS_TRACE_LEVEL=DEBUG HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so HS_FDS_SUPPORT_STATISTICS = FALSE HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1 #HS_LANGUAGE=AMERICAN_AMERICA.US7ASCII HS_FDS_TIMESTAMP_MAPPING = "TIMESTAMP(6)" HS_FDS_FETCH_ROWS=1 HS_FDS_SQLLEN_INTERPRETATION=32 # # ODBC specific environment variables # set ODBCINI=/etc/unixODBC/odbc.ini # # Environment variables required for the non-Oracle system # #set =

        # sqlnet.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ADR_BASE = /opt/oracle 

        CREATE DATABASE LINK ODBC_SERVER123 CONNECT TO "*****" IDENTIFIED BY "*****" USING 'ODBC_SERVER123'; 

        [SQL] select * from "legit_view"@ODBC_SERVER123 [Err] ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [unixODBC][Driver Manager]Data source name not found, and no default driver specified ORA-02063: preceding 2 lines from ODBC_SERVER123 

        >isql -v postgresql ***** ***** +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> 

        Источник

        Читайте также:  Godex g500 драйвер linux