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

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/

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

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailtogsql-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?

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> 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


---------------------------(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

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

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

>>>> 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/