Oracle linux postgresql odbc

agapoff.name | IT blog

Whatever

• Main
• About…
• Beyond IT
• R/C

Вы здесь : Home » Links between Oracle and PostgreSQL, or There and Back Again

Links between Oracle and PostgreSQL, or There and Back Again

Date September 24th, 2015 Author Vitaly Agapov

“I should like to know about risks, out-of-pocket expenses, time required and remuneration, and so forth” — by which he meant: “What am I going to get out of it? And am I going to come back alive?” J.R.R.Tolkien «The Hobbit, or There and Back Again» Please don’t wait for any revelations from this article. […]

Please don’t wait for any revelations from this article. This is just a compilation of some notes about configuring links between Oracle Database 11.2 and PostgreSQL 9.4. Everything written below is true for the case of Linux (RHEL/CentOS 7 to be exact) and I cannot guarantee its operability on other platforms or versions.

So let’s get to the point. In the first part of the article I will show how to configure the DB link from Oracle to PostgreSQL using ODBC driver and Oracle Heterogeneous Services (HS) agent. In the second part I will show how to access Oracle databases from PostgreSQL using foreign data wrapper (oracle_fdw).

Accessing PostgreSQL from Oracle

Step 1. Install PgSQL ODBC driver on Oracle server

If you have only default repositories configured then install this package containing PostgreSQL repo-file: http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm.

Step 2. Configure ODBC

ODBC (Open Database Connectivity) is a standard API for interacting with any DBMS independently of the operating systems or databases themselves. If you have any issues, questions or maybe you are too curious then refer to the ODBC documentation here: http://www.unixodbc.org/odbcinst.html. This documentation was really helpful during my experiments with all these databases.

Foremost we should define the driver in file /etc/odbcinst.ini. To do that add or edit such a section in this file:

Check if the driver configured:

Then we configure the ODBC data source in file /etc/odbc.ini:

All parameters including ReadOnly should not be set as “Yes” to be switched on or they will not be working. They should be set as 1.
Username and Password are optional and can be removed. We will use them only for checking the connectivity. The actual username and pasword will be set in Oracle while creating the db link. Also Debug, CommLog and Trace parameters can be turned on while testing.

If there is no writing access to /etc to edit files then the connection can defined in

/.odbc.ini file. And also the Driver parameter can contain the path to the driver library file rather than reference to driver description in odbcinst.ini.

Check the data source:

And check the ODBC connection with isql utility:

If everything is Ok then we will be able to issue SQL statements and get the appropriate responces. After this check the credentials could be removed from odbc.ini if needed.

Step 3. Configure HS agent to use ODBC link

Create init file for new HS instance – $ORACLE_HOME/hs/admin/initPSQL.ora

As for HS_LANGUAGE then this value is the only one the got it working finally. If I set it as AMERICAN_AMERICA.AL32UTF8 then nothing works. So… Let’s just keep it now.

Step 4. Configure listener

Add this to listener.ora and set the right ORACLE_HOME and SID (PSQL in my case):

Add this record to tnsnames.ora:

To check the tnsnames we can issue command “tnsping PSQL”.

Then we reload the listener:

Note that the listener should be always reloaded after changing the odbc ini-files to catch the changes.

Step 5. Create db link

Now we can create a synonym for the table:

But we should take into consideration that all identifiers in Oracle are automatically converted to uppercase and in PostgreSQL they otherwise converted to lowercase. This causes some troubles because we cannot just select:

And we are forced to use quotes:

Accessing Oracle from PostgreSQL

Step 1. Setup oracle_fdw

oracle_fdw is a third-party software. It is an extension for PostgreSQL which implements Foreign Data Wrapper for accessing Oracle databases. Its source code can be taken from
https://github.com/laurenz/oracle_fdw/releases/ and you can build and RPM using this spec-file: https://github.com/agapoff/RPM-specs/tree/master/oracle_fdw

Step 2. Configure foreign server

Run this under psql:

And seems like that is all. It is obviously much easier to access Oracle from PgSQL than vice versa. And there are no problems with uppercase and lowercase – data wrapper does all the work.

So Long, and Thanks for all the Fish.

Comments

5 комментариев на “Links between Oracle and PostgreSQL, or There and Back Again”

Hi Vitaly, this is a very clear and useful manual for the project I am working on now. I still have one problem and that is how to build oracle_fdw. My build raises numerous errors and I suspect that I am missing something. My question is: do you have a rpm for this fdw that I can install on centos 6? It would be very helpful to me.

M: +31 6 51917568

I have RPM for CentOS7 and for CentOS5:

You can try to use one of them.

When connecting to Postrgresql fro Oracle, there are some table identifiers and column identifiers > 30 cahracters. Oracle does not like chars > 30, so any ideas on how to over vcome, get round?

You may try to create a synonym-like view in PgSQL:

CREATE VIEW short AS SELECT * FROM some_long_name;

And then just create a synonym in Oracle:

create synonym some_pg_table for «short»@PG_LINK;

This might work

