本番DB変更手順メモ

前提

Webアプリケーション開発における本番DB変更を対象とする。
作業は一人で実施する。担当者決定、承認者決定、監視担当者決定、会議体調整は扱わない。
対象はスキーマ変更、データ変更、スキーマ変更とアプリケーション変更が同時に必要な作業。
SQL、マイグレーション、確認クエリ、ロールバッククエリ、実行ログを作業単位で保存する。
DBMS固有のコマンド名はこのメモでは固定しない。採用DBの公式マニュアルで該当構文、ロック、トランザクション、バックアップ、リストアを確認する。
本番DBで最初に実行する作業を作らない。ステージング、複製環境、ローカル再現環境、本番トランザクション内検証の順で実行前確認を増やす。

用語

DDL: テーブル、カラム、インデックス、制約、ビュー、トリガーを含む定義変更SQL。
DML: INSERT、UPDATE、DELETEを含む行データ変更SQL。
反映SQL: 本番DBへ適用するSQLまたはマイグレーション。
戻しSQL: 反映SQLの結果を戻すSQL。DDLの戻し、DMLの補償更新、バックアップからの復元を含む。
確認SQL: 反映前後の件数、制約違反、NULL、重複、参照整合性、アプリケーション期待値を確認するSQL。
対象行退避: DML対象行を復元可能な形式で保存する作業。

0. 共通方針

0.1 作業ファイル構成

作業単位でディレクトリを作成する。

db-ops/
  YYYYMMDD_change_name/
    README.md
    00_precheck.sql
    01_migrate.sql
    02_verify.sql
    03_rollback.sql
    04_app_checklist.md
    logs/

各ファイルに次を記録する。

change_id:
対象本番DB識別子:
対象アプリケーション:
反映前アプリケーションバージョン:
反映後アプリケーションバージョン:
対象テーブル:
対象カラム:
対象インデックス:
対象制約:
バックアップID:
バックアップ取得時刻:
リストア確認有無:
反映SQL:
確認SQL:
戻しSQL:
作業中止条件:

0.2 本番直接手打ちを避ける

本番SQLクライアントで即興SQLを作成しない。
本番で実行するSQLはファイル化する。
本番で実行したコマンド、標準出力、標準エラー、開始時刻、終了時刻を保存する。
SQLファイルを実行する前に接続先DB名、ホスト名、ユーザー名、スキーマ名を確認する。
本番DB接続用ターミナルと検証DB接続用ターミナルを同時に開かない。
本番DB接続後、最初に接続先確認SQLのみ実行する。

0.3 DB変更の分類

変更を以下に分類する。

分類	内容	主な確認対象
スキーマ追加	テーブル追加、NULL許容カラム追加、インデックス追加、制約追加	ロック、実行時間、アプリ互換性
スキーマ変更	型変更、NOT NULL化、UNIQUE化、外部キー追加、カラム名変更、テーブル名変更	既存データ、ロック、アプリ互換性、戻し手順
データ変更	UPDATE、DELETE、INSERT、バックフィル	対象件数、WHERE句、復元可能性、実行時間
破壊的変更	DROP、TRUNCATE、データ削除、不可逆な型変換	バックアップ、対象行退避、復旧時間、実施可否

0.4 アプリケーション互換性

DB変更とアプリケーション反映の順序を決める。

状態	実行順序
旧アプリが新DBスキーマで動作する	DB変更後にアプリ反映可能
新アプリが旧DBスキーマで動作する	アプリ反映後にDB変更可能
旧アプリと新DB、新アプリと旧DBのどちらかが動作しない	メンテナンスモードまたは段階反映が必要
旧カラム削除、旧テーブル削除、カラム名変更を含む	旧参照をアプリから消した後、別作業で削除

段階反映の順序。

互換性のあるDB追加を先に入れる。
新アプリを反映する。
データ移行がある場合はバックフィルを実行する。
新旧の読み書きが一致することを確認する。
旧カラム、旧テーブル、旧処理の削除は別作業にする。

0.5 戻し方針

戻し手順を次の順に決める。

トランザクション内ROLLBACK。
戻しSQL。
アプリケーション差し戻し。
対象行退避からの復元。
フルバックアップまたはスナップショットからのリストア。
前方修正SQL。

DDLはDBMSによりトランザクション挙動が異なる。DDLをトランザクションで戻せる前提にしない。

0.6 バックアップ方針

本番反映前にフルバックアップまたはスナップショットを取得する。
バックアップID、取得時刻、対象DB、保存先、保持期限を記録する。
複製環境またはステージング環境がある場合は、同じ方式でリストアを実施する。
DMLがある場合は対象行退避を実施する。
対象行退避には、対象SQL、件数、保存先、復元SQL、取得時刻を記録する。
個人情報を含むバックアップ、ダンプ、対象行退避ファイルは保存場所を限定する。
作業終了後、不要なローカルダンプと一時退避ファイルを削除する。

0.7 事前確認SQL

反映前に確認する。

対象テーブルの行数。
UPDATE、DELETE対象件数。
NOT NULL追加対象カラムのNULL件数。
UNIQUE追加対象カラムの重複件数。
外部キー追加対象の孤立データ件数。
インデックス追加対象カラムのNULL比率と重複状況。
対象SQLの実行計画。
DDL対象テーブルのロック状態。
長時間トランザクションの有無。
DB空き容量。
アプリケーションの対象機能、バッチ、ジョブ、API、管理画面の参照箇所。

DML実行前テンプレート。

-- 対象件数
SELECT COUNT(*)
FROM {{table_name}}
WHERE {{condition}};

-- 対象主キー確認
SELECT {{primary_key}}
FROM {{table_name}}
WHERE {{condition}}
ORDER BY {{primary_key}}
LIMIT {{limit}};

トランザクション内確認テンプレート。

BEGIN;

{{DML}}

-- 変更後確認
SELECT COUNT(*)
FROM {{table_name}}
WHERE {{post_condition}};

-- 戻す場合
ROLLBACK;

-- 確定する場合
-- COMMIT;

DDLは採用DBの公式マニュアルで、該当DDLのロック、暗黙COMMIT、トランザクション可否を確認してから実行する。

0.8 作業中止条件

以下のいずれかに該当する場合、本番反映を開始しない。

本番DB識別子を確認できない。
バックアップIDを記録できない。
リストア手順を説明できない。
反映SQL、確認SQL、戻しSQLのいずれかが存在しない。
UPDATE、DELETEの対象件数が想定件数と一致しない。
NOT NULL、UNIQUE、外部キー追加前のデータ検査で違反が残っている。
DDLのロックレベル、暗黙COMMIT、トランザクション可否を確認できない。
アプリケーション反映順序を決めていない。
メンテナンスモードが必要な変更で、メンテナンスモードの開始と解除を実行できない。
外部通知、メール、決済、Webhook、外部APIへの実送信を止められない検証環境を使っている。

1. ステージング環境が存在する場合

1.1 ステージングDBを本番相当にする

本番DBからバックアップまたはスナップショットを取得する。
ステージングDBへリストアする。
個人情報、認証情報、決済情報、トークン、メールアドレス、電話番号、住所をマスキングまたは削除する。
ステージングアプリケーションの接続先がステージングDBであることを確認する。
ステージング環境で本番用秘密情報を使っていないことを確認する。
メール送信、SMS送信、プッシュ通知、決済、Webhook、外部APIの本番送信を停止またはサンドボックス接続にする。
本番で動いているバッチ、ジョブ、Cron、キューのうち、検証に不要なものを停止する。
検証に必要なバッチ、ジョブ、Cron、キューはステージング用設定で起動する。

1.2 ステージングでの反映リハーサル

ステージングのアプリケーションバージョンを本番反映前の状態に合わせる。
00_precheck.sql を実行する。
事前確認結果を logs/staging-precheck.log に保存する。
01_migrate.sql またはマイグレーションコマンドを実行する。
実行時間、対象件数、標準出力、標準エラーを logs/staging-migrate.log に保存する。
02_verify.sql を実行する。
アプリケーションを反映する必要がある場合、予定順序で反映する。
対象機能を確認する。
周辺機能を確認する。
バッチ、ジョブ、キューを確認する。
APIを確認する。
管理画面を確認する。
外部連携はステージング用送信先で確認する。
エラーログを確認する。
DB負荷、ロック、スロークエリを確認する。

対象機能確認項目。

作成:
更新:
削除:
一覧:
詳細:
検索:
CSV出力:
APIレスポンス:
バッチ実行:
ジョブ再実行:
管理画面:
権限別表示:

1.3 ステージングでの戻し確認

03_rollback.sql またはマイグレーションロールバックコマンドを実行する。
02_verify.sql の反対確認を実行する。
旧アプリケーションが戻し後DBで動作することを確認する。
DML対象行を対象行退避から復元できることを確認する。
フルリストアが必要な戻しの場合、ステージングDBをバックアップからリストアする。
戻しに要した時刻、件数、実行ログを保存する。
戻し後、再度 01_migrate.sql を実行し、再反映可能性を確認する。

1.4 ステージング完了条件

本番反映へ進む条件。

反映SQLがステージングで成功している。
確認SQLが期待値と一致している。
戻しSQLがステージングで成功している。
対象機能の確認が完了している。
バッチ、ジョブ、API、管理画面の確認が完了している。
実行時間を記録している。
ロック、スロークエリ、DB負荷を確認している。
本番反映順序が確定している。
本番バックアップ手順が確定している。

1.5 本番反映手順

反映前

作業ディレクトリのSQLとREADMEを最終確認する。
本番DB接続先を確認する。
本番アプリケーションバージョンを確認する。
メンテナンスモードが必要な場合は開始する。
本番バックアップまたはスナップショットを取得する。
バックアップID、取得時刻、対象DBを記録する。
DMLがある場合は対象行退避を実施する。
00_precheck.sql を実行する。
事前確認結果がステージング結果と矛盾しないことを確認する。
事前確認結果が想定件数と一致することを確認する。

反映

予定順序どおりにアプリケーション反映またはDB変更を開始する。
01_migrate.sql またはマイグレーションコマンドを実行する。
実行ログを保存する。
02_verify.sql を実行する。
アプリケーション反映が未実施の場合は反映する。
キャッシュ、設定キャッシュ、スキーマキャッシュ、ORMキャッシュがある場合はクリアまたはプロセス再起動を実施する。
対象機能を確認する。
バッチ、ジョブ、API、管理画面を確認する。
エラーログ、DB負荷、ロック、スロークエリを確認する。
メンテナンスモードを使った場合は解除する。

反映後

反映直後のアプリケーションログを確認する。
5分後のアプリケーションログを確認する。
15分後のアプリケーションログを確認する。
DBエラー、ロック待ち、スロークエリ、ジョブ失敗を確認する。
監視項目に異常がある場合、障害発生時手順へ移行する。
作業ログ、SQL、バックアップID、確認結果を保存する。

2. ステージング環境は無いが作成可能な場合

2.1 複製環境の作成方針

本番複製環境は、本番DB変更のリハーサルを目的に作成する。本番DBと同じデータを扱うため、本番と同等の保護対象として扱う。

2.2 インフラ複製

本番と同じDBエンジンを用意する。
本番と同じDBメジャーバージョンを用意する。
本番と同じ文字コード、照合順序、タイムゾーン、拡張機能、ストレージ設定を確認する。
本番アプリケーションと同じランタイムバージョンを用意する。
本番アプリケーションと同じ環境変数名を用意する。
値は検証用に置換する。
本番秘密情報は複製環境へ持ち込まない。
複製環境から本番外部サービスへ送信できないようにする。
複製環境から本番DBへ接続できないようにする。
複製環境のアクセス元を自分の作業端末または検証ネットワークに限定する。

2.3 DB複製

本番DBからバックアップまたはスナップショットを取得する。
バックアップID、取得時刻、対象DBを記録する。
複製DBへリストアする。
リストア完了後、DB名、ホスト名、スキーマ数、テーブル数、主要テーブル件数を確認する。
個人情報、認証情報、決済情報、トークン、メールアドレス、電話番号、住所をマスキングまたは削除する。
マスキング後に主要テーブル件数が変化していないことを確認する。削除方針を採用した項目は削除件数を記録する。
複製DBのユーザー権限を検証用に限定する。
複製DBのバックアップ、ダンプ、一時ファイルの保存先を記録する。

2.4 アプリケーション複製

本番反映前のアプリケーションバージョンをデプロイする。
DB接続先を複製DBにする。
メール、SMS、プッシュ通知、決済、Webhook、外部APIを停止または検証用接続先にする。
ストレージ、CDN、検索エンジン、キュー、キャッシュの接続先を検証用にする。
起動確認を行う。
ログに本番秘密情報が出ていないことを確認する。
対象機能の起動確認を行う。

2.5 複製環境での検証

以降は「1.2 ステージングでの反映リハーサル」「1.3 ステージングでの戻し確認」と同じ手順を実施する。

追加で確認する項目。

複製環境作成に使用したバックアップが本番バックアップとして復旧に使える形式か。
複製環境へのリストア所要時間。
マスキング処理後も対象機能の検証に必要なデータ関係が残っているか。
本番送信系の副作用が発生しない設定になっているか。
複製環境を破棄する手順。
複製環境に置いたダンプ、一時ファイル、ログの削除手順。

2.6 本番反映手順

本番反映手順は「1.5 本番反映手順」と同じにする。

追加で実施する項目。

複製環境で取得した実行時間を本番作業メモに転記する。
複製環境で発生した警告、ロック、スロークエリを本番作業メモに転記する。
複製環境で使ったSQLファイルと本番で実行するSQLファイルの差分が無いことを確認する。
本番反映完了後、複製環境を削除する。
複製環境のダンプ、ログ、一時退避ファイルを削除する。
複製環境に秘密情報を誤投入した場合は該当秘密情報をローテーションする。

3. 本番環境しか存在せず、ステージング環境も作れない場合

3.1 方針

本番のみの場合でも、SQLファイル、確認SQL、戻しSQL、バックアップ、実行ログは省略しない。
スキーマだけはローカルまたは一時DBで再現する。
DMLは本番トランザクション内でROLLBACK確認を行う。
DDLはDBMS公式マニュアルでロック、暗黙COMMIT、トランザクション可否を確認する。
破壊的変更は同一作業に含めない。旧参照停止後の別作業にする。
戻せないDDLは、前方修正またはリストアが復旧手段になることを作業前に記録する。

3.2 ローカル再現

本番スキーマ定義を取得する。
ローカルまたは一時DBにスキーマを作成する。
マイグレーション履歴を再現する。
01_migrate.sql を実行する。
03_rollback.sql を実行する。
再度 01_migrate.sql を実行する。
構文エラー、制約名衝突、インデックス名衝突、マイグレーション番号衝突が無いことを確認する。
DMLがある場合、検証用データで対象条件が意図した行だけに一致することを確認する。

ローカル再現で確認できない項目。

本番データ分布。
本番テーブルサイズ。
本番ロック状況。
本番実行時間。
本番固有のインデックス利用状況。
本番の長時間トランザクション。

上記は本番事前確認SQLで確認する。

3.3 本番事前確認

本番DB接続先を確認する。
本番バックアップまたはスナップショットを取得する。
バックアップID、取得時刻、対象DBを記録する。
DMLがある場合は対象行退避を実施する。
対象件数確認SQLを実行する。
実行計画確認SQLを実行する。
NULL、重複、孤立データの確認SQLを実行する。
ロック状態と長時間トランザクションを確認する。
DB空き容量を確認する。
アプリケーションエラーログの作業前状態を保存する。
ジョブキューの作業前状態を保存する。
DB負荷の作業前状態を保存する。

3.4 DML実行手順

UPDATE、DELETEは次の順序で実行する。

対象件数を確認する。
対象主キーを確認する。
対象行退避を実施する。
トランザクションを開始する。
DMLを実行する。
変更件数を確認する。
変更後の値を確認する。
アプリケーション上の影響を確認できる場合は確認する。
件数または値が想定と違う場合はROLLBACKする。
件数と値が想定と一致する場合のみCOMMITする。
COMMIT後に確認SQLを再実行する。
アプリケーションログを確認する。

分割更新が必要な条件。

単一UPDATEまたはDELETEがHTTPリクエストのタイムアウト値を超える。
単一トランザクションでロック待ちが発生する。
単一トランザクションでレプリケーション遅延が許容値を超える。
単一トランザクションでUNDO、WAL、binlog、redo logの使用量が許容値を超える。

分割更新時の条件。

主キー範囲または作成日時範囲で対象を固定する。
各バッチの対象件数を記録する。
各バッチのCOMMIT後に確認SQLを実行する。
同じバッチを再実行しても二重更新にならないWHERE条件にする。
途中停止後の再開条件を記録する。

3.5 DDL実行手順

採用DBの公式マニュアルで該当DDLのロックを確認する。
採用DBの公式マニュアルで該当DDLの暗黙COMMITを確認する。
採用DBの公式マニュアルで該当DDLのトランザクション可否を確認する。
対象テーブルの行数とサイズを確認する。
対象テーブルのロック状態を確認する。
長時間トランザクションがある場合、DDL開始前に中止する。
DBMSにロック待ちタイムアウトまたはステートメントタイムアウトがある場合、作業セッションだけに設定する。
DDLを1文ずつ実行する。
各DDL後にスキーマ確認SQLを実行する。
各DDL後にアプリケーションエラーログを確認する。
ロック待ちタイムアウト、容量不足、重複、NULL、制約違反が出た場合は次のDDLを実行しない。
DDL成功後、アプリケーション反映が必要な場合は予定順序で反映する。

3.6 本番のみの反映順序

作業ファイルを最終確認する。
接続先DBを確認する。
バックアップを取得する。
対象行退避を実施する。
作業前ログを保存する。
00_precheck.sql を実行する。
中止条件に該当しないことを確認する。
メンテナンスモードが必要な場合は開始する。
01_migrate.sql を実行する。
02_verify.sql を実行する。
アプリケーション反映が必要な場合は反映する。
キャッシュ、設定キャッシュ、スキーマキャッシュ、ORMキャッシュがある場合はクリアまたはプロセス再起動を実施する。
対象機能を確認する。
バッチ、ジョブ、API、管理画面を確認する。
メンテナンスモードを使った場合は解除する。
反映直後、5分後、15分後にログとDB状態を確認する。

3.7 障害発生時

COMMIT前

ROLLBACKする。
確認SQLを実行する。
アプリケーションログを確認する。
原因を作業ログに記録する。
再実行する場合はSQLを修正してローカル再現からやり直す。

COMMIT後、戻しSQLで戻せる場合

アプリケーションを旧バージョンへ戻す必要があるか確認する。
03_rollback.sql を実行する。
確認SQLを実行する。
対象機能を確認する。
ログ、DB負荷、ジョブ状態を確認する。

COMMIT後、戻しSQLで戻せない場合

アプリケーション差し戻しで影響を止められるか確認する。
前方修正SQLで整合性を回復できるか確認する。
対象行退避から復元できるか確認する。
フルバックアップまたはスナップショットからのリストアが必要か確認する。
リストアを行う場合は、反映後に発生した正当なデータ更新の扱いを記録する。
復旧後、同じSQLを再実行しない。

