CODEWITHSUNDEEP
postgresql
Dylan Hettinger
Dylan Hettinger

Reputation: 795

How should I terminate a process when pg_cancel_backend doesn't work?

Occassionally a query will continue to run even after I 'stop' it in pgAdmin, run pg_cancel_backend, pg_terminate_backend and kill from the command line. The only option I've found is to fully stop and restart the postgres service (generally using pg_ctl -m immediate). I'm currently using Postgres 9.1.

Are there other options to fully terminate a running process?

Upvotes: 1

Views: 5460

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324731

Revisiting this, there are places where PostgreSQL backends are doing work where there's no CHECK_FOR_INTERRUPTS for some time. These patches are avoided where possible, but still happen.

If you find such a case, report it.

You can't cleanly stop the backend until it checks for an interrupt, so you must simply wait. Or you can restart the whole database server. If you hard kill the backend of interest with (e.g.) kill -9, PostgreSQL will treat shared_buffers as potentially corrupt and immediately force a restart, disconnecting all current sessions. So you might as well use an immediate shutdown instead.

Upvotes: 1

Craig Ringer
Craig Ringer

Reputation: 324731

This shouldn't really be happening. What is the problem backend doing? Check:

ps -C postgres -o pid,ppid,stat,start,time,%cpu,%mem,blocked,ignored,wchan:80,cmd

replacing -C postgres with -p the_pid if you know it. Make sure to include the process name which appears after the wide chan line. Update your answer with the whole line.

You may also want to get a backtrace from the backend to see what it's doing. You're probably on Linux or BSD given kill, so try:

gdb -p the_pid
(gdb) bt
... blah blah copy this blah ...
(gdb) q

eg:

gdb -p 914
......blah blah ........
(gdb) bt
#0  0x0000003c31ceacc3 in __select_nocancel () from /lib64/libc.so.6
#1  0x00000000005f73b6 in ?? ()
#2  0x00000000005f7c36 in SysLogger_Start ()
#3  0x00000000005f60b0 in PostmasterMain ()
#4  0x0000000000457039 in main ()
(gdb) q
A debugging session is active.

        Inferior 1 [process 914] will be detached.

Quit anyway? (y or n) y
Detaching from program: /usr/bin/postgres, process 914

If possible install debugging symbol packages first. I haven't for Pg 9.1 on my machine, so my backtrace above isn't very useful. See the Pg wiki article.

The backtrace could be very long, so consider dropping it on a pastebin site, not directly into your question, and just linking to it here.

Upvotes: 3

Related Questions