I use Heroku PostgreSQL and their PGBackups for my production environment. I have found, for reasons that I'm not quite clear on, that at least some schema changes do not run correctly during the time that the backups are happening.
According to these Heroku docs, it seems like pgbackups is using pg_dump internally, though there may be additional things going on as well. The docs indicate that the output is essentially the same as this pg_dump command.
pg_dump -F c --no-acl --no-owner --quote-all-identifiers $DATABASE_URL
Attempting to run migrations during the backup window has repeatedly caused me some pain, as the migrations lock up and hang. To mitigate this, I'd like to detect when the backups are running, and avoid running the migrations by having a pre-check in the script that runs the migrations.
I can do this by checking with Heroku's CLI, and that will work for my current specifics, but I'm wondering if something less specific to Heroku might be reasonable, by watching for a running pg_dump instead.
Is there a good way to detect if pg_dump is currently running, and may block schema changes?
Best Answer
Unless somebody has overridden the
application_nameparameter forpg_dump, you could do this:If that gives you 0, no
pg_dumpis running.