4. SQL作成チェック

4.1 UPDATE

WHERE句がある。
WHERE句に主キー、ユニークキー、インデックス列、状態列、日時列のいずれかを含めている。
対象件数を事前確認している。
更新前値を退避している。
更新後値を確認するSQLがある。
同じSQLを再実行した場合の結果を確認している。
更新件数が0件の場合の扱いを記録している。
更新件数が想定超過した場合の中止条件を記録している。

4.2 DELETE

WHERE句がある。
対象主キーを退避している。
削除対象行を復元可能な形式で退避している。
外部キー、論理削除、関連テーブルを確認している。
削除後の件数確認SQLがある。
削除ではなく無効化で足りるか確認している。
TRUNCATEを使わない。

4.3 INSERT

主キー、ユニークキーの衝突時の扱いを記録している。
再実行時に重複しない条件がある。
NOT NULL列、デフォルト値、外部キーを確認している。
作成件数を確認するSQLがある。
戻しSQLがある。

4.4 ALTER TABLE

該当DDLのロックレベルを確認している。
該当DDLの暗黙COMMITを確認している。
該当DDLのテーブル再構築有無を確認している。
対象テーブルの行数とサイズを確認している。
対象テーブルの読み書き頻度を確認している。
アプリケーションの旧バージョン互換性を確認している。
DDL失敗時の状態を確認している。
制約追加前に既存データ違反を確認している。

4.5 インデックス追加

対象クエリの実行計画を取得している。
追加前後で確認するクエリを決めている。
対象カラムのNULL件数と重複状況を確認している。
インデックス名の衝突が無い。
オンライン作成、CONCURRENT作成、LOCK指定の可否を採用DBの公式マニュアルで確認している。
インデックス作成失敗時の残骸確認手順がある。

4.6 制約追加

NOT NULL追加前にNULL件数が0件。
UNIQUE追加前に重複件数が0件。
外部キー追加前に孤立データ件数が0件。
CHECK追加前に違反件数が0件。
制約名を明示している。
アプリケーションで制約違反時のエラー処理を確認している。

5. アプリケーション確認

5.1 反映前

対象DB変更を参照するコードを確認する。
モデル、ORM、SQL、Repository、Query Builderを確認する。
バリデーションを確認する。
APIリクエストとレスポンスを確認する。
管理画面を確認する。
バッチ、ジョブ、キューを確認する。
CSV、インポート、エクスポートを確認する。
キャッシュキー、検索インデックス、集計テーブルを確認する。
Feature Flagがある場合、ON/OFF状態を確認する。

5.2 反映後

対象機能の作成、更新、削除、一覧、詳細、検索を確認する。
APIのステータスコードとレスポンスボディを確認する。
管理画面の表示と更新を確認する。
バッチ、ジョブ、キューの成功件数と失敗件数を確認する。
アプリケーションログのDBエラーを確認する。
例外監視の新規エラーを確認する。
DBスロークエリを確認する。
DBロック待ちを確認する。
DB CPU、メモリ、接続数を確認する。

6. 本番作業ログテンプレート

change_id:
作業開始時刻:
作業終了時刻:
作業端末:
本番DB識別子:
本番DBホスト:
本番DBユーザー:
アプリケーション反映前バージョン:
アプリケーション反映後バージョン:

バックアップID:
バックアップ取得時刻:
対象行退避ファイル:
対象行退避件数:

00_precheck.sql 実行時刻:
00_precheck.sql 結果:

01_migrate.sql 実行時刻:
01_migrate.sql 結果:
実行時間:
変更件数:

02_verify.sql 実行時刻:
02_verify.sql 結果:

アプリ確認:
バッチ確認:
API確認:
管理画面確認:
ログ確認:
DB負荷確認:

戻しSQL未使用理由:
03_rollback.sql 使用有無:
障害有無:
残作業:

kobitoDevelopment/.md