解决方法 dropdb: database removal failed: ERROR: database "TDADB" is being accessed by other users

backup/restore TDA database
~#pg_dump TDADB > backup.sql
~#dropdb TDADB
~#createdb TDADB
~#psql TDADB < backup.sql

 

但是在dropdb的时候一直报dropdb: database removal failed: ERROR:  database "TDADB" is being accessed by other users，上网搜了下，

有价值的参考网页

http://www.question-defense.com/2008/11/12/postgres-error-database-dev-is-being-accessed-by-other-users

 

~ # psql -l
could not find a "psql" to execute
could not find a "psql" to execute
                                 List of databases
   Name    |    Owner    | Encoding | Collate | Ctype |      Access privileges     
-----------+-------------+----------+---------+-------+-----------------------------
 TDADB     | root        | UTF8     | en_US   | en_US |
 postgres  | root        | UTF8     | en_US   | en_US |
 template0 | root        | UTF8     | en_US   | en_US | =c/root                    +
           |             |          |         |       | root=CTc/root
 template1 | root        | UTF8     | en_US   | en_US | =c/root                    +
           |             |          |         |       | root=CTc/root
 usboxdb   | usboxdbuser | UTF8     | en_US   | en_US | =Tc/usboxdbuser            +
           |             |          |         |       | usboxdbuser=CTc/usboxdbuser
(5 rows)

/var/log # dropdb TDADB
could not find a "dropdb" to execute
dropdb: database removal failed: ERROR:  database "TDADB" is being accessed by other users
DETAIL:  There are 1 other session(s) using the database.

/var/log # psql -d TDADB
could not find a "psql" to execute
could not find a "psql" to execute
psql (9.1.9)
Type "help" for help.

TDADB=# SELECT * FROM pg_stat_activity;                    
 datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          | waiting |              current_query              
-------+---------+---------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+------------------------------------------
 16384 | TDADB   |   17996 |       10 | root    | psql             |             |                 |          -1 | 2013-04-25 11:55:54.100522+07 | 2013-04-25 11:55:54.1334+07   | 2013-04-25 11:55:54.1334+07   | f       | select tb_geomap_malemail_logs_refresh()
 16384 | TDADB   |   23637 |       10 | root    | psql             |             |                 |          -1 | 2013-04-25 12:04:55.610448+07 | 2013-04-25 12:06:06.278803+07 | 2013-04-25 12:06:06.278803+07 | f       | SELECT * FROM pg_stat_activity;
(2 rows)

TDADB=# \q

kill掉procpid对应的进程后再dropdb

/var/log # kill -9 17996
/var/log # kill -9 23637

/var/log # psql -l

                                 List of databases
   Name    |    Owner    | Encoding | Collate | Ctype |      Access privileges     
-----------+-------------+----------+---------+-------+-----------------------------
 postgres  | root        | UTF8     | en_US   | en_US |
 template0 | root        | UTF8     | en_US   | en_US | =c/root                    +
           |             |          |         |       | root=CTc/root
 template1 | root        | UTF8     | en_US   | en_US | =c/root                    +
           |             |          |         |       | root=CTc/root
 usboxdb   | usboxdbuser | UTF8     | en_US   | en_US | =Tc/usboxdbuser            +
           |             |          |         |       | usboxdbuser=CTc/usboxdbuser
(4 rows)