pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWho's attached to the database?
12+ messages / 8 participants
[nested] [flat]
* Who's attached to the database?
@ 2008-07-03 19:45 Carol Walter <walterc@indiana.edu>
2008-07-03 19:53 ` Re: Who's attached to the database? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-03 19:54 ` Re: Who's attached to the database? Tom Lane <tgl@sss.pgh.pa.us>
2008-07-03 19:55 ` Re: Who's attached to the database? Jeff Frost <jeff@frostconsultingllc.com>
2008-07-03 19:55 ` Re: Who's attached to the database? paul socha <pawel.socha@gmail.com>
2008-07-04 13:47 ` Re: Who's attached to the database? Lennin Caro <lennin.caro@yahoo.com>
0 siblings, 5 replies; 12+ messages in thread
From: Carol Walter @ 2008-07-03 19:45 UTC (permalink / raw)
To: pgsql-admin
Hi, Guys,
I'm trying to drop a database and I'm getting an error that says that
the database is being accessed by other users. Is there a way I can
find out who these users are or if there really is a user accessing it?
Carol
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who's attached to the database?
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
@ 2008-07-03 19:53 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-03 20:07 ` Re: Who's attached to the database? Carol Walter <walterc@indiana.edu>
4 siblings, 1 reply; 12+ messages in thread
From: Kevin Grittner @ 2008-07-03 19:53 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; pgsql-admin
>>> Carol Walter <walterc@indiana.edu> wrote:
> I'm trying to drop a database and I'm getting an error that says that
> the database is being accessed by other users. Is there a way I can
> find out who these users are or if there really is a user accessing
it?
select * from pg_stat_activity where datname = 'yourdb';
-Kevin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who's attached to the database?
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
2008-07-03 19:53 ` Re: Who's attached to the database? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2008-07-03 20:07 ` Carol Walter <walterc@indiana.edu>
2008-07-03 21:17 ` Re: Who's attached to the database? Bricklen Anderson <banderson@presinet.com>
2008-07-04 13:50 ` Re: Who's attached to the database? Lennin Caro <lennin.caro@yahoo.com>
0 siblings, 2 replies; 12+ messages in thread
From: Carol Walter @ 2008-07-03 20:07 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
Thanks, guys,
This told me that the user that has it open is the interface user
called db_user. Is there a command to disconnect this user?
Carol
On Jul 3, 2008, at 3:53 PM, Kevin Grittner wrote:
>>>> Carol Walter <walterc@indiana.edu> wrote:
>> I'm trying to drop a database and I'm getting an error that says that
>
>> the database is being accessed by other users. Is there a way I can
>
>> find out who these users are or if there really is a user accessing
> it?
>
> select * from pg_stat_activity where datname = 'yourdb';
>
> -Kevin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who's attached to the database?
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
2008-07-03 19:53 ` Re: Who's attached to the database? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-03 20:07 ` Re: Who's attached to the database? Carol Walter <walterc@indiana.edu>
@ 2008-07-03 21:17 ` Bricklen Anderson <banderson@presinet.com>
2008-07-03 21:23 ` Re: Who's attached to the database? Carol Walter <walterc@indiana.edu>
2008-07-03 22:04 ` Re: Who's attached to the database? Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 2 replies; 12+ messages in thread
From: Bricklen Anderson @ 2008-07-03 21:17 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
Carol Walter wrote:
> Thanks, guys,
>
> This told me that the user that has it open is the interface user called
> db_user. Is there a command to disconnect this user?
>
> Carol
select pg_cancel_backend(<procpid of connection>);
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who's attached to the database?
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
2008-07-03 19:53 ` Re: Who's attached to the database? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-03 20:07 ` Re: Who's attached to the database? Carol Walter <walterc@indiana.edu>
2008-07-03 21:17 ` Re: Who's attached to the database? Bricklen Anderson <banderson@presinet.com>
@ 2008-07-03 21:23 ` Carol Walter <walterc@indiana.edu>
1 sibling, 0 replies; 12+ messages in thread
From: Carol Walter @ 2008-07-03 21:23 UTC (permalink / raw)
To: Bricklen Anderson <banderson@presinet.com>; +Cc: pgsql-admin
Oh, thanks so much. This is something I really needed.
Carol
On Jul 3, 2008, at 5:17 PM, Bricklen Anderson wrote:
> Carol Walter wrote:
>> Thanks, guys,
>> This told me that the user that has it open is the interface user
>> called db_user. Is there a command to disconnect this user?
>> Carol
>
> select pg_cancel_backend(<procpid of connection>);
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who's attached to the database?
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
2008-07-03 19:53 ` Re: Who's attached to the database? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-03 20:07 ` Re: Who's attached to the database? Carol Walter <walterc@indiana.edu>
2008-07-03 21:17 ` Re: Who's attached to the database? Bricklen Anderson <banderson@presinet.com>
@ 2008-07-03 22:04 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 12+ messages in thread
From: Kevin Grittner @ 2008-07-03 22:04 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; Bricklen Anderson <banderson@presinet.com>; +Cc: pgsql-admin
>>> Bricklen Anderson <banderson@presinet.com> wrote:
>> Is there a command to disconnect this user?
>
> select pg_cancel_backend(<procpid of connection>);
I thought that just canceled the currently active statement (if any).
Don't you need to use pg_ctl kill (or the OS kill command) to actually
close the connection?
http://www.postgresql.org/docs/8.3/interactive/functions-admin.html
http://www.postgresql.org/docs/8.3/interactive/app-pg-ctl.html
-Kevin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who's attached to the database?
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
2008-07-03 19:53 ` Re: Who's attached to the database? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-03 20:07 ` Re: Who's attached to the database? Carol Walter <walterc@indiana.edu>
@ 2008-07-04 13:50 ` Lennin Caro <lennin.caro@yahoo.com>
2008-07-04 16:41 ` Re: Who's attached to the database? Tino Schwarze <postgresql@tisc.de>
1 sibling, 1 reply; 12+ messages in thread
From: Lennin Caro @ 2008-07-04 13:50 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
try this
select pg_cancel_backend(<processid_db>);
if the command not work kill the process in os
kill -9 proccessid_os
--- On Thu, 7/3/08, Carol Walter <walterc@indiana.edu> wrote:
> From: Carol Walter <walterc@indiana.edu>
> Subject: Re: [ADMIN] Who's attached to the database?
> To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
> Cc: pgsql-admin@postgresql.org
> Date: Thursday, July 3, 2008, 8:07 PM
> Thanks, guys,
>
> This told me that the user that has it open is the
> interface user
> called db_user. Is there a command to disconnect this
> user?
>
> Carol
>
> On Jul 3, 2008, at 3:53 PM, Kevin Grittner wrote:
>
> >>>> Carol Walter <walterc@indiana.edu>
> wrote:
> >> I'm trying to drop a database and I'm
> getting an error that says that
> >
> >> the database is being accessed by other users. Is
> there a way I can
> >
> >> find out who these users are or if there really is
> a user accessing
> > it?
> >
> > select * from pg_stat_activity where datname =
> 'yourdb';
> >
> > -Kevin
>
>
> --
> Sent via pgsql-admin mailing list
> (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who's attached to the database?
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
2008-07-03 19:53 ` Re: Who's attached to the database? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-03 20:07 ` Re: Who's attached to the database? Carol Walter <walterc@indiana.edu>
2008-07-04 13:50 ` Re: Who's attached to the database? Lennin Caro <lennin.caro@yahoo.com>
@ 2008-07-04 16:41 ` Tino Schwarze <postgresql@tisc.de>
0 siblings, 0 replies; 12+ messages in thread
From: Tino Schwarze @ 2008-07-04 16:41 UTC (permalink / raw)
To: pgsql-admin
On Fri, Jul 04, 2008 at 06:50:43AM -0700, Lennin Caro wrote:
> try this
>
> select pg_cancel_backend(<processid_db>);
>
> if the command not work kill the process in os
>
> kill -9 proccessid_os
*NO*! Never kill -9 a postgres backend!!! This is like a headshot, no
chance to clean up. You will make your whole pg server shut down!
A simple kill should suffice.
Tino.
--
"What we nourish flourishes." - "Was wir nähren erblüht."
www.craniosacralzentrum.de
www.forteego.de
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who's attached to the database?
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
@ 2008-07-03 19:54 ` Tom Lane <tgl@sss.pgh.pa.us>
4 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2008-07-03 19:54 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
Carol Walter <walterc@indiana.edu> writes:
> I'm trying to drop a database and I'm getting an error that says that
> the database is being accessed by other users. Is there a way I can
> find out who these users are or if there really is a user accessing it?
pg_stat_activity ... or if you turned that off, "ps auxww | grep postgres"
(or local equivalent) might help.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who's attached to the database?
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
@ 2008-07-03 19:55 ` Jeff Frost <jeff@frostconsultingllc.com>
4 siblings, 0 replies; 12+ messages in thread
From: Jeff Frost @ 2008-07-03 19:55 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
Carol Walter wrote:
> I'm trying to drop a database and I'm getting an error that says that
> the database is being accessed by other users. Is there a way I can
> find out who these users are or if there really is a user accessing it?
Carol, depending on version and options turned on in the
postgresql.conf, you probably can have a look in the pg_stat_activity
system view:
SELECT datname, client_addr FROM pg_stat_activity WHERE datname = '<db
you're trying to drop>';
Otherwise, you can also have a look at the command line on linux servers
like this:
ps -ef|grep postgres
or
ps aux | grep postgres
depending what ps variant your system includes.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who's attached to the database?
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
@ 2008-07-03 19:55 ` paul socha <pawel.socha@gmail.com>
4 siblings, 0 replies; 12+ messages in thread
From: paul socha @ 2008-07-03 19:55 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
On 2008-07-03, at 21:45, Carol Walter wrote:
> Hi, Guys,
>
> I'm trying to drop a database and I'm getting an error that says
> that the database is being accessed by other users. Is there a way
> I can find out who these users are or if there really is a user
> accessing it?
>
> Carol
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
select * from pg_stat_activity where datname='<database2drop>';
--
Pawel Socha
pawel.socha@gmail.com
perl -le 's**02).4^&-%2,).^9%4^!./4(%2^3,!#+7!2%^53%2&**y%& -;^[%"`-
{ a%%s%%$_%ee'
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Who's attached to the database?
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
@ 2008-07-04 13:47 ` Lennin Caro <lennin.caro@yahoo.com>
4 siblings, 0 replies; 12+ messages in thread
From: Lennin Caro @ 2008-07-04 13:47 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, Carol Walter <walterc@indiana.edu>
ps auxw | grep postgres
list the user and some time the process in execution
--- On Thu, 7/3/08, Carol Walter <walterc@indiana.edu> wrote:
> From: Carol Walter <walterc@indiana.edu>
> Subject: [ADMIN] Who's attached to the database?
> To: pgsql-admin@postgresql.org
> Date: Thursday, July 3, 2008, 7:45 PM
> Hi, Guys,
>
> I'm trying to drop a database and I'm getting an
> error that says that
> the database is being accessed by other users. Is there a
> way I can
> find out who these users are or if there really is a user
> accessing it?
>
> Carol
>
> --
> Sent via pgsql-admin mailing list
> (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2008-07-04 16:41 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-03 19:45 Who's attached to the database? Carol Walter <walterc@indiana.edu>
2008-07-03 19:53 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-03 20:07 ` Carol Walter <walterc@indiana.edu>
2008-07-03 21:17 ` Bricklen Anderson <banderson@presinet.com>
2008-07-03 21:23 ` Carol Walter <walterc@indiana.edu>
2008-07-03 22:04 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-07-04 13:50 ` Lennin Caro <lennin.caro@yahoo.com>
2008-07-04 16:41 ` Tino Schwarze <postgresql@tisc.de>
2008-07-03 19:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-03 19:55 ` Jeff Frost <jeff@frostconsultingllc.com>
2008-07-03 19:55 ` paul socha <pawel.socha@gmail.com>
2008-07-04 13:47 ` Lennin Caro <lennin.caro@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox