Postgresql DB로 Oracle DB에 DB LINK를 걸어보자

    DB Link

    Cho.Duck's profile image

    Cho.Duck

    2019-07-08 14:00

    이 포스트는 Ubuntu 환경 및 ubuntu docker 에서 Postgresql DB로 Oracle DB에 DB LINK를 걸어버리는 내용입니다!

    Overview

    1. ubuntu에 Postgresql을 설치합니다.
    2. Oracle fdw를 설치합니다.
    3. Postgresql에 Oracle fdw 확장모듈을 설치합니다.
    4. Pstgresql에 외부 테이블을 정의합니다.
    5. 사용해봅시다!

    Index

    1. Postgresql 설치
      1. Postgresql 설치 및 설정
    2. Oracle fdw 설치
      1. OCI 라이브러리 설치
      2. Oracle fdw 설치
      3. Oracle_fdw EXTENSION
    3. Db link
      1. 외부 서버 만들기
      2. 사용자 매핑 정의
      3. 외부 테이블 정의
      4. 외부 테이블 사용

    Postgresql 설치

    • ubuntu 16:04 docker image를 다운받아 실행한 상태라는 전제하에 작성된 글입니다.

    1. Postgresql 설치

      1. Postgresql 설치 및 설정

        1. apt-get update 및 upgrade apt-get update 후 apt-get upgrade 를 통해 apt 모듈을 업데이트 해줍시다.

          apt-get upgrade 중간에 한번 물어보는데 Y를 눌러줍시다.

        2. posgtreasql install apt-get install postgresql 을 입력해 설치를 진행해 줍시다.

          apt-get_postgresql 중간에 한번 물어볼텐데 마찬가지로 Y를 눌러줍시다.

        3. dpkg -l |grep postgres 를 입력해 설치가 잘 되었는지 확인합시다.

          grep_postgres 요렇게 표시되면 잘 된겁니다.

        4. cat /etc/passwd | grep postgres 를 입력하여 관리자 계정이 잘 생성되어 있는지 확인합니다.

          postgresID postgres 로 잘 생성되어 있습니다.

        5. Postgresql 상태 확인 및 실행 /etc/init.d/postgresql status 을 입력해서 상태를 확인합시다.

          postgresql_status 보통 이렇게 종료되어 있습니다.

          /etc/init.d/postgresql start 를 입력해 Postgresql을 실행한 후, psql -U postgres 를 입력하여 로그인 해 봅시다.

          postgresql_login 보시다시피 로그인이 되지 않습니다. 초기 비밀번호가 설정이 되어 있지 않아서 입니다.

          로그인을 하기 위해, 설정을 변경하기 위해 apt-get install vim를 입력해 vi 에디터를 설치합시다.

          install_vim 중간에 물어보면 Y를 살포시 입력해주세요. install_vim2

          vi /etc/postgresql/9.5/main/pg_hba.conf 를 입력해서 설정 파일을 열어 줍시다. (pg_hba.conf는 Postgresql의 인증 관련 설정 파일입니다.) 열면 엄청나게 긴 내용이 있는데 우리는 전부 무시하고 맨 아래로 내려갑시다!

          vi_pg_hba

          커서를 아래 사진에 빨간 박스 친 곳 으로 보낸 후 i를 눌러 인설트 모드로 변경, 내용을 peer에서 trust로 수정해 줍시다.

          ch_pg_hba

          psql -U postgres 를 입력하여 로그인 해 봅시다.

          postgres_login 이제 로그인이 됩니다! 보안을 위해서는 비밀번호를 설정해주고, Postgresql을 외부에서 조회할 수 있도록 설정파일의 변경이 필요합니다만, 그 부분은 다른 블로그를 찾아봐 주시고, 저는 바로 Oracle_fdw 설치로 가겠습니다. \q 를 입력하여 우분투로 돌아와 주세요.

    2. Oracle fdw 설치

      1. OCI 라이브러리 설치 Oracle fdw는 OCI(Oracle Call Interface)라이브러리를 사용하여 Oracle DB에 엑세스 합니다. 따라서 OCI 라이브러리를 설치해야 하죠. OCI는 OTN(Oracle Technology Network)에서 배포하고 있습니다.

      oci

      oracle-instantclientxx.x-basic
oracle-instantclientxx.x-devel
oracle-instantclientxx.x-sqlplus    를 다운로드 해서 docker에 넣어 줍시다.

      .rpm 파일은 RPM 패키지 매니저(RPM Package Manager←Red Hat Package Manager)는 원래 레드햇에서 사용되었던 패키지 파일로서 ubuntu 설치를 위해서는 alien이라는 모듈이 필요합니다. apt-get install alien dpkg-dev debhelper build-essential 을 입력하여 에일리언 패키지와 설치에 필요한 여타 모듈들을 설치해 줍시다.

      alien_install 사진은 짧지만 밑에 주루루루룩 올라옵니다. 중간에 Y 입력인 이제 아시죠?

      이제 다운받은 rpm 파일들을 설치해 줍시다. alien -i xxx.rpm 입력하면 설치가 진행됩니다. basic -> devel -> sqlplus 순서로 설치합시다.

      alien_-i 사진은 basic 설치입니다. 나머지도 하나씩 설치해줍시다. basic이 오래걸리고 나머지는 빠르게 설치되는 편입니다.

      1. Oracle_fdw 라이브러리 설치 Oracle fdw를 다운로드해 봅시다. Oracle fdw는 github에서 다운로드 받아 docker에 넣어 줍시다. zip 파일로 받으셨으면 압축을 풀어주세요. unzip 을 설치해서 압축을 풀어주시면 됩니다. 이제 Oracle fdw를 설치하면 되는데… 그에 앞서 선행 프로그램들을 설치해 줍시다. 이 4개의 파일들을 설치해 줍니다.

        apt-get install python-psycopg2 apt-get install libpq-dev apt-get install postgresql-server-dev-all apt-get install postgresql-common oracle_fdw1 Y눌러주시는건 아시죠?

      Oracle fdw 폴더로 들어가서 make를 해 줍시다.

      oracle_fdw1 make: Nothing to be done for ‘all’. 요런 에러가 발생하면 make clean 을 해주신 후 make 해주세요.

      위의 사진처럼 잘 make 되면 make install 해줍시다.

      make_install

      1. Oracle_fdw EXTENSION 이제 사용하려는 데이터베이스에 대해 SQL의 CREATE EXTENSION 명령을 사용하여 oracle_fdw 확장 모듈을 설치해야 합니다. 그런데 postgresql에 들어가서 CREATE EXTENSION 명령을 사용하면 아래와 같은 에러가 발생하게 됩니다.

        CREATE_EXTENSION_ERR

      oracle_fdw.so를 로드할 libaio1가 없기 때문이므로 apt-get install libaio1를 입력해 인스톨 해 줍시다.

      libaio1

      다시 postgresql로 들어가서 해봅시다!

      CREATE_EXTENSION 잘 됩니다! 이제 사전 설치 작업은 종료되었습니다. 이제 아래에서 본격적으로 Db link를 연결하고 사용해 봅시다.

    3. Db link

      1. 외부 서버 만들기 Oracle 데이터베이스에 액세스하기위한 연결 정보를 외부 서버 (oradb)로 정의합니다.

        =# CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw -# OPTIONS (dbserver ‘//xxx.xxx.xxx.xxx:xxxx/XE’); 위 두줄을 입력하면 CREATE SERVER 라는 대답이 돌아오면 성공!

      테스트 용 일반 데이터베이스 사용자 postgresql유저가 정의한 외부 서버를 사용할 수 있도록 권한을 부여합니다.

      =# GRANT USAGE ON FOREIGN SERVER oradb TO postgresql유저; 위에 대로 입력하면 GRANT 라는 대답이 돌아오면 성공!

      1. 사용자 매핑 정의 위에서 만든 외부 서버(oradb)와 사용자(postgresql유저)의 매핑을 정의합니다. PostgreSQL에서 사용자(postgresql유저)가 외부 테이블에 액세스 할 때 위에서 정의한 oradb서버에서 ‘oracle유저id’와 ‘oracle유저pass’로 로그인하게 됩니다.

        =# CREATE USER MAPPING FOR postgresql유저 SERVER oradb OPTIONS (USER ‘oracle유저id’ , PASSWORD ‘oracle유저pass’); 라고 위에 입력하면 CREATE USER MAPPING 라는 대답이 돌아오면 성공!

      2. 외부 테이블 정의 Oracle 데이터베이스에 테이블 뷰를 PostgreSQL을 외부 테이블로 정의합니다. Oracle 데이터베이스의 테이블 ORA_TEST1을 외부 테이블 pg_test1로 정의해 봅시다.

        => CREATE FOREIGN TABLE pg_test1 -> ( (> id int OPTIONS (key ‘true’) NOT NULL, (> etc varchar (100), (> colldate date (>) -> SERVER oradb112 OPTIONS (SCHEMA ‘oracle유저id’, TABLE ‘ORA_TEST1을’); 위에대로 (link를 붙일 오라클 table과 같은 형태로 선언하되 “OPTIONS (key ‘true’)”는 해당 열이 기본키임을 선언하는 부분이며 not null은 비면 안된다는 것.)선언을 해 주면 CREATE FOREIGN TABLE 대답이 돌아옵니다. 나중에 select 가 아닌 insert update delete를 사용할때는 반드시 “OPTIONS (key ‘true’)”을 사용하여 기본키를 선언해 놓은 상태여야 합니다.

      3. 외부 테이블 사용 위의 대로 모든게 갖추어 지셨다면 select insert update delete 등의 쿼리로 외부 테이블을 사용 할 수 있게 됩니다.

        => select * from _ora_test1; id | etc | colldate ——————+——+———— 030F00E82F00002F | test | 2019-06-20 030F00E802000002 | test | 2019-06-20 (2 rows)

    이상으로 Postgresql 에서 Oracle 에 DB Link를 연결하는 방법에 대해 포스트해 보았습니다. 유익한 자료가 되셨길 바랍니다.