[Pkg-postgresql-public] Bug#739276: postgresql: Put pgstat in /var/run

[Martin Pitt]

Hello Christian,

Christian Tacke [2014-02-17 13:14 +0100]:
> PGDATA/pg_stat_tmp/* is written to a lot, even on a
> completely idle system. This is not good if running on an
> SSD or alike. That's for my motivation.
> 
> This directory is only needed at runtime, so putting it in
> /var/run seems more appropiate also. The manual even
> recommends using a RAM based filesystem for this directory.

Indeed, this is recommended here:
http://www.postgresql.org/docs/8.4/static/runtime-config-statistics.html

I think configuring a tmpfs dir by default is a good idea, thanks for
pointing that out.

> === modified file 'createcluster.conf'
> --- createcluster.conf	2013-04-12 12:32:08 +0000
> +++ createcluster.conf	2014-02-17 11:26:47 +0000
> @@ -23,3 +23,6 @@
>  # All other options are copied into the new cluster's postgresql.conf
>  
>  log_line_prefix = '%%t '
> +
> +# Starting at version 8.4 one can move the pg_stat_tmp dir
> +stats_temp_directory = '/var/run/postgresql/%v-%c_stat_tmp'

Note that this is a little too simplistic. This directory is only
writable to clusters owned by postgres. While that is the default, you
can assign any other owner, and you can even call pg_createcluster as
a normal user and put everything into your $HOME; in those cases,
/tmp/ will be used for the Unix socket, and we can't use
/var/run/postgresql/.

One idea is to add a new "%s" macro to the evaluation of
createcluster.conf which expands to the used socket dir, and set
stats_temp_directory = '%s/%v-%c_stat_tmp'. That's a little more
complex, but always ought to work since we know that the socket dir is
writable.

A simpler solution might be to use /var/run/lock/ instead, which is
world-writable (same permissions as /tmp/). It's just a bit of an
abuse as these are not really lock files.

A third option would be to not put this into createcluster.conf, but
dynamically set it up in pg_createcluster; i. e. only postgres-owned
clusters would configure this (more specifically, add the option if
/var/run/postgresql is writable for the owner, so that admins could
put other users into the postgres group).

Christian, Christoph, What do you think?

Thanks,

Martin

-- 
Martin Pitt                        | http://www.piware.de
Ubuntu Developer (www.ubuntu.com)  | Debian Developer  (www.debian.org)
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: Digital signature
URL: <http://lists.alioth.debian.org/pipermail/pkg-postgresql-public/attachments/20140307/ae02778a/attachment.sig>