A migration can finish successfully while older application instances fail or backfilled values carry the wrong meaning.
Database migration testing checks whether schema changes and data transformations preserve the behavior your application promises. Every scenario and example below is hypothetical, including the proposed assertions and failure drills. None represents a completed experiment, production incident, or measured performance result.
This guide targets PostgreSQL 18, a supported major version in the official versioning policy. Pin the exact server minor version and migration runner in your test configuration. Rehearse on disposable databases or isolated, sanitized clones with production access disabled. Never aim failure injection, reset commands, or destructive recovery drills at production.
Define your database migration testing strategy
Describe what must survive the change
Consider an orders service replacing a nullable delivery_note column with delivery_instructions. A successful migration must preserve each order's text and ownership while supporting application instances deployed at different times. Write those expectations into a reviewable test plan before generating migration scripts.
Record the starting schema revision, candidate revision, and supported application versions. Specify how null differs from an empty string, whether defaults apply to historical records, and which constraints become stricter. A column rename and a data backfill have different failure modes even when the final schema looks identical.
A data migration testing strategy should turn those rules into test cases with explicit starting data and expected outcomes. For this backfill, source data lives in the original column within the same database. Data integrity means preserving ownership and relationships; data quality checks establish whether the copied instructions remain usable under the application's rules. Define those rules before selecting test data.
Give each release an explicit stop condition. A mismatched transformed value should fail CI; a blocked rehearsal should stop the rollout plan until someone explains the lock. Assign recovery ownership and identify the last point at which an application rollback remains compatible with the stored data.
Build a repeatable database migration testing workflow
Exercise the same release artifact through three steps
Prepare realistic data
Create two isolated targets. One starts empty and receives the supported migration history. The other starts at the previously deployed revision with fixtures loaded before the candidate migration. Add upgrade targets for older revisions that customers or installations still use.
Build fixtures around awkward values. Include nulls, empty strings, Unicode instructions, and orders owned by different tenants. Seed deliberate constraint violations in a separate negative fixture. Record expected values by stable identifier so an assertion can identify the exact damaged order.
Match production extensions, collation, permissions, and relevant settings. Use a representative volume for operational rehearsals while keeping small fixtures for fast CI checks. PostgreSQL's SQL dump documentation describes consistent logical exports; restore an approved sanitized export into a separate target when that is your cloning method. Remove credentials and disable outbound integrations before starting the app.
Give each parallel CI worker its own database and retain the fixture seed in failure output. Assert the target's host and database identity before migration setup begins. An allowlist should reject any connection that points outside the disposable test environment, even when someone supplies a familiar database name.
Verify the change
Run the exact migration artifact intended for release with the deployment role. Assert the resulting schema, transformed records, and application behavior. Then run the runner again and verify that already applied migrations are recognized without executing their effects twice. Separately test resumable backfill jobs after interruption.
Keep migration history validation in this stage. Flyway validate compares available migrations with applied history, including checksums. That check detects changed migration artifacts; application queries and explicit data assertions must establish whether the resulting data is correct.
Rehearse recovery
Interrupt a backfill between committed batches, cause a lock timeout, and exercise a failed index build on separate disposable targets. Record the surviving database objects and migration history after each failure. Follow the recovery instructions from that state without manually hiding errors in the history table.
Restore into another isolated target and run the application's critical read and write checks there. Record actual elapsed recovery time when your team performs the drill. Until then, recovery time and acceptable data loss remain requirements to verify, not established capabilities.
Assert data integrity on fresh and upgrade paths
Check values that row counts cannot explain
An unchanged row count can conceal swapped tenant identifiers, truncated text, or an incorrect default. Compare expected and actual values by primary key. For the orders example, assert that order 101 retains its Unicode instructions, order 102 keeps null, and order 103 keeps an intentional empty string. Read records through the service as well as SQL to catch client mapping errors.
During the copy phase, a useful discrepancy query is SELECT id FROM orders WHERE delivery_instructions IS DISTINCT FROM delivery_note;. Require an empty result only after the defined synchronization process has completed. If the transformation deliberately changes values, compare against the specified mapping instead of demanding equality.
Check foreign key relationships, uniqueness within the intended tenant scope, and generated identifiers on subsequent inserts. Assert that excluded records remain unchanged. For converted amounts or timestamps, include boundary values and the agreed rounding or timezone rules. A checksum over an entire table can signal a change without explaining whether that change was correct.
Fresh installation tests catch missing prerequisites and migration ordering problems. Upgrade tests expose assumptions about existing records. Run both against the same release files, then compare intended schema properties and execute the same product operations. A schema generated directly from the latest model cannot establish that the historical upgrade path works.
Make post-migration testing a release requirement. Automated testing should repeat data validation after the backfill finishes and after application versions switch. Include acceptance testing through the product, such as editing an existing order and reopening it to confirm the new instructions persist. Record which assertions establish correctness at each stage so a later check cannot conceal an earlier mismatch.
Keep old and new applications compatible
Expand before removing the old representation
Add the replacement column while retaining the original. Establish a synchronization policy, backfill existing rows, switch reads after reconciliation, and remove the old column in a later release. Prisma's expand and contract guide explains staged replacement. Inspect guidance for your installed Prisma version because its migration commands and execution model vary by version.
For orders, define which column is authoritative during each stage. If an old instance writes only delivery_note, a new reader needs a tested fallback or synchronization mechanism until those writers disappear. Dual writes also need a failure policy that prevents one representation from silently diverging.
Run old and new application binaries against the expanded schema. Alternate writes between them and verify reads from both. Include a record created after the backfill cursor passed its identifier range. Interrupt synchronization and retry it, checking that the most recent legitimate user edit survives.
Before contract, prove that old web instances, workers, and scheduled jobs have stopped using the original column. Test the application rollback against the expanded schema. Removing a column can invalidate that rollback even if the new application passes every test. Use the contract testing guide to extend compatibility checks across independently released API consumers.
Rehearse locks and backfill work under contention
Bound waiting and inspect the blocking session
Hold a transaction open on the clone, then run the candidate DDL from another connection. Observe application requests during the wait. PostgreSQL's locking reference explains conflicts and notes that ACCESS EXCLUSIVE conflicts with ordinary reads. A short metadata operation can still wait behind an existing transaction.
For a transactional rehearsal, set SET LOCAL lock_timeout = '2s'; and SET LOCAL statement_timeout = '30s'; after BEGIN in the migration connection. These values are examples to adapt. The timeout reference distinguishes waiting for a lock from total statement duration. Verify that the runner uses that connection, exposes the failure, and rolls back the failed transaction.
Outside a transaction, use appropriately scoped session settings and reset them before returning a pooled connection. Capture the blocked statement and blocker identity with sanitized logs. Avoid retry loops that repeatedly queue DDL without a deadline or operator visibility.
Backfill in bounded, committed batches with a stable traversal rule and a predicate that identifies unfinished work. Test a concurrent edit immediately before a batch update to check the overwrite policy. Track batch duration, application latency, and WAL volume during the rehearsal; include replica lag if the deployment uses replicas. The k6 load testing guide can help exercise product requests while database work runs, but clone timings do not guarantee production timings.
For an identifier cursor, persist progress only with the batch whose writes it describes. Terminate the worker after commit but before its next acknowledgement, then restart it. Assert that repeated work preserves the intended values and that the final reconciliation catches records missed by the traversal. Choose batch size from rehearsal evidence rather than embedding a universal number in the runbook.
Validate constraints and concurrent indexes explicitly
Separate historical cleanup from enforcement
For a staged foreign key, add it with NOT VALID, repair historical violations, then run VALIDATE CONSTRAINT. PostgreSQL 18's ALTER TABLE reference documents that new inserts and updates are still checked while historical validation is deferred. Validation takes a SHARE UPDATE EXCLUSIVE lock, so rehearse it under contention too.
Seed an orphan before adding the constraint and assert that validation fails. Verify that a new orphan is rejected after the constraint is installed. Repair the historical row, validate again, and inspect the catalog state. NOT VALID is not a general switch for every constraint type; inspect the supported syntax for the exact operation.
Inspect index validity after a failed build
CREATE INDEX CONCURRENTLY allows writes during construction but cannot run inside a transaction block. A deadlock or uniqueness failure can leave an invalid index that adds write overhead. A failed unique build may even continue enforcing uniqueness. These caveats are documented in PostgreSQL's CREATE INDEX reference.
On a dedicated negative fixture, introduce duplicate values before attempting the candidate unique index. After failure, inspect the named index and test subsequent writes. Assert validity through pg_index.indisvalid and readiness through indisready, whose meanings appear in the pg_index catalog reference. Index name existence alone is insufficient release evidence.
Rehearse the documented cleanup and rebuild path on that clone. Keep the index operation separate from transactional schema changes and verify the runner's handling. Flyway transaction handling covers nontransactional migrations and script configuration. Never assume all PostgreSQL DDL shares one universal rollback boundary.
Prove post migration recovery before release
Distinguish application rollback from restoring data
An application rollback replaces binaries. A compensating migration changes the schema again. Neither automatically recreates information discarded by a committed transformation. Define which failures permit a forward fix and which require restoring preserved data. Keep new writes made after deployment in the recovery decision.
PostgreSQL continuous archiving and recovery requires a suitable base backup and a continuous archived WAL sequence for point in time recovery. A logical dump cannot substitute for that WAL replay foundation. Rehearse your actual backup method into an isolated cluster, verify the recovery target, and run data assertions before considering traffic cutover.
Include a marker written before the target time and another written afterward. Verify the expected marker boundary in the recovered cluster, then check representative business records. Plan how to reconcile legitimate writes beyond that boundary and how operators will prevent clients from continuing to write to the abandoned database.
Retain evidence reviewers can reproduce
Save migration hashes, server and runner versions, fixture revision, and sanitized assertion failures. Record which paths ran and which were skipped. Have CI fail when setup or an expected check never executes. Pin runner syntax to the installed version; the current Prisma migration application documentation describes its own artifact checks and application workflow.
After automated checks pass, create a BetaRoast feedback round with safe accounts and a task such as editing delivery instructions after an upgrade. Ask people whether saved values and recovery messages make sense. BetaRoast provides human product feedback; it does not execute database migrations or certify recoverability.
Frequently asked questions
What should database migration testing cover first?
Test the upgrade from the deployed revision with representative existing records. Assert transformed values and critical application writes, then add a fresh installation path and the failure drills required by the specific change.
Are matching row counts enough to verify a backfill?
No. Counts cannot detect incorrect values or relationships. Compare expected records by identifier, verify tenant ownership, and exercise the application's interpretation of migrated values.
Can every PostgreSQL schema change be rolled back in a transaction?
No. CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Check each statement and the migration runner's transaction settings, then inspect what remains after failure.
Should a failed migration trigger an automatic restore?
Use the rehearsed recovery decision for that failure state. A restore can discard later writes and requires verified backups. A compatible application rollback or forward correction may be appropriate, but each path needs explicit data checks.
Test fresh databases and upgrades separately, verify transformed values, and exercise overlapping application versions. Rehearse locks and partial failures on isolated clones, then prove the recovery path with application checks. Human feedback can assess the resulting product experience after the automated database checks pass.