Monitoring base de donnée PostgreSQL [Mon wheequi a moi]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== Monitoring base de donnée PostgreSQL ======
{{tag>monitoring sysadmin database 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 :
  * avec PostgreSQL, les vues se lancent avec les permissions de l'utilisateur qui les a créées, elles peuvent donc servir de barrières de sécurité.
  * les fonctions correspondantes sont utilisées pour partager ces données avec un utilisateur non superadmin
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 :
  - Creation de l'utilisateur spécifique au monitoring (le lecteur averti voudra surement changer le mot de passe :p) :<code>
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;
</code>
  - Créer le schéma spécifique au monitoring : <code>
CREATE SCHEMA IF NOT EXISTS postgres_monitoring;
GRANT USAGE ON SCHEMA postgres_monitoring TO postgres_monitoring;
GRANT CONNECT ON DATABASE postgres TO postgres_monitoring;
</code>
  - 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) :<code>
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;
</code>
  - 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  : <code>
host    postgres      postgres_monitoring      192.168.16.151/32            md5</code>
  - 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

<note>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</note>

===== Ajouter le monitoring associé =====

Notre nouvel utilisateur postgres_monitoring va donc maintenant etre capable de lancer certaines commandes sur la base postgres, par exemple :<code>
SELECT * FROM pg_stat_database WHERE datname = 'maBase'</code> ou encore <code>SELECT * FROM pg_stat_activity ;</code>

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 : <code>
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;</code>