Subject: Re: Never kill -9 postgres client processes on Linux... but why not?
To: pgsql-admin@lists.postgresql.org
References: 
 <CAOC+FBVsc3ehFduq_z=5H6k2G-KyNCKnBzkzY0Q53nVpEasnhw@mail.gmail.com>
 <57d6f3fa-3d9f-9dda-f2a2-131966b5f407@catalyst.net.nz>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <418d6fe1-a96a-9299-f173-09a804d0b7b3@gmail.com>
Date: Thu, 19 Apr 2018 02:03:33 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.7.0
MIME-Version: 1.0
In-Reply-To: <57d6f3fa-3d9f-9dda-f2a2-131966b5f407@catalyst.net.nz>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk


On 04/18/2018 11:53 PM, Mark Kirkwood wrote:
> Hi,
>
>
> On 19/04/18 16:40, Wells Oliver wrote:
>> Had an issue tonight where I had a bunch of stalled queries from a client 
>> connection and I just... could... not... kill... them. We disconnected 
>> the client machine, turned it off, picked it up, shook it around, yelled 
>> at it, and still these idle queries remained in pg_stat_activity.
>>
>> Then I did select pg_cancel_backend(pid) from pg_stat_activity where 
>> client_addr = '..' and they just would... not... go.. away.
>>
>> So me being the big smart system administrator guy with shell access, I 
>> logged in, and did a kill -9 xxx where xxx was the sme pid from the 
>> pg_stat_activity result and... they finally went away!
>>
>> Felt good about myself until I realized, well, so did every other 
>> connection, and in fact PG momentarily went into recovery mode.
>>
>> Everything was fine, but a) why is it a bad idea to kill -9 a client PG 
>> process, but pg_cancel_backend() is OK-- and b) what to do about stalled 
>> PG queries that won't die when you disconnect AND when you 
>> pg_cancel_backend() them?
>>
>
> Did you try pg_terminate_backend? I'm guessing that might not have worked 
> either...but is worth it trying before belting them with kill -9!

+1 to pg_terminate_backend.  On the rare occasion pg_cancel_backend doesn't 
work, I hit the pid with pg_terminate_backend, and that always works.

-- 
Angular momentum makes the world go 'round.