---
title: "Deploy migrations safely"
description: "Classify each change by risk, plan in CI, and let a production deploy apply exactly the plan that was approved."
canonical_url: "https://docs.galleon.thec1oud.uk/docs/guides/migrations"
markdown_url: "https://docs.galleon.thec1oud.uk/docs/guides/migrations.md"
last_updated: "2018-10-20"
x_farming_labs_generated_preamble: true
---

# Deploy migrations safely
URL: /docs/guides/migrations
LLM index: /llms.txt
Description: Classify each change by risk, plan in CI, and let a production deploy apply exactly the plan that was approved.

# Deploy migrations safely

In production, Galleon never applies a migration nobody looked at. This guide sets up the flow that gets a schema change from a pull request to the production database:

1. Each migration file declares its risk.
2. CI prints the plan for the production database, with the fingerprint that identifies it.
3. Someone approves that fingerprint.
4. The deploy applies the plan only if it is still the one approved, and only up to the risk the policy allows.

It assumes a service that migrates its own schema with `pg/sqlmigrate`, as in [Migrations](/docs/learn/migrations) in Learn. For how the parts fit together, see [Migrations](/docs/concepts/migrations) in Concepts.

## Declare each file's risk

Start each SQL file with its risk: `safe`, `caution`, `destructive` or `manual`. A file without one is of unknown risk, which production refuses.

```sql file=examples/guides/migrations/migrations/0001_create_notes.sql title="migrations/0001_create_notes.sql"
-- galleon:risk safe
CREATE TABLE notes (
    id    bigserial PRIMARY KEY,
    title text NOT NULL,
    body  text NOT NULL DEFAULT ''
);
```

Building an index on a large table blocks writes unless it is built concurrently, which can't run in a transaction. Mark such a file `no-transaction`, and keep it to one statement:

```sql file=examples/guides/migrations/migrations/0002_index_notes_title.sql title="migrations/0002_index_notes_title.sql"
-- galleon:risk caution
-- galleon:no-transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS notes_title ON notes (title);
```

If the statement fails part way, or runs past `apply_timeout` (ten minutes by default) and is cancelled, the file is pending again. A concurrent build that fails leaves an invalid index behind, and on the next attempt `IF NOT EXISTS` skips it: the file is recorded as applied, and nothing was built. A restarted node can make that attempt on its own when the failed file was the first step of the approved plan, since the pending plan, and so its fingerprint, still matches. Without a pinned fingerprint, any restart in apply mode retries it.

So after a failed apply, switch the deploy back to verify mode (unset `NOTES_MIGRATE_MODE`) or stop restarts, and check the index:

```sql
SELECT indisvalid FROM pg_index WHERE indexrelid = 'notes_title'::regclass;
```

If it is invalid, drop it with `DROP INDEX CONCURRENTLY IF EXISTS notes_title`, then deploy again, approving the new plan if its fingerprint changed. If an attempt has already recorded the file, ship two new `no-transaction` files: one that drops the index, and one that builds it again. For a large table, raise `apply_timeout` for the deploy.

The migrator is registered as in Learn:

```go file=examples/guides/migrations/notes.go#module title="notes.go"
// Notes registers the "notes" migrator over Files.
type Notes struct{ Files fs.FS }

func (Notes) Name() string { return "notes" }

func (n Notes) Register(r *galleon.Registry) error {
	galleon.Migrator(r, "notes", func(m *pg.Manager) (migrate.Migrator, error) {
		return sqlmigrate.New("notes", n.Files, m.Pool(""))
	})
	return nil
}
```

## Know what production does by default

The `Production` profile verifies at boot and never applies. Each migrator's readiness gate, `migrate.<name>`, stays failing while changes are pending, so a node with an old schema takes no traffic. When it applies, it allows steps up to `caution`.

Deploys change that with the `migrate` config section, or its environment variables, `<APP>_MIGRATE_<KEY>`:

| Key | What it does |
|---|---|
| `mode` | `apply` applies at boot, under the cluster lock |
| `approve` | pins each migrator's approved fingerprint, as `name=fingerprint` pairs |
| `max_risk` | raises or lowers the highest risk a deploy may apply |
| `allow_unknown` | lets steps of unknown risk run |
| `tolerate_ahead` | keeps a node ready when the database is newer than its build |
| `apply_timeout` | bounds applying at boot, waiting for the lock included; ten minutes by default |

[Configuration](/docs/reference/config#migrate) in Reference lists every key.

## Plan in CI

A plan says what applying would do to one database: the pending steps, their risks, and a fingerprint of exactly those steps. Make it against the production database, or a copy at the same version, right before you deploy.

There is no `migrate plan` command yet. Until there is, `App.Migrations().Plan` is the Go API. This function prints the plan and fails if the policy would refuse it:

```go file=examples/guides/migrations/notes.go#plan title="notes.go"
// PrintPlan writes what applying would do to the database app is connected
// to: each migrator's pending steps, their risks, and the fingerprint to
// approve. It fails when a plan couldn't be made, the database's status
// couldn't be read, or the policy would refuse the plan, so CI stops there.
func PrintPlan(ctx context.Context, app *galleon.App, w io.Writer) error {
	reports, err := app.Migrations().Plan(ctx, false)
	if err != nil {
		return err
	}
	if err := migrate.WritePlans(w, reports); err != nil {
		return err
	}
	for _, r := range reports {
		if r.Err != nil || r.StatusErr != nil || r.Refused != nil {
			return fmt.Errorf("migrator %s can't be deployed as planned", r.Name)
		}
	}
	return nil
}
```

Call it from a command your CI runs, on an app built with the production config, `pg.Module()` and the modules that own migrators. Run it in verify mode, never with the deploy's `NOTES_MIGRATE_MODE=apply`: `Start` would apply everything before the plan is made. It prints:

```txt
notes: 2 step(s), max risk caution, fingerprint aeba9062426a4f9383ab70aa950f722a0d21932b7d985e74de8bb7b938536772
  0001_create_notes [safe] 0001_create_notes.sql
  0002_index_notes_title [caution] 0002_index_notes_title.sql (outside a transaction)
```

Review the steps, then record the approval where your deploy reads it, such as a protected variable in your pipeline.

## Deploy the approved plan

Deploy with migrations applied at boot, and the fingerprint pinned:

```sh
NOTES_MIGRATE_MODE=apply
NOTES_MIGRATE_APPROVE=notes=aeba9062426a4f9383ab70aa950f722a0d21932b7d985e74de8bb7b938536772
```

With today's only coordination backend, `coord/local`, the app runs as one process (`galleon.Deploy(galleon.SingleNode)`), and that process applies. With a cluster backend, which isn't built yet, every node can boot this way: one applies under the coordination lock, and the others wait for it, then find nothing pending. A migrator with nothing pending has no plan to check.

If the database changed since the plan was made, or the build has a file the plan didn't, the plan differs from the approved one, and boot fails before anything is applied:

```txt
applying migrations at boot (mode apply): migrate.fingerprint_mismatch: migrator "notes": the plan's fingerprint is aeba9062…, but 0123abcd was approved; review the new plan and approve it
```

This test runs the whole flow against a real database:

```go file=examples/guides/migrations/notes_test.go#approve title="notes_test.go"
func TestADeployAppliesOnlyTheApprovedPlan(t *testing.T) {
	dsn := testkit.PackageDSN(t) // an empty database, standing in for production
	t.Cleanup(func() { dropAll(t, dsn) })
	ctx := context.Background()

	// CI: boot as production does, in verify mode, and print the plan.
	ci := testkit.New(t,
		galleon.Name("notes"),
		galleon.Environment(galleon.Production),
		galleon.Modules(pg.Module(), Notes{Migrations}),
		galleon.ConfigJSON([]byte(`{"pg": {"primary": {"dsn": "`+dsn+`"}}}`)),
	)
	var out bytes.Buffer
	if err := PrintPlan(ctx, ci.App, &out); err != nil {
		t.Fatal(err)
	}
	t.Log(out.String())
	approve := approval(t, out.String())

	// The deploy: a plan other than the approved one is refused.
	t.Setenv("NOTES_MIGRATE_APPROVE", "notes=0123abcd")
	err := deploy(dsn, Migrations)
	if err == nil || !strings.Contains(err.Error(), "migrate.fingerprint_mismatch") {
		t.Fatalf("an unapproved plan: %v", err)
	}

	// The approved plan applies.
	t.Setenv("NOTES_MIGRATE_APPROVE", approve)
	if err := deploy(dsn, Migrations); err != nil {
		t.Fatalf("the approved plan: %v", err)
	}
	reports, err := ci.Migrations().Status(ctx)
	if err != nil {
		t.Fatal(err)
	}
	if len(reports) != 1 || reports[0].Status.State != migrate.UpToDate {
		t.Fatalf("after the deploy: %+v", reports)
	}
}

// deploy boots the service as production does, with migrations applied at
// boot, and stops it again.
func deploy(dsn string, files fs.FS) error {
	app, err := galleon.New(
		galleon.Name("notes"),
		galleon.Environment(galleon.Production),
		galleon.Coordination(local.New()),
		galleon.Deploy(galleon.SingleNode),
		galleon.DrainGrace(0), // nothing to drain: the test stops it at once
		galleon.Logger(slog.New(slog.NewTextHandler(io.Discard, nil))),
		galleon.Modules(pg.Module(), Notes{files}),
		galleon.ConfigJSON([]byte(`{"pg": {"primary": {"dsn": "`+dsn+`"}}, "migrate": {"mode": "apply"}}`)),
	)
	if err != nil {
		return err
	}
	if err := app.Start(context.Background()); err != nil {
		return err
	}
	return app.Shutdown(context.Background())
}
```

## Ship a destructive change

Dropping a column is `destructive`, above what production allows. CI's plan already says so, and fails:

```txt
notes: 1 step(s), max risk destructive, fingerprint d1cf2bc2…
  0003_drop_body [destructive] 0003_drop_body.sql
  refused: migrate.risk_refused: migrator "notes": the policy refuses 0003_drop_body (destructive), since the maximum risk is caution, and unknown risk needs approval (allow_unknown); nothing was applied
```

```go file=examples/guides/migrations/notes_test.go#risk
func TestProductionRefusesADestructiveStep(t *testing.T) {
	dsn := testkit.PackageDSN(t)
	t.Cleanup(func() { dropAll(t, dsn) })
	if err := deploy(dsn, Migrations); err != nil { // no approval pinned: any plan up to caution
		t.Fatal(err)
	}

	drop := fstest.MapFS{"0003_drop_body.sql": {Data: []byte("-- galleon:risk destructive\nALTER TABLE notes DROP COLUMN body;\n")}}
	files := merge(Migrations, drop)

	// CI's plan already says the deploy would be refused.
	ci := testkit.New(t,
		galleon.Name("notes"),
		galleon.Environment(galleon.Production),
		galleon.Modules(pg.Module(), Notes{files}),
		galleon.ConfigJSON([]byte(`{"pg": {"primary": {"dsn": "`+dsn+`"}}}`)),
	)
	var out bytes.Buffer
	if err := PrintPlan(context.Background(), ci.App, &out); err == nil || !strings.Contains(out.String(), "refused") {
		t.Fatalf("PrintPlan = %v:\n%s", err, out.String())
	}

	// And so is the deploy, if it gets that far.
	err := deploy(dsn, files)
	if err == nil || !strings.Contains(err.Error(), "3_drop_body (destructive)") {
		t.Fatalf("a destructive step in production: %v", err)
	}
}
```

Drop a column only once no running build reads it: stop using it in one release, and drop it in a later one. Then raise the limit for that one change, both for the plan in CI and for the deploy beside its approval, and set it back afterwards:

```sh
NOTES_MIGRATE_MAX_RISK=destructive
```

## Keep old nodes ready during a rolling deploy

While a rolling deploy migrates, the nodes of the previous release are still serving. Their gates read the status again every five seconds, and find the database ahead of their build. Without `tolerate_ahead` they leave rotation until they are replaced.

Each release's migrations must therefore work with the previous release's code. Set `tolerate_ahead` on every release, so that it is already set on the old nodes when the next one migrates.

## Sitemap

See the full [sitemap](/sitemap.md) for all pages.
Docs-scoped sitemap: [/docs/sitemap.md](/docs/sitemap.md).
Well-known sitemap: [/.well-known/sitemap.md](/.well-known/sitemap.md).