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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Antti Salmela <asalmela (AT) iki (DOT) fi> writes:
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 ...

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

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Thu, Nov 03, 2005 at 10:03:16AM -0500, Tom Lane wrote:
PANIC'ed.

Now I know better..

Thanks.

--
Antti Salmela

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq