2

I am trying to terminate a session (a specific session or all sessions, doesnt matter) in postgresql 8.3 and am having trouble doing that. I know in newer versions (8.4 and later) there is a pg_terminate_backend command that will do the trick but this is not available in postgresql 8.3. If I use pg_stat_activity, I can see all the sessions that are active but have no way of terminating them.

The solution does not have to necessarily be sql commands but I would like it to be independent of the OS that is being used (i.e. no DOS/UNIX commands).

Stopping and starting the postgres service in windows services works perfectly but this is an OS specific approach. Using 'pg_ctl restart -D DATA_DIR' does not stop the service however. Actually using pg_ctl to try and restart the service at the time I am trying to do it causes some weird behavior. If there is a way I can somehow use pg_ctl to force shutdown the process like I assume windows does, then I can probably use that.

Anyways, I am looking for a way to terminate one or all sessions in postgresql 8.3 that is not platform specific. Any help would be great!

user972276
  • 2,973
  • 9
  • 33
  • 46
  • 2
    You should *really* think about upgrading to a supported version. 8.3 is really old (btw: there are no "DOS" commands to stop Postgres because Postgres does not run on "DOS") –  Mar 21 '13 at 22:00
  • @a_horse_with_no_name I know windows doesnt really use 'DOS' anymore. I use the term mainly to distinguish between a windows GUI approach vs a command line approach. I guess I should get out of the habit of misusing the term and find another simple word like CMD or something to call it. – user972276 Mar 22 '13 at 14:15

2 Answers2

7

You can use pg_cancel_backend():

select pg_cancel_backend(55555);

You can use this with pg_stat_activity. For example:

select pg_cancel_backend(procpid)
from pg_stat_activity where current_query='<IDLE>';

If that doesn't work you can try this:

pg_ctl kill -TERM pid

That should be OS independent. I'm not sure if there's any real difference in behaviour.

Other than that you could try stopping and starting the server, but you indicated odd behaviour from that. (What kind?)

Finally, for an OS specific option, on linux you can of course try using the kill command. kill -15 (SIGTERM) is safe; that's basically what pg_terminate_backend uses: kill -15 <pid>. kill -9 is moderately unsafe and you should use it only as a last resort.

Community
  • 1
  • 1
Ilion
  • 6,772
  • 3
  • 24
  • 47
  • 1
    Re `kill -9` please read http://serverfault.com/questions/415188/kill-9-a-postgres-process for why it's really not a good idea. Please don't advise people to `kill -9` Pg without linking to a detailed explanation; it's fairly fine so long as you don't do anything silly, but you have to be careful. `kill -9` will *always* cause PostgreSQL to crash and restart, aborting all running statements and uncommitted transactions. – Craig Ringer Mar 22 '13 at 08:53
  • Thanks! pg_ctl kill worked for me. One thing to point out is that the dash before the kill type should not be there. Here is the corrected command: pg_ctl kill TERM pid – user972276 Mar 22 '13 at 13:59
  • @CraigRinger: Thanks for the clarification. At the time I am doing the kill, all the sessions should be idle anyways so a restart would not be a problem. – user972276 Mar 22 '13 at 14:10
  • Also, pg_cancel_backend(pid) does not terminate the process, it just cancels the query that proc was working on. – user972276 Mar 22 '13 at 14:17
0
su - posgres
psql


SELECT pg_terminate_backend(pg_stat_activity.procpid)  FROM  pg_stat_activity  WHERE  procpid <> pg_backend_pid()  AND datname = 'dbname' ;
drop database "database name";
ρss
  • 5,115
  • 8
  • 43
  • 73
Abhishek Tiwari
  • 1
  • 1
    It is recommended to add some explanation to your answer so the other users have a clear understanding about the answer. – ρss Apr 21 '15 at 12:19