다중 데이터베이스 통합을 위한 PostgreSQL Foreign Data Wrappers(FDW) 최적화

서론

PostgreSQL Foreign Data Wrappers(FDW)는 외부 데이터 소스와의 원활한 통합을 지원하여 원격 데이터베이스, 파일, 심지어 웹 API까지도 로컬 테이블처럼 쿼리할 수 있게 해줍니다. 이 강력한 기능은 복잡한 ETL 파이프라인 없이도 데이터베이스 페더레이션(Federation), 교차 데이터베이스 조인 및 중앙 집중식 데이터 액세스를 가능하게 합니다.

Foreign Data Wrappers란 무엇인가요?

FDW는 SQL/MED(Management of External Data) 표준을 구현하며 다음을 제공합니다:

• 투명한 액세스: 표준 SQL을 사용하여 원격 데이터 쿼리
• 가상 테이블: 데이터 복제가 필요 없음
• 실시간 데이터: 동기화 지연 없는 항상 최신 상태 유지
• 조인 기능: 로컬 및 원격 데이터 결합
• 쓰기 작업: 일부 FDW는 INSERT/UPDATE/DELETE 지원

주요 사용 사례

1. 데이터베이스 마이그레이션: 마이그레이션 중 레거시 시스템에 액세스
2. 멀티 테넌트 아키텍처: 테넌트 데이터베이스 간 쿼리
3. 데이터 집계: 여러 소스의 데이터 결합
4. 하이브리드 클라우드: 온프레미스와 클라우드 데이터베이스 연결
5. 보고서 분석: 중앙 집중식 보고용 데이터베이스 생성
6. 마이크로서비스: 서비스 경계를 넘나드는 쿼리

사용 가능한 Foreign Data Wrappers

postgresql_fdw (내장형)

• 다른 PostgreSQL 데이터베이스에 연결
• 모든 기능 지원
• 최고의 성능

mysql_fdw

• MySQL/MariaDB에 연결
• 읽기 및 쓰기 지원

oracle_fdw

• Oracle 데이터베이스에 연결
• 상용 및 오픈 소스 버전 존재

mongodb_fdw

• MongoDB 컬렉션 액세스
• JSON 데이터 통합

file_fdw (내장형)

• CSV 및 텍스트 파일 읽기
• 로그 파일 분석

기타

• redis_fdw, sqlite_fdw, couchdb_fdw 등

postgres_fdw 설정하기

1단계: 확장 프로그램 설치

-- 로컬 데이터베이스에서 실행
CREATE EXTENSION postgres_fdw;

2단계: 외부 서버 생성

CREATE SERVER remote_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote-db.example.com',
        port '5432',
        dbname 'production_db',
        fetch_size '10000'
    );

3단계: 사용자 매핑 생성

-- 로컬 사용자를 원격 자격 증명에 매핑
CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (
        user 'remote_user',
        password 'secure_password'
    );

-- 모든 로컬 사용자에 대한 매핑
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (
        user 'readonly_user',
        password 'readonly_pass'
    );

4단계: 외부 테이블 생성

수동 테이블 정의:

CREATE FOREIGN TABLE remote_users (
    id INTEGER,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (schema_name 'public', table_name 'users');

전체 스키마 가져오기:

IMPORT FOREIGN SCHEMA public
    FROM SERVER remote_db
    INTO local_schema;

특정 테이블만 가져오기:

IMPORT FOREIGN SCHEMA public
    LIMIT TO (users, orders, products)
    FROM SERVER remote_db
    INTO public;

5단계: 외부 데이터 쿼리

-- 단순 쿼리
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '7 days';

-- 로컬 및 원격 데이터 조인
SELECT 
    l.order_id,
    l.amount,
    r.username,
    r.email
FROM local_orders l
JOIN remote_users r ON l.user_id = r.id
WHERE l.status = 'pending';

고급 구성

성능 튜닝 옵션

CREATE SERVER fast_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'db.example.com',
        port '5432',
        dbname 'mydb',

        -- Fetch size: 왕복당 가져오는 행 수
        fetch_size '50000',

        -- 쿼리 푸시다운(pushdown) 최적화 활성화
        use_remote_estimate 'true',

        -- 커넥션 풀링
        keep_connections 'on',

        -- 병렬 쿼리 실행
        parallel_commit 'on',
        parallel_abort 'on'
    );

테이블 수준 옵션

CREATE FOREIGN TABLE optimized_table (
    id INTEGER,
    data TEXT
)
SERVER remote_db
OPTIONS (
    schema_name 'public',
    table_name 'large_table',

    -- 이 테이블에 대해 fetch size 재정의
    fetch_size '100000',

    -- 더 나은 쿼리 계획을 위해 원격 추정치 사용
    use_remote_estimate 'true'
);

컬럼 수준 옵션

CREATE FOREIGN TABLE mapped_columns (
    local_id INTEGER OPTIONS (column_name 'remote_user_id'),
    local_name TEXT OPTIONS (column_name 'remote_username')
)
SERVER remote_db
OPTIONS (table_name 'users');

쓰기 작업

쓰기 작업 활성화

-- 권한 부여
GRANT INSERT, UPDATE, DELETE ON remote_users TO app_user;

-- 데이터 삽입
INSERT INTO remote_users (username, email)
VALUES ('newuser', '[email protected]');

-- 데이터 수정
UPDATE remote_users
SET email = '[email protected]'
WHERE username = 'newuser';

-- 데이터 삭제
DELETE FROM remote_users WHERE username = 'newuser';

데이터베이스 간 트랜잭션

BEGIN;
    -- 로컬 테이블에 삽입
    INSERT INTO local_orders (user_id, amount) VALUES (123, 99.99);

    -- 원격 테이블 업데이트
    UPDATE remote_users SET last_order = NOW() WHERE id = 123;

    -- 두 작업이 함께 커밋됨
COMMIT;

참고: 2단계 커밋(Two-phase commit)은 데이터베이스 간의 원자성을 보장합니다.

다중 데이터베이스 페더레이션

여러 데이터베이스 연결

-- 운영 데이터베이스 연결
CREATE SERVER prod_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'prod.example.com', dbname 'production');

CREATE USER MAPPING FOR PUBLIC SERVER prod_db
    OPTIONS (user 'readonly', password 'pass1');

-- 분석 데이터베이스 연결
CREATE SERVER analytics_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'analytics.example.com', dbname 'warehouse');

CREATE USER MAPPING FOR PUBLIC SERVER analytics_db
    OPTIONS (user 'readonly', password 'pass2');

-- 아카이브 데이터베이스 연결
CREATE SERVER archive_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'archive.example.com', dbname 'historical');

CREATE USER MAPPING FOR PUBLIC SERVER archive_db
    OPTIONS (user 'readonly', password 'pass3');

데이터베이스 통합 뷰(View)

-- 테이블 가져오기
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (current_orders) 
    FROM SERVER prod_db INTO public;

IMPORT FOREIGN SCHEMA public 
    LIMIT TO (archived_orders) 
    FROM SERVER archive_db INTO public;

-- 통합 뷰 생성
CREATE VIEW all_orders AS
SELECT *, 'current' AS source FROM current_orders
UNION ALL
SELECT *, 'archived' AS source FROM archived_orders;

-- 모든 데이터베이스에 걸쳐 쿼리 실행
SELECT 
    source,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM all_orders
WHERE created_at > '2024-01-01'
GROUP BY source;

MySQL 연결하기

mysql_fdw 설치

# Ubuntu/Debian
sudo apt install postgresql-15-mysql-fdw

# CentOS/RHEL
sudo yum install mysql_fdw_15

MySQL 연결 구성

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (
        host 'mysql-db.example.com',
        port '3306'
    );

CREATE USER MAPPING FOR PUBLIC
    SERVER mysql_server
    OPTIONS (
        username 'mysql_user',
        password 'mysql_pass'
    );

CREATE FOREIGN TABLE mysql_products (
    id INTEGER,
    name VARCHAR(100),
    price DECIMAL(10,2)
)
SERVER mysql_server
OPTIONS (dbname 'ecommerce', table_name 'products');

-- PostgreSQL에서 MySQL 데이터 쿼리
SELECT * FROM mysql_products WHERE price > 100;

성능 최적화

1. WHERE 절 푸시다운(Pushdown) 사용

PostgreSQL은 필터를 원격 서버로 전달합니다:

-- 좋음: 필터가 원격에서 적용됨
SELECT * FROM remote_users WHERE country = 'US';

-- 쿼리 실행 계획 확인
EXPLAIN (VERBOSE) SELECT * FROM remote_users WHERE country = 'US';
-- 확인 사항: "Remote SQL: SELECT ... WHERE country = 'US'"

2. 데이터 전송 제한

-- 나쁨: 모든 컬럼을 가져옴
SELECT * FROM remote_large_table;

-- 좋음: 필요한 컬럼만 선택
SELECT id, username FROM remote_large_table;

-- 좋음: LIMIT 사용
SELECT * FROM remote_large_table LIMIT 1000;

3. Fetch Size 최적화

-- 대규모 결과 집합의 경우
ALTER SERVER remote_db OPTIONS (SET fetch_size '50000');

-- 작고 빈번한 쿼리의 경우
ALTER SERVER remote_db OPTIONS (SET fetch_size '1000');

4. 원격 추정치(Remote Estimates) 사용

ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- PostgreSQL이 정확한 비용 추정을 위해 원격 EXPLAIN을 쿼리합니다.

5. 원격 테이블 인덱싱

-- 원격 테이블에 적절한 인덱스가 있는지 확인합니다.
-- 원격 데이터베이스에 직접 연결하여 실행:

CREATE INDEX idx_users_country ON users(country);
CREATE INDEX idx_orders_created_at ON orders(created_at);

6. 자주 액세스하는 데이터를 위한 구체화된 뷰(Materialized Views)

-- 원격 데이터를 로컬에 캐싱
CREATE MATERIALIZED VIEW cached_remote_data AS
SELECT * FROM remote_users WHERE active = true;

-- 주기적으로 새로 고침
REFRESH MATERIALIZED VIEW cached_remote_data;

-- cron 작업 또는 트리거로 자동 새로 고침
CREATE EXTENSION pg_cron;
SELECT cron.schedule('refresh-cache', '0 */6 * * *', 
    'REFRESH MATERIALIZED VIEW cached_remote_data');

모니터링 및 문제 해결

외부 서버 조회

SELECT 
    srvname AS server_name,
    srvoptions AS options
FROM pg_foreign_server;

외부 테이블 조회

SELECT 
    foreign_table_schema,
    foreign_table_name,
    foreign_server_name
FROM information_schema.foreign_tables;

사용자 매핑 확인

SELECT 
    um.umuser::regrole AS local_user,
    fs.srvname AS foreign_server,
    um.umoptions AS options
FROM pg_user_mapping um
JOIN pg_foreign_server fs ON um.umserver = fs.oid;

쿼리 성능 분석

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '1 day';

커넥션 풀링 확인

-- 활성 외부 연결 확인
SELECT 
    datname,
    usename,
    application_name,
    client_addr,
    state
FROM pg_stat_activity
WHERE application_name LIKE '%fdw%';

일반적인 문제 및 해결 방법

문제 1: 연결 시간 초과

오류: could not connect to server

해결 방법:

-- 연결 제한 시간 추가
ALTER SERVER remote_db OPTIONS (ADD connect_timeout '10');

-- 네트워크 연결 확인
-- 쉘(Shell)에서 실행:
psql -h remote-db.example.com -U user -d dbname

문제 2: 느린 쿼리

해결 방법:

-- 원격 추정치 활성화
ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- Fetch size 증가
ALTER SERVER remote_db OPTIONS (SET fetch_size '100000');

-- WHERE 절이 푸시다운되었는지 확인
EXPLAIN VERBOSE SELECT * FROM remote_table WHERE id > 1000;

문제 3: 인증 실패

오류: password authentication failed

해결 방법:

-- 사용자 매핑 업데이트
ALTER USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (SET password 'new_password');

-- 원격 서버의 pg_hba.conf 확인
-- 로컬 서버로부터의 연결을 허용하는지 확인

문제 4: 컬럼 유형 불일치

오류: column type mismatch

해결 방법:

-- 컬럼을 명시적으로 캐스팅
CREATE FOREIGN TABLE remote_data (
    id INTEGER,
    data TEXT,
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (table_name 'source_table');

-- 또는 올바른 유형으로 다시 가져오기
DROP FOREIGN TABLE remote_data;
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (source_table) 
    FROM SERVER remote_db INTO public;

보안 고려 사항

1. 읽기 전용 계정 사용

-- 원격 데이터베이스에서 제한된 사용자 생성
CREATE ROLE fdw_readonly;
GRANT CONNECT ON DATABASE production_db TO fdw_readonly;
GRANT USAGE ON SCHEMA public TO fdw_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fdw_readonly;

-- 사용자 매핑에서 사용
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (user 'fdw_readonly', password 'safe_pass');

2. 안전한 비밀번호 저장

-- 하드코딩된 비밀번호 대신 .pgpass 파일 사용
-- ~/.pgpass 형식:
-- hostname:port:database:username:password

CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (user 'remote_user');
    -- 비밀번호는 .pgpass에서 읽어옴

3. 네트워크 보안

# SSL 연결 사용
# 원격 서버의 postgresql.conf에서:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

-- 사용자 매핑에서 SSL 요구
CREATE SERVER secure_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote.example.com',
        dbname 'mydb',
        sslmode 'require'
    );

권장 사항(Best Practices)

1. 데이터 전송 최소화: 필요한 컬럼만 선택
2. 인덱스 활용: 원격 테이블에 적절한 인덱스 생성 확인
3. 자주 액세스하는 데이터 캐싱: 구체화된 뷰 사용
4. 성능 모니터링: 정기적인 EXPLAIN ANALYZE 수행
5. 쓰기 작업 제한: 대부분의 FDW 사용 시 읽기 전용 선호
6. 커넥션 풀링 사용: keep_connections 활성화
7. 적절한 fetch_size 설정: 쿼리 패턴에 따라 튜닝
8. 장애 조치(Failover) 시나리오 테스트: 원격 데이터베이스 사용 불가 상황 대비
9. 외부 종속성 문서화: 명확한 문서 유지 관리
10. 정기적인 보안 감사: 사용자 매핑 및 권한 검토

결론

PostgreSQL Foreign Data Wrappers는 강력한 데이터베이스 페더레이션 기능을 제공합니다.

• 원활한 통합: 표준 SQL로 원격 데이터 쿼리
• 실시간 액세스: 데이터 복제나 동기화 지연 없음
• 다중 데이터베이스 지원: PostgreSQL, MySQL, Oracle 등
• 성능: 쿼리 푸시다운 및 최적화
• 유연성: 읽기 및 쓰기 작업 지원

원격 데이터베이스에 대한 읽기 전용 쿼리로 시작하여, 적절한 인덱싱과 fetch size 설정으로 성능을 최적화한 후, 필요에 따라 더 복잡한 통합으로 확장해 보시기 바랍니다.