Thread: Cannot drop database that is in use (NOT)

Cannot drop database that is in use (NOT)

From
Gary Stainburn
Date:
Hi all,

I'm developing a database and have a small shell script that drops the
database, creates it, and then feeds psql to create the tables etc.

I've tried to run it and get the following error:
[revcom@curly revcom]$ recreate
ERROR:  DROP DATABASE: Database "revcom" is being accessed by other users
dropdb: database removal failed
ERROR:  CREATE DATABASE: database "revcom" already exists
createdb: database creation failed
ERROR:  Relation 'teams_tid_seq' already exists
ERROR:  Relation 'members_mid_seq' already exists
blah blah....

I know that there is nobody using the database, so I can only assume it is
some left-over garbage from my first every php script (as I don't know how to
close the link openned by pg_pconnect).

How do I close any pending connections and reset the usage count so that I
can drop the database?

--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000

Re: Cannot drop database that is in use (NOT)

From
Peter Eisentraut
Date:
Gary Stainburn writes:

> ERROR:  DROP DATABASE: Database "revcom" is being accessed by other users

> I know that there is nobody using the database, so I can only assume it is
> some left-over garbage from my first every php script (as I don't know how to
> close the link openned by pg_pconnect).
>
> How do I close any pending connections and reset the usage count so that I
> can drop the database?

Either follow the advice at
http://www.php.net/manual/en/function.pg-pconnect.php or kill the
PostgreSQL backend process.  (Look with ps ax.  With luck you might
identify the process which is using that database from the command line
that ps shows.)  All in all you will find that persistent connections and
volatile databases don't mix well.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Re: Cannot drop database that is in use (NOT)

From
Jie Liang
Date:
what's in your script??


Jie LIANG

St. Bernard Software

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.stbernard.com
www.ipinc.com

On Mon, 2 Jul 2001, Gary Stainburn wrote:

> Hi all,
>
> I'm developing a database and have a small shell script that drops the
> database, creates it, and then feeds psql to create the tables etc.
>
> I've tried to run it and get the following error:
> [revcom@curly revcom]$ recreate
> ERROR:  DROP DATABASE: Database "revcom" is being accessed by other users
> dropdb: database removal failed
> ERROR:  CREATE DATABASE: database "revcom" already exists
> createdb: database creation failed
> ERROR:  Relation 'teams_tid_seq' already exists
> ERROR:  Relation 'members_mid_seq' already exists
> blah blah....
>
> I know that there is nobody using the database, so I can only assume it is
> some left-over garbage from my first every php script (as I don't know how to
> close the link openned by pg_pconnect).
>
> How do I close any pending connections and reset the usage count so that I
> can drop the database?
>
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Re: Cannot drop database that is in use (NOT)

From
Gary Stainburn
Date:
On Monday 02 July 2001  7:46 pm, Jie Liang wrote:
> what's in your script??
>

[revcom@curly revcom]$ more bin/recreate
#!/bin/bash

dropdb revcom
createdb revcom
psql <schema.sql

Doing 'apactrl graceful' did the trick, but a way where I don't need to gain
access to the web server would be nice.

Gary
>
> Jie LIANG
>
> St. Bernard Software
>
> 10350 Science Center Drive
> Suite 100, San Diego, CA 92121
> Office:(858)320-4873
>
> jliang@ipinc.com
> www.stbernard.com
> www.ipinc.com
>
> On Mon, 2 Jul 2001, Gary Stainburn wrote:
> > Hi all,
> >
> > I'm developing a database and have a small shell script that drops the
> > database, creates it, and then feeds psql to create the tables etc.
> >
> > I've tried to run it and get the following error:
> > [revcom@curly revcom]$ recreate
> > ERROR:  DROP DATABASE: Database "revcom" is being accessed by other users
> > dropdb: database removal failed
> > ERROR:  CREATE DATABASE: database "revcom" already exists
> > createdb: database creation failed
> > ERROR:  Relation 'teams_tid_seq' already exists
> > ERROR:  Relation 'members_mid_seq' already exists
> > blah blah....
> >
> > I know that there is nobody using the database, so I can only assume it
> > is some left-over garbage from my first every php script (as I don't know
> > how to close the link openned by pg_pconnect).
> >
> > How do I close any pending connections and reset the usage count so that
> > I can drop the database?
> >
> > --
> > Gary Stainburn
> >
> > This email does not contain private or confidential material as it
> > may be snooped on by interested government parties for unknown
> > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster

--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000

Re: Cannot drop database that is in use (NOT)

From
Jie Liang
Date:
I am not sure that ERROR is a misleading or not.
try this:
1.become user postgres by typing: su postgres
2.ensure no other user access revcom by typing: ps -aux|grep postgres
  (no other postgres process except postmaster)
3.dropdb revcom
4.createdb revcom

if OK, your script should no problem

Jie LIANG

St. Bernard Software

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.stbernard.com
www.ipinc.com

On Tue, 3 Jul 2001, Gary Stainburn wrote:

> On Monday 02 July 2001  7:46 pm, Jie Liang wrote:
> > what's in your script??
> >
>
> [revcom@curly revcom]$ more bin/recreate
> #!/bin/bash
>
> dropdb revcom
> createdb revcom
> psql <schema.sql
>
> Doing 'apactrl graceful' did the trick, but a way where I don't need to gain
> access to the web server would be nice.
>
> Gary
> >
> > Jie LIANG
> >
> > St. Bernard Software
> >
> > 10350 Science Center Drive
> > Suite 100, San Diego, CA 92121
> > Office:(858)320-4873
> >
> > jliang@ipinc.com
> > www.stbernard.com
> > www.ipinc.com
> >
> > On Mon, 2 Jul 2001, Gary Stainburn wrote:
> > > Hi all,
> > >
> > > I'm developing a database and have a small shell script that drops the
> > > database, creates it, and then feeds psql to create the tables etc.
> > >
> > > I've tried to run it and get the following error:
> > > [revcom@curly revcom]$ recreate
> > > ERROR:  DROP DATABASE: Database "revcom" is being accessed by other users
> > > dropdb: database removal failed
> > > ERROR:  CREATE DATABASE: database "revcom" already exists
> > > createdb: database creation failed
> > > ERROR:  Relation 'teams_tid_seq' already exists
> > > ERROR:  Relation 'members_mid_seq' already exists
> > > blah blah....
> > >
> > > I know that there is nobody using the database, so I can only assume it
> > > is some left-over garbage from my first every php script (as I don't know
> > > how to close the link openned by pg_pconnect).
> > >
> > > How do I close any pending connections and reset the usage count so that
> > > I can drop the database?
> > >
> > > --
> > > Gary Stainburn
> > >
> > > This email does not contain private or confidential material as it
> > > may be snooped on by interested government parties for unknown
> > > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
>
> --
> Gary Stainburn
>
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>