To: Matthew Wakeling <matthew@flymine.org>
cc: pgsql-performance@postgresql.org
Subject: Re: Disc space usage 
In-reply-to: <alpine.DEB.1.10.0810081455070.15851@aragorn.flymine.org> 
References: <alpine.DEB.1.10.0810081238380.15851@aragorn.flymine.org>
	<27956.1223471810@sss.pgh.pa.us>
	<alpine.DEB.1.10.0810081455070.15851@aragorn.flymine.org>
Comments: In-reply-to Matthew Wakeling <matthew@flymine.org>
	message dated "Wed, 08 Oct 2008 15:00:24 +0100"
Date: Wed, 08 Oct 2008 10:38:01 -0400
Message-ID: <29539.1223476681@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Matthew Wakeling <matthew@flymine.org> writes:
> Speaking to some of my colleagues, sometimes the createdb process fails 
> with a very specific error message. If we wait five seconds and try again, 
> then it succeeds. So, maybe the duff directories are from those failures.

> The error message is always something like this:

> createdb: database creation failed: ERROR:  could not stat file "base/32285287/32687035": No such file or directory

> Just before running createdb, we always have some quite heavy write 
> traffic.

Hmm, would that include dropping tables in the database you are about to
copy?  If so, this error is fairly readily explainable as a side effect
of the delayed dropping of physical files in recent PG versions.

(As noted in the manual, CREATE DATABASE isn't really intended as a COPY
DATABASE operation --- it is expecting the source database to be pretty
static.  I think you could make this more reliable if you do a manual
checkpoint between modifying the source database and copying it.)

However, that still leaves me wondering why the leftover copied
directories stick around.  If the copying step failed that way,
CREATE DATABASE *should* try to clean up the target tree before
exiting.  And AFAICS it wouldn't even report the error until after
completing that cleanup.  So there's still some piece of the puzzle
that's missing.

Do you have some specific examples of this error message at hand?
Can you try to confirm whether the reported path corresponds to
something in the CREATE's source database?  If it's actually
complaining about a stat failure in the target tree, then there's
something else going on altogether.  I don't see anything in that
path that would give this message, but I might be missing it.

			regards, tom lane