Re: databases/pg_statsinfo, databases/pg_stats_reporter

Le Tue, May 28, 2024 at 09:13:27PM +0100, Stuart Henderson a écrit :
> 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.

well, made a bit more progress, the 'repository1' database (mentioned in
/etc/pg_stats_reporter.ini) needs to be created, but then at startup the
log is filled with

May 29 08:21:18 c64 postgres: [179-1] 2024-05-29 08:21:18 CEST 36385 - (pg_statsinfod, , , pg_statsinfod) ERROR: pg_statsinfo: query failed: ERROR: could not open file "/proc/meminfo":
May 29 08:21:18 c64 postgres: [179-2] 2024-05-29 08:21:18 CEST 36385 - (pg_statsinfod, , , pg_statsinfod) DETAIL: query was: SELECT mem_total FROM statsinfo.meminfo()
May 29 08:21:18 c64 postgres: [180-1] 2024-05-29 08:21:18 CEST 36385 - (pg_statsinfod, , , pg_statsinfod) ERROR: pg_statsinfo: query failed: ERROR: could not open file "/proc/cpuinfo":
May 29 08:21:18 c64 postgres: [180-2] 2024-05-29 08:21:18 CEST 36385 - (pg_statsinfod, , , pg_statsinfod) DETAIL: query was: SELECT vendor_id, model_name, cpu_mhz, processors, threads_per_core, cores_per_socket, sockets FROM statsinfo.cpuinfo()

i havent checked if the same code was already present in the version
currently in-tree, but ... yuck.

https://github.com/ossc-db/pg_statsinfo/blob/main/agent/lib/libstatsinfo.c#L2420

Landry