Issue
A process in Postgres is locked and is preventing my app from working.
Resolution
You can check the processes running on your Heroku Postgres database by running heroku pg:ps.
For professional tier databases, you can view warnings in your log output to see details of stuck processes. For example:
1966-01-01T12:00:00Z app[postgres.1234]: [ONYX] process 1234 still waiting for AccessShareLock on relation 16668 of database 16385 after 1000.291 ms
Detail: Process holding the lock: 2048. Wait queue: 2580, 2579, 2577, 2578, 2575, 25115, 2589, 2593, 2576, 2592, 2574, 27121, 2691, 2692, 2693, 2695, 2694, 2696, 2698, 2699, 2722, 2757, 2756, 2759, 2758, 2760, 2764, 2761, 2762, 2772, 2776, 2777, 2779, 2781, 2783, 2802, 2782, 2812, 2801, 2819, 2820, 2821, 2818, 2828, 2829, 2830, 2831, 2832, 2873, 2870, 2868, 2875, 2876, 2869, 2874, 2877, 2871, 3037, 3038, 3039, 3040, 3041, 3043, 3044, 3045, 3046, 3093, 3092, 3094, 3096, 3095, 3104, 3091, 3110, 3111, 3178, 3339, 3338, 3340, 3341, 3344, 3343, 3356, 3355, 3357, 3359, 3459, 2314, 31846, 3606, 3605, 3607, 3608, 3620, 3631, 3632, 3637, 3644.
You can cancel individual processes with the following command:
$ heroku pg:kill 2048 -a example-app
where 2048 is the PID (process id) that you wish to stop.
Alternatively, you terminate individual processes by force with the --force flag like so:
$ heroku pg:kill 2048 --force -a example-app
Please note that --force will terminate the entire process, not just the current query.
For cases where killing individual processes isn't working, you can also terminate all the connections to the database by running:
$ heroku pg:killall -a example-app
After this, you should restart your app to remove any previous bad connections:
$ heroku restart -a example-app