Обсуждение: 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
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 >
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)
Alvaro Herrera <alvherre@alvh.no-ip.org> 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
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] 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?
On Tue, Dec 11, 2007 at 05:50:46PM -0500, Tom Lane wrote: > Alvaro Herrera <alvherre@alvh.no-ip.org> 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
> On Tue, Dec 11, 2007 at 05:50:46PM -0500, Tom Lane wrote: >> Alvaro Herrera <alvherre@alvh.no-ip.org> 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
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.
Thomas H. wrote: > >> On Tue, Dec 11, 2007 at 05:50:46PM -0500, Tom Lane wrote: >>> Alvaro Herrera <alvherre@alvh.no-ip.org> 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.
>> 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
>>>> 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.
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.
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 <command>kill</> (or <command>pg_ctl kill TERM [process id]</> on <productname>Windows</>). You can actually use pg_ctl kill on Unix too but it seems awkward to suggest it in the existing sentence. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
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 <command>kill</>
> (or <command>pg_ctl kill TERM [process id]</> on <productname>Windows</>).
>
> 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 <command>kill</>
(or <command>pg_ctl kill TERM [process id]</>).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
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 <command>kill</> > > (or <command>pg_ctl kill TERM [process id]</> on <productname>Windows</>). > > > > 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 <command>kill</> > (or <command>pg_ctl kill TERM [process id]</>). I think we should mention Windows in there somewhere, because it isn't "alternatively" on Windows. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Alvaro Herrera wrote: > > Huh, why is it awkward? > > > > Alternatively, you can send the signal directly using <command>kill</> > > (or <command>pg_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 Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Bruce Momjian wrote: > > Alvaro Herrera wrote: > > > > Huh, why is it awkward? > > > > > > Alternatively, you can send the signal directly using <command>kill</> > > > (or <command>pg_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 <command>kill</> on non-Windows systems. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
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 <command>kill</> >>>> (or <command>pg_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 > <command>kill</> 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.
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
> > <command>kill</> 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:
<option>kill</option> mode allows you to send a signal to a specified
process. This is particularly valuable for <productname>Microsoft Windows</>
which does not have a <application>kill</> 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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +