Amazon RDS for PostgreSQL로 PostgreSQL 확장 사용 - Amazon Relational Database Service

Amazon RDS for PostgreSQL로 PostgreSQL 확장 사용

다양한 확장 프로그램 및 모듈을 설치하여 PostgreSQL의 기능을 확장할 수 있습니다. 예를 들어 공간 데이터로 작업하려면 PostGIS 확장을 설치하고 사용할 수 있습니다. 자세한 정보는 PostGIS 확장을 사용하여 공간 데이터 관리을 참조하십시오. 또 다른 예로, 매우 큰 테이블의 데이터 입력을 개선하려는 경우 pg_partman 확장을 사용하여 데이터 분할을 고려할 수 있습니다. 자세한 내용은 pg_partman 확장자를 사용하여 PostgreSQL 파티션 관리하기 단원을 참조하세요.

RDS for PostgreSQL 버전에 따라 확장 프로그램을 설치하려면 다음과 같이 rds_superuser 권한이 필요합니다.

  • RDS for PostgreSQL 버전 12 및 이전 버전의 경우 확장 프로그램을 설치하려면 rds_superuser 권한이 필요합니다.

  • RDS for PostgreSQL 버전 13 및 이상 버전의 경우 지정된 데이터베이스 인스턴스에 대한 생성 권한이 있는 사용자(역할)는 신뢰할 수 있는 확장 프로그램을 설치하고 사용할 수 있습니다. 신뢰할 수 있는 확장 프로그램 목록은 PostgreSQL 신뢰할 수 있는 확장 섹션을 참조하세요.

RDS for PostgreSQL DB 인스턴스에 설치할 수 있는 확장 프로그램을 rds.allowed_extensions 파라미터에 나열하여 정확하게 지정할 수도 있습니다. 기본적으로 이 파라미터는 설정되지 않으므로 사용자에게 권한이 있는 경우 지원되는 확장 프로그램을 추가할 수 있습니다. 이 파라미터에 확장 프로그램 목록을 추가하면 RDS for PostgreSQL DB 인스턴스가 사용할 수 있는 확장을 명시적으로 식별할 수 있습니다. 나열되지 않은 확장 프로그램은 설치할 수 없습니다. 이 기능은 다음 버전에서 사용할 수 있습니다.

  • RDS for PostgreSQL 14.1 이상의 모든 버전

  • RDS for PostgreSQL 13.2 이상의 마이너 버전

  • RDS for PostgreSQL 12.6 이상의 마이너 버전

자세한 정보는 PostgreSQL 확장의 설치 제한을 참조하십시오.

rds_superuser 역할에 대한 자세한 내용은 PostgreSQL 역할 및 권한 이해 섹션을 참조하세요.

orafce 확장에서 함수 사용

orafce 확장은 Oracle 데이터베이스에서 함수와 패키지의 하위 집합을 에뮬레이션하는 함수 및 연산자를 제공합니다. orafce 확장을 사용하면 Oracle 애플리케이션을 PostgreSQL로 쉽게 포팅할 수 있습니다. 이 확장은 RDS for PostgreSQL 버전 9.6.6 이상에서 지원됩니다. orafce에 대한 자세한 내용은 GitHub에서 orafce를 참조하세요.

참고

RDS for PostgreSQL은 orafce 확장의 일부분인 utl_file 패키지를 지원하지 않습니다. 이는 utl_file 스키마 함수가 기본 호스트에 대한 수퍼유저 권한을 필요로 하는 운영 체제 텍스트 파일의 읽기 및 쓰기 작업을 제공하기 때문입니다. 관리형 서비스에서 RDS for PostgreSQL은 호스트 액세스를 제공하지 않습니다.

