Table des matières

Monitoring base de donnée PostgreSQL

Par défaut (pour les versions antérieures a la version 10.X, donc 9.X et avant), il n'existe pas encore de role pg_monitor, ce qui “oblige” votre monitoring à être effectué avec les droits d'admin.

Comme on est des gens sérieux et qu'on ne veut pas que le monitoring puisse dropper des bases de prod on va faire ca mieux ;)

Merci a https://github.com/wrouesnel/postgres_exporter#running-as-non-superuser

L'idée est la suivante :

Pour collecter des metriques de pg_stat_activity et pg_stat_replication en tant qu'ilisateur non superadmin, on va créer des fonctions et vues associées (en tant que superadmin), et on va pouvoir ajouter les permissions séparément pour un utilisateur non privilégié.

Créer tout ce qu'il faut

En tant que superadmin on va donc créer les fonctions, vues, utilisateur non privilégié et droits associés :

  1. Creation de l'utilisateur spécifique au monitoring (le lecteur averti voudra surement changer le mot de passe :p) :
    CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$
    BEGIN
      IF NOT EXISTS (
              SELECT                       -- SELECT list can stay empty for this
              FROM   pg_catalog.pg_user
              WHERE  usename = 'postgres_monitoring') THEN
        CREATE USER postgres_monitoring;
      END IF;
    END;
    $$ language plpgsql;
    
    SELECT __tmp_create_user();
    DROP FUNCTION __tmp_create_user();
    
    ALTER USER postgres_exporter WITH PASSWORD 'password';
    ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_monitoring,pg_catalog;
  2. Créer le schéma spécifique au monitoring :
    CREATE SCHEMA IF NOT EXISTS postgres_monitoring;
    GRANT USAGE ON SCHEMA postgres_monitoring TO postgres_monitoring;
    GRANT CONNECT ON DATABASE postgres TO postgres_monitoring;
  3. Créer les fonctions et vues associées du schéma spécifique au monitoring (pour l'instant juste pg_stat_activity et pg_stat_replication : on voudra certainement en ajouter d'autres plus tard sur le même principe) :
    CREATE OR REPLACE FUNCTION get_pg_stat_activity() RETURNS SETOF pg_stat_activity AS
    $$ SELECT * FROM pg_catalog.pg_stat_activity; $$
    LANGUAGE sql
    VOLATILE
    SECURITY DEFINER;
    
    CREATE OR REPLACE VIEW postgres_monitoring.pg_stat_activity
    AS
      SELECT * from get_pg_stat_activity();
    
    GRANT SELECT ON postgres_monitoring.pg_stat_activity TO postgres_monitoring;
    
    CREATE OR REPLACE FUNCTION get_pg_stat_replication() RETURNS SETOF pg_stat_replication AS
    $$ SELECT * FROM pg_catalog.pg_stat_replication; $$
    LANGUAGE sql
    VOLATILE
    SECURITY DEFINER;
    
    CREATE OR REPLACE VIEW postgres_monitoring.pg_stat_replication
    AS
      SELECT * FROM get_pg_stat_replication();
    
    GRANT SELECT ON postgres_monitoring.pg_stat_replication TO postgres_monitoring;
  4. Pensez a autoriser cette nouvelle connexion utilisateur dans le fichier pg_hba.conf, par exemple en y ajoutant la ligne suivante, limitée a vos serveurs de monitoring par exemple :
    host    postgres      postgres_monitoring      192.168.16.151/32            md5
  5. Il ne reste plus qu'a recharger ce fichier, par exemple avec un service postgresql reload (adapter le nom au besoin) ou encore lui envoyer un signal HUP
Dans le cas d'un cluster de serveur PostgreSQL répliqués, il faut bien sur lancer les commandes de création de l'utilisateur/fonctions/vues/schéma sur le serveur maître, les esclaves étant en lecture seule : il ne vous restera que les acces a autoriser dans le fichier pg_hba.conf

Ajouter le monitoring associé

Notre nouvel utilisateur postgres_monitoring va donc maintenant etre capable de lancer certaines commandes sur la base postgres, par exemple :

SELECT * FROM pg_stat_database WHERE datname = 'maBase'

ou encore

SELECT * FROM pg_stat_activity ;

Le reste dépend de votre monitoring en place, et de ce que vous souhaitez mettre dedans

Autre monitoring

Délai de réplication d'un esclave

Pour obtenir le délai de réplication (en secondes) entre un esclave et son maitre, a lancer sur l'esclave :

SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay;