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
<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
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)
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