Utilitaires habituels¶

Utilitaires habituels pour les serveurs PostgreSQL¶

Les outils suivants servent au quotidien des consultants Dalibo et de nombreux DBA.

La plupart sont génériques, parfois installés par défaut.

L’installation peut nécessiter l’installation de dépôts absents par défaut.

Le paramétrage parfois nécessaire est également repris.

Outils génériques¶

Supervision & analyse
- atop
- dd
- fio
- grep
- ioping
- iotop
- ioping
- perf
- ps
- strace, ltrace
- smem
- sysstat: outils sar, sadf, iostat, mpstat, vmstat, pidstat
- vmstat
Exploration de système :
- lsblk
- lscpu
- lshw
- lspci
- lstopo (paquets hwloc*)
Utilitaires pour scripts, édition, administration :
- vim
- tail
- consoles virtuelles : screen / tmux
- rsync
- moreutils : quelques utilitaires, notamment ts
- générateur de mots de passe : pwmake (défaut sous CentOS), pwgen ou apg
- téléchargement : wget (un peu plus pratique que le curl par défaut)
- git (pour récupérer des dépôts d’utilitaires)
Administration
- hugeadm (Huge Pages)
- sysctl

Installation : consignes générales¶

Ce qui suit vise à installer en bloc l’essentiel des outils présentés ci-dessus. Au préalable, une mise à jour des paquets (dnf update ou apt update ; apt upgrade) est potentiellement nécessaire. Certains dépôts annexes peuvent devoir être installés selon la distribution exacte.

Installation : Cent OS 8, Rocky Linux 8 et 9¶

sudo dnf install sysstat tmux rsync vim iotop strace ltrace perf lshw wget lsof hwloc-gui
sudo dnf install git

# EPEL release est un dépôt supplémentaire, pas forcément installé d'office
sudo dnf install epel-release
sudo dnf install screen ioping atop htop
# si pwmake ne convient pas, au choix 
sudo dnf install pwgen apg

# Powertool est un dépôt d'utilitaires supplémentaires.
sudo dnf config-manager --set-enabled powertools
sudo dnf install moreutils

Installation : Debian 10 « buster »/11 « bullseye »/12 « bookworm » et Ubuntu 18.04/20.04/22.04¶

sudo apt update
sudo apt-get install sysstat screen tmux vim rsync atop htop iotop strace ltrace lshw lsof ioping wget moreutils hwloc
sudo apt-get install git
# Sur Debian uniquement :
sudo apt-get install linux-perf
# Sur Ubuntu uniquement :
sudo apt-get install linux-tools-$(uname -r) linux-tools-generic
# hugeadm
sudo apt-get install libhugetlbfs-bin # Debian et Ubuntu >= 20.04
# sudo apt-get install hugeadm  # Ubuntu < 20.04

Cent OS et Rocky Linux 8¶

La collecte n’est pas activée par défaut, il faut demander :

sudo systemctl enable sysstat
sudo systemctl start sysstat

Destination par défaut : /var/log/sa
Options diverses : mettre au moins -S XALL :

/etc/sysconfig/sysstat

HISTORY=28

# Compress (using gzip or bzip2) sa and sar files older than (in days):
COMPRESSAFTER=31

# Parameters for the system activity data collector (see sadc manual page)
# which are used for the generation of log files.
SADC_OPTIONS="-S XALL"   

# Compression program to use.
ZIP="bzip2"

Modifier la fréquence avec :

sudo systemctl edit sysstat-collect.timer

[Timer]
OnCalendar=*:00/5

* Recharger avec :

sudo systemctl daemon-reload

Installation : Cent OS 7¶

sudo yum install sysstat screen tmux rsync vim atop iotop strace ltrace perf lshw lsof ioping wget hwloc-gui
sudo yum install libhugetlbfs-utils
sudo yum install git

sudo yum install epel-release
sudo yum install moreutils atop htop ioping
# si pwmake ne convient pas, au choix 
sudo yum install pwgen apg

