Amazon RDS for PostgreSQL 또는 Amazon Aurora PostgreSQL 호환 버전에서 외래 키 또는 트리거를 끄려면 어떻게 해야 합니까?

간략한 설명

PostgreSQL은 외래 키 제약 조건을 켜거나 끄는 직접 함수를 제공하지 않습니다. PostgreSQL의 테이블에서 외래 키를 생성하면 PostgreSQL은 내부 트리거를 생성하여 데이터 무결성을 검사합니다. 외래 키 제약 조건을 끄려면 PostgreSQL에서 생성하는 내부 트리거를 삭제할 수 있는 superuser 권한이 있어야 합니다.

테이블의 모든 트리거를 끄는 다음과 같은 명령을 실행하면 permission denied 오류가 발생합니다.

ALTER TABLE TestTable DISABLE TRIGGER ALL;
Error report -
ERROR: permission denied: "RI_ConstraintTrigger_c_9999181" is a system trigger

RDS for PostgreSQL 및 Aurora PostgreSQL 호환 버전은 관리형 서비스이기 때문에 기본 사용자에게도 모든 superuser 권한을 허용하지 않습니다. ALTER TABLE 'tablename' DISABLE TRIGGER ALL; 명령은 허용되지 않습니다.

해결 방법

참고: 트리거가 켜져 있지 않으면 제약 조건의 무결성이 영향을 받을 수 있습니다. 또한 session_replication_role 구성 변수는 트리거를 활성화하는 메커니즘에 영향을 줄 수 있습니다. origin 또는 local 복제 역할에 대해 트리거를 켜면 트리거가 시작됩니다. 자세한 내용은 PostreSQL 웹사이트에서 session_replication_role (enum)을 참조하십시오.

session_replication_role을 replica로 변경

RDS for PostgreSQL 또는 Aurora PostgreSQL 호환 버전에서 외래 키 제약 조건을 끄려면 session_replication_role 파라미터를 origin에서 replica로 변경하십시오. 이 파라미터를 replica로 변경하면 pg_restore 또는 pgloader가 테이블에 데이터를 삽입하거나 업데이트할 수 있습니다.

session_replication_role을 replica로 변경하려면 다음 옵션 중 하나를 사용하십시오.

• pgadmin 또는 PostgreSQL 세션을 사용하는 경우 내부적으로 참조를 적용하는 시스템 트리거가 시작되지 않도록 다음 명령을 실행합니다.

postgres=> set session_replication_role = replica;

참고: 변경 사항은 현재 세션에만 유효합니다.

• Amazon RDS 또는 Aurora DB 인스턴스의 해당 데이터베이스 파라미터 그룹에서 session_replication_role 데이터베이스 파라미터를 수정합니다.

참고: 작업을 완료한 후에는 session_replication_role 파라미터를 origin으로 되돌리십시오. session_replication_role을 origin으로 다시 변경하지 않으면 다른 테이블에서 참조 무결성 문제가 발생할 수 있습니다.

테이블에 외래 키 제약 조건 삭제

superuser 권한 없이 트리거를 끄려면 다음 명령을 실행하여 테이블에서 외래 키 제약 조건을 삭제하십시오.

Alter table table_name drop constraint key_name;

table_name을 데이터베이스의 테이블 이름으로 바꾸고 key_name을 테이블의 키 이름으로 바꿉니다. 자세한 내용은 PostgreSQL 웹사이트에서 ALTER TABLE을 참조하십시오.

관련 정보

Amazon RDS for MySQL DB 인스턴스의 함수, 프로시저 및 트리거를 활성화하려면 어떻게 해야 합니까?

PostgreSQL 데이터베이스를 AWS Database Migration Service의 대상으로 사용

PostreSQL 웹사이트의 session_replication_role 'replica' 동작