Thread: Kill -9 for a session process caused all the sessions to be killed

Hi All,

 

I by mistake ran a query to update a huge table with around 500000 rows and has to kill the session.

 

I found the process-id from the query select * from pg_stat_activity. I killed the process using Kill -9 process_id.

 

This caused all other sessions in the system to be killed and database was unreachable for a minute or so.

 

 

MY QUESTION: Is this normal? Why did command Kill-9 caused other sessions to be killed? What is the best way to kill a session in Postgres?

 

Regards,

Atul Goel

 

Below is the logs I could find

 

 

terminating any other active server processes

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

WARNING:  terminating connection because of crash of another server process

DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT:  In a moment you should be able to reconnect to the database and repeat your command.

LOG:  all server processes terminated; reinitializing

LOG:  database system was interrupted; last known up at 2010-09-22 18:11:41 BST

LOG:  database system was not properly shut down; automatic recovery in progress

LOG:  redo starts at 9/65010CC8

LOG:  unexpected pageaddr 9/61576000 in log file 9, segment 105, offset 5726208

LOG:  redo done at 9/69574B20

LOG:  autovacuum launcher started

LOG:  database system is ready to accept connections

 

Atul Goel

SENIOR DEVELOPER

 

Global DataPoint

Middlesex House, 34-42 Cleveland Street

London W1T 4LB, UK

T: +44 (0)20 7079 4822

M: +44 (0)7846765098

www.globaldatapoint.com

 

This e-mail is confidential and should not be used by anyone who is not the original intended recipient. Global DataPoint Limited does not accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of Global DataPoint Limited. No contracts may be concluded on behalf of Global DataPoint Limited by means of e-mail communication. Global DataPoint Limited Registered in England and Wales with registered number 3739752 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T 4LB

Re: Kill -9 for a session process caused all the sessions to be killed

From
Tom Lane
Date:
<Atul.Goel@globaldatapoint.com> writes:
> I by mistake ran a query to update a huge table with around 500000 rows and has to kill the session.

> I found the process-id from the query select * from pg_stat_activity. I killed the process using Kill -9 process_id.

> This caused all other sessions in the system to be killed and database was unreachable for a minute or so.


> MY QUESTION: Is this normal?

Yes.  That is not the recommended way to cancel a query.

            regards, tom lane

Re: Kill -9 for a session process caused all the sessions to be killed

From
Francisco Reyes
Date:
Atul.Goel@globaldatapoint.com writes:

> to be killed? What is the best way to kill a session in Postgres?


Just plain kill would have worked better. I believe the issue is with the
'-9'.

Also see

http://www.postgresonline.com/journal/index.php?/archives/134-Terminating-An
noying-Back-Ends.html

and

http://www.postgresql.org/docs/8.4/interactive/functions-admin.html
pg_terminate_backend(pid int)
pg_cancel_backend(pid int)

Re: Kill -9 for a session process caused all the sessions to be killed

From
Craig Ringer
Date:
On 23/09/2010 12:26 AM, Atul.Goel@globaldatapoint.com wrote:
> Hi All,
>
> I by mistake ran a query to update a huge table with around 500000 rows
> and has to kill the session.
>
> I found the process-id from the query select * from pg_stat_activity.

> I killed the process using Kill -9 process_id.

Why kill -9 (SIGKILL)?

Did it not respond to regular SIGTERM (the default for kill) ?

SIGKILL will forcibly terminate a process, with no chance for the
process to flush any half-written buffers, tell other processes it is
being killed, etc. In a system of co-operating processes like the
PostgreSQL server, this is bad.

As per the manual, you should not "kill -9" a backend.

For that matter, you should not as a sysadmin "kill -9" (SIGKILL) any
process at all, unless it fails to respond to SIGTERM after a few
seconds (or longer) and letting it finish whatever it's doing isn't an
option. SIGKILL is a really big hammer.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

Thanks Tom,

I found the right way to kill the process.

Regards,
Atul Goel


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 22 September 2010 17:43
To: Atul Goel
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Kill -9 for a session process caused all the sessions to be killed

<Atul.Goel@globaldatapoint.com> writes:
> I by mistake ran a query to update a huge table with around 500000 rows and has to kill the session.

> I found the process-id from the query select * from pg_stat_activity. I killed the process using Kill -9 process_id.

> This caused all other sessions in the system to be killed and database was unreachable for a minute or so.


> MY QUESTION: Is this normal?

Yes.  That is not the recommended way to cancel a query.

                        regards, tom lane
This e-mail is confidential and should not be used by anyone who is not the original intended recipient. Global
DataPointLimited does not accept liability for any statements made which are clearly the sender's own and not expressly
madeon behalf of Global DataPoint Limited. No contracts may be concluded on behalf of Global DataPoint Limited by means
ofe-mail communication. Global DataPoint Limited Registered in England and Wales with registered number 3739752
RegisteredOffice Middlesex House, 34-42 Cleveland Street, London W1T 4LB