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. This keeps the database self-contained in the captured root disk. Do not rely on external named-directory or bind-mounted storage being copied into the snapshot.

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. The shell keeps the VM available after PostgreSQL stops, until the explicit msb stop below:

msb run -d --name migration-base --replace \
  --cpus 1 --memory 1G --root-disk 4G \
  --entrypoint /bin/sh \
  -e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" \
  -e POSTGRES_DB=examples \
  -e DOCKER_PG_LLVM_DEPS= \
  postgres:17-alpine -- \
  -ec 'docker-entrypoint.sh postgres & wait "$!"; exec sleep infinity'

msb run -d --name migration-base --replace `
  --cpus 1 --memory 1G --root-disk 4G `
  --entrypoint /bin/sh `
  -e "POSTGRES_PASSWORD=$env:POSTGRES_PASSWORD" `
  -e POSTGRES_DB=examples `
  -e DOCKER_PG_LLVM_DEPS= `
  postgres:17-alpine -- `
  -ec 'docker-entrypoint.sh postgres & wait "$!"; exec sleep infinity'

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
'

Shut PostgreSQL down with its own control command before stopping the VM and taking the snapshot. VM shutdown alone does not guarantee a clean database shutdown:

msb exec --no-tty --user postgres migration-base -- /usr/local/bin/pg_ctl -D /var/lib/postgresql/data -m fast -w stop
msb stop migration-base

Snapshot the baseline

msb snap create postgres-before-migration \
  --sandbox migration-base \
  --integrity

msb snap create postgres-before-migration `
  --sandbox migration-base `
  --integrity

Verify the captured snapshot:

msb snap verify migration-base:postgres-before-migration

Apply the migration

Restore into an unused sandbox name, then start PostgreSQL explicitly. Dedicated restore boots an idle detached sandbox instead of running the image entrypoint. The database, role password, and initialization settings already live in the captured data directory; the POSTGRES_* initialization variables do not need to be replayed.pg_ctl starts PostgreSQL in its own process group with output redirected to the log, so it survives the startup exec completing. The separate status check below reconnects after that exec has exited.

msb snap restore migration-base:postgres-before-migration --name migration-test \
  --memory 1G -p 127.0.0.1:55432:5432
msb exec --no-tty --user postgres migration-test -- \
  /usr/local/bin/pg_ctl -D /var/lib/postgresql/data -l /var/lib/postgresql/data/rehearsal.log -w start

msb snap restore migration-base:postgres-before-migration --name migration-test `
  --memory 1G -p 127.0.0.1:55432:5432
msb exec --no-tty --user postgres migration-test -- `
  /usr/local/bin/pg_ctl -D /var/lib/postgresql/data -l /var/lib/postgresql/data/rehearsal.log -w start

Wait for the restored database to become ready:

msb exec --no-tty --user postgres migration-test -- /usr/local/bin/pg_ctl -D /var/lib/postgresql/data status
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 exec --no-tty --user postgres migration-test -- /usr/local/bin/pg_ctl -D /var/lib/postgresql/data -m fast -w stop
msb stop migration-test
msb rm migration-test

After removing the stopped sandbox, restore a fresh copy under the same name and start PostgreSQL:

msb snap restore migration-base:postgres-before-migration --name migration-test \
  --memory 1G -p 127.0.0.1:55432:5432
msb exec --no-tty --user postgres migration-test -- \
  /usr/local/bin/pg_ctl -D /var/lib/postgresql/data -l /var/lib/postgresql/data/rehearsal.log -w start

msb snap restore migration-base:postgres-before-migration --name migration-test `
  --memory 1G -p 127.0.0.1:55432:5432
msb exec --no-tty --user postgres migration-test -- `
  /usr/local/bin/pg_ctl -D /var/lib/postgresql/data -l /var/lib/postgresql/data/rehearsal.log -w start

Wait for the clean database to become ready:

msb exec --no-tty --user postgres migration-test -- /usr/local/bin/pg_ctl -D /var/lib/postgresql/data status
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

Stop the restored database cleanly, then remove the stopped sandboxes:

msb exec --no-tty --user postgres migration-test -- /usr/local/bin/pg_ctl -D /var/lib/postgresql/data -m fast -w stop
msb stop migration-test
msb rm migration-base migration-test

Remove the snapshot:

msb snap rm migration-base:postgres-before-migration

Clear the password from the host shell:

unset POSTGRES_PASSWORD

Remove-Item Env:POSTGRES_PASSWORD

Overview

AI Agents

Dev Environments

CI/CD & Jobs

Browser & Web Automation

File Processing

Docker & System Tools

Data & Storage

Observability

Rehearse a database migration

Rehearse a migration

​Rehearse a migration

Rehearse a migration