Re: pg_stat_tmp

On Mon, Dec 16, 2013 at 5:57 AM, Tim Kane <tim(dot)kane(at)gmail(dot)com> wrote:

> Hi all,
>
> The past few days I’ve been encountering the following error, followed by
> a full db restart and recovery
>
>
> 2013-12-16 07:12:53 GMT LOG: could not write temporary statistics file
> "pg_stat_tmp/pgstat.tmp": No space left on device
>

Is that the only thing in the logs? pg_stat_tmp problems should not bring
down your database. But problems with pg_xlog running out of space
certainly can--but they should also be logged.

> This occurs at a time of moderate load, during the same set of operations
> each morning.
> Interestingly, when I execute this manually at any other time of date, the
> process completes normally.
>
> I presume that the *pg_stat_tmp* location is system-wide and likely is
> not impacted by *temp_tablespaces*
> The root partition, where postgresql is installed does *not* have a lot
> of disk available (4GB).
>
> My first instinct here is to symlink *pg_stat_tmp* against another disk
> with a little more room to breathe, however I’m surprised that pgstat.tmp
> would grow to be so large in the first place – possibly there is something
> else at play here.
>

We don't know how large it is getting! If pg_stat_tmp shares the same
partition as pg_xlog, base (as in the default configuration), and pg_log,
then any of those things could be filling up the partition, and pg_stat_tmp
could just be the canary, not the culprit.

Anyway, you don't need to use a symlink, you could just change
stats_temp_directory to point someplace else.

Your best bet is run "du" or something similar to figure out where your
space is actually going.

Cheers,

Jeff