Rehearse a database migration - Microsandbox

<Tooltip tip="This workflow creates and restores local disk snapshots, which are not available on microsandbox cloud."><span className="msb-badge-local">Local-only <Icon icon="circle-info" size={11} /></span></Tooltip>

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.

<Warning> 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. </Warning>

Rehearse a migration

Set a temporary password in the host shell:

<CodeGroup> ```sh macOS & Linux export POSTGRES_PASSWORD="$(openssl rand -hex 24)" ```

powershell

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

</CodeGroup>

Start the baseline database:

<CodeGroup> ```sh macOS & Linux 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 ```

powershell

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

</CodeGroup>

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

</Step> <Step title="Snapshot the baseline"> <CodeGroup> ```sh macOS & Linux msb snapshot create postgres-before-migration \ --from migration-base \ --integrity ```

powershell

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

</CodeGroup>

Verify the captured snapshot:

msb snapshot verify postgres-before-migration

</Step> <Step title="Apply the migration">

Boot a fresh database from the snapshot:

<CodeGroup> ```sh macOS & Linux 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= ```

powershell

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=

</CodeGroup>

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:

<CodeGroup> ```sh macOS & Linux 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;' ```

powershell

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;'

</CodeGroup>

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

</Step> <Step title="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:

powershell

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=

</CodeGroup>

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:

<CodeGroup> ```sh macOS & Linux 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');" ```

powershell

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');"

</CodeGroup>

The result should be rolled-back.

</Step> <Step title="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:

<CodeGroup> ```sh macOS & Linux unset POSTGRES_PASSWORD ```

powershell

Remove-Item Env:POSTGRES_PASSWORD

</CodeGroup> </Step> </Steps>