PostgreSQL to Oracle DB링크 만들기(Centos, Docker)

■ psql 올리기

#docker run -p 5432:5432 --name pg-test2 -it 레포지토리주소/ubuntu:18.04 bash

> apt-get update

> apt-get install postgresql postgresql-contrib

> service postgresql start

> exit

#firewall-cmd --permanent --zone=public --add-port=5432

 

■ oracle instant client 설치
https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

 

#mkdir /download

#docker cp /storage/download/oracle-instantclient11.2-basiclite-11.2.0.4.0-1.x86_64.rpm pg-test2:/download;docker cp /storage/download/oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64.rpm pg-test2:/download;docker cp /storage/download/oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm pg-test2:/download;docker cp /storage/download/oracle-instantclient11.2-tools-11.2.0.4.0-1.x86_64.rpm pg-test2:/download;docker cp /storage/download/oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm pg-test2:/download;docker cp /storage/download/oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm pg-test2:/download

 

#docker -it 로 pg-test2 접속

> apt-get install wget
> apt-get install alien

 

> alien oracle-instantclient11.2-basiclite-11.2.0.4.0-1.x86_64.rpm;alien oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm;alien oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm;alien oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64.rpm;alien oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm;alien oracle-instantclient11.2-tools-11.2.0.4.0-1.x86_64.rpm

 

> dpkg -i oracle-instantclient11.2-basiclite_11.2.0.4.0-2_amd64.deb;dpkg -i oracle-instantclient11.2-devel_11.2.0.4.0-2_amd64.deb;dpkg -i oracle-instantclient11.2-jdbc_11.2.0.4.0-2_amd64.deb;dpkg -i oracle-instantclient11.2-odbc_11.2.0.4.0-2_amd64.deb;dpkg -i oracle-instantclient11.2-sqlplus_11.2.0.4.0-2_amd64.deb;dpkg -i oracle-instantclient11.2-tools_11.2.0.4.0-2_amd64.deb

 

> vi .bashrc 에서 환경변수 수정
> vi /etc/profile 에서도 수정
> vi .profile 에서도 수정

ORACLE_HOME=/usr/lib/oracle/11.2/client64
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH
TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_HOME LD_LIBRARY_PATH PATH TNS_ADMIN

 

> apt-get install libaio1

 

■ oracle 클라이언트 테스트

> sqlplus 스키마명/패스워드@OralceDB인스턴스

 

■ Oracle_fdw 라이브러리 설치

> apt-get install python-psycopg2
> apt-get -y install libpq-dev
> apt-get -y install postgresql-server-dev-all
> apt-get -y install postgresql-common

> wget https://github.com/laurenz/oracle_fdw/archive/ORACLE_FDW_2_2_0.tar.gz

> tar -zxvf ORACLE_FDW_2_2_0.tar.gz
> cd /oracle_fdw-ORACLE_FDW_2_2_0
> make
> make install

*심볼릭링크로 라이브러리 복사하기(환경변수 버그가 있을경우)
> ln -s /usr/lib/oracle/11.2/client64/lib/libnnz11.so /usr/lib/libnnz11.so
> ln -s /usr/lib/oracle/11.2/client64/lib/libclntsh.so.11.1 /usr/lib/libclntsh.so.11.1

 

■ DBLink 만들기
/etc/postgres/.../pg_hba.conf 에서 host all postgres trust로 변경

 

> psql -U postgres

alter user postgres with password 'postgres';

create extension oracle_fdw;

 

CREATE SERVER OracleDB인스턴스명 FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//OracleDB_IP:1521/OracleDB인스턴스명'); 

CREATE USER MAPPING FOR postgres SERVER OracleDB인스턴스명 
OPTIONS (USER 'Source스키마명' , PASSWORD '패스워드');

postgres=# create foreign table pg2oracle(
postgres(#  IINVOICE     NUMERIC(11)                        NOT NULL,
postgres(#   ISSUEID       VARCHAR(255)               DEFAULT null,
postgres(#   IINVOICEPACK  NUMERIC(11)                      DEFAULT null,
postgres(#   SENDER       VARCHAR(255)               DEFAULT null,
postgres(#   ISSUER        VARCHAR(255)               DEFAULT null,
postgres(#   HEXRVALUE     VARCHAR(255)               DEFAULT null,
postgres(#   XMLINVOICE     TEXT                           DEFAULT null,
postgres(#   TSAVE         DATE                            DEFAULT now()                                                                                                                           NOT NULL,
postgres(#   ISVALID       NUMERIC(1)                       DEFAULT 0                                                                                                                                  NOT NULL,
postgres(#   CAUSE         VARCHAR(255)               DEFAULT null,
postgres(#   TSEND         DATE,
postgres(#   ISLOCK        NUMERIC(1)                       DEFAULT 0
postgres(# )
postgres-# server OracleDB인스턴스명 OPTIONS (SCHEMA 'Source스키마명', TABLE 'SOURCE테이블명');

 

 

※ env에러가 계속 덜어진다면 환경변수를 명시적으로 줘서 service start하기
> su - postgres

 

/usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf

 

 

※ 환경변수 디버깅하기
SELECT oracle_diag();
select * from pg_stat_activity;
SELECT pg_backend_pid();
cat /proc/57/environ | xargs -0 -n1

 

 

※ 참고 링크
https://dkant.net/2019/07/08/DBlink/