Обсуждение: Cannot drop database that is in use (NOT)
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
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
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 >
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
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 >