Prevent migrations from running in parallel

[silverpill]

I have an application that runs migrations automatically at startup. However, in certain cases multiple instances of an application might be started at the same time, and that leads to conflicts (e.g. an instance fails because some table already exists).

What is the recommended way of dealing with this?

Driver: tokio-postgres.

[jxs]

Hi, does running tokio single threaded help?

[silverpill]

I don't expect it to help, because in my case there are different processes executing migrations, not different tasks within same process.

The problem might be solved by an exclusive lock on the migration table, for example.

[skeet70]

We have the same question, and looking at the migration source I'm not seeing anything that would indicate it has any protection for clustered deployments.

Flyway has this in their FAQ:

Can multiple nodes migrate in parallel?
Yes! Flyway uses the locking technology of your database to coordinate multiple nodes. This ensures that even if multiple instances of your application attempt to migrate the database at the same time, it still works. Cluster configurations are fully supported.

which is the sort of thing I was hoping to see implemented here. Maybe I missed it?

[skeet70]

The use case for us is there is a service, and we'd like to embed refinery in the service (or its container) so when it's started the DB is brought up to the version it needs. Those services are always clustered in the real world, so if someone starts a deployment there could be multiple instances trying to start up at once, and it looks like they'd potentially run migrations in parallel.

[jxs]

Hi! ah I see now, thanks for elaborating. I am assuming set_grouped isn't enough to address that right? If not feel free to submit a PR that locks the table on the grouped migration option.
cc @silverpill

[skeet70]

Right, set_grouped doesn't help because you have N instances of the service starting at the same all trying to to the same set_grouped migration in their own transaction. Clobbering can ensue, even with all the migrations in one transaction per service.

If we end up using refinery we may use postgres' advisory locks feature to work around it not being built into refinery. If we do use refinery and don't like that workaround for other reasons, I may get bandwidth to PR a change adding an option to lock to prevent parallel migrations.

[silverpill]

Potential solution: implement AsyncMigrate for tokio_postgres::Transaction.

I haven't analyzed it thoroughly, but I think it might work. It should allow us to wrap the whole migration procedure in a transaction, and add necessary locks.