Différences

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

--- serveurs:installation:db-srv:postgresql-config [2024/01/11 16:33] – [Mettre à jour Postgresql (Ex. v11 vers v15)] jpmilcent
+++ serveurs:installation:db-srv:postgresql-config [2026/06/03 09:26] (Version actuelle) – [Mise à jour des mots de passe des rôles] 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 206: / 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 233: / Ligne 237: @@
     * Cela devrait afficher un nombre supérieur à 0 : ''HugePages_Rsvd:        64''
+===== Créer un nouvel espace de stockage "tablespaces" =====
+  * Créer un dossier : <code bash>mkdir /data/postgresql-tmp</code>
+  * Attribuer les droits à l'utilisateur //postgres// : <code bash>chown postgres: /data/postgresql-tmp</code>
+  * Se connecter à psql avec //admin// : <code bash>ssh admin@db-<region>-sinp</code>
+  * Accéder au terminal Psql :<code bash>psql</code>
+    * Créer l'espace : <code sql>CREATE TABLESPACE tmp_data_storage LOCATION '/data/postgres-tmp'; </code>
+    * Donner les droits à tout monde : <code sql>GRANT CREATE ON TABLESPACE tmp_data_storage TO PUBLIC; </code>
+    * Voir les espaces existant et l'occupation : <code sql>\db+</code>
@@ Ligne 252: / Ligne 265: @@
       * 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 286: / Ligne 300: @@
 host    gnatlas         geonatadmin     10.0.1.10/32            scram-sha-256
 host    gnatlas         geonatatlas     10.0.1.10/32            scram-sha-256
-# Si telegraf utilisé seulement avec un accès basique :
+# 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é seulement avec un accès avancés (toutes les bases) :
+# 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)
@@ Ligne 335: / Ligne 349: @@
   * Tester une connexion en vous connectant via : <code bash>psql -U <login> -h localhost </code>
+==== Mise à jour des collations ====
+Après chaque mise à jour Debian ou de Postgresql, il peut être nécessaire de corriger les collations des bases de données Postgresql :
+  * Ressource : [[https://www.postgresql.org/docs/current/sql-altercollation.html#SQL-ALTERCOLLATION-NOTES|Doc Postgresql ALTER COLLATION]]
+  * Passer en utilisateur //postgres// : ''su - postgres''
+  * Lancer //Psql// : ''psql''
+  * Lister les problèmes de collations vis à vis des bases de données :<code sql>
+SELECT datname,
+  datcollate,
+  datcollversion,
+  pg_database_collation_actual_version(oid)
+FROM pg_database;
+</code>
+=== Mise à jour manuelle ===
+Pour chaque base avec une collation //datcollversion// différente de //pg_database_collation_actual_version//, il faut :
+  * Basculer sur la base (Ex. pour //geonature2db//) : ''\c geonature2db''
+  * Liste les éventuels objets à reconstruire : <code sql>
+SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
+  pg_describe_object(classid, objid, objsubid) AS "Object"
+FROM pg_depend d JOIN pg_collation c
+  ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
+WHERE c.collversion <> pg_collation_actual_version(c.oid)
+ORDER BY 1, 2;
+</code>
+  * Mettre à jour la collation, une fois les objets reconstruits : <code sql>ALTER DATABASE geonature2db REFRESH COLLATION VERSION;</code>
+  * La base //template0// ne doit pas avoir de collation. Pour Debian 12, nous devrions au final avoir quelque chose comme ceci:<code>
+   datname    | datcollate  | datcollversion | pg_database_collation_actual_version
+--------------+-------------+----------------+--------------------------------------
+ geonature2db | fr_FR.UTF-8 | 2.36           | 2.36
+ template1    | fr_FR.UTF-8 | 2.36           | 2.36
+ admin        | fr_FR.UTF-8 | 2.36           | 2.36
+ gnatlas      | fr_FR.UTF-8 | 2.36           | 2.36
+ postgres     | fr_FR.UTF-8 | 2.36           | 2.36
+ telegraf     | fr_FR.UTF-8 | 2.36           | 2.36
+ template0    | fr_FR.UTF-8 |                | 2.36
+(7 lignes)
+</code>
+=== Mise à jour auto ===
+Il existe également des [[https://gist.github.com/troykelly/616df024050dd50744dde4a9579e152e|scripts Bash capable d'automatiser cette mise à jour des collations]].
+  * Télécharger le script dans votre dossier ''~/bin'' : ''%%cd ~/bin; curl https://gist.githubusercontent.com/troykelly/616df024050dd50744dde4a9579e152e/raw/fe84e53cedf0caa6903604894454629a15867439/reindex_and_refresh_collation.sh%%''
+  * Ajouter les droits d'execution au script: ''chmod +x reindex_and_refresh_collation.sh''
+  * Exporter les variables d'environnement nécessaire : ''%%export POSTGRES_HOST=localhost; export POSTGRES_PORT=5432; export POSTGRES_USER=admin; export POSTGRES_PASSWORD=<mot-de-passe-de-admin>  %%''
+  * Exécuter le script : ''./reindex_and_refresh_collation.sh''
+  * Vérifier que les collations sont correctes avec la requête SQL indiqué ci-dessus
+  * Vérifier **la présence** des variables d'env : ''%%env|grep POSTGRES%%''
+  * Supprimer les variables d'env : ''%% unset POSTGRES_HOST; unset POSTGRES_PORT; unset POSTGRES_USER; unset POSTGRES_PASSWORD; %%''
+  * Vérifier **l'abscence** des variables d'env : ''%%env|grep POSTGRES%%''
 ===== 🗑️ Sauvegarder les bases de données =====
   * Se connecter sur "//db-srv//" en tant qu'//admin//