Différences

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

--- postgresql [Le 11/09/2022, 11:36]
moths-art Suppression des espaces en fin de ligne (détecté et corrigé via le bot wiki-corrector (https://forum.ubuntu-fr.org/viewtopic.php?id=2067892)
+++ postgresql [Le 10/12/2024, 14:20] (Version actuelle)
bcag2 [encodage UTF-8] par défaut car par défaut sous Ubuntu (+ v9 n'est plus supporté!)
@@ Ligne 1: / Ligne 1: @@
-{{tag>sgbd serveur réseau BROUILLON}}
+{{tag>sgbd serveur réseau programmation}}
 ----
@@ Ligne 121: / Ligne 121: @@
 ^ \c nom_base nom_utilisateur | connecte à la base <nom_base> avec le rôle <nom_utilisateur>|
 ^ \lt | liste les tables de la base courante|
-^ set search_path to <mon_schéma>; | intéressant à faire avant la commande ci-dessous !-)|
+^ set search_path to mon_schema1, mon_schema2; | intéressant à faire avant la commande ci-dessous !-)|
-^ \d | liste les tables du schéma courant|
+^ \d | liste les tables du ou des schéma·s courant·s|
 ^ \d nom_table_ou_vue | description de la table <nom_table> (colonne, type…) ou de la vue nom_vue|
 ^ \d+ nom_table_ou_vue | description complète (avec commentaires!) de la table <nom_table> (colonne, type…) ou de la vue nom_vue avec son code source|
@@ Ligne 128: / Ligne 128: @@
 ^ \dn | liste tout le schéma de la base courante|
 ^ \h ALTER TABLE | affiche l'aide pour la commande ALTER TABLE|
+Pour lister les schémas :
+<code sql>
+select *from pg_namespace;
+</code>
 Pour lister les tables d'un schéma :
@@ Ligne 150: / Ligne 155: @@
 WHERE routines.specific_schema='monSchema'
 ORDER BY routines.routine_name, parameters.ordinal_position;
+</code>
+Lister les droits (//\du// est très limité !) :
+<code>
+select grantee, table_catalog, table_schema, table_name, string_agg(privilege_type, ', ' order by privilege_type) as privileges from information_schema.role_table_grants where grantee != 'postgres' group by grantee, table_catalog, table_schema, table_name;
+</code>
+et si besoin :
+<code sql>
+REVOKE ALL ON SCHEMA mon_schema FROM mon_role;
 </code>
 ====Supprimer l'utilisateur Postgres du greeter sous xubuntu 14.04.1====
@@ Ligne 217: / Ligne 232: @@
 Vous pouvez maintenant reprendre le tutoriel au début de la section "L'utilisateur postgres".
 ==== encodage UTF-8  ====
-<note tips> Ce paragraphe est optionnel </note>
+Postgresql se base sur la configuration du système d'exploitation… qui est en UTF-8 par défaut sous les versions francophone d'Ubuntu.
-FIXME En version PostgreSQL 9.1.5, l'installateur semble avoir directement créé avec le bon codage. Cette opération semble donc être devenue inutile.
-L'installateur initialise le cluster postgresql avec le support de jeux de caractères SQL_ASCII
-Pour ceux qui souhaitent privilégier l'encodage **UTF-8** / locale **FR-fr**, il faut supprimer le cluster et le recréer.
-=== jeux de caractères par défaut ===
-  * Connexion en tant qu'utilisateur postgres
-  sudo -i -u postgres
-  psql -l
-                             List of databases
-    Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges
-  ----------+----------+-----------+---------+-------+-----------------------
-  postgres  | postgres | SQL_ASCII | C       | C     |
-  template0 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
-            |          |           |         |       | postgres=CTc/postgres
-  template1 | postgres | SQL_ASCII | C       | C     | =c/postgres          +
-            |          |           |         |       | postgres=CTc/postgres
-=== supprimer le cluster et le recréer ===
-  * Identifier la version et le nom de votre cluster
-<code>
-$ pg_lsclusters
-  Version Cluster   Port Status Owner    Data directory                     Log file
-.1     main      5432 online postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log
-</code>
-  * Supprimer le cluster
-  pg_dropcluster 9.1 main --stop
-  * Créer le cluster
-  pg_createcluster --locale fr_FR.UTF-8 9.1 main
-  * Démarrage
-  pg_ctlcluster 9.1 main start
-Les bases systèmes seront alors conformes aux locales système
-<code>
-$ psql -l
-                                  List of databases
-    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-  ----------+----------+----------+-------------+-------------+-----------------------
-  postgres  | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 |
-  template0 | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres          +
-            |          |          |             |             | postgres=CTc/postgres
-  template1 | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres          +
-            |          |          |             |             | postgres=CTc/postgres
-</code>
 ==== Créer un utilisateur PostgreSQL ====
@@ Ligne 334: / Ligne 295: @@
 Il faut d'abord modifier le fichier de configuration pour autoriser les connexions via mot de passe chiffré (sur les versions récentes, au moins la 12, ça ne semble pas nécessaires) :
-<code>
+<code bash>
 sudo nano /etc/postgresql/x.x/main/pg_hba.conf
 </code>
@@ Ligne 346: / Ligne 307: @@
 Modifier le fichier en remplaçant **ident sameuser** par **md5** afin d'obtenir les lignes suivantes :
-<code>
+<code bash>
 ...
 # Database administrative login by Unix domain socket
@@ Ligne 364: / Ligne 325: @@
 La modification des paramètres de connexion au serveur nécessite son redémarrage pour que ces derniers soient pris en compte.
-<code>
+<code bash>
 $ sudo service postgresql restart
 </code>
 ou
-<code>
+<code bash>
 $ sudo pg_ctlcluster 9.5 main restart
 </code>
@@ Ligne 374: / Ligne 335: @@
 On peut ensuite créer notre utilisateur :
-<code>
+<code bash>
 $ sudo -i -u postgres
 $ createuser -P --interactive <nom_utilisateur>
@@ Ligne 385: / Ligne 346: @@
 Puis, on lui crée une base de données dédiée :
-<code>
+<code bash>
 $ createdb -O <nom_utilisateur> -E UTF8 <ma_database>
 </code>
@@ Ligne 394: / Ligne 355: @@
 L'option //-O <nom_utilisateur>//  est là pour préciser l'identité du propriétaire de la base <ma_database>.
 </note>
 Ensuite, on peut se connecter via :
-<code>
+<code bash>
 $ psql -U <nom_utilisateur>
-Mot de passe pour l'utilisateur <nom_utilisateur> :
+Mot de passe pour l’utilisateur <nom_utilisateur> :
 </code>
 ou, si le nom de la base de l'utilisateur n'est pas le même que celui de l'utilisateur :
-<code>
+<code bash>
 $ psql -U <nom_utilisateur> <nom_database_de_l_utilisateur>
-Mot de passe pour l'utilisateur <nom_utilisateur> :
+Mot de passe pour l’utilisateur <nom_utilisateur> :
 </code>
@@ Ligne 413: / Ligne 373: @@
 == Les variables d'environnement ==
 Les [[http://docs.postgresqlfr.org/9.0/libpq-envars.html|variables  d'environnement]] sont utilisées pour sélectionner des valeurs par défaut pour les paramètres de connexion.
-Les plus intéressantes à utiliser son PGUSER et PGHOST (notamment pour cette dernière si vous administrez une base qui n'est pas sur un serveur //localhost//).
+Les plus intéressantes à utiliser son //PGUSER// et //PGHOST// (notamment pour cette dernière si vous administrez une base qui n'est pas sur un serveur //localhost//).
 Pour que ces variables soient systématiquement disponibles dans votre shell de commandes, il faut les ajouter dans le fichier ~/.profile correspondant à votre utilisateur.
-Les lignes à ajouter sont (à vous de défionir les variables à ajouter):
+Les lignes à ajouter sont (à vous de définir les variables à ajouter):
+<code bash>
   export PGHOST=<nom_hôte>
   export PGUSER=<nom_utilisateur>
    ...
+ </code>
- La variable PGUSER étant ainsi définie, votre connexion peut alors se résumer à
+ La variable //PGUSER// étant ainsi définie, votre connexion peut alors se résumer à
+<code bash>
   psql <nom_database>
+</code>
 et si vous définissez PGDATABASE, votre connexion express sera simplement
+<code bash>
   psql
+</code>
 <note>
 il faut toujours saisir le mot de passe correspondant à l'utilisateur. Une variable PGPASSWORD existe, mais pour des raisons de sécurité, il est vivement recommandé de ne pas en faire usage.
 </note>
 == Le fichier .pgpass ==
@@ Ligne 443: / Ligne 408: @@
 Et la sécurité ?
 si vous tentez une connexion
+<code bash>
   psql <nom_database>
   WARNING: password file "/home/<user>/.pgpass" has group or world access; permissions should be u=rw (0600) or less
+</code>
 La limitation des droits d'accès à ce fichier est recommandée
+<code bash>
   chmod 0600 ~/.pgpass.
+</code>
 === Personnalisation ===
@@ Ligne 461: / Ligne 430: @@
 Ces quelques paramètres doivent suffire dans la majorité des cas. Si vous souhaitez aller plus loin dans le tuning de PostgreSQL, je vous conseille d'aller voir là [[http://docs.postgresqlfr.org|documentation PostgreSQL]] (qui existe en français)
 === Rôles et droits ===
@@ Ligne 485: / Ligne 452: @@
 Différentes contributions sont disponibles (cf la [[http://docs.postgresqlfr.org/9.1/contrib.html|documentation PostgreSQL]]). Leur installation (et désinstallation) est facilitée par l'existence de paquets dédiés
-<code>
+<code bash>
 sudo apt-get install postgresql-contrib
 Les paquets supplémentaires suivants seront installés :
@@ Ligne 493: / Ligne 460: @@
 Les scripts permettant l'application de ces extensions sont disponibles dans **/usr/share/postgresql/9.1/extension/*.sql**
 L'ajout se fait par exécution du script concerné sur la base de données souhaitée.
-<code>
+<code bash>
 $ psql -U <nom_utilisateur> <nom_database> -f /usr/share/postgresql/9.1/extension/<nom_extension>.sql
 </code>
@@ Ligne 500: / Ligne 467: @@
 Pour ne permettre seulement une consultation des données par un utilisateur sans droit de modification, il faut exécuter la requête suivante:
-<code>
+<code sql>
 ALTER USER <nom_utilisateur>  set default_transaction_read_only = on;
 </code>
@@ Ligne 617: / Ligne 584: @@
 ====Pool de connexion : PgBouncer====
 [[pgbouncer|PgBouncer]]
-===== Récupération du contenu d'une base de données après un crash serveur =====
+===== Sauvegarde et Restauration =====
+Il est conseillé de lire le support de [[#formations|formation]] //PostgreSQL Administration (DBA1)// qui contient les chapitres //Politique de sauvegarde// et //Sauvegarde et restauration//\\
+On pourra lire aussi [[https://cyber.gouv.fr/sites/default/files/document/anssi-fondamentaux-sauvegarde_systemes_dinformation_v1-0.pdf|SAUVEGARDE DES SYSTÈMES D’INFORMATION − LES FONDAMENTAUX]] de l’ANSII
+==== PgDump ====
+==== pgBackRest ====
+Le site officiel [[https://pgbackrest.org|pgBackRest]] et [[https://ng-pe.github.io/pgbackrest_doc_fr/user-guide.html|le guide utilisateur traduit]]
+=== Installation ===
+Simplement
+<code bash>
+sudo apt install pgbackrest
+</code>
+==== Récupération du contenu d'une base de données après un crash serveur ====
 Supposons que vous ayez installé sous [[ubuntu_server|ubuntu server]] une application d'entreprise s'appuyant sur une base de données postgresql (cette procédure a été testée avec [[redmine|redmine]]), et qu'un crash se produise qui rende impossible de redémarrer le serveur. Se pose alors le problème de la récupération des données. La procédure est relativement simple. Il va vous falloir:
   * Monter votre ancienne installation depuis une autre installation ou via un live cd
@@ Ligne 680: / Ligne 661: @@
 ===== Voir aussi =====
   * [[https://www.pgmodeler.io|PgModeler]] qui permet de définir le modèle et générer les scripts de mise à jour de la base postgresql, installable depuis les dépôts <code bash>sudo apt install pgmodeler</code>
+  * (en) [[https://labs.dalibo.com/temboard|temboard]] outils de supervision et configuration d'instances Postgresql
 ----
 //Contributeurs : [[utilisateurs:yannick_LM|Yannick]], [[utilisateurs:sparky|Sparky]], [[utilisateurs:elemmire|Elemmire]], [[utilisateurs:aldian|Aldian]], [[utilisateurs:fred|Fred]], [[utilisateurs:xavierjm|XAVIER_jean-marie]], [[utilisateurs:bcag2|bcag2]] //