Embed Migrations in a Go Binary and Run Them on Start

Shipping migrations inside the binary removes a whole class of deployment mistakes: the code and the schema it expects travel together, there is no folder to forget to copy into the image and no version skew between the two. Go makes it nearly free with go:embed. The part that needs care is not the embedding. It is what happens when several copies of the service start at once and each tries to migrate, which is what a rolling deploy does. This article does both on Go 1.25.6 with goose v3.26.0 and PostgreSQL 18.6, and runs the start-up race to see what actually happens.

It sits in the databases cluster. The tools themselves are introduced in migrations with goose and migrations with golang-migrate, and keeping the statements inside them safe on a busy table is the subject of zero downtime migrations.

Embedding the files

The migrations live in a folder beside the code:

-- +goose Up
create table notes604 (id bigserial primary key, body text not null);
select pg_sleep(2.5);

-- +goose Down
drop table notes604;

-- +goose Up
alter table notes604 add column pinned boolean not null default false;

-- +goose Down
alter table notes604 drop column pinned;

The first file ends with select pg_sleep(2.5). That is not a recommendation; it stands in for a migration that takes real time, such as an index build on a big table, because the interesting behaviour only appears when a migration is slower than the gap between instances starting.

Embedding is two lines in the Go file:

//go:embed migrations/*.sql
var migrations embed.FS

The directive must sit directly above the variable, and the pattern is relative to the source file's directory. The variable holds the files read-only inside the executable, which is what makes the binary self-contained. Two details trip people up. A pattern that starts with a dot or an underscore is skipped by a directory embed, but an explicit *.sql glob matches the names you expect. And embed.FS keeps the folder prefix, so the file is migrations/00001_notes.sql, not 00001_notes.sql. goose looks for files at the root of the file system it is given, so the program strips the prefix with fs.Sub(migrations, "migrations"). Forgetting that step is the most common reason for the error "no migrations found".

The provider

goose has an older global API (goose.SetBaseFS, goose.Up) and a newer instance based one, goose.NewProvider. The provider keeps its configuration in a value instead of package state, which matters when a program, or a test binary, runs migrations for more than one database. It also adds the one option that matters here, a locker.

package main

import (
	"context"
	"database/sql"
	"embed"
	"fmt"
	"io/fs"
	"log"
	"strings"
	"sync"

	_ "github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"
	"github.com/pressly/goose/v3/lock"
)

//go:embed migrations/*.sql
var migrations embed.FS

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

// migrateOnStart is what every instance runs before it serves traffic.
func migrateOnStart(ctx context.Context, locked bool) (int, error) {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		return 0, err
	}
	defer db.Close()
	sub, err := fs.Sub(migrations, "migrations")
	if err != nil {
		return 0, err
	}
	opts := []goose.ProviderOption{goose.WithTableName("goose_604")}
	if locked {
		locker, err := lock.NewPostgresSessionLocker()
		if err != nil {
			return 0, err
		}
		opts = append(opts, goose.WithSessionLocker(locker))
	}
	p, err := goose.NewProvider(goose.DialectPostgres, db, sub, opts...)
	if err != nil {
		return 0, err
	}
	results, err := p.Up(ctx)
	return len(results), err
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	const rounds = 6
	for _, locked := range []bool{false, true} {
		failedRounds, doubleApplied := 0, 0
		var sample string
		for r := 0; r < rounds; r++ {
			for _, q := range []string{"drop table if exists notes604", "drop table if exists goose_604"} {
				if _, err := db.ExecContext(ctx, q); err != nil {
					log.Fatal(err)
				}
			}
			var wg sync.WaitGroup
			var mu sync.Mutex
			total, failed := 0, 0
			for i := 0; i < 4; i++ {
				wg.Add(1)
				go func() {
					defer wg.Done()
					n, err := migrateOnStart(ctx, locked)
					mu.Lock()
					defer mu.Unlock()
					total += n
					if err != nil {
						failed++
						if sample == "" {
							sample = strings.SplitN(err.Error(), "\n", 2)[0]
						}
					}
				}()
			}
			wg.Wait()
			if failed > 0 {
				failedRounds++
			}
			if total > 2 {
				doubleApplied++
			}
		}
		fmt.Printf("session lock=%-5v rounds=%d  rounds with a failing instance=%d  rounds that applied a file twice=%d\n", locked, rounds, failedRounds, doubleApplied)
		if sample != "" {
			fmt.Println("  first error:", sample)
		}
	}
}

session lock=false rounds=6  rounds with a failing instance=6  rounds that applied a file twice=0
  first error: partial migration error (type:sql,version:1): ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" (SQLSTATE 23505)
session lock=true  rounds=6  rounds with a failing instance=0  rounds that applied a file twice=0

migrateOnStart is what every instance would call before it opens its listener. It opens its own *sql.DB, builds a provider over the embedded files with a private version table name (goose_604, so this lab does not touch another's), and calls Up. Up returns one result per migration it applied, which is how the program counts them.

The race

The program simulates a rolling deploy. Four goroutines call migrateOnStart at the same time against a freshly emptied database, and it repeats that six times with and without a session locker. A round counts as failed when any instance returned an error, and a file counts as applied twice when the sum of results exceeds two.

The unlocked result is plain: all six rounds had a failing instance. The first error was duplicate key value violates unique constraint "pg_type_typname_nsp_index". That message does not say "migration conflict". It is PostgreSQL refusing to create the same table twice, because two instances saw version 0, both started create table notes604, and the loser hit the catalog's unique index when the winner committed. No file was applied twice, since PostgreSQL stopped the second one, but one or more instances failed to start. In a real deploy that is a crash loop on the new pods until the winner finishes and the rest retry and find nothing to do.

With lock.NewPostgresSessionLocker() passed as goose.WithSessionLocker, no instance failed in any of the six rounds. The locker takes a PostgreSQL advisory lock on a dedicated connection before it reads the version table and releases it when Up returns. The first instance applies both files; the others wait on the lock, then read the table, find everything applied and return with zero results. One instance does the work and the rest are idle for exactly as long as it takes.

What the first version of the lab showed

The first version of this lab slept for 0.3 seconds instead of 2.5. Over 20 unlocked rounds not one instance failed. That looked like proof that the locker was unnecessary, and it is the wrong conclusion. Reading goose's source explains it: before running migrations the provider creates its version table, and when several instances do that at once the losers retry after a one second pause. The winner finishes a 0.3 second migration inside that second, so the retrying instances arrive late and find nothing to do. The race is hidden by an accident of timing, and it comes back as soon as one migration takes longer than a second, which is the case that matters. Any test of a start-up race has to use a migration slower than the retry window, or it passes for the wrong reason.

Rules for running migrations at start

1. Use a lock whenever more than one instance can start at once. A rolling deploy, a Kubernetes replica set and an autoscaler all qualify. The advisory lock costs nothing when there is no contention.
2. Know what the lock is. It is a session lock on one connection. It holds when that connection reaches PostgreSQL directly or through a pooler in session mode. Behind PgBouncer in transaction mode the lock can be taken on one server connection and the work done on another, and the guarantee is gone. Run migrations over a direct connection.
3. Do not make readiness wait forever. An instance blocked on the lock is not ready. Give the start-up routine a deadline from a context, so a stuck migration fails the deploy and does not leave pods waiting; the pattern is in query timeouts.
4. Fail the start-up if migration fails. A service that logs the error and serves traffic against an old schema produces errors that look unrelated.
5. Keep a way to run migrations without starting the service, such as a migrate subcommand of the same binary. A pre-deploy job that runs it once, before the rollout, removes the race altogether and keeps slow migrations out of the readiness path.

When start-up migration is the wrong choice

Running migrations at boot is convenient for small services and local development, and it is a poor fit when a migration can take minutes, when many different services share one database, or when the application's database user should not have permission to change the schema. In those cases a separate job with its own, more privileged credentials is better, and the application runs with only the rights it needs. The embedded files still help: the same binary can carry the schema and run it under a different command. The goose provider and its locker options are documented on pkg.go.dev, and the go:embed rules are in the embed package reference.