To: Matthew Wakeling <matthew@flymine.org>
cc: pgsql-performance@postgresql.org
Subject: Re: Disc space usage 
In-reply-to: <alpine.DEB.1.10.0810081238380.15851@aragorn.flymine.org> 
References: <alpine.DEB.1.10.0810081238380.15851@aragorn.flymine.org>
Comments: In-reply-to Matthew Wakeling <matthew@flymine.org>
	message dated "Wed, 08 Oct 2008 12:55:52 +0100"
Date: Wed, 08 Oct 2008 09:16:50 -0400
Message-ID: <27956.1223471810@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Matthew Wakeling <matthew@flymine.org> writes:
> One of our build servers recently ran out of disc space while trying to 
> copy an entire database. This led me to investigate the database cluster, 
> which is stored on a RAID array with a total size of 1TB. Running a query 
> to list all databases and their sizes did not add up to the amount of 
> space being used by Postgres, so I had a look at the pgsql/base directory. 
> It appears that there are a few large directories that do not correspond 
> to any database. I wonder if these have been left behind accidentally by 
> Postgres.

Anything under $PGDATA/base that doesn't correspond to a live row in
pg_database is junk.  The interesting question is how it got that way,
and in particular how you seem to have managed to have repeated
instances of it.

I gather that you're in the habit of using CREATE DATABASE to copy
large existing databases, so the most likely theory is that these are
leftovers from previous failed copy attempts.  Now CREATE DATABASE
does attempt to clean up if its copying fails, but there are various
ways to break that, for instance hitting control-C partway through the
cleanup phase.  So I'm wondering if maybe that's been done a few times.

What PG version is this, anyway?

			regards, tom lane