명령어/DB

[PostgreSQL] 흩어진 데이터를 SQL 하나로 — postgres_fdw + dblink + file_fdw

jykim23 2026. 7. 21. 21:54
반응형

설치·접속: PostgreSQL 설치와 접속

부제: 결제 서비스는 별도 DB, 회원 정보는 우리 DB, 장애 흔적은 서버 CSV 로그에 있는데 "유저별 결제 리포트"를 한 판에 뽑아야 할 때

주문/결제는 다른 서버의 PostgreSQL(orders DB)에 있고, 회원(users)은 지금 이 shop DB에 있다. 게다가 "그 시간에 에러가 몇 건 났나"는 서버 로그(CSV) 안에 있다. 데이터를 한 곳으로 ETL 하기 전에, 세 소스를 한 SELECT로 붙여서 먼저 보고 싶다. FDW 계열 세 형제 — postgres_fdw(원격 테이블), dblink(애드혹 원격 SQL), file_fdw(파일) — 를 순서대로 얹는다.

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE EXTENSION IF NOT EXISTS dblink;
CREATE EXTENSION IF NOT EXISTS file_fdw;

1단계 — postgres_fdw: 원격 DB 테이블을 로컬 테이블처럼

원격 orders DB에는 remote_orders(id, user_id, amount, status, ordered_at) 6건이 들어 있다. 서버 등록 → 유저 매핑 → foreign table 3단계면 로컬 테이블처럼 SELECT 된다.

CREATE SERVER fs FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '127.0.0.1', port '5433', dbname 'orders', use_remote_estimate 'true');

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

CREATE FOREIGN TABLE remote_orders (
  id int, user_id int, amount numeric(10,2), status text, ordered_at timestamptz
) SERVER fs OPTIONS (table_name 'remote_orders');

SELECT id, user_id, amount, status FROM remote_orders ORDER BY id;
 id | user_id | amount |  status
----+---------+--------+----------
  1 |       3 | 120.00 | paid
  2 |       3 |  59.90 | refunded
  3 |       7 | 340.50 | paid
  4 |      12 |  88.00 | paid
  5 |      25 | 512.75 | pending
  6 |       7 |  15.00 | paid
(6 rows)

원격 6건이 로컬 테이블처럼 그대로 조회된다. IMPORT FOREIGN SCHEMA public FROM SERVER fs INTO ... 로 컬럼 정의를 자동 생성해도 되지만, 테이블 하나면 CREATE FOREIGN TABLE이 더 명시적이다.

2단계 — 원격 orders를 로컬 users와 조인

여기서부터가 핵심이다. 원격 결제(remote_orders)와 로컬 회원(users)을 user_id로 붙여 "유저별 결제 합계"를 만든다. 서비스 경계를 넘는 조인이 SQL 한 줄이다.

SELECT u.id, u.name, u.email,
       count(*)      AS orders,
       sum(r.amount) AS spent
FROM remote_orders r
JOIN users u ON u.id = r.user_id
WHERE r.status = 'paid'
GROUP BY u.id, u.name, u.email
ORDER BY spent DESC;
 id |  name  |   email    | orders | spent
----+--------+------------+--------+--------
  7 | user7  | u7@ex.com  |      2 | 355.50
  3 | user3  | u3@ex.com  |      1 | 120.00
 12 | user12 | u12@ex.com |      1 |  88.00
(3 rows)

use_remote_estimate 'true' 를 켜두면 플래너가 원격 EXPLAIN 비용을 받아 조건을 원격으로 푸시다운한다. 실제 어떤 SQL이 원격에 나가는지 EXPLAIN으로 확인한다.

EXPLAIN (VERBOSE, COSTS OFF)
SELECT r.user_id, sum(r.amount)
FROM remote_orders r
WHERE r.status = 'paid'
GROUP BY r.user_id;
 GroupAggregate
   Output: user_id, sum(amount)
   Group Key: r.user_id
   ->  Foreign Scan on public.remote_orders r
         Output: id, user_id, amount, status, ordered_at
         Remote SQL: SELECT user_id, amount FROM public.remote_orders WHERE ((status = 'paid')) ORDER BY user_id ASC NULLS LAST

Remote SQL: 줄을 보면 WHERE status = 'paid' 가 원격 서버로 넘어가 있다. 즉 6건 전부를 끌어와 로컬에서 거르는 게 아니라, 원격이 필터링한 결과만 받는다. WHERE·ORDER BY·조인까지 넘길 수 있는 게 dblink 대비 postgres_fdw의 가장 큰 이점이다.

3단계 — dblink: 스키마 정의 없이 애드혹 원격 SQL

foreign table을 미리 정의하기 애매한 일회성 질의는 dblink로 던진다. 연결 문자열 + 원격 SQL 문자열을 넘기고, 결과 컬럼 타입만 AS t(...)로 알려주면 된다.

SELECT * FROM dblink(
  'host=127.0.0.1 port=5433 dbname=orders user=postgres password=pw',
  'SELECT status, count(*), sum(amount) FROM remote_orders GROUP BY status ORDER BY 2 DESC'
) AS t(status text, cnt bigint, total numeric);
  status  | cnt | total
----------+-----+--------
 paid     |   4 | 563.50
 pending  |   1 | 512.75
 refunded |   1 |  59.90
(3 rows)

postgres_fdw가 "테이블을 매핑해두고 쓰는" 선언형이라면, dblink는 "원격 SQL 문자열을 그때그때 실행하는" 명령형이다. 미리 스키마를 잡지 않은 원격 DDL, 애드혹 집계, 비동기 팬아웃(dblink_send_query/get_result)에 유리하다. 대신 결과 컬럼 타입을 매번 손으로 적어야 하고, 푸시다운·플래너 최적화는 없다.

4단계 — file_fdw: 서버 CSV 로그를 SQL 테이블로

