Oracle에서 Postgre로 가상 생성 열 마이그레이션SQL - AWS 권장 가이드
요약사전 조건 및 제한 사항도구에픽관련 리소스

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

Oracle에서 Postgre로 가상 생성 열 마이그레이션SQL

작성자: Veeranjaneyulu Grandhi(AWS), Rajesh Madiwale(AWS) 및 Ramesh Pathuri(AWS)

환경: 프로덕션

‬소스:‭ Oracle 네이터베이스

대상: Amazon RDS for PostgreSQL 또는 Aurora Postgre SQL-호환 가능

R 유형: 리아키텍트

워크로드: Oracle

기술: 마이그레이션, 데이터베이스

AWS 서비스: Amazon Aurora, Amazon RDS, AWS DMS

요약

버전 11 이하에서는 PostgreSQL가 Oracle 가상 열과 직접적으로 동등한 기능을 제공하지 않습니다. Oracle Database에서 PostgreSQL 버전 11 이하로 마이그레이션하는 동안 가상으로 생성된 열을 처리하는 것은 두 가지 이유로 어렵습니다. 

  • 마이그레이션 중에는 가상화 열이 보이지 않습니다.

  • PostgreSQL는 버전 12 이전의 generate 표현식을 지원하지 않습니다.

그러나 유사한 기능을 에뮬레이트하는 해결 방법이 있습니다. AWS Database Migration Service(AWS DMS)를 사용하여 Oracle Database에서 PostgreSQL 버전 11 이하로 데이터를 마이그레이션하는 경우 트리거 함수를 사용하여 가상 생성 열의 값을 채울 수 있습니다. 이 패턴은 이 용도로 사용할 수 있는 Oracle Database 및 PostgreSQL 코드의 예를 제공합니다. 에서 PostgreSQL용 Amazon Relational Database Service(Amazon RDS) 또는 PostgreSQL 데이터베이스용 Amazon Aurora Postgre SQL호환 버전을 사용할 AWS수 있습니다.

PostgreSQL 버전 12부터 생성된 열이 지원됩니다. 생성된 열은 다른 열 값에서 바로 계산하거나 계산하여 저장할 수 있습니다. PostgreSQL에서 생성된 열은 Oracle 가상 열과 유사합니다.

사전 조건 및 제한 사항

사전 조건 

  • 활성 AWS 계정

  • 소스 오라클 데이터베이스

  • Target PostgreSQL 데이터베이스(Amazon RDS for PostgreSQL 또는 Aurora Postgre SQL- 호환)

  • PL/pgSQL 코딩 전문 지식

제한 사항

  • 버전 12 이전의 PostgreSQL 버전에만 적용됩니다. 

  • 오라클 데이터베이스 버전 11g 이상에만 적용됩니다.

  • 가상화 열은 데이터 마이그레이션 도구에서 지원되지 않습니다.

  • 동일한 테이블에 정의된 열에만 적용됩니다.

  • 가상으로 생성된 열이 결정론적 사용자 정의 함수를 참조하는 경우 파티셔닝 키 열로 사용할 수 없습니다.

  • 표현식 출력은 스칼라 값이어야 합니다. 오라클에서 제공한 데이터 유형, 사용자 정의 유형, LOB, 또는 LONG RAW 반환은 안 됩니다.

  • 가상 열에 대해 정의된 인덱스는 Postgre의 함수 기반 인덱스와 동일합니다SQL.

  • 테이블 통계를 수집해야 합니다.

도구

  • pgAdmin 4는 Postgre 의 오픈 소스 관리 도구입니다SQL. 이 도구는 데이터베이스 개체의 생성, 유지 관리 및 사용을 간소화하는 그래픽 인터페이스를 제공합니다.

  • Oracle SQL Developer는 기존 및 클라우드 배포 모두에서 Oracle 데이터베이스SQL에서 작업할 수 있는 무료 통합 개발 환경입니다. 

에픽

작업설명필요한 기술

소스 오라클 데이터베이스 테이블을 만듭니다.

오라클 데이터베이스에서 다음 문을 사용하여 가상으로 생성된 열이 있는 테이블을 생성합니다.