sar (sysstat) : configuration¶

Il doit tourner toutes les 5 minutes de préférence.

Ubuntu¶

Il est actif d’entrée. Modifier etc/sysstat/sysstat (voir la partie Debian).

On peut vérifier que sar est actif avec :

sar -q

Debian¶

Il faut modifier un fichier puis activer la collecte dans systemd :

/etc/default/sysstat

ENABLED="true"

sudo systemctl enable sysstat
sudo systemctl start  sysstat

Paramétrage de l’historique et diverses options :

/etc/default/sysstat

# sysstat configuration file. See sysstat(5) manual page.

# How long to keep log files (in days).
# Used by sa2(8) script
# If value is greater than 28, then use sadc's option -D to prevent older
# data files from being overwritten. See sadc(8) and sysstat(5) manual pages.

HISTORY=28

# Compress (using xz, gzip or bzip2) sa and sar files older than (in days):
COMPRESSAFTER=10

# Parameters for the system activity data collector (see sadc(8) manual page)
# which are used for the generation of log files.
# By default contains the `-S DISK' option responsible for generating disk
# statisitcs. Use `-S XALL' to collect all available statistics.
SADC_OPTIONS="-S XALL"

# Directory where sa and sar files are saved. The directory must exist.
SA_DIR=/var/log/sysstat

# Compression program to use.
ZIP="xz"

# By default sa2 script generates yesterday's summary, since the cron job
# usually runs right after midnight. If you want sa2 to generate the summary
# of the same day (for example when cron job runs at 23:53) set this variable.
#YESTERDAY=no

# By default sa2 script generates reports files (the so called sarDD files).
# Set this variable to false to disable reports generation.
#REPORTS=fals

Vérifier que sar est actif avec :

sar -q

CentOS 7 et 8, Rocky Linux 8 et 9¶

La périodicité et le chemin sont définies ici :

/etc/sysconfig/atop

LOGOPTS=""
LOGINTERVAL=600
LOGGENERATIONS=28
LOGPATH=/var/log/atop

Activation :

sudo systemctl enable atop
sudo systemctl start  atop

Cent OS 7¶

Activation et démarrage par systemctl comme pour CentOS/Rocky 8
Adapter /etc/sysconfig/sysstat de la même manière.
Le changement de la planification se fait par contre différemment :

/etc/cron.d/sysstat

#  Changer le /10 en /5 
*/5 * * * * root /usr/lib64/sa/sa1 1 1
# 0 * * * * root /usr/lib64/sa/sa1 600 6 &
# Generate a daily summary of process accounting at 23:53
53 23 * * * root /usr/lib64/sa/sa2 -A

atop : configuration¶

Il doit tourner toutes les 5 minutes de préférence (c’est le défaut sur CentOS et Debian).

Debian 10, 11 et 12, Ubuntu¶

Le service systemd atop est activé par défaut.
S’il faut modifier la périodicité (défaut : 10min), modifier /usr/share/atop/atop.daily et redémarrer le service.

Outils dédiés à PostgreSQL¶

Outils de supervision (confort)
- pg_activity , pg_top
- pgstat (inspiré par vmstat, source)
Outils d’audit
- pgBadger
- pgCluu (uniquement en audit ponctuel ici)
Outils de sauvegarde
- Physique : pgBackrest ou barman
- Logique : pg_back
Sondes compatibles Nagios :
- check_pgactivity
- check_postgres
- check_pgbackrest (surveillance de pgBackrest)

Installation (généralités)¶

Préférer les dépôts du PGDG aux dépôts des distributions, souvent incomplets ou en retard.

Il est parfois nécessaire de remonter au dépôt de l’outil si les paquets sont absents ou trop anciens.

Installation sous CentOS 7¶

Voir le howto de yum.postgresql.org ou cet extrait de nos formations pour les détails pour les dépôts du PGDG.

sudo yum install pg_activity
sudo yum install pg_top
sudo yum install pg_top

