Recently we had interesting problem. There are some maintenance tools, that do stuff that touch database. One can be calling repack, another can be specific dump, and yet another can be applying migrations from application.

The problem is that they can step over each other toes, and cause issues.

So we needed to add some way to prevent them from running at the same time…

Naturally since the problem is when they do something to database, the natural choice was having some kind of lock in database.

But how? Write in some table that it's locked, and check? It simple, but if the tool that would get such lock would die, the lock would stay in place. Ideally we'd like to have lock that we can obtain, and then it will disappear once the program that ran it finishes.

There is a thing in PostgreSQL that matches this specific criteria: advisory lock.

This is lock that you can obtain on a number (or pair of numbers). These numbers don't represent anything for PostgreSQL – they are just for you and your apps.

You can tell Pg: lock number 1 for me, and then, until you will free this lock, number 1 will be locked, and no other process will be able to get lock on it.

Amazing. Solves the problem. We picked random number, and apps were modified to get this lock when starting process.

But then we realized that there is a shell script that does stuff to database, that should get this lock too.

The thing is – shell scripts access database only via psql, and once psql will finish – it doesn't stay there, but rather closes connection. And lock is gone.

So, we'd need a way to run:

SELECT pg_advisory_lock(12345);

But then keep the connection open. Simple. I can put psql to background. But then – how will I know that I got the lock?

I might get it immediately, or might not get it at all.

Also – we have a program that might kill idle session. So it shouldn't be idle for too long.

To sum it, we needed to find a way to:

• obtain lock
• inform about it parent script, somehow
• keep connection open, doing something, anything, every so often
• when parent script will die, the psql and connection should also end

So, to do it all, we finally did:

What does it do?

• Line 1 makes temp file, and stores it's path in locker_file variable.
• printf in line 2 prints this:

  \o :x
  SELECT 'LOCKED', pg_advisory_lock(:l);
  \o /dev/NULL
  SELECT :'m' \watch 60

  Which will do:

  • Redirect output to file which path is in variable x
  • print (select) word ‘LOCKED' and obtain lock for number in variable l. Output will happen only when lock will be obtained.
  • redirect all output to /dev/null
  • run a loop (\watch) that, every 60 seconds will select given message (variable m, so we will see it in query field in pg_stat_activity
• psql in line 3 will run the sql script, in background, giving appropriate values to variables x, m, and l.
• line 4: store pid of just started psql in variable
• line 5: make sure that however this script will end, psql will be killed, and file with output from psql removed
• lines 6-10: loop that checks if word “LOCKED" appeared in psql output, and if yes – end loop. Otherwise wait 5 seconds, and retry.

Side note: in our example, there was more logic in the final loop, as we were checking for some side-stuff, as maintenance tools can only run within specific hours, but that's not important for now.

Nothing magical, but does the trick. Hope you'll find it useful 🙂