이 서버는 log_destination='csvlog'라 /var/lib/postgresql/16/main/log/*.csv에 로그가 쌓인다. 그 CSV를 임포트하지 않고 "그 자리에서" foreign table로 걸면, grep 대신 GROUP BY로 로그를 집계할 수 있다. PostgreSQL 16 csvlog는 26개 컬럼이다(순서 고정).

CREATE SERVER pglog_srv FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE pglog (
  log_time timestamptz, user_name text, database_name text, process_id int,
  connection_from text, session_id text, session_line_num bigint, command_tag text,
  session_start_time timestamptz, virtual_transaction_id text, transaction_id bigint,
  error_severity text, sql_state_code text, message text, detail text, hint text,
  internal_query text, internal_query_pos int, context text, query text, query_pos int,
  location text, application_name text, backend_type text, leader_pid int, query_id bigint
) SERVER pglog_srv
  OPTIONS (filename '/var/lib/postgresql/16/main/log/postgresql-2026-07-18_012026.csv', format 'csv');

SELECT error_severity, count(*)
FROM pglog
GROUP BY error_severity
ORDER BY 2 DESC;
 error_severity | count
----------------+-------
 ERROR          |    22
 LOG            |     9
 WARNING        |     3
(3 rows)

CSV 파일이 곧 읽기 전용 테이블이 됐다. WHERE로 특정 에러만 골라내는 것도 된다.

SELECT log_time, error_severity, sql_state_code, message
FROM pglog
WHERE error_severity = 'ERROR' AND message LIKE '%s6%'
ORDER BY log_time;
          log_time          | error_severity | sql_state_code |                  message
----------------------------+----------------+----------------+--------------------------------------------
 2026-07-18 01:27:06.859+00 | ERROR          | 42P01          | relation "no_such_table" does not exist
(1 row)

5단계 — 세 소스를 한 SELECT로

원격 DB(postgres_fdw) + 로컬 DB + 서버 로그(file_fdw)를 UNION ALL로 한 결과에 모은다. "데이터가 어디 있든 SQL 하나로"가 FDW 계열의 그림이다.

SELECT 'remote_orders (postgres_fdw)' AS source, count(*) AS rows FROM remote_orders
UNION ALL
SELECT 'users (local)',                count(*)          FROM users
UNION ALL
SELECT 'server log ERROR (file_fdw)',  count(*)          FROM pglog WHERE error_severity='ERROR'
ORDER BY 1;
            source            | rows
------------------------------+------
 remote_orders (postgres_fdw) |    6
 server log ERROR (file_fdw)  |   23
 users (local)                |   50
(3 rows)

세 갈래 소스가 한 테이블처럼 정렬돼 나온다. (로그 ERROR가 22 → 23으로 는 건 그새 쿼리가 더 돌아 로그가 살아 있다는 뜻 — 파일을 스냅샷이 아니라 "지금 그 파일"로 읽기 때문이다.)

결론 — 언제 무엇을 고르나

  • postgres_fdw — 기본값. 원격이 PostgreSQL이고, 조인·WHERE·집계를 원격으로 푸시다운하고 싶고, 같은 원격 테이블을 반복 조회할 때. 표준(SQL/MED) 방식이고 플래너가 개입한다.
  • dblink — 미리 foreign table을 만들 수 없는 애드혹 1회성, 원격 DDL, 비동기 병렬 팬아웃. 문자열로 SQL을 던지는 대신 최적화는 포기한다. 공식 문서도 정착 용도로는 postgres_fdw를 권한다.
  • file_fdw — 소스가 DB가 아니라 서버 위 파일(CSV 로그, 익스포트 파일, program 출력)일 때. 임포트 없이 읽기 전용으로 SQL을 건다.

이렇게도 쓴다

여러 테이블을 한꺼번에 가져올 땐 IMPORT FOREIGN SCHEMA로 컬럼 정의를 자동 생성한다. (조합: postgres_fdw)

IMPORT FOREIGN SCHEMA public LIMIT TO (remote_orders)
  FROM SERVER fs INTO public;

 

dblink는 원격 DDL/DML도 문자열로 실행한다. foreign table로는 못 하는 애드혹 작업. (조합: dblink)

SELECT dblink_exec(
  'host=127.0.0.1 port=5433 dbname=orders user=postgres password=pw',
  'UPDATE remote_orders SET status=''void'' WHERE status=''pending''');

 

file_fdw의 program 옵션으로 명령 출력을 테이블처럼 읽는다. 파일이 아니라 파이프. (조합: file_fdw)

CREATE FOREIGN TABLE diskusage (line text)
  SERVER pglog_srv OPTIONS (program 'df -h', format 'text');

 

원격 통계 뷰까지 조인해 원격 서버 상태를 로컬에서 모니터링한다. (조합: postgres_fdw + 시스템뷰)

CREATE FOREIGN TABLE remote_activity (pid int, state text, query text)
  SERVER fs OPTIONS (schema_name 'pg_catalog', table_name 'pg_stat_activity');

 

정리는 서버를 CASCADE로 지우면 foreign table·user mapping이 함께 사라진다.

DROP SERVER fs CASCADE;          -- foreign table + user mapping 동반 삭제
DROP SERVER pglog_srv CASCADE;

경계: 원격이 PostgreSQL이 아니면(MySQL/Oracle/파일 API 등) 각각 mysql_fdw, oracle_fdw, multicorn 같은 별도 FDW를 쓴다. FDW는 "원격을 로컬처럼"이지 "원격을 로컬만큼 빠르게"는 아니다 — 네트워크 왕복이 걸리니 대량 조인은 결국 자재화(materialized view)나 ETL로 내리는 게 맞다.

반응형
저작자표시 비영리 변경금지 (새창열림)

'명령어 > DB' 카테고리의 다른 글

[PostgreSQL] 블로트 정밀 진단 3종 — pgstattuple + pg_freespacemap + pageinspect  (0) 2026.07.21
[PostgreSQL] 감사 로그 싸게 남기기 — 문장 트리거 + transition table + JSONB + 이벤트 트리거  (0) 2026.07.21
[PostgreSQL] SLA 대시보드: percentile_cont(p50/p95/p99) + FILTER + TABLESAMPLE  (0) 2026.07.21
[PostgreSQL] 배치 동기화 ETL — COPY FROM PROGRAM + MERGE 다분기 + PROCEDURE 청크 커밋  (0) 2026.07.21
[PostgreSQL] 월간 매출 리포트 한 방에: ROLLUP + FILTER + GROUPING()  (0) 2026.07.21

'명령어/DB'의 다른글

  • 현재글[PostgreSQL] 흩어진 데이터를 SQL 하나로 — postgres_fdw + dblink + file_fdw

관련글

티스토리툴바