---
title: Rehearse a database migration
sidebarTitle: Migrations
description: Snapshot PostgreSQL, apply a risky migration, and restore the baseline
icon: "rotate-left"
---

<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

<Steps>
<Step title="Create an initialized baseline">

Set a temporary password in the host shell:

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

```powershell Windows
$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 Windows
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:

```sh
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:

```sh
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 Windows
msb snapshot create postgres-before-migration `
  --from migration-base `
  --integrity
```
</CodeGroup>

Verify the captured snapshot:

```sh
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 Windows
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:

```sh
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 Windows
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:

```sh
msb stop migration-test
```

Replace it with a fresh sandbox 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 Windows
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:

```sh
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 Windows
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:

```sh
msb rm -f migration-base migration-test
```

Remove the snapshot:

```sh
msb snapshot rm postgres-before-migration
```

Clear the password from the host shell:

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

```powershell Windows
Remove-Item Env:POSTGRES_PASSWORD
```
</CodeGroup>

</Step>
</Steps>