スキーマ変更とロールバックスクリプトの作成

한국어 원문으로 표시합니다.

목표

확장-이관-축소 패턴으로 스키마를 변경하고, 롤백 스크립트와 배치 백필, 검증 스크립트, 작업 절차서까지 갖춘 변경관리 한 세트를 만들 수 있게 됩니다.

왜 중요한가

"down 스크립트가 있으니 안전하다"는 가장 흔한 착각입니다. DROP COLUMN 을 되돌리는 down 은 컬럼 구조만 되살릴 뿐 값은 못 되살립니다. 가역성은 코드의 속성이 아니라 코드와 데이터와 시간의 조합이라서, 같은 변경도 어제 배포했으면 가역이고 백만 건이 쌓인 뒤엔 비가역입니다. 그리고 대량 백필을 한 트랜잭션으로 돌리면 잠금과 로그가 폭증해 읽기 복제본이 밀리고, 조회 서비스가 오래된 데이터를 보여 줍니다. 이 실습은 그 함정들을 손으로 피해 보는 훈련입니다.

단계

준비: dbo-schema 실습의 /root/db/si.db 가 있어야 합니다. 없다면 /opt/lab/fixtures/dbo/si-base.sql 로 만들 수 있습니다.
현재 스키마를 /root/db/schema_v1.sql 로 저장하고, /root/db/baseline.txt 를 만듭니다. 세 줄입니다.
```
orders_cnt=<ORDERS 행 수>
orders_amt=<ORDERS 의 ORD_AMT 합계>
item_qty=<ORDER_ITEM 의 QTY 합계>
```
/root/db/changelog.csv 를 만듭니다. 첫 줄은 chg_id,date,object,ddl_file,rollback_file,est_sec,approver. 3~5 단계에서 만들 변경 3건을 미리 등록합니다. ddl_file·rollback_file·approver 는 비어 있으면 안 되고, est_sec 에는 숫자가 들어가야 합니다.
/root/db/mig/V2__add_dlvr_sts.sql 을 만듭니다.
- ORDERS 에 DLVR_STS_CD 컬럼 추가 (기본값 '01')
- 기존 행을 모두 '01' 로 채움 적용 후 DLVR_STS_CD 가 NULL 인 행이 0건이어야 합니다.
/root/db/mig/V2__rollback.sql 을 만듭니다. 적용하면 스키마가 schema_v1.sql 과 같아져야 합니다. (si.db 사본에서 up → down 을 실행해 확인하세요.)
/root/db/mig/V3__item_qty_check.sql 을 만듭니다. ORDER_ITEM.QTY 의 CHECK 를 1 이상 9999 이하로 강화합니다. sqlite 는 제약 변경을 지원하지 않으므로 테이블 재작성 절차가 필요합니다. 적용 후에도 ORDER_ITEM 행 수와 QTY 합계가 1단계 기준선과 같아야 하고, 외래키 관계도 유지돼야 합니다.
/root/db/batch-update.sh 를 만듭니다. 인자 두 개(DB파일 배치크기)를 받아 ORDERS 의 DLVR_STS_CD 가 '01' 인 행을 '02' 로 바꾸되, 한 번에 배치크기만큼씩 반복 처리합니다. 마지막 줄에 batches=<반복횟수> updated=<총건수> 를 출력합니다. (원본을 더럽히지 않도록 인자로 받은 DB 에만 적용합니다.)
/root/db/mig-verify.sh 를 만듭니다. 인자 두 개(기준선파일 DB파일)를 받아 기준선의 세 지표를 현재 DB 와 비교하고, 모두 같으면 첫 줄에 OK, 다르면 NG 로 시작하는 줄과 함께 어떤 지표가 다른지 출력합니다. 종료코드도 각각 0 과 0 이 아닌 값입니다.
/root/db/mig-runbook.md 를 작성합니다. ## 작업 개요, ## 사전 백업, ## 적용 절차, ## 검증, ## 롤백 기준, ## 롤백 절차 여섯 개의 h2 제목이 있어야 하고, 본문에 백업 파일 경로와 숫자로 된 롤백 기준(소요 시간 또는 검증 실패 건수)이 들어가야 합니다.

참고

스키마 덤프: sqlite3 /root/db/si.db .schema > schema_v1.sql
테이블 재작성 순서: PRAGMA foreign_keys=off → 새 테이블 생성 → INSERT ... SELECT → 기존 테이블 DROP → RENAME → 인덱스 재생성 → PRAGMA foreign_keys=on
영향 행 수: SELECT changes();
흔한 실수 1: 사본을 만들지 않고 원본에서 시험해 되돌릴 수 없게 되는 것.
흔한 실수 2: 테이블 재작성 때 인덱스를 다시 만들지 않는 것. DROP 하면 인덱스도 함께 사라집니다.
흔한 실수 3: 배치 백필 루프에 종료 조건이 없어 무한 루프가 되는 것. 영향 행이 0이면 멈춰야 합니다.

스키마 스냅샷과 기준선

현재 스키마를 /root/db/schema_v1.sql 로 저장하고, /root/db/baseline.txt 를 만듭니다. 세 줄입니다.

orders_cnt=<ORDERS 행 수>
orders_amt=<ORDERS 의 ORD_AMT 합계>
item_qty=<ORDER_ITEM 의 QTY 합계>

작업 후에 '원래 몇 건이었죠'를 묻게 되면 이미 늦습니다. 스키마와 데이터 지표를 모두 남기세요.

변경관리 대장

/root/db/changelog.csv 를 만듭니다. 첫 줄은 chg_id,date,object,ddl_file,rollback_file,est_sec,approver. 3~5 단계에서 만들 변경 3건을 미리 등록합니다. ddl_file·rollback_file·approver 는 비어 있으면 안 되고, est_sec 에는 숫자가 들어가야 합니다.

롤백 스크립트 경로를 필수 항목으로 두면, 대장을 채우다가 '이건 되돌릴 수 없네'를 배포 전에 깨닫게 됩니다.

확장 스크립트

/root/db/mig/V2__add_dlvr_sts.sql 을 만듭니다.

ORDERS 에 DLVR_STS_CD 컬럼 추가 (기본값 '01')
기존 행을 모두 '01' 로 채움 적용 후 DLVR_STS_CD 가 NULL 인 행이 0건이어야 합니다.

컬럼을 추가하고 기존 행을 채웁니다. NULL 허용으로 추가한 뒤 채우는 것과 처음부터 NOT NULL 로 추가하는 것의 차이를 생각해 보세요.

롤백 스크립트

/root/db/mig/V2__rollback.sql 을 만듭니다. 적용하면 스키마가 schema_v1.sql 과 같아져야 합니다. (si.db 사본에서 up → down 을 실행해 확인하세요.)

되돌린 뒤의 스키마가 원래와 같은지 확인할 수 있어야 합니다. 스냅샷을 비교하는 것이 가장 확실합니다.

테이블 재작성 마이그레이션

/root/db/mig/V3__item_qty_check.sql 을 만듭니다. ORDER_ITEM.QTY 의 CHECK 를 1 이상 9999 이하로 강화합니다. sqlite 는 제약 변경을 지원하지 않으므로 테이블 재작성 절차가 필요합니다. 적용 후에도 ORDER_ITEM 행 수와 QTY 합계가 1단계 기준선과 같아야 하고, 외래키 관계도 유지돼야 합니다.

제약을 바꾸려면 새 테이블을 만들어 옮기는 절차가 필요합니다. 순서를 지키지 않으면 데이터나 참조가 깨집니다.

배치 백필

/root/db/batch-update.sh 를 만듭니다. 인자 두 개(DB파일 배치크기)를 받아 ORDERS 의 DLVR_STS_CD 가 '01' 인 행을 '02' 로 바꾸되, 한 번에 배치크기만큼씩 반복 처리합니다. 마지막 줄에 batches=<반복횟수> updated=<총건수> 를 출력합니다. (원본을 더럽히지 않도록 인자로 받은 DB 에만 적용합니다.)

대량 UPDATE 를 한 트랜잭션으로 하면 잠금과 로그가 폭증합니다. 영향 행이 0이 될 때까지 반복하는 구조로 만드세요.

마이그레이션 검증 스크립트

/root/db/mig-verify.sh 를 만듭니다. 인자 두 개(기준선파일 DB파일)를 받아 기준선의 세 지표를 현재 DB 와 비교하고, 모두 같으면 첫 줄에 OK, 다르면 NG 로 시작하는 줄과 함께 어떤 지표가 다른지 출력합니다. 종료코드도 각각 0 과 0 이 아닌 값입니다.

행 수와 합계만으로는 '한 건이 빠지고 다른 한 건이 두 배가 된' 상황을 못 잡습니다. 지표를 여러 개 두세요.

작업 절차서

/root/db/mig-runbook.md 를 작성합니다. ## 작업 개요, ## 사전 백업, ## 적용 절차, ## 검증, ## 롤백 기준, ## 롤백 절차 여섯 개의 h2 제목이 있어야 하고, 본문에 백업 파일 경로와 숫자로 된 롤백 기준(소요 시간 또는 검증 실패 건수)이 들어가야 합니다.

새벽에 읽을 문서입니다. 명령을 그대로 복사해 쓸 수 있어야 하고, 언제 멈추고 되돌릴지가 숫자로 적혀 있어야 합니다.