내용 x
    PostgreSQL에서 BigQuery로 전송(CDC)
      • 인쇄
      • PDF
      내용

      PostgreSQL에서 BigQuery로 전송(CDC)

        • 인쇄
        • PDF
        기사 요약

        개요

        PostgreSQL의 WAL에서 CDC(Change Data Capture) 방식으로 변경된 데이터만 식별하여 BigQuery로 전송할 수 있습니다. 이를 통해 전송량을 줄이고 전송 속도를 높일 수 있습니다.
        처음 실행할 때 모든 건을 전송해야 합니다. 이후 전송 시에는 차액만 CDC 방식으로 연동합니다.

        PostgreSQL CDC 이용 시

        • 전송 설정마다(CDC에서 전송할 테이블마다) 절차에 따라 직접 복제 슬롯을 생성해야 합니다.

          • 현재 복제 슬롯의 자동 생성 및 삭제는 지원하지 않습니다.
          • 전송 설정을 삭제해도 복제 슬롯은 자동으로 삭제되지 않습니다. 반드시 삭제하는 것을 잊지 마세요.
          • 복제 슬롯을 삭제하는 것을 잊어버린 경우, WAL이 스토리지를 압박하여 DB가 충돌할 수 있습니다.

        • PostgreSQL CDC를 이용할 경우, DB의 저장공간에 충분한 여유를 두어야 합니다.

          • 스토리지 크기를 모니터링하는 것이 좋습니다.

        필수 조건

        PostgreSQL 대응표

        MasterRead Replica지원 버전
        Amazon Aurora PostgreSQL RDS○x10.6+
        Amazon PostgreSQL RDS○x9.4+
        Google CloudSQL PostgreSQLxx
        Herokuxx
        PostgreSQL○x9.4+

        PostgreSQL 파라미터 설정

        rds.logical_replication= 1 # RDS인 경우에만 설정합니다.
wal_level=logical # RDS가 아닌 경우 설정해 주세요.
max_replication_slots= 5 # 연동할 테이블과 동일한 개수를 설정합니다.
max_wal_senders= 5 # 연동할 테이블과 동일한 숫자를 설정합니다.

        WAL 설정을 반영하기 위해 DB 재부팅이 필요할 수 있습니다.

        Slot 생성

        • 연동하는 테이블마다(전송 설정마다) 별도의 Slot을 생성해야 합니다.
          • SUPERUSER로 로그인 후 아래 SQL로 슬롯을 생성해 주세요.
            현재는 wasl2json만 지원하고 있습니다.
        SELECT *
FROM pg_create_logical_replication_slot('trocco_<table_name>', 'wal2json');
        • 아래 SQL로 Slot에서 데이터를 가져올 수 있는지 확인할 수 있습니다.
        SELECT COUNT(*)
FROM pg_logical_slot_peek_changes('<replication_slot_name>', null, null);

        연결 사용자 권한

        • 아래 권한이 필요합니다.

        CONNECT

        예시:
GRANT CONNECT ON DATABASE <database_name> TO <사용자 이름>;

        사용방법

        예시:
GRANT USAGE ON SCHEMA <schema_name> TO <username>에 대한 사용권 부여

        SELECT

        예시:
GRANT SELECT ON <schema_name>. <table_name> TO <username>.

        rds_replication(RDS만 해당)

        예시:
GRANT rds_replication TO <사용자 이름>에게

        ALTER DEFAULT PRIVILEGES
        SELECT를 부여한 후 데이터베이스의 모든 스키마에 접근할 수 있도록 한다.

        예시:
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <사용자 이름>에 대한 기본 권한 부여

        전송원 테이블의 Primary Key

        • 전송 원본 테이블에는 Primary Key 설정이 필요합니다.
          • 여러 개의 Primary Key(Composite Primary Key)도 지원합니다.

        전송 시 변환 기능

        PostgreSQL to BigQuery의 CDC 전송은 다음을 지원합니다.
        * 컬럼 드롭
        * 컬럼 데이터 변환
        * 컬럼 이름 변경

        컬럼의 데이터 변환은 BigQuery에서 CAST로 이루어집니다. 그래서 BigQuery의 CAST 규칙을 따릅니다.
        자세한 내용은 아래를 참고하세요.
        https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules?hl=ja

        메타데이터

        • 아래 컬럼이 추가되어 DWH와 연동됩니다. 따라서 아래는 컬럼 이름에 사용할 수 없습니다.
        칼럼명설명
        _trocco_fetched_attorcco의 전송 작업에 의해 전송이 시작된 시간
        • 또한, 임시 테이블에 대한 데이터 연동에 이용하기 때문에 아래도 컬럼 이름에 사용할 수 없습니다.
        칼럼명설명
        _trocco_deleted논리 삭제 플래그. true일 때 데이터가 삭제되었음을 의미함.
        _trocco_seq전송 내 WAL 순서 보장

        전송처 PostgreSQL의 SQL 지원

        • INSERT
        • 업데이트
        • DELETE

        스키마 변경(ALTER) 시 동작

        스키마 변경 작업SQL 예시스키머 추종 활성화 시 동작스키마 추종 비활성화 시 동작
        컬럼 추가ALTER TABLE <table_name> ADD <column_name> <data_type> <data_typeBigQuery에 컬럼이 추가됩니다.
        컬럼의 형태는 TROCCO 측에서 결정한 것이 사용됩니다.
        단, 컬럼에 기본값이 설정되어 있는 경우 기본값은 BigQuery에 반영되지 않으므로 모두 전송해야 합니다.        		추가 컬럼은 BigQuery에 추가되지 않습니다.
        TROCCO의 전송 설정 편집에서 대상 컬럼 설정을 추가하면 BigQuery 측에 컬럼이 추가됩니다.
        신규 컬럼 설정 후 업데이트가 있는 레코드만 값을 가져옵니다.
        모든 값을 가져오려면 전체 전송을 수행해야 합니다.
        컬럼 삭제ALTER TABLE <table_name> DROP <column_name>삭제된 컬럼은 BigQuery에서 삭제되지 않습니다.스키머 추종 활성화 시와 동일한 거동
        컬럼 타입 변경ALTER TABLE <table_name> ALTER <column_name> TYPE <data_type변경된 금형이 금형 변경이 가능하다면 계속 전송할 수 있습니다. 오류가 발생한 경우 모든 건을 전송해야 합니다.스키마 추종 활성화 시와 동일한 동작
        컬럼 이름 변경ALTER TABLE <table_name> RENAME <old_column_name> TO <new_column_name>으로 변경변경된 컬럼 이름으로 BigQuery에 새로운 컬럼이 추가됩니다.
        컬럼 이름 변경 후 업데이트된 레코드만 새로운 컬럼의 값이 업데이트됩니다.
        컬럼 이름 변경 전의 컬럼 값은 변경 후의 새로운 컬럼으로 이어지지 않습니다. 모든 것을 가져오려면 모든 건을 전송해야 합니다.
        또한, 컬럼 이름 변경 전의 컬럼은 BigQuery에서 삭제되지 않습니다.        		변경된 컬럼은 BigQuery에 추가되지 않습니다.
        변경 전 컬럼은 업데이트된 레코드에 대해 NULL 값이 됩니다.

        지원되지 않는 SQL

        • 아래 SQL은 WAL에서 캐치할 수 없기 때문에 데이터가 DWH로 제대로 전송되지 않습니다.
          TRUNCATE
          DROP
          LOAD
          RENAME
          캐스케이딩 삭제
          캐스케이딩 업데이트
          ALTER TABLE ... SET DEFAULT
          이미 연동되어 있는 데이터가 NULL인 경우 NULL인 상태로 업데이트되지 않습니다.

        PostgreSQL과 BigQuery의 스키마 타입

        • 데이터 타입 변환 기능에서 변경이 가능한 데이터 타입에서는 지정한 타입으로 변환할 수 있습니다.
          • 기본적으로 아래와 같이 대응하고 있습니다.
        PostgreSQL 데이터 유형BigQuery 데이터 유형
        CHARACTER VARYING
        CHARACTERTER
        CIDR
        CITEXT
        MACADDR
        TEXT
        TIME WITH TIMEZONE
        TIME WITHOUT TIMEZONE
        UUID
        TIME
        INTERVAL        		STRING
        BOOLEANBOOLEAN
        BIGINT
        BIG SERIAL
        INTEGER
        SERIAL
        NUMERIC
        DECIMAL
        SMALL INT
        SMALL SERIAL        		INTEGER
        DOUBLE PRECISION
        REAL
        MONEY        		FLOAT
        DATEDATE
        TIMESTAMP
        DATETIME
        TIMESTAMP WITH TIMEZONE
        TIMESTAMP WITHOUT TIMEZONE        		TIMESTAMP
        GEOGRAPHY
        GEOMETRY
        HSTORE
        JSON
        JSONB
        POINT
        TSRANGE
        TSTZRANGE
        BOX
        PATH        		STRING(STRUCT는 현재 지원하지 않습니다.)

        설정의 흐름

        1. 전송 설정 생성

        전송처 PostgreSQL WAL, 전송처 BigQuery를 선택하고 전송 설정을 생성합니다.
        image.png

        2. 첫 번째 전체 전송을 수행합니다.

        최초 1회만 전체 전송을 수행하여 현재 PostgreSQL의 테이블을 모두 BigQuery로 전송합니다.
        신규 작업 실행 화면에서****전체 전송을 체크하고 작업을 실행합니다.
        image.png

        3. 차등 전송 스케줄을 설정합니다.

        스케줄 설정 화면에서 스케줄을 등록해 주세요.
        설정한 빈도로 작업이 시작되고 차분한 작업이 실행됩니다.

        전건 전송이 필요한 경우

        다음과 같은 경우 전건 전송이 필요합니다.

        • 첫 번째 전송
        • BigQuery에서 타입 변환 런타임 오류가 발생한다.
        • 논리적 삭제가 아닌 모든 데이터를 지우고 싶다
          전송 중인 테이블을 삭제한 후 실행하십시오.
        • '전송처 PostgreSQL의 SQL 대응'에서 설명한 쿼리를 실행하는 경우
        이 문서가 도움이 되었습니까?
        What's Next