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
more? I see that many (but not all) rows in pg_class have oid=relfilenode
but for many rows relfilenode=0 which is meaningless as filename.
>
>> So what is the moral of the story? How to guard against this?
>
> Monitor disk usage ...
It happened *fast*. And it was quite a big suprise coming
from "just" a disk-full situation.
A couple of suggestions; wouldn't it make sense:
+ for the index to be written by default to the table's tablespace?
+ for postgres to refuse to write non-wal files, if it's on
the same device as the WAL and less than max_wal_size bytes are free?
>
> The root of the problem is that you created the index in the default
> tablespace. You should have
>
> ALTER TABLE x ADD UNIQUE ... USING INDEX TABLESPACE bigtblspc;
>
Thank you, was reading the docs but didn't realize this
syntax is valid. I thought it was only for CREATE/ALTER INDEX.
>> Needless to say, I would have hoped the database cleaned-up after itself
>> even after an uncontrolled crash, or that it provided some tools for the
>> job. (I tried VACUUM FULL on the table, but the orphaned files did not go
>> away).
>
> That is not so simple... Also, it would slow down crash recovery.
>
> But I agree that it would be nice to have a tool that reports or
> cleans up orphaned files.
>
> Yours,
> Laurenz Albe
>