다중 데이터베이스 통합을 위한 PostgreSQL 외부 데이터 래퍼 최적화

PostgreSQL 외부 데이터 래퍼(FDW)는 데이터가 여러 곳에 분산되어 있고 모든 데이터를 먼저 복사하지 않고 쿼리해야 할 때 유용합니다. FDW는 원격 테이블을 로컬 테이블처럼 보이게 만들 수 있지만, 성능과 트랜잭션 동작은 래퍼, 네트워크 및 쿼리 형태에 크게 의존합니다.

외부 데이터 래퍼란?

FDW는 SQL/MED(외부 데이터 관리) 표준을 구현하며, 다음을 제공합니다:

• 투명한 접근: 표준 SQL을 사용하여 원격 데이터 쿼리
• 가상 테이블: 데이터 중복 불필요
• 실시간 원격 읽기: 로컬 복사본 대신 원격 소스에서 쿼리 읽기
• 조인 기능: 로컬 및 원격 데이터 결합
• 쓰기 작업: 일부 래퍼는 INSERT, UPDATE, DELETE 지원

일반적인 사용 사례

1. 데이터베이스 마이그레이션: 마이그레이션 중 레거시 시스템 접근
2. 멀티 테넌트 아키텍처: 테넌트 데이터베이스 간 쿼리
3. 데이터 집계: 여러 소스의 데이터 결합
4. 하이브리드 클라우드: 온프레미스 및 클라우드 데이터베이스 연결
5. 리포팅: 중앙 집중식 리포팅 데이터베이스 생성
6. 마이크로서비스: 서비스 경계 간 쿼리

사용 가능한 외부 데이터 래퍼

postgresql_fdw (내장)

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

mysql_fdw

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

oracle_fdw

• Oracle 데이터베이스에 연결
• 일반적으로 오픈 소스 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 '50000',
        
        -- 쿼리 푸시다운 최적화 활성화
        use_remote_estimate 'true',
        
        -- PostgreSQL 세션 내에서 외부 서버 연결 재사용
        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 '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;

PostgreSQL은 FDW를 통해 원격 작업을 조정하지만, 모든 래퍼가 완전한 분산 트랜잭션 보장을 제공한다고 가정해서는 안 됩니다. postgres_fdw의 경우, 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');

데이터베이스 간 통합 뷰

-- 테이블 가져오기
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 설치

# 패키지 이름은 PostgreSQL 버전 및 저장소에 따라 다릅니다.
# Debian/Ubuntu에서 먼저 검색:
apt search mysql-fdw

# 그런 다음 PostgreSQL 주요 버전과 일치하는 패키지를 설치합니다.
sudo apt install postgresql-16-mysql-fdw

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 절 푸시다운 사용

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. 가져오기 크기 최적화

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

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

4. 원격 추정 사용

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. 자주 액세스하는 데이터에 대한 구체화된 뷰

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

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

-- pg_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';

연결 확인

EXPLAIN (ANALYZE, VERBOSE)를 사용하여 원격 SQL 및 타이밍을 확인하세요. PostgreSQL 간 링크의 경우, 긴 FDW 쿼리가 실행되는 동안 원격 서버의 pg_stat_activity도 확인하세요.

일반적인 문제 및 해결 방법

문제 1: 연결 시간 초과

오류: could not connect to server

해결 방법:

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

-- 네트워크 연결 확인
-- 셸에서:
psql -h remote-db.example.com -U user -d dbname

문제 2: 느린 쿼리

해결 방법:

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

-- 가져오기 크기 증가
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. 안전한 비밀번호 저장

-- 사용자 매핑에 비밀번호를 저장하는 대신 libpq 연결에 .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'
    );

모범 사례

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

핵심 요약

PostgreSQL 외부 데이터 래퍼는 데이터베이스 페더레이션에 유용하며, 특히 다음이 필요할 때 유용합니다:

• 원활한 통합: 표준 SQL로 원격 데이터 쿼리
• 실시간 액세스: 데이터 중복 또는 동기화 지연 없음
• 다중 데이터베이스 지원: PostgreSQL, MySQL, Oracle 등
• 성능: 래퍼와 쿼리가 지원하는 경우 쿼리 푸시다운
• 유연성: 먼저 읽기 액세스, 래퍼 동작 확인 후 쓰기로 확장

원격 데이터베이스에 대한 읽기 전용 쿼리로 시작하고, 적절한 인덱싱과 가져오기 크기로 성능을 최적화한 다음, 필요에 따라 더 복잡한 통합으로 확장하세요.