Différences

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

--- serveurs:installation:db-srv:postgresql-config [2024/10/23 14:17] – [Restaurer localement un dump de la base du serveur] jpmilcent
+++ serveurs:installation:db-srv:postgresql-config [2026/09/17 14:31] (Version actuelle) – [Configurer les utilisateurs pour Postgresql] jpmilcent
@@ Ligne 35: / Ligne 35: @@
     * Y placer la commande d'activation du chronométrage des requêtes SQL : <code psql>\timing</code>
   * Se connecter au compte //admin//, et basculer sur le compte de l'utilisateur //postgres// : ''sudo -i -u postgres''
-    * Créer l'utilisateur //admin// et son mot de passe à l'aide de //psql// : '' psql -c "CREATE ROLE admin WITH LOGIN PASSWORD '<mot-de-passe>';"''
+    * Créer l'utilisateur //admin// et son mot de passe à l'aide de //psql// : ''%% psql -c "CREATE ROLE admin WITH LOGIN PASSWORD '<mot-de-passe>';" %%''
-    * Attribuer les droits de super-utilisateur : ''psql -c "ALTER USER admin WITH SUPERUSER CREATEDB CREATEROLE REPLICATION;"''
+    * Attribuer les droits de super-utilisateur : ''%% psql -c "ALTER USER admin WITH SUPERUSER CREATEDB CREATEROLE REPLICATION;" %%''
-    * Créer une base de données du nom de l'utilisateur pour qu'il puisse accéder à la commande ''psql'' : ''createdb -E UTF8 -O admin admin''
+    * Créer une base de données du nom de l'utilisateur pour qu'il puisse accéder à la commande ''psql'' : ''%% createdb -E UTF8 -O admin admin %%''
     * Se déconnecter de l'utilisateur //postgres// : ''exit''
   * Depuis le compte de l'utilisateur //admin// :
-    * Tester l'accès à Psql : ''psql''
+    * Tester l'accès à Psql : ''%% psql %%''
-      * Puis en sortie : ''\q''
+      * Puis en sortie : ''%% \q %%''
     * Créer l'utilisateur //Postgresql// //geonatadmin// et la base de données de GeoNature :
-      * Créer l'utilisateur //geonatadmin// pour //GeoNature// : '' psql -c "CREATE ROLE geonatadmin WITH LOGIN PASSWORD '<mot-de-passe>';"''
+      * Créer l'utilisateur //geonatadmin// pour //GeoNature// : ''%% psql -c "CREATE ROLE geonatadmin WITH LOGIN PASSWORD '<mot-de-passe>';" %%''
-      * Ajouter //geonatadmin// au rôle autorisant la lecture des fichiers pour la commande SQL COPY : '' GRANT pg_read_server_files TO geonatadmin ; ''
+      * Ajouter //geonatadmin// au rôle autorisant la lecture des fichiers pour la commande SQL COPY : ''%% psql -c "GRANT pg_read_server_files TO geonatadmin ;" %%''
-      * Le script d'installation créés la base de données de //GeoNature// (au cas ou la commande : ''createdb -E UTF8 -O geonatadmin geonature2db'' )
+      * Le script d'installation créés la base de données de //GeoNature// avec la commande : ''%% createdb -O geonatadmin -T template0 -E UTF-8 -l fr_FR.UTF-8 geonature2db %%''
     * Créer l'utilisateur //Postgresql// //geonatatlas// et la base de données de //GeoNature Atlas// :
-      * Créer l'utilisateur //geonatatlas// pour //GeoNature// : '' psql -c "CREATE ROLE geonatatlas WITH LOGIN PASSWORD '<mot-de-passe>';"''
+      * 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'' )
+      *  Le script d'installation créés la base de données de //GeoNature Atlas// (au cas ou la commande : ''%% createdb -O geonatatlas -T template0 -E UTF-8 -l fr_FR.UTF-8 gnatlas %%'' )
     * 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>';"''
+      * Créer l'utilisateur //telegraf// pour l'accès à la base //postgres// : ''%% psql -c "CREATE ROLE telegraf WITH LOGIN PASSWORD '<mot-de-passe>';" %%''
       * 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;"''
+        * à 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;"''
+          * 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é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 237: / 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 340: / 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//