Re: Orphan table files at data/base/

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(dot)albe(at)cybertec(dot)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