--- title: Rehearse a database migration sidebarTitle: Migrations description: Snapshot PostgreSQL, apply a risky migration, and restore the baseline icon: "rotate-left" --- Local-only 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 Set a temporary password in the host shell: ```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') }) ``` Start the baseline database. The shell keeps the VM available after PostgreSQL stops, until the explicit `msb stop` below: ```sh macOS & Linux 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' ``` ```powershell Windows 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: ```sh 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: ```sh 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 ``` ```sh macOS & Linux msb snapshot create postgres-before-migration \ --from-sandbox migration-base \ --integrity ``` ```powershell Windows msb snapshot create postgres-before-migration ` --from-sandbox migration-base ` --integrity ``` Verify the captured snapshot: ```sh msb snapshot verify migration-base:postgres-before-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`](https://www.postgresql.org/docs/17/app-pg-ctl.html) 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. ```sh macOS & Linux msb 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 ``` ```powershell Windows msb 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: ```sh 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: ```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;' ``` Replace those statements with your real migration command and validation suite. Stop the mutated database and boot another clean copy of the baseline under the same name: ```sh 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: ```sh macOS & Linux msb 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 ``` ```powershell Windows msb 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: ```sh 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: ```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');" ``` The result should be `rolled-back`. Stop the restored database cleanly, then remove the stopped sandboxes: ```sh 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: ```sh msb snapshot rm migration-base:postgres-before-migration ``` Clear the password from the host shell: ```sh macOS & Linux unset POSTGRES_PASSWORD ``` ```powershell Windows Remove-Item Env:POSTGRES_PASSWORD ```