Postgresql – Dropped database still shows but causes errors PostgreSQL

postgresqlpsql

I had dropped a database a couple weeks ago but it still shows with \l. I've tried using DROP DATABASE mixmobi_master; and the first time it gave me a confirmation message but mixmobi_master was still showing up with \l. Now when I try that command again I get:

template1=# \l

template1=# drop database mixmobi_master;
ERROR:  database "mixmobi_master" does not exist

template1=# \l
                                 List of databases
      Name           | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-------------------------+-------+----------+-------------+-------------+-------------------
biterator_development   | kyle  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
mixmobi_master          | kyle  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
plantjotter_development | kyle  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
postgres                | atmos | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
template0               | atmos | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/atmos         +
                        |       |          |             |             | atmos=CTc/atmos
template1               | atmos | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/atmos         +
                        |       |          |             |             | atmos=CTc/atmos
(6 rows)

Every couple of minutes or so it will also give me this error message:

FATAL:  database "mixmobi_master" does not exist
DETAIL:  It seems to have just been dropped or renamed.

Anyone have suggestions? I'm running Lion and had used Atmos' Cinderella environment.

Edit

I used @Erwin's suggestion and got this:

template1=# SELECT * FROM pg_stat_database WHERE datname = 'mixmobi_master';

datid |    datname     | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts |          stats_reset          
-------+----------------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+-------------------------------
16387 | mixmobi_master |           0 |           9 |          2118 |       262 |     1826 |         2181 |        1157 |            0 |           0 |           0 |         0 | 2012-01-24 11:47:34.692079-06
(1 row)

template1=# SELECT pg_terminate_backend(procpid)
template1-# FROM   pg_stat_activity
template1-# WHERE  datname = 'mixmobi_master';
pg_terminate_backend 
----------------------
(0 rows)

Does anyone else have any suggestions? I'm still getting these errors and nothing has worked so far! Thanks in advance for your time!

Best Answer

I would guess you had open connections / transactions while you dropped the db initially and the DROP is stalled somehow.

You can get more info on databases with statistics views. For instance:

SELECT * FROM pg_stat_database WHERE datname = 'mixmobi_master';

In case you find open backend processes, you might want to shut them down like this:

SELECT pg_terminate_backend(procpid)
FROM   pg_stat_activity
WHERE  datname = 'mixmobi_master';

More about that in the manual here.

Find a safe method to drop a database under load at this related answer:

Related Question