OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : PostgreSQL 10, Oracle Database 11.2.0.4
방법 : PostgreSQL 10, oracle fdw extension 으로 오라클 DB와 연결(dblink)
postgresql에서 oracle database 로 연결하는 방법을 설명함
postgresql이 설치된 서버 ip : 192.168.137.50
oracle이 설치된 서버 ip : 192.168.137.51
오라클 리스너 포트 : 1521
오라클 sid : orcl
Postgresql 에서 Oracle에 있는 데이터를 가져오기 위해서는 FDW를 이용할 수 있음
FDW는 Foreign Data Wrapper 의 약자로 이기종 데이터베이스의 외래테이블 간에 연결하여 사용할 수 있는 솔루션임
PostgreSQL 10, oracle fdw extension 생성방법
현재 PostgresSQL database에 oracle_fdw extension 생성
1 2 3 4 | # su - postgres $ psql psql> create extension oracle_fdw; CREATE EXTENSION |
oracle 데이터베이스 연결서버 생성
1 2 | psql> create server ora11g foreign data wrapper oracle_fdw options(dbserver '//192.168.137.51:1521/orcl'); CREATE SERVER |
oracle 데이터베이스와 연결 할 유저 생성
1 2 3 4 5 6 | psql> create user oracon password 'oracon' login; CREATE ROLE psql> grant usage on foreign server ora11g to oracon; GRANT psql> create user mapping for oracon server ora11g options(user 'scott', password 'tiger'); CREATE USER MAPPING |
oracle 데이터베이스와 연결 할 유저로 재접속 후 외래테이블 생성
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | psql> \q $ psql -U oracon psql> create foreign table fdw_emp( empno numeric, ename text, job text, mgr numeric, hiredate date, sal numeric, comm numeric, deptno numeric )server ora11g options(schema 'SCOTT', table 'EMP'); CREATE FOREIGN TABLE |
외래테이블 조회
1 2 3 | psql> select * from fdw_emp; 오류: connection for foreign table "fdw_emp" cannot be established DETAIL: ORA-12543: TNS:destination host unreachable |
에러발생
oracle 데이터베이스가 설치된 서버 방화벽 조치(방화벽 정지)
1 2 3 4 | # /etc/init.d/iptables stop iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Flushing firewall rules: [ OK ] iptables: Unloading modules: [ OK ] |
다시 외래테이블 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | psql> select * from fdw_emp; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+------------+------+------+-------- 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | | 20 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | | 10 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | | 20 7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | | 20 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10 (14 rows) |
oracle 데이터베이스에서 동일 테이블 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | $ sqlplus scott/tiger SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. |
동일한 결과를 확인 할 수 있음
postgresql db 링크 테이블 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 | psql> select * from information_schema.tables; 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 | i s_typed | commit_action ---------------+--------------------+---------------------------------------+---------------+------------------------------+- ---------------------+---------------------------+--------------------------+------------------------+--------------------+-- --------+--------------- postgres | pg_catalog | pg_type | BASE TABLE | | | | | | YES | N O | postgres | public | fdw_emp | FOREIGN TABLE | | | | | | YES | N O | |
특정 스키마의 외래 테이블 확인
1 2 3 | psql> select table_schema, table_name, table_type from information_schema.tables where table_schema='스키마명'; |
fdw 서버 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 | psql> select srvname as name, srvowner::regrole as owner, fdwname as wrapper, srvoptions as options from pg_foreign_server join pg_foreign_data_wrapper w on w.oid = srvfdw; name | owner | wrapper | options --------+----------+------------+--------------------------------------- ora11g | postgres | oracle_fdw | {dbserver=//192.168.137.51:1521/orcl} (1 row) |
fdw 서버 조회 다른방법
1 2 3 4 5 6 7 8 | psql> \des+ List of foreign servers Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description --------+----------+----------------------+---------------------+------+---------+-----------------------------------------+- ------------ ora11g | postgres | oracle_fdw | postgres=U/postgres+| | | (dbserver '//192.168.137.51:1521/orcl') | | | | oracon=U/postgres | | | | (1 row) |
oid 조회
1 2 3 4 5 6 7 8 | psql> select oid, * from pg_foreign_server; oid | srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptio ns -------+---------+----------+--------+---------+------------+-----------------------------------------+---------------------- ----------------- 18053 | ora11g | 10 | 18049 | | | {postgres=U/postgres,oracon=U/postgres} | {dbserver=//192.168.1 37.51:1521/orcl} (1 row) |
oid로 외래테이블 조회
1 2 3 4 5 6 | psql> select * from pg_foreign_table where ftserver=(select oid from pg_foreign_server where oid = 18053); ftrelid | ftserver | ftoptions ---------+----------+-------------------------- 18058 | 18053 | {schema=SCOTT,table=EMP} (1 rows) |
relkind로 외래테이블 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | psql> select oid, * from pg_class where relkind = 'f'; oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuple s | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relrep lident | relispartition | relfrozenxid | relminmxid | relacl | reloptions | relpartbound -------+----------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+--------- --+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------ +------------+-------------+----------------+----------------+----------------+---------------------+----------------+------- -------+----------------+--------------+------------+--------+------------+-------------- 18058 | fdw_emp | 2200 | 18060 | 0 | 18051 | 0 | 18058 | 0 | 0 | 0 | 0 | 0 | f | f | p | f | 8 | 0 | f | f | f | f | f | f | f | t | n | f | 0 | 0 | | | (1 rows) |
물리서버 IP 변경시 fdw server IP 변경
postgresql에서 oracle database 로 연결하는 방법을 설명함
oracle이 설치된 서버 ip 가 192.168.137.53 으로 변경되었다고 가정함
fdw 서버 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 | psql> select srvname as name, srvowner::regrole as owner, fdwname as wrapper, srvoptions as options from pg_foreign_server join pg_foreign_data_wrapper w on w.oid = srvfdw; name | owner | wrapper | options --------+----------+------------+--------------------------------------- ora11g | postgres | oracle_fdw | {dbserver=//192.168.137.51:1521/orcl} (1 row) |
유저 접속 해제 후 superuser로 접속 후 서버 IP 변경
1 2 3 4 | psql>\q $ psql psql> alter server ora11g options (add dbserver '192.168.137.53:1521/orcl'); 오류: "dbserver" 옵션이 여러 번 제공되었음 |
바로 add 를 하면 dbserver 옵션이 여러 번 제공되었다고 적용되지 않음
set 옵션으로 변경
1 2 | psql> alter server ora11g options (set dbserver '192.168.137.53:1521/orcl'); ALTER SERVER |
fdw 서버 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 | psql> select srvname as name, srvowner::regrole as owner, fdwname as wrapper, srvoptions as options from pg_foreign_server join pg_foreign_data_wrapper w on w.oid = srvfdw; name | owner | wrapper | options --------+----------+------------+--------------------------------------- ora11g | postgres | oracle_fdw | {dbserver=//192.168.137.53:1521/orcl} (1 row) |
정상적으로 변경 완료
외래 테이블 조회 테스트
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | psql> \q $ psql -U oracon psql> select * from fdw_emp; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+------------+------+------+-------- 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | | 20 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | | 10 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | | 20 7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | | 20 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10 (14 rows) |
정상적으로 조회됨
참조 : https://positivemh.tistory.com/465
http://www.gurubee.net/lecture/2962
https://www.postgresql.org/docs/8.4/sql-alterserver.html
'PostgreSQL > Admin' 카테고리의 다른 글
| PostgreSQL 16 기동, 정지 방법 및 로그 확인 (0) | 2024.03.22 |
|---|---|
| PostgreSQL 10 유저 Password_encryption 변경 (0) | 2020.02.24 |
| oracle_utils.c:22:17: fatal error: oci.h: No such file or directory (0) | 2019.11.27 |
| linux7.6에 PostgreSQL 10 + PG-Strom 2.2 GPU 사용 테스트 (0) | 2019.11.20 |
| PostgreSQL 10 유저 superuser 권한, 롤 부여 (0) | 2019.11.04 |