Différences

Ci-dessous, les différences entre deux révisions de la page.

--- serveurs:installation:db-srv:postgresql-config [2023/11/13 15:27] – jpmilcent
+++ serveurs:installation:db-srv:postgresql-config [2024/10/23 14:17] (Version actuelle) – [Restaurer localement un dump de la base du serveur] jpmilcent
@@ Ligne 49: / Ligne 49: @@
       * Créer l'utilisateur //geonatatlas// pour //GeoNature// : '' psql -c "CREATE ROLE geonatatlas WITH LOGIN PASSWORD '<mot-de-passe>';"''
       *  Le script d'installation créés la base de données de //GeoNature Atlas// (au cas ou la commande : ''createdb -E UTF8 -O geonatatlas gnatlas'' )
-    * Créer l'utilisateur //Postgresql// //telegraf// qui peut accéder à la base //postgres// :
+    * Créer l'utilisateur //Postgresql// //telegraf// :
       * Créer l'utilisateur //telegraf// pour l'accès à la base //postgres// : '' psql -c "CREATE ROLE telegraf WITH LOGIN PASSWORD '<mot-de-passe>';"''
-      * Ajouter le droit de connection à la base ''postgres'' avec : ''psql -c "GRANT CONNECT ON DATABASE postgres TO telegraf;"''
+      * avec au choix un accès :
+        * à la base //postgres// seulement (mode basique), ajouter le droit de connexion à la base ''postgres'' avec : ''psql -c "GRANT CONNECT ON DATABASE postgres TO telegraf;"''
+        * à toutes les bases (mode avancé) :
+          * droits de super-utilisateur : ''psql -c "ALTER USER telegraf SUPERUSER CONNECTION LIMIT 3;"''
+          * création d'une base de données a son nom : ''createdb -E UTF8 -O telegraf telegraf''
     * Créer, si nécessaire, l'utilisateur //Postgreqsl// //gnreader// qui a des droits d'accès lecture seule. Voir [[serveurs:installation:db-srv:postgresql-ssh-tunnel|la documentation correspondante]].
@@ Ligne 96: / Ligne 100: @@
 ===== Optimiser la configuration de Postgresql vis à vis du serveur =====
