oracle_fdw, postgres_fdw, tibero_fdw는 PostgreSQL에서 제공하는 Foreign Data Wrapper(FDW) 기능을 활용해 다른 데이터베이스와 상호 작용할 수 있도록 해주는 extension입니다.
FDW는 PostgreSQL 외부의 데이터베이스와 연결하여 데이터를 조회하거나 통합하는 데 사용됩니다.
PostgreSQL을 사용하다 보면 여러 데이터베이스를 동시에 조회하거나 통합해야 하는 경우가 많습니다. 이를 위해 데이터를 ETL(Extract, Transform, Load) 방식으로 주기적으로 이동하는 방법도 있지만, 실시간 데이터 접근이 필요한 경우에는 FDW(Foreign Data Wrapper)가 더욱 유용합니다.
FDW란?
Foreign Data Wrapper(FDW)는 PostgreSQL에서 외부 데이터베이스를 로컬 테이블처럼 사용할 수 있도록 지원하는 기능입니다.
FDW는 SQL 표준의 Foreign Data Wrapper 개념을 구현하며, 외부 데이터 소스와 PostgreSQL 간 네트워크 통신을 통해 데이터를 주고받습니다.
FDW는 다음과 같은 상황에서 유용합니다:
- 서로 다른 데이터베이스 간 데이터 통합
- 데이터 마이그레이션
- 원격 데이터베이스의 데이터 조회 및 관리
Note
FDW는 네트워크 속도와 원격 서버 성능에 따라 쿼리 성능이 달라질 수 있습니다.ㆍ주요 차이점
|
기능
|
oracle_fdw
|
postgres_fdw
|
tibero_fdw
|
|---|---|---|---|
|
대상 DB
|
Oracle
|
PostgreSQL
|
Tibero
|
|
데이터 타입 호환성
|
Oracle과 PostgreSQL 간 매핑 제공
|
PostgreSQL 간 네이티브
타입 지원
|
Oracle과 유사한 SQL 지원.
Tibero와 PostgreSQL 간 일부 데이터 타입 차이 존재. 숫자, 문자 타입은 호환되지만, 날짜 및 LOB 타입은 변환 필요. |
|
트랜잭션 제어
|
제한적
( 2PC(Two-Phase Commit) 미지원, SAVEPOINT(부분 Rollback) 사용 불가, 트랜잭션 모델 차이 존재 ) |
O
|
제한적
( 2PC(Two-Phase Commit) 미지원, SAVEPOINT 사용 불가, 트랜잭션 격리 수준 차이 존재 ) |
|
DML 지원 여부(select, insert, update, delete)
|
O
( foreign table 생성 시 ‘OPTIONS (key ‘true’)’ 옵션 필요 )
|
O
|
△
(updatable ‘true’ 옵션 적용 시 insert만 지원)
|
|
DDL 지원 여부
|
X
|
X
|
X
|
|
JOIN 최적화
|
Remote 테이블 간 JOIN 지원
|
PostgreSQL 내부 최적화 가능
|
제한적
(Tibero에서 조인을 수행할지, PostgreSQL에서 수행할지에 따라 성능이 크게 차이 날 수 있음) |
|
설치 요구사항
|
Oracle Instant Client 필요
|
PostgreSQL 서버 필요
|
Tibero 클라이언트 필요
|
○ oracle_fdw
- PostgreSQL과 Oracle 데이터베이스를 연결하는 FDW입니다.
- 주요 특징:
- Oracle 데이터베이스의 테이블과 뷰를 PostgreSQL에서 원격 테이블처럼 조회 가능
- Oracle의 데이터 타입과 PostgreSQL 데이터 타입 간 매핑 지원
- Oracle 클라이언트 라이브러리(OCI) 설치 필요
- Remote 테이블과 Foreign Table 간 join 지원
- 활용 사례:
- Oracle의 데이터를 PostgreSQL로 마이그레이션
- 두 데이터베이스 간 데이터 통합 및 질의
○ postgres_fdw
- PostgreSQL 서버 간 데이터를 공유하고 통합할 수 있도록 지원하는 FDW입니다
- 주요 특징:
- PostgreSQL 서버 간 데이터 공유 및 조회 가능
- JSON, JSONB, 배열 등 PostgreSQL 고유 데이터 타입 지원
- 트랜잭션 및 JOIN 최적화 지원
- 네트워크 오버헤드가 적으며, 동일한 PostgreSQL 버전 간에 가장 높은 호환성 제공
- DML 작업 가능(트랜잭션 관리 및 데이터 일관성을 신중하게 고려해야 한다.)
- 활용 사례:
- 데이터베이스 샤딩 환경에서 여러 PostgreSQL 인스턴스 간 데이터를 통합 조회할 때 사용
- PostgreSQL의 분산 환경에서 통합 데이터 분석
- PostgreSQL 간 데이터 복제, 동기화, 분석
FDW 별 연결 방법
ㆍoracle_fdw
1. 의존성 설치
ㆍOracle Instant Client 설치
2. oracle_fdw extension 생성
postgres=# create extension oracle_fdw ;
CREATE EXTENSION
postgres=# dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+----------------------------------------
oracle_fdw | 1.2 | public | foreign data wrapper for Oracle access
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)3. Server 및 User map 생성
postgres=# CREATE SERVER oracle_server FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//oracle_host:oracle_port/oracle_service_name');
postgres=# CREATE USER MAPPING FOR USER_NAME
SERVER oracle_server
OPTIONS (user 'oracle_user', password 'oracle_password') ;4. Foreign 테이블 생성 및 마이그레이션
-- Oracle(source) DB의 HR 스키마의 JOBS 테이블 to PostgreSQL(target) DB의 public 스키마의 ft_jobs foreign table
postgres=# CREATE FOREIGN TABLE public.ft_jobs
(job_id character varying(10) not null,
job_title character varying(35),
min_salary numeric(6,0), max_salary numeric(6,0))
SERVER oracle_server
OPTIONS (SCHEMA 'HR', TABLE 'JOBS');
postgres=# SELECT * FROM pg_foreign_table;
ftrelid | ftserver | ftoptions
---------+----------+------------------------
17530 | 17528 | {schema=HR,table=JOBS}
(1 row)
postgres=# det
List of foreign tables
Schema | Table | Server
--------+--------------------+-----------------
public | ft_jobs | oracle_server
(1 rows)
postgres=# select * from public.ft_jobs ;
job_id | job_title | min_salary | max_salary
------------+---------------------------------+------------+------------
AD_PRES | President | 20080 | 40000
AD_VP | Administration Vice President | 15000 | 30000
AD_ASST | Administration Assistant | 3000 | 6000
FI_MGR | Finance Manager | 8200 | 16000
FI_ACCOUNT | Accountant | 4200 | 9000
AC_MGR | Accounting Manager | 8200 | 16000
AC_ACCOUNT | Public Accountant | 4200 | 9000
SA_MAN | Sales Manager | 10000 | 20080
SA_REP | Sales Representative | 6000 | 12008
PU_MAN | Purchasing Manager | 8000 | 15000
PU_CLERK | Purchasing Clerk | 2500 | 5500
ST_MAN | Stock Manager | 5500 | 8500
ST_CLERK | Stock Clerk | 2008 | 5000
SH_CLERK | Shipping Clerk | 2500 | 5500
IT_PROG | Programmer | 4000 | 10000
MK_MAN | Marketing Manager | 9000 | 15000
MK_REP | Marketing Representative | 4000 | 9000
HR_REP | Human Resources Representative | 4000 | 9000
PR_REP | Public Relations Representative | 4500 | 10500
(19 rows)
postgres=# dt
Did not find any relations.
-- CTAS로 마이그레이션 진행
postgres=# CREATE TABLE public.jobs AS
SELECT * FROM public.ft_jobs ;
SELECT 19
postgres=# select * from public.jobs ;
job_id | job_title | min_salary | max_salary
------------+---------------------------------+------------+------------
AD_PRES | President | 20080 | 40000
AD_VP | Administration Vice President | 15000 | 30000
AD_ASST | Administration Assistant | 3000 | 6000
FI_MGR | Finance Manager | 8200 | 16000
FI_ACCOUNT | Accountant | 4200 | 9000
AC_MGR | Accounting Manager | 8200 | 16000
AC_ACCOUNT | Public Accountant | 4200 | 9000
SA_MAN | Sales Manager | 10000 | 20080
SA_REP | Sales Representative | 6000 | 12008
PU_MAN | Purchasing Manager | 8000 | 15000
PU_CLERK | Purchasing Clerk | 2500 | 5500
ST_MAN | Stock Manager | 5500 | 8500
ST_CLERK | Stock Clerk | 2008 | 5000
SH_CLERK | Shipping Clerk | 2500 | 5500
IT_PROG | Programmer | 4000 | 10000
MK_MAN | Marketing Manager | 9000 | 15000
MK_REP | Marketing Representative | 4000 | 9000
HR_REP | Human Resources Representative | 4000 | 9000
PR_REP | Public Relations Representative | 4500 | 10500
(19 rows)ㆍpostgres_fdw
1. postgres_fdw extension 생성
postgres=# create extension postgres_fdw ;
postgres=# dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+----------------------------------------------
oracle_fdw | 1.2 | public | foreign data wrapper for Oracle access
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.1 | public | foreign-data wrapper for remote PostgreSQL servers
(3 rows)2. 서버 및 사용자 맵 생성
postgres=# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'source_postgresql_host', dbname 'source_postgresql_db', port 'source_postgresql_port');
postgres=# CREATE USER MAPPING FOR USER_NAME
SERVER postgres_server
OPTIONS (user 'source_postgres_user', password 'source_postgres_password');3. 외부 테이블 생성
-- PostgreSQL(source) DB의 public 스키마의 pgbench_tellers 테이블 to PostgreSQL(target) DB의 public 스키마의 ft_pgbench_tellers foreign table
postgres=# create foreign table public.ft_pgbench_tellers (
tid integer NOT NULL,
bid integer,
tbalance integer,
filler character(84)
) server postgres_server options(schema_name 'public', table_name 'pgbench_tellers') ;
postgres=# SELECT * FROM pg_foreign_table ;
ftrelid | ftserver | ftoptions
---------+----------+-------------------------------------------------
17530 | 17528 | {schema=HR,table=JOBS}
25719 | 25718 | {schema_name=public,table_name=pgbench_tellers}
(2 rows)
postgres=# det
List of foreign tables
Schema | Table | Server
--------+--------------------+-----------------
public | ft_jobs | oracle_server
public | ft_pgbench_tellers | postgres_server
(2 rows)
postgres=# select * from ft_pgbench_tellers ;
tid | bid | tbalance | filler
-----+-----+----------+--------
1 | 1 | 0 |
2 | 1 | 0 |
3 | 1 | 0 |
4 | 1 | 0 |
5 | 1 | 0 |
6 | 1 | 0 |
7 | 1 | 0 |
8 | 1 | 0 |
9 | 1 | 0 |
10 | 1 | 0 |
(10 rows)
-- CTAS로 마이그레이션 진행
postgres=# CREATE TABLE public.pgbench_tellers AS
SELECT * FROM public.ft_pgbench_tellers ;
SELECT 10
postgres=# select * from public.pgbench_tellers ;
tid | bid | tbalance | filler
-----+-----+----------+--------
1 | 1 | 0 |
2 | 1 | 0 |
3 | 1 | 0 |
4 | 1 | 0 |
5 | 1 | 0 |
6 | 1 | 0 |
7 | 1 | 0 |
8 | 1 | 0 |
9 | 1 | 0 |
10 | 1 | 0 |
(10 rows)Note
ㆍtibero_fdw
PostgreSQL과 Tibero 데이터베이스를 연결하는 FDW 입니다
- 주요 특징:
- Tibero의 테이블과 뷰를 PostgreSQL에서 원격 테이블처럼 조회 가능
- Oracle과 유사한 SQL 및 데이터베이스 구조 지원
- Tibero 클라이언트 설치 필요
- 사용 사례:
- Tibero 데이터를 PostgreSQL로 통합
- 하이브리드 데이터베이스 환경 구성
- Tibero에서 PostgreSQL로 마이그레이션
- 연결 방법
1. 의존성 설치
- tibero_fdw 소스 빌드 필요
2. tibero_fdw extension 생성
postgres=# create extension tibero_fdw ;
CREATE EXTENSION
postgres=# dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+----------------------------------------------
oracle_fdw | 1.2 | public | foreign data wrapper for Oracle access
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.1 | public | foreign-data wrapper for remote PostgreSQL servers
tibero_fdw | 1.0 | public | foregin data wrapper for Tibero access
(4 rows)
3. 서버 및 사용자 맵 생성
postgres=# CREATE SERVER tibero_server FOREIGN DATA WRAPPER tibero_fdw
OPTIONS (host 'tibero_host', port '8629', dbname 'tibero_db');
postgres=# CREATE USER MAPPING FOR CURRENT_USER
SERVER tibero_server
OPTIONS (username 'tibero_user', password 'tibero_password');
4. 외부 테이블 생성
-- tibero(source) DB의 TESTU 스키마의 SALES 테이블 to PostgreSQL(target) DB의 public 스키마의 ft_sales foreign table
postgres=# CREATE FOREIGN TABLE public.ft_sales (
sales_no int,
sale_year int,
sale_month int,
sale_day int,
customer_name varchar(30),
birth_date date,
price int,
state varchar(2)
) SERVER tibero_server OPTIONS (table_name 'SALES', owner_name 'TESTU') ;
postgres=# SELECT * FROM pg_foreign_table;
ftrelid | ftserver | ftoptions
---------+----------+-------------------------------------------------
17530 | 17528 | {schema=HR,table=JOBS}
25724 | 25722 | {schema_name=public,table_name=pgbench_tellers}
25763 | 25754 | {table_name=SALES,owner_name=TESTU}
(3 rows)
postgres=# det
List of foreign tables
Schema | Table | Server
--------+--------------------+-----------------
public | ft_jobs | oracle_server
public | ft_pgbench_tellers | postgres_server
public | ft_sales | tibero_server
(3 rows)
postgres=# select * from ft_sales ;
sales_no | sale_year | sale_month | sale_day | customer_name | birth_date | price | state
----------+-----------+------------+----------+---------------+------------+-------+-------
1 | 2004 | 5 | 2 | Sophia | 1974-05-02 | 65000 | WA
2 | 2005 | 3 | 2 | Emily | 1975-03-02 | 23000 | OR
4 | 2007 | 2 | 2 | Amelia | 1977-02-02 | 12000 | CA
3 | 2006 | 8 | 2 | Olivia | 1976-08-02 | 34000 | TX
(4 rows)
-- CTAS로 마이그레이션 진행
postgres=# CREATE TABLE public.sales AS
SELECT * FROM public.ft_sales ;
SELECT 4
postgres=# select * from sales ;
sales_no | sale_year | sale_month | sale_day | customer_name | birth_date | price | state
----------+-----------+------------+----------+---------------+------------+-------+-------
1 | 2004 | 5 | 2 | Sophia | 1974-05-02 | 65000 | WA
2 | 2005 | 3 | 2 | Emily | 1975-03-02 | 23000 | OR
4 | 2007 | 2 | 2 | Amelia | 1977-02-02 | 12000 | CA
3 | 2006 | 8 | 2 | Olivia | 1976-08-02 | 34000 | TX
(4 rows)
※ FDW 주의사항
1. Foreign table과 Local table 간의 조인(join)은 피하는 것이 좋다.
- 성능 저하를 방지하기 위해 가능한 모든 조인을 Foreign Table 간에만 수행하는 것이 권장됩니다.
2. 작업 완료 후 Foreign Table과 postgres_fdw 설정 제거
작업 완료 후 Foreign Table과 FDW 설정을 제거하여 시스템 자원을 관리하고 보안을 강화해야 합니다.
- 시스템 자원 관리
Foreign Table과 FDW 설정은 연결된 원격 데이터베이스와의 통신을 위해 PostgreSQL 서버의 리소스를 소모합니다. 마이그레이션이 완료된 후에도 이를 유지하면 불필요한 자원 소모로 이어질 수 있습니다. - 보안 강화
마이그레이션 과정에서 설정된 FDW는 원격 데이터베이스로의 접근 권한을 유지합니다. 마이그레이션 완료 후 이를 방치하면 의도치 않은 접근 또는 데이터 누출 위험이 발생할 수 있습니다. - 시스템 복잡성 감소
Foreign Table과 FDW는 유지 보수 시 혼란을 초래할 수 있습니다. 특히, 불필요한 설정이 남아 있으면 시스템 구조가 복잡해지고, 의도치 않은 쿼리가 원격 데이터베이스를 참조하는 등의 문제가 발생할 수 있습니다. - 추적 가능성 개선
마이그레이션 후에 Foreign Table과 FDW를 삭제하면, 이후의 데이터 참조가 로컬 데이터베이스로 제한되어 문제가 발생했을 때 원인을 추적하기 쉽습니다.
FDW는 강력한 기능이지만, 네트워크 성능, 원격 데이터베이스의 부하, 그리고 보안 문제를 충분히 고려해야 합니다.
특히, Foreign Table을 통한 조인은 신중하게 사용하고, 마이그레이션이 끝난 후에는 불필요한 FDW 설정을 정리하는 것이 좋습니다.
Reference
ㆍhttps://www.postgresql.org/docs/15/postgres-fdw.html
ㆍhttps://tmaxtibero.blog/4562/
ㆍhttps://tmaxtibero.blog/4280/
