[PostgreSQL] postgres_fdw 확장 / 외부 테이블 사용

🧸 postgres_fdw

foreign-data wrapper for remote PostgreSQL servers

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

 

1. postgres_fdw 확장을 설치합니다.

CREATE EXTENSION postgres_fdw;

 

2. CREATE SERVER로 외부 데이터 서버를 생성합니다.

CREATE SERVER {1}
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '{2}', port '{3}', dbname '{4}');

  {1}: 앞으로 원격 db를 사용할 server name (사용자 임의로 입력가능)
  {2}: 연결할 원격디비의 주소
  {3}: 연결할 원격디비의 포트
  {4}: 연결할 원격디비의 데이터베이스명

 

CREATE SERVER test_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '127.0.0.1', port '5432', dbname 'test');

 

3. 원격 서버에 사용할 역할 식별을 위하여 사용자 매핑을 생성합니다.

CREATE USER MAPPING FOR {1}
SERVER {2}
    OPTIONS (user '{3}', password '{4}');

  {1}: 원격 서버와 맵핑시킬 현재 데이터베이스 내에 존재하는 user name (ex, postgres)
  {2}: 위에서 정한 원격 db server name
  {3}: 원격 DB의 user name
  {4}: 원격 DB user의 password

 

CREATE USER MAPPING FOR postgres
SERVER test_server
OPTIONS (user 'postgres', password 'postgres');

 

4. 원격 서버의 테이블과 매핑할 테이블을 생성하거나, IMPORT하여 Foreign Tables을 생성합니다. (택1)

   

    1) 원격 서버의 테이블과 매핑할 테이블을 생성

CREATE FOREIGN TABLE user_profile (
user_id character varying(100) COLLATE pg_catalog."default" NOT NULL,
user_name character varying(80) COLLATE pg_catalog."default" NOT NULL,
user_name_eng character varying(100) COLLATE pg_catalog."default",
email character varying(600) COLLATE pg_catalog."default",
datetime_last_loggedin timestamp with time zone
)
SERVER test_server
OPTIONS (schema_name 'public', table_name 'user_profile');

 

    2) IMPORT하여 Foreign Table을 생성

-- DROP FOREIGN TABLE IF EXISTS test_schema .user_profile;
IMPORT FOREIGN SCHEMA public LIMIT TO (user_profile)
FROM SERVER test_server INTO test_schema;

 

---

운영DB(AWS)에서 다른 데이터베이스와 join하기 위해 사용해보았습니다.

 

간단하게 정리하면 이렇습니다.

1. postgres-fdw를 이용하여 Extensions Server서버 생성

2. 외부 데이터를 받을 Foreign Servers를 생성

3. 각 Foreign Servers를 user와 매핑

4. 외부 데이터를 받을 테이블 생성

5. 위 작업 완료 후 맘껏 join가능!

 

postgresql에서 매우 간편하게 제공하고 있어서 사용하기 편리했습니다. 👍

 

 

참고
https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.foreign-data-wrappers.html#postgresql-commondbatasks-fdw
https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-EXAMPLES
https://brownbears.tistory.com/99