Can you run goose.Run("up") in parallel from multiple instances of application?

[arvenil]

I have below at the start of http server:

	if err := goose.Run("up", db, dir); err != nil {
		return errors.WithStack(err)
	}

However I've noticed recently that if two instances of application run migrations at the same time, then one will fail trying to apply migration that was already applied by the other one. Looks like there is a race condition. I was under impression that this library solves this problem with some smart queries but looks like not?

[mfridman]

This is intended behaviour. goose is meant to be run as a singleton to apply migrations sequentially, generally this is best practice.

E.g., however your application starts up, you'd run a container, or a binary, etc. to apply your migrations and upon success continue to rollout your application.

I'd suggest decoupling your migrations from your main application.

[arvenil]

A workaround for me:

	for {
		_, err := db.ExecContext(ctx, "CREATE TABLE goose_migrations_in_progress (dummy boolean)")
		if err == nil {
			break
		}

		log.Errorf("can't acquire lock to do migration, might be another one in progress: %s", err)
		time.Sleep(time.Second)
	}

	defer func() {
		_, err := db.ExecContext(ctx, "DROP TABLE goose_migrations_in_progress")
		if err != nil {
			log.Error(err)
		}
	}()

	goose.SetLogger(log.Logger())

	if err := goose.Run("up", db, dir); err != nil {
		return errors.WithStack(err)
	}

Instead of creating table I've tried to use pg_advisory_lock but for some reason it was blocking migrations with CREATE INDEX concurrently.

[arvenil]

Also maybe this lib would be better choice for me: https://github.com/golang-migrate/migrate/blob/master/FAQ.md#what-happens-if-two-programs-try-and-update-the-database-at-the-same-time

[mfridman]

There is a directive you can add to the migration file telling goose to run migration outside a transaction, such as CREATE INDEX concurrently

-- +goose NO TRANSACTION

This is the relevant bit of the docs:

By default, all migrations are run within a transaction. Some statements like CREATE DATABASE, however, cannot be run within a transaction. You may optionally add -- +goose NO TRANSACTION to the top of your migration file in order to skip transactions within that specific migration file. Both Up and Down migrations within this file will be run without transactions.

[arvenil]

Yes, I had that directive. Still create index concurrently hanged. Apparently there is some clash with that statement and pg_advisory_xact_lock().