[PostgreSQL]다른 db 조회할 수 있는 Extension - 'postgres_fdw'

머릿말

PostgreSQL에서 쿼리를 날리다가 다른 DBMS처럼 외부 DB 테이블과 조인을 해야할 때, 아래와 같은 오류를 보신적 있으신가요?

SQL Error [0A000]: ERROR: cross-database references are not implemented: "테이블명"

snowflake에서 PostgreSQL로 마이그레이션 작업을 하던 도중, 같은 서버 DBMS 내 다른 DB를 조회할 때 제약이 걸려 있다는 것을 확인했습니다.
타 DB접근하기 위해 설정 방법을 글로 정리해야겠다고 생각했습니다.

---

PostgreSQL 다른 DB 조회 정책

• PostgreSQL 서버에 한 클라이언트가 접속할 때, 반드시 연결할 DB를 지정해야 합니다.
• 하나의 연결(한 클라이언트가 접속할 때)로 여러 DB를 접속할 수 없습니다.
• PostgreSQL에선 타 RDBMS처럼 서로 다른 DB의 테이블 끼리 조인하면 아래와 같은 에러 발생
• 출처: https://postgresql.kr/docs/13/manage-ag-overview.html

---

다른 DB 연결하기 위한 방법

PostgreSQL에서 2가지 확장을 지원하는데 'dblink'와 'postgres_fdw' 입니다.

• 속도적인 측면에선 dblink보다 postgres_fdw가 우월합니다.
• 기능은 비슷하나 dblink보다 postgers_fdw 확장이 최근에 나와서 postgres_fdw 쓰는게 좋다고 보시면 됩니다
• 같은 PostgreSQL뿐만 아니라 타 RDBMS 테이블도 외부 테이블로 생성 가능해서 활용성이 좋습니다.
  -> 설치해야하는 확장명이 달라짐

---

 

postgres_fdw 사용하여 foreign table 생성

-- 1) CREATE EXTENSION: postgres_fdw 확장 설치
--pg<->pg간의 참조
CREATE EXTENSION IF NOT EXISTS postgres_fdw SCHEMA {schema_name} ;   --확장을 설치할 pg스키마명


--pg<->ms간의 참조
CREATE EXTENSION IF NOT EXISTS tds_fdw SCHEMA {schema_name} ;        --확장을 설치할 pg스키마명


-- 2) CREATE FOREIGN SERVER: 다른 데이터베이스에 대한 fdw서버 생성
CREATE SERVER {foreign_server_name} FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (host '{host/ip}', dbname '{database_name}', port '{port}');    --연결할 서버정보 입력


-- 3) CREATE FOREIGN TABLE: 로컬 데이터베이스에서 외부 테이블 생성 및 서버 연결
CREATE FOREIGN TABLE schema.foreign_pg_table (    
    column1 varchar(10) NOT NULL,    
    column2 date NOT NULL,
    column3    int,
    column4 OPTIONS (column_name 'foreign_column4') NOT NULL        -- 컬럼명에 옵션을 줘서 컬럼명을 변경하여 원격테이블 생성 가능
) SERVER {remote_server}
OPTIONS (table_name '{remote_table}'); 


--4) CREATE USER MAPPING: fdw 서버와 유저 매핑
--postgres_fdw를 사용하기 위해서는 extension 확장하는 User에게 superuser 권한이 있어야 함
CREATE USER MAPPING FOR "{user_name}"    --non-superuser_name
    SERVER {foreign_server_name}            --fdw 서버 생성 시 설정했던 foreign_server_name 기입
OPTIONS (password_required 'false');    -- 매핑하는 유저가 superusers가 아니라면 password_required 옵션을 'false'처리 해줘야 함

role에 postgres_fdw 관련 권한 부여

-- 1) CREATE ROLE
CREATE USER {role_name};


--2) CREATE USER MAPPING
GRANT ALL PRIVILEGES ON FOREIGN DATA WRAPPER postgres_fdw TO {role_name} WITH GRANT OPTION;


--3) 생성한 원격 테이블에 사용할 유저로 owner 변경
ALTER FOREIGN TABLE schema.foreign_pg_table
    OWNER TO {role_name};

foreign schema 생성

-- 연결할 스키마에 모든 테이블, 뷰 그대로 생성하는 쿼리문
IMPORT FOREIGN SCHEMA {foreign_schema_name}
FROM SERVER {foreign_server_name} INTO {local_schema_name};


-- 특정 테이블만 생성
IMPORT FOREIGN SCHEMA {foreign_schema_name} 
LIMIT TO ({foreign_table1}, {foreign_table2}, ...)
FROM SERVER {foreign_server_name} INTO {local_schema_name};


-- 특정 테이블 제외하고 생성
IMPORT FOREIGN SCHEMA {foreign_schema_name} 
EXCLUDE ({foreign_table1}, {foreign_table2}, ...)
FROM SERVER {foreign_server_name} INTO {local_schema_name};