Re: pg_stat_tmp

On Mon, Dec 16, 2013 at 2:57 PM, 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
>
>
> 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.
>
> I should note that this issue has only recently occurred, no major changes
> have been introduced.
>
> Thanks for any advice on this.
>
>
You can control the location of the file with the parameter
stats_temp_directory.

The size is most likely because the directory needs space for >1 copy of
the file, since it's written to a separate file and then renamed()d over
the existing one for atomicity. That said, even with that it shouldn't grow
*that* large, unless you've go a huge number of tables. Certainly not into
gigabyte range. So it could be that it's just the first indicator that
fires, but that there's actually something else using up your space. But
setting stats_temp_directory is a good first way to find that out.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/