PostgreSQL에 oracle_fdw 설정하기- 2/2

이번 글에서는 docker 관계없이 PostgreSQL에서 oracle_fdw를 설치 및 외부 테이블 생성하여 데이터를 조회해보는 작업까지를 진행하려고 한다. 

설치전 확인 및 고지 사항

• 오라클 클라이언트 버전 확인 후 설치 필요(필수)

• oracle fdw github에서 README 확인 필요(오라클 client 호환 버전 관련)

  https://github.com/laurenz/oracle_fdw

• fdw나 dblink는 온라인 업무에는 권장하지 않음

• 보편적인 체크 사항들 목록

오라클 클라이언트 다운로드가 필요(zip 파일) - 오라클 계정 필요
오라클 서버와 버전이 맞는 버전을 사용하는 것이 좋음(이슈 관련)
32비트 오라클 서버와 64비트 PostgreSQL 서버는 동작 불가.(동일한 bit의 프로그램 필요)
#SELECT * FROM v$version; 로 oracle 버전 정보 확인

----------Oracle client version 11.2 or better is required.---------- oracle_fdw 2.3.0버전 기준
그 외 호환 버전 정보는 Matrix in support document 207303.1 문서 참조
호환 Matrix에서는 최신 버전이 아닌 oracle 버전의 호환성에 대해 확인해주지 않는다는 점은 감안하여야 한다.
oracle_fdw 2.2.0 버전 기준 oracle client 10.2버전은 지원하지 않는다.

 

• 오라클 부분 확인 내용

--SID값으로 Service_name값 확인하기, 확인한 Service_name값으로 foreign server 설정값에 넣어주면 된다.
select value from v$parameter where name='<sid값>';
-------------------------
ORCLCDB.localdomain

 

• PostgreSQL 서버에서 필요한 파일 download 후 extension 설치하기

# Oracle Client Download
http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
 instantclient-basic-linux.x64-11.2.0.4.0.zip
 instantclient-sdk-linux.x64-11.2.0.4.0.zip
 instantclient-sqlplus-linux.x64-11.2.0.4.0.zip

# oracle fdw download
https://github.com/laurenz/oracle_fdw
 oracle_fdw-ORACLE_FDW_2_2_0.zip

#unzip all zip files
unzip instantclient-basic-linux.x64-11.2.0.4.0.zip
unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip
unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip
unzip oracle_fdw-ORACLE_FDW_2_2_0.zip

#환경변수 추가 
 #LD_LIBRARY_PATH 변수 끝에 :<오라클 클라이언트 압축 해제 경로> 추가
예) export LD_LIBRARY_PATH=$PGLIB:/home/seandb/instantclient_11_2
 #새로 추가 필요
export ORACLE_HOME=/home/seandb/instantclient_10_2
export TNS_ADMIN=/home/seandb/instantclient_10_2

#환경변수 적용 확인
source ~/.bashrc ~/.bash_profile
cd $ORACLE_HOME;pwd;cd -
cd $TNS_ADMIN;pwd;cd -
echo $LD_LIBRARY_PATH

## 라이브러리 파일 존재 여부 확인
ls -al $ORACLE_HOME/libclntsh*

# 오라클 클라이언트 디렉토리에 있는 so 파일 관련 링크 설정.
cd $ORACLE_HOME
ln -s libclntsh.so.11.1 libclntsh.so

#oracle pwd 빌드 및 설치
cd oracle_fdw-master
make&&make install

#PostgreSQL 재기동
pg_ctl stop
pg_ctl start

#oracle_fdw 익스텐션 설치
-- drop EXTENSION oracle_fdw;
CREATE EXTENSION oracle_fdw;

#외부 테이블 목록 조회
select * from pg_foreign_table;

 

• 서버 연결 및 확인 방법

--drop server oradb cascade;
--service name으로 외부(foreign) server 등록하기
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.56.222:1521/ORCLCDB.localdomain');

--하단의 명령어는 tnsnames.ora를 생성해준 뒤에 그 정의된 이름을 사용하는 방법이다.
vi tnsnames.ora
TESTDB =
        ( DESCRIPTION =
                ( ADDRESS_LIST =
                        ( ADDRESS = ( PROTOCOL = TCP ) ( HOST = 192.168.56.222 ) ( PORT = 1521 ) )
                )
                ( CONNECT_DATA =
                        ( SID = ORCLCDB )
                )
        )
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver 'TESTDB');

--혹은 하단과 같이 옵션을 전부 명시해주어도 동작한다
CREATE SERVER pdspawn FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=myhost.com)(Port=1521))(CONNECT_DATA=(SID=FOREIGN_DATABASE)))');

-- 별도 유저 생성 및 권한 부여가 필요한 경우 값들 변경하여 사용
--CREATE USER oracon PASSWORD 'oracon' LOGIN ;
--GRANT usage ON FOREIGN SERVER oradb TO oracon ;
--GRANT usage ON SCHEMA public TO oracon ;

CREATE USER MAPPING FOR experdba SERVER oradb OPTIONS (user 'oracle', password 'orapass');
-- oracle_fdw 익스텐션에서 제공하는 함수로 설정이 정상적으로 되었는지 확인 가능하다.
select oracle_diag('oradb');

--drop foreign table fr_persons;
CREATE FOREIGN TABLE fr_persons(
  PERSON_ID NUMERIC NOT NULL
, FIRST_NAME VARCHAR(50)
, LAST_NAME VARCHAR(50)
) SERVER oradb OPTIONS (SCHEMA 'ORACLE', TABLE 'PERSONS');

select * from fr_persons;

 

• oracle_fdw 관련 함수 목록

oracle_close_connections();
oracle_diag();
oracle_execute();
oracle_fdw_handler();
oracle_fdw_validator();

 

• 관련 오브젝트 목록 조회

seandb=> select * from pg_foreign_data_wrapper ;
  oid  |  fdwname   | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions
-------+------------+----------+------------+--------------+--------+------------
 16495 | dblink_fdw |    16386 |          0 |        16494 |        |
 16601 | oracle_fdw |       10 |      16596 |        16597 |        |
(2 rows)

seandb=> select * from pg_foreign_server
seandb-> ;
  oid  | srvname | srvowner | srvfdw | srvtype | srvversion |                 srvacl
            |                srvoptions
-------+---------+----------+--------+---------+------------+-----------------------------
------------+------------------------------------------
 16602 | oradb   |       10 |  16601 |         |            | {seandba=U/seandba,oracon=
U/seandba} | {dbserver=//192.168.56.222:1521/orclcdb}
(1 row)

seandb=> select * from pg_foreign_table ;
 ftrelid | ftserver |            ftoptions
---------+----------+----------------------------------
   16604 |    16602 | {schema=SYSTEM,table=TEST_TABLE}
   16607 |    16602 | {schema=ORACLE,table=PERSONS}
(2 rows)

 

• 외부 테이블 생성 후 테이블에 대한 정보 조회방법

select * from information_schema.tables where lower(table_type) like 'foreign';

 table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into
| is_typed | commit_action
---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------
+----------+---------------
 seandb       | seandba     | fr_persons | FOREIGN    |                              |                      |                           |                          |                        | YES
| NO       |
(1 row)

 

reference list:

https://github.com/laurenz/oracle_fdw/wiki

https://www.postgresql.org/docs/12/sql-createserver.html