From: Stuart Henderson <stu@spacehopper.org>
Subject: Re: databases/pg_statsinfo, databases/pg_stats_reporter
To: Landry Breuil <landry@openbsd.org>
Cc: ports <ports@openbsd.org>
Date: Tue, 28 May 2024 21:13:27 +0100

On 2024/05/28 21:32, Landry Breuil wrote:
> Le Tue, May 28, 2024 at 02:34:17PM +0100, Stuart Henderson a écrit :
> > databases/pg_statsinfo includes a program and loadable module for
> > postgresql that's used to collect server stats. It seems to be fairly
> > specific to postgresql major versions but hasn't been updated since
> > 13.0. I've tried updating to 16.x (diff below) but am not getting
> > far in testing - after adding the suggested lines to postgresql.conf
> > and running the pg_statsinfo binary with various options I get errors
> > like
> > 
> > ERROR: query failed: ERROR:  schema "statsinfo" does not exist
> > 
> > 
> > databases/pg_stats_reporter is a PHP program taking data from the
> > above. The version in-tree is for PHP 7.4 only. Also it looks like it
> > should probably be tied to the pg_statsinfo version; currently it's
> > a much older 3.2.1.
> > 
> > 
> > Is anyone currently using one or both of these ports?
> > If not, I wonder if they should be removed.
> > if yes, does the update work?
> 
> well, i've had to:
> - create /var/run/pg_statsinfo (hardcoded in source) owned by _postgres, it creates a pidfile there
> - start the db with all the lines added to postgresql.conf

Got those, /var/run/pg_statsinfo seemed the best place to use instead of
upstream's /run for Linux (per-user), but it's unfortunately hard to add
via an rc-script because it needs creating before postgresql starts.
It's a fixed filename (/some/path/$pgsql_port.pid) so /tmp wouldn't be
nice at all.

I suppose it could be patched to use /var/postgresql/pg_statsinfo,
maybe that's the least worst option. Would be better in a dir cleaned
at boot though, if a previous instance died uncleanly it could lose
the pid reuse lottery and kill something random.

This is how it tries to protect the file.

        /* automatic removal of the lock file at exit */
        atexit(unlink_lock_file);

wish I hadn't looked!

> - \i /usr/local/share/postgresql/contrib/pg_statsinfo.sql

that's what I missed, thanks.

> 
> with that i had a statsinfo schema.
> 
> for pg_stats_reporter i had to copy
> /var/www/htdocs/pg_stats_reporter/doc/files/pg_stats_reporter.ini.sample
> to ~/.pg_stats_reporter.ini and set the database host, but even with
> that i cant get much out of pg_stats_reporter command. As a php script,
> it runs with 8.1 or 8.2 so for that part at least it 'works'.
> 
> i havent tried the 'online dashboard' variant, i feel dirty enough for
> now :)
>