sudo yum install nagios-plugins-pgactivity  # sonde dans /usr/lib64/nagios/plugins/check_pgactivity
sudo yum install check_postgres             # sonde dans /usr/bin/check_postgres.pl
sudo dnf install nagios-plugins-pgbackrest  # sonde dans /usr/lib64/nagios/plugins/check_pgbackrest

sudo yum install pgbadger                   
# alternative : le script du dépôt, directement utilisable (prérequis : package '' perl-Text-CSV_XS'')
# sudo yum install perl-Text-CSV_XS
# git clone https://github.com/darold/pgbadger/

sudo yum install pgcluu
# Alternative : prendre la dernière version des scripts dans le dépôt
# git clone  https://github.com/darold/pgcluu

# Outils de sauvegarde, au choix :
sudo yum install pgbackrest        # penser à la sonde check_pgbackrest
sudo yum install barman

pgstat n’est pas packagé : si on y tient, il faut le télécharger et le compiler (hors du sujet de cet article)

Installation CentOS/Rocky 8¶

Voir le howto de yum.postgresql.org ou cet extrait de nos formations pour les détails pour les dépôts du PGDG.

sudo dnf install pg_activity
sudo dnf install pg_top_15  # adapter à la version de PostgreSQL

# Sondes
sudo dnf install nagios-plugins-pgactivity  # sonde dans /usr/lib64/nagios/plugins/check_pgactivity
sudo dnf install check_postgres             # sonde dans /usr/bin/check_postgres.pl
sudo dnf install nagios-plugins-pgbackrest  # sonde dans /usr/lib64/nagios/plugins/check_pgbackrest

sudo dnf install pgbadger                   
# alternative : le script du dépôt, directement utilisable (prérequis : package '' perl-Text-CSV_XS'')
# sudo dnf install perl-Text-CSV_XS
# git clone https://github.com/darold/pgbadger/

sudo dnf install pgcluu --setopt=install_weak_deps=False
# NB : httpd (Apache) est une dépendance généralement non voulue
# Alternative : récupérer la dernière version des scripts directement du dépôt :
# git clone  https://github.com/darold/pgcluu

# Outils de sauvegarde, au choix :
sudo dnf install pgbackrest        # penser à la sonde check_pgbackrest
sudo dnf install barman

* pgstat n’est pas packagé : si on y tient, il faut le télécharger et le compiler (hors du sujet de cet article)

Installation sous Debian et Ubuntu¶

Voir le howto de apt.postgresql.org ou cet extrait de nos formations pour les détails pour les dépôts du PGDG.

sudo apt-get install pgtop pg-activity pgstat

# Sondes
sudo apt-get install check-pgactivity
sudo apt-get install check-postgres

sudo apt-get install check-pgbackrest
# après installation des dépendances, il est parfois nécessaire de récupérer la dernière version sur le dépôt:
# git clone https://github.com/pgstef/check_pgbackrest

sudo apt-get install pgbadger
# Une fois les dépendances récupérées, il est parfois préférable de récupérer la dernière version sur le dépôt
# git clone https://github.com/darold/pgbadger/

sudo apt-get install pgcluu
# Voir la configuration plus bas !
# après installation des dépendances, il peut être nécessaire de prendre la dernière version des scripts dans le dépôt
# git clone  https://github.com/darold/pgcluu

# Outils de sauvegarde, au choix :
sudo apt-get install pgbackrest        # penser à la sonde check_pgbackrest
sudo apt-get install barman

Installations indépendantes de la distribution¶

pg_back¶

L’outil n’est pas packagé, il faut récupérer directement l’exécutable sur le dépôt :

v2
v1 (en bash)

Configuration de pgCluu¶

pgCluu peut-être en mode audit permanent. Par défaut, il n’est pas activé sur CentOS, et activé sur Debian.

