pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNever kill -9 postgres client processes on Linux... but why not?
5+ messages / 5 participants
[nested] [flat]
* Never kill -9 postgres client processes on Linux... but why not?
@ 2018-04-19 04:40 Wells Oliver <wells.oliver@gmail.com>
2018-04-19 04:53 ` Re: Never kill -9 postgres client processes on Linux... but why not? Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-04-19 05:23 ` Re: Never kill -9 postgres client processes on Linux... but why not? Tim Cross <theophilusx@gmail.com>
2018-04-19 12:59 ` Re: Never kill -9 postgres client processes on Linux... but why not? Jerry Sievers <gsievers19@comcast.net>
0 siblings, 3 replies; 5+ messages in thread
From: Wells Oliver @ 2018-04-19 04:40 UTC (permalink / raw)
To: pgsql-admin
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?
Thanks!
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Never kill -9 postgres client processes on Linux... but why not?
2018-04-19 04:40 Never kill -9 postgres client processes on Linux... but why not? Wells Oliver <wells.oliver@gmail.com>
@ 2018-04-19 04:53 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-04-19 07:03 ` Re: Never kill -9 postgres client processes on Linux... but why not? Ron <ronljohnsonjr@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Mark Kirkwood @ 2018-04-19 04:53 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
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!
Kill -9 leaves the shared memory the backend was using intact but
possibly corrupted (partial writes etc), so Postgres shuts down
everything! This is pretty 'normal' in the db world (I think DB2 does
the same if you kill -9 a connection from the OS).
regards
Mark
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Never kill -9 postgres client processes on Linux... but why not?
2018-04-19 04:40 Never kill -9 postgres client processes on Linux... but why not? Wells Oliver <wells.oliver@gmail.com>
2018-04-19 04:53 ` Re: Never kill -9 postgres client processes on Linux... but why not? Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
@ 2018-04-19 07:03 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ron @ 2018-04-19 07:03 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Never kill -9 postgres client processes on Linux... but why not?
2018-04-19 04:40 Never kill -9 postgres client processes on Linux... but why not? Wells Oliver <wells.oliver@gmail.com>
@ 2018-04-19 05:23 ` Tim Cross <theophilusx@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tim Cross @ 2018-04-19 05:23 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
On 19 April 2018 at 14:40, Wells Oliver <wells.oliver@gmail.com> 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?
>
>
>
Not sure about postgres specific reasons not to kill -9, but from a more
general perspective, kill -9 should only be used once all other more
'polite' kill requests have been attempted. For example, kill -TERM The
problem with kill -9 is that it is a hard, non-catchable kill signal - this
means there is no opportunity for the process to handle the request and
cleanup before it quits. This can result in memory not being released or
being corrupted as well as other resources not being released. Rather than
going straight for kill -9, at least try kill -15 (TERM) first. If that
doesn't work, then pull out the big gun.
--
regards,
Tim
--
Tim Cross
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Never kill -9 postgres client processes on Linux... but why not?
2018-04-19 04:40 Never kill -9 postgres client processes on Linux... but why not? Wells Oliver <wells.oliver@gmail.com>
@ 2018-04-19 12:59 ` Jerry Sievers <gsievers19@comcast.net>
2 siblings, 0 replies; 5+ messages in thread
From: Jerry Sievers @ 2018-04-19 12:59 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
Wells Oliver <wells.oliver@gmail.com> writes:
> 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.
Idle backends won't respond to a cancel since if they're "idle" there's
nothing to cancel :-)
If OTOH you tried pg_terminate_backend and this too drew no response,
then you might have a case of backend blocked in a critical section
such as SendV, symtomatic of a backend trying to write to a full pipe.
HTH
>
> 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?
>
> Thanks!
>
> --
> Wells Oliver
> wells.oliver@gmail.com
>
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-04-19 12:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-19 04:40 Never kill -9 postgres client processes on Linux... but why not? Wells Oliver <wells.oliver@gmail.com>
2018-04-19 04:53 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-04-19 07:03 ` Ron <ronljohnsonjr@gmail.com>
2018-04-19 05:23 ` Tim Cross <theophilusx@gmail.com>
2018-04-19 12:59 ` Jerry Sievers <gsievers19@comcast.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox