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