CREATE TABLE test.generated_column
( CODE NUMBER,
STATUS VARCHAR2(12) DEFAULT 'PreOpen',
FLAG CHAR(1) GENERATED ALWAYS AS (CASE UPPER(STATUS) WHEN 'OPEN' THEN 'N' ELSE 'Y' END) VIRTUAL VISIBLE
);

이 소스 테이블에서는 STATUS 열의 데이터가 대상 데이터베이스AWSDMS로 마이그레이션됩니다. 그러나 이 FLAG 열은 generate by 기능을 사용하여 채워지므로 마이그레이션 AWS DMS 중에는 이 열이 표시되지 않습니다. generated by의 기능을 구현하려면 다음 에픽과 같이 대상 데이터베이스에서 트리거와 함수를 사용하여 FLAG 열 값을 채워야 합니다.

DBA, 앱 개발자

에서 대상 PostgreSQL 테이블을 생성합니다AWS.

다음 문을 AWS 사용하여 에서 PostgreSQL 테이블을 생성합니다.

CREATE TABLE test.generated_column
(
    code integer not null,
    status character varying(12) not null ,
    flag character(1)
);

이 표에서 status 열은 표준 열입니다. flag 열은 열에 있는 데이터를 기반으로 생성된 status 열이 됩니다.

DBA, 앱 개발자
작업설명필요한 기술

PostgreSQL 트리거를 생성합니다.

Postgre 에서 트리거를 SQL생성합니다.

CREATE TRIGGER tgr_gen_column
AFTER INSERT OR UPDATE OF status ON test.generated_column
FOR EACH ROW 
EXECUTE FUNCTION test.tgf_gen_column();
DBA, 앱 개발자

PostgreSQL 트리거 함수를 생성합니다.

Postgre 에서 트리거에 대한 함수를 SQL생성합니다. 이 함수는 애플리케이션 또는 AWS 에서 삽입하거나 업데이트한 가상 열을 채우DMS고 데이터를 검증합니다.

CREATE OR REPLACE FUNCTION test.tgf_gen_column() RETURNS trigger AS $VIRTUAL_COL$
BEGIN
IF (TG_OP = 'INSERT') THEN
IF (NEW.flag IS NOT NULL) THEN
RAISE EXCEPTION 'ERROR: cannot insert into column "flag"' USING DETAIL = 'Column "flag" is a generated column.';
END IF;
END IF;
IF (TG_OP = 'UPDATE') THEN
IF (NEW.flag::VARCHAR != OLD.flag::varchar) THEN
RAISE EXCEPTION 'ERROR: cannot update column "flag"' USING DETAIL = 'Column "flag" is a generated column.';
END IF;
END IF;
IF TG_OP IN ('INSERT','UPDATE') THEN
IF (old.flag is NULL) OR (coalesce(old.status,'') != coalesce(new.status,'')) THEN
UPDATE test.generated_column
SET flag = (CASE UPPER(status) WHEN 'OPEN' THEN 'N' ELSE 'Y' END)
WHERE code = new.code;
END IF;
END IF;
RETURN NEW;
END
$VIRTUAL_COL$ LANGUAGE plpgsql;
DBA, 앱 개발자
작업설명필요한 기술

복제 인스턴스를 만듭니다.

복제 인스턴스를 생성하려면 AWS DMS 설명서의 지침을 따르세요. 복제 인스턴스는 소스 및 대상 데이터베이스와 동일한 가상 프라이빗 클라우드(VPC)에 있어야 합니다.

DBA, 앱 개발자

소스 및 대상 엔드포인트를 생성합니다.

엔드포인트를 생성하려면 AWS DMS 설명서의 지침을 따르세요.

DBA, 앱 개발자

엔드포인트 연결을 테스트합니다.

VPC 및 복제 인스턴스를 지정하고 테스트 실행을 선택하여 엔드포인트 연결을 테스트할 수 있습니다.

DBA, 앱 개발자

전체 로드 작업을 생성하고 시작합니다.

지침은 AWS DMS 설명서의 작업 생성 및 전체 로드 작업 설정을 참조하세요.

DBA, 앱 개발자

가상화 열의 데이터 유효성을 검사합니다.

원본 데이터베이스와 대상 데이터베이스의 가상화 열에 있는 데이터를 비교합니다. 이 단계에서 데이터 유효성 검사를 수동으로 수행하거나 스크립트를 작성할 수 있습니다.

DBA, 앱 개발자

관련 리소스