Unused files in the database directory after crashed VACUUM FULL

From: Hannes Erven <hannes(at)erven(dot)at>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Unused files in the database directory after crashed VACUUM FULL
Date: 2019-02-10 08:19:30
Message-ID: da60fd67-f660-39f2-764f-c5792ca568e8@erven.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I've just had a "VACUUM FULL <table>" crash due to 100% disk usage.
Clearly my fault, I was expecting the new table to be small enough.

After freeing up space, restarting the cluster and issuing another
VACCUM FULL, I noticed that the cluster was way bigger that it should be.
In the base/<db>/ folder, there was a large number of files with one
certain number that pg_filenode_relation() could not turn into a
relation. As that number was just a bit smaller that the
pg_relation_filepath() of the table I was working on, I guess these were
the remains of the failed VACUUM FULL operation?
I removed those files and a VACCUM ANALYZE of the whole database went fine.

So... is this the expected behaviour? Is there a safe procedure how to
check/clean up "unnecessary" files in the cluster directory?

Version:
('PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit')

Table definition:
(
id serial PRIMARY KEY,
data bytea,
parent_id integer NOT NULL
)
The "data" column values are large enough to be TOASTed.

Thanks & best regards,

-hannes

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2019-02-10 15:41:08 Re: Unused files in the database directory after crashed VACUUM FULL
Previous Message rob stone 2019-02-10 04:15:38 Re: Server goes to Recovery Mode when run a SQL