[PostgreSQL] FDW로 Cross Database 해결하기

문제 상황

• PostgreSQL 환경에서 다른 Database 내에 있는 DB Function을 호출해야 함
• JPA에서는 서로 다른 Database에 접근하려면 Cross Database 이슈 발생
• DBConfig를 통해 해결할 수 있지만, 코드 추가 없이 해결 하고 싶었음

FDW를 통해 문제를 해결했다. 그러나 DB Function을 원격으로 호출하는 방법은 아니었다. 해당 DB Function이 접근하는 테이블에 대한 외래 테이블을 만들고, Function 소스 코드를 복사해와, 테이블명을 바꿨다.

운영 환경

하나의 PostgreSQL RDS에 여러 Database를 두고 있음

서비스 특성

• B2B로, 제한된 사용자만 사용(입력이 정직함)
• 두 DB 모두 동시성 이슈는 거의 발생하지 않음
• 트래픽이 일정하고, 쓰는 시간대가 한정돼있음
• DB Function 응답 속도는 매우 빠름

테스트 환경 구성

이 Function이 쓰기 작업을 하지 않지만, SQL을 실행하는 방식이어서 신중해야 했다.

먼저 개인 docker를 사용해 검증했는데, 생각보다 localhost는 봐야 할 곳이 하나 더 있어서 불편했다.

준비방법은 크게 4가지다.

1. CREATE EXTENSION 으로 postgres_fdw 확장 프로그램 설치
2. CREATE SERVER를 통해 원격 DB 정보가 있는 외래 서버 객체를 만든다. 옵션으로 연결 정보 또한 넣는다.
3. 외부 서버 접근을 허용하는 사용자 객체를 mapping 한다. 옵션으로 사용자 이름, 비밀번호를 입력한다.
4. CREATE FOREIGN TABLE 또는 IMPORT FOREIGN TABLE를 사용해 액세스하려는 원격 테이블에 대한 외래 테이블(FOREIGN TABLE)을 만든다.

postgres 컨테이너 만들기

docker run --name fdw_test_container 
-e POSTGRES_USER=fdw_user 
-e POSTGRES_PASSWORD=postgres 
-e POSTGRES_DB=databaseA 
-p 15434:5432 -d postgres

편의상 DB 명을 다음과 같이 한다.

databaseA: DB Function이 있는 DB

databaesB: DB Function을 쓰고 싶은 DB

databaseA에 사용된 SQL이다.

-- 원본이 될 스키마 생성
CREATE SCHEMA origin_schema AUTHORIZATION fdw_user;

-- 원본이 될 테이블 생성
CREATE TABLE origin_schema.origin_table (
id int4 NOT NULL,
title varchar NULL,
CONSTRAINT origin_pk PRIMARY KEY (id)
);

-- 데이터 삽입
INSERT INTO origin_schema.origin_table
(id, title)
VALUES(1, 'title1');

INSERT INTO origin_schema.origin_table
(id, title)
VALUES(2, 'title2');

INSERT INTO origin_schema.origin_table
(id, title)
VALUES(3, 'title3');

-- DB Function 생성
CREATE OR REPLACE FUNCTION origin_schema.get_origin_function()
RETURNS TABLE(id int4, title varchar) AS $$
BEGIN
    RETURN QUERY SELECT o.id, o.title FROM origin_schema.origin_table AS o;
END;
$$ LANGUAGE plpgsql;

-- DB Function 테스트
SELECT * FROM origin_schema.get_origin_function();

databaseB에 사용된 SQL이다.

fdw 확장 프로그램을 설치해야 하고 Foreign 이란 키워드도 붙는다.

-- DB Function을 호출할 DB 생성
CREATE DATABASE databaseB;

-- 스키마 생성(fdw_schema)
CREATE SCHEMA fdw_schema AUTHORIZATION fdw_user;

-- fdw 확장 프로그램 설치
CREATE EXTENSION postgres_fdw;

-- remote_postgres_server란 서버 정의 
-- 주의! host가 127.0.0.1 이기 때문에 도커 환경이면
-- 외부 포트인 15434가 아니라 내부 포트 5434로 설정한다.
-- 만약, AWS RDS 같이 외부로 접근하는 거면 포트를 동일하게 한다.
create server remote_postgres_server
foreign data wrapper postgres_fdw 
options (host '127.0.0.1', port '5432' , dbname 'databaseA');

-- [DEBUG] 서버가 정의됐는지 확인용
select
    srvname as name,
    srvowner::regrole as owner,
    fdwname as wrapper,
    srvoptions as options
from pg_foreign_server
         join pg_foreign_data_wrapper w on w.oid = srvfdw;
         
-- 접근할 서버의 계정 정보 입력
-- 현재 하나의 postgreSQL내 다른 DB에 접근하므로 위에 docker container 생성에 사용된 정보 입력
create user mapping for fdw_user
server remote_postgres_server options (user 'fdw_user', password 'postgres');

-- [DEBUG] 매핑됐는지 조회
SELECT * FROM pg_user_mappings;

-- 원격 테이블 생성
-- CREATE 절에선 databaseB의 기준에 맞춰 스키마와 테이블 입력
-- OPTIONS 절에선 실제 원본이 되는 databaeA의 스키마와 테이블 입력
CREATE FOREIGN TABLE fdw_schema.fdw_table (
    id int4 NOT NULL,
    title varchar NULL
)
SERVER remote_postgres_server
OPTIONS (schema_name 'origin_schema', table_name 'origin_table');

-- databaseA의 Function을 복붙하고 스키마, 테이블명만 바꾼 거다.
CREATE OR REPLACE FUNCTION fdw_schema.get_fdw_function()
RETURNS TABLE(id int4, title varchar) AS $$
BEGIN
    RETURN QUERY SELECT o.id, o.title FROM fdw_schema.fdw_table AS o;
END;
$$ LANGUAGE plpgsql;

-- [DEBUG] Function이 외래 테이블을 잘 읽어서 실행하는지 테스트
SELECT * FROM fdw_schema.get_fdw_function();

외래 테이블을 만들면서 알게된 점

INDEX를 만들 필요는 없었다. 추측으론 외래 테이블은 포인터 같은 개념이라 실제 실행은 원본 테이블 쪽에서 실행하는 것 같다.

외래 테이블의 열 이름과 타입은 원본 테이블과 일치해야 한다. 만약, 열 이름을 다르게 하고 싶다면, 옵션을 통해 원본 테이블의 어떤 열과 매칭되는지 명시해야 한다.

마치면서

처음에 FDW를 쓰란 지시를 받았을 땐, 서로 다른 datasource 에서만 써야 하는 거 아닌가? 란 생각을 했는데, 같은 datasource 내에서도 될 줄 몰랐다.

찾아보니 dblink란 게 있었지만, 9.x 대 버전 이전에 주로 사용된 방법이었던 거 같아서 건너뛰었다.

docs를 보니 연결, 트랜잭션 관리에 대해서도 있었지만 관련 경험을 하지 못해, 제대로 와닿지는 않았다. 이런 쪽에서 문제가 생긴다면 다시 찾아보고 적용해야겠다.

Reference

https://www.postgresql.org/docs/current/postgres-fdw.html

F.36. postgres_fdw — access data stored in external PostgreSQL servers

 

https://velog.io/@dailylifecoding/postgresql-how-to-create-foreign-table-with-data-wrapper

[PostgreSQL] Foreign Data Wrapper 를 사용한 Foreign Table 생성법