Thread: Failed CLUSTER seems to leave files behind

Failed CLUSTER due to insufficient disk space seems to leave temporary files
behind at least on 7.4.7.

I used the following perl script to determine if there is files which do not
have corresponding entry in pg_class and timestamps of these files matched
with failed CLUSTER attempts.

Is it safe to remove these files manually?

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=foo", "postgres", "") or die;

my $sth = $dbh->prepare("SELECT relname FROM pg_class WHERE relfilenode = ?") or die;

foreach(<*>) {
    if(/^\d+$/) {
    $sth->execute($_);
    my ($result) = $sth->fetchrow_array;
    if (! $result) {
        print "$_\n";
        foreach(<$_.*>) {
        print "$_\n";
        }
    }
    }
}

postgres@helium:~/data/base/1045047$ ~as/bin/pg_base.pl | xargs ls -l
-rw-------  1 postgres postgres 1073741824 Sep 18 04:09 32857427
-rw-------  1 postgres postgres 1073741824 Sep 18 04:14 32857427.1
-rw-------  1 postgres postgres 1073741824 Sep 18 04:19 32857427.2
-rw-------  1 postgres postgres  747692032 Sep 18 04:22 32857427.3
-rw-------  1 postgres postgres          0 Sep 18 04:04 32857429
-rw-------  1 postgres postgres       8192 Sep 18 04:04 32857431
-rw-------  1 postgres postgres  529743872 Sep 18 04:28 32857432
-rw-------  1 postgres postgres  529743872 Sep 18 04:39 32857433
-rw-------  1 postgres postgres 1073741824 Sep 18 05:00 32857434
-rw-------  1 postgres postgres 1073741824 Sep 18 05:01 32857434.1
-rw-------  1 postgres postgres  784752640 Sep 18 05:02 32857434.2
-rw-------  1 postgres postgres 1073741824 Oct 23 04:13 32870084
-rw-------  1 postgres postgres 1073741824 Oct 23 04:19 32870084.1
-rw-------  1 postgres postgres 1073741824 Oct 23 04:25 32870084.2
-rw-------  1 postgres postgres 1073741824 Oct 23 04:31 32870084.3
-rw-------  1 postgres postgres   99000320 Oct 23 04:32 32870084.4
-rw-------  1 postgres postgres          0 Oct 23 04:05 32870086
-rw-------  1 postgres postgres       8192 Oct 23 04:05 32870088
-rw-------  1 postgres postgres  584728576 Oct 23 04:38 32870089
-rw-------  1 postgres postgres  584728576 Oct 23 04:51 32870090
-rw-------  1 postgres postgres 1073741824 Oct 23 05:12 32870091
-rw-------  1 postgres postgres 1073741824 Oct 23 05:14 32870091.1
-rw-------  1 postgres postgres  831553536 Oct 23 05:15 32870091.2

--
Antti Salmela

Antti Salmela <asalmela@iki.fi> writes:
> Failed CLUSTER due to insufficient disk space seems to leave temporary files
> behind at least on 7.4.7.

What was the "failure" exactly?  If you ran out of disk space for the
data files, I'd have expected it to reclaim the temp files.  On the
other hand, running out of disk space for WAL would lead to a
database-wide PANIC, and there's no mechanism for getting rid of
unreferenced files after a PANIC.  (IIRC this is intentional ---
deleting files that we think aren't used seems too risky.)

This is one of several reasons for keeping WAL and data on separate
disks ...

> Is it safe to remove these files manually?

Yeah, if you're sure they correspond to no pg_class.relfilenode entry.

            regards, tom lane

On Thu, Nov 03, 2005 at 10:03:16AM -0500, Tom Lane wrote:
> Antti Salmela <asalmela@iki.fi> writes:
> > Failed CLUSTER due to insufficient disk space seems to leave temporary files
> > behind at least on 7.4.7.
>
> What was the "failure" exactly?  If you ran out of disk space for the
> data files, I'd have expected it to reclaim the temp files.  On the
> other hand, running out of disk space for WAL would lead to a
> database-wide PANIC, and there's no mechanism for getting rid of
> unreferenced files after a PANIC.  (IIRC this is intentional ---
> deleting files that we think aren't used seems too risky.)

PANIC'ed.

> This is one of several reasons for keeping WAL and data on separate
> disks ...

Now I know better..

> > Is it safe to remove these files manually?
>
> Yeah, if you're sure they correspond to no pg_class.relfilenode entry.

Thanks.

--
Antti Salmela