기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다.
# 원격 PostgreSQL 데이터베이스와 Aurora PostgreSQL 호환 통합
이 섹션에서는 `postgres_fdw` (해외 데이터 래퍼) 확장 또는 `dblink` 기능을 사용하여 원격 PostgreSQL 데이터베이스와 Amazon Aurora PostgreSQL 호환 버전 통합에 대해 설명합니다. `postgres_fdw` 모듈은 원격 PostgreSQL 기반 데이터베이스와 상호 작용하기 위한 페더레이션 쿼리 기능을 제공합니다. 원격 데이터베이스는 [Amazon EC2](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/concepts.html) 또는 온프레미스에서 관리하거나 자체 관리할 수 있습니다. `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`, `UPDATE``DELETE`, 및 `COPY` 작업을 수행하여 데이터베이스 간 데이터 조작 및 유지 관리 작업 활성화
를 구성하려면 다음과 같은 상위 수준 단계를 `postgres_fdw`사용합니다.
1. PostgreSQL 클라이언트를 사용하여 Aurora PostgreSQL 호환 클러스터에 연결하고 `postgres_fdw` 확장을 생성합니다.
```
CREATE EXTENSION postgres_fdw;
```
이 확장은 원격 PostgreSQL 데이터베이스에 연결하는 기능을 제공합니다.
1. `CREATE SERVER` 명령을 `my_fdw_target` 사용하여 라는 외래 서버를 생성합니다. 이 서버는 연결하려는 원격 PostgreSQL 데이터베이스를 나타냅니다. 데이터베이스 이름, 호스트 이름 및 SSL 모드를이 서버의 옵션으로 지정합니다.
1. 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');
```
1. `my_fdw_target` 서버의 사용자에 대한 `dbuser` 사용자 매핑을 생성합니다. 이 매핑은 로컬 Aurora PostgreSQL 호환 인스턴스의 `dbuser` 사용자와 암호를 원격 데이터베이스의 해당 사용자와 연결합니다.
```
CREATE USER MAPPING FOR dbuser SERVER my_fdw_target OPTIONS (user 'DBUSER', password 'PASSWORD');
```
이 단계는 원격 데이터베이스를 인증하고 액세스를 제공하는 데 필요합니다.
1. 이전에 설정한 `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` 테이블에 매핑됩니다. 외부 테이블은 원격 테이블과 구조가 동일하므로 로컬 테이블인 것처럼 원격 데이터와 상호 작용할 수 있습니다.
1. `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;
```
1. `EXPLAIN` 문을 사용하여 `customer_fdw` 테이블의 쿼리에 대한 쿼리 계획을 분석하여 SQL `SELECT` 쿼리 계획을 검증할 수 있습니다.
```
EXPLAIN select * from customer_fdw where id =1;
```
이렇게 하면 쿼리가 실행되는 방식과 쿼리를 최적화하는 방법을 이해하는 데 도움이 될 수 있습니다. `EXPLAIN` 문 사용에 대한 자세한 내용은 AWS 권장 가이드의 [ PostgreSQL 쿼리 성능 최적화](https://docs.aws.amazon.com/prescriptive-guidance/latest/postgresql-query-tuning/introduction.html)를 참조하세요.
1. 원격 데이터베이스에서 로컬 스키마로 여러 테이블을 가져오려면 `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` 하면 스키마에서 지정된 테이블에 대한 로컬 외래 테이블이 생성됩니다. 이 예제에서 특정 테이블은 직원 및 부서입니다.
1. 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를 사용하여 연결 생성
`dblink` 모듈 함수는 연결을 생성하고 원격 PostgreSQL 데이터베이스에서 SQL 문을 실행하는 대체 방법을 제공합니다. 이 `dblink` 솔루션은 원격 데이터베이스에서 일회성 쿼리 또는 작업을 실행하는 더 간단하고 유연한 방법입니다. 대규모 데이터 통합, 성능 최적화 및 데이터 무결성 요구 사항이 포함된 보다 복잡한 시나리오의 경우를 사용하는 것이 좋습니다`postgres_fdw`.
를 사용하려면 다음과 같은 상위 단계가 `dblink` 필요합니다.
1. `dblink` 확장 기능을 생성합니다.
```
CREATE EXTENSION dblink;
```
이 확장은 원격 PostgreSQL 데이터베이스에 연결하는 기능을 제공합니다.
1. 원격 PostgreSQL 데이터베이스에 대한 연결을 설정하려면 `dblink_connect` 함수를 사용합니다.
```
SELECT dblink_connect('myconn', 'dbname=postgres port=5432 host=SOURCE_HOSTNAME user=postgres password=postgres');
```
1. 원격 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`.
1. `dblink_exec` 함수를 사용하여 원격 데이터베이스`DELETE`에서 , `INSERT` `UPDATE`또는와 같은 쿼리가 아닌 문을 실행할 수도 있습니다.
```
SELECT dblink_exec('myconn', 'INSERT INTO remote_table VALUES (1, ''value'')');
```