netesolutions / oracle_fdw_read_committed Goto Github PK

PostgreSQL Foreign Data Wrapper for Oracle with READ COMMITTED isolation level.

See Plagued by ORA-08177: can't serialize access for this transaction for more information about switch to READ COMMITTED.

Be aware that one PostgreSQL query might cause several Oracle queries (inner side of a nested loop join, several foreign tables involved in one query, etc.), and these queries need to use SERIALIZED isolation level see a consistent state of the database. You might have to simplify your queries by using staging tables.

• General installation and usage instructions are available in the original repo

On a brand new CentOS 7 you can follow this sequence of steps with Postgres 9.6:

1. Install Postgres 9.6

sudo yum install -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
sudo yum install -y postgresql96
sudo yum install -y postgresql96-server
sudo yum install -y postgresql96-contrib

# Database cluster = /var/lib/pgsql/9.6/data
sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb
sudo systemctl enable postgresql-9.6
sudo systemctl start postgresql-9.6  

2. Configure Postgres 9.6 (see Postgres 9.6 docs)
3. Install Oracle Instant Client

• Download recommended RPM packages
  • Basic
    • Basic rather than Basic Lite is a dependency of SDK
  • SQL*Plus
    • Install SQL*Plus to test that Instant Client is functioning correctly.
  • SDK
    • SDK is required to build some packages from sources
• Install:

sudo yum install -y *.rpm
{ cat <<'HEREDOC'
#!/usr/bin/env bash
export ORACLE_HOME=/usr/lib/oracle/12.2/client64
[[ "$PATH" != *"${ORACLE_HOME}/bin"* ]] && export PATH=$PATH:${ORACLE_HOME}/bin
HEREDOC
} | sudo tee /etc/profile.d/oracle_instant_client.sh
echo "/usr/lib/oracle/12.2/client64/lib" | sudo tee /etc/ld.so.conf.d/oracle-instantclient.conf
sudo ldconfig

• Log out and log in again

4. Install Postgres Development Package: sudo yum install -y postgresql96-devel
5. Install GNU Compiler Collection: sudo yum install -y gcc
6. Download source archive
7. Upload to a server and unarchive
8. sudo make
9. sudo make install
10. sudo -u postgres psql -c "CREATE EXTENSION oracle_fdw;"
11. Verify that the extension is installed

-- This should return extension and Oracle client versions
SELECT oracle_diag();

A result example: oracle_fdw_read_committed 2.0.1, PostgreSQL 9.6.9, Oracle client 12.2.0.1.0, ORACLE_HOME=/usr/lib/oracle/12.2/client64

12. Create a foreign server

CREATE SERVER :server
  FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver :'EZ_Connect_identifier');

CREATE USER MAPPING FOR :user
  SERVER :server OPTIONS (USER :'user', PASSWORD :'password');

13. Verify Oracle connection

-- Connect to Oracle and return Oracle server version
SELECT oracle_diag(:'server');

A result example: oracle_fdw_read_committed 2.0.1, PostgreSQL 9.6.9, Oracle client 12.2.0.1.0, Oracle server 12.1.0.2.0