Hi Vitaly, thanks for this useful manual… maybe you have a RPM for PostgreSQL 11 on CentOS 7? I have a lot of problems building it from the git source.

Leave a comment

About this post

This entry was posted on Thursday, September 24th, 2015 at 17:32 and is filed under Linux, Oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Статистика

There are currently 114 posts and 199 comments, contained within 23 categories.

Источник

Установка и первоначальная настройка PostgreSQL 11 на Oracle Linux 7.6

В октябре 2017 года я писал статью про установку PostgreSQL 10 на Debian 9, с тех пор много воды утекло, вышла 11-я версия PostgreSQL и я решил написать краткую статью про его установку на Oracle Linux 7.6

По сравнению с Debian в установке на Oracle Linux есть несколько нюансов и в статье я это опишу.

Исходные данные: Oracle Linux 7.6
Задача: Установить PostgreSQL 11.x

Идем на официальную страницу выбора rpm-пакетов репозитариев и выбираем репозитарий Oracle Enterprise Linux 7 — x86_64

Обновляем кэш пакетов:

Устанавливаем PostgreSQL 11.x:

После установки PostgreSQL 11.x не создает кластер баз данных, это нужно сделать вручную.
Важно знать, что в Oracle Linux каталог с данными и файлами настройки будет /var/lib/pgsql/11/data

Создадим кластер баз данных, при этом зададим дополнительные настройки через PGSETUP_INITDB_OPTIONS:

Будет выведено сообщение:

Я задал через PGSETUP_INITDB_OPTIONS свои настройки, у Вас их может не быть (можете использовать настройки по умолчанию), не нужно слепо повторять мой пример, читайте документацию и задавайте нужные Вам опции создания кластера баз данных PostgreSQL.

Теперь включим запуск PostgreSQL 11.x при запуске системы:

Проверим открытые порты:

Первым делом меняем пароль пользователя postgres:

По умолчанию PostgreSQL принимает соединения только с локальных служб, т.к. слушает интерфейс localhost и это абсолютно правильно с точки зрения безопасности, но если Вы планируете подключения к серверу извне или из локальной сети, то Вам потребуется поменять параметр listen_addresses

Для PostgreSQL 11 открываем основной файл настроек /var/lib/pgsql/11/data/postgresql.conf и раскомментируем строку

таким образом мы укажем PostgreSQL слушать сетевые соединения на интерфейсе localhost и на нашем внутреннем интерфейсе локальной сети с IP адресом 192.168.35.10

Теперь разрешим подключение из локальной сети с любых хостов и к любым БД, для этого файле /var/lib/pgsql/11/data/pg_hba.conf после строки:

,где 192.168.35.0/24 — это наша локальная подсеть.

Далее выходим из под пользователя postgres и рестартуем PostgreSQL:

Проверяем открытые порты:

По умолчанию в Oracle Linux включен firewalld и он запрещает все входящие соединения кроме служб ssh и dhcpv6-client, если мы решили открыть доступ на порт 5432 из локальной сети, то нужно настроить firewalld.

Проверим список разрешенных служб:

Теперь добавим службу postgresql:

Проверим правила через iptables:

Более подробно о настройке firewalld можно узнать из этой статьи.

Отлично, порт открыт! Теперь мы можем подключиться к PostgreSQL c локального сервера и из нашей локальной сети.

В данной статье мы не производили первоначальную минимальную настройку параметров PostgreSQL, для этого Вы можете воспользоваться моей старой статьей про установку PostgreSQL 10 на Debian 9 + использовать онлайн конфигураторы простой и более сложный

На этом все, до скорых встреч.

Если у Вас возникли вопросы или Вы хотите, чтобы я помог Вам, то Вы всегда можете связаться со мной разными доступными способами.

Источник

Oracle Heterogeneous Services

An example configuring Oracle ODBC gateway to PostgreSQL

Jul 12, 2019 · 8 min read

That sounds futuristic, like a 2025 post-microservices trend. Monolithic databases are definitely dismantled, we developed in microservices, spread data to many databases. Because we had agile development teams, they have chosen the database technology that fits their need: cheap, easy, free from any infrastructure constraints, and of course cool enough for their CV. The enterprise information system is now a zoo with all species represented.

Then, the business needs to query a n d operate on data that has been produced in many different sources, heterogeneous systems, and polyglot data structures. Of course, all was sourced, streamed and replicated, with an approximate consistency and latency.

But after all that fun, the data analysts need a consistent and integrated view of this data to meet their business objectives. They need to process it in one system only. They need to map it and query transparently, without having to learn different languages and structures. They may even want to enrich it, updating the multiple systems, in a transaction that stays consistent even in case of failure.

Actually, this is not new. The screenshot above is from 1995 when Oracle introduced their Oracle Gateway to connect to other databases like if they were Oracle ones. We can run DML, procedures, and transactions over Database Links. It has evolved with gateways to specific database systems, but the gateway to ODBC databases is still there and free. And Heterogenous Services are available even in Standard Edition.

Today, whether you want to offload some tables to a different DB, or you already have multiple DBMS that you want to query from a single service, you will have to run queries and transactions across different systems. Here is an example where I’ll connect Oracle DB Links to a PostgreSQL database.

In this example, I prefix the name with the type, like: usrdemo for the user, dsndemo for the data source name, lsndemo for the listener… That’s not something I do usually but it helps to understand clearly the link of components. There is a double network communication for each call here: from the database Heterogenous Service to the Gateway listener, and the listener calls the remote database through ODBC.

1. Install a PostgreSQL database

For this demo, I have setup an Oracle DBaaS in the Oracle Cloud Infrastructure which is a PaaS (the database is installed automatically and ready to use) but with IaaS access (I can connect as root and install whatever I need).

I chose a 19c Standard Edition (19c has just been made available this week on this service) running on Oracle Linux 7.5 which is the same as RHEL7.

There, I have an Oracle Database 19c already running on Oracle Enterprise Linux 7 and I install PostgreSQL 11 to demo the gateway.

I create a database and initialize it with pgbench.

Now I have a ‘demo’ database with pgbench tables, accessible from my oracle user through TCP/IP port 5432 with user ‘usrdemo’ password ‘pwddemo’

2. Install Linux ODBC and Postgres ODBC

The free Oracle Gateway connects to any database through ODBC. I install the ODBC driver for PostgreSQL

I define my PostgreSQL database as the ODBC Data Source:

You can use the default location like /etc/odbc.ini or $HOME/.odbc.ini but here I show a specific location just to mention it.

3. Install Oracle Gateway

I download the Heterogeneous Services Database Gateways to non-Oracle Databases. On the Oracle Database Software Downloads, choose the “See All” link:

Oracle Database 19c Download for Linux x86-64

Oracle Database 19c Download for Linux x86-64

Oracle Database 19c Download for Linux x86-64www.oracle.com

From there I download the LINUX.X64_193000_gateways.zip (that must be done manually, clicking the “I accept the license terms” checkbox).

I install it in my Oracle Home (ORACLE_HOME must be set):

I have now some additional binaries and an HS (Heterogeneous Services) product directory:

4. Setup Oracle Gateway to ODBC

In this directory I define the Gateway configuration which references the ODBC driver and DSN:

The “set ODBCINI” must be used if we don’t use the default /etc/odbc.init or $HOME/.odbc.ini

The trace can dump more information to the log subdirectory in case of an error is encountered.

This Database Gateway has a System IDentifier, that will be referenced by the listener. The exposed database name is HO.WORLD by default, which is very ugly, but you can change that with HS_DB_NAME and HS_DB_DOMAIN.

5. Setup Oracle Gateway listener

The name is referenced with SID_NAME from a listener:

I start this listener:

The listener is the Gateway that will be contacted by the Heterogenous Services.

6. Setup Heterogenous Services

I define a tnsnames.ora entry to connection to this listener:

I can tnsping but not connect because this is an Heterogenous Service agent

7. Create Heterogenous Services database link

All components are there and ready for heterogeneous queries and transactions. I have a pluggable database PDB1 and create a demo user there.

Note that I’ll set global_names to false here because I’ve not defined a name and domain for my gateway. Then it is called HO.WORLD and I don’t want to have my database link names starting with “HO.WORLD”. And if I don’t, with the default global_names, I’ll get something like: “ORA-02085: database link DBLPGDEMO.SUB11131414120.ACEDVCN.ORACLEVCN.COM”. To avoid this, either I set HS_DB_NAME and HS_DB_DOMAIN or I forget about global name enforcement.

7a. dictionary translation

That’s the first gem from Oracle transparent gateway: you see the metadata as if it were an Oracle database. The PostgreSQL tables, columns and datatypes were converted to Oracle ones.

7b. distributed queries

The second gem is about the optimizer. You can run joins between two remote queries and they will be processed on the right site:

Cardinality estimation is not as accurate as for local tables, but the query that is pushed will be optimized by the remote optimizer.

7c. native passthrough

The third gem is the possibility to run any native query with the DBMS_HS_PASSTHROUGH which is very similar to DBMS_SQL:

You wonder how this DBMS_HS_PASSTHROUGH is called with a database link as of it was on the remote database, but you know that it is not defined there? It is actually interpreted by the Gateway, more info about this magic:

dbms_hs_passthrough-the magic package

This is a package that you can call, that does not exist in the database. I found this out after reading about its…

8. Troubleshoot

There are limitation, some cryptic error messages but enabling the trace with HS_FDS_TRACE_LEVEL=ON creates a file in ?/hs/log/

At least we have the default values for the non-specified parameters.

Finally, a little note about the architecture: the Gateway with the ODBC driver and the heterogeneous service listener can be installed on the Oracle side, the remote database side, or in the middle (why not a Docker container if it makes it more appealing?) as it communicates by TCP/IP and is not subject to licensing. Like a streaming replication server but with redundancy, no latency, and transparent real-time query.

Franck Pachot

Developer Advocate at Yugabyte, Open Source distributed SQL database. Incidentally Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member

Источник