Re: Inconsitancies in pg_stat_bgwriter and pg_stat_database returned values

On Thu, 17 Sep 2020 08:38:10 +0200 (CEST)
RECHTÉ Marc <marc(dot)rechte(at)meteo(dot)fr> wrote:
[...]
> The full row are different in your example, not just a few fields. It looks
> like mixed values from different instance.
>
> Could you share some more informations about the context and how to reproduce
> it?
>
> Regards,
>
> Hello
>
> Thanks a lot, you got it right: there are 2 instances on this machine and
> they are both configured with:
>
> stats_temp_directory = '/dev/shm'
>
> We some time ago tried, stats_temp_directory = '/dev/shm/instance', but
> PostgreSQL ignored the subdirectory part and kept creating the temp file at
> the FS root. As this is a temporary FS, one cannot in advance create the
> instance subdirectory as it is wiped out between reboots.

Use "/var/run/postgresql/instance_name" and setup systemd tmpfiles to create
the folder automatically. Eg.:

cat <<EOF > /etc/tmpfiles.d/postgresql-stats.conf
# Directory for PostgreSQL temp stat files
d /var/run/postgresql/stats-instance_name 0700 postgres postgres - -
EOF

You can add as many folders as you need.

To take this file in consideration immediately without rebooting the server,
run the following command:

systemd-tmpfiles --create /etc/tmpfiles.d/postgresql-stats.conf

Regards,