Thread: How To Handle Hung Connections

How To Handle Hung Connections

From
Carlos Mennens
Date:
I'm attempting to delete a database that I've obviously not closed
connections from cleanly.

postgres=# DROP DATABASE filters;
ERROR:  database "filters" is being accessed by other users
DETAIL:  There are 4 other session(s) using the database.

How exactly would one manage this issue from a PostgreSQL
administration stand point? I know there are not real users connected
to this database but rather zombie connections. Is there a way to
force the DROP command or can I manually view / kill connections to
this database w/o having to restart the entire PostgreSQL daemon and
impact other active databases?

Re: How To Handle Hung Connections

From
Mike Blackwell
Date:
To se a list of backends for a database:

select * from pg_stat_activity where datname = 'database_in_question';

To terminate a backend:

select pg_terminate_backend(pid);

Be sure you get the right ones...

I like to revoke connect privileges for the database first, if I can't stop the applications/users attempting to access the database, to avoid them grabbing another connection while I'm typing. 


__________________________________________________________________________________
Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com





On Tue, Dec 20, 2011 at 09:50, Carlos Mennens <carlos.mennens@gmail.com> wrote:
I'm attempting to delete a database that I've obviously not closed
connections from cleanly.

postgres=# DROP DATABASE filters;
ERROR:  database "filters" is being accessed by other users
DETAIL:  There are 4 other session(s) using the database.

How exactly would one manage this issue from a PostgreSQL
administration stand point? I know there are not real users connected
to this database but rather zombie connections. Is there a way to
force the DROP command or can I manually view / kill connections to
this database w/o having to restart the entire PostgreSQL daemon and
impact other active databases?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general