pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedKill postgresql process
8+ messages / 3 participants
[nested] [flat]
* Kill postgresql process
@ 2021-06-14 15:41 abbas alizadeh <ramkly@yahoo.com>
0 siblings, 2 replies; 8+ messages in thread
From: abbas alizadeh @ 2021-06-14 15:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi all.
How we can kill the stuck process?
The process didn’t kill by pg_cancel_backend/pg_terminate_backend.
Is there any way beside of using “kill -9” or “pg_ctl kill ABRT” ?
Best regard
Abbas
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Kill postgresql process
@ 2021-06-14 15:49 Julien Rouhaud <rjuju123@gmail.com>
parent: abbas alizadeh <ramkly@yahoo.com>
1 sibling, 1 reply; 8+ messages in thread
From: Julien Rouhaud @ 2021-06-14 15:49 UTC (permalink / raw)
To: abbas alizadeh <ramkly@yahoo.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Jun 14, 2021 at 11:41 PM abbas alizadeh <ramkly@yahoo.com> wrote:
>
> How we can kill the stuck process?
> The process didn’t kill by pg_cancel_backend/pg_terminate_backend.
> Is there any way beside of using “kill -9” or “pg_ctl kill ABRT” ?
pg_cancel_backend / pg_terminate_backend is the normal way to do that.
If it's not working it means there's something going on. Is it a
regular backend, and if yes what query is it executing?
Do you have any 3rd party extensions, and what is the postgres versions?
If could also help to get a stack trace:
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Kill postgresql process
@ 2021-06-14 16:15 Laurenz Albe <laurenz.albe@cybertec.at>
parent: abbas alizadeh <ramkly@yahoo.com>
1 sibling, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2021-06-14 16:15 UTC (permalink / raw)
To: abbas alizadeh <ramkly@yahoo.com>; pgsql-admin@lists.postgresql.org
On Mon, 2021-06-14 at 23:41 +0800, abbas alizadeh wrote:
> How we can kill the stuck process?
> The process didn’t kill by pg_cancel_backend/pg_terminate_backend.
> Is there any way beside of using “kill -9” or “pg_ctl kill ABRT” ?
If you don't mind a little hackery, I found a simple way to
terminate backends that don't react to interrupts that avoids
"kill -9" and the ensuing crash recovery:
- Attach to the backend with gdb.
- Enter "print ProcessInterrupts()"
Of course that will not work if the backend is in uninterruptible
sleep (for example, stuck in an I/O operation).
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Kill postgresql process
@ 2021-06-14 16:25 Julien Rouhaud <rjuju123@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 8+ messages in thread
From: Julien Rouhaud @ 2021-06-14 16:25 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: abbas alizadeh <ramkly@yahoo.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Le mar. 15 juin 2021 à 00:15, Laurenz Albe:
>
> - Attach to the backend with gdb.
> - Enter "print ProcessInterrupts()"
>
> Of course that will not work if the backend is in uninterruptible
> sleep (for example, stuck in an I/O operation).
>
that may be a terrible idea and leave a buffer pinned or something. and if
it's not it won't tell us where we're missing a CALL_FOR_INTERRUPTS
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Kill postgresql process
@ 2021-06-14 16:32 abbas alizadeh <ramkly@yahoo.com>
parent: Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: abbas alizadeh @ 2021-06-14 16:32 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
The query was called by web server, It was connected to database through socket and connection wasn’t terminate. I tried to close the connection, but it didn’t work.
Postgres version is 9.2 for this case.
We use postgis and pg_repack
> On 14 Jun 2021, at 11:49 PM, Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> On Mon, Jun 14, 2021 at 11:41 PM abbas alizadeh <ramkly@yahoo.com> wrote:
>>
>> How we can kill the stuck process?
>> The process didn’t kill by pg_cancel_backend/pg_terminate_backend.
>> Is there any way beside of using “kill -9” or “pg_ctl kill ABRT” ?
>
> pg_cancel_backend / pg_terminate_backend is the normal way to do that.
> If it's not working it means there's something going on. Is it a
> regular backend, and if yes what query is it executing?
>
> Do you have any 3rd party extensions, and what is the postgres versions?
>
> If could also help to get a stack trace:
> https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Kill postgresql process
@ 2021-06-14 17:03 Julien Rouhaud <rjuju123@gmail.com>
parent: abbas alizadeh <ramkly@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Julien Rouhaud @ 2021-06-14 17:03 UTC (permalink / raw)
To: abbas alizadeh <ramkly@yahoo.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, Jun 15, 2021 at 12:32:40AM +0800, abbas alizadeh wrote:
> The query was called by web server, It was connected to database through
> socket and connection wasn’t terminate. I tried to close the connection, but
> it didn’t work.
Yes, I understand that. What do you see in pg_stat_activity for the given pid?
Is there any chance you can get a stack trace (with the link previously
provided)?
> Postgres version is 9.2 for this case.
Which minor version?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Kill postgresql process
@ 2021-06-15 07:01 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2021-06-15 07:01 UTC (permalink / raw)
To: Julien Rouhaud <rjuju123@gmail.com>; +Cc: abbas alizadeh <ramkly@yahoo.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, 2021-06-15 at 00:25 +0800, Julien Rouhaud wrote:
> Le mar. 15 juin 2021 à 00:15, Laurenz Albe:
> > - Attach to the backend with gdb.
> > - Enter "print ProcessInterrupts()"
> >
> > Of course that will not work if the backend is in uninterruptible
> > sleep (for example, stuck in an I/O operation).
>
> that may be a terrible idea and leave a buffer pinned or something.
> and if it's not it won't tell us where we're missing a CALL_FOR_INTERRUPTS
If there is an error, AbortCurrentTransaction() gets called, which releases
all buffer pins. If you were stuck in an endless loop in a critical
section, you are going to crash anyway.
If you want to investigate where you are stuck, run a backtrace first.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Kill postgresql process
@ 2021-06-18 14:33 abbas alizadeh <ramkly@yahoo.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 8+ messages in thread
From: abbas alizadeh @ 2021-06-18 14:33 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Julien Rouhaud <rjuju123@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks Laurenz, Julien
Stack trace helps alot to find a stuck point.
That AbortCurrentTransaction wirked as well.
Regards
Abbas
> On 15 Jun 2021, at 3:01 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Tue, 2021-06-15 at 00:25 +0800, Julien Rouhaud wrote:
>> Le mar. 15 juin 2021 à 00:15, Laurenz Albe:
>>> - Attach to the backend with gdb.
>>> - Enter "print ProcessInterrupts()"
>>>
>>> Of course that will not work if the backend is in uninterruptible
>>> sleep (for example, stuck in an I/O operation).
>>
>> that may be a terrible idea and leave a buffer pinned or something.
>> and if it's not it won't tell us where we're missing a CALL_FOR_INTERRUPTS
>
> If there is an error, AbortCurrentTransaction() gets called, which releases
> all buffer pins. If you were stuck in an endless loop in a critical
> section, you are going to crash anyway.
>
> If you want to investigate where you are stuck, run a backtrace first.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-06-18 14:33 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-14 15:41 Kill postgresql process abbas alizadeh <ramkly@yahoo.com>
2021-06-14 15:49 ` Julien Rouhaud <rjuju123@gmail.com>
2021-06-14 16:32 ` abbas alizadeh <ramkly@yahoo.com>
2021-06-14 17:03 ` Julien Rouhaud <rjuju123@gmail.com>
2021-06-14 16:15 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-06-14 16:25 ` Julien Rouhaud <rjuju123@gmail.com>
2021-06-15 07:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-06-18 14:33 ` abbas alizadeh <ramkly@yahoo.com>
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