orafce 확장을 사용하려면

  1. DB 인스턴스를 생성할 때 사용한 마스터 사용자 이름으로 DB 인스턴스에 연결합니다.

    동일한 DB 인스턴스의 다른 데이터베이스에 대해 orafce를 활성화하려면 /c dbname psql 명령을 사용합니다. 이 명령을 사용하면 연결을 시작한 후 기본 데이터베이스에서 변경합니다.

  2. CREATE EXTENSION 문을 사용하여 orafce 확장을 활성화합니다.

    CREATE EXTENSION orafce;
  3. ALTER SCHEMA 문을 사용하여 oracle 스키마 소유권을 rds_superuser 역할로 이전합니다.

    ALTER SCHEMA oracle OWNER TO rds_superuser;

    oracle 스키마의 소유자 목록을 보려면 \dn psql 명령을 사용합니다.

pgAudit 확장 프로그램을 사용하여 세션 및 객체 수준에서 로깅

RDS for PostgreSQL DB 인스턴스에서 PostgreSQL Audit(pgAudit) 확장 프로그램을 설치하여 세션 수준 또는 객체 수준에서 활동을 로깅할 수 있습니다. 이 확장은 사용 가능한 모든 RDS for PostgreSQL 버전에서 지원됩니다. 이때 기본 PostgreSQL 로깅 메커니즘을 사용합니다.

pgAudit 확장 프로그램에 대한 자세한 내용은 GitHub에서 pgAudit을 참조하세요.

세션 감사를 사용하면 다양한 소스의 감사 이벤트를 로그할 수 있으며 사용 가능한 경우 정규화된 명령 텍스트를 포함할 수 있습니다. shared_preload_libraries가 pgaudit을 포함하도록 DB 인스턴스와 연결된 사용자 지정 파라미터 그룹을 수정합니다. 그런 다음 pgaudit.log 파라미터를 설정하여 다음 유형의 이벤트를 기록합니다.

  • READ – 소스가 관계 또는 쿼리인 경우 SELECTCOPY를 기록합니다.

  • WRITE - 대상이 관계인 경우 INSERT, UPDATE, DELETE, TRUNCATE, 및 COPY를 기록합니다.

  • FUNCTION - 함수 호출 및 DO 블록을 기록합니다.

  • ROLE - 역할 및 권한과 관련된 문을 기록합니다(예: GRANT, REVOKE, CREATE ROLE, ALTER ROLEDROP ROLE).

  • DDL - ROLE 클래스에 포함되지 않은 모든 데이터 정의 언어(DDL) 문을 기록합니다.

  • MISC – 기타 명령을 기록합니다(예: DISCARD, FETCH, CHECKPOINT, VACUUMSET).

세션 감사를 사용하여 여러 이벤트 유형을 기록하려면 쉼표로 구분된 목록을 사용합니다. 모든 이벤트 유형을 기록하려면 pgaudit.logALL로 설정합니다. DB 인스턴스를 재부팅하여 변경 사항을 적용합니다.

객체 감사를 사용하면 특정 관계를 사용하도록 감사 로깅을 구체화할 수 있습니다. 예를 들어 특정 테이블 수에서 READ 작업에 대한 감사 로깅을 지정할 수 있습니다.

pgAudit 확장 프로그램을 사용하려면 RDS for PostgreSQL DB 인스턴스의 shared_preload_libraries 파라미터에 pgaudit을 추가합니다. 기본 DB 파라미터 그룹 값은 편집할 수 없으므로 DB 인스턴스에 사용자 지정 DB 파라미터 그룹을 사용해야 합니다. 파라미터 그룹에 대한 자세한 내용은 파라미터 그룹 작업 단원을 참조하십시오.

pgAudit 확장 프로그램으로 객체 감사를 사용하는 방법

  1. 다음 명령을 사용하여 rds_pgaudit라는 데이터베이스 역할을 생성합니다.

    CREATE ROLE rds_pgaudit;
  2. 다음을 수행하도록 DB 인스턴스와 연결된 DB 사용자 지정 파라미터 그룹을 수정합니다.

    1. shared_preload_libraries 파라미터 목록에 pgaudit을 추가합니다. AWS CLI를 사용하여 다음을 실행합니다.

      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
    2. pgaudit.rolerds_pgaudit 역할로 설정합니다. AWS CLI를 사용하여 다음을 실행합니다.

      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
  3. 파라미터 그룹 변경 사항을 적용하려면 DB 인스턴스를 재부팅합니다.

    aws rds reboot-db-instance \ --db-instance-identifier your-RDS-db-instance \ --region aws-region
  4. 다음 명령을 실행하여 pgaudit가 초기화되었는지 확인합니다.

    SHOW shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pgaudit (1 row)
  5. 다음 명령을 실행하여 pgaudit 확장을 생성합니다.

    CREATE EXTENSION pgaudit;
  6. 다음 명령을 실행하여 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 RDS 로그 파일 모니터링 단원을 참조하십시오.

pg_repack 확장을 사용하여 테이블 및 인덱스에서 부풀림을 줄입니다.

pg_repack 확장을 사용하여 테이블과 인덱스에서 부풀림을 제거할 수 있습니다. 이 확장은 RDS for PostgreSQL 버전 9.6.3 이상에서 지원됩니다. pg_repack 확장에 대한 자세한 내용은 GitHub 프로젝트 설명서를 참조하세요.

pg_repack 확장을 사용하려면

  1. 다음 명령을 실행하여 RDS for PostgreSQL 인스턴스에 pg_repack 확장을 설치합니다.

    CREATE EXTENSION pg_repack;
  2. 다음 명령을 실행하여 pg_repack에서 생성한 임시 로그 테이블을 다시 패키징할 수 있는 쓰기 권한을 부여합니다.

    ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC; ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;
  3. pg_repack 클라이언트 유틸리티를 사용하여 데이터베이스에 연결합니다. rds_superuser 권한이 있는 계정을 사용합니다. 예를 들어 rds_test 역할이 rds_superuser 권한을 가지고 있다고 가정하겠습니다. 다음 명령 구문이 나와 있습니다.

    pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test -k postgres

    -k 옵션을 사용하여 연결합니다. -a 옵션은 지원되지 않습니다.

  4. pg_repack 클라이언트의 응답은 다시 구성된 DB 인스턴스 상의 테이블에 대한 정보를 제공합니다.

    INFO: repacking table "pgbench_tellers" INFO: repacking table "pgbench_accounts" INFO: repacking table "pgbench_branches"

PLV8 확장 업그레이드 및 사용

PLV8은 PostgreSQL을 위한 신뢰할 수 있는 Javascript 언어 확장입니다. 저장 프로시저, 트리거 및 SQL에서 호출할 수 있는 기타 절차 코드에 사용할 수 있습니다. 이 언어 확장은 모든 최신 PostgreSQL 릴리스에서 지원됩니다.

PLV8을 사용하고 PostgreSQL을 새 PLV8 버전으로 업그레이드하는 경우 즉시 새로운 확장을 사용하세요. 다음 단계를 수행하여 카탈로그 메타데이터를 새 PLV8 버전과 동기화합니다. 이 단계는 선택 사항이지만 메타데이터 불일치 경고를 방지하려면 완료하는 것이 좋습니다.

업그레이드 프로세스는 기존 PLV8 기능을 모두 삭제합니다. 따라서 업그레이드하기 전에 RDS for PostgreSQL DB 인스턴스의 스냅샷을 생성하는 것이 좋습니다. 자세한 정보는 DB 스냅샷 생성을 참조하십시오.

새 PLV8 버전과 카탈로그 메타데이터 동기화

  1. 업데이트해야 하는 것을 확인합니다. 이렇게 하려면 인스턴스에 연결된 동안 다음 명령을 실행합니다.

    SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');

    결과에 기본 버전보다 낮은 설치 버전에 대한 값이 포함된 경우 이 절차를 계속 진행하여 확장을 업데이트합니다. 예를 들어, 다음 결과 집합은 업데이트해야 함을 나타냅니다.

    name | default_version | installed_version | comment --------+-----------------+-------------------+-------------------------------------------------- plls | 2.1.0 | 1.5.3 | PL/LiveScript (v8) trusted procedural language plcoffee| 2.1.0 | 1.5.3 | PL/CoffeeScript (v8) trusted procedural language plv8 | 2.1.0 | 1.5.3 | PL/JavaScript (v8) trusted procedural language (3 rows)
  2. RDS for PostgreSQL DB 인스턴스의 스냅샷을 생성합니다. 이미 해당 스냅샷을 생성한 경우 이 단계를 건너뜁니다. 스냅샷이 생성되는 동안 다음 단계를 따라 계속 진행할 수 있습니다.

  3. DB 인스턴스에 있는 PLV8 함수의 개수를 파악해야 업그레이드 이후 모두 존재하는지 확인할 수 있습니다. 예를 들어 다음 SQL 쿼리는 plv8, plcoffee 및 plls로 작성된 함수의 수를 반환합니다.

    SELECT proname, nspname, lanname FROM pg_proc p, pg_language l, pg_namespace n WHERE p.prolang = l.oid AND n.oid = p.pronamespace AND lanname IN ('plv8','plcoffee','plls');
  4. pg_dump를 사용하여 스키마 전용 덤프 파일을 생성합니다. 예를 들어, 클라이언트 시스템의 /tmp 디렉터리에 파일을 생성합니다.

    ./pg_dump -Fc --schema-only -U master postgres >/tmp/test.dmp

    이 예에서는 다음 옵션을 사용합니다.

    • -Fc - 사용자 지정 형식

    • --schema-only - 스키마(이 경우에서는 함수)를 생성하는 데 필요한 명령만 덤프

    • -U – RDS 기본 사용자 이름

    • database – DB 인스턴스의 데이터베이스 이름

    pg_dump에 대한 자세한 내용은 PostgreSQL 설명서의 pg_dump를 참조하세요.

  5. 덤프 파일에 있는 'CREATE FUNCTION' DDL 문을 추출하십시오. 다음 예에서는 grep 명령을 사용하여 함수를 생성하는 DDL 문을 추출하여 파일에 저장합니다. 후속 단계에서 이를 사용하여 함수를 다시 생성합니다.

    ./pg_restore -l /tmp/test.dmp | grep FUNCTION > /tmp/function_list/

    pg_restore에 대한 자세한 내용은 PostgreSQL 설명서의 pg_restore를 참조하세요.

  6. 함수 및 확장 기능을 중단합니다. 다음 예는 모든 PLV8 기반 객체를 중단합니다. 캐스케이드 옵션은 모든 종속이 중단되도록 합니다.

    DROP EXTENSION pvl8 CASCADE;

    PostgreSQL 인스턴스에 plcoffee 또는 plls 기반 객체가 포함된 경우 이러한 확장 기능에 대해 이 단계를 반복합니다.

  7. 확장 기능을 생성합니다. 다음 예에서는 plv8, plcoffee 및 plls 확장을 생성합니다.

    CREATE EXTENSION plv8; CREATE EXTENSION plcoffee; CREATE EXTENSION plls;
  8. 덤프 파일과 "드라이버" 파일을 사용하여 함수를 생성합니다.

    다음 예에서는 이전에 추출한 함수를 다시 생성합니다.

    ./pg_restore -U master -d postgres -Fc -L /tmp/function_list /tmp/test.dmp
  9. 다음 쿼리를 사용하여 모든 함수가 다시 생성되었는지 확인합니다.

    SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');

    PLV8 버전 2는 결과 세트에 다음 추가 행을 추가합니다.

    proname | nspname | lanname ---------------+------------+---------- plv8_version | pg_catalog | plv8