Обсуждение: Who's attached to the database?
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
>>> 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
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
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
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'
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
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>);
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>);
>>> 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
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
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
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