Changes between Version 2 and Version 9 of Ticket #30028


Ignore:
Timestamp:
Apr 11, 2019, 8:11:27 PM (5 months ago)
Author:
anarcat
Comment:

i deployed the psql exporter by hand on troodi. this required the magic sql injected as the postgres user (sudo -u postgres psql):

  CREATE USER prometheus;
  ALTER USER prometheus SET SEARCH_PATH TO prometheus,pg_catalog;
  
  CREATE SCHEMA prometheus AUTHORIZATION prometheus;
  
  CREATE FUNCTION prometheus.f_select_pg_stat_activity()
  RETURNS setof pg_catalog.pg_stat_activity
  LANGUAGE sql
  SECURITY DEFINER
  AS $$
    SELECT * from pg_catalog.pg_stat_activity;
  $$;
  
  CREATE FUNCTION prometheus.f_select_pg_stat_replication()
  RETURNS setof pg_catalog.pg_stat_replication
  LANGUAGE sql
  SECURITY DEFINER
  AS $$
    SELECT * from pg_catalog.pg_stat_replication;
  $$;
  
  CREATE VIEW prometheus.pg_stat_replication
  AS
    SELECT * FROM prometheus.f_select_pg_stat_replication();
  
  CREATE VIEW prometheus.pg_stat_activity
  AS
    SELECT * FROM prometheus.f_select_pg_stat_activity();
  
  GRANT SELECT ON prometheus.pg_stat_replication TO prometheus;
  GRANT SELECT ON prometheus.pg_stat_activity TO prometheus;

then the following in /etc/default/prometheus-postgres-exporter:

DATA_SOURCE_NAME='user=prometheus host=/run/postgresql dbname=postgres'

Finally, I have deployed the latter through puppet. Remaining steps are to figure out how the heck to load that custom SQL in the server correctly and to deploy the exporter package properly.

There's a postgresql::psql resource which we might use to load the blurb for what it's worth. We might also want to set a password on that user although the README.Debian provided in the exporter say it doesn't really need a password, presumably because its only access are readonly stats.

I've also deployed the most popular psql dashbaord (at the time of writing) in grafana. it provides basic stats and mostly works, but i've asked upstream for other suggestions.

it should also be noted that other debian fellows use the more generic sql exporter to do their magic sql stuff, which means they can deploy the same exporter everywhere, and just need to have the right SQL magic strings in a config file somewhere depending on the server backend. this is, in particular, what the folks at credative are doing with their elephant shed, which provides a grafana dashboard and sql exporter config.

that seems like a reasonable approach we could consider if we want to support mariadb as well in the future, but for now i focused on something that would just work.

Legend:

Unmodified
Added
Removed
Modified
  • Ticket #30028

    • Property Status changed from assigned to closed
    • Property Resolution changed from to fixed
  • Ticket #30028 – Description

    v2 v9  
    1414* [https://github.com/wrouesnel/postgres_exporter/ postgres exporter in debian], [https://github.com/wrouesnel/postgres_exporter/issues/218 no offocial dashboard], but [https://grafana.com/dashboards?dataSource=prometheus&search=postgres many possible dashboards]
    1515* [https://github.com/prometheus/mysqld_exporter mysqld exporter in debian] - [https://grafana.com/dashboards/625 possible dashboard] [https://github.com/percona/grafana-dashboards another from  percona], [https://github.com/prometheus/mysqld_exporter/issues/286 not officially documented]
     16* [https://github.com/free/sql_exporter generic sql exporter], in debian - [https://github.com/credativ/elephant-shed/tree/master/sql-exporter credativ config] and [https://github.com/credativ/elephant-shed/tree/master/grafana dashboard]
    1617
    1718'''DNS / bind'''