Le service pgcluu génère les rapports dans /var/lib/pgcluu/report. Il est lancé par pgcluu.timer toutes les 5 minutes, mais peut être lancé manuellement. Le service pgcluu_collectd collecte les données toutes les minutes.

Les données sont dans /var/lib/pgcluu/data, les rapports dans /var/lib/pgcluu/report.

Selon que l’on veut ou non l’activer en arrière-plan :

# démarrage
sudo systemctl enable pgcluu_collectd
sudo systemctl start  pgcluu_collectd
sudo systemctl enable pgcluu.timer
sudo systemctl start  pgcluu.timer
# stopper
sudo systemctl stop    pgcluu_collectd
sudo systemctl disable pgcluu_collectd
sudo systemctl stop    pgcluu.timer
sudo systemctl disable pgcluu.timer

Sur Debian, si l’on n’en veut pas, penser à désactiver pgcluu_collectd.service comme indiqué plus haut ou à configurer la rétention, sous peine de voir exploser la place disque. Elle est définir par défaut à 30 jours dans /etc/pgcluu.conf :

# Retention limit in the statistics directory expressed in number of days.
# This directive is only used by pgcluu_collectd in incremental mode. Only
# the last number of days will be preserved, obsolete directories will be
# removed. It can be used to preserved disk space. Default store indefinitely.
STATS_COLLECTD_RETENTION        30

Des fichiers pour la visualisation des rapports via Apache sont inclus dans les paquets. La configuration d’Apache n’est pas traitée ici, car en général on n’en voudra pas sur un serveur de base de données.

Autorisations sudoers nécessaires¶

Si un simple sudo n’est pas possible, autoriser ce qui suit (lignes avec (root)).

Les lignes avec (postgres) servent pour le cas où un sudo -iu postgres générique n’est pas possible, ou pour des connexions sans mot de passe.

# En toute rigueur, nombre de ces commandes sont des failles de sécurité potentielles
# Vérifier les chemins. Ce qui suit suppose une installation par les paquets si possible
sudo visudo /etc/sudoers.d/dalibo

# sudo <commande>
dalibo ALL=(root) /usr/sbin/iotop
dalibo ALL=(root) /usr/sbin/lshw
dalibo ALL=(root) /usr/bin/ioping
dalibo ALL=(root) /usr/bin/strace
dalibo ALL=(root) /usr/bin/ltrace
dalibo ALL=(root) /usr/bin/perf
dalibo ALL=(root) /usr/bin/hugeadm
# sudo -iu postgres <commande>
dalibo ALL=(postgres) /usr/bin/pgbadger
dalibo ALL=(postgres) /usr/bin/pgcluu
dalibo ALL=(postgres) /usr/bin/pgcluu_collectd

# contrôle des instances
# CentOS et Debian :
dalibo ALL=(root) NOPASSWD: /bin/systemctl * postgresql
# Debian uniquement :
dalibo ALL=(root) NOPASSWD: /bin/systemctl * postgresql@*
dalibo ALL=(root) NOPASSWD: /usr/bin/pg_ctlcluster

# supervision avec un utilisateur nommé Nagios, sans mot de passe
# NB : pitrery est sa propre sonde Nagios
nagios ALL=(postgres) NOPASSWD:/usr/bin/pitrery * check
# sondes, utilisateur Nagios, chemins paquets CentOS
nagios ALL=(postgres) NOPASSWD:/usr/bin/check_postgres.pl
nagios ALL=(postgres) NOPASSWD:/usr/lib64/nagios/plugins/check_pgactivity
nagios ALL=(postgres) NOPASSWD:/bin/bash -c /usr/lib64/nagios/plugins/check_pgbackrest
# sondes, utilisateur Nagios, chemins paquets Debian
nagios ALL=(ALL) NOPASSWD:/usr/bin/check_postgres*
nagios ALL=(postgres) NOPASSWD:/usr/lib/nagios/plugins/check_pgactivity
nagios ALL=(postgres) NOPASSWD:/bin/bash -c /usr/bin/check_pgbackrest