Contents of data/base/<oid> 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).
Cheers,
--
Kjetil Jørgensen <kjetijor@gmail.com>
=?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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general