制定数据库引擎迁移与回滚方案
结合源库与目标库差异规划数据迁移,输出分阶段计划、停机切换步骤、校验标准和回滚机制。
完整提示词
<context>
Engine migrations rarely fail on the bulk copy. They fail on semantics that differ quietly: case-insensitive comparisons that become case-sensitive, zero dates and unsigned integers with no equivalent, sequences not reset after the load, different default isolation levels, query plans that change for the worst queries, and a cutover with no tested way back. A credible plan finds those differences before the copy and makes the cutover boring.
</context>
<task>
Plan a migration from {{source}} to {{target}}.
{{#data_size}}Data size and write rate: {{data_size}}
{{/data_size}}{{#downtime_budget}}Downtime budget: {{downtime_budget}}
{{/downtime_budget}}
1. If the data size or the downtime budget is not stated above, or you do not have the schema, ask for them under "Inputs needed" and write the rest of the plan with each dependent choice labelled as an assumption. Ask also for the features in use (stored procedures, triggers, full-text search, JSON, spatial), the application stack and ORM, and the top queries by load.
2. Audit incompatibilities for this pair of engines: data types (booleans, unsigned integers, date and time zones, zero dates, enums, text and binary sizes), character sets and collations including case sensitivity, auto-increment versus identity or sequences, NULL versus empty-string handling, SQL dialect (upsert, limit, group-by strictness, quoting, functions), procedures and triggers, full-text search, JSON operators, default transaction isolation and locking behaviour, and implicit casts.
3. Choose the copy approach from size and downtime: an offline dump and load when the window allows; otherwise a bulk load followed by change data capture to stay in sync until cutover. Name candidate tools and why. Avoid application dual-writes unless you explain how consistency is guaranteed.
4. Phase the work: schema conversion, a test load, application changes behind a switch, performance testing of the top queries on the target, a rehearsal of the full cutover, then production.
5. Write the cutover runbook: stop or freeze writes, drain replication lag to zero, verify, reset sequences, switch connections, smoke test, decision point. Give each step an owner role and duration, and compare the total to the downtime budget.
6. Verification: row counts per table, checksums per chunk on normalised values, sampled row comparison, and application-level comparison of read results.
7. Rollback: how to return to the source after writes have landed on the target (reverse replication or a replay plan), the triggers for rolling back, and the deadline after which you roll forward instead.
</task>
<constraints>
- Be specific to {{source}} and {{target}}. Do not list incompatibilities that do not apply to this pair.
- Do not invent table names or sizes. Use the information given and label assumptions.
- A cutover without a rehearsed rollback is a risk to state plainly, not a footnote.
{{> output/uncertainty}}
</constraints>
<output_format>
## Summary
Approach, expected downtime, and the top three risks.
## Inputs needed
Bullets, or "None".
## Incompatibilities
A table: area, behaviour in the source, behaviour in the target, action.
## Approach
The copy method and tools, and why.
## Phases
A table: phase, work, exit criteria.
## Cutover runbook
Numbered steps with owner role and duration, plus the go or no-go checks.
## Verification
The checks and their pass criteria.
## Rollback
The mechanism, triggers and deadline.
## Risks
Bullets with mitigations.
</output_format>可替换内容
- {{source}}
- {{target}}
- {{#data_size}}
- {{data_size}}
- {{/data_size}}
- {{#downtime_budget}}
- {{downtime_budget}}
- {{/downtime_budget}}
- {{> output/uncertainty}}
分类与标签
编程开发、数据库迁移、兼容性审查、变更数据捕获、停机切换、回滚演练