yum -y update

cat << __EOF__ >> /etc/yum.repos.d/postgrespro-enterprise-9.6.repo
[postgrespro-enterprise-9.6]
name=PostgresPro Packages for CentOS - \$basearch
#baseurl=https://integrator-it:[email protected]/pgproee-9.6/centos/\$releasever/os/\$basearch/rpms
baseurl=https://repoee.postgrespro.ru/pgproee-9.6/rhel/\$releasever/os/\$basearch/rpms
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-POSTGRESPRO
__EOF__


Установка необходимых пакетов
yum -y install postgrespro-enterprise96-contrib postgrespro-enterprise96-server


Инициализируем экземпляр в созданную:
su - postgres -c "/usr/pgproee-9.6/bin/initdb -D /var/lib/pgproee/9.6/data --data-checksums --auth=trust"

Запускаем экземпляр:
su - postgres -c "/usr/pgproee-9.6/bin/pg_ctl -D /var/lib/pgproee/9.6/data start"

su - postgres -c "/usr/pgproee-9.6/bin/psql -h /tmp -c \"select pgpro_version();\""


mkdir -p /opt/oracle/product/
cd /opt/oracle/product/
wget https://www.dropbox.com/s/aleeegnc8tffeo8/client_1.tar.gz
tar -xzvf client_1.tar.gz
yum -y install libaio

cat << __EOF__ >> /var/lib/pgsql/.bash_profile
export ORACLE_HOME=/opt/oracle/product/client_1
export PATH=$ORACLE_HOME:/usr/pgproee-9.6/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
__EOF__


cat << __EOF__ >> /root/.bash_profile
export ORACLE_HOME=/opt/oracle/product/client_1
export PATH=$ORACLE_HOME:/usr/pgproee-9.6/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
__EOF__

# export ORACLE_HOME=/opt/oracle/product/client_1
# export PATH=$ORACLE_HOME:/usr/pgproee-9.6/bin:$PATH
# export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH

. /root/.bash_profile

# git clone https://github.com/laurenz/oracle_fdw
https://www.dropbox.com/s/f2kkztxdpe1u0eo/oracle_fdw-ORACLE_FDW_2_0_0.zip
unzip oracle_fdw-ORACLE_FDW_2_0_0.zip
cd oracle_fdw-ORACLE_FDW_2_0_0

pg_config --pgxs
-- /usr/pgproee-9.6/lib/pgxs/src/makefiles/pgxs.mk

make USE_PGXS=1
make USE_PGXS=1 install


su - postgres -c "/usr/pgproee-9.6/bin/psql -h /tmp -c \"CREATE EXTENSION oracle_fdw;\""

ostgres=# CREATE SERVER db11g FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver ‘//172.26.12.82:1522/db11g’);
CREATE SERVER
— Назначаем права на созданный сервер пользователю postgres (можно другим пользователям и ролям)

postgres=# GRANT USAGE ON FOREIGN SERVER db11g TO postgres;
GRANT
— Создаём USER MAPPING

postgres=# CREATE USER MAPPING FOR postgres SERVER db11g OPTIONS (user ‘unro2015’, password ‘unro2015’);
CREATE USER MAPPING
— Создаём в БД postgres схему которая будет соответствовать схеме в Oracle

postgres=# CREATE SCHEMA unro2015;
CREATE SCHEMA
— Импортируем описания всех объектов схемы в oracle в схему в postgres

postgres=# IMPORT FOREIGN SCHEMA «UNRO2015» from SERVER db11g INTO unro2015;
IMPORT FOREIGN SCHEMA
— Проверяем что работает

postgres=# select * from unro2015.classificator;
id    |    name
———+————-
3637254 | Субъекты
(1 row)




Применим настройкм перезапустив экземпляр NODE1:
# su - postgres -c "/usr/pgproee-9.6/bin/pg_ctl -D /var/lib/pgproee/9.6/data stop"
# su - postgres -c "/usr/pgproee-9.6/bin/pg_ctl -D /var/lib/pgproee/9.6/data start"


#####################
# Troubleshooting #01
#####################

postgres=# CREATE EXTENSION oracle_fdw;
ERROR:  could not load library "/usr/pgproee-9.6/lib/oracle_fdw.so": libclntsh.so.12.1: cannot open shared object file: No such file or directory

-bash-4.2$ ldd /usr/pgproee-9.6/lib/oracle_fdw.so
        linux-vdso.so.1 =>  (0x00007ffdd0386000)
        libclntsh.so.12.1 => /opt/oracle/product/client_1/libclntsh.so.12.1 (0x00007fe2f2246000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fe2f1e79000)
        libmql1.so => /opt/oracle/product/client_1/libmql1.so (0x00007fe2f1c03000)
        libipc1.so => /opt/oracle/product/client_1/libipc1.so (0x00007fe2f1885000)
        libnnz12.so => /opt/oracle/product/client_1/libnnz12.so (0x00007fe2f117b000)
        libons.so => /opt/oracle/product/client_1/libons.so (0x00007fe2f0f36000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fe2f0d32000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fe2f0a30000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fe2f0814000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fe2f05fa000)
        librt.so.1 => /lib64/librt.so.1 (0x00007fe2f03f2000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fe2f5425000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007fe2f01f0000)
        libclntshcore.so.12.1 => /opt/oracle/product/client_1/libclntshcore.so.12.1 (0x00007fe2efc7e000)


cat << __EOF__ >> /etc/ld.so.conf.d/ora_client.conf
/opt/oracle/product/client_1
__EOF__

ldconfig