Automatic database migrations with pg_advisory_lock

When you are implementing a Continuous Delivery pipeline, one of the issues you have to deal with is database migrations. You want to be able to automatically run the migrations. But if you have multiple instances of your app running, you have to make sure only one instance is able to run that migration. If you’re on Postgres, an easy way to solve this is to use pg_advisory_lock.

You acquire a lock by providing an integer that can represent anything, run the migration logic, then release the lock:

SELECT pg_advisory_lock(42);

-- run migration logic

SELECT pg_advisory_unlock(42);

Any concurrent process that attempts to run the same logic will be blocked until the lock is released.

#pg_advisory_lock #locks #continuous delivery