+==== Gestion des logs et des stats ====
+  * Commencer par modifier le fichier logrotate de Postgresql qui se chargera des rotations :<code properties>
+/var/log/postgresql/*.log {
+       weekly
+       rotate 10
+       copytruncate
+       delaycompress
+       compress
+       notifempty
+       missingok
+       su root root
+}
+</code>
+  * Nous allons surcharger de nouveaux paramètres de configuration de Postgresql (présents dans ''postgresql.conf'') via le fichier ''01_override.conf'' avec :  ''vi /etc/postgresql/12/main/conf.d/01_override.conf'' <code properties>
+# General
+shared_preload_libraries = 'pg_stat_statements,pg_prewarm'
+# Log
+log_destination = stderr
+logging_collector = on
+# Use logrotate for rotation. See: /etc/logrotate.d/postgresql-common
+#log_rotation_age = 1d
+#log_rotation_size = 100MB
+# Log format for PgBadger. See: https://pgbadger.darold.net/documentation.html#REQUIREMENT
+log_line_prefix= '%t [%p]: db=%d,user=%u,app=%a,client=%h '
+lc_messages = 'C.UTF-8'
+log_autovacuum_min_duration = 0
+log_checkpoints = on
+log_connections = on
+log_disconnections = on
+log_lock_waits = on
+log_temp_files = 0
+log_error_verbosity = default
+# Activity stats
+track_activity_query_size = 20480
+compute_query_id = on
+track_io_timing = on
+track_wal_io_timing = on
+track_functions = 'pl'
+# Extenssion pg_stat_statements config
+pg_stat_statements.max = 10000
+pg_stat_statements.track = all
+</code>
 ==== Utilisation de PgTune ====
 L'idée est d'adapter rapidement la configuration de Postgresql fournie par défaut aux caractéristiques de l'instance abritant le serveur Postgresql. Pour cela nous utiliserons le site https://pgtune.leopard.in.ua/.
@@ Ligne 161: / Ligne 210: @@
   * Donner les droits d'execution : ''%%chmod +x postgresqltuner.pl%%''
   * Recharger le shell : ''source ~/.bashrc''
-  * Lancer le script (mettre un espace devant la commande pour éviter d'enregistrer le mot de passe dans l'historique) :  '' postgresqltuner.pl --host=localhost --database=geonature2db --user=admin --password=<mot-de-passe>''
+  * Lancer le script (mettre un espace devant la commande pour éviter d'enregistrer le mot de passe dans l'historique) :  ''%% postgresqltuner.pl --host=localhost --database=geonature2db --user=admin --password=<mot-de-passe>%%''
   * Etudier les consigne du script et modifier le fichier de config si nécessaire : ''vi /etc/postgresql/15/main/conf.d/02_optimize.conf''
@@ Ligne 188: / Ligne 237: @@
     * Cela devrait afficher un nombre supérieur à 0 : ''HugePages_Rsvd:        64''
-===== 🗑️ Sauvegarder les bases de données =====
-  * Se connecter sur "//db-srv//" en tant qu'//admin//
-  * La mise en place de sauvegardes automatique des bases de données en local dans le dossier ///home/admin/backups/postgresql/// avec copie distante sur Dropbox, passe par l'installation de scripts Bash et de Cron :
-    * Créer le dossier //~/bin// pour l'utilisateur //admin// '' mkdir ~/bin ''
-      * Copier dedans le contenu [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/home/admin/bin|des scripts Bash présents dans le dépôt Github sinp-paca-srv emplacement /db-srv/home/admin/bin/]].
-    * Installer en //root// dans le dossier ///opt/bkp2dbx/// le contenu [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/shared/opt/bkp2dbx| présents dans le dépôt Github sinp-paca-srv emplacement /shared/opt/bkp2dbx]]
-    * Installer dans le dossier ///etc/cron.d// les //cron// [[https://github.com/cbn-alpin/sinp-paca-srv/blob/master/db-srv/etc/cron.d/| présents dans le dépôt Github sinp-paca-srv emplacement /db-srv/etc/cron.d/]]. **ATTENTION** : bien retirer l'extension //.cron//. Les fichiers présents dans le dossier ///etc/cron.d// ne doivent pas contenir de point (''.'') ou de tiret (''-'') car sinon ils ne sont pas exécutés.
-  * Exemple de commande pour sauvegarder les bases GeoNature sans l'utilisation des scripts précédents :
-    * S'il n'existe pas créer un dossier //~/backups/postgresql/// avec la commande : '' mkdir -p ~/backups/postgresql/ ''
-    * Création de la sauvegarde pour la base **//geonature2db//** :
-      * Pour accélérer la sauvegarde et économiser de la place nous utiliserons le format de sauvegarde "//directory//" (paramètre ''<nowiki>--format=d</nowiki>'') qui permet une parallélisation sur plusieurs CPUs (paramètre ''<nowiki>--jobs</nowiki>'') et une compression (paramètre ''<nowiki>--compress 9</nowiki>''): <code bash>
-export BACKUP_DIR="/home/${USER}/backups/postgresql"; \
-export BACKUP_NAME="$(date +%F)_gonature2db"; \
-export BACKUP_PATH="${BACKUP_DIR}/${BACKUP_NAME}"; \
-mkdir -p "${BACKUP_DIR}"; \
-pg_dump --file "${BACKUP_PATH}" --host "localhost" --port "5432" --username "geonatadmin" --verbose --format=d --jobs=$(grep -c ^processor /proc/cpuinfo) --compress 9 geonature2db ; \
-tar -cvf "${BACKUP_NAME}.tar" -C "${BACKUP_DIR}" "${BACKUP_NAME}/" ; \
-cd "${BACKUP_DIR}" ; \
-rm -fR "${BACKUP_NAME}/" ;
-find "${BACKUP_DIR}" -name "*_gonature2db.tar" -type f -mtime +5 -exec rm -f {} \;
-</code>
-    * Création de la sauvegarde pour la base **//gnatlas//** :
-      * Elle ne contient que des tables étrangères et des vues matérialisées, nous l'exportons donc en SQL (paramètre ''<nowiki>--format=p</nowiki>'') : <code bash>
-export BACKUP_DIR="/home/${USER}/backups/postgresql"; \
-export BACKUP_NAME="$(date +%F)_gnatlas"; \
-export BACKUP_PATH="${BACKUP_DIR}/${BACKUP_NAME}.sql"; \
-pg_dump --file "${BACKUP_PATH}" --host "localhost" --port "5432" --username "geonatatlas" --verbose --format=p gnatlas ; \
-tar -cvf "${BACKUP_NAME}.tar" -C "${BACKUP_DIR}" "${BACKUP_NAME}.sql" ; \
-rm -f "${BACKUP_PATH}" ; \
-find "${BACKUP_DIR}" -name "*_gnatlas.tar" -type f -mtime +365 -exec rm -f {} \;
-</code>
-   * Pour envoyer ces sauvegardes sur Dropbox utilisé le script : '' /opt/bkp2dbx/bkp2dbx.sh "postgresql" "/home/admin/backups/postgresql/*.tar" "/home/admin/.dropbox_uploader" ''
-==== 🗑️ Automatisation de la sauvegarde ====
-  * Se connecter sur "//db-srv//" en tant qu'//admin//
-  * Créer le dossier //~/bin/// : '' mkdir ~/bin ''
-  * Copier dans ce dossier le contenu du dossier [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/home/admin/bin| db-srv/home/admin/bin]] sur le serveur : '' scp -r bin/* admin@db-<region>-sinp:~/bin/ ''
-  * Créer un fichier //~/.pgpass// ([[https://www.postgresql.org/docs/current/libpq-pgpass.html|voir doc]])qui contiendra les mots de passe pour accéder aux base de données : '' vi ~/.pgpass ''
-    * Y stocker ceci : <code>
-# Format: hostname:port:database:username:password
-localhost:5432:geonature2db:geonatadmin:<mot-de-passe>
-localhost:5432:gnatlas:geonatatlas:<mot-de-passe>
-</code>
-    * Donner les bons droits : '' chmod 600 ~/.pgpass ''
-  * S'il n'existe pas créer un dossier //~/backups/postgresql/// avec la commande : '' mkdir -p ~/backups/postgresql/ ''
-  * Copier dans le dossier ///etc/cron.d/// le fichier [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/etc/cron.d/backups| db-srv/etc/cron.d/backups]] sur le serveur : '' scp -r backups admin@db-<region>-sinp:~/dwl/ '' puis sur le serveur : '' sudo -i ; mv /home/admin/dwl/backups /etc/cron.d/ ''
-  * L'administrateur système doit recevoir un email après chaque exécution des scripts de sauvegarde.
 ===== Restaurer localement un dump de la base du serveur =====
@@ Ligne 253: / Ligne 256: @@
       * pour l'option ''<nowiki>--jobs</nowiki>'' indiquer seulement 3/4 du nombre de processeurs (sinon une erreur "nombre max de client atteint" peut apparaître). Dans l'exemple, 8 pour 12 CPU. Pour afficher le nombre de processeur de votre machine : ''grep -c ^processor /proc/cpuinfo''
   * Retirer les droits de super-utilisateur à ''geonatadmin'' : <code bash> sudo -u postgres psql -c "ALTER ROLE geonatadmin NOSUPERUSER;" </code>
-  * Renommer la base de données après avoir supprimé la base de données de destination. Exemple avec pour base de destination ''gn2_sinp_paca'' :
+  * Supprimer la base de données de destination. Exemple avec pour base de destination ''gn2_sinp_paca'' : <code bash> sudo -u postgres dropdb --if-exists gn2_sinp_paca </code>
-    * <code bash> sudo -u postgres dropdb --if-exists gn2_sinp_paca </code>
+  * Vous pouvez ensuite soit renommer la base de données ''geonature2db'' soit vous en servir de template et la maintenir en local :
-    * <code bash> sudo -u postgres psql -c "ALTER DATABASE geonature2db RENAME TO gn2_sinp_paca;" </code>
+    * Copier la base : <code bash> sudo -u postgres psql -c "CREATE DATABASE gn2_sinp_paca WITH TEMPLATE geonature2db ;" </code>
+    * Renommer la base : <code bash> sudo -u postgres psql -c "ALTER DATABASE geonature2db RENAME TO gn2_sinp_paca;" </code>
+  * Réattributer la propriété de la base à geonatadmin : <code sql>psql -c "ALTER DATABASE gn2_sinp_paca OWNER TO geonatadmin;"</code>
 ===== Mettre à jour Postgresql (Ex. v11 vers v15) =====
   * Sur ''web-srv'', activer la maintenance longue de GeoNature et l'Atlas.
@@ Ligne 287: / Ligne 291: @@
 host    gnatlas         geonatadmin     10.0.1.10/32            scram-sha-256
 host    gnatlas         geonatatlas     10.0.1.10/32            scram-sha-256
-host    postgres        telegraf        172.18.5.0/24           scram-sha-256
+# Si telegraf utilisé avec un accès basique (base postgres uniquement) :
+#host    postgres        telegraf        172.18.5.0/24           scram-sha-256
+# Si telegraf utilisé avec un accès avancé (toutes les bases) :
+host    all             telegraf        172.18.5.0/24           scram-sha-256
 # GeoNature : access by gnreader (read only)
 host    geonature2db    gnreader        10.0.1.20/32            scram-sha-256
@@ Ligne 333: / Ligne 340: @@
   * Tester une connexion en vous connectant via : <code bash>psql -U <login> -h localhost </code>
+===== 🗑️ Sauvegarder les bases de données =====
+  * Se connecter sur "//db-srv//" en tant qu'//admin//
+  * La mise en place de sauvegardes automatique des bases de données en local dans le dossier ///home/admin/backups/postgresql/// avec copie distante sur Dropbox, passe par l'installation de scripts Bash et de Cron :
+    * Créer le dossier //~/bin// pour l'utilisateur //admin// '' mkdir ~/bin ''
+      * Copier dedans le contenu [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/home/admin/bin|des scripts Bash présents dans le dépôt Github sinp-paca-srv emplacement /db-srv/home/admin/bin/]].
+    * Installer en //root// dans le dossier ///opt/bkp2dbx/// le contenu [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/shared/opt/bkp2dbx| présents dans le dépôt Github sinp-paca-srv emplacement /shared/opt/bkp2dbx]]
+    * Installer dans le dossier ///etc/cron.d// les //cron// [[https://github.com/cbn-alpin/sinp-paca-srv/blob/master/db-srv/etc/cron.d/| présents dans le dépôt Github sinp-paca-srv emplacement /db-srv/etc/cron.d/]]. **ATTENTION** : bien retirer l'extension //.cron//. Les fichiers présents dans le dossier ///etc/cron.d// ne doivent pas contenir de point (''.'') ou de tiret (''-'') car sinon ils ne sont pas exécutés.
+  * Exemple de commande pour sauvegarder les bases GeoNature sans l'utilisation des scripts précédents :
+    * S'il n'existe pas créer un dossier //~/backups/postgresql/// avec la commande : '' mkdir -p ~/backups/postgresql/ ''
+    * Création de la sauvegarde pour la base **//geonature2db//** :
+      * Pour accélérer la sauvegarde et économiser de la place nous utiliserons le format de sauvegarde "//directory//" (paramètre ''<nowiki>--format=d</nowiki>'') qui permet une parallélisation sur plusieurs CPUs (paramètre ''<nowiki>--jobs</nowiki>'') et une compression (paramètre ''<nowiki>--compress 9</nowiki>''): <code bash>
+export BACKUP_DIR="/home/${USER}/backups/postgresql"; \
+export BACKUP_NAME="$(date +%F)_gonature2db"; \
+export BACKUP_PATH="${BACKUP_DIR}/${BACKUP_NAME}"; \
+mkdir -p "${BACKUP_DIR}"; \
+pg_dump --file "${BACKUP_PATH}" --host "localhost" --port "5432" --username "geonatadmin" --verbose --format=d --jobs=$(grep -c ^processor /proc/cpuinfo) --compress 9 geonature2db ; \
+tar -cvf "${BACKUP_NAME}.tar" -C "${BACKUP_DIR}" "${BACKUP_NAME}/" ; \
+cd "${BACKUP_DIR}" ; \
+rm -fR "${BACKUP_NAME}/" ;
+find "${BACKUP_DIR}" -name "*_gonature2db.tar" -type f -mtime +5 -exec rm -f {} \;
+</code>
+    * Création de la sauvegarde pour la base **//gnatlas//** :
+      * Elle ne contient que des tables étrangères et des vues matérialisées, nous l'exportons donc en SQL (paramètre ''<nowiki>--format=p</nowiki>'') : <code bash>
+export BACKUP_DIR="/home/${USER}/backups/postgresql"; \
+export BACKUP_NAME="$(date +%F)_gnatlas"; \
+export BACKUP_PATH="${BACKUP_DIR}/${BACKUP_NAME}.sql"; \
+pg_dump --file "${BACKUP_PATH}" --host "localhost" --port "5432" --username "geonatatlas" --verbose --format=p gnatlas ; \
+tar -cvf "${BACKUP_NAME}.tar" -C "${BACKUP_DIR}" "${BACKUP_NAME}.sql" ; \
+rm -f "${BACKUP_PATH}" ; \
+find "${BACKUP_DIR}" -name "*_gnatlas.tar" -type f -mtime +365 -exec rm -f {} \;
+</code>
+   * Pour envoyer ces sauvegardes sur Dropbox utilisé le script : '' /opt/bkp2dbx/bkp2dbx.sh "postgresql" "/home/admin/backups/postgresql/*.tar" "/home/admin/.dropbox_uploader" ''
+==== 🗑️ Automatisation de la sauvegarde ====
+  * Se connecter sur "//db-srv//" en tant qu'//admin//
+  * Créer le dossier //~/bin/// : '' mkdir ~/bin ''
+  * Copier dans ce dossier le contenu du dossier [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/home/admin/bin| db-srv/home/admin/bin]] sur le serveur : '' scp -r bin/* admin@db-<region>-sinp:~/bin/ ''
+  * Créer un fichier //~/.pgpass// ([[https://www.postgresql.org/docs/current/libpq-pgpass.html|voir doc]])qui contiendra les mots de passe pour accéder aux base de données : '' vi ~/.pgpass ''
+    * Y stocker ceci : <code>
+# Format: hostname:port:database:username:password
+localhost:5432:geonature2db:geonatadmin:<mot-de-passe>
+localhost:5432:gnatlas:geonatatlas:<mot-de-passe>
+</code>
+    * Donner les bons droits : '' chmod 600 ~/.pgpass ''
+  * S'il n'existe pas créer un dossier //~/backups/postgresql/// avec la commande : '' mkdir -p ~/backups/postgresql/ ''
+  * Copier dans le dossier ///etc/cron.d/// le fichier [[https://github.com/cbn-alpin/sinp-paca-srv/tree/master/db-srv/etc/cron.d/backups| db-srv/etc/cron.d/backups]] sur le serveur : '' scp -r backups admin@db-<region>-sinp:~/dwl/ '' puis sur le serveur : '' sudo -i ; mv /home/admin/dwl/backups /etc/cron.d/ ''
+  * L'administrateur système doit recevoir un email après chaque exécution des scripts de sauvegarde.