개요
팀에서 GCP BigQuery와 PostgreSQL을 CDC(Change Data Capture) 방식으로 동기화하는 과정에서 두 저장소가 제대로 동기화되지 않는 문제가 발생했다.
PostgreSQL의 CDC 동작 방식을 깊이 이해하지 못한 상태에서 설정했기 때문에 해결에도 꽤 시간이 걸렸는데, 이번 글에서는 BigQuery와 PostgreSQL의 CDC 동기화 방식을 살펴보고 정리해보자....
CDC란?
CDC(Changed Data Capture)는 데이터의 변경 사항을 감지하고 추적하는 소프트웨어 설계 패턴이다. 일반적인 CDC 방식으로는 Timestamps on rows, Triggers on tables, Event Programming, Log Scanner 등이 있다.
최근 CDC라고 하면 Log Scanner 방식을 지칭하는 경우가 대부분인데 GCP도 Log Scanner 방식을 채택하여 데이터베이스의 트랜잭션 로그를 통해 변경 사항을 감지 -> 동기화를 지원한다.
PostgreSQL의 CDC 지원 방식
대부분의 데이터베이스 관리 시스템은 트랜잭션 로그에 데이터와 메타데이터 변경 사항을 기록하고 이를 사용해 CDC를 지원한다.
PostgreSQL 또한 WAL이라는 로그를 통해 데이터 무결성을 유지하고 동시에 CDC를 지원하지만, 특이하게 Publication, Replication Slot 이라는 조금은 특이한 요소를 사용해 CDC가 가능하도록 지원한다.
- WAL(Write-Ahead Logging)
PostgreSQL도 트랜잭션 로그를 이용해 변경 사항을 추적하며, 이를 WAL(Write-Ahead Logging)이라고 한다.
WAL은 "데이터 파일 변경은 로그에 기록된 이후에만 적용된다"는 원칙에 기반하며, 이 때문에 데이터 변경 시마다 WAL을 위한 추가적인 메모리, 디스크 비용이 소요된다.
이런 단점이 있더라도 PostgreSQL에서는 WAL을 통해 데이터 무결성을 지원하고 있고, 특정 시점 복구(Point-in-Time Recovery)를 지원하며, 이는 장애 복구나 데이터 복원 시 매우 중요한 역할을 수행한다.
- PG Publication
PostgreSQL의 WAL을 사용해 특정 시점 복구를 지원했지만, v10 이전에는 Physical Replication을 사용했기 때문에 특정 데이터에 대한 복제를 지원하지 못했다.
이 방식의 한계점을 개선하기 위해 PostgreSQL v10에서는 Logical Replication 개념이 도입되었고, 이에 중요한 역할을 하는 것이 바로 Publication과 Subscription 테이블이다.
Logical Replication은 하나의 게시자(Publication)와 여러 구독자(Subscription) 간에 논리적으로 데이터 변경 사항을 주고받아, 특정 테이블만 선택적으로 복제할 수 있게 지원하는데, 이 뿐 아니라 insert, update 등 어떤 변경을 복제할지도 지정 가능하다.
- Replication Slot
데이터 복제 시 네트워크 문제 등으로 복제 시점에 데이터 손실이 발생할 수 있다.
PostgreSQL에서는 replication slot을 사용해 데이터 손실을 방지할 수 있도록 지원하는데, 이를 위해 복제를 수행하면서 변경 사항을 지우지 않고 보관해두는 역할을 한다.
이를 통해 구독자가 데이터를 수신하지 못한 경우에도 서버가 데이터를 유지하며 누락 없이 전달할 수 있고, 구독자가 데이터를 읽어 갈 때까지 해당 트랜잭션 로그를 유지하며, pgoutput 형식의 논리적 복제 슬롯을 활용해 구독자가 로그를 추출할 수 있다.
GCP에서의 CDC 사용법
GCP는 Datastream 서비스를 통해 CDC를 지원하며, PostgreSQL의 트랜잭션 로그와 Logical Replication 기능을 활용해 데이터 변경 사항을 BigQuery로 전달한다. BigQuery는 이를 통해 실시간 데이터 분석과 데이터 웨어하우스 기능을 수행한다. GCP Datastream은 PostgreSQL의 WAL 로그를 기반으로 하는 Log Scanner 방식으로, 트랜잭션 로그의 변경 사항을 주기적으로 확인하여 BigQuery로 전달해 데이터 최신 상태를 유지한다.
- PostgreSQL 설정
CDC를 위해 PostgreSQL에서 특정 설정을 해야 한다.
- 복제 권한 부여
ALTER USER USER_NAME WITH REPLICATION;- Publication 테이블 설정
CREATE PUBLICATION PUBLICATION_NAME
FOR TABLE SCHEMA1.TABLE1, SCHEMA2.TABLE2;- Replication Slot 설정
SELECT PG_CREATE_LOGICAL_REPLICATION_SLOT('REPLICATION_SLOT_NAME', 'pgoutput');이처럼 GCP에서 Datastream을 설정하기 위해 복제를 위한 권한을 부여하고, 구독할 publication 테이블과 replication slot을 미리 설정해야 한다.
- GCP Datastream 생성 절차
-
Datastream 프로필 설정
스트림 프로필을 설정하고, Source Type과 Destination Type을 지정한다.
-
DB 연결 설정
복제를 위해 사용할 DB User로 PostgreSQL과 GCP를 연결하는 단계다. 이때 앞서 설정한 replication 권한을 가진 사용자를 입력해 DB에 접속한다.
-
대상 테이블 및 복제 속성 설정
이 단계에서는 구독할 publication, replication slot을 지정하고, 어떤 테이블을 대상으로 복제할지 설정한다.
여기서 Backfill 설정도 선택할 수 있는데, 기존 데이터 복제와 함께 CDC 이벤트를 수행할지, 또는 CDC만 실행할지를 결정할 수 있다.사내 서비스에서는 DB 부하 우려와 초/분단위 데이터 정합성이 중요한 것 보다는 일 단위 데이터 정합성이 중요했기 때문에 매일 수동으로 Backfill 트리거하는 방식을 택했다.
-
복제 위치 설정
데이터 복제 시 하나의 dataset에 모든 schema를 복제할지, schema별로 dataset을 구분할지 선택한다. 이외에도 복제 옵션으로 Merge와 Append 중 하나를 설정할 수 있고,
staleness를 통해 동기화 주기를 초 단위로 조정할 수 있다.
- BigQuery에서 데이터 확인
SELECT
project_id,
destination_table.dataset_id,
destination_table.table_id,
APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)] AS p95_background_apply_duration_in_seconds,
CEILING(APPROX_QUANTILES((TIMESTAMP_DIFF(end_time, creation_time,MILLISECOND)/1000), 100)[OFFSET(95)]*2/60)+7 AS recommended_max_staleness_with_buffer_in_minutes
FROM `region`.INFORMATION_SCHEMA.JOBS AS job
WHERE
DATE(creation_time) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND CURRENT_DATE()
AND job_id LIKE "%cdc_background%"
GROUP BY 1,2,3;설정이 완료된 후 Datastream이 실행되면 PostgreSQL의 데이터를 BigQuery로 복제해주는 작업이 시작되는데, 언급한 쿼리를 사용해 검사를 하면 CDC 이벤트가 발생하고, 실제 동기화된 시점을 볼 수 있다.
마무리
이 글에서는 PostgreSQL에서 CDC를 지원하는 방식과 GCP에서 이를 연결하는 방식을 알아봤다.
기존 MySQL만을 사용하다 PostgreSQL이 CDC를 지원하는 방식을 이해하지 못하고 이를 적용했을 때 삽질을 많이했는데, 덕분에 기존 구조를 더 이해하게 된 기회가 되었다.
