Re: [GENERAL] Killing a session in windows
Howard Cole wrote: Wow, yea, I see that now, but it is alone a paragraph above. I updated the text to: The xref linkend=app-pg-ctl program provides a convenient interface for sending these signals to shut down the server. Alternatively, you can send the signal directly using commandkill/ on non-Windows systems. My documentation point is that in administering the service, I cannot drop a database if there is still an active connection. To drop a process in linux appears to be easy using kill, but this does not seem to be the case in windows using taskkill. I would rather see some reference to killing rogue connections using pg_ctl in the Managing Databases chapter, and reference to it in Destroying a Database and DROP DATABASE documentation because this would be where I would first search for a solution when I had the problem. Uh, well we have this TODO: * Allow administrators to safely terminate individual sessions either via an SQL function or SIGTERM Lock table corruption following SIGTERM of an individual backend has been reported in 8.0. A possible cause was fixed in 8.1, but it is unknown whether other problems exist. This item mostly requires additional testing rather than of writing any new code. http://archives.postgresql.org/pgsql-hackers/2006-08/msg00174.php so I am unsure how we would give such a capability on Windows when we don't support it on Unix either. In the pg_ctl documentation, I would recommend explicitly stating that pg_ctl kill -TERM pid can be used to kill individual connections to a database in windows, because taskkill and select pg_cancel_backend() do not seem to always work (for me anyway) in windows. Also HUP and Where do we say that about Unix in the pg_ctl manual? other signals mean nothing to a windows user. We actually simulate these signals on Windows, so the pg_ctl kill actually works just like Unix. We do have in the pg_ctl manual: optionkill/option mode allows you to send a signal to a specified process. This is particularly valuable for productnameMicrosoft Windows/ which does not have a applicationkill/ command. Use literal--help/ to see a list of supported signal names. Is that unclear? In general the documentation, understandably, is geared toward *nix, I do not know what proportion of installations are Windows, but I suspect they are growing at a rapid rate since version 8. Postgres on Windows is a fabulous product, and the migration to the windows platform has been much cleaner than the migration of Mysql, so it would be a shame to lose market share on the basis that the documentation still has sections biased towards *nix. Removal of *nix-isms from the main strand of the documentation and additions of clearly marked build dependant comments where appropriate would make a big difference in uniting the world! So for example, the documentation for pg_ctl would have a description and common options, and then list any linux/bsd/unix/windows differences in section similar to the User Comments sections of the documentation. Can you give a specific example? As I said we simulate Windows so it should act just like Unix. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Killing a session in windows
Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: Alvaro Herrera wrote: Huh, why is it awkward? Alternatively, you can send the signal directly using commandkill/ (or commandpg_ctl kill TERM [process id]/). I think we should mention Windows in there somewhere, because it isn't alternatively on Windows. Actually, this whole change is a bit silly, because the text now says something like: You can send these signals using pg_ctl kill. Alternatively you can use kill (or pg_ctl kill) Wow, yea, I see that now, but it is alone a paragraph above. I updated the text to: The xref linkend=app-pg-ctl program provides a convenient interface for sending these signals to shut down the server. Alternatively, you can send the signal directly using commandkill/ on non-Windows systems. My documentation point is that in administering the service, I cannot drop a database if there is still an active connection. To drop a process in linux appears to be easy using kill, but this does not seem to be the case in windows using taskkill. I would rather see some reference to killing rogue connections using pg_ctl in the Managing Databases chapter, and reference to it in Destroying a Database and DROP DATABASE documentation because this would be where I would first search for a solution when I had the problem. In the pg_ctl documentation, I would recommend explicitly stating that pg_ctl kill -TERM pid can be used to kill individual connections to a database in windows, because taskkill and select pg_cancel_backend() do not seem to always work (for me anyway) in windows. Also HUP and other signals mean nothing to a windows user. In general the documentation, understandably, is geared toward *nix, I do not know what proportion of installations are Windows, but I suspect they are growing at a rapid rate since version 8. Postgres on Windows is a fabulous product, and the migration to the windows platform has been much cleaner than the migration of Mysql, so it would be a shame to lose market share on the basis that the documentation still has sections biased towards *nix. Removal of *nix-isms from the main strand of the documentation and additions of clearly marked build dependant comments where appropriate would make a big difference in uniting the world! So for example, the documentation for pg_ctl would have a description and common options, and then list any linux/bsd/unix/windows differences in section similar to the User Comments sections of the documentation. I enjoyed that! Anyway - Merry Christmas / Eid / Holidays to you all and I'm looking forward to 8.3 under the christmas tree. Howard. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Killing a session in windows
Bruce Momjian wrote: Howard Cole wrote: Which you can do, no? I thought pg_ctl's kill option was invented specifically to make this less painful on Windows. I shall look into the pg_ctl options to see if the kill option does what taskill cannot (thanks for the heads up on that) Using $ pg_ctl kill TERM [pid] worked great. Since very few people seem to know about this, could I suggest making it more prominent in the server administration pages. Agreed. I have added the second sentence to our 8.3 beta docs: Alternatively, you can send the signal directly using commandkill/ (or commandpg_ctl kill TERM [process id]/ on productnameWindows/). You can actually use pg_ctl kill on Unix too but it seems awkward to suggest it in the existing sentence. Huh, why is it awkward? Alternatively, you can send the signal directly using commandkill/ (or commandpg_ctl kill TERM [process id]/). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Killing a session in windows
Alvaro Herrera wrote: Bruce Momjian wrote: Howard Cole wrote: Which you can do, no? I thought pg_ctl's kill option was invented specifically to make this less painful on Windows. I shall look into the pg_ctl options to see if the kill option does what taskill cannot (thanks for the heads up on that) Using $ pg_ctl kill TERM [pid] worked great. Since very few people seem to know about this, could I suggest making it more prominent in the server administration pages. Agreed. I have added the second sentence to our 8.3 beta docs: Alternatively, you can send the signal directly using commandkill/ (or commandpg_ctl kill TERM [process id]/ on productnameWindows/). You can actually use pg_ctl kill on Unix too but it seems awkward to suggest it in the existing sentence. Huh, why is it awkward? Alternatively, you can send the signal directly using commandkill/ (or commandpg_ctl kill TERM [process id]/). I think we should mention Windows in there somewhere, because it isn't alternatively on Windows. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Killing a session in windows
Bruce Momjian wrote: Alvaro Herrera wrote: Huh, why is it awkward? Alternatively, you can send the signal directly using commandkill/ (or commandpg_ctl kill TERM [process id]/). I think we should mention Windows in there somewhere, because it isn't alternatively on Windows. Actually, this whole change is a bit silly, because the text now says something like: You can send these signals using pg_ctl kill. Alternatively you can use kill (or pg_ctl kill) In my opinion this change should be reverted. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Killing a session in windows
Alvaro Herrera wrote: Bruce Momjian wrote: Alvaro Herrera wrote: Huh, why is it awkward? Alternatively, you can send the signal directly using commandkill/ (or commandpg_ctl kill TERM [process id]/). I think we should mention Windows in there somewhere, because it isn't alternatively on Windows. Actually, this whole change is a bit silly, because the text now says something like: You can send these signals using pg_ctl kill. Alternatively you can use kill (or pg_ctl kill) Wow, yea, I see that now, but it is alone a paragraph above. I updated the text to: The xref linkend=app-pg-ctl program provides a convenient interface for sending these signals to shut down the server. Alternatively, you can send the signal directly using commandkill/ on non-Windows systems. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Killing a session in windows
Howard Cole wrote: Which you can do, no? I thought pg_ctl's kill option was invented specifically to make this less painful on Windows. I shall look into the pg_ctl options to see if the kill option does what taskill cannot (thanks for the heads up on that) Using $ pg_ctl kill TERM [pid] worked great. Since very few people seem to know about this, could I suggest making it more prominent in the server administration pages. Agreed. I have added the second sentence to our 8.3 beta docs: Alternatively, you can send the signal directly using commandkill/ (or commandpg_ctl kill TERM [process id]/ on productnameWindows/). You can actually use pg_ctl kill on Unix too but it seems awkward to suggest it in the existing sentence. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Killing a session in windows
Dann Corbit wrote: What happens if you disable the net card on your server machine? Hi Dann, The connection is from IIS worker process running on the same machine. My problem is that I want to disable connections for a single database. Disabling the pgsql service or disabling the card (can you do this on a windows machine?) would disconnect all the databases which would be major incident on the server. Howard. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Killing a session in windows
Thomas H. wrote: On Tue, Dec 11, 2007 at 05:50:46PM -0500, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Howard Cole wrote: I take it from the lack of response that nobody knows how to kill a connection from the postgresql side on windows? You can't, short of sending a signal to the process or restarting the service. Which you can do, no? I thought pg_ctl's kill option was invented specifically to make this less painful on Windows. It does, and it shuold work. But it's just as dangerous as using kill directly on the backends on Unix, of course. wasn't the OP asking for a way to kill active connections to a db? afaik pgAdmin3 does provide this functionality: pgadmin3 tools server status there you can easily terminate connections transactions to a particular db. works pretty well in my test, i can kill active connections and drop the db afterwards. regards, thomas The pgadmin terminate connections never did anything on any of my windows servers. I always assumed it was something geared towards nix servers that never quite worked on windows??? I shall look into the pg_ctl options to see if the kill option does what taskill cannot (thanks for the heads up on that) Thanks all. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Killing a session in windows
wasn't the OP asking for a way to kill active connections to a db? afaik pgAdmin3 does provide this functionality: pgadmin3 tools server status there you can easily terminate connections transactions to a particular db. works pretty well in my test, i can kill active connections and drop the db afterwards. regards, thomas The pgadmin terminate connections never did anything on any of my windows servers. I always assumed it was something geared towards nix servers that never quite worked on windows??? i'm using the pgAdmin3 on windows 2003 and win32 pgsql 8.x for quite a while now, and the terminate feature has been working just fine a few times in the last years. - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Killing a session in windows
Which you can do, no? I thought pg_ctl's kill option was invented specifically to make this less painful on Windows. I shall look into the pg_ctl options to see if the kill option does what taskill cannot (thanks for the heads up on that) Using $ pg_ctl kill TERM [pid] worked great. Since very few people seem to know about this, could I suggest making it more prominent in the server administration pages. Thanks for your help gentlemen. Once again I am overwhelmed by the quality of support on the forum. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Killing a session in windows
Thomas H. wrote: wasn't the OP asking for a way to kill active connections to a db? afaik pgAdmin3 does provide this functionality: pgadmin3 tools server status there you can easily terminate connections transactions to a particular db. works pretty well in my test, i can kill active connections and drop the db afterwards. regards, thomas The pgadmin terminate connections never did anything on any of my windows servers. I always assumed it was something geared towards nix servers that never quite worked on windows??? i'm using the pgAdmin3 on windows 2003 and win32 pgsql 8.x for quite a while now, and the terminate feature has been working just fine a few times in the last years. - thomas Hi Thomas, Just tried it on my vista client machine. The Terminate button is greyed out - I just have a cancel option. Maybe I am not using pgAdmin right? P.S. The pg_ctl kill TERM worked fine. Howard. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Killing a session in windows
On Tue, Dec 11, 2007 at 05:50:46PM -0500, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Howard Cole wrote: I take it from the lack of response that nobody knows how to kill a connection from the postgresql side on windows? You can't, short of sending a signal to the process or restarting the service. Which you can do, no? I thought pg_ctl's kill option was invented specifically to make this less painful on Windows. It does, and it shuold work. But it's just as dangerous as using kill directly on the backends on Unix, of course. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Killing a session in windows
On Tue, Dec 11, 2007 at 05:50:46PM -0500, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Howard Cole wrote: I take it from the lack of response that nobody knows how to kill a connection from the postgresql side on windows? You can't, short of sending a signal to the process or restarting the service. Which you can do, no? I thought pg_ctl's kill option was invented specifically to make this less painful on Windows. It does, and it shuold work. But it's just as dangerous as using kill directly on the backends on Unix, of course. wasn't the OP asking for a way to kill active connections to a db? afaik pgAdmin3 does provide this functionality: pgadmin3 tools server status there you can easily terminate connections transactions to a particular db. works pretty well in my test, i can kill active connections and drop the db afterwards. regards, thomas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Killing a session in windows
Hello everyone, I take it from the lack of response that nobody knows how to kill a connection from the postgresql side on windows? Is there another way around this? In the end I created another database and changed all my code to use the new database, the original database is still there (now redundant for 4 days) and the mysterious connection is still there. There has to be a better solution? Howard. Howard Cole wrote: Hi, I've written about this problem before and thanks to Bill Bartlett and Richard Huxton for previous replies, but the problem keeps coming up... I'm running postgresql V8.2.5 (I think!) on W2K3 Server and occasionally I want to rebuild a database. However I cannot drop the database because there is some unclosed client attached. Following Richard and Bills suggestions, I have tried pg_cancel_backend(pid) but the process remains in pg_stat_activity and the process still runs. I then kill the process directly using taskill but again, the process remains in pg_stat_activity and I cannot drop the database. I eventually have to resort to restarting the postgres service, but this is on a live system running several database so I do not want to have to do this. Any suggestions? Ta Howard www.selestial.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Killing a session in windows
Howard Cole wrote: Hello everyone, I take it from the lack of response that nobody knows how to kill a connection from the postgresql side on windows? You can't, short of sending a signal to the process or restarting the service. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 We're here to devour each other alive(Hobbes) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Killing a session in windows
Alvaro Herrera [EMAIL PROTECTED] writes: Howard Cole wrote: I take it from the lack of response that nobody knows how to kill a connection from the postgresql side on windows? You can't, short of sending a signal to the process or restarting the service. Which you can do, no? I thought pg_ctl's kill option was invented specifically to make this less painful on Windows. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Killing a session in windows
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Howard Cole Sent: Tuesday, December 11, 2007 2:21 PM To: 'PgSql General' Subject: Re: [GENERAL] Killing a session in windows Hello everyone, I take it from the lack of response that nobody knows how to kill a connection from the postgresql side on windows? Is there another way around this? In the end I created another database and changed all my code to use the new database, the original database is still there (now redundant for 4 days) and the mysterious connection is still there. There has to be a better solution? What happens if you disable the net card on your server machine? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Killing a session in windows
Hi, I've written about this problem before and thanks to Bill Bartlett and Richard Huxton for previous replies, but the problem keeps coming up... I'm running postgresql V8.2.5 (I think!) on W2K3 Server and occasionally I want to rebuild a database. However I cannot drop the database because there is some unclosed client attached. Following Richard and Bills suggestions, I have tried pg_cancel_backend(pid) but the process remains in pg_stat_activity and the process still runs. I then kill the process directly using taskill but again, the process remains in pg_stat_activity and I cannot drop the database. I eventually have to resort to restarting the postgres service, but this is on a live system running several database so I do not want to have to do this. Any suggestions? Ta Howard www.selestial.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Killing a session on windows
I have a database I want to drop on a windows server. Unfortunately I cannot restart postgres because it is running several live database. To kill the offending session, I tried select * from pg_stat_activity to find the PID of the session, and then tried to kill it with command line: taskkill /f /pid 1234 This appeared to kill the session, but postgres still thinks the session is live with the same process id. Using the SysInternals process explorer - there doesn't appear to be a process with the given ID. How can I get postgres to drop this session? Thanks Howard Cole www.selestial.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Killing a session on windows
Howard Cole wrote: I have a database I want to drop on a windows server. Unfortunately I cannot restart postgres because it is running several live database. To kill the offending session, I tried select * from pg_stat_activity to find the PID of the session, and then tried to kill it with command line: taskkill /f /pid 1234 This appeared to kill the session, but postgres still thinks the session is live with the same process id. Hmm - some signalling glitch presumably. Was it not possible to identify the client and disconnect that? Using the SysInternals process explorer - there doesn't appear to be a process with the given ID. How can I get postgres to drop this session? You could try pg_cancel_backend() - not sure what happens if it can't find the process though. http://www.postgresql.org/docs/8.2/static/functions-admin.html -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Killing a session on windows
Use select pg_cancel_backend(pid) instead -- we have to do this periodically when queries get timed out by the web server but Postgres doesn't notice / doesn't get notified... - Bill -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Howard Cole Sent: Thursday, November 29, 2007 5:55 AM To: 'PgSql General' Subject: [GENERAL] Killing a session on windows I have a database I want to drop on a windows server. Unfortunately I cannot restart postgres because it is running several live database. To kill the offending session, I tried select * from pg_stat_activity to find the PID of the session, and then tried to kill it with command line: taskkill /f /pid 1234 This appeared to kill the session, but postgres still thinks the session is live with the same process id. Using the SysInternals process explorer - there doesn't appear to be a process with the given ID. How can I get postgres to drop this session? Thanks Howard Cole www.selestial.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/