Re: Orphan files filling root partition after crash - Mailing list pgsql-general
From | Dimitrios Apostolou |
---|---|
Subject | Re: Orphan files filling root partition after crash |
Date | |
Msg-id | 5233ff6e-f363-92a4-aa18-7a9392d35ad7@gmx.net Whole thread Raw |
In response to | Re: Orphan files filling root partition after crash (Laurenz Albe <laurenz.albe@cybertec.at>) |
Responses |
Re: Orphan files filling root partition after crash
(Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Orphan files filling root partition after crash (Laurenz Albe <laurenz.albe@cybertec.at>) Re: Orphan files filling root partition after crash ("Peter J. Holzer" <hjp-pgsql@hjp.at>) |
List | pgsql-general |
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 >
pgsql-general by date: