Обсуждение: Orphan table files at data/base/
I was an unfortunate sequence of commands (all in single transaction) DROP TABLE huge; CREATE TABLE huge AS... (huge PostGIS ST_Union operation); CREATE INDEX ON huge USING GIST (geometry); by a well-meaning user, that caused a crash+recovery: server process (PID 92411) was terminated by signal 9: Killed terminating any other active server processes all server processes terminated; reinitializing database system was not properly shut down; automatic recovery in progress And that left behind 280GB of files (of TABLE huge): data/base/16384/2403959 ... data/base/16384/2403959.282 SELECT pg_filenode_relation(0,2403959); -- returns NULL may I do sudo rm data/base/2403959* ?
On Wed, 2024-02-28 at 15:44 +0200, Riivo Kolka wrote: > I was an unfortunate sequence of commands (all in single transaction) > > DROP TABLE huge; > CREATE TABLE huge AS... (huge PostGIS ST_Union operation); > CREATE INDEX ON huge USING GIST (geometry); > > by a well-meaning user, that caused a crash+recovery: > > server process (PID 92411) was terminated by signal 9: Killed > terminating any other active server processes > all server processes terminated; reinitializing > database system was not properly shut down; automatic recovery in progress > > And that left behind 280GB of files (of TABLE huge): > data/base/16384/2403959 > ... > data/base/16384/2403959.282 > > > SELECT pg_filenode_relation(0,2403959); > -- returns NULL > > may I do > sudo rm data/base/2403959* > ? If you *know* these files belong to the table created with CREATE TABLE huge AS SELECT ... then you can do that. If you are not 100% certain, go the safe way and use dump/restore to a new database. Then DROP DATABASE on the old database, and all orphaned files will be gone. Yours, Laurenz Albe
No, I would not remove those files without making 100% sure they do not belong to that database or any other. Are you sure you are inside database 16384 when you ran those commands? Does a 'stat' on those files line up with the time of the crash? If so, I would stop pg, move the files someplace else, do a pg_dump > /dev/null for another sanity check, then remove those files.
Cheers,
Greg
I *know*, beyond reasonable doubt. Also, I have the luxury of stopping connections temporarily and having a backup ready. I know backups do not include such data. db restored from a backup is ~50GB. I can afford to screw up even. Thanks. Kontakt Laurenz Albe (<laurenz.albe@cybertec.at>) kirjutas kuupäeval K, 28. veebruar 2024 kell 16:30: > > On Wed, 2024-02-28 at 15:44 +0200, Riivo Kolka wrote: > > I was an unfortunate sequence of commands (all in single transaction) > > > > DROP TABLE huge; > > CREATE TABLE huge AS... (huge PostGIS ST_Union operation); > > CREATE INDEX ON huge USING GIST (geometry); > > > > by a well-meaning user, that caused a crash+recovery: > > > > server process (PID 92411) was terminated by signal 9: Killed > > terminating any other active server processes > > all server processes terminated; reinitializing > > database system was not properly shut down; automatic recovery in progress > > > > And that left behind 280GB of files (of TABLE huge): > > data/base/16384/2403959 > > ... > > data/base/16384/2403959.282 > > > > > > SELECT pg_filenode_relation(0,2403959); > > -- returns NULL > > > > may I do > > sudo rm data/base/2403959* > > ? > > If you *know* these files belong to the table created with > CREATE TABLE huge AS SELECT ... > then you can do that. > > If you are not 100% certain, go the safe way and use dump/restore > to a new database. Then DROP DATABASE on the old database, and > all orphaned files will be gone. > > Yours, > Laurenz Albe