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

식빵·2023년 6월 1일

postgresql-memo

목록 보기

27/34

외부 데이터 래퍼(Foreign Data Wrapper) 는 외부 소스에 저장된 데이터에 접근하는
외부 테이블(Foreign Table) 을 생성할 수 있습니다. 이런 Foreign Table 을 생성한 후에
쿼리를 실행하면, 실제로 외부 소스에서 쿼리가 실행되고 필요하다면 결과 또한 반환받습니다.
지금부터 Foreign Data Wrapper 를 통한 Foreign Table 을 생성하는 방법을 알아보겠습니다.

0. Postgresql Docker Container

이 목차는 필수가 아닙니다. 순수 사용법만 알고 싶으신 분들은 그냥
1번 목차부터 보시면 됩니다!

이 게시물에서는 2개의 Postgresql Docker Container 를 생성하고,
하나의 컨테이너에서 다른 컨테이너의 Table 정보를 읽는 Data Wrapper 를 생성하는
방식으로 글을 작성할 겁니다.

그래서 아래와 같이 2개의 postgresql container 와 1개의 docker network 를
생성하고, 해당 네트워크에 2개의 컨테이너를 연결하였습니다.

## 로컬 DB 서버 역할을 해줄 Container 생성
docker run --name MyPostgresKo -p 10011:5432 -d -e POSTGRES_PASSWORD=postgres postgres

## 원격 DB 서버 역할을 해줄 Container 생성
docker run --name RemotePostgresKo -p 10012:5432 -d -e POSTGRES_PASSWORD=postgres postgres

## docker network (DRIVER=bridge as default) 생성
docker create network my_postgres_network

## docker network 에 container 연결하기
docker network connect my_postgres_network MyPostgresKo
docker network connect my_postgres_network RemotePostgresKo


## docker container 에 할당된 IP 확인! 중요!
## 참고: 결과 출력에서 다수의 내용을 생략했습니다. 너무 깁니다.
docker network inspect my_postgres_network
[
    {
        "Name": "my_postgres_network",
        "Scope": "local",
        "Driver": "bridge",
        "Containers": {
            "588eb5cbe0594b9f3ec29b10a51d20f4d1f47f73a7185a0cbc244e4c63e3c8d3": {
                "Name": "MyPostgresKo",
                "IPv4Address": "172.18.0.3/16",
            },
            "c5f560bf23c47b1dc409fb234c361f5e79ef176041bc8119458d606987aa2191": {
                "Name": "RemotePostgresKo",
                "IPv4Address": "172.18.0.2/16",
            }
        }
    }
]

생성한 컨테이너 : MyPostgresKo, RemotePostgresKo
각 컨테이너의 docker network (my_postgres_network) 내에서 할당된 IP
- MyPostgresKo : 172.18.0.3
- RemotePostgresKo : 172.18.0.2

지금부터 MyPostgresKo 컨테이너 내의 postgresql 에서 Data Wrapper 를 생성하고,
이 Data Wrapper 를 통해서 외부 DB 서버인 RemotePostgresKo 컨테이너 내에 있는
테이블 정보를 조회해보겠습니다.

이후부터 작성되는 쿼리들은 모두 MyPostgresKo (IP = 172.18.0.3 ) 컨테이너에
설치된 postgresql 에서 실행하는 것입니다!

1. Extension 설치

CREATE EXTENSION postgres_fdw;

# 설치되었는지 확인하고 싶다면...? 
# select * from pg_extension

2. 연결할 외부 DB 서버 생성

create server remote_postgres_server
foreign data wrapper postgres_fdw
options (host '172.18.0.2', port '5432' , dbname 'postgres');
-- 원격 서버 (현재 RemotePostgresKo 컨테이너) 의 IP 를 host 에 정확히 기입!

참고: postgres_fdw
- postgres_fdw Extension 을 설치하면 깔리는 built-in data wrapper 입니다.
- PostgreSQL servers foreign tables 생성 및 접근을 위한 것입니다.

먼저 외부 DB Server 생성 여부를 먼저 확인하고 싶다면 아래 쿼리를 먼저 돌려주세요.
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;
참고: https://stackoverflow.com/questions/44170038/how-to-list-all-foreign-servers

3. 외부 DB 접속 인증정보 매핑

외부 DB 서버에 접속하려면 당연히 user, password 정보가 필요하겠죠?
이러한 정보를 담기 위해서 특정 role(user) 에 그 정보를 매핑해줘야 합니다.

create user mapping for postgres
server remote_postgres_server options (user 'postgres', password 'postgres');

저는 super user 인 postgres 에 매핑하도록 하겠습니다.
options 에서 원격 DB 서버에 접속하는 인증정보를 기입합니다.

매핑 정보 생성 여부를 먼저 확인하고 싶다면 아래 쿼리를 돌려보시기 바랍니다.
select
       srvname,
       usename,
       umoptions
from pg_user_mappings;

4. foreign table 생성

create foreign table remote_users
(
    user_id integer not null,
    name varchar(100) not null,
    age smallint not null
)
server remote_postgres_server
options (schema_name 'public', table_name 'users');

-- 조회해서 문제없으면 성공입니다.
select * from remote_users;

(원격 DB 서버에는 public.users 라는 테이블이 이미 생성되어 있다고 가정합니다)

참고

만약에 원격 DB Table 과 다른 이름의 column 명칭을 주고 싶다면 아래처럼 하면 됩니다.

create foreign table remote_users
(
    user_id integer not null,
    name varchar(100) not null,
    user_age smallint not null options (column_name 'age')
)
server remote_postgres_server
options (schema_name 'public', table_name 'users');

보충: schema 통으로 Import 받기

-- import 받을 스키마를 미리 생성
CREATE SCHEMA schema_name;

-- import 실행!
IMPORT FOREIGN SCHEMA external_schema
FROM SERVER remote_postgres_server
INTO schema_name;