[PostgreSQL] postgres_fdw

※ postgres_fdw

   - dblink와 마찬가지로 물리적으로 떨어져 있는 원격 테이블에 접속하기 위해 사용.

   - foreign table 생성으로 target db에 해당 테이블이 존재하는 것처럼 사용 가능.

     (dblink는 select문에 dblink정보를 줘야함)

   - dblink를 사용하기 위해서는 주체가 되는 유저가 superuser권한이 있어햐 하지만 postgres_fdw는 없어도 가능

 

1. 설치

  - CREATE EXTENSION postgres_fdw;

 

  ※ CREATE EXTENSION [IF NOT EXITST] postgres_fdw

      [WITH] [ SCHEMA schema_name ]

      [VERSION version]

      [FROM old_version]

 

  - 스키마 지정하지 않으면 default인 public에 생성됨

 

 

2. CREATE SERVER

  - CREATE SERVER SERVER_NAME

     FOREIGN DATA WRAPPER postgres_fdw

     OPTIONS ( host '원격db주소', port '원격db_port', dbname '원격 db_name');

 

  ※ CREATE SERVER server_name 

      [ TYPE 'server_type'] [ VERSION 'server_version']

      FOREIGN DATA WRAPPER fdw_name

      [ OPTIONS (options 'value' [, ...])]

 

 

3. CREATE USER MAPPING

  - CREATE USER MAPPING FOR user_name(target db의 user)

     SERVER server_name

     OPTIONS ( user '원격db_username', password '원격db_password');

 

  ※ CREATE USER MAPPING FOR {user_name | USER | CURRENT_USER | PUBLIC}

      SERVER server_name

      [ OPTIONS (option 'value' [, ...])]

 

  ※ 만약 하나의 스키마를 2개의 user가 사용하는 경우 USER MAPPING이 생성된 유저만 FORIEGN TABLE 조회가 가능

     여러개의 유저가 보기 위해서는 

 

4. CREATE FOREIGN TABLE

  - CREATE FOREIGN TABLE table_name(

      test_id int,

      test_name varchar,

      ...

    )

    SERVER server_name

    OPTIONS (schema_name '원격db_schema', table_name '원격db_table_name');

 

  ※ CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [

       column1  datatype [ OPTIONS (options 'value' [, ...])] [COLLATE collation] [column_constraint [...]]

       [, ...]

      ])

      SERVER server_name

      [OPTIONS (option 'value' [, ...])]

    WHERE column_constraint is :

     [ CONSTRAINT constraint_name]

     { NOT NULL |

       NULL |

       DEFAULT default_expr }

 

5. 데이터 조회

  - SELECT * fROM foreign_table_name;

 

 

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------

POSTGRES_FDW 예시

 

 

1. test용 user 생성 (superuser 계정으로 실행)

  - create user link_test password 'link_test';

 

2. source, target db 생성 (superuser 계정으로 실행)

 target db에서 source db의 테이블 조회하려고 함

  - create database source owner to link_test;

  - create database target owner to link_test;

 

3. link test용 table 생성 (source db에서 실행)

  - create table tb_link(test_id int, test_name varchar(50));

  - insert into tb_link values(1, 'test1'), (2, 'test2);

 

4. dblink extension 생성 (target db에서 실행)

  - create extension postgres_fdw;

  - \dx로 생성된 extension 확인

 

 

5. server 생성

  - create server svr_source foreign data wrapper postgres_fdw options
    (
host 'localhost', port '5432', dbname 'source'
);

 

6. user mapping 생성

  - create user mapping for us_source server svr_source options(user 'us_source', password 'us_source');

 

7.  foreign table 생성

 

 

8. foreign table 조회

  - select * From s_link_test; 로 조회 가능

 

 

 

※ foreign table 생성까지 완료되었는데 데이터 조회시 쿼리 실행이완료되지 않고 대기중이라면 방화벽 확인 필요