Rehearse a database migration

Use a snapshot to rehearse a schema migration against a realistic PostgreSQL data directory, inspect the result, and return to the exact pre-migration disk state. The disposable rehearsal does not require a down migration.

Keep PGDATA in the sandbox root filesystem for this example. Snapshots capture the sandbox’s writable layer, but they do not capture external named or bind-mounted volumes.

Rehearse a migration

Create an initialized baseline

Set a temporary password in the host shell:

export POSTGRES_PASSWORD="$(openssl rand -hex 24)"

$bytes = New-Object byte[] 24
$rng = [Security.Cryptography.RandomNumberGenerator]::Create()
$rng.GetBytes($bytes)
$rng.Dispose()
$env:POSTGRES_PASSWORD = -join ($bytes | ForEach-Object { $_.ToString('x2') })

Start the baseline database:

msb run -d --name migration-base --replace \
  --cpus 1 --memory 1G --root-disk 4G \
  -e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" \
  -e POSTGRES_DB=examples \
  -e DOCKER_PG_LLVM_DEPS= \
  postgres:17-alpine

msb run -d --name migration-base --replace `
  --cpus 1 --memory 1G --root-disk 4G `
  -e "POSTGRES_PASSWORD=$env:POSTGRES_PASSWORD" `
  -e POSTGRES_DB=examples `
  -e DOCKER_PG_LLVM_DEPS= `
  postgres:17-alpine

Wait for initialization, then stop the database cleanly:

msb exec migration-base -- sh -lc '
  until pg_isready -h 127.0.0.1 -d examples -U postgres; do sleep 1; done
'

Stop it cleanly before taking the snapshot:

msb stop migration-base

Snapshot the baseline

msb snapshot create postgres-before-migration \
  --from migration-base \
  --integrity

msb snapshot create postgres-before-migration `
  --from migration-base `
  --integrity

Verify the captured snapshot:

msb snapshot verify postgres-before-migration

Apply the migration

Boot a fresh database from the snapshot:

msb run -d --name migration-test --replace \
  --from-snapshot postgres-before-migration \
  --memory 1G \
  -p 127.0.0.1:55432:5432 \
  -e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" \
  -e POSTGRES_DB=examples \
  -e DOCKER_PG_LLVM_DEPS=

msb run -d --name migration-test --replace `
  --from-snapshot postgres-before-migration `
  --memory 1G `
  -p 127.0.0.1:55432:5432 `
  -e "POSTGRES_PASSWORD=$env:POSTGRES_PASSWORD" `
  -e POSTGRES_DB=examples `
  -e DOCKER_PG_LLVM_DEPS=

Wait for the restored database to become ready:

msb exec migration-test -- sh -lc '
  until pg_isready -h 127.0.0.1 -d examples -U postgres; do sleep 1; done
'

Apply a sample destructive change and verify it:

msb exec -e PGPASSWORD="$POSTGRES_PASSWORD" migration-test -- \
  psql -h 127.0.0.1 -U postgres -d examples -v ON_ERROR_STOP=1 \
  -c 'create table dangerous_migration(id integer);' \
  -c 'insert into dangerous_migration values (42);' \
  -c 'select * from dangerous_migration;'

msb exec -e "PGPASSWORD=$env:POSTGRES_PASSWORD" migration-test -- `
  psql -h 127.0.0.1 -U postgres -d examples -v ON_ERROR_STOP=1 `
  -c 'create table dangerous_migration(id integer);' `
  -c 'insert into dangerous_migration values (42);' `
  -c 'select * from dangerous_migration;'

Replace those statements with your real migration command and validation suite.

Roll back by replacing the sandbox

Stop the mutated database and boot another clean copy of the baseline under the same name:

msb stop migration-test

Replace it with a fresh sandbox from the snapshot:

msb run -d --name migration-test --replace \
  --from-snapshot postgres-before-migration \
  --memory 1G \
  -p 127.0.0.1:55432:5432 \
  -e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" \
  -e POSTGRES_DB=examples \
  -e DOCKER_PG_LLVM_DEPS=

msb run -d --name migration-test --replace `
  --from-snapshot postgres-before-migration `
  --memory 1G `
  -p 127.0.0.1:55432:5432 `
  -e "POSTGRES_PASSWORD=$env:POSTGRES_PASSWORD" `
  -e POSTGRES_DB=examples `
  -e DOCKER_PG_LLVM_DEPS=

Wait for the clean database to become ready:

msb exec migration-test -- sh -lc '
  until pg_isready -h 127.0.0.1 -d examples -U postgres; do sleep 1; done
'

Confirm the sample table is absent:

msb exec -e PGPASSWORD="$POSTGRES_PASSWORD" migration-test -- \
  psql -h 127.0.0.1 -U postgres -d examples -Atc \
  "select coalesce(to_regclass('public.dangerous_migration')::text, 'rolled-back');"

msb exec -e "PGPASSWORD=$env:POSTGRES_PASSWORD" migration-test -- `
  psql -h 127.0.0.1 -U postgres -d examples -Atc `
  "select coalesce(to_regclass('public.dangerous_migration')::text, 'rolled-back');"

The result should be rolled-back.

Clean up

Remove the database sandboxes:

msb rm -f migration-base migration-test

Remove the snapshot:

msb snapshot rm postgres-before-migration

Clear the password from the host shell:

unset POSTGRES_PASSWORD

Remove-Item Env:POSTGRES_PASSWORD

Agents

CI/CD

Docker

Sandbox Workflows

Automation & Tooling

Data & Observability

Rehearse a database migration

Rehearse a migration

​Rehearse a migration

Rehearse a migration