원격 PostgreSQL 데이터베이스와 Aurora PostgreSQL 호환 통합 - AWS 권장 가이드
postgres_fdw 사용 사례 및 상위 수준 단계dblink를 사용하여 연결 생성

기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다.

원격 PostgreSQL 데이터베이스와 Aurora PostgreSQL 호환 통합

이 섹션에서는 postgres_fdw (해외 데이터 래퍼) 확장 또는 dblink 기능을 사용하여 원격 PostgreSQL 데이터베이스와 Amazon Aurora PostgreSQL 호환 버전 통합에 대해 설명합니다. postgres_fdw 모듈은 원격 PostgreSQL 기반 데이터베이스와 상호 작용하기 위한 페더레이션 쿼리 기능을 제공합니다. 원격 데이터베이스는 Amazon EC2 또는 온프레미스에서 관리하거나 자체 관리할 수 있습니다. postgres_fdw 확장은 현재 지원되는 모든 버전의 Amazon Relational Database Service(Amazon RDS) for PostgreSQL 및 Aurora PostgreSQL-Compatible에서 사용할 수 있습니다.

postgres_fdw 확장을 사용하면 로컬 테이블인 것처럼 원격 PostgreSQL 데이터베이스의 데이터에 액세스하고 쿼리할 수 있습니다. postgres_fdw 확장은 다음 기능도 지원합니다.

  • 다른 버전을 실행하는 외부 PostgreSQL 서버에서 데이터에 액세스하기 위한 교차 버전 호환성.

  • 트랜잭션 관리는 로컬 및 외부 PostgreSQL 서버에서 작업을 수행할 때 데이터 일관성과 무결성을 보장하는 데 도움이 됩니다.

  • 여러 외부 PostgreSQL 서버에서 작업을 수행할 때 원자성(ACID 트랜잭션의 속성)과 격리 보장을 제공하는 분산 트랜잭션입니다. 이렇게 하면 트랜잭션의 모든 작업이 커밋되거나 커밋되지 않도록 하여 데이터 일관성과 무결성을 유지할 수 있습니다.

dblink 모듈은 원격 PostgreSQL 데이터베이스와 상호 작용하는 방법을 제공하지만 분산 트랜잭션 또는 기타 고급 기능을 지원하지 않습니다. 고급 기능이 필요한 경우 대신 postgres_fdw 확장을 사용하는 것이 좋습니다. postgres_fdw 확장은 더 많은 통합 및 최적화 기능을 제공합니다.

postgres_fdw 사용 사례 및 상위 수준 단계

Aurora PostgreSQL 호환을 사용한 postgres_fdw 확장 사용은 다음 사용 사례 및 시나리오를 지원합니다.

  • 연합 쿼리 및 데이터 통합 - 단일 Aurora PostgreSQL 호환 인스턴스 내에서 여러 PostgreSQL 데이터베이스의 데이터 쿼리 및 결합

  • 읽기 워크로드 오프로드 ‒ 외부 PostgreSQL 서버의 읽기 전용 복제본에 연결, 읽기 중심 워크로드 오프로드, 쿼리 성능 향상

  • 데이터베이스 간 작업 ‒ 여러 PostgreSQL 데이터베이스에서 INSERT, UPDATEDELETE, 및 COPY 작업을 수행하여 데이터베이스 간 데이터 조작 및 유지 관리 작업 활성화

를 구성하려면 다음과 같은 상위 수준 단계를 postgres_fdw사용합니다.

  1. PostgreSQL 클라이언트를 사용하여 Aurora PostgreSQL 호환 클러스터에 연결하고 postgres_fdw 확장을 생성합니다.

    CREATE EXTENSION postgres_fdw;

    이 확장은 원격 PostgreSQL 데이터베이스에 연결하는 기능을 제공합니다.

  2. CREATE SERVER 명령을 my_fdw_target 사용하여 라는 외래 서버를 생성합니다. 이 서버는 연결하려는 원격 PostgreSQL 데이터베이스를 나타냅니다. 데이터베이스 이름, 호스트 이름 및 SSL 모드를이 서버의 옵션으로 지정합니다.

  3. Aurora PostgreSQL 호환이 원격 PostgreSQL 데이터베이스에 연결할 수 있도록 필요한 보안 그룹 및 네트워크 구성이 마련되어 있는지 확인합니다.

    원격 데이터베이스가 온프레미스에서 호스팅되는 경우 가상 프라이빗 네트워크(VPN) 또는 AWS Direct Connect 연결을 구성해야 할 수 있습니다.

    다음 명령 실행:

    CREATE SERVER my_fdw_target Foreign Data Wrapper postgres_fdw OPTIONS (DBNAME 'postgres', HOST 'SOURCE_HOSTNAME', SSLMODE 'require');

  4. my_fdw_target 서버의 사용자에 대한 dbuser 사용자 매핑을 생성합니다. 이 매핑은 로컬 Aurora PostgreSQL 호환 인스턴스의 dbuser 사용자와 암호를 원격 데이터베이스의 해당 사용자와 연결합니다.

    CREATE USER MAPPING FOR dbuser SERVER my_fdw_target OPTIONS (user 'DBUSER', password 'PASSWORD');

    이 단계는 원격 데이터베이스를 인증하고 액세스를 제공하는 데 필요합니다.

  5. 이전에 설정한 my_fdw_target 서버 및 사용자 매핑customer_fdw을 사용하여 라는 외래 테이블을 생성합니다.

    CREATE FOREIGN TABLE customer_fdw( id int, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');

    customer_fdw 테이블은 my_fdw_target 서버에서 지정한 원격 데이터베이스의 customers 테이블에 매핑됩니다. 외부 테이블은 원격 테이블과 구조가 동일하므로 로컬 테이블인 것처럼 원격 데이터와 상호 작용할 수 있습니다.

  6. customer_fdw 외부 테이블에서 , INSERT UPDATE및 SELECT 쿼리와 같은 다양한 데이터 조작 작업을 수행할 수 있습니다. 스크립트는 새 행을 삽입하고 기존 행을 업데이트하고, 레코드를 삭제하고, customer_fdw 외부 테이블을 통해 원격 customers 테이블의 테이블을 잘라내는 방법을 보여줍니다.

    INSERT INTO customer_fdw values ( 1, 'Test1', 'Test1@email.com', 'LMS1', '888888888');
INSERT INTO customer_fdw values ( 2, 'Test2', 'Test2@email.com', 'LMS2', '999999999');
INSERT INTO customer_fdw values ( 3, 'Test3', 'Test3@email.com', 'LMS3', '111111111');
UPDATE customer_fdw set contactnumber = '123456789' where id = 2;
DELETE FROM customer_fdw where id = 1;
TRUNCATE TABLE customer_fdw;

  7. EXPLAIN 문을 사용하여 customer_fdw 테이블의 쿼리에 대한 쿼리 계획을 분석하여 SQL SELECT 쿼리 계획을 검증할 수 있습니다.

    EXPLAIN select * from customer_fdw where id =1;

    이렇게 하면 쿼리가 실행되는 방식과 쿼리를 최적화하는 방법을 이해하는 데 도움이 될 수 있습니다. EXPLAIN 문 사용에 대한 자세한 내용은 AWS 권장 가이드의 PostgreSQL 쿼리 성능 최적화를 참조하세요.

  8. 원격 데이터베이스에서 로컬 스키마로 여러 테이블을 가져오려면 IMPORT FOREIGN SCHEMA 명령을 사용합니다.

    CREATE SCHEMA public_fdw;
IMPORT FOREIGN SCHEMA public LIMIT TO (employees, departments)
    FROM SERVER my_fdw_target INTO public_fdw;

    이렇게 public_fdw 하면 스키마에서 지정된 테이블에 대한 로컬 외래 테이블이 생성됩니다. 이 예제에서 특정 테이블은 직원 및 부서입니다.

  9. FDW 및 연결된 외부 서버에 액세스하고 사용할 수 있도록 특정 데이터베이스 사용자에게 필요한 권한을 부여하려면 다음 명령을 실행합니다.

    GRANT USAGE ON FOREIGN SERVER my_fdw_target TO targetdbuser;
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO targetdbuser;

    이 단계는 여러 사용자가 외부 데이터 래퍼가 지원하는 외부 테이블에 액세스해야 하는 경우에 유용할 수 있습니다.

외래 테이블을 사용할 때는 다음 제한 사항에 유의하세요.

  • 원격 소스에서 데이터에 액세스하면 네트워크 지연 시간으로 인한 데이터 전송 비용과 성능 오버헤드가 발생할 수 있습니다. Aurora PostgreSQL 호환 인스턴스와 원격 데이터 소스 간에 상당한 데이터 전송이 필요한 대규모 데이터 세트 또는 쿼리의 경우 성능 문제가 발생할 수 있습니다.

  • 창 함수와 같은 기능이 포함된 복잡한 쿼리에서는 재귀 쿼리가 예상대로 작동하지 않거나 지원되지 않을 수 있습니다.

  • 현재 암호 암호화는 지원되지 않습니다. 권한 있는 사용자만 FDWs.

  • 다음 테이블 생성 스크립트 시도에서 알 수 있듯이 외래 테이블에는 기본 키 제약 조건을 정의할 수 없습니다.

    CREATE FOREIGN TABLE customer_fdw2( id int primary key, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');
Primary keys cannot be defined on Foreign table

  • 다음 예제와 같이 INSERT 문에 대한 ON CONFLICT 절은 외래 테이블에서 지원되지 않습니다.

    INSERT INTO customer_fdw (id, name, emailid, projectname, contactnumber) VALUES
(1, 'test1', 'test@email.com', 'LMS', 11111111 ),
(3, 'test3', 'test3@email.com', 'LMS', 22222222 )
ON CONFLICT (id) DO UPDATE 
SET name = EXCLUDED.name;
On Conflict option doesnot work.

정리

postgres_fdw 확장 프로그램, my_fdw_target 서버, 사용자 매핑 및 외래 테이블 삭제를 포함하여 생성된 객체를 정리하려면 다음 명령을 실행합니다.

DROP FOREIGN TABLE customer_fdw;
DROP USER MAPPING for postgres;
DROP SERVER  my_fdw_target;
DROP EXTENSION postgres_fdw cascade;

dblink 모듈 함수는 연결을 생성하고 원격 PostgreSQL 데이터베이스에서 SQL 문을 실행하는 대체 방법을 제공합니다. 이 dblink 솔루션은 원격 데이터베이스에서 일회성 쿼리 또는 작업을 실행하는 더 간단하고 유연한 방법입니다. 대규모 데이터 통합, 성능 최적화 및 데이터 무결성 요구 사항이 포함된 보다 복잡한 시나리오의 경우를 사용하는 것이 좋습니다postgres_fdw.

를 사용하려면 다음과 같은 상위 단계가 dblink 필요합니다.

  1. dblink 확장을 생성합니다.

    CREATE EXTENSION dblink;

    이 확장은 원격 PostgreSQL 데이터베이스에 연결하는 기능을 제공합니다.

  2. 원격 PostgreSQL 데이터베이스에 대한 연결을 설정하려면 dblink_connect 함수를 사용합니다.

    SELECT dblink_connect('myconn', 'dbname=postgres port=5432 host=SOURCE_HOSTNAME user=postgres password=postgres');

  3. 원격 PostgreSQL 데이터베이스에 연결한 후 dblink 함수를 사용하여 원격 데이터베이스에서 SQL 문을 실행합니다.

    SELECT  FROM dblink('myconn', 'SELECT col1, col2 FROM remote_table') AS remote_data(col1 int, col2 text);

    이 쿼리는 myconn 연결을 사용하여 원격 데이터베이스에서 SELECT * FROM remote_table 문을 실행합니다. 쿼리는 col1 및 열이 있는 로컬 임시 테이블로 결과를 검색합니다col2.

  4. dblink_exec 함수를 사용하여 원격 데이터베이스DELETE에서 , UPDATE또는 INSERT와 같은 쿼리가 아닌 문을 실행할 수도 있습니다.

    SELECT dblink_exec('myconn', 'INSERT INTO remote_table VALUES (1, ''value'')');