Thread: Extra files in "base" dir not seen in relfilenodes

Daniel Farina <daniel@fdr.io> writes:
> I am looking at a database with a wide (~500G) divergence between the total
> space expended by the database directory and the result of select
> sum(pg_relation_size(oid)) from pg_class;.

Odd.

> I located about 280G of apparent extra space by performing an anti-join
> between files on disk and files in the catalog via the pg_class.relfilenode
> field.

Umm ... are you accounting for catalogs that have zeroes in
pg_class.relfilenode?  It's generally better to rely on the
pg_relation_filenode(oid) function than the raw column contents.

            regards, tom lane

Daniel Farina <daniel@fdr.io> writes:
> On Tue, Jan 16, 2018 at 3:04 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Umm ... are you accounting for catalogs that have zeroes in
>> pg_class.relfilenode?  It's generally better to rely on the
>> pg_relation_filenode(oid) function than the raw column contents.

> Yeah, the catalogs are not considered here (oids < 10000). The oids in
> question are rather high. Let me re-run the antijoin with the function
> though....

> Indeed, same result.

Hmm, you should have gotten a result that was different by the size
of the bootstrap catalogs (pg_class, pg_attribute, pg_proc, pg_type,
plus their indexes).  I'm worried that you're going to accidentally
delete those critical catalogs.

However, assuming you've gotten that detail right, then any file
you can't match up with a relfilenode value must be an orphan you
can just "rm".

Do you have any theories about how the DB got like this?  Were there
system crashes or anything like that recently?  Is there any pattern
to the file access or mod dates on the putatively-orphaned files?

            regards, tom lane

Tom Lane wrote:

> However, assuming you've gotten that detail right, then any file
> you can't match up with a relfilenode value must be an orphan you
> can just "rm".

Maybe look in pg_buffercache for entries referencing those files before
deleting.  It would be surprising to see any if no catalog points to
those files, but who knows ...  If you break checkpointing, you're not
going to be pleased.

If it turns out that a shared buffer exists for any of those files, what
would be a way to evict them without pain?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Tom Lane wrote:
>> However, assuming you've gotten that detail right, then any file
>> you can't match up with a relfilenode value must be an orphan you
>> can just "rm".

> Maybe look in pg_buffercache for entries referencing those files before
> deleting.  It would be surprising to see any if no catalog points to
> those files, but who knows ...  If you break checkpointing, you're not
> going to be pleased.

Even if there are any such entries, surely they ought to be clean by
now and thus not a hazard.  Not that a manual "CHECKPOINT" might not
be a good idea.

            regards, tom lane