PostgreSQL - FDW이용 외부 테이블 사용

김무성·2022년 5월 2일

PostgreSQL

SQL

목록 보기

6/7

사용 필요 시점

Foreign table은 외부DB에 있는 data를 실시간으로 연계가 필요시 사용
Foreign table은 FDW(foreign-data wrapper) 방식을 이용하며, DBLink 보다 향상된 성능 보장

장점

생성 이후 Local에 생성된 Table과 동일하게 사용 가능하며 local table과 다양한 join query가 가능함
DBLink로 연계시 여러 단계(Connect, Close, ..)를 fdw에서 처리해 주어 안정성 있으며, 간단하게 사용 가능함

Postgres_fdw Extension 추가

CREATE EXTENSION IF NOT EXISTS postgres_fdw
SCHEMA public;

foreign server object 생성

target db에 연결 할 수 있는 정보로 foreign 서버를 생성함
foreign 서버명은 local db에 foreign table 생성시 사용 됨

CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS(
  host 'xxx.xxx.x.xx',
  port 'xxxx',
  dbname 'foreign_db'
);

user mapping 생성

foreign 서버에 접근하는 local db의 사용자(public)와 target db인 foreign 서버 사용자의 로그인 정보 ( options 에 설정된 정보 ) 를 설정하여 local db와 target db 사용자 정보를 매핑함

CREATE USER MAPPING
FOR public
SERVER foreign_server
OPTIONS(
  user 'foreign_db_usr',
  password 'foreign_db_pwd'
);

foreign table 생성 Script

foreign table의 column은 comment를 생성할 수 없으니 table 생성 script를 따로 관리하는 것이 좋음

컬럼명 동일하게 foreign table 생성 Script

/* 컬럼명이 local과 remote가 동일할 때 생성방법 */

DROP FOREIGN TABLE foreign_copy_schema.foreign_copy_tbl;

CREATE FOREIGN TABLE IF NOT EXISTS foreign_copy_schema.foreign_copy_tbl (
    xxxx_id              VARCHAR(10) NOT NULL,  /* xxxxID */
    plant_cd             VARCHAR(4) ,           /* 공장코드 */
    mtrl_cd              VARCHAR(18) ,          /* 자재코드 */
    prd_dttm             TIMESTAMP(0) ,         /* 생산일시 */
    xxx_val              NUMERIC ,              /* xxx값 */
    yyy_val              NUMERIC ,              /* yyy값 */
    issu_dt              DATE                   /* 발급일자 */
)
SERVER foreign_server
OPTIONS (
    schema_name 'foreign_schema',
    table_name 'foreign_tbl'
    use_remote_estimate 'true'
);

ALTER FOREIGN TABLE foreign_copy_schema.foreign_copy_tbl
    OWNER TO xxxxro;

COMMENT ON FOREIGN TABLE foreign_copy_schema.foreign_copy_tbl IS 'xxxx성적서';

컬럼명을 다르게 foreign table 생성 Script

/* 컬럼명을 local과 remote가 다르게 설정할 때 생성방법 */

DROP FOREIGN TABLE foreign_copy_schema.foreign_copy_tbl;

CREATE FOREIGN TABLE foreign_copy_schema.foreign_copy_tbl(
    plant_cd    varchar(4) NOT NULL,
    in_dt       date  NOT NULL,
    in_seq      smallint NOT NULL,
    lab_dt      date ,
    lab_tm      varchar(4) ,
    insp_itm_cd varchar(7) OPTIONS (column_name 'raw_mtrl_insp_itm_cd') NOT NULL,
    insp_itm_val numeric OPTIONS (column_name 'raw_mtrl_insp_itm_val') ,
    reg_user_id varchar(20) NOT NULL,
    reg_dttm    timestamp 
)
SERVER foreign_server
OPTIONS (
    schema_name 'foreign_schema'
    table_name 'foreign_tbl'
);

ALTER FOREIGN TABLE foreign_copy_schema.foreign_copy_tbl
    OWNER TO xxxro;

foreign server schema 그대로 생성

foreign server에 정의된 모든 table / view에 대해 정의된 내용 그대로 생성하고자 할때 사용

/* foreign_schema에 정의된 모든 table / view에 대해 foreign table을 public schema에 생성함 */
IMPORT FOREIGN SCHEMA foreign_schema
FROM SERVER foreign_server INTO public;

특정 table만 골라서 생성하는 것 가능함

/* foreign_schema에 정의된 table list에 대해 foreign table을 public schema에 생성함 */
IMPORT FOREIGN SCHEMA froeign_schema LIMIT TO(foreign_table1, foreign_table2, ...)
FROM SERVER foreign_server INTO public;

특정 table만 제외하고 생성하기 가능함

/* foreign_schema에 정의된 table list를 제외하고 foreign table을 public schema에 생성함 */
IMPORT FOREIGN SCHEMA foreign_schema EXCLUDE(foreign_table1, foreign_table2, ...)
FROM SERVER foreign_server INTO public;