Why this is hard to get right
A Real Migration Gone Wrong — and How Better Prompting Fixed It
Marcus is a senior engineer at a mid-sized SaaS company. His team has been running PostgreSQL 12 for three years. Security patches are falling behind, and the CTO wants an upgrade to PostgreSQL 15 before the next compliance audit. Marcus owns the migration.
He starts where most engineers do: he asks an AI assistant for help. His first prompt is simple — "Help me migrate our PostgreSQL database with minimal downtime." The response he gets back is technically accurate but completely useless in practice. It describes pg_dump and pg_restore in generic terms. It mentions "test in staging first." It says nothing about his 1 TB dataset, his Sequelize ORM, his read replica, or the 20-minute read-only window his SLA allows on Sunday nights.
Marcus spends the next hour asking follow-up questions. He gets fragments. He pieces together a rough plan. Then he realizes the plan doesn't account for replica lag during cutover. He asks again. The AI contradicts its earlier recommendation about pglogical. Now Marcus has four different outputs and no clear path forward.
This is the core problem with vague prompts for high-stakes technical work. The AI doesn't know what it doesn't know about your environment. It defaults to the median case — a single-node setup, moderate data volume, no ORM constraints, no downtime SLA. If your environment diverges from that median at all, the output diverges from useful.
Marcus steps back and rewrites his prompt from scratch. This time he specifies the source and target versions, the data volume, the topology, the ORM, the time window, and the exact deliverables he needs — pre-flight checklist, cutover commands, verification queries, and a rollback plan with explicit go/no-go triggers.
The difference is immediate. The AI produces a staged plan with pglogical for zero-downtime logical replication, exact psql commands with timing estimates, a Sequelize compatibility check for breaking changes between v12 and v15, and a rollback decision matrix tied to replica lag thresholds.
Marcus shares it with his DBA and their DevOps lead. Both make small adjustments, but the structure is sound. The Sunday migration runs in 18 minutes — two minutes inside the window. Zero data loss. No rollback needed.
The migration didn't succeed because Marcus is a better engineer than anyone who's failed at this before. It succeeded because a well-structured prompt gave the AI the full context it needed to reason about his specific environment — not a hypothetical one.
Common mistakes to avoid
Omitting Database Topology and Replica Count
When you don't specify whether you're running a primary-only setup, a primary with replicas, or a multi-region cluster, the AI generates a single-node migration plan by default. Replica consistency steps, failover handling, and replication lag management get skipped entirely — which can cause data divergence or extended downtime during cutover.
Not Specifying the ORM or Application Layer
Database migrations don't happen in isolation. If you're using Sequelize, ActiveRecord, or SQLAlchemy, those frameworks have their own version-specific behaviors. Failing to name your ORM means the AI won't flag breaking changes — like PostgreSQL 15's revised permission model or changed default settings — that directly affect your application queries.
Leaving Downtime Tolerance Undefined
Phrases like 'minimal downtime' mean nothing to an AI. Does that mean 30 seconds or 4 hours? Without a specific window (e.g., 20-minute read-only period on Sunday nights), the AI can't recommend the right strategy. It may suggest pg_upgrade when your SLA demands logical replication, or vice versa — wasting both planning time and execution risk.
Requesting a Plan Without Specifying Deliverable Format
Asking for a 'migration plan' without defining the output format produces narrative prose instead of executable steps. You need numbered sections, code blocks with exact commands, and risk tables — not paragraphs. Specifying the format in your prompt forces the AI to structure output you can actually hand to a DBA or paste into a runbook.
Skipping Rollback Triggers and Success Criteria
Most prompts focus entirely on the happy path. Without explicit rollback conditions — like maximum acceptable replica lag or error rate thresholds — the AI produces a one-directional plan with no go/no-go logic. A migration without clear abort criteria is a liability, not a plan. Tell the AI what failure looks like so it can build in the checkpoints.
Underspecifying Data Volume and Table Complexity
A 10 GB migration and a 1 TB migration are entirely different problems. Skipping data volume causes the AI to recommend strategies that don't scale — like a full pg_dump/restore on a live 1 TB database during peak hours. Include total data size, number of tables, and any high-row-count tables to get timing-aware, realistic command sequences.
The transformation
Help me migrate our database with minimal downtime.
You are a senior DevOps DBA. Create a production-safe migration from PostgreSQL 12 to 15. 1) Context: 1 TB data, primary + 1 replica, app uses Sequelize ORM, read-only window 20 minutes. 2) Deliverables: a) step-by-step migration plan b) pre-flight checklist c) cutover commands d) verification queries e) rollback plan. 3) Constraints: zero data loss, keep replicas consistent, avoid long locks, use pglogical or pg_upgrade if justified. 4) Include: exact psql/pg_dump/pg_restore commands, config changes, expected timings. 5) Output format: numbered sections, code blocks for commands, risks with mitigations.
Why this works
Role Anchoring Sets Expertise Level
The After Prompt opens with "You are a senior DevOps DBA." This single instruction shifts the AI's output register from beginner-safe explanations to production-grade recommendations. It suppresses boilerplate caveats and pushes the model toward tool-specific advice — like weighing pglogical against pg_upgrade — that a senior practitioner would actually evaluate.
Environment Specificity Drives Realistic Commands
The After Prompt names the exact source and target versions (PostgreSQL 12 to 15), data volume (1 TB), topology (primary + 1 replica), ORM (Sequelize), and available window (20 minutes). These aren't cosmetic details — they're the constraints the AI needs to select the right strategy and generate accurate timings instead of generic placeholder commands.
Explicit Deliverable List Prevents Incomplete Output
By listing five numbered deliverables — step-by-step plan, pre-flight checklist, cutover commands, verification queries, and rollback plan — the After Prompt prevents the AI from stopping after the first coherent-sounding section. Each item becomes a required output component, and the AI structures its response to satisfy each one before finishing.
Constraints Define the Solution Space
The After Prompt specifies zero data loss, replica consistency, avoidance of long locks, and acceptable tools (pglogical or pg_upgrade if justified). These constraints force the AI to reason about trade-offs rather than defaulting to the simplest answer. The phrase 'if justified' signals that the AI should explain its tool choice, not just use it.
Format Instruction Produces a Usable Artifact
The final line — "numbered sections, code blocks for commands, risks with mitigations" — converts the AI's output from explanatory prose into a runbook-ready document. Without this, the same content would arrive as paragraphs that require manual reformatting before any DBA or DevOps engineer can act on them.
The framework behind the prompt
The Theory Behind Database Migration Planning
Database migrations sit at the intersection of distributed systems theory, operational risk management, and change management. Understanding the underlying principles helps you build better prompts — because better prompts reflect better mental models of the problem.
RTO and RPO as Design Constraints
Every migration strategy is ultimately constrained by two metrics: Recovery Time Objective (RTO — how long can the system be unavailable?) and Recovery Point Objective (RPO — how much data loss is acceptable?). A 20-minute maintenance window with zero data loss tolerance demands a completely different architecture than a 4-hour window with 5-minute RPO. Your prompt must encode these values explicitly, because the AI uses them to eliminate incompatible strategies before recommending one.
The CAP Theorem in Practice
During a migration cutover, you're temporarily operating in a partition-tolerant state where your source and target databases may diverge. The CAP theorem tells us that during this partition, you must choose between consistency and availability. Logical replication strategies (like pglogical) favor availability — the application stays up, but write conflicts must be managed. Offline migration strategies (like pg_upgrade) favor consistency — the database is unavailable but the state is guaranteed to be identical post-migration.
The Expand-Contract Pattern
Coined by Martin Fowler, the expand-contract pattern (sometimes called parallel change) is the gold standard for zero-downtime schema migrations in continuous deployment environments. The pattern has three phases: expand (add the new schema alongside the old), migrate (backfill data and shift application writes), and contract (remove the deprecated schema once all consumers have switched). This pattern decouples database deployments from application deployments — a critical property for teams deploying multiple times per day.
Flyway and Liquibase: Migration Versioning Theory
Database schema versioning tools apply the same principles as source code version control. Flyway uses a linear versioning model — each migration has a version number, and migrations apply in order. Liquibase uses a more flexible changeset model with checksums and contexts. Both tools enforce idempotency through applied migration logs, ensuring a migration never runs twice. Understanding which model fits your team's deployment cadence is a prerequisite for generating a useful migration pipeline prompt.
Prompt variations
You are a senior cloud database engineer specializing in AWS RDS migrations.
Migrate a production MySQL 8.0 database to Amazon Aurora MySQL 3.0.
Environment:
- Database size: 80 GB
- Single primary, no read replicas
- Application uses Laravel with Eloquent ORM
- Acceptable downtime: 2-hour maintenance window on Saturday night
Deliver:
- Pre-migration compatibility checklist for Aurora MySQL 3.0
- Step-by-step AWS DMS setup and task configuration
- Cutover sequence with exact AWS CLI and console steps
- Post-migration validation queries for row counts and referential integrity
- Rollback procedure if validation fails
Constraints: Keep costs under $50 for the DMS task. Flag any Laravel/Eloquent behaviors that change on Aurora.
Format: Numbered sections, code blocks for all commands, a table of risks with likelihood and mitigation.
You are a senior MongoDB architect.
I need to migrate a production MongoDB 4.4 cluster to 6.0 while simultaneously refactoring a core collection schema.
Environment:
- 3-node replica set, 200 GB total data
- Primary collection: orders (150 million documents), migrating from flat structure to nested line_items array
- Application: Node.js with Mongoose 6.x
- Zero downtime allowed — application must stay live throughout
Deliver:
- MongoDB version upgrade path (4.4 to 5.0 to 6.0 sequence) with exact commands
- Dual-write migration strategy for the orders schema transformation
- Mongoose model compatibility changes required between versions
- Backfill script in JavaScript for transforming existing documents in batches of 5,000
- Rollback plan including how to revert the schema transformation
Format: Numbered sections, all code in JavaScript or mongosh, timing estimates per phase, risks with mitigations.
You are a senior database architect specializing in Microsoft Azure migrations.
Migrate a production SQL Server 2016 instance to Azure SQL Database (General Purpose tier).
Environment:
- 500 GB database, 120 tables, 40 stored procedures, 15 SQL Agent jobs
- Application: .NET 6 with Entity Framework Core
- Compliance: SOC 2 Type II — all data must remain encrypted in transit and at rest
- Maximum allowed downtime: 4-hour window on the last Sunday of the month
Deliver:
- Azure Database Migration Service (DMS) configuration and assessment report interpretation
- Compatibility fixes for features unsupported in Azure SQL (SQL Agent replacement strategy, linked server alternatives)
- Entity Framework Core connection string and retry policy updates
- Cutover runbook with Azure CLI commands and timing estimates
- Post-migration smoke test suite — 10 critical queries to validate business logic
- Rollback plan back to SQL Server 2016
Constraints: No stored procedure rewrites unless strictly necessary. Flag any SOC 2 risks introduced by the migration.
Format: Numbered runbook sections, T-SQL and Azure CLI code blocks, risk register table with severity ratings.
You are a senior platform engineer with expertise in database DevOps.
I need to build a repeatable, automated schema migration pipeline for a PostgreSQL 14 database using Flyway, integrated into a GitHub Actions CI/CD workflow.
Environment:
- PostgreSQL 14 on AWS RDS
- 30 existing tables, currently managed with ad-hoc SQL scripts
- Team of 8 engineers committing schema changes weekly
- Target: zero manual migration steps after initial setup
Deliver:
- Flyway project structure and naming conventions for versioned migrations
- GitHub Actions workflow YAML for running Flyway migrate on merge to main
- Strategy for baselining existing schema into Flyway version history
- Undo migration pattern for reversible changes
- Conflict resolution process when two engineers submit competing migrations
Format: YAML and SQL code blocks, numbered setup steps, a decision table for when to use versioned vs repeatable migrations.
When to use this prompt
Product Managers
Define migration acceptance criteria and verification steps for a release that changes database versions or schemas.
Engineers
Generate precise commands and scripts for a weekend cutover, including pre-flight checks and post-cutover tests.
DevOps Teams
Plan blue-green or logical replication migrations that meet strict RTO/RPO targets with rollback paths.
Customer Success
Prepare a clear communication plan on expected read-only windows and validation milestones for enterprise clients.
Researchers
Compare migration strategies (in-place vs logical) with risks, timings, and tooling for a technical decision memo.
Pro tips
- 1
Quantify downtime and data volume to guide tool and strategy recommendations.
- 2
Specify topology (replicas, read replicas, failover) to ensure consistency steps are included.
- 3
Define verification queries and business KPIs so success checks match real user impact.
- 4
State rollback triggers and thresholds so the plan includes clear go/no-go decision points.
Zero-downtime migrations are architecturally complex. The AI needs more than a list of commands — it needs to reason about state transitions across multiple systems simultaneously.
Expand-contract pattern: Ask the AI to structure the migration in three phases: expand (add new columns/tables without removing old ones), migrate (backfill data, switch application writes), and contract (remove deprecated schema). This pattern avoids deployment lock-step between database and application.
Prompt addition: Include this in your constraint section: 'Use the expand-contract pattern. The application deploys independently of the database. Both old and new schema must be valid simultaneously for at least 48 hours after cutover.'
Replication slot management: For logical replication strategies, prompt the AI to include replication slot monitoring commands. Unconsumed slots block VACUUM and can cause disk exhaustion. Ask for:
- Commands to monitor slot lag
- A slot cleanup procedure after cutover confirms success
- Alert thresholds for lag that should trigger intervention
Blue-green with DNS cutover: If you're running on cloud infrastructure, ask the AI to include a DNS TTL reduction strategy (lowering TTL 24 hours before cutover to 60 seconds) alongside the database cutover. This technique reduces the blast radius of a failed cutover to seconds rather than minutes of cached DNS resolution.
The core prompt structure applies across database engines, but each engine introduces specific concerns you should surface in your prompt.
PostgreSQL: Emphasize pg_upgrade compatibility checks, extension dependencies (PostGIS, pgvector, pg_cron), and changes to the permission model in PostgreSQL 15 (public schema privileges changed). Ask the AI to run a pg_upgrade --check-only simulation step.
MySQL / MariaDB: Prompt for utf8mb4 charset validation, InnoDB tablespace encryption compatibility, and GROUP BY behavior changes. MySQL 8.0's strict mode affects applications that relied on implicit grouping.
MongoDB: Specify replica set protocol version and request a step-by-step version hop plan (you can't skip major versions). Include index compatibility between versions and the impact of the new query engine in 6.0+.
SQL Server: Always include compatibility level settings — SQL Server allows databases to run at a lower compatibility level on a newer instance, which is a useful intermediate step. Prompt for: current compatibility level, target compatibility level, and a staged compatibility level upgrade after the instance migration stabilizes.
Oracle to PostgreSQL (Heterogeneous): These migrations require schema conversion tools like AWS SCT or ora2pg. Your prompt should specify which tool is available and ask for conversion confidence scores by object type — stored procedures typically score lowest and need manual review.
AI-generated migration scripts are a strong starting point, but they require human validation before touching production. Use this checklist:
Before staging:
- Verify all tool versions match your production environment exactly (pg_dump version must match target cluster version)
- Confirm extension dependencies are installed on the target instance
- Validate that all user roles and permissions are recreated, not just data and schema
- Check that sequences are migrated with correct current values, not reset to defaults
In staging:
- Run the full migration script on a production-sized dataset copy, not a sample
- Measure actual timing against AI estimates — adjust go/no-go window if timings diverge more than 20%
- Execute every verification query from the AI output and compare row counts to source
- Simulate a rollback from the midpoint of migration, not just from start or end
Before production cutover:
- Get a second engineer to review the rollback plan independently
- Confirm backup completion timestamp is within the last 2 hours
- Lower DNS TTL 24 hours in advance if using DNS-based cutover
- Communicate the maintenance window to customer success and support teams with a written status page update
After cutover:
- Run AI-generated smoke tests within the first 5 minutes
- Monitor slow query log for queries that regressed under the new version's planner
- Keep rollback path available for at least 72 hours post-cutover
When not to use this prompt
When Not to Use This Prompt Pattern
This prompt pattern is powerful, but it's not the right tool for every database change.
Don't use it for trivial schema changes. Adding a nullable column to a small, low-traffic table doesn't require a staged migration plan with pglogical and rollback matrices. Over-engineering simple changes wastes time and creates noise in your runbooks. A single ALTER TABLE with a brief pre/post check is sufficient.
Don't rely on it as your sole planning artifact for first-time engine switches. Migrating from PostgreSQL to MySQL, or from Oracle to PostgreSQL, involves data type incompatibilities, function rewrites, and application-layer changes that exceed what even a well-prompted AI can fully enumerate. Use the AI output as a checklist starting point, but pair it with a dedicated schema conversion tool (AWS SCT, ora2pg) and a professional review.
Don't skip staging validation. AI-generated scripts should never run directly in production, regardless of how detailed the prompt was. The AI doesn't have access to your actual schema, your index definitions, or your current data distribution. Always run the full plan against a production-sized staging environment first.
Alternatives to consider:
- For simple DDL changes: a concise ALTER TABLE with a pre-change backup prompt
- For compliance-heavy environments: pair this with a dedicated security review prompt
- For heterogeneous migrations: use a schema assessment tool output as input context for your AI prompt
Troubleshooting
The AI generates a migration plan that ignores replicas and only covers the primary instance
Add explicit topology language to your prompt: 'Environment: 1 primary + 2 read replicas using streaming replication. All steps must address replica consistency, including promoting replicas if needed and re-syncing them post-cutover.' Without this, the AI treats every migration as a single-node problem. Include your replication method (streaming vs logical) to get the right consistency commands.
The generated rollback plan just says 'restore from backup' with no specific commands
Replace vague rollback language in your prompt with measurable triggers and time constraints: 'Rollback must complete in under 15 minutes. Include exact pg_restore or pg_basebackup commands. Define rollback triggers: replica lag above 60 seconds, any primary key violation detected by verification queries, or application error rate above 0.5% within 10 minutes of cutover.' Specific thresholds produce specific procedures.
The AI recommends tools not available in the production environment
Add an explicit tools inventory to your prompt: 'Available tools: pg_dump 15, psql 15, pg_basebackup, pglogical 2.4. NOT available: pg_upgrade, barman, repmgr.' Listing unavailable tools is as important as listing available ones. This prevents the AI from designing a plan around binaries that don't exist on your servers, especially in locked-down enterprise environments.
Timing estimates in the plan are wildly inaccurate for the actual data volume
Include hardware and I/O context in your prompt: 'Storage: AWS EBS gp3, 3,000 IOPS baseline. Network: 10 Gbps between source and target. Largest table: orders at 400 GB with 1.2 billion rows.' Ask the AI to express timings as ranges tied to these specs and flag which steps are I/O-bound vs CPU-bound. Request that it calculate pg_dump throughput at your disk speed, not a generic average.
The migration plan doesn't include application-layer changes, only database commands
Add an application integration section to your deliverables list: 'Include application changes required: connection string updates, ORM configuration changes for PostgreSQL 15 compatibility, and any Sequelize model adjustments for renamed or removed functions.' Database migrations don't end at the database layer — prompting for the full stack forces the AI to cover the connection between the two.
How to measure success
How to Evaluate AI Output Quality for Migration Prompts
A strong AI response to this prompt type should meet these criteria before you hand it to a DBA or run a single command.
Completeness checks:
- Does the output include all five deliverables you requested (plan, checklist, commands, verification queries, rollback)?
- Are all commands specific to your named versions and tools — not generic placeholders?
- Does the rollback section include explicit triggers, not just a generic "restore from backup" instruction?
Technical accuracy signals:
- Commands reference your actual versions — pg_dump flags differ between PostgreSQL 12 and 15
- Replica steps are present — any plan for a replicated environment should address lag monitoring and re-sync
- ORM compatibility is mentioned — Sequelize, ActiveRecord, or your named ORM should appear in the application-layer section
- Timing estimates are tied to your stated data volume — not generic "approximately 2 hours"
Format quality:
- Code blocks are properly formatted and copy-pasteable
- Risks appear in a structured format (table or numbered list with mitigations)
- Section headers match the deliverables you requested
Red flags to reject and re-prompt:
- Generic advice that could apply to any database
- Rollback steps that are shorter than cutover steps (rollback is always at least as complex)
- No mention of your specific ORM or application stack
Now try it on something of your own
Reading about the framework is one thing. Watching it sharpen your own prompt is another — takes 90 seconds, no signup.
Turn your database specs into a production-safe migration plan with exact commands and rollback steps.
Try one of these
Frequently asked questions
Yes, but you need to specify which type you're doing in your prompt. Schema-only migrations (DDL changes, table restructuring) require different tooling — like Flyway or Liquibase — compared to full data migrations that involve pg_dump, pg_restore, or DMS. Mixing them without clarification causes the AI to blend strategies incompatibly. State explicitly: 'schema migration only' or 'full data + schema migration.'
Replace the self-managed topology details with your cloud service tier and region. Key additions for managed databases:
- Instance class and storage type (e.g., db.r6g.2xlarge, gp3)
- Parameter group constraints
- Automated backup windows and their conflict with migration timing
- IAM role permissions required for DMS or export tasks
Managed databases restrict certain commands (like direct file access), so naming your service forces the AI to avoid recommending inapplicable approaches.
Add a tool constraint to your prompt: 'Available tools: pg_dump, pg_restore, pg_upgrade. pglogical is not installed and cannot be added.' This prevents the AI from designing a plan around unavailable tooling. You can also ask it to compare strategies and explain trade-offs, then select one explicitly. Always list what you have, not just what you want.
Include data volume, disk I/O specs, and network bandwidth in your prompt. For example: '1 TB database on NVMe SSD, 10 Gbps network between source and target.' Without hardware context, timing estimates are theoretical averages. You can also ask the AI to express timings as ranges (best/worst case) tied to specific variables like index rebuild size or replication lag.
Add this constraint explicitly: 'The application runs transactions up to 45 minutes in duration during batch jobs. The migration must not terminate these or cause lock conflicts.' Long-running transactions are a major migration hazard — lock conflicts, replication slot bloat, and vacuum issues all stem from them. The AI needs this information to recommend deferred cutover windows or advisory lock strategies.
Yes, but you must describe your multi-tenancy model. Row-level tenancy (tenant_id column) requires different migration logic than schema-per-tenant or database-per-tenant models. For schema-per-tenant setups, include the number of tenant schemas and whether they're identical in structure — the AI will then generate a loop-based script rather than a one-time migration command.
Your prompt likely didn't specify rollback triggers or time constraints. Add language like: 'Rollback must complete within 10 minutes. Include specific error conditions that trigger rollback — replica lag above 30 seconds, error rate above 1%, or any primary key violation.' Concrete thresholds force the AI to build a decision matrix, not a generic 'restore from backup' sentence.
It's a strong starting point, but you should add a compliance layer to your prompt. Include:
- Applicable regulation (HIPAA, GDPR, PCI DSS)
- Encryption requirements for data in transit and at rest
- Audit log requirements during migration
- Whether the migration tool itself needs to be BAA-compliant
The AI will then flag steps that expose unencrypted data or break audit chain continuity.