Orphan table files at data/base/

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: