# 확장 및 외부 데이터 래퍼 작업
Aurora PostgreSQL 호환 버전 DB 클러스터의 기능을 확장하기 위해 다양한 PostgreSQL *확장*을 설치하고 사용할 수 있습니다. 예를 들어 사용 사례에서 매우 큰 테이블에 집중적인 데이터 입력이 필요한 경우 `[pg\$1partman](https://pgxn.org/dist/pg_partman/doc/pg_partman.html)` 확장을 설치하여 데이터를 분할하고 워크로드를 분산할 수 있습니다.
**참고**
Aurora PostgreSQL 14.5부터 Aurora PostgreSQL은 PostgreSQL용 신뢰할 수 있는 언어 확장을 지원합니다. 이 기능은 Aurora PostgreSQL에 추가할 수 있는 `pg_tle` 확장으로 구현됩니다. 이 확장을 사용하면 개발자는 설정 및 구성 요구 사항을 간소화하고 새 확장에 대한 많은 예비 테스트를 줄일 수 있는 안전한 환경에서 자체 PostgreSQL 확장을 만들 수 있습니다. 자세한 내용은 [PostgreSQL용 신뢰할 수 있는 언어 확장 작업](PostgreSQL_trusted_language_extension.md) 섹션을 참조하세요.
경우에 따라 확장을 설치하는 대신 Aurora PostgreSQL DB 클러스터의 사용자 지정 DB 클러스터 파라미터 그룹의 `shared_preload_libraries` 목록에 특정 *모듈*을 추가할 수 있습니다. 일반적으로 기본 DB 클러스터 파라미터 그룹은 `pg_stat_statements`만 로드하지만 목록에 추가할 수 있는 다른 모듈도 몇 개 있습니다. 예를 들어, [PostgreSQL pg\$1cron 확장을 사용하여 유지 관리 예약](PostgreSQL_pg_cron.md)에 자세히 설명된 대로 `pg_cron` 모듈을 추가하여 스케줄링 기능을 추가할 수 있습니다. 또 다른 예로, `auto_explain` 모듈을 로드하여 쿼리 실행 계획을 로깅할 수 있습니다. 자세히 알아보려면 AWS 지식 센터에서 [쿼리 실행 계획 로깅](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/#)을 참조하세요.
외부 데이터에 대한 액세스를 제공하는 확장을 구체적으로 *외부 데이터 래퍼*(FDW)라고 합니다. 하나의 예로는 `oracle_fdw` 확장을 사용하면 Aurora PostgreSQL DB 클러스터가 Oracle 데이터베이스와 함께 작동할 수 있는 경우가 있습니다.
또한 Aurora PostgreSQL DB 인스턴스에 설치할 수 있는 확장 프로그램을 `rds.allowed_extensions` 파라미터에 나열하여 정확하게 지정할 수 있습니다. 자세한 내용은 [PostgreSQL 확장 프로그램의 설치 제한](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction.html)을 참조하세요.
아래에서 Aurora PostgreSQL에 사용할 수 있는 일부 확장, 모듈 및 FDW 설정과 사용에 대한 정보를 확인할 수 있습니다. 간단히 이 모든 것을 '확장'이라고 합니다. 현재 사용 가능한 Aurora PostgreSQL 버전에서 사용할 수 있는 확장 목록은 Aurora PostgreSQL 릴리스 정보**에서 [Extension versions for Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html)(Aurora PostgreSQL 확장 버전)을 참조하세요.
+ [lo 모듈을 사용하여 대형 객체 관리](PostgreSQL_large_objects_lo_extension.md)
+ [PostGIS 확장을 사용하여 공간 데이터 관리](Appendix.PostgreSQL.CommonDBATasks.PostGIS.md)
+ [pg\$1partman 확장자를 사용하여 PostgreSQL 파티션 관리하기](PostgreSQL_Partitions.md)
+ [PostgreSQL pg\$1cron 확장을 사용하여 유지 관리 예약](PostgreSQL_pg_cron.md)
+ [pgAudit를 사용하여 데이터베이스 활동 로깅](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md)
+ [pglogical을 사용하여 인스턴스 간 데이터 동기화](Appendix.PostgreSQL.CommonDBATasks.pglogical.md)
+ [oracle\$1fdw 확장을 사용하여 Oracle 데이터베이스 작업](postgresql-oracle-fdw.md)
+ [tds\$1fdw 확장을 사용하여 SQL Server 데이터베이스 작업](postgresql-tds-fdw.md)
# PostgreSQL에 대한 Amazon Aurora 위임 확장 지원 사용
PostgreSQL에 대한 Amazon Aurora 위임 확장 지원을 사용하면 `rds_superuser` 역할이 아닌 사용자에게도 확장 관리를 위임할 수 있습니다. 이 위임 확장 지원을 통해 `rds_extension`이라는 새 역할이 생성되며, 이 역할이 할당되어야 해당 사용자가 다른 확장을 관리할 수 있습니다. 이 역할은 확장을 생성하고, 업데이트하고, 삭제할 수 있습니다.
Aurora PostgreSQL DB 인스턴스에 설치할 수 있는 확장을 `rds.allowed_extensions` 파라미터에 등록하여 지정할 수 있습니다. 자세한 내용은 [Amazon RDS for PostgreSQL로 PostgreSQL 확장 사용](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.html)을 참조하세요.
`rds.allowed_delegated_extensions` 파라미터를 사용하여 `rds_extension` 역할이 있는 사용자가 관리할 수 있는 가용 확장 목록을 제한할 수 있습니다.
위임 확장 지원은 다음 버전에서 사용할 수 있습니다.
+ 모든 상위 버전
+ 15.5 이상의 15 버전
+ 14.10 이상의 14 버전
+ 13.13 이상의 13 버전
+ 12.17 이상의 12 버전
**Topics**
+ [사용자에게 위임 확장 지원 활성화](#AuroraPostgreSQL.delegated_ext_mgmt)
+ [Aurora 위임 확장 지원에서 PostgreSQL에 대해 사용되는 구성](#AuroraPostgreSQL.delegated_ext_config)
+ [위임 확장에 대한 지원 비활성화](#AuroraPostgreSQL.delegated_ext_disable)
+ [Amazon Aurora 위임 확장 지원 사용의 이점](#AuroraPostgreSQL.delegated_ext_benefits)
+ [PostgreSQL에 대한 Aurora 위임 확장 지원 제한](#AuroraPostgreSQL.delegated_ext_limit)
+ [특정 확장에 필요한 권한](#AuroraPostgreSQL.delegated_ext_perm)
+ [보안 고려 사항](#AuroraPostgreSQL.delegated_ext_sec)
+ [확장 삭제 캐스케이드 비활성화](#AuroraPostgreSQL.delegated_ext_drop)
+ [위임 확장 지원을 사용하여 추가할 수 있는 확장의 예](#AuroraPostgreSQL.delegated_ext_support)
## 사용자에게 위임 확장 지원 활성화
사용자에게 위임 확장 지원을 활성화하려면 다음을 수행해야 합니다.
1. **사용자에게 `rds_extension` 역할 부여** - `rds_superuser` 권한으로 데이터베이스에 연결하고 다음 명령을 실행합니다.
```
Postgres => grant rds_extension to user_name;
```
1. **위임된 사용자가 관리할 수 있는 확장 목록 설정** - `rds.allowed_delegated_extensions`를 통해 DB 클러스터 파라미터에서 `rds.allowed_extensions`를 사용하여 가용 확장의 하위 집합을 지정할 수 있습니다. 다음 수준 중 하나에서 이 작업을 수행할 수 있습니다.
+ 클러스터 또는 인스턴스 파라미터 그룹에서 AWS Management Console 또는 API를 통해 가능합니다. 자세한 내용은 [Amazon Aurora의 파라미터 그룹](USER_WorkingWithParamGroups.md) 섹션을 참조하세요.
+ 데이터베이스 수준에서 다음 명령을 사용하세요.
```
alter database database_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
+ 사용자 수준에서 다음 명령을 사용하세요.
```
alter user user_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
**참고**
`rds.allowed_delegated_extensions` 동적 파라미터를 변경한 후에는 데이터베이스를 다시 시작할 필요가 없습니다.
1. **확장 생성 프로세스 중에 생성된 객체에 대한 위임된 사용자 액세스 허용** - 특정 확장에서는 추가 권한을 부여해야 `rds_extension` 역할을 가진 사용자가 해당 개체에 액세스할 수 있는 개체를 만들 수 있습니다. `rds_superuser`는 위임된 사용자에게 해당 객체에 대한 액세스 권한을 부여해야 합니다. 이벤트 트리거를 사용하여 위임된 사용자에게 권한을 자동으로 부여하는 것도 한 가지 방법입니다.
**이벤트 트리거 예제**
확장 생성 시 만들어진 객체에 대한 권한 설정이 필요한 확장을 `rds_extension` 권한이 있는 위임된 사용자가 사용할 수 있도록 하려면, 아래 이벤트 트리거 예제를 사용자 지정하고 위임된 사용자가 전체 기능에 액세스할 수 있도록 하려는 확장만 추가하면 됩니다. 이 이벤트 트리거는 template1(기본 템플릿)에서 만들 수 있으므로, template1에서 만든 모든 데이터베이스에는 해당 이벤트 트리거가 있습니다. 위임된 사용자가 확장을 설치하면 이 트리거는 확장에서 생성된 객체에 대한 소유권을 자동으로 부여합니다.
```
CREATE OR REPLACE FUNCTION create_ext()
RETURNS event_trigger AS $$
DECLARE
schemaname TEXT;
databaseowner TEXT;
r RECORD;
BEGIN
IF tg_tag = 'CREATE EXTENSION' and current_user != 'rds_superuser' THEN
RAISE NOTICE 'SECURITY INVOKER';
RAISE NOTICE 'user: %', current_user;
FOR r IN SELECT * FROM pg_catalog.pg_event_trigger_ddl_commands()
LOOP
CONTINUE WHEN r.command_tag != 'CREATE EXTENSION' OR r.object_type != 'extension';
schemaname = (
SELECT n.nspname
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_namespace AS n
ON e.extnamespace = n.oid
WHERE e.oid = r.objid
);
databaseowner = (
SELECT pg_catalog.pg_get_userbyid(d.datdba)
FROM pg_catalog.pg_database d
WHERE d.datname = current_database()
);
RAISE NOTICE 'Record for event trigger %, objid: %,tag: %, current_user: %, schema: %, database_owenr: %', r.object_identity, r.objid, tg_tag, current_user, schemaname, databaseowner;
IF r.object_identity = 'address_standardizer_data_us' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_gaz TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_lex TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_rules TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'dict_int' THEN
EXECUTE pg_catalog.format('ALTER TEXT SEARCH DICTIONARY %I.intdict OWNER TO %I;', schemaname, databaseowner);
ELSIF r.object_identity = 'pg_partman' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config_sub TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.custom_time_partitions TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'postgis_topology' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE ON SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
END IF;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE EVENT TRIGGER log_create_ext ON ddl_command_end EXECUTE PROCEDURE create_ext();
```
## Aurora 위임 확장 지원에서 PostgreSQL에 대해 사용되는 구성
| 구성 이름 | 설명 | 기본값 | 참고 | 권한을 수정하거나 부여할 수 있는 사람 |
| --- | --- | --- | --- | --- |
| `rds.allowed_delegated_extensions` | 이 파라미터는 rds\$1extension 역할이 데이터베이스에서 관리할 수 있는 확장을 제한합니다. rds.allowed\$1extensions의 하위 집합이어야 합니다. | 빈 문자열 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/Aurora_delegated_ext.html) 이 파라미터 설정에 대한 자세한 내용은 [사용자에게 위임 확장 지원 활성화](#AuroraPostgreSQL.delegated_ext_mgmt) 섹션을 참조하세요. | rds\$1superuser |
| `rds.allowed_extensions` | 이 파라미터를 사용하면 고객이 Aurora PostgreSQL DB 인스턴스에 설치할 수 있는 확장을 제한할 수 있습니다. 자세한 내용은 [PostgreSQL 확장 설치 제한](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction)을 참조하세요. | "\$1" | 기본적으로 이 파라미터는 ‘\$1’로 설정되어 있습니다. 즉, RDS for PostgreSQL과 Aurora PostgreSQL에서 지원되는 모든 확장은 필요한 권한이 있는 사용자가 만들 수 있습니다. 이 값이 비어 있으면 Aurora PostgreSQL DB 인스턴스에 확장을 설치할 수 없습니다. | 관리자 |
| `rds-delegated_extension_allow_drop_cascade` | 이 파라미터는 `rds_extension` 권한이 있는 사용자가 캐스케이드 옵션을 사용하여 확장을 삭제할 수 있는 기능을 제어합니다. | 끄기 | 기본적으로 `rds-delegated_extension_allow_drop_cascade`는 `off`로 설정됩니다. 즉, `rds_extension` 사용자는 캐스케이드 옵션을 사용하여 확장 프로그램을 삭제할 수 없습니다. 이 기능을 부여하려면 `rds.delegated_extension_allow_drop_cascade` 파라미터를 `on`으로 설정해야 합니다. | rds\$1superuser |
## 위임 확장에 대한 지원 비활성화
**부분적으로 비활성화**
위임된 사용자는 새 확장을 만들 수는 없지만 기존 확장을 계속 업데이트할 수는 있습니다.
+ DB 클러스터 파라미터 그룹에서 `rds.allowed_delegated_extensions`를 기본값으로 재설정합니다.
+ 데이터베이스 수준에서 다음 명령을 사용하세요.
```
alter database database_name reset rds.allowed_delegated_extensions;
```
+ 사용자 수준에서 다음 명령을 사용하세요.
```
alter user user_name reset rds.allowed_delegated_extensions;
```
**전체적으로 비활성화**
사용자로부터 `rds_extension` 역할을 취소하면 사용자는 표준 권한으로 되돌아갑니다. 해당 사용자는 더 이상 확장을 생성, 업데이트 또는 삭제할 수 없습니다.
```
postgres => revoke rds_extension from user_name;
```
## Amazon Aurora 위임 확장 지원 사용의 이점
PostgreSQL에 대한 Amazon Aurora 위임 확장 지원을 사용하면 `rds_superuser` 역할이 아닌 사용자에게도 확장 관리를 안전하게 위임할 수 있습니다. 이 기능에는 다음과 같은 이점이 있습니다.
+ 원하는 사용자에게 확장 관리를 쉽게 위임할 수 있습니다.
+ 이 작업에는 `rds_superuser` 역할이 필요하지 않습니다.
+ 동일한 DB 클러스터의 여러 데이터베이스에 대해 서로 다른 확장 세트를 지원하는 기능을 제공합니다.
## PostgreSQL에 대한 Aurora 위임 확장 지원 제한
+ 확장 생성 프로세스 중에 생성되는 객체에서 확장이 제대로 작동하려면 추가 권한이 필요할 수 있습니다.
## 특정 확장에 필요한 권한
다음과 같은 확장을 생성, 사용 또는 업데이트하려면 위임된 사용자에게 다음과 같은 함수, 테이블 및 스키마에 대해 필요한 권한이 있어야 합니다.
| 소유권이나 권한이 필요한 확장 | 함수 | 테이블 | 스키마 | 텍스트 검색 사전 | 설명 |
| --- | --- | --- | --- | --- | --- |
| address\$1standardizer\$1data\$1us | | us\$1gaz, us\$1lex, us\$1lex, I.us\$1rules | | | |
| amcheck | bt\$1index\$1check, bt\$1index\$1parent\$1check | | | | |
| dict\$1int | | | | intdict | |
| pg\$1partman | | custom\$1time\$1partitions, part\$1config, part\$1config\$1sub | | | |
| pg\$1stat\$1statements | | | | | |
| PostGIS | st\$1tileenvelope | spatial\$1ref\$1sys | | | |
| postgis\$1raster | | | | | |
| postgis\$1topology | | topology, layer | topology | | 위임된 사용자는 데이터베이스 소유자여야 함 |
| log\$1fdw | create\$1foreign\$1table\$1for\$1log\$1file | | | | |
| rds\$1tools | role\$1password\$1encryption\$1type | | | | |
| postgis\$1tiger\$1geocoder | | geocode\$1settings\$1default, geocode\$1settings | tiger | | |
| pg\$1freespacemap | pg\$1freespace | | | | |
| pg\$1visibility | pg\$1visibility | | | | |
## 보안 고려 사항
`rds_extension` 역할이 있는 사용자는 접속 권한이 있는 모든 데이터베이스의 확장을 관리할 수 있다는 점을 기억하세요. 위임된 사용자가 단일 데이터베이스의 확장을 관리하도록 하려는 경우, 각 데이터베이스의 모든 공개 권한을 취소한 다음 해당 특정 데이터베이스의 연결 권한을 위임 사용자에게 명시적으로 부여하는 것이 좋습니다.
사용자가 여러 데이터베이스의 정보에 액세스할 수 있도록 하는 확장에는 여러 가지가 있습니다. 이러한 확장을 `rds.allowed_delegated_extensions`에 추가하기 전에 `rds_extension` 권한을 부여한 사용자가 데이터베이스 간 기능을 사용할 수 있는지 확인하세요. 예를 들어, `postgres_fdw` 및 `dblink`는 동일한 인스턴스 또는 원격 인스턴스의 여러 데이터베이스를 쿼리할 수 있는 기능을 제공합니다. `log_fdw`는 인스턴스의 모든 데이터베이스에 대한 postgres 엔진 로그 파일을 읽습니다. 이 로그 파일에는 여러 데이터베이스의 느린 쿼리나 오류 메시지가 포함될 수 있습니다. `pg_cron`은 예약된 백그라운드 작업을 DB 인스턴스에서 실행할 수 있도록 하고 작업이 다른 데이터베이스에서 실행되도록 구성할 수 있습니다.
## 확장 삭제 캐스케이드 비활성화
`rds_extension` 역할의 사용자가 캐스케이드 옵션을 사용하여 확장을 삭제하는 기능은 `rds.delegated_extension_allow_drop_cascade` 파라미터에 의해 제어됩니다. 기본적으로 `rds-delegated_extension_allow_drop_cascade`는 `off`로 설정됩니다. 즉, `rds_extension` 역할의 사용자는 아래 쿼리에 나와 있는 캐스케이드 옵션으로 확장을 삭제할 수 없습니다.
```
DROP EXTENSION CASCADE;
```
이렇게 하면 확장에 종속된 객체와 해당 객체에 종속된 모든 객체가 자동으로 삭제되기 때문입니다. 캐스케이드 옵션을 사용하려고 시도하면 오류가 발생합니다.
이 기능을 부여하려면 `rds.delegated_extension_allow_drop_cascade` 파라미터를 `on`으로 설정해야 합니다.
`rds.delegated_extension_allow_drop_cascade` 동적 파라미터를 변경하기 위해 데이터베이스를 다시 시작할 필요가 없습니다. 다음 수준 중 하나에서 이 작업을 수행할 수 있습니다.
+ 클러스터 또는 인스턴스 파라미터 그룹에서 AWS Management Console 또는 API를 통해 가능합니다.
+ 데이터베이스 수준에서 다음 명령을 사용하세요.
```
alter database database_name set rds.delegated_extension_allow_drop_cascade = 'on';
```
+ 사용자 수준에서 다음 명령을 사용하세요.
```
alter role tenant_user set rds.delegated_extension_allow_drop_cascade = 'on';
```
## 위임 확장 지원을 사용하여 추가할 수 있는 확장의 예
+ `rds_tools`
```
extension_test_db=> create extension rds_tools;
CREATE EXTENSION
extension_test_db=> SELECT * from rds_tools.role_password_encryption_type() where rolname = 'pg_read_server_files';
ERROR: permission denied for function role_password_encryption_type
```
+ `amcheck`
```
extension_test_db=> CREATE TABLE amcheck_test (id int);
CREATE TABLE
extension_test_db=> INSERT INTO amcheck_test VALUES (generate_series(1,100000));
INSERT 0 100000
extension_test_db=> CREATE INDEX amcheck_test_btree_idx ON amcheck_test USING btree (id);
CREATE INDEX
extension_test_db=> create extension amcheck;
CREATE EXTENSION
extension_test_db=> SELECT bt_index_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_check
extension_test_db=> SELECT bt_index_parent_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_parent_check
```
+ `pg_freespacemap`
```
extension_test_db=> create extension pg_freespacemap;
CREATE EXTENSION
extension_test_db=> SELECT * FROM pg_freespace('pg_authid');
ERROR: permission denied for function pg_freespace
extension_test_db=> SELECT * FROM pg_freespace('pg_authid',0);
ERROR: permission denied for function pg_freespace
```
+ `pg_visibility`
```
extension_test_db=> create extension pg_visibility;
CREATE EXTENSION
extension_test_db=> select * from pg_visibility('pg_database'::regclass);
ERROR: permission denied for function pg_visibility
```
+ `postgres_fdw`
```
extension_test_db=> create extension postgres_fdw;
CREATE EXTENSION
extension_test_db=> create server myserver foreign data wrapper postgres_fdw options (host 'foo', dbname 'foodb', port '5432');
ERROR: permission denied for foreign-data wrapper postgres_fdw
```
# lo 모듈을 사용하여 대형 객체 관리
lo 모듈(확장)은 JDBC 또는 ODBC 드라이버를 통해 PostgreSQL 데이터베이스로 작업하는 데이터베이스 사용자 및 개발자를 위한 것입니다. JDBC와 ODBC는 모두 참조가 변경될 때 데이터베이스에서 대형 객체의 삭제를 처리할 것으로 예상합니다. 그러나 PostgreSQL은 이런 식으로 작동하지 않습니다. PostgreSQL 참조가 변경될 때 객체를 삭제해야 한다고 가정하지 않습니다. 따라서 객체는 참조되지 않고 디스크에 남아 있게 됩니다. lo 확장에는 필요한 경우 객체를 삭제하기 위해 참조 변경 사항을 트리거하는 데 사용하는 함수가 포함되어 있습니다.
**작은 정보**
데이터베이스가 lo 확장의 이점을 누릴 수 있는지 확인하려면 `vacuumlo` 유틸리티를 사용하여 분리되어 있는 대형 객체가 있는지 확인합니다. 작업을 수행하지 않고 분리되어 있는 대형 객체 수를 확인하려면 `-n` 옵션(no-op)과 함께 유틸리티를 실행하면 됩니다. 자세한 방법은 다음 [vacuumlo utility](#vacuumlo-utility) 섹션을 참조하세요.
lo 모듈은 Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 이상 마이너 버전에서 사용할 수 있습니다.
모듈(확장)을 설치하려면 `rds_superuser` 권한이 필요합니다. lo 확장을 설치하면 데이터베이스에 다음이 추가됩니다.
+ `lo` - 바이너리 대형 객체(BLOB) 및 기타 대형 객체에 사용할 수 있는 대형 객체(lo) 데이터 유형입니다. `lo` 데이터 유형은 `oid` 데이터 유형의 영역에 있습니다. 바꿔 말하면 선택적 제약 조건이 포함된 객체 식별자입니다. 자세한 내용은 PostgreSQL 설명서의 [객체 식별자](https://www.postgresql.org/docs/14/datatype-oid.html)를 참조하세요. 간단히 말해, `lo` 데이터 유형을 사용하여 대형 객체 참조가 들어 있는 데이터베이스 열을 다른 객체 식별자(OID)와 구별할 수 있습니다.
+ `lo_manage` - 대형 객체 참조가 포함된 테이블 열의 트리거에 사용할 수 있는 함수입니다. 대형 객체를 참조하는 값을 삭제하거나 수정할 때마다 트리거는 객체(`lo_unlink`)를 참조에서 분리합니다. 열이 대형 객체에 대한 유일한 데이터베이스 참조인 경우에만 열에 트리거를 사용합니다.
대형 객체 모듈에 대한 자세한 내용은 PostgreSQL 설명서의 [lo](https://www.postgresql.org/docs/current/lo.html)를 참조하세요.
## lo 확장 설치
lo 확장을 설치하려면 `rds_superuser` 권한이 필요합니다.
**lo 확장 설치**
1. `psql`을 사용하여 Aurora PostgreSQL DB 클러스터의 프라이머리 DB 인스턴스에 연결합니다.
```
psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
메시지가 표시되면 암호를 입력합니다. `psql` 클라이언트는 기본 관리 연결 데이터베이스인 `postgres=>`를 연결하고 프롬프트로 표시합니다.
1. 다음과 같이 확장을 설치합니다.
```
postgres=> CREATE EXTENSION lo;
CREATE EXTENSION
```
이제 `lo` 데이터 유형을 사용하여 테이블의 열을 정의할 수 있습니다. 예를 들어, 래스터 이미지 데이터가 들어 있는 테이블(`images`)을 생성할 수 있습니다. 테이블을 생성하는 다음 예와 같이 열 `raster`에 `lo` 데이터 유형을 사용할 수 있습니다.
```
postgres=> CREATE TABLE images (image_name text, raster lo);
```
## lo\$1manage 트리거 함수를 사용하여 객체 삭제
`lo`의 트리거 또는 다른 대형 객체 열에서 `lo_manage` 함수를 사용하여 `lo`가 업데이트되거나 삭제될 때 분리된 객체를 지우거나 발생하지 않도록 방지할 수 있습니다.
**대형 객체를 참조하는 열에 트리거 설정**
+ 다음 중 하나를 수행하세요.
+ 인수의 열 이름을 사용하여 대형 객체에 대한 고유한 참조를 포함하도록 각 열에 BEFORE UPDATE OR DELETE 트리거를 생성합니다.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
+ 열이 업데이트되는 경우에만 트리거를 적용합니다.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
`lo_manage` 트리거 함수는 트리거를 정의하는 방법에 따라 열 데이터를 삽입하거나 삭제하는 맥락에서만 작동합니다. 데이터베이스에서 `DROP` 또는 `TRUNCATE` 작업을 수행할 때는 영향을 미치지 않습니다. 즉, 삭제하기 전에 테이블에서 객체 열을 삭제하여 분리된 객체가 생성되지 않도록 해야 합니다.
예를 들어, `images` 테이블을 포함하는 데이터베이스를 삭제한다고 가정해 봅니다. 다음과 같이 열을 삭제합니다.
```
postgres=> DELETE FROM images COLUMN raster
```
해당 열에 삭제를 처리하는 `lo_manage` 함수가 정의되어 있다는 가정하에 이제 테이블을 안전하게 삭제할 수 있습니다.
## `vacuumlo`를 사용하여 고립된 대형 객체 제거
`vacuumlo` 유틸리티는 데이터베이스에서 고립된 대형 객체를 식별하고 제거할 수 있습니다. 이 유틸리티는 PostgreSQL 9.1.24부터 지원됩니다. 데이터베이스 사용자가 대형 객체로 자주 작업하는 경우 가끔 `vacuumlo`를 실행하여 분리되어 있는 대형 객체를 정리하는 것이 좋습니다.
lo 확장을 설치하기 전에 `vacuumlo`를 사용하여 Aurora PostgreSQL DB 클러스터의 이점을 얻을 수 있는지를 평가할 수 있습니다. 이렇게 하려면 다음과 같이 `-n` 옵션(no-op)과 함께 `vacuumlo`를 실행하여 제거할 항목을 표시합니다.
```
$ vacuumlo -v -n -h your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgres docs-lab-spatial-db
Password:*****
Connected to database "docs-lab-spatial-db"
Test run: no large objects will be removed!
Would remove 0 large objects from database "docs-lab-spatial-db".
```
출력에서 알 수 있듯이 분리된 대형 객체는 이 특정 데이터베이스에서는 문제가 되지 않습니다.
유틸리티에 대한 자세한 내용은 PostgreSQL 설명서의 [https://www.postgresql.org/docs/current/vacuumlo.html](https://www.postgresql.org/docs/current/vacuumlo.html) 섹션을 참조하세요.
## `vacuumlo` 작동 방식 이해
`vacuumlo` 명령은 사용자 테이블에 영향을 미치거나 충돌하지 않고 PostgreSQL 데이터베이스에서 고립된 대형 객체(LO)를 제거합니다.
명령은 다음과 같이 작동합니다.
1. `vacuumlo`는 데이터베이스에 있는 대형 객체의 모든 객체 ID(OID)가 포함된 임시 테이블을 만드는 것으로 시작합니다.
1. 그런 다음 `vacuumlo`는 데이터 유형 `oid` 또는 `lo`를 사용하는 데이터베이스의 모든 열을 스캔합니다. `vacuumlo`가 이러한 열에서 일치하는 OID를 찾으면 임시 테이블에서 OID를 제거합니다. `vacuumlo`는 이러한 유형을 기반으로 하는 도메인이 아니라 이름이 `oid` 또는 `lo`인 열만 검사합니다.
1. 임시 테이블의 나머지 항목은 고립된 LO를 나타내며, `vacuumlo`가 안전하게 제거합니다.
## `vacuumlo` 성능 개선
`-l` 옵션을 사용하여 배치 크기를 늘려 `vacuumlo`의 성능을 잠재적으로 개선할 수 있습니다. 이렇게 하면 `vacuumlo`가 한 번에 더 많은 LO를 처리할 수 있습니다.
시스템에 충분한 메모리가 있고 메모리에 임시 테이블을 완전히 수용할 수 있는 경우 데이터베이스 수준에서 `temp_buffers` 설정을 늘리면 성능이 향상될 수 있습니다. 이렇게 하면 테이블이 메모리에 완전히 상주할 수 있으므로 전체 성능이 향상될 수 있습니다.
다음 쿼리는 임시 테이블의 크기를 추정합니다.
```
SELECT
pg_size_pretty(SUM(pg_column_size(oid))) estimated_lo_temp_table_size
FROM
pg_largeobject_metadata;
```
## 대형 객체에 대한 고려 사항
다음은 대형 객체로 작업할 때 유의해야 할 몇 가지 중요한 고려 사항입니다.
+ 현재 고립된 LO를 제거하는 다른 방법이 없기 때문에 `Vacuumlo`가 유일한 솔루션입니다.
+ pglogical, 네이티브 논리적 복제, 복제 기술을 사용하는 AWS DMS와 같은 도구는 대형 객체 복제를 지원하지 않습니다.
+ 데이터베이스 스키마를 설계할 때는 가능하면 대형 객체를 사용하지 말고 `bytea`와 같은 대체 데이터 유형을 사용하는 것이 좋습니다.
+ 고립된 LO 관련 문제를 방지하려면 최소 매주 정기적으로 `vacuumlo`를 실행합니다.
+ 대형 객체를 저장하는 테이블에서 `lo_manage` 함수와 함께 트리거를 사용하면 고립된 LO가 만들어지는 것을 방지할 수 있습니다.
# PostGIS 확장을 사용하여 공간 데이터 관리
PostGIS는 공간 정보를 저장하고 관리하기 위해 PostgreSQL을 확장한 것입니다. PostGIS에 대한 자세한 내용은 [PostGIS.net](https://postgis.net/)을 참조하세요.
버전 10.5부터 PostgreSQL은 맵 상자 벡터 타일 데이터 작업을 위해 PostGIS에서 사용하는 libprotobuf 1.3.0 라이브러리를 지원합니다.
PostGIS 확장을 설정하려면 `rds_superuser` 권한이 필요합니다. PostGIS 확장 프로그램 및 공간 데이터를 관리할 사용자(역할)를 생성하는 것이 좋습니다. PostGIS 확장 프로그램 및 관련 구성 요소는 PostgreSQL에 수천 개의 함수를 추가합니다. 사용 사례에 적합한 경우 자체 스키마에서 PostGIS 확장 프로그램을 만드는 것이 좋습니다. 다음 예제에서는 자체 데이터베이스에 확장 프로그램을 설치하는 방법을 보여주지만, 필수 사항은 아닙니다.
**Topics**
+ [1단계: PostGIS 확장을 관리할 사용자(역할) 생성](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Connect)
+ [2단계: PostGIS 확장 모듈을 로드합니다.](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.LoadExtensions)
+ [3단계: 확장 스키마 소유권 이전](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferOwnership)
+ [4단계: PostGIS 테이블 소유권 이전](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferObjects)
+ [5단계: 확장 모듈을 테스트합니다.](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Test)
+ [6단계: PostGIS 확장 업그레이드](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Update)
+ [PostGIS 확장 버전](#CHAP_PostgreSQL.Extensions.PostGIS)
+ [PostGIS 2를 PostGIS 3으로 업그레이드](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3)
## 1단계: PostGIS 확장을 관리할 사용자(역할) 생성
먼저 `rds_superuser` 권한이 있는 사용자로 RDS for PostgreSQL DB 인스턴스에 연결합니다. 인스턴스를 설정할 때 기본 이름을 유지했다면 `postgres`로 연결합니다.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
PostGIS 확장을 관리하기 위해 별도의 역할(사용자)을 생성합니다.
```
postgres=> CREATE ROLE gis_admin LOGIN PASSWORD 'change_me';
CREATE ROLE
```
역할이 확장을 설치하도록 허용하려면 이 역할 `rds_superuser` 권한을 부여합니다.
```
postgres=> GRANT rds_superuser TO gis_admin;
GRANT
```
PostGIS 아티팩트에 사용할 데이터베이스를 만듭니다. 이 단계는 선택 사항입니다. 또는 PostGIS 확장을 위해 사용자 데이터베이스에 스키마를 생성할 수 있지만 이 역시 필수 사항은 아닙니다.
```
postgres=> CREATE DATABASE lab_gis;
CREATE DATABASE
```
`gis_admin`에게 `lab_gis` 데이터베이스에 대한 모든 권한을 부여합니다.
```
postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_gis TO gis_admin;
GRANT
```
세션을 종료하고 다음과 같이 RDS for PostgreSQL DB 인스턴스에 `gis_admin`으로 다시 연결합니다.
```
postgres=> psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=gis_admin --password --dbname=lab_gis
Password for user gis_admin:...
lab_gis=>
```
다음 단계에 설명된 대로 확장 프로그램을 계속 설정합니다.
## 2단계: PostGIS 확장 모듈을 로드합니다.
PostGIS 확장에는 지리 공간 기능을 제공하기 위해 함께 작동하는 여러 관련 확장이 포함되어 있습니다. 사용 사례에 따라 이 단계에서 만든 확장이 모두 필요하지 않을 수 있습니다.
`CREATE EXTENSION` 문을 사용하여 PostGIS 확장 모듈을 로드합니다.
```
CREATE EXTENSION postgis;
CREATE EXTENSION
CREATE EXTENSION postgis_raster;
CREATE EXTENSION
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION postgis_topology;
CREATE EXTENSION
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION
```
다음 예에 표시된 확장 및 해당 소유자가 나열된 SQL 쿼리를 실행하여 결과를 확인할 수 있습니다.
```
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+-----------
public | postgres
tiger | rdsadmin
tiger_data | rdsadmin
topology | rdsadmin
(4 rows)
```
## 3단계: 확장 스키마 소유권 이전
ALTER SCHEMA 문을 사용하여 스키마 소유권을 `gis_admin` 역할로 이전합니다.
```
ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO gis_admin;
ALTER SCHEMA
```
다음 SQL 쿼리를 실행하여 소유권 변경을 확인할 수 있습니다. 또는 psql 명령줄에서 `\dn` 메타 명령을 사용할 수 있습니다.
```
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+---------------
public | postgres
tiger | gis_admin
tiger_data | gis_admin
topology | gis_admin
(4 rows)
```
## 4단계: PostGIS 테이블 소유권 이전
**참고**
PostGIS 함수의 소유권을 변경하지 마세요. PostGIS를 올바르게 운영하고 향후 업그레이드하려면 이러한 함수가 원래 소유권을 유지해야 합니다. PostGIS 권한에 대한 자세한 내용은 [PostgreSQL 보안](https://postgis.net/workshops/postgis-intro/security.html)을 참조하세요.
다음 함수를 사용하여 PostGIS 테이블 소유권을 `gis_admin` 역할로 이전합니다. psql 프롬프트에서 다음 문을 실행하여 함수를 생성합니다.
```
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION
```
그런 다음 쿼리로 `exec` 함수를 실행하면 함수가 해당하는 문을 실행하여 권한을 변경합니다.
```
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname in ('tiger','topology') AND
relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
```
## 5단계: 확장 모듈을 테스트합니다.
스키마 이름을 지정할 필요가 없도록 하려면 다음 명령을 사용하여 검색 경로에 `tiger` 스키마를 추가하세요.
```
SET search_path=public,tiger;
SET
```
다음 SELECT 문을 사용하여 `tiger` 스키마를 테스트합니다.
```
SELECT address, streetname, streettypeabbrev, zip
FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev | zip
---------+------------+------------------+-------
1 | Devonshire | Pl | 02109
(1 row)
```
이 확장에 대한 자세한 내용은 PostGIS 문서에서 [Tiger Geocoder](https://postgis.net/docs/Extras.html#Tiger_Geocoder)를 참조하세요.
다음 `SELECT` 문을 사용하여 `topology` 스키마를 테스트합니다. 이렇게 하면 지정된 공간 참조 식별자(26986) 및 기본 허용 오차(0.5)를 사용하여 새 토폴로지 개체(my\$1new\$1topo)를 등록하는 `createtopology` 함수를 호출합니다. 자세한 내용은 PostgreSQL의 [CreateTopology](https://postgis.net/docs/CreateTopology.html) 문서를 참조하세요.
```
SELECT topology.createtopology('my_new_topo',26986,0.5);
createtopology
----------------
1
(1 row)
```
## 6단계: PostGIS 확장 업그레이드
PostgreSQL의 각 새 릴리스는 해당 릴리스와 호환되는 하나 이상의 PostGIS 확장 버전을 지원합니다. PostgreSQL 엔진을 새 버전으로 업그레이드해도 PostGIS 확장은 자동으로 업그레이드되지 않습니다. PostgreSQL 엔진을 업그레이드하려면 일반적으로 PostGIS를 현재 PostgreSQL 버전에서 사용 가능한 최신 버전으로 업그레이드를 먼저 해야 합니다. 자세한 내용은 [PostGIS 확장 버전](#CHAP_PostgreSQL.Extensions.PostGIS) 단원을 참조하세요.
PostgreSQL 엔진 업그레이드 후 PostGIS 확장을 새로 업그레이드된 PostgreSQL 엔진 버전에 대해 지원되는 버전으로 다시 업그레이드합니다. PostgreSQL 엔진 업그레이드에 대한 자세한 내용은 [새로운 메이저 버전으로 프로덕션 DB 클러스터 업그레이드 테스트](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary) 단원을 따라가세요.
Aurora PostgreSQL DB 클러스터 에서 사용 가능한 PostGIS 확장 버전 업데이트를 언제든지 확인할 수 있습니다. 이렇게 하려면 다음 명령을 실행합니다. 이 기능은 PostGIS 2.5.0 이상 버전에서 사용할 수 있습니다.
```
SELECT postGIS_extensions_upgrade();
```
애플리케이션이 최신 PostGIS 버전을 지원하지 않는 경우에도 다음과 같이 사용 중인 메이저 버전에서 사용할 수 있는 이전 버전의 PostGIS를 설치할 수 있습니다.
```
CREATE EXTENSION postgis VERSION "2.5.5";
```
이전 버전에서 특정 PostGIS 버전으로 업그레이드하려는 경우 다음 명령을 사용할 수도 있습니다.
```
ALTER EXTENSION postgis UPDATE TO "2.5.5";
```
업그레이드하는 버전에 따라 이 기능을 다시 사용해야 할 수도 있습니다. 기능의 첫 번째 실행 결과에 따라 추가 업그레이드 기능이 필요한지 여부가 결정됩니다. PostGIS 2에서 PostGIS 3으로 업그레이드하는 경우를 예로 들 수 있습니다. 자세한 내용은 [PostGIS 2를 PostGIS 3으로 업그레이드](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3)을 참조하세요.
PostgreSQL 엔진의 메이저 버전 업그레이드를 준비하기 위해 이 확장을 업그레이드했다면 다른 예비 작업을 계속할 수 있습니다. 자세한 내용은 [새로운 메이저 버전으로 프로덕션 DB 클러스터 업그레이드 테스트](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary) 단원을 참조하세요.
## PostGIS 확장 버전
*Aurora PostgreSQL 릴리스 정보*의 [Aurora PostgreSQL 호환 버전용 확장 버전](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html)에 나열된 PostGIS와 같은 모든 확장 버전을 설치하는 것이 좋습니다. *Amazon RDS for PostgreSQL 릴리스 정보*의 릴리스에서 사용 가능한 버전 목록을 얻으려면 다음 명령을 사용하세요.
```
SELECT * FROM pg_available_extension_versions WHERE name='postgis';
```
*Aurora PostgreSQL 릴리스 정보*의 다음 단원에서 버전 정보를 찾을 수 있습니다.
+ [Aurora PostgreSQL 확장 버전 14](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.14)
+ [Aurora PostgreSQL 호환 버전의 확장 버전 13](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.13)
+ [Aurora PostgreSQL 호환 버전의 확장 버전 12](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.12)
+ [Aurora PostgreSQL 호환 버전의 확장 버전 11](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.11)
+ [Aurora PostgreSQL 호환 버전의 확장 버전 10](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.10)
+ [Aurora PostgreSQL 호환 버전의 확장 버전 9.6](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.96)
## PostGIS 2를 PostGIS 3으로 업그레이드
버전 3.0부터 PostGIS 래스터 기능이 이제 별도의 확장인 `postgis_raster`입니다. 이 확장에는 자체 설치 및 업그레이드 경로가 있습니다. 이러한 경로를 통해 코어에서 핵심 `postgis` 확장에서 래스터 이미지 처리에 필요한 수십 가지 기능, 데이터 유형 및 기타 아티팩트를 제거합니다. 즉, 사용 사례에 래스터 처리가 필요하지 않은 경우 `postgis_raster` 확장을 설치할 필요가 없습니다.
다음 업그레이드 예에서 첫 번째 업그레이드 명령은 래스터 기능을 `postgis_raster` 확장으로 추출합니다. 그런 다음 `postgis_raster`를 새 버전으로 업그레이드하려면 두 번째 업그레이드 명령이 필요합니다.
**PostGIS 2에서 PostGIS 3으로 업그레이드하려면**
1. Aurora PostgreSQL DB 클러스터의 PostgreSQL 버전에 사용할 수 있는 PostGIS의 기본 버전을 식별합니다. 이렇게 하려면 다음 쿼리를 실행합니다.
```
SELECT * FROM pg_available_extensions
WHERE default_version > installed_version;
name | default_version | installed_version | comment
---------+-----------------+-------------------+------------------------------------------------------------
postgis | 3.1.4 | 2.3.7 | PostGIS geometry and geography spatial types and functions
(1 row)
```
1. Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에서 각 데이터베이스에 설치된 PostGIS 버전을 식별합니다. 즉, 다음과 같이 각 사용자 데이터베이스를 쿼리합니다.
```
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
---------+---------+--------+---------------------------------------------------------------------
postgis | 2.3.7 | public | PostGIS geometry, geography, and raster spatial types and functions
(1 row)
```
기본 버전(PostGIS 3.1.4)과 설치된 버전(PostGIS 2.3.7) 간의 이러한 불일치는 PostGIS 확장을 업그레이드해야 함을 의미합니다.
```
ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged
```
1. 다음 쿼리를 실행하여 래스터 기능이 이제 자체 패키지에 포함되어 있는지 확인합니다.
```
SELECT
probin,
count(*)
FROM
pg_proc
WHERE
probin LIKE '%postgis%'
GROUP BY
probin;
probin | count
--------------------------+-------
$libdir/rtpostgis-2.3 | 107
$libdir/postgis-3 | 487
(2 rows)
```
출력 결과를 통해 여전히 버전 간에 여전히 차이가 있음을 알 수 있습니다. PostGIS 함수는 버전 3(postgis-3)이고 래스터 함수(rtpostgis)는 버전 2(rtpostgis-2.3)입니다. 업그레이드를 완료하려면 다음과 같이 upgrade 명령을 다시 실행합니다.
```
postgres=> SELECT postgis_extensions_upgrade();
```
경고 메시지는 무시해도 됩니다. 다음 쿼리를 다시 실행하여 업그레이드가 완료되었는지 확인합니다. PostGIS 및 모든 관련 확장 프로그램이 업그레이드 필요 항목으로 표시되지 않으면 업그레이드가 완료된 것입니다.
```
SELECT postgis_full_version();
```
1. 다음 쿼리를 사용하여 완료된 업그레이드 프로세스와 별도로 패키징된 확장을 확인하고 해당 버전이 일치하는지 확인합니다.
```
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
----------------+---------+--------+---------------------------------------------------------------------
postgis | 3.1.5 | public | PostGIS geometry, geography, and raster spatial types and functions
postgis_raster | 3.1.5 | public | PostGIS raster types and functions
(2 rows)
```
출력은 PostGIS 2 확장이 PostGIS 3으로 업그레이드되었으며 `postgis`와 현재 별도의 `postgis_raster` 확장이 모두 버전 3.1.5임을 보여줍니다.
이 업그레이드가 완료된 후 래스터 기능을 사용하지 않으려는 경우 다음과 같이 확장을 삭제할 수 있습니다.
```
DROP EXTENSION postgis_raster;
```
# pg\$1partman 확장자를 사용하여 PostgreSQL 파티션 관리하기
PostgreSQL 테이블 파티셔닝은 데이터 입력 및보고의 고성능 처리를 위한 프레임워크를 제공합니다. 대량의 데이터를 매우 빠르게 입력해야 하는 데이터베이스의 경우 파티셔닝을 사용합니다. 파티셔닝은 큰 테이블의 빠른 쿼리를 제공합니다. 파티셔닝은 I/O 리소스가 적기 때문에 데이터베이스 인스턴스에 영향을 주지 않고 데이터를 유지 관리하는 데 도움이 됩니다.
파티셔닝을 사용하면 데이터를 사용자 지정 크기의 청크로 분할하여 처리할 수 있습니다. 예를 들어, 시간별, 일별, 주별, 월별, 분기별, 연도별, 사용자 지정 또는 이러한 조합과 같은 범위의 시계열 데이터를 분할할 수 있습니다. 시계열 데이터 예제의 경우 테이블을 시간별로 분할한 경우 각 파티션에는 1시간의 데이터가 포함됩니다. 시계열 테이블을 일별로 분할한 경우 파티션에는 하루 분량의 데이터가 저장되는 식입니다. 파티션 키는 파티션의 크기를 제어합니다.
분할된 테이블에서 `INSERT` 또는 `UPDATE` SQL 명령을 사용하는 경우 데이터베이스 엔진은 데이터를 적절한 파티션으로 라우팅합니다. 데이터를 저장하는 PostgreSQL 테이블 파티션은 기본 테이블의 하위 테이블입니다.
데이터베이스 쿼리를 읽는 동안 PostgreSQL 최적화 프로그램은 쿼리 `WHERE` 절을 검사하고 가능한 경우 데이터베이스 스캔을 관련 파티션에만 보냅니다.
버전 10부터, PostgreSQL은 선언적 분할을 사용하여 테이블 분할을 구현합니다. 이를 네이티브 PostgreSQL 파티셔닝이라고도 합니다. PostgreSQL 버전 10 이전에는 트리거를 사용하여 파티션을 구현했습니다.
PostgreSQL 테이블 분할은 다음과 같은 기능을 제공합니다:
+ 언제든지 새 파티션을 만들 수 있습니다.
+ 가변 분할 영역 범위입니다.
+ 데이터 정의 언어(DDL) 문을 사용하여 분리 및 재연결 가능한 파티션
예를 들어 분리 가능한 파티션은 주 파티션에서 기록 데이터를 제거하지만 분석을 위해 기록 데이터를 유지하는 데 유용합니다.
+ 새 파티션은 다음을 포함하여 상위 데이터베이스 테이블 속성을 상속합니다.
+ 인덱스
+ 파티션 키 열을 포함해야 하는 기본 키
+ 외래 키
+ 제약 점검
+ 참조
+ 전체 테이블 또는 각 특정 파티션에 대한 인덱스 생성
개별 파티션에 대한 스키마를 변경할 수 없습니다. 그러나 분할 영역에 전파되는 상위 테이블 (예: 새 열 추가) 을 변경할 수 있습니다.
**Topics**
+ [PostgreSQL pg\$1partman 확장 개요](#PostgreSQL_Partitions.pg_partman)
+ [pg\$1partman 확장 활성화](#PostgreSQL_Partitions.enable)
+ [create\$1parent 함수를 사용하여 파티션 구성](#PostgreSQL_Partitions.create_parent)
+ [run\$1maintenance\$1proc 함수를 사용하여 파티션 유지 관리 구성](#PostgreSQL_Partitions.run_maintenance_proc)
## PostgreSQL pg\$1partman 확장 개요
PostgreSQL `pg_partman` 확장을 사용하여 테이블 파티션의 생성 및 유지 관리를 자동화할 수 있습니다. 자세한 내용은 `pg_partman` 문서의 [PG 파티션 관리자](https://github.com/pgpartman/pg_partman)를 참조하세요.
**참고**
`pg_partman` 확장은 Aurora PostgreSQL 버전 12.6 이상에서 지원됩니다.
각 파티션을 수동으로 생성하지 않고 다음 설정으로 `pg_partman`을 구성합니다.
+ 분할할 테이블
+ 파티션 유형
+ 파티션 키
+ 분할 영역 세분성
+ 파티션 사전 생성 및 관리 옵션
PostgreSQL 분할 테이블을 만든 후, `create_parent` 함수를 호출하여 `pg_partman`에 등록합니다. 이렇게 하면 함수에 전달하는 파라미터를 기반으로 필요한 파티션이 생성됩니다.
`pg_partman` 확장은 예약된 시간에 호출하여 파티션을 자동으로 관리할 수 있는 `run_maintenance_proc` 함수도 제공합니다. 필요에 따라 적절한 파티션이 생성되도록 하려면 이 기능을 주기적으로 실행하도록 예약합니다(예: 매 시간). 파티션이 자동으로 삭제되게 할 수도 있습니다.
## pg\$1partman 확장 활성화
파티션을 관리하려는 동일한 PostgreSQL DB 인스턴스 내에 여러 개의 데이터베이스가 있는 경우 각 데이터베이스별로 별도로 `pg_partman` 확장을 활성화해야 합니다. 특정 데이터베이스에 대해 `pg_partman` 확장을 활성화하려면 파티션 유지 관리 스키마를 생성한 후 다음과 같이 `pg_partman` 확장을 생성합니다.
```
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
```
**참고**
`pg_partman` 확장을 만들려면 `rds_superuser` 권한이 있는지 확인하세요.
다음과 같은 오류가 발생하면 계정에 `rds_superuser` 권한을 부여하거나 수퍼유저 계정을 사용하십시오.
```
ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.
```
`rds_superuser` 권한을 부여하려면 수퍼유저 계정으로 연결하고 다음 명령을 실행합니다.
```
GRANT rds_superuser TO user-or-role;
```
pg\$1partman 확장 사용 방법을 보여 주는 예제에서는 다음 예제 데이터베이스 테이블 및 파티션을 사용합니다. 이 데이터베이스는 타임스탬프를 기반으로 분할된 테이블을 사용합니다. `data_mart` 스키마에는 라는 열이 `events` 있는 테이블이 포함되어 `created_at`있습니다. 이 `events` 테이블에는 다음과 같은 설정이 포함되어 있습니다.
+ 기본 키 `event_id` 및 `created_at`파티션을 안내하는 데 사용되는 열이 있어야 합니다.
+ `ck_valid_operation` 테이블 열에 대한 값을 `operation` 적용하는 CHECK 제약 조건.
+ 두 개의 외래 키. 하나는 외부 테이블을`fk_orga_membership)` `organization` 가리키고 다른 하나는 자체 참조 외래 키입니다.`fk_parent_event_id`
+ 두 개의 인덱스. 여기서 하나 (`idx_org_id`) 는 외래 키용이고 다른 인덱스 (`idx_event_type`) 는 이벤트 유형용입니다.
다음 DDL 명령문은 각 파티션에 자동으로 포함되는 이러한 객체를 만듭니다.
```
CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
org_name TEXT,
CONSTRAINT pk_organization PRIMARY KEY (org_id)
);
CREATE TABLE data_mart.events(
event_id BIGSERIAL,
operation CHAR(1),
value FLOAT(24),
parent_event_id BIGINT,
event_type VARCHAR(25),
org_id BIGSERIAL,
created_at timestamp,
CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at),
CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'),
CONSTRAINT fk_orga_membership
FOREIGN KEY(org_id)
REFERENCES data_mart.organization (org_id),
CONSTRAINT fk_parent_event_id
FOREIGN KEY(parent_event_id, created_at)
REFERENCES data_mart.events (event_id,created_at)
) PARTITION BY RANGE (created_at);
CREATE INDEX idx_org_id ON data_mart.events(org_id);
CREATE INDEX idx_event_type ON data_mart.events(event_type);
```
## create\$1parent 함수를 사용하여 파티션 구성
`pg_partman` 확장을 활성화한 후에는 `create_parent` 함수를 사용하여 파티션 유지 관리 스키마 내에 파티션을 구성합니다. 다음 예에서는 `events`에서 만든 [pg\$1partman 확장 활성화run\$1maintenance\$1proc 함수를 사용하여 파티션 유지 관리 구성](#PostgreSQL_Partitions.enable) 테이블 예제를 사용합니다. `create_parent` 함수를 다음과 같이 호출합니다.
```
SELECT partman.create_parent(
p_parent_table => 'data_mart.events',
p_control => 'created_at',
p_type => 'range',
p_interval => '1 day',
p_premake => 30);
```
파라미터는 다음과 같습니다.
+ `p_parent_table` – 상위 분할 테이블입니다. 이 테이블은 이미 존재해야 하며 스키마를 포함하여 정규화되어야 합니다.
+ `p_control` – 분할이 기반으로되는 열입니다. 데이터 유형은 정수 또는 시간 기반이어야 합니다.
+ `p_type` - 유형은 `'range'` 또는 `'list'`입니다.
+ `p_interval` – 각 파티션에 대한 시간 간격 또는 정수 범위입니다. 값의 예로는 `1 day`, `1 hour` 등이 포함됩니다.
+ `p_premake` – 새 삽입을 지원하기 위해 미리 생성할 분할 영역 수입니다.
`create_parent` 함수에 대한 자세한 설명은 `pg_partman` 설명서에서 [생성 함수](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#user-content-creation-functions)를 참조하세요.
## run\$1maintenance\$1proc 함수를 사용하여 파티션 유지 관리 구성
파티션 유지 관리 작업을 실행하여 자동으로 새 파티션을 생성하거나, 파티션을 분리하거나, 이전 파티션을 제거할 수 있습니다. 파티션 유지 관리에는 내부 스케줄러를 시작하는 `pg_partman` 확장 및 `pg_cron` 확장의 `run_maintenance_proc` 함수가 사용됩니다. `pg_cron` 스케줄러는 데이터베이스에 정의된 SQL 문, 함수 및 프로시저를 자동으로 실행합니다.
다음 예제에서는 에서 만든 `events` 테이블 예제를 [pg\$1partman 확장 활성화run\$1maintenance\$1proc 함수를 사용하여 파티션 유지 관리 구성](#PostgreSQL_Partitions.enable) 사용하여 파티션 유지 관리 작업이 자동으로 실행되도록 설정합니다. 전제 조건으로 `pg_cron`을 DB 인스턴스의 파라미터 그룹의 `shared_preload_libraries` 파라미터에 추가합니다.
```
CREATE EXTENSION pg_cron;
UPDATE partman.part_config
SET infinite_time_partitions = true,
retention = '3 months',
retention_keep_table=true
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);
```
다음으로, 앞의 예제에 대한 단계별 설명을 찾을 수 있습니다.
1. DB 인스턴스와 연결된 파라미터 그룹을 수정하고 `pg_cron` 파라미터 값에 `shared_preload_libraries` 추가합니다. 이 변경 사항을 적용하려면 DB 인스턴스를 다시 시작해야 합니다. 자세한 내용은 [Amazon Aurora에서 DB 파라미터 그룹의 파라미터 수정](USER_WorkingWithParamGroups.Modifying.md) 섹션을 참조하세요.
1. `CREATE EXTENSION pg_cron;` 권한이 있는 계정을 사용하여 `rds_superuser` 명령을 실행합니다. 이렇게 하면 `pg_cron` 확장이 활성화됩니다. 자세한 내용은 [PostgreSQL pg\$1cron 확장을 사용하여 유지 관리 예약](PostgreSQL_pg_cron.md) 섹션을 참조하세요.
1. `UPDATE partman.part_config` 명령을 실행하여 `data_mart.events` 테이블에 대한 `pg_partman` 설정을 조정합니다.
1. `SET` 명령을 실행합니다. `data_mart.events` 테이블을 구성하려면 다음 절을 함께 사용합니다.
1. `infinite_time_partitions = true,` – 제한 없이 자동으로 새 파티션을 만들 수 있도록 테이블을 구성합니다.
1. `retention = '3 months',` – 최대 보존 기간이 3개월이 되도록 테이블을 구성합니다.
1. `retention_keep_table=true `– 보존 기간이 만료될 때 테이블이 자동으로 삭제되지 않도록 테이블을 구성합니다. 대신 보존 기간보다 오래된 파티션은 상위 테이블에서만 분리됩니다.
1. `SELECT cron.schedule` 명령을 실행합니다. `pg_cron` 함수를 호출하려면 이 호출은 스케줄러가 `pg_partman` 유지 관리 프로시저 `partman.run_maintenance_proc`를 실행하는 빈도를 정의합니다. 이 예제에서는 프로시저가 매 시간 실행됩니다.
`run_maintenance_proc` 함수에 대한 자세한 설명은 `pg_partman` 설명서에서 [유지 관리 함수](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#maintenance-functions)를 참조하세요.
# PostgreSQL pg\$1cron 확장을 사용하여 유지 관리 예약
PostgreSQL `pg_cron` 확장을 사용하여 PostgreSQL 데이터베이스 내에서 유지 관리 명령을 예약할 수 있습니다. 확장에 대한 자세한 내용은 pg\$1cron 설명서의 [pg\$1cron이란 무엇입니까?](https://github.com/citusdata/pg_cron) 섹션을 참조하십시오.
`pg_cron` 확장은 Aurora PostgreSQL 엔진 버전 12.6 이상에서 지원됩니다.
`pg_cron` 사용에 대한 자세한 내용은 [RDS for PostgreSQL 또는 Aurora PostgreSQL 호환 에디션 데이터베이스에서 pg\$1cron을 사용하여 작업 예약](https://aws.amazon.com/blogs/database/schedule-jobs-with-pg_cron-on-your-amazon-rds-for-postgresql-or-amazon-aurora-for-postgresql-databases/)을 참조하세요.
**참고**
`pg_cron` 확장 버전은 pg\$1available\$1extensions 보기에 1.6과 같은 2자리 버전으로 표시됩니다. 일부 컨텍스트에 나열된 1.6.4 또는 1.6.5와 같은 세 자리 버전이 표시될 수 있지만, 확장 업그레이드를 수행할 때 두 자리 버전을 지정해야 합니다.
**Topics**
+ [pg\$1cron 확장 설정](#PostgreSQL_pg_cron.enable)
+ [데이터베이스 사용자에게 pg\$1cron 사용 권한 부여](#PostgreSQL_pg_cron.permissions)
+ [pg\$1cron 작업 예약](#PostgreSQL_pg_cron.examples)
+ [pg\$1cron 확장에 대한 참조](#PostgreSQL_pg_cron.reference)
## pg\$1cron 확장 설정
다음과 같이 `pg_cron` 확장을 설정합니다.
1. `shared_preload_libraries` 파라미터 값에 `pg_cron` 을 추가하여 PostgreSQL DB 인스턴스와 연결된 사용자 지정 파라미터 그룹을 수정합니다.
파라미터 그룹에 대한 변경 사항을 적용하려면 PostgreSQL DB 인스턴스를 다시 시작합니다. 파라미터 그룹 사용에 대한 자세한 내용은 [Amazon Aurora PostgreSQL parameters](AuroraPostgreSQL.Reference.ParameterGroups.md). 섹션을 참조하십시오.
1. PostgreSQL DB 인스턴스가 다시 시작된 후 `rds_superuser` 권한이 있는 계정을 사용하여 다음 명령을 수행합니다. 예를 들어 Aurora PostgreSQL DB 클러스터를 생성할 때 기본 설정을 사용한 경우 사용자 `postgres`로 연결하고 확장을 생성합니다.
```
CREATE EXTENSION pg_cron;
```
`pg_cron` 스케줄러는 `postgres`라는 기본 PostgreSQL 데이터베이스에 설정됩니다. `pg_cron` 객체가 이 `postgres` 데이터베이스에 생성되고 모든 예약 작업이 이 데이터베이스에서 실행됩니다.
1. 기본 설정을 사용하거나 PostgreSQL DB 인스턴스 내의 다른 데이터베이스에서 실행되도록 작업을 예약할 수 있습니다. PostgreSQL DB 인스턴스 내의 다른 데이터베이스에 대한 작업을 예약하려면 [기본 데이터베이스 이외의 데이터베이스에 대한 cron 작업 예약](#PostgreSQL_pg_cron.otherDB)의 예제를 참조하세요.
## 데이터베이스 사용자에게 pg\$1cron 사용 권한 부여
`pg_cron` 확장을 설치하려면 `rds_superuser` 권한이 필요합니다. 그러나 `pg_cron` 사용 권한은 (`rds_superuser` 그룹/역할의 구성원에 의해) 다른 데이터베이스 사용자에게 부여되어 자신의 작업을 예약할 수 있습니다. 프로덕션 환경에서 작업을 개선하는 경우 필요한 경우에만 `cron` 스키마에 권한을 부여하는 것이 좋습니다.
`cron` 스키마에서 데이터베이스 사용자 권한을 부여하려면 다음 명령을 실행합니다.
```
postgres=> GRANT USAGE ON SCHEMA cron TO db-user;
```
이렇게 하면 액세스 권한이 있는 개체에 대해 cron 작업을 예약하기 위해 `cron` 스키마에 액세스할 수 있는 권한이 *db-user*에게 부여됩니다. 데이터베이스 사용자에게 권한이 없으면 다음과 같이 `postgresql.log` 파일에 오류 메시지를 게시한 후 작업이 실패합니다.
```
2020-12-08 16:41:00 UTC::@:[30647]:ERROR: permission denied for table table-name
2020-12-08 16:41:00 UTC::@:[27071]:LOG: background worker "pg_cron" (PID 30647) exited with exit code 1
```
즉, `cron` 스키마에 대한 사용 권한이 부여된 데이터베이스 사용자는 예약하려는 개체(테이블, 스키마 등)에 대한 사용 권한도 있어야 합니다.
cron 작업 및 성공 또는 실패에 대한 세부 정보도 `cron.job_run_details` 테이블에 캡처됩니다. 자세한 내용은 [작업 예약 및 상태 캡처를 위한 테이블](#PostgreSQL_pg_cron.tables) 섹션을 참조하세요.
## pg\$1cron 작업 예약
다음 단원에서는 `pg_cron` 작업을 사용하여 다양한 관리 태스크를 예약하는 방법을 보여 줍니다.
**참고**
`pg_cron` 작업을 생성할 때 `max_worker_processes` 설정이 `cron.max_running_jobs` 개수보다 큰지 확인하십시오. 백그라운드 작업자 프로세스가 부족하면 `pg_cron` 작업은 실패합니다. 기본 `pg_cron` 작업 수는 `5`입니다. 자세한 내용은 [pg\$1cron 확장을 관리하기 위한 파라미터](#PostgreSQL_pg_cron.parameters) 섹션을 참조하십시오.
**Topics**
+ [테이블 베큠](#PostgreSQL_pg_cron.vacuum)
+ [pg\$1cron 기록 테이블 지우기](#PostgreSQL_pg_cron.job_run_details)
+ [postgresql.log 파일에만 오류 로깅](#PostgreSQL_pg_cron.log_run)
+ [기본 데이터베이스 이외의 데이터베이스에 대한 cron 작업 예약](#PostgreSQL_pg_cron.otherDB)
### 테이블 베큠
Autovacuum은 대부분의 경우 정리 유지 관리를 처리합니다. 하지만 선택한 시간에 특정 테이블을 베큠하도록 예약해야 하는 경우도 있습니다.
다음은 매일 22:00(GMT)에 특정 테이블에 `cron.schedule`를 사용하는 작업을 설정하는 `VACUUM FREEZE` 함수의 사용 예입니다.
```
SELECT cron.schedule('manual vacuum', '0 22 * * *', 'VACUUM FREEZE pgbench_accounts');
schedule
----------
1
(1 row)
```
앞의 예제를 실행한 후, 다음과 같이 `cron.job_run_details` 테이블에서 기록을 확인할 수 있습니다.
```
postgres=> SELECT * FROM cron.job_run_details;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+--------------------------------+-----------+----------------+-------------------------------+-------------------------------
1 | 1 | 3395 | postgres | adminuser| vacuum freeze pgbench_accounts | succeeded | VACUUM | 2020-12-04 21:10:00.050386+00 | 2020-12-04 21:10:00.072028+00
(1 row)
```
다음은 실패한 작업을 확인하기 위한 `cron.job_run_details` 테이블의 쿼리입니다.
```
postgres=> SELECT * FROM cron.job_run_details WHERE status = 'failed';
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
------+-------+---------+----------+----------+-------------------------------+--------+--------------------------------------------------+-------------------------------+------------------------------
5 | 4 | 30339 | postgres | adminuser| vacuum freeze pgbench_account | failed | ERROR: relation "pgbench_account" does not exist | 2020-12-04 21:48:00.015145+00 | 2020-12-04 21:48:00.029567+00
(1 row)
```
자세한 내용은 [작업 예약 및 상태 캡처를 위한 테이블](#PostgreSQL_pg_cron.tables) 섹션을 참조하세요.
### pg\$1cron 기록 테이블 지우기
`cron.job_run_details` 테이블에는 시간이 지남에 따라 매우 커질 수 있는 cron 작업 기록이 포함되어 있습니다. 이 테이블을 지우는 작업을 예약하는 것이 좋습니다. 예를 들어, 1 주일 분량의 항목을 보관하면 문제 해결을 위해 충분할 수 있습니다.
다음 예에서는 [cron.schedule](#PostgreSQL_pg_cron.schedule) 함수를 사용하여, 매일 자정에 실행되어 `cron.job_run_details` 테이블을 지우는 작업을 예약합니다. 이 작업은 지난 7 일 동안의 항목만 유지합니다. `rds_superuser` 계정을 사용하여 다음과 같은 작업을 예약합니다.
```
SELECT cron.schedule('0 0 * * *', $$DELETE
FROM cron.job_run_details
WHERE end_time < now() - interval '7 days'$$);
```
자세한 내용은 [작업 예약 및 상태 캡처를 위한 테이블](#PostgreSQL_pg_cron.tables) 섹션을 참조하세요.
### postgresql.log 파일에만 오류 로깅
`cron.job_run_details` 테이블에 대한 쓰기 작업을 완전히 차단하려면 PostgreSQL DB 인스턴스와 연결된 파라미터 그룹을 수정하고 `cron.log_run` 파라미터를 '꺼짐'으로 설정합니다. `pg_cron` 확장이 더 이상 테이블에 기록하지 않고 `postgresql.log` 파일에만 오류를 캡처합니다. 자세한 내용은 [Amazon Aurora에서 DB 파라미터 그룹의 파라미터 수정](USER_WorkingWithParamGroups.Modifying.md) 섹션을 참조하세요.
다음 명령을 사용하여 `cron.log_run` 파라미터 값을 확인합니다.
```
postgres=> SHOW cron.log_run;
```
자세한 내용은 [pg\$1cron 확장을 관리하기 위한 파라미터](#PostgreSQL_pg_cron.parameters) 섹션을 참조하세요.
### 기본 데이터베이스 이외의 데이터베이스에 대한 cron 작업 예약
`pg_cron`의 메타데이터는 모두 `postgres`라는 PostgreSQL 기본 데이터베이스에 보관됩니다. 백그라운드 작업자는 유지 관리 cron 작업을 실행하는 데 사용되므로 PostgreSQL DB 인스턴스 내의 모든 데이터베이스에서 작업을 예약할 수 있습니다.
**참고**
`rds_superuser` 역할 또는 `rds_superuser` 권한이 있는 사용자만 데이터베이스의 모든 cron 작업을 나열할 수 있습니다. 다른 사용자는 `cron.job` 테이블에서 자신의 작업만 볼 수 있습니다.
1. cron 데이터베이스에서 정상적으로 [cron.schedule](#PostgreSQL_pg_cron.schedule)을(를) 사용하는 것처럼 작업을 예약합니다.
```
postgres=> SELECT cron.schedule('database1 manual vacuum', '29 03 * * *', 'vacuum freeze test_table');
```
1. `rds_superuser` 역할을 가진 사용자는 방금 생성한 작업에 대한 데이터베이스 열을 업데이트하여 PostgreSQL DB 인스턴스 내의 다른 데이터베이스에서 실행되도록 합니다.
```
postgres=> UPDATE cron.job SET database = 'database1' WHERE jobid = 106;
```
1. `cron.job` 테이블을 쿼리하여 확인합니다.
```
postgres=> SELECT * FROM cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
------+-------------+--------------------------------+-----------+----------+----------+-----------+--------+-------------------------
106 | 29 03 * * * | vacuum freeze test_table | localhost | 8192 | database1| adminuser | t | database1 manual vacuum
1 | 59 23 * * * | vacuum freeze pgbench_accounts | localhost | 8192 | postgres | adminuser | t | manual vacuum
(2 rows)
```
**참고**
경우에 따라, 다른 데이터베이스에서 실행하고자 하는 cron 작업을 추가할 수 있습니다. 이 경우, 올바른 데이터베이스 열을 업데이트하기 전에 작업이 기본 데이터베이스(`postgres`)에서 실행을 시도할 수 있습니다. 사용자 이름에 권한이 있으면 작업이 기본 데이터베이스에서 성공적으로 실행됩니다.
## pg\$1cron 확장에 대한 참조
`pg_cron` 확장과 함께 다음 파라미터, 함수 및 테이블을 사용할 수 있습니다. 자세한 내용은 pg\$1cron 설명서의 [pg\$1cron이란 무엇입니까?](https://github.com/citusdata/pg_cron) 섹션을 참조하세요.
**Topics**
+ [pg\$1cron 확장을 관리하기 위한 파라미터](#PostgreSQL_pg_cron.parameters)
+ [함수 참조: cron.schedule](#PostgreSQL_pg_cron.schedule)
+ [함수 참조: cron.unschedule](#PostgreSQL_pg_cron.unschedule)
+ [작업 예약 및 상태 캡처를 위한 테이블](#PostgreSQL_pg_cron.tables)
### pg\$1cron 확장을 관리하기 위한 파라미터
다음은 `pg_cron` 확장 동작을 제어하는 파라미터의 목록입니다.
| 파라미터 | 설명 |
| --- | --- |
| cron.database\$1name | `pg_cron` 메타데이터가 보관되는 데이터베이스입니다. |
| cron.host | PostgreSQL에 연결할 호스트 이름입니다. 이 값은 수정할 수 없습니다. |
| cron.log\$1run | `job_run_details` 테이블에서 실행되는 모든 작업을 로깅합니다. 유효한 값은 `on` 또는 `off`입니다. 자세한 내용은 [작업 예약 및 상태 캡처를 위한 테이블](#PostgreSQL_pg_cron.tables) 섹션을 참조하세요. |
| cron.log\$1statement | 모든 cron 문을 실행하기 전에 기록합니다. 유효한 값은 `on` 또는 `off`입니다. |
| cron.max\$1running\$1jobs | 동시에 실행할 수 있는 최대 작업 수입니다. |
| cron.use\$1background\$1workers | 클라이언트 세션 대신 백그라운드 작업자를 사용합니다. 이 값은 수정할 수 없습니다. |
다음 SQL 명령을 사용하여 이러한 파라미터와 해당 값을 표시합니다.
```
postgres=> SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;
```
### 함수 참조: cron.schedule
이 함수는 cron 작업을 예약합니다. 작업은 처음에 기본 `postgres` 데이터베이스에서 예약됩니다. 이 함수는 작업 식별자를 나타내는 `bigint` 값을 반환합니다. PostgreSQL DB 인스턴스 내의 다른 데이터베이스에서 작업이 실행되도록 예약하려면 [기본 데이터베이스 이외의 데이터베이스에 대한 cron 작업 예약](#PostgreSQL_pg_cron.otherDB)의 예제를 참조하세요.
이 함수에는 두 가지 구문 형식이 있습니다.
**구문**
```
cron.schedule (job_name,
schedule,
command
);
cron.schedule (schedule,
command
);
```
**파라미터**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html)
**예제**
```
postgres=> SELECT cron.schedule ('test','0 10 * * *', 'VACUUM pgbench_history');
schedule
----------
145
(1 row)
postgres=> SELECT cron.schedule ('0 15 * * *', 'VACUUM pgbench_accounts');
schedule
----------
146
(1 row)
```
### 함수 참조: cron.unschedule
이 함수는 cron 작업을 삭제합니다. `job_name` 또는 `job_id`를 지정할 수 있습니다. 정책은 사용자가 작업 일정을 제거할 수 있는 소유자인지를 확인합니다. 이 함수는 성공 또는 실패를 나타내는 부울 값을 반환합니다.
함수의 구문 형식은 다음과 같습니다.
**구문**
```
cron.unschedule (job_id);
cron.unschedule (job_name);
```
**파라미터**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/PostgreSQL_pg_cron.html)
**예제**
```
postgres=> SELECT cron.unschedule(108);
unschedule
------------
t
(1 row)
postgres=> SELECT cron.unschedule('test');
unschedule
------------
t
(1 row)
```
### 작업 예약 및 상태 캡처를 위한 테이블
다음 표는 cron 작업을 예약하고 작업 완료 방법을 기록하는 데 사용됩니다.
| 표 | 설명 |
| --- | --- |
| cron.job | 예약된 각 작업에 대한 메타데이터를 포함합니다. 이 테이블과의 대부분의 상호 작용은 `cron.schedule` 및 `cron.unschedule` 함수를 사용하여 수행해야 합니다. 이 테이블에 직접 업데이트 또는 삽입 권한을 부여하지 않는 것이 좋습니다. 이렇게 하면 사용자가 `username`(으)로 실행되도록 `rds-superuser` 열을 업데이트할 수 있습니다. |
| cron.job\$1run\$1details | 이전에 예약된 작업 실행에 대한 기록 정보를 포함합니다. 이는 실행한 작업에서 상태, 반환 메시지, 시작 및 종료 시간을 조사하는 데 유용합니다. 이 테이블이 무한정 증가하지 않게 하려면 정기적으로 삭제하세요. 관련 예제는 [pg\$1cron 기록 테이블 지우기](#PostgreSQL_pg_cron.job_run_details) 섹션을 참조하세요 |
# pgAudit를 사용하여 데이터베이스 활동 로깅
금융 기관, 정부 기관 및 많은 업계에서는 규제 요구 사항을 충족하기 위해 감사 로그를** 보관해야 합니다. Aurora PostgreSQL DB 클러스터와 함께 PostgreSQL Audit 확장(pgAudit)을 사용하면 감사자가 일반적으로 필요로 하거나 규제 요구 사항을 충족하는 데 필요한 세부 레코드를 캡처할 수 있습니다. 예를 들어 특정 데이터베이스 및 테이블의 변경 내용을 추적하고 변경한 사용자 및 기타 여러 세부 정보를 기록하도록 pgAudit 확장을 설정할 수 있습니다.
pgAudit 확장은 네이티브 PostgreSQL 로깅 인프라의 기능을 기반으로 로그 메시지를 더 자세히 확장한 것입니다. 즉, 다른 로그 메시지를 보는 것과 동일한 접근 방식을 사용하여 감사 로그를 볼 수 있습니다. PostgreSQL 로깅에 대한 자세한 내용은 [Aurora PostgreSQL 데이터베이스 로그 파일](USER_LogAccess.Concepts.PostgreSQL.md) 섹션을 참조하세요.
pgAudit 확장은 일반 텍스트 암호와 같은 민감한 데이터를 로그에서 삭제합니다. Aurora PostgreSQL DB 클러스터가 [Aurora PostgreSQL DB 클러스터 에 쿼리 로깅을 활성화합니다.](USER_LogAccess.Concepts.PostgreSQL.Query_Logging.md)에 설명된 대로 데이터 조작 언어(DML) 문을 로깅하도록 구성된 경우 PostgreSQL Audit 확장을 사용하여 일반 텍스트 암호 문제를 방지할 수 있습니다.
매우 구체적으로 데이터베이스 인스턴스에 대한 감사를 구성할 수 있습니다. 모든 데이터베이스와 모든 사용자를 감사할 수 있습니다. 또는 특정 데이터베이스, 사용자 및 기타 객체만 감사하도록 선택할 수 있습니다. 특정 사용자 및 데이터베이스를 감사에서 명시적으로 제외할 수도 있습니다. 자세한 내용은 [감사 로깅에서 사용자 또는 데이터베이스 제외](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md) 단원을 참조하십시오.
캡처할 수 있는 세부 정보의 양을 고려하여 pgAudit를 사용하는 경우 스토리지 사용량을 모니터링하는 것이 좋습니다.
pgAudit 확장은 사용 가능한 모든 Aurora PostgreSQL 버전에서 지원됩니다. Aurora PostgreSQL 버전에서 지원되는 pgAudit 버전 목록을 보려면 **Aurora PostgreSQL 릴리스 정보의 [Extension versions for Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html)(Amazon Aurora PostgreSQL 확장 버전)을 참조하세요.
**Topics**
+ [pgAudit 확장 설정](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md)
+ [데이터베이스 객체 감사](Appendix.PostgreSQL.CommonDBATasks.pgaudit.auditing.md)
+ [감사 로깅에서 사용자 또는 데이터베이스 제외](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md)
+ [pgAudit 확장 프로그램에 대한 참조](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md)
# pgAudit 확장 설정
Aurora PostgreSQL DB 클러스터에 pgAudit 확장을 설정하려면 먼저 Aurora PostgreSQL DB 클러스터용 사용자 지정 DB 클러스터 파라미터 그룹의 공유 라이브러리에 pgAudit을 추가해야 합니다. 사용자 지정 DB 클러스터 파라미터 그룹을 만드는 방법에 관한 자세한 내용은 [Amazon Aurora의 파라미터 그룹](USER_WorkingWithParamGroups.md) 섹션을 참조하세요. 다음으로 pgAudit 확장을 설치합니다. 마지막으로, 감사하려는 데이터베이스 및 객체를 지정합니다. 이 섹션에 절차가 설명되어 있습니다. AWS Management Console 또는 AWS CLI를 사용할 수 있습니다.
이 모든 작업을 수행하려면 `rds_superuser` 역할의 권한이 있어야 합니다.
다음 단계에서는 사용자의 Aurora PostgreSQL DB 클러스터가 사용자 지정 DB 클러스터에 연결되어 있다고 가정합니다.
## 콘솔
**pgAudit 확장 설정 방법**
1. [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)에서 AWS Management Console에 로그인한 후 Amazon RDS 콘솔을 엽니다.
1. 탐색 창에서 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 선택합니다.
1. Aurora PostgreSQL DB 클러스터 라이터 인스턴스의 **구성** 탭을 엽니다. 인스턴스 세부 정보 중에서 **파라미터 그룹** 링크를 찾습니다.
1. 링크를 선택하여 Aurora PostgreSQL DB 클러스터와 연결된 사용자 지정 파라미터를 엽니다.
1. **파라미터** 검색 필드에 `shared_pre`를 입력하여 `shared_preload_libraries` 파라미터를 찾습니다.
1. **파라미터 편집**을 선택하여 속성 값에 액세스합니다.
1. **값** 필드의 목록에 `pgaudit`를 추가합니다. 쉼표를 사용하여 값 목록에서 항목을 구분합니다.
![\[pgAudit가 추가된 shared_preload_libaries 파라미터 이미지.\]](http://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pgaudit.png)
1. Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 재부팅하여 `shared_preload_libraries` 파라미터 변경 사항이 적용되도록 합니다.
1. 인스턴스를 사용할 수 있게 되면 pgAudit가 초기화되었는지 확인합니다. `psql`을 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 연결하고 다음 명령을 실행합니다.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
1. pgAudit가 초기화되었으므로 이제 확장을 생성할 수 있습니다. 확장은 라이브러리를 초기화한 후에 생성해야 합니다. `pgaudit` 확장이 데이터 정의 언어(DDL) 문 감사를 위한 이벤트 트리거를 설치하기 때문입니다.
```
CREATE EXTENSION pgaudit;
```
1. `psql` 세션을 닫습니다.
```
labdb=> \q
```
1. [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)에서 AWS Management Console에 로그인한 후 Amazon RDS 콘솔을 엽니다.
1. 목록에서 `pgaudit.log` 파라미터를 찾아 사용 사례에 적합한 값으로 설정합니다. 예를 들어, 다음 이미지처럼 `pgaudit.log` 파라미터를 `write`로 설정하면 로그에 대한 삽입, 업데이트, 삭제 및 기타 유형의 변경 사항이 캡처됩니다.
![\[pgaudit.log 파라미터의 설정 이미지.\]](http://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/images/rpg_set_pgaudit-log-level.png)
`pgaudit.log` 파라미터에 다음 값 중 하나를 선택할 수도 있습니다.
+ 없음 - 기본값입니다. 데이터베이스 변경 사항이 로깅되지 않습니다.
+ 모두 - 모든 항목(읽기, 쓰기, 함수, 역할, ddl, 기타)을 로깅합니다.
+ ddl - `ROLE` 클래스에 포함되지 않은 모든 데이터 정의 언어(DDL) 문을 로깅합니다.
+ 함수 - 함수 호출 및 `DO` 블록을 로깅합니다.
+ 기타 – 기타 명령(예: `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM`, `SET`)을 로깅합니다.
+ 읽기 – 원본이 관계(예: 테이블) 또는 쿼리인 경우 `SELECT` 및 `COPY`를 로깅합니다.
+ 역할 - 역할 및 권한과 관련된 문을 로깅합니다(예: `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE`, `DROP ROLE`).
+ 쓰기 - 대상이 관계(테이블)인 경우 `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `COPY`를 로깅합니다.
1. **Save changes**(변경 사항 저장)를 선택합니다.
1. [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)에서 Amazon RDS 콘솔을 엽니다.
1. 데이터베이스 목록에서 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스 를 선택합니다.
## AWS CLI
**pgAudit 설정 방법**
AWS CLI를 사용하여 pgAudit를 설정하려면 다음 절차와 같이 [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) 작업을 호출하여 사용자 지정 파라미터 그룹의 감사 로그 파라미터를 수정합니다.
1. 다음 AWS CLI 명령을 사용하여 `shared_preload_libraries` 파라미터에 `pgaudit`를 추가합니다.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. 다음 AWS CLI 명령으로 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 재부팅하여 pgaudit 라이브러리가 초기화되도록 합니다.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. 인스턴스를 사용할 수 있게 되면 `pgaudit`가 초기화되었는지 확인할 수 있습니다. `psql`을 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 연결하고 다음 명령을 실행합니다.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
pgAudit가 초기화되었으므로 이제 확장을 생성할 수 있습니다.
```
CREATE EXTENSION pgaudit;
```
1. AWS CLI를 사용할 수 있도록 `psql` 세션을 닫습니다.
```
labdb=> \q
```
1. 다음 AWS CLI 명령을 사용하여 세션 감사 로깅을 통해 로깅할 문의 클래스를 지정합니다. 이 예에서는 `pgaudit.log` 파라미터를 `write`로 설정하여 로그에 대한 삽입, 업데이트 및 삭제를 캡처합니다.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.log,ParameterValue=write,ApplyMethod=pending-reboot" \
--region aws-region
```
`pgaudit.log` 파라미터에 다음 값 중 하나를 선택할 수도 있습니다.
+ 없음 - 기본값입니다. 데이터베이스 변경 사항이 로깅되지 않습니다.
+ 모두 - 모든 항목(읽기, 쓰기, 함수, 역할, ddl, 기타)을 로깅합니다.
+ ddl - `ROLE` 클래스에 포함되지 않은 모든 데이터 정의 언어(DDL) 문을 로깅합니다.
+ 함수 - 함수 호출 및 `DO` 블록을 로깅합니다.
+ 기타 – 기타 명령(예: `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM`, `SET`)을 로깅합니다.
+ 읽기 – 원본이 관계(예: 테이블) 또는 쿼리인 경우 `SELECT` 및 `COPY`를 로깅합니다.
+ 역할 - 역할 및 권한과 관련된 문을 로깅합니다(예: `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE`, `DROP ROLE`).
+ 쓰기 - 대상이 관계(테이블)인 경우 `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `COPY`를 로깅합니다.
다음 AWS CLI 명령으로 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 재부팅합니다.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# 데이터베이스 객체 감사
Aurora PostgreSQL DB 클러스터에 pgAudit를 설정하고 요구 사항에 맞게 구성하면 PostgreSQL 로그에 더 자세한 정보가 캡처됩니다. 예를 들어 기본 PostgreSQL 로깅 구성은 데이터베이스 테이블에서 변경 사항이 적용된 날짜 및 시간을 식별하지만 pgAudit 확장을 사용하면 확장 파라미터의 구성에 따라 스키마, 변경한 사용자 및 기타 세부 정보가 로그 항목에 포함될 수 있습니다. 감사를 설정하여 다음 방법으로 변경 사항을 추적할 수 있습니다.
+ 세션마다 사용자별로 추적. 세션 수준에서 정규화된 명령 텍스트를 캡처할 수 있습니다.
+ 객체마다 사용자별, 데이터베이스별로 추적
객체 감사 기능은 시스템에서 `rds_pgaudit` 역할을 만든 다음, 사용자 지정 파라미터 그룹의 `pgaudit.role` 파라미터에 이 역할을 추가하면 활성화됩니다. 기본적으로 `pgaudit.role` 파라미터는 설정되어 있지 않으며 유일하게 허용되는 값은 `rds_pgaudit`입니다. 다음 단계에서는 `pgaudit`가 초기화되었고 [pgAudit 확장 설정](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md)의 절차에 따라 `pgaudit` 확장을 만든 것으로 가정합니다.
![\[pgAudit를 설정한 후의 PostgreSQL 로그 파일 이미지.\]](http://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/images/pgaudit-log-example.png)
이 예에서 볼 수 있듯이 LOG: AUDIT: SESSION 행은 테이블 및 해당 스키마를 비롯한 세부 정보를 제공합니다.
**객체 감사를 설정하는 방법**
1. `psql`을 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 연결합니다.
```
psql --host=your-instance-name.aws-region.rds.amazonaws.com --port=5432 --username=postgrespostgres --password --dbname=labdb
```
1. 다음 명령을 사용하여 `rds_pgaudit`라는 데이터베이스 역할을 생성합니다.
```
labdb=> CREATE ROLE rds_pgaudit;
CREATE ROLE
labdb=>
```
1. `psql` 세션을 닫습니다.
```
labdb=> \q
```
이어질 몇 단계에서는 AWS CLI를 사용하여 사용자 지정 파라미터 그룹에서 감사 로그 파라미터를 수정합니다.
1. 다음 AWS CLI 명령을 사용하여 `rds_pgaudit`에 `pgaudit.role` 파라미터를 추가합니다. 기본적으로 이 파라미터는 설정되어 있지 않으며 유일하게 허용되는 값은 `rds_pgaudit`입니다.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. 다음 AWS CLI 명령으로 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 재부팅하여 파라미터 변경 사항이 적용되도록 합니다.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. 다음 명령을 실행하여 `pgaudit.role`이 `rds_pgaudit`로 설정되었는지 확인합니다.
```
SHOW pgaudit.role;
pgaudit.role
------------------
rds_pgaudit
```
pgAudit 로깅을 테스트하기 위해 감사하려는 몇 가지 예제 명령을 실행할 수 있습니다. 예를 들어 다음과 같은 명령을 실행할 수 있습니다.
```
CREATE TABLE t1 (id int);
GRANT SELECT ON t1 TO rds_pgaudit;
SELECT * FROM t1;
id
----
(0 rows)
```
데이터베이스 로그에는 다음과 유사한 항목이 포함됩니다.
```
...
2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1;
...
```
로그 확인에 대한 자세한 내용은 [Amazon Aurora 로그 파일 모니터링](USER_LogAccess.md) 단원을 참조하십시오.
pgAudit 확장 프로그램에 대한 자세한 내용은 GitHub에서 [pgAudit](https://github.com/pgaudit/pgaudit/blob/master/README.md)을 참조하세요.
# 감사 로깅에서 사용자 또는 데이터베이스 제외
[Aurora PostgreSQL 데이터베이스 로그 파일](USER_LogAccess.Concepts.PostgreSQL.md)에서 설명한 대로 PostgreSQL 로그는 스토리지 공간을 사용합니다. pgAudit 확장을 사용하면 추적하는 변경 사항에 따라 로그에 수집된 데이터 양이 다양한 수준으로 늘어납니다. Aurora PostgreSQL DB 클러스터의 모든 사용자 또는 데이터베이스를 감사할 필요는 없을 수도 있습니다.
스토리지에 미치는 영향을 최소화하고 불필요하게 감사 레코드를 캡처하지 않도록 사용자 및 데이터베이스를 감사에서 제외할 수 있습니다. 지정된 세션 내에서 로깅을 변경할 수도 있습니다. 다음 예에서는 그 방법을 보여줍니다.
**참고**
세션 수준의 파라미터 설정은 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 대한 사용자 지정 DB 파라미터 그룹의 설정보다 우선합니다. 데이터베이스 사용자가 감사 로깅 구성 설정을 우회하지 못하게 하려면 사용자의 권한을 변경해야 합니다.
Aurora PostgreSQL DB 클러스터 가 모든 사용자 및 데이터베이스에 대해 동일한 수준의 활동을 감사하도록 구성되어 있다고 가정하겠습니다. 그런데 사용자 `myuser`를 감사하지 않기로 결정한다면 다음 SQL 명령으로 `myuser`에 대한 감사를 해제할 수 있습니다.
```
ALTER USER myuser SET pgaudit.log TO 'NONE';
```
그런 다음 다음 쿼리를 사용하여 `pgaudit.log`의 `user_specific_settings` 열에서 파라미터가 `NONE`으로 설정되었는지 확인할 수 있습니다.
```
SELECT
usename AS user_name,
useconfig AS user_specific_settings
FROM
pg_user
WHERE
usename = 'myuser';
```
출력은 다음과 같습니다.
```
user_name | user_specific_settings
-----------+------------------------
myuser | {pgaudit.log=NONE}
(1 row)
```
다음 명령을 사용하여 데이터베이스 세션 중에 특정 사용자에 대한 로깅을 해제할 수 있습니다.
```
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'none';
```
다음 쿼리를 사용하여 pgaudit.log의 설정 열에서 특정 사용자 및 데이터베이스 조합을 확인할 수 있습니다.
```
SELECT
usename AS "user_name",
datname AS "database_name",
pg_catalog.array_to_string(setconfig, E'\n') AS "settings"
FROM
pg_catalog.pg_db_role_setting s
LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
LEFT JOIN pg_catalog.pg_user r ON r.usesysid = setrole
WHERE
usename = 'myuser'
AND datname = 'mydatabase'
ORDER BY
1,
2;
```
출력은 다음과 비슷합니다.
```
user_name | database_name | settings
-----------+---------------+------------------
myuser | mydatabase | pgaudit.log=none
(1 row)
```
`myuser`에 대한 감사를 해제한 후 `mydatabase`에 대한 변경 내용을 추적하지 않기로 결정한다면 다음 명령을 사용하여 특정 데이터베이스에 대한 감사를 해제합니다.
```
ALTER DATABASE mydatabase SET pgaudit.log to 'NONE';
```
그런 다음, 다음 쿼리를 사용하여 database\$1specific\$1settings 열에서 pgaudit.log가 NONE으로 설정되어 있는지 확인합니다.
```
SELECT
a.datname AS database_name,
b.setconfig AS database_specific_settings
FROM
pg_database a
FULL JOIN pg_db_role_setting b ON a.oid = b.setdatabase
WHERE
a.datname = 'mydatabase';
```
출력은 다음과 같습니다.
```
database_name | database_specific_settings
---------------+----------------------------
mydatabase | {pgaudit.log=NONE}
(1 row)
```
myuser의 설정을 기본 설정으로 되돌리려면 다음 명령을 사용합니다.
```
ALTER USER myuser RESET pgaudit.log;
```
데이터베이스의 설정을 기본 설정으로 되돌리려면 다음 명령을 사용합니다.
```
ALTER DATABASE mydatabase RESET pgaudit.log;
```
사용자와 데이터베이스를 기본 설정으로 초기화하려면 다음 명령을 사용합니다.
```
ALTER USER myuser IN DATABASE mydatabase RESET pgaudit.log;
```
`pgaudit.log`를 `pgaudit.log` 파라미터에 허용되는 다른 값 중 하나로 설정하여 특정 이벤트를 로그에 캡처할 수도 있습니다. 자세한 내용은 [`pgaudit.log` 파라미터에 허용되는 설정 목록](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings) 단원을 참조하십시오.
```
ALTER USER myuser SET pgaudit.log TO 'read';
ALTER DATABASE mydatabase SET pgaudit.log TO 'function';
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'read,function'
```
# pgAudit 확장 프로그램에 대한 참조
이 섹션에 나열된 파라미터를 하나 이상 변경하여 감사 로그의 세부 정보 수준을 지정할 수 있습니다.
## pgAudit 동작 제어
다음 표에 나열된 파라미터 중 하나 이상을 변경하여 감사 로깅을 제어할 수 있습니다.
| 파라미터 | 설명 |
| --- | --- |
| `pgaudit.log` | 세션 감사 로깅으로 로깅될 문의 클래스를 지정합니다. 허용되는 값에는 ddl, 함수, 기타, 읽기, 역할, 쓰기, 없음, 모두 등이 포함됩니다. 자세한 내용은 [`pgaudit.log` 파라미터에 허용되는 설정 목록](#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings) 섹션을 참조하세요. |
| `pgaudit.log_catalog` | 활성화(1로 설정)하면 문의 모든 관계가 pg\$1catalog에 있는 경우 감사 추적에 문을 추가합니다. |
| `pgaudit.log_level` | 로그 항목에 사용할 로그 수준을 지정합니다. 허용되는 값은 ebug5, debug4, debug3, debug2, debug1, 정보, 알림, 경고, 로그입니다. |
| `pgaudit.log_parameter` | 활성화(1로 설정)하면 문과 함께 전달된 파라미터가 감사 로그에 캡처됩니다. |
| `pgaudit.log_relation` | 활성화(1로 설정)하면 세션의 감사 로그에서 SELECT 또는 DML 문에서 참조되는 각 관계(TABLE, VIEW 등) 에 대해 별도의 로그 항목을 생성합니다. |
| `pgaudit.log_statement_once` | 로깅에 문/하위 문 조합에 대한 첫 번째 로그 항목이 있는 문 텍스트 및 파라미터를 포함할지 아니면 모든 항목이 있는 문 텍스트 및 파라미터를 포함할지를 지정합니다. |
| `pgaudit.role` | 객체 감사 로깅에 사용할 마스터 역할을 지정합니다. 유일하게 허용되는 항목은 `rds_pgaudit`입니다. |
## `pgaudit.log` 파라미터에 허용되는 설정 목록
| 값 | 설명 |
| --- | --- |
| 없음 | 이 값이 기본값입니다. 데이터베이스 변경 사항이 로깅되지 않습니다. |
| 모두 | 모든 항목(읽기, 쓰기, 함수, 역할, ddl, 기타)을 로깅합니다. |
| ddl | `ROLE` 클래스에 포함되지 않은 모든 데이터 정의 언어(DDL) 문을 로깅합니다. |
| 함수 | 함수 호출 및 `DO` 블록을 로깅합니다. |
| 기타 | 기타 명령을 로깅합니다(예: `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM`, `SET`). |
| 읽기 | 원본이 관계(예: 테이블) 또는 쿼리인 경우 `SELECT` 및 `COPY`를 로깅합니다. |
| 역할 | 역할 및 권한과 관련된 문을 로깅합니다(예: `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE`, `DROP ROLE`). |
| write | 대상이 관계(테이블)인 경우 `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `COPY`를 로깅합니다. |
세션 감사를 사용하여 여러 이벤트 유형을 기록하려면 쉼표로 구분된 목록을 사용합니다. 모든 이벤트 유형을 기록하려면 `pgaudit.log`를 `ALL`로 설정합니다. DB 인스턴스를 재부팅하여 변경 사항을 적용합니다.
객체 감사를 사용하면 특정 관계를 사용하도록 감사 로깅을 구체화할 수 있습니다. 예를 들어 하나 이상의 테이블에서 `READ` 작업에 대한 감사 로깅을 지정할 수 있습니다.
# pglogical을 사용하여 인스턴스 간 데이터 동기화
현재 사용 가능한 모든 Aurora PostgreSQL 버전은 `pglogical` 확장을 지원합니다. pglogical 확장은 PostgreSQL 버전 10에서 도입된 기능적으로 유사한 논리적 복제 기능보다 먼저 출시되었습니다. 자세한 내용은 [Aurora에서의 PostgreSQL 논리적 복제 개요](AuroraPostgreSQL.Replication.Logical.md) 단원을 참조하세요.
`pglogical` 확장은 둘 이상의 Aurora PostgreSQL DB 클러스터 간의 논리적 복제를 지원합니다. 서로 다른 PostgreSQL 버전 간의 복제와 PostgreSQL DB 인스턴스용 RDS 및 Aurora PostgreSQL DB 클러스터에서 실행되는 데이터베이스 간의 복제도 지원합니다. `pglogical` 확장은 게시-구독 모델을 사용하여 게시자의 테이블 및 기타 객체(예: 시퀀스)의 변경 사항을 구독자에 복제합니다. 이 확장은 복제 슬롯을 사용하여, 다음과 같이 게시자 노드의 변경 사항이 구독자 노드로 동기화되게 합니다.
+ *게시자 노드*는 다른 노드에 복제할 데이터의 소스인 Aurora PostgreSQL DB 클러스터입니다. 게시자 노드는 게시 세트에서 복제될 테이블을 정의합니다.
+ *구독자 노드*는 게시자로부터 WAL 업데이트를 받는 Aurora PostgreSQL DB 클러스터입니다. 구독자는 구독을 생성하여 게시자에 연결하고 디코딩된 WAL 데이터를 얻습니다. 구독자가 구독을 생성하면 게시자 노드에서 복제 슬롯이 생성됩니다.
아래에서 `pglogical` 확장 설정 관련 정보를 확인할 수 있습니다.
**Topics**
+ [plogical 확장에 대한 요구 사항 및 제한](#Appendix.PostgreSQL.CommonDBATasks.pglogical.requirements-limitations)
+ [pglogical 확장 설정](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)
+ [Aurora PostgreSQL DB 클러스터용 논리적 복제 설정](Appendix.PostgreSQL.CommonDBATasks.pglogical.setup-replication.md)
+ [메이저 업그레이드 후 논리적 복제 재설정](Appendix.PostgreSQL.CommonDBATasks.pglogical.recover-replication-after-upgrade.md)
+ [Aurora PostgreSQL 용 논리적 복제 슬롯 관리](Appendix.PostgreSQL.CommonDBATasks.pglogical.handle-slots.md)
+ [plogical 확장용 파라미터 참조](Appendix.PostgreSQL.CommonDBATasks.pglogical.reference.md)
## plogical 확장에 대한 요구 사항 및 제한
현재 사용 가능한 모든 Aurora PostgreSQL 릴리스는 `pglogical` 확장을 지원합니다.
게시자 노드와 구독자 노드 모두가 논리적 복제를 할 수 있도록 설정되어야 합니다.
게시자에서 구독자로 복제할 테이블은 이름과 스키마가 동일해야 합니다. 또한 이러한 테이블은 동일한 열을 포함해야 하며, 각 열은 동일한 데이터 유형을 사용해야 합니다. 게시자와 구독자 테이블 모두 프라이머리 키가 동일해야 합니다. PRIMARY KEY만 고유 제약 조건으로 사용하는 것이 좋습니다.
구독자 노드의 테이블에는 CHECK 제약 조건 및 NOT NULL 제약 조건에 대해 게시자 노드의 테이블에 있는 것보다 더 많은 허용 제약 조건이 존재할 수 있습니다.
`pglogical` 확장은 PostgreSQL(버전 10 이상)에 내장된 논리적 복제 기능에서는 지원하지 않는 양방향 복제 같은 기능을 제공합니다. 자세한 내용은 [pglogical을 사용한 PostgreSQL 양방향 복제](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/)를 참조하십시오.
# pglogical 확장 설정
Aurora PostgreSQL DB 클러스터에 `pglogical` 확장을 설정하려면 Aurora PostgreSQL DB 클러스터용 사용자 지정 DB 클러스터 파라미터 그룹의 공유 라이브러리에 `pglogical`을 추가해야 합니다. 논리적 디코딩을 켜려면 `rds.logical_replication` 파라미터의 값을 `1`로 설정해야 합니다. 마지막으로, 데이터베이스에서 확장을 만듭니다. 이러한 작업에는 AWS Management Console 또는 AWS CLI를 사용할 수 있습니다.
이러한 작업을 수행하려면 `rds_superuser` 역할의 권한이 있어야 합니다.
다음 단계에서는 사용자의 Aurora PostgreSQL DB 클러스터 가 사용자 지정 DB 클러스터 파라미터 그룹에 연결되어 있다고 가정합니다. 사용자 지정 DB 클러스터 파라미터 그룹을 만드는 방법에 관한 자세한 내용은 [Amazon Aurora의 파라미터 그룹](USER_WorkingWithParamGroups.md) 섹션을 참조하세요.
## 콘솔
**pglogical 확장 설정 방법**
1. AWS Management Console에 로그인한 후 [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)에서 Amazon RDS 콘솔을 엽니다.
1. 탐색 창에서 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 선택합니다.
1. Aurora PostgreSQL DB 클러스터 라이터 인스턴스의 **구성** 탭을 엽니다. 인스턴스 세부 정보 중에서 **파라미터 그룹** 링크를 찾습니다.
1. 링크를 선택하여 Aurora PostgreSQL DB 클러스터와 연결된 사용자 지정 파라미터를 엽니다.
1. **파라미터** 검색 필드에 `shared_pre`를 입력하여 `shared_preload_libraries` 파라미터를 찾습니다.
1. **파라미터 편집**을 선택하여 속성 값에 액세스합니다.
1. **값** 필드의 목록에 `pglogical`를 추가합니다. 쉼표를 사용하여 값 목록에서 항목을 구분합니다.
![\[pglogical이 추가된 shared_preload_libraries 파라미터 이미지.\]](http://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/images/apg_rpg_shared_preload_pglogical.png)
1. `rds.logical_replication` 파라미터를 찾아 `1`로 설정하여 논리적 복제를 켭니다.
1. Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 재부팅하여 파라미터 변경 사항이 적용되게 합니다.
1. 인스턴스를 사용할 수 있다면 `psql`(또는 pgAdmin)을 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 연결할 수 있습니다.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. plogical이 초기화되었는지 확인하려면 다음 명령을 실행합니다.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pglogical
(1 row)
```
1. 논리적 디코딩을 활성화하는 설정을 다음과 같이 확인합니다.
```
SHOW wal_level;
wal_level
-----------
logical
(1 row)
```
1. 다음과 같이 확장을 생성합니다.
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. **변경 사항 저장**을 선택합니다.
1. [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/)에서 Amazon RDS 콘솔을 엽니다.
1. 데이터베이스 목록에서 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 선택한 다음, 작업 메뉴에서 **재부팅**을 선택합니다.
## AWS CLI
**plogical 확장 설정 방법**
AWS CLI를 사용하여 pglogical을 설정하려면 다음 절차와 같이 [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) 작업을 호출하여 사용자 지정 파라미터 그룹의 특정 파라미터를 수정합니다.
1. 다음 AWS CLI 명령을 사용하여 `shared_preload_libraries` 파라미터에 `pglogical`를 추가합니다.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pglogical,ApplyMethod=pending-reboot" \
--region aws-region
```
1. 다음 AWS CLI 명령을 사용하여 `rds.logical_replication`을 `1`로 설정하여 Aurora PostgreSQL DB 클러스터터의 라이터 인스턴스용 논리적 디코딩 기능을 켭니다.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \
--region aws-region
```
1. 다음 AWS CLI 명령으로 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 재부팅하여 pglogical 라이브러리가 초기화되도록 합니다.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. 인스턴스를 사용할 수 있다면 `psql`을 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 연결합니다.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. 다음과 같이 확장을 생성합니다.
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. 다음 AWS CLI 명령으로 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 재부팅합니다.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# Aurora PostgreSQL DB 클러스터용 논리적 복제 설정
다음 절차는 Aurora PostgreSQL DB 클러스터 간에 논리적 복제를 시작하는 방법을 보여줍니다. 다음 단계에서는 원본(게시자)과 대상(구독자) 모두에 [pglogical 확장 설정](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)에서 설명하는 방법에 따라 `pglogical` 확장이 설정되어 있다고 가정합니다.
**참고**
구독자 노드의 `node_name`은 `rds`로 시작할 수 없습니다.
**게시자 노드를 생성하고 복제할 테이블을 정의하는 방법**
이 단계에서는 Aurora PostgreSQL DB 클러스터에 라이터 인스턴스와 데이터베이스가 하나씩 있고 이러한 데이터베이스에는 다른 노드에 복제할 하나 이상의 테이블이 있다고 가정합니다. 구독자의 테이블 구조를 게시자에서 다시 만들어야 하므로, 필요한 경우 먼저 테이블 구조를 가져와야 합니다. 이렇게 하려면 `psql` 메타 명령 `\d tablename`을 사용한 다음 구독자 인스턴스에서 동일한 테이블을 생성해야 합니다. 다음 절차에서는 시연을 위해 게시자(원본)에서 예제 테이블을 만듭니다.
1. `psql`을 사용하여 구독자용 소스로 사용할 테이블이 있는 인스턴스에 연결합니다.
```
psql --host=source-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
복제하려는 기존 테이블이 없는 경우 다음과 같이 샘플 테이블을 생성할 수 있습니다.
1. 다음 SQL 문을 사용하여 예제 테이블을 생성합니다.
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. 다음 SQL 문을 사용하여 테이블에 생성된 데이터를 입력합니다.
```
INSERT INTO docs_lab_table VALUES (generate_series(1,5000));
INSERT 0 5000
```
1. 다음 SQL 문을 사용하여 테이블에 데이터가 있는지 확인합니다.
```
SELECT count(*) FROM docs_lab_table;
```
1. 다음과 같이 이 Aurora PostgreSQL DB 클러스터를 게시자 노드로 식별합니다.
```
SELECT pglogical.create_node(
node_name := 'docs_lab_provider',
dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 dbname=labdb');
create_node
-------------
3410995529
(1 row)
```
1. 복제할 테이블을 기본 복제 세트에 추가합니다. 복제 세트에 대한 자세한 내용은 pglogical 설명서의 [복제 세트](https://github.com/2ndQuadrant/pglogical/tree/REL2_x_STABLE/docs#replication-sets)를 참조하십시오.
```
SELECT pglogical.replication_set_add_table('default', 'docs_lab_table', 'true', NULL, NULL);
replication_set_add_table
---------------------------
t
(1 row)
```
게시자 노드 설정이 완료되었습니다. 이제 게시자로부터 업데이트를 수신하도록 구독자 노드를 설정할 수 있습니다.
**구독자 노드를 설정하고, 업데이트를 수신할 구독을 만드는 방법**
이 단계에서는 Aurora PostgreSQL DB 클러스터 가 `pglogical` 확장을 이용해 설정되었다고 가정합니다. 자세한 내용은 [pglogical 확장 설정](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md) 섹션을 참조하세요.
1. `psql`을 사용하여 게시자로부터 업데이트를 수신할 인스턴스에 연결합니다.
```
psql --host=target-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. 구독자 Aurora PostgreSQL DB 클러스터 에서 게시자에 존재하는 것과 동일한 테이블을 만듭니다. 이 예제에서 테이블은 `docs_lab_table`입니다. 다음과 같이 테이블을 만들 수 있습니다.
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. 이 테이블이 비어 있는지 확인합니다.
```
SELECT count(*) FROM docs_lab_table;
count
-------
0
(1 row)
```
1. 다음과 같이 이 Aurora PostgreSQL DB 클러스터를 구독자 노드로 식별합니다.
```
SELECT pglogical.create_node(
node_name := 'docs_lab_target',
dsn := 'host=target-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=********');
create_node
-------------
2182738256
(1 row)
```
1. 구독을 생성합니다.
```
SELECT pglogical.create_subscription(
subscription_name := 'docs_lab_subscription',
provider_dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=*******',
replication_sets := ARRAY['default'],
synchronize_data := true,
forward_origins := '{}' );
create_subscription
---------------------
1038357190
(1 row)
```
이 단계를 완료하면 게시자 테이블의 데이터가 구독자 테이블에서 생성됩니다. 다음 SQL 쿼리를 사용하면 이 문제가 발생했는지 확인할 수 있습니다.
```
SELECT count(*) FROM docs_lab_table;
count
-------
5000
(1 row)
```
이 시점 이후로는 게시자의 테이블에 적용한 변경 사항이 구독자의 테이블에 복제됩니다.
# 메이저 업그레이드 후 논리적 복제 재설정
논리적 복제용 게시자 노드로 설정된 Aurora PostgreSQL DB 클러스터 의 메이저 버전 업그레이드를 수행하려면, 먼저 활성화되지 않는 슬롯을 포함한 모든 복제 슬롯을 삭제해야 합니다. 게시자 노드에서 데이터베이스 트랜잭션을 일시적으로 전환하고, 복제 슬롯을 삭제하고, Aurora PostgreSQL DB 클러스터를 업그레이드한 다음 복제를 다시 설정하고 재시작하는 것이 좋습니다.
복제 슬롯은 게시자 노드에서만 호스팅됩니다. 논리적 복제 시나리오에서 Aurora PostgreSQL 구독자 노드는 삭제할 슬롯이 없습니다. Aurora PostgreSQL 메이저 버전 업그레이드 프로세스에서는 게시자 노드와 별개로 구독자를 PostgreSQL의 새 메이저 버전으로 업그레이드할 수 있습니다. 그러나 업그레이드 프로세스를 진행하면 복제 프로세스가 중단되고 게시자 노드와 구독자 노드 간의 WAL 데이터 동기화가 간섭을 받습니다. 게시자나 구독자 또는 둘 항목 모두를 업그레이드한 후에는 게시자와 구독자 간의 논리적 복제를 다시 설정해야 합니다. 다음 절차에서는 복제가 중단되었는지 판단하는 방법과 문제를 해결하는 방법을 확인할 수 있습니다.
## 논리적 복제가 중단되었는지 확인
다음과 같이 게시자 노드 또는 구독자 노드를 쿼리하면 복제 프로세스 중단 여부를 확인할 수 있습니다.
**게시자 노드를 확인하는 방법**
+ `psql`을 사용하여 게시자 노드에 연결한 다음 `pg_replication_slots` 함수를 쿼리합니다. 활성 열의 값을 기록해 둡니다. 일반적으로 이 값은 `t`(true)를 반환하며, 복제가 활성 상태라는 뜻입니다. 쿼리가 `f`(false)를 반환한다면 구독자로의 복제가 중단되었다는 뜻입니다.
```
SELECT slot_name,plugin,slot_type,active FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_labcb4fa94_docs_lab3de412c | pglogical_output | logical | f
(1 row)
```
**구독자 노드를 확인하는 방법**
구독자 노드에서는 세 가지 방법으로 복제 상태를 확인할 수 있습니다.
+ 구독자 노드의 PostgreSQL 로그를 확인하여 실패 메시지를 찾습니다. 로그는 다음과 같이 종료 코드 1을 포함하는 메시지를 이용해 실패를 식별합니다.
```
2022-07-06 16:17:03 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 14610) exited with exit code 1
2022-07-06 16:19:44 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 21783) exited with exit code 1
```
+ `pg_replication_origin` 함수를 쿼리합니다. 다음과 같이 `psql`을 사용하여 구독자 노드의 데이터베이스에 연결하고 `pg_replication_origin` 함수를 쿼리합니다.
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+--------
(0 rows)
```
결과 집합이 비어 있다면 복제가 중단되었다는 뜻입니다. 일반적인 출력은 다음과 같습니다.
```
roident | roname
---------+----------------------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
+ 다음 예제와 같이 `pglogical.show_subscription_status` 함수를 쿼리합니다.
```
SELECT subscription_name,status,slot_name FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
---====----------------+--------+-------------------------------------
docs_lab_subscription | down | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
이 출력은 복제가 중단되었음을 보여줍니다. 상태는 `down`입니다. 일반적으로 출력에서는 상태가 `replicating`으로 표시됩니다.
논리적 복제 프로세스가 중단된 경우 다음 단계에 따라 복제를 재설정할 수 있습니다.
**게시자와 구독자 노드 간의 논리적 복제를 재설정하는 방법**
복제를 다시 설정하려면 먼저 게시자 노드에서 구독자의 연결을 끊은 다음, 다음 단계의 설명에 따라 구독을 다시 설정합니다.
1. 다음과 같이 `psql`을 사용하여 구독자 노드에 연결합니다.
```
psql --host=222222222222.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. `pglogical.alter_subscription_disable` 함수를 사용하여 구독을 비활성화합니다.
```
SELECT pglogical.alter_subscription_disable('docs_lab_subscription',true);
alter_subscription_disable
----------------------------
t
(1 row)
```
1. 다음과 같이 `pg_replication_origin`을 쿼리하여 게시자 노드의 식별자를 가져옵니다.
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+-------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
1. 이전 단계의 응답을 `pg_replication_origin_create` 명령과 함께 사용하여, 구독을 다시 설정할 때 사용할 수 있는 식별자를 할당합니다.
```
SELECT pg_replication_origin_create('pgl_labdb_docs_labcb4fa94_docs_lab3de412c');
pg_replication_origin_create
------------------------------
1
(1 row)
```
1. 다음 예제와 같이 구독의 이름을 `true` 상태로 전달하여 구독을 활성화합니다.
```
SELECT pglogical.alter_subscription_enable('docs_lab_subscription',true);
alter_subscription_enable
---------------------------
t
(1 row)
```
노드의 상태를 확인합니다. 노드의 상태는 이 예제에서처럼 `replicating`이어야 합니다.
```
SELECT subscription_name,status,slot_name
FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
-------------------------------+-------------+-------------------------------------
docs_lab_subscription | replicating | pgl_labdb_docs_lab98f517b_docs_lab3de412c
(1 row)
```
게시자 노드에서 구독자 복제 슬롯의 상태를 확인합니다. 슬롯의 `active` 열은 `t`(true)를 반환해야 하며, 복제가 다시 설정되었다는 뜻입니다.
```
SELECT slot_name,plugin,slot_type,active
FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_lab98f517b_docs_lab3de412c | pglogical_output | logical | t
(1 row)
```
# Aurora PostgreSQL 용 논리적 복제 슬롯 관리
논리적 복제 시나리오에서 게시자 노드 역할을 하는 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스 의 메이저 버전 업그레이드를 수행하려면, 먼저 인스턴스에서 복제 슬롯을 삭제해야 합니다. 메이저 버전 업그레이드 사전 점검 프로세스에서는 업그레이드를 진행하려면 슬롯을 삭제해야 한다는 메시지가 표시됩니다.
`pglogical` 확장을 사용하여 만든 복제 슬롯을 식별하려면 각 데이터베이스에 로그인하여 노드 이름을 확인하십시오. 구독자 노드를 쿼리하면 이 예제에서처럼 출력에 게시자와 구독자 노드가 모두 표시됩니다.
```
SELECT * FROM pglogical.node;
node_id | node_name
------------+-------------------
2182738256 | docs_lab_target
3410995529 | docs_lab_provider
(2 rows)
```
다음 쿼리를 사용하면 구독 세부 정보를 확인할 수 있습니다.
```
SELECT sub_name,sub_slot_name,sub_target
FROM pglogical.subscription;
sub_name | sub_slot_name | sub_target
----------+--------------------------------+------------
docs_lab_subscription | pgl_labdb_docs_labcb4fa94_docs_lab3de412c | 2182738256
(1 row)
```
이제 다음과 같이 구독을 삭제할 수 있습니다.
```
SELECT pglogical.drop_subscription(subscription_name := 'docs_lab_subscription');
drop_subscription
-------------------
1
(1 row)
```
구독을 삭제한 후에는 노드를 삭제해도 됩니다.
```
SELECT pglogical.drop_node(node_name := 'docs-lab-subscriber');
drop_node
-----------
t
(1 row)
```
다음과 같은 방법을 이용해 노드가 더 이상 존재하지 않는지 확인할 수 있습니다.
```
SELECT * FROM pglogical.node;
node_id | node_name
---------+-----------
(0 rows)
```
# plogical 확장용 파라미터 참조
표에서는 `pglogical` 확장과 관련된 파라미터를 확인할 수 있습니다. `pglogical.conflict_log_level` 및 `pglogical.conflict_resolution` 같은 파라미터는 업데이트 충돌을 처리하는 용도로 사용합니다. 게시자의 변경 사항을 구독한 테이블과을 로컬로 변경하면 충돌이 발생할 수 있습니다. 양방향 복제 또는 동일한 게시자로부터 여러 구독자를 복제하는 경우를 비롯한 다양한 시나리오에서도 충돌이 발생할 수 있습니다. 자세한 내용은 [pglogical을 사용한 PostgreSQL 양방향 복제](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/)를 참조하십시오.
| 파라미터 | 설명 |
| --- | --- |
| pglogical.batch\$1inserts | 가능한 경우 배치를 삽입합니다. 기본적으로는 설정되지 않습니다. 켜려면 '1'로 변경하고 해제하려면 '0'으로 변경합니다. |
| pglogical.conflict\$1log\$1level | 해결된 충돌을 로깅하는 데 사용할 로그 수준을 설정합니다. 지원되는 문자열 값은 debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal 및 panic입니다. |
| pglogical.conflict\$1resolution | 해결할 수 있는 충돌인 경우 충돌을 해결하는 데 사용할 메서드를 설정합니다. 지원되는 문자열 값은 error, apply\$1remote, keep\$1local, last\$1update\$1wins 및 first\$1update\$1wins입니다. |
| pglogical.extra\$1connection\$1options | 모든 피어 노드 연결에 추가할 연결 옵션입니다. |
| pglogical.synchronous\$1commit | pglogical 특정 동기 커밋 값입니다. |
| pglogical.use\$1spi | 하위 수준 API 대신 SPI(서버 프로그래밍 인터페이스)를 사용하여 변경 사항을 적용합니다. 켜려면 '1'로 설정하고 해제하려면 '0'으로 설정합니다. SPI에 대한 자세한 내용은 PostgreSQL 설명서의 [서버 프로그래밍 인터페이스](https://www.postgresql.org/docs/current/spi.html)를 참조하십시오. |
# Amazon Aurora PostgreSQL용 지원되는 외부 데이터 래퍼 작업
외부 데이터 래퍼(FDW)는 외부 데이터에 대한 액세스를 제공하는 특정 유형의 확장입니다. 예를 들어 `oracle_fdw` 확장을 사용하면 Aurora PostgreSQL DB 인스턴스 가 Oracle 데이터베이스와 함께 작동할 수 있습니다.
아래에서는 여러 지원되는 PostgreSQL 외부 데이터 래퍼에 관한 정보를 확인할 수 있습니다.
**Topics**
+ [log\$1fdw 확장으로 SQL을 사용하여 DB 로그에 액세스](CHAP_PostgreSQL.Extensions.log_fdw.md)
+ [postgres\$1fdw 확장을 사용하여 외부 데이터 액세스](postgresql-commondbatasks-fdw.md)
+ [mysql\$1fdw 확장을 사용하여 MySQL 데이터베이스 작업](postgresql-mysql-fdw.md)
+ [oracle\$1fdw 확장을 사용하여 Oracle 데이터베이스 작업](postgresql-oracle-fdw.md)
+ [tds\$1fdw 확장을 사용하여 SQL Server 데이터베이스 작업](postgresql-tds-fdw.md)
# log\$1fdw 확장으로 SQL을 사용하여 DB 로그에 액세스
Aurora PostgreSQL DB 클러스터는 SQL 인터페이스를 사용하여 데이터베이스 엔진 로그에 액세스할 수 있는 `log_fdw` 확장을 지원합니다. `log_fdw` 확장은 데이터베이스 로그용 외부 테이블을 간편하게 생성할 수 있게 해주는 2가지 함수를 제공합니다.
+ `list_postgres_log_files` – 데이터베이스 로그 디렉터리의 파일과 파일 크기(단위: 바이트)를 나열합니다.
+ `create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text)` – 현재 데이터베이스에서 지정된 파일에 대해 외부 테이블을 빌드합니다.
`log_fdw`가 생성하는 모든 함수는 `rds_superuser`가 소유합니다. `rds_superuser` 역할의 구성원은 다른 데이터베이스 사용자에게 이러한 함수에 대한 액세스 권한을 부여할 수 있습니다.
기본적으로 로그 파일은 `log_destination` 파라미터에 명시된 대로 Amazon Aurora에 의해 `stderr`(표준 오류) 형식으로 생성됩니다. 이 파라미터에는 두 가지 옵션만 있습니다. `stderr` 및 `csvlog`(쉼표로 구분된 값, CSV)입니다. 파라미터에 `csvlog` 옵션을 추가하는 경우 Amazon Aurora가 `stderr` 및 `csvlog` 로그를 모두 생성합니다. 이는 DB 클러스터의 스토리지 용량에 영향을 줄 수 있으므로 로그 처리에 영향을 주는 다른 파라미터를 알고 있어야 합니다. 자세한 내용은 [로그 대상 설정(`stderr`, `csvlog`)](USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.md#USER_LogAccess.Concepts.PostgreSQL.Log_Format) 섹션을 참조하세요.
`csvlog` 로그를 생성할 때의 한 가지 이점은 `log_fdw` 확장을 사용하여 여러 열로 깔끔하게 분할된 데이터로 외부 테이블을 작성할 수 있다는 점입니다. 그러려면 인스턴스를 사용자 지정 DB 파라미터 그룹과 연결해야 `log_destination`에 대한 설정을 변경할 수 있습니다. 이에 관한 정보는 [Amazon Aurora의 파라미터 그룹](USER_WorkingWithParamGroups.md) 섹션을 참조하세요.
다음 예제에서는 `log_destination` 파라미터에 `cvslog`가 포함된 것으로 간주합니다.
**log\$1fdw 확장을 사용하려면**
1. `log_fdw` 확장을 설치합니다.
```
postgres=> CREATE EXTENSION log_fdw;
CREATE EXTENSION
```
1. 로그 서버를 외부 데이터 래퍼로 생성합니다.
```
postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw;
CREATE SERVER
```
1. 로그 파일 목록에서 모든 파일을 선택합니다.
```
postgres=> SELECT * FROM list_postgres_log_files() ORDER BY 1;
```
샘플 응답은 다음과 같습니다.
```
file_name | file_size_bytes
------------------------------+-----------------
postgresql.log.2023-08-09-22.csv | 1111
postgresql.log.2023-08-09-23.csv | 1172
postgresql.log.2023-08-10-00.csv | 1744
postgresql.log.2023-08-10-01.csv | 1102
(4 rows)
```
1. 선택한 파일에 대해 단일 'log\$1entry' 열이 있는 테이블을 생성합니다.
```
postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log',
'log_server', 'postgresql.log.2023-08-09-22.csv');
```
응답은 테이블이 현재 존재한다는 것 외에는 세부 정보를 제공하지 않습니다.
```
-----------------------------------
(1 row)
```
1. 로그 파일의 샘플을 선택합니다. 다음 코드는 로그 시간 및 오류 메시지 설명을 검색합니다.
```
postgres=> SELECT log_time, message FROM my_postgres_error_log ORDER BY 1;
```
샘플 응답은 다음과 같습니다.
```
log_time | message
----------------------------------+---------------------------------------------------------------------------
Tue Aug 09 15:45:18.172 2023 PDT | ending log output to stderr
Tue Aug 09 15:45:18.175 2023 PDT | database system was interrupted; last known up at 2023-08-09 22:43:34 UTC
Tue Aug 09 15:45:18.223 2023 PDT | checkpoint record is at 0/90002E0
Tue Aug 09 15:45:18.223 2023 PDT | redo record is at 0/90002A8; shutdown FALSE
Tue Aug 09 15:45:18.223 2023 PDT | next transaction ID: 0/1879; next OID: 24578
Tue Aug 09 15:45:18.223 2023 PDT | next MultiXactId: 1; next MultiXactOffset: 0
Tue Aug 09 15:45:18.223 2023 PDT | oldest unfrozen transaction ID: 1822, in database 1
(7 rows)
```
# postgres\$1fdw 확장을 사용하여 외부 데이터 액세스
[postgres\$1fdw](https://www.postgresql.org/docs/current/static/postgres-fdw.html) 확장으로 원격 데이터베이스에 있는 테이블의 데이터에 액세스할 수 있습니다. PostgreSQL DB 인스턴스에서 원격 연결을 설정하는 경우 읽기 전용 복제본에도 액세스할 수 있습니다.
**postgres\$1fdw로 원격 데이터베이스 서버에 액세스하려면**
1. postgres\$1fdw 확장을 설치합니다.
```
CREATE EXTENSION postgres_fdw;
```
1. CREATE SERVER로 외부 데이터 서버를 생성합니다.
```
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
```
1. 원격 서버에 사용할 역할 식별을 위하여 사용자 매핑을 생성합니다.
**중요**
로그에 표시되지 않도록 암호를 수정하려면 세션 수준에서 `log_statement=none`을 설정합니다. 파라미터 수준에서 설정하면 암호가 수정되지 않습니다.
```
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password');
```
1. 원격 서버에서 테이블을 매핑할 테이블을 생성합니다.
```
CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');
```
# mysql\$1fdw 확장을 사용하여 MySQL 데이터베이스 작업
Aurora PostgreSQL DB 클러스터에서 MySQL 호환 데이터베이스에 액세스하려면 `mysql_fdw` 확장을 설치하고 사용하면 됩니다. 이 외부 데이터 래퍼를 사용하면 RDS for MySQL, Aurora MySQL, MariaDB 및 기타 MySQL 호환 데이터베이스로 작업할 수 있습니다. Aurora PostgreSQL DB 클러스터에서 MySQL 데이터베이스로의 연결은 클라이언트 및 서버 구성에 따라 최선의 방식으로 암호화됩니다. 그러나 원하는 경우 암호화를 적용할 수 있습니다. 자세한 내용은 [확장에서 전송 중 암호화 사용](#postgresql-mysql-fdw.encryption-in-transit) 섹션을 참조하세요.
`mysql_fdw` 확장은 Amazon Aurora PostgreSQL 버전 15.4, 14.9, 13.12, 12.16 이상에서 지원됩니다. RDS for PostgreSQL DB에서 MySQL 호환 데이터베이스 인스턴스의 테이블에 대한 선택, 삽입, 업데이트 및 삭제를 지원합니다.
**Topics**
+ [mysql\$1fdw 확장을 사용하도록 Aurora PostgreSQL DB 설정](#postgresql-mysql-fdw.setting-up)
+ [예: Aurora PostgreSQL에서 Aurora MySQL 데이터베이스로 작업](#postgresql-mysql-fdw.using-mysql_fdw)
+ [확장에서 전송 중 암호화 사용](#postgresql-mysql-fdw.encryption-in-transit)
## mysql\$1fdw 확장을 사용하도록 Aurora PostgreSQL DB 설정
Aurora PostgreSQL DB 클러스터에서 `mysql_fdw` 확장을 설정하려면 DB 클러스터에서 확장을 로드한 다음 MySQL DB 인스턴스에 대한 연결 지점을 생성해야 합니다. 이 작업을 수행하려면 MySQL DB 인스턴스에 대한 다음과 같은 세부 정보가 필요합니다.
+ 호스트 이름 또는 엔드포인트. Aurora MySQL DB 클러스터의 경우 콘솔을 사용하여 엔드포인트를 찾을 수 있습니다. 연결 및 보안(Connectivity & security) 탭을 선택하고 '엔드포인트 및 포트(Endpoint and port)' 섹션을 살펴봅니다.
+ 포트 번호. MySQL의 기본 포트 번호는 3306입니다.
+ 데이터베이스 이름 DB 식별자입니다.
또한 MySQL 포트 3306의 액세스 제어 목록(ACL) 또는 보안 그룹에 대한 액세스를 제공해야 합니다. Aurora PostgreSQL DB 클러스터와 Aurora MySQL DB 클러스터에 포트 3306에 대한 액세스 권한이 있어야 합니다. 액세스가 올바르게 구성되지 않은 경우 MySQL 호환 테이블에 연결하려고 하면 다음과 비슷한 오류 메시지가 표시됩니다.
```
ERROR: failed to connect to MySQL: Can't connect to MySQL server on 'hostname.aws-region.rds.amazonaws.com:3306' (110)
```
다음 절차에서는 `rds_superuser` 계정으로 외부 서버를 생성합니다. 그런 다음 특정 사용자에게 외부 서버에 대한 액세스 권한을 부여합니다. 그러면 이러한 사용자가 MySQL DB 인스턴스로 작업하기 위해 적절한 MySQL 사용자 계정에 대한 자체 매핑을 생성합니다.
**mysql\$1fdw로 MySQL 데이터베이스 서버에 액세스하려면**
1. `rds_superuser` 역할이 있는 계정을 사용하여 PostgreSQL DB 인스턴스에 연결합니다. Aurora PostgreSQL DB 클러스터를 생성할 때 기본값을 수락한 경우 사용자 이름은 `postgres`이고 다음과 같이 `psql` 명령줄 도구를 사용하여 연결할 수 있습니다.
```
psql --host=your-DB-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres –-password
```
1. 다음과 같이 `mysql_fdw` 확장을 설치합니다.
```
postgres=> CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
```
Aurora PostgreSQL DB 클러스터에 확장을 설치한 후 MySQL 데이터베이스에 대한 연결을 제공하는 외부 서버를 설정합니다.
**외부 서버를 생성하려면**
Aurora PostgreSQL DB 클러스터에서 작업을 수행합니다. 이러한 단계에서는 `postgres`와 같은 `rds_superuser` 권한이 있는 사용자로 연결되어 있다고 가정합니다.
1. Aurora PostgreSQL DB 클러스터에서 외부 서버를 생성합니다.
```
postgres=> CREATE SERVER mysql-db FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'db-name.111122223333.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. 적절한 사용자에게 외부 서버에 대한 액세스 권한을 부여합니다. 관리자가 아닌 사용자, 즉 `rds_superuser` 역할이 없는 사용자여야 합니다.
```
postgres=> GRANT USAGE ON FOREIGN SERVER mysql-db to user1;
GRANT
```
PostgreSQL 사용자는 외부 서버를 통해 MySQL 데이터베이스에 대한 자체 연결을 생성하고 관리합니다.
## 예: Aurora PostgreSQL에서 Aurora MySQL 데이터베이스로 작업
Aurora PostgreSQL DB 인스턴스에 간단한 테이블이 있다고 가정합니다. Aurora PostgreSQL 사용자는 해당 테이블에 대해 (`SELECT`), `INSERT`, `UPDATE`, `DELETE` 항목을 쿼리하려고 합니다. 앞의 절차에서 설명한 대로 RDS for PostgreSQL DB 인스턴스에 `mysql_fdw` 확장이 생성되었다고 가정합니다. `rds_superuser` 권한이 있는 사용자로 RDS for PostgreSQL DB 인스턴스에 연결한 후 다음 단계를 진행할 수 있습니다.
1. Aurora PostgreSQL DB 인스턴스에서 외부 서버를 생성합니다.
```
test=> CREATE SERVER mysqldb FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. `rds_superuser` 권한이 없는 사용자(예: `user1`)에게 사용 권한을 부여합니다.
```
test=> GRANT USAGE ON FOREIGN SERVER mysqldb TO user1;
GRANT
```
1. *user1*으로 연결한 다음 MySQL 사용자에 대한 매핑을 생성합니다.
```
test=> CREATE USER MAPPING FOR user1 SERVER mysqldb OPTIONS (username 'myuser', password 'mypassword');
CREATE USER MAPPING
```
1. MySQL 테이블에 연결된 외부 테이블을 만듭니다.
```
test=> CREATE FOREIGN TABLE mytab (a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name '');
CREATE FOREIGN TABLE
```
1. 외부 테이블에 대한 간단한 쿼리를 실행합니다.
```
test=> SELECT * FROM mytab;
a | b
---+-------
1 | apple
(1 row)
```
1. MySQL 테이블에서 데이터를 추가, 변경 및 제거할 수 있습니다. 예를 들면 다음과 같습니다.
```
test=> INSERT INTO mytab values (2, 'mango');
INSERT 0 1
```
`SELECT` 쿼리를 다시 실행하여 결과를 확인합니다.
```
test=> SELECT * FROM mytab ORDER BY 1;
a | b
---+-------
1 | apple
2 | mango
(2 rows)
```
## 확장에서 전송 중 암호화 사용
Aurora PostgreSQL에서 MySQL에 대한 연결은 기본적으로 전송 중 암호화(TLS/SSL)를 사용합니다. 그러나 클라이언트와 서버 구성이 다를 경우 연결이 암호화되지 않은 상태로 폴백됩니다. RDS for MySQL 사용자 계정에서 `REQUIRE SSL` 옵션을 지정하여 나가는 모든 연결에 대해 암호화를 적용할 수 있습니다. 이와 동일한 접근 방식이 MariaDB 및 Aurora MySQL 사용자 계정에서도 작동합니다.
`REQUIRE SSL`로 구성된 MySQL 사용자 계정의 경우 보안 연결을 설정할 수 없으면 연결 시도가 실패합니다.
기존 MySQL 데이터베이스 사용자 계정에 암호화를 적용하려면 `ALTER USER` 명령을 사용할 수 있습니다. 구문은 다음 표에 나온 대로 MySQL 버전에 따라 다릅니다. 자세한 내용은 *MySQL 참조 매뉴얼*의 [ALTER USER](https://dev.mysql.com/doc/refman/8.0/en/alter-user.html)를 참조하세요.
| MySQL 5.7, MySQL 8.0 | MySQL 5.6 |
| --- | --- |
| `ALTER USER 'user'@'%' REQUIRE SSL;` | `GRANT USAGE ON *.* to 'user'@'%' REQUIRE SSL;` |
`mysql_fdw` 확장에 대한 자세한 내용은 [mysql\$1fdw](https://github.com/EnterpriseDB/mysql_fdw) 설명서를 참조하세요.
# oracle\$1fdw 확장을 사용하여 Oracle 데이터베이스 작업
Aurora PostgreSQL DB 클러스터 에서 Oracle 데이터베이스에 액세스하려면 `oracle_fdw` 확장을 설치하고 사용할 수 있습니다. 이 확장은 Oracle 데이터베이스의 외부 데이터 래퍼입니다. 이 확장 프로그램에 대해 자세히 알아보려면 [oracle\$1fdw](https://github.com/laurenz/oracle_fdw) 문서를 참조하세요.
`oracle_fdw` 확장은 Aurora PostgreSQL 12.7(Amazon Aurora 릴리스 4.2) 이상에서 지원됩니다.
**Topics**
+ [oracle\$1fdw 확장 켜기](#postgresql-oracle-fdw.enabling)
+ [예제: Amazon RDS for Oracle Database에 연결된 외부 서버 사용](#postgresql-oracle-fdw.example)
+ [전송 중 암호화 작업](#postgresql-oracle-fdw.encryption)
+ [pg\$1user\$1mappings 보기 및 권한 이해](#postgresql-oracle-fdw.permissions)
## oracle\$1fdw 확장 켜기
oracle\$1fdw 확장을 사용하려면 다음 절차를 수행하십시오.
**oracle\$1fdw 확장을 켜려면**
+ `rds_superuser` 권한이 있는 계정을 사용하여 다음 명령을 실행합니다.
```
CREATE EXTENSION oracle_fdw;
```
## 예제: Amazon RDS for Oracle Database에 연결된 외부 서버 사용
다음 예에서는 Amazon RDS for Oracle Database에 연결된 외부 서버를 사용하는 방법을 보여줍니다.
**RDS for Oracle Database에 연결된 외부 서버를 만들려면**
1. RDS for Oracle DB 인스턴스에서는 다음 사항에 유의하세요.
+ Endpoint
+ 포트
+ 데이터베이스 이름
1. 외부 서버를 만듭니다.
```
test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');
CREATE SERVER
```
1. `rds_superuser` 권한이 없는 사용자(예: `user1`)에게 사용 권한을 부여합니다.
```
test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1;
GRANT
```
1. `user1`로 연결하고 Oracle 사용자에 대한 매핑을 생성합니다.
```
test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword');
CREATE USER MAPPING
```
1. Oracle 테이블에 연결된 외부 테이블을 만듭니다.
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. 외부 테이블을 쿼리합니다.
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
쿼리에서 다음 오류를 보고하면 보안 그룹 및 액세스 제어 목록(ACL)을 확인하여 두 인스턴스가 모두 통신할 수 있는지 확인합니다.
```
ERROR: connection for foreign table "mytab" cannot be established
DETAIL: ORA-12170: TNS:Connect timeout occurred
```
## 전송 중 암호화 작업
PostgreSQL에서 Oracle 간의 전송 중인 암호화는 클라이언트와 서버 구성 파라미터의 조합을 기반으로 합니다. Oracle 21c를 사용하는 예제는 Oracle 문서의 [About the Values for Negotiating Encryption and Integrity](https://docs.oracle.com/en/database/oracle/oracle-database/21/dbseg/configuring-network-data-encryption-and-integrity.html#GUID-3A2AF4AA-AE3E-446B-8F64-31C48F27A2B5)를 참조하세요. Amazon RDS에서 oracle\$1fdw에 사용되는 클라이언트는 `ACCEPTED`로 구성됩니다. 즉, Oracle 데이터베이스 서버 구성에 따라 암호화가 달라지며, 암호화에 Oracle Security Library(libnnz)를 사용한다는 의미입니다.
데이터베이스가 RDS for Oracle에 있는 경우 암호화를 구성하려면 [Oracle 기본 네트워크 암호화](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.NetworkEncryption.html)를 참조하세요.
## pg\$1user\$1mappings 보기 및 권한 이해
PostgreSQL 카탈로그 `pg_user_mapping`에서 Aurora PostgreSQL 사용자의 매핑을 외부 데이터(원격) 서버의 사용자에게 저장합니다. 카탈로그에 대한 액세스는 제한되어 있지만 `pg_user_mappings` 보기를 사용하여 매핑을 확인합니다. 다음에서는 예제 Oracle 데이터베이스를 통해 권한이 적용되는 방법을 보여 주는 예제를 확인할 수 있지만, 이 정보는 다른 외부 데이터 래퍼에도 일반적으로 적용됩니다.
다음 결과에서는 세 가지 예제 사용자에 매핑된 역할 및 권한을 찾을 수 있습니다. 사용자 `rdssu1`과 `rdssu2`는 `rds_superuser` 역할이며 `user1`은 아닙니다. 다음 예제에서는 `psql` 메타 명령 `\du`를 사용하여 기존 역할을 나열합니다.
```
test=> \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-------------------------------------------------------------
rdssu1 | | {rds_superuser}
rdssu2 | | {rds_superuser}
user1 | | {}
```
`rds_superuser` 권한이 있는 사용자를 포함한 모든 사용자는 `pg_user_mappings` 테이블에서 자신의 사용자 매핑(`umoptions`)을 볼 수 있습니다. 다음 예제와 같이 `rdssu1`이 모든 사용자 매핑을 얻으려고 하면 `rdssu1``rds_superuser` 권한에도 불구하고 오류가 발생합니다.
```
test=> SELECT * FROM pg_user_mapping;
ERROR: permission denied for table pg_user_mapping
```
다음은 일부 예입니다.
```
test=> SET SESSION AUTHORIZATION rdssu1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 | {user=oracleuser,password=mypwd}
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
test=> SET SESSION AUTHORIZATION rdssu2;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 | {user=oracleuser,password=mypwd}
(3 rows)
test=> SET SESSION AUTHORIZATION user1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+--------------------------------
16414 | 16411 | oradb | 16412 | user1 | {user=oracleuser,password=mypwd}
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
```
`information_schema._pg_user_mappings` 및 `pg_catalog.pg_user_mappings`의 구현 차이점 때문에 수동으로 생성된 `rds_superuser`는 `pg_catalog.pg_user_mappings`에서 암호를 보려면 추가 권한이 필요합니다.
`information_schema._pg_user_mappings`에서 암호를 보려면 `rds_superuser`에 대한 추가 권한은 필요하지 않습니다.
`rds_superuser` 역할이 없는 사용자는 다음 조건에서만 `pg_user_mappings`에서 암호를 볼 수 있습니다.
+ 현재 사용자는 매핑되는 사용자이며 서버를 소유하거나 서버에 대한 `USAGE` 권한을 보유하고 있습니다.
+ 현재 사용자는 서버 소유자이고 매핑은 `PUBLIC`에 대한 것입니다.
# tds\$1fdw 확장을 사용하여 SQL Server 데이터베이스 작업
PostgreSQL `tds_fdw` 확장을 사용하여 Sybase 및 Microsoft SQL Server 데이터베이스와 같은 테이블 형식 데이터 스트림(TDS) 프로토콜을 지원하는 데이터베이스에 액세스할 수 있습니다. 이 외부 데이터 래퍼를 사용하면 Aurora PostgreSQL DB 클러스터에서 Amazon RDS for Microsoft SQL Server를 포함하여 TDS 프로토콜을 사용하는 데이터베이스에 연결할 수 있습니다. 자세한 내용은 GitHub에서 [tds-fdw/tds\$1fdw](https://github.com/tds-fdw/tds_fdw) 설명서를 참조하세요.
`tds_fdw` 확장은 Amazon Aurora PostgreSQL 버전 13.6 이상에서 지원됩니다.
## tds\$1fdw 확장을 사용하도록 Aurora PostgreSQL DB 설정
다음 절차에서는 Aurora PostgreSQL DB 클러스터와 함께 `tds_fdw`를 설정하고 사용하는 예를 확인할 수 있습니다. `tds_fdw`를 사용하여 SQL Server 데이터베이스에 연결하기 전에 인스턴스에 대한 다음 세부 정보를 확인해야 합니다.
+ 호스트 이름 또는 엔드포인트. RDS for SQL Server DB 인스턴스의 경우 콘솔을 사용하여 엔드포인트를 찾을 수 있습니다. 연결 및 보안(Connectivity & security) 탭을 선택하고 '엔드포인트 및 포트(Endpoint and port)' 섹션을 살펴봅니다.
+ 포트 번호. Microsoft SQL Server의 기본 포트 번호는 포트 1433입니다.
+ 데이터베이스 이름 DB 식별자입니다.
또한 SQL Server 포트 1433의 액세스 제어 목록(ACL) 또는 보안 그룹에 대한 액세스를 제공해야 합니다. Aurora PostgreSQL DB 클러스터와 RDS for MySQL DB 인스턴스가 모두 포트 1433에 액세스할 수 있어야 합니다. 액세스가 올바르게 구성되지 않은 경우 Microsoft SQL Server를 쿼리하려고 할 때 다음 오류 메시지가 나타납니다.
```
ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect:
Adaptive Server is unavailable or does not exist (mssql2019.aws-region.rds.amazonaws.com), OS #: 0, OS Msg: Success, Level: 9
```
**tds\$1fdw를 사용하여 SQL Server 데이터베이스에 연결하려면**
1. `rds_superuser` 역할이 있는 계정을 사용하여 Aurora PostgreSQL DB 클러스터의 기본 인스턴스에 연결합니다.
```
psql --host=your-cluster-name-instance-1.aws-region.rds.amazonaws.com --port=5432 --username=test –-password
```
1. `tds_fdw` 확장을 설치합니다.
```
test=> CREATE EXTENSION tds_fdw;
CREATE EXTENSION
```
Aurora PostgreSQL DB 클러스터 에 확장을 설치한 후 외부 서버를 설정합니다.
**외부 서버를 생성하려면**
`rds_superuser` 권한이 있는 계정을 사용하여 Aurora PostgreSQL DB 클러스터에서 다음과 같은 작업을 수행합니다.
1. Aurora PostgreSQL DB 클러스터에서 외부 서버를 생성합니다.
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing');
CREATE SERVER
```
SQLServer 측에서 ASCII가 아닌 데이터에 액세스하려면 Aurora PostgreSQL DB 클러스터에서 character\$1set 옵션을 사용하여 서버 링크를 생성합니다.
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing', character_set 'UTF-8');
CREATE SERVER
```
1. `rds_superuser` 역할 권한이 없는 사용자(예: `user1`)에게 권한을 부여합니다.
```
test=> GRANT USAGE ON FOREIGN SERVER sqlserverdb TO user1;
```
1. user1으로 연결한 다음 SQL Server 사용자에 대한 매핑을 생성합니다.
```
test=> CREATE USER MAPPING FOR user1 SERVER sqlserverdb OPTIONS (username 'sqlserveruser', password 'password');
CREATE USER MAPPING
```
1. SQL Server 테이블에 연결된 외부 테이블을 만듭니다.
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER sqlserverdb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. 외부 테이블을 쿼리합니다.
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
### 연결에 전송 중 암호화 사용
Aurora PostgreSQL에서 SQL Server로의 연결은 SQL Server 데이터베이스 구성에 따라 전송 중 암호화(TLS/SSL)를 사용합니다. SQL Server에 암호화가 구성되지 않은 경우 SQL Server 데이터베이스에 대한 요청을 수행하는 RDS for PostgreSQL 클라이언트가 암호화되지 않은 상태로 폴백됩니다.
`rds.force_ssl` 파라미터를 설정하여 RDS for SQL Server DB 인스턴스에 대한 연결에 암호화를 적용할 수 있습니다. 자세한 방법은 [DB 인스턴스 연결이 SSL을 사용하도록 지정](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html#SQLServer.Concepts.General.SSL.Forcing)을 참조하세요. RDS for SQL Server에 대한 SSL/TLS 구성과 관련된 자세한 내용은 [Microsoft SQL Server DB 인스턴스와 함께 SSL 사용](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html)을 참조하세요.