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@xxxxxxxxxxx>) 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
