Thread: Contents of data/base/ and no corresponding entry in pg_database
Hi,
(We're running postgres 9.2.X)
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 ?
Additionally, the directory and it's contents haven't been touched in quite a while as well, and some of them do correspond to being touched at the machine having had a hard crash.
The reason I'm asking is that space used as reported by pg_database_size is off by a LOT compared to df on the filesystem (4-5TB as oposed to df/du agreeing on 9-ish TB).
=?UTF-8?Q?Kjetil_J=C3=B8rgensen?= <kjetijor@gmail.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