Re: Orphan files filling root partition after crash

On 2/28/24 11:30, Dimitrios Apostolou wrote:
> Thanks for the feedback Laurenz,
>
> On Wed, 28 Feb 2024, Laurenz Albe wrote:
>
>> On Wed, 2024-02-28 at 12:16 +0100, Dimitrios Apostolou wrote:
>>>
>>> I ended up doing some risky actions to remediate the problem: Find the
>>> filenames that have no identically named "oid" in pg_class, and delete
>>> (move to backup) the biggest ones while the database is stopped.
>>> Fortunately the database started up fine after that!
>>
>> Lucky you.  It should have been "relfilenode" rather than "oid",
>> and some catalog tables don't have their files listed in the catalog,
>> because they are needed *before* the database can access tables.
>
> I actually double checked that the filenames don't appear anywhere in
> SELECT * FROM pg_class
> and that the files were multi-GB in size including all the
> 1GB-pieces. But luck was definitely a part of the equation, I didn't know
> that the files might be accessed before tables (at db startup?) or that
> "relfilenode" would be more appropriate. Why is that, where can I read

https://www.postgresql.org/docs/current/storage-file-layout.html

Caution

Note that while a table's filenode often matches its OID, this is not
necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER
and some forms of ALTER TABLE, can change the filenode while preserving
the OID. Avoid assuming that filenode and table OID are the same. Also,
for certain system catalogs including pg_class itself,
pg_class.relfilenode contains zero. The actual filenode number of these
catalogs is stored in a lower-level data structure, and can be obtained
using the pg_relation_filenode() function.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com