For some reason a cluster was missing a whole bunch of databases – the underlying files were simply gone. I wanted to drop the databases from the catalog to avoid errors that were happening while trying to run queries against pg_database, e.g.:

postgres=# SELECT datname, pg_database_size(datname) FROM pg_database;
psql:sql/db_size:1: ERROR: database with OID 1221713 does not exist

Several of them were fine until I hit:

postgres=# drop database template_postgis;
ERROR: cannot drop a template database

The template_postgis database is a template created by the postgis extension, which isn’t even installed on this server. PostgreSQL will not let you drop template databases, but it turns out I can change the database to a normal one by updating the pg_database catalog table.

postgres=# select oid, datname, datistemplate from pg_database;
oid      | datname          | datistemplate
---------+------------------+---------------
       1 | template1        | t
   11510 | template0        | t
   11511 | postgres         | f
 1221713 | template_postgis | t

postgres=# update pg_database set datistemplate = false where datname = 'template_postgis';
UPDATE 1

postgres=# select oid, datname, datistemplate from pg_database;
oid      | datname          | datistemplate
---------+------------------+---------------
       1 | template1        | t
   11510 | template0        | t
   11511 | postgres         | f
 1221713 | template_postgis | f

postgres=# drop database template_postgis;
DROP DATABASE

Now with all the remnants of missing databases gone, my original query works fine.

postgres=# SELECT datname, pg_database_size(datname) FROM pg_database;
datname    | pg_database_size
-----------+------------------
 template1 | 4448260
 template0 | 4456452
 postgres  | 4591212

Additionally on this same server, when trying to query some tablespace information I got the error:

postgres=# select spcname, pg_tablespace_size(oid) from pg_tablespace;
ERROR: could not open tablespace directory "pg_tblspc/2074496": No such file or directory

Sure enough, there are three tablespace links under $PGDATA/pg_tblspc which are broken. I just want to remove the tablespaces as they are old definitions and not used any more.

postgres=# drop tablespace ts_gonsky;
WARNING: could not open directory "pg_tblspc/2074495": No such file or directory
DROP TABLESPACE

Just a warning – it removed the definition ok and my queries work now. However, I needed to manually drop the sym links on the file system.