Postgres Emergency Commands

heroku-pg-extra.md

With Heroku's pg-extra

On Heroku, the pg-extras CLI Plugin is available.

$ heroku pg:long-running-queries

List currently running queries that have been running for longer than 5 minutes. Heroku dynos have a 30-seconds disconnect so queries still running after 5 mins are usually problematic and can slow down the entire DB.

$ heroku pg:blocking

List queries that are currently holding locks that other queries are waiting to be released. Long-running locks can block an entire table.

$ heroku pg:locks

List queries that have taken out an exclusive lock on a relation.

> SELECT pg_cancel_backend(<pid of the process>)

Kill query.

> SELECT pg_terminate_backend(<pid of the process>)

Force-kill query.

pg-top.md

With pg-top

On any DB, use my pg-top CLI Plugin.

$ pgtop queries:long-running

List currently running queries that have been running for longer than 10 seconds.

$ pgtop queries:blocking

List queries that are currently holding locks that other queries are waiting to be released.

$ pgtop queries:idle

List idle queries.

$ pgtop queries:cancel PID

Kill query.

$ pgtop queries:terminate PID

Force-kill query.