Postgres Pro
Downloads
Home   >   mailing lists

Thread: Dropped database still shows in the database list.

Dropped database still shows in the database list.

From
girish R G peetle
Date:
Hi,
Dropped database 'RestoreDB' is still being listed in pg_database. 
Any idea why this behavior is seen ? Also I don't see any open connections to this database. 
pg_stat_activity has no rows for this database.

  • postgres=# create database ResotreDB;

    CREATE DATABASE
 

  •  postgres=# select oid, * from pg_database where datname like 'RestoreDB';
oid   |  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
--------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+--------
177109 | RestoreDB |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         12891 |         1800 |          1 |          1663 |
(1 row)
 
 
  • postgres=# drop database RestoreDB;
          DROP DATABASE
 
  • postgres=# select oid, * from pg_database where datname like 'RestoreDB';
oid   |  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
--------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+--------
177109 | RestoreDB |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         12891 |         1800 |          1 |          1663 |
 
 
  • postgres=# create database RestoreDB;
          CREATE DATABASE
 
  • postgres=# select oid, * from pg_database where datname like 'RestoreDB';
oid   |  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
--------+-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+--------
177109 | RestoreDB |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         12891 |         1800 |          1 |          1663 |
(1 row)
 
 
  • postgres=# select * from version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit
(1 row)
 
  • postgres=# select * from pg_stat_activity where datname like 'RestoreDB';
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state | query
-------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+------------+-------------+--------------+---------+-------+-------
(0 rows)

Thanks
Girish

Re: Dropped database still shows in the database list.

From
Albe Laurenz
Date:
girish R G peetle wrote:
> Dropped database 'RestoreDB' is still being listed in pg_database.
> Any idea why this behavior is seen ? Also I don't see any open connections to this database.
> pg_stat_activity has no rows for this database.
> 
> *    postgres=# create database ResotreDB;
>     CREATE DATABASE

This is a typo, and it should be "RestoreDB", right?
When reporting problems of this kind, please always copy and paste from the screen,
rather than typing it by hand.  Often typos cause problems like that, and additional
typos in the report make it harder to figure out the cause of the problem.

> *     postgres=# select oid, * from pg_database where datname like 'RestoreDB';
> oid   |  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn |
> datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
> --------+-----------+--------+----------+-------------+-------------+---------------+--------------+--
> ------------+---------------+--------------+------------+---------------+--------
> 177109 | RestoreDB |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |
> -1 |         12891 |         1800 |          1 |          1663 |
> (1 row)

But this is a different database!

The database you created above (modulo typo) is "restoredb", all in lower case,
because PostgreSQL folds unquoted strings to lower case.

So now, if you run "SELECT datname FROM pg_database;", you should have both
"restoredb" and "RestoreDB" in there.  Is that so?

If you want mixed case, use double quotes:
CREATE DATABASE "RestoreDB";
That statement should fail because the database already exists.

> *    postgres=# drop database RestoreDB;
>           DROP DATABASE

You dropped "restoredb".

> *    postgres=# select oid, * from pg_database where datname like 'RestoreDB';
> oid   |  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn |
> datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
> --------+-----------+--------+----------+-------------+-------------+---------------+--------------+--
> ------------+---------------+--------------+------------+---------------+--------
> 177109 | RestoreDB |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |
> -1 |         12891 |         1800 |          1 |          1663 |

... and "RestoreDB" is still around.

I urge you to only use lower case for all database objects in PostgreSQL.
It will save you a lot of trouble.

Yours,
Laurenz Albe

Re: Dropped database still shows in the database list.

From
"David G. Johnston"
Date:
On Thursday, May 21, 2015, girish R G peetle <giri.anamika0@gmail.com> wrote:
Hi,
Dropped database 'RestoreDB' is still being listed in pg_database. 
Any idea why this behavior is seen ? Also I don't see any open connections to this database. 


Try removing your where clause on the select,

"RestoreDB" and RestoreDB are two different databases.  The later is case-folded to be equivalent to "restoredb".

The double quotes I'm using are significant.

David J.

Re: Dropped database still shows in the database list.

From
girish R G peetle
Date:
Thanks a lot, got it.

Thanks
Girish

On Fri, May 22, 2015 at 1:36 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thursday, May 21, 2015, girish R G peetle <giri.anamika0@gmail.com> wrote:
Hi,
Dropped database 'RestoreDB' is still being listed in pg_database. 
Any idea why this behavior is seen ? Also I don't see any open connections to this database. 


Try removing your where clause on the select,

"RestoreDB" and RestoreDB are two different databases.  The later is case-folded to be equivalent to "restoredb".

The double quotes I'm using are significant.

David J.