Re: Contents of data/base/<oid> and no corresponding entry in pg_database

=?UTF-8?Q?Kjetil_J=C3=B8rgensen?= <kjetijor(at)gmail(dot)com> writes:
> from reading
> http://www.postgresql.org/docs/9.2/static/storage-file-layout.html the
> directories under $PG_DATADIR/data/base should correspond to an actual
> database. I've however found a few directories in $PG_DATADIR/data/base
> where select datname from pg_database where oid = <oid> returns 0 rows.

> The machine does have "high churn" on databases and have suffered a few
> crashes, is it possible for things to get left behind in
> $PG_DATADIR/data/base after recovery ? or am I possibly missing things
> still in use, that's reflected elsewhere ?

If there's no row with matching OID in pg_database, then that
subdirectory is unreachable by Postgres. There's not a lot of detail
here, but this situation wouldn't surprise me much if there'd been a
crash during a CREATE or DROP DATABASE operation. We generally aren't
too eager to try to remove "unused" files during crash recovery, because
of the possibility that they might be useful for forensics or manual
data recovery.

Double-check your work (one good check is to verify that nothing inside
the directory has a recent mod date) and then rm -rf those
subdirectories by hand.

regards, tom lane