- 스키마 복원
RESTORE DATABASE production
FROM S3('https://your-bucket.s3.amazonaws.com/backup-schema', 'access_key', 'secret_key')
SETTINGS structure_only = true;
- 데이터 복원
RESTORE DATABASE production
FROM S3('https://your-bucket.s3.amazonaws.com/backup-full-20250311', 'access_key', 'secret_key');

# 로컬 백업 생성
clickhouse-backup create backup-20250311

# S3로 업로드
clickhouse-backup upload backup-20250311

# 백업 목록 확인
clickhouse-backup list remote

# 새로운 Cloud 환경에서# S3에서 다운로드
clickhouse-backup download backup-20250311

# 복원
clickhouse-backup restore backup-20250311

# 특정 테이블만 복원
clickhouse-backup restore --table=production.events backup-20250311

- OSS 환경에서 실행
CREATE USER readonly_migrator IDENTIFIED BY 'secure_password';
GRANT SELECT ON production.* TO readonly_migrator;

- 레코드 수 비교- OSS에서
SELECT count() FROM production.events;
- Cloud에서
SELECT count() FROM production.events;
- 체크섬 비교- OSS에서
SELECT cityHash64(toString(groupArray(event_time))) as time_hash, cityHash64(toString(groupArray(user_id))) as user_hash
FROM production.events;
- Cloud에서 동일한 쿼리 실행하여 비교

- OSS와 동일한 스키마 생성 (ENGINE만 수정)
CREATE TABLE production.events
( event_time DateTime, user_id UInt64, event_type String, properties String
)
ENGINE = ReplicatedMergeTree()
ORDER BY (event_time, user_id)
PARTITION BY toYYYYMM(event_time);

- 특정 기간의 데이터 수 비교- OSS
SELECT toYYYYMMDD(event_time) as day, count() as cnt
FROM production.events
WHERE event_time >= '2025-02-01' AND event_time < '2025-03-01'
GROUP BY day
ORDER BY day;
- Cloud (동일한 쿼리)- 결과를 비교하여 일치 여부 확인

# Blue-Green 방식 전환
config = {
    'clickhouse_host': 'cloud-cluster.clickhouse.cloud',  # OSS에서 Cloud로 변경
    'clickhouse_port': 9440,
    'secure': True
}

# 또는 Load Balancer를 통한 점진적 전환# 10% -> 50% -> 100% 트래픽을 Cloud로 이동

- Cloud 환경
CREATE TABLE production.events
( event_time DateTime, user_id UInt64, event_type String, properties String
)
ENGINE = ReplicatedMergeTree()
ORDER BY (event_time, user_id)
PARTITION BY toYYYYMM(event_time);

- OSS 환경- remoteSecure를 포함한 Materialized View
CREATE MATERIALIZED VIEW production.events_to_cloud
TO FUNCTION remoteSecure( 'cloud-cluster.clickhouse.cloud:9440', 'production', 'events', 'migration_user', 'secure_password'
)
AS
SELECT *
FROM production.events;

- remoteSecure 또는 S3를 통한 backfill (방법 3-1, 3-2 참조)
INSERT INTO production.events
SELECT *
FROM remoteSecure( 'oss-server.example.com:9440', 'production', 'events', 'readonly_migrator', 'secure_password'
)
WHERE event_time < '2025-03-11 00:00:00'; - MV 활성화 시점 이전

- OSS에서 복제 상태 모니터링
SELECT table, last_exception, total_rows, total_bytes
FROM system.parts
WHERE database = 'production' AND table = 'events';
- Cloud에서 수신 확인
SELECT toStartOfHour(event_time) as hour, count() as events_count
FROM production.events
WHERE event_time > now() - INTERVAL 1 DAY
GROUP BY hour
ORDER BY hour DESC;

# ClickHouse 서버 설정에서 네트워크 버퍼 증가
<max_network_bandwidth>100000000</max_network_bandwidth>  # 100 MB/s
<max_network_bandwidth_for_user>50000000</max_network_bandwidth_for_user>

- 설정 최적화
SET max_threads = 16;
SET max_insert_threads = 8;
SET max_block_size = 1048576;
SET min_insert_block_size_rows = 1048576;

# 연결 테스트
clickhouse-client -h target-host --port 9440 --secure --query "SELECT 1"

# 방화벽 규칙 확인# OSS 서버 IP가 Cloud의 IP Access List에 있는지 확인

- 배치 크기 줄이기
SET max_block_size = 65536;
SET max_insert_block_size_rows = 100000;
- 파티션별로 나누어 처리
INSERT INTO target_table
SELECT * FROM remoteSecure(...)
WHERE partition_id = 'specific_partition';