How do I fix a stuck Postgres process?

Issue

A process in Postgres has locked and is preventing my app from working.

Resolution

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 kill individual processes with the following command:

$ heroku pg:kill 2048 -a example_app_name

where 2048 is the PID (process id) that you wish to terminate.

For cases where killing individual processes isn't working you can also use:

$ heroku pg:killall -a example_app_name

After running this you should restart your app to remove any previous bad connections:

$ heroku restart -a example_app_name