Différences

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

--- postgresql [Le 01/05/2012, 07:34]
=ô= [Installation du serveur PostgreSQL]
+++ postgresql [Le 05/03/2024, 11:27] (Version actuelle)
bcag2 [Guide de survie pour le client psql] lister les schémas
@@ Ligne 4: / Ligne 4: @@
 ====== PostgreSQL ======
 ===== Introduction =====
-PostgreSQL est un système de gestion de bases de données (SGBD) très performant sous licence BSD dont les performances sont comparables à Oracle 9.
+[[wpfr>PostgreSQL]] est un système de gestion de bases de données ([[:SGBD]]) très performant sous licence BSD dont les performances sont comparables à Oracle 9.
-Il propose de très nombreuses fonctionnalités, tout en respectant les standards SQL : SQL 92, 99 et en partie la norme SQL2003. En outre, il intègre plusieurs langages embarqués (Perl, Python, Java) depuis de nombreuses années. [[http://www.postgresql.org/about/|Pour en savoir plus]]
+Il propose de très nombreuses fonctionnalités, tout en respectant les standards SQL : SQL 92, 99 et en partie la norme SQL2003. En outre, il intègre plusieurs langages embarqués (Perl, Python, Java) depuis de nombreuses années. [[https://www.postgresql.org/about/|Pour en savoir plus]]
 ===== Documentation =====
-La documentation ainsi que d'autres informations sont disponibles sur le [[http://www.postgresql.org/|site officiel de PostgreSQL]].
+La documentation ainsi que d'autres informations sont disponibles sur le [[https://www.postgresql.org/|site officiel de PostgreSQL]].
-La communauté [[http://www.postgresqlfr.org/|postgresqlfr.org]] la reprend en [[http://docs.postgresql.fr/|français]].
+La communauté [[https://www.postgresql.fr/|postgresql francophone]] la reprend en [[http://docs.postgresql.fr/|français]].
 ===== Installation du serveur PostgreSQL=====
-On commence par [[:tutoriel/comment_installer_un_paquet|installer]] le paquet [[apt://postgresql|postgresql]].
+==== Méthode recommandée ====
-<code>
-Les paquets supplémentaires suivants seront installés :
-  libpq5 postgresql-9.1 postgresql-client-9.1 postgresql-client-common
-</code>
-La version installée par défaut (Précise Pangolin) est la 9.1. Il est possible d'installer la dernière version majeur (ici la 8.4). Il faut alors le préciser à l'installation. [[apt://postgresq-8.4l|postgresql-8.4]].
+[[:tutoriel/comment_installer_un_paquet|Installez]] simplement le paquet [[apt://postgresql|postgresql]], soit en ligne de commande :
+<code>sudo apt install postgresql</code>
 Notez que l'installation ajoute l'utilisateur postgres à la liste des utilisateurs. Utilisez finger si vous souhaitez de plus amples informations sur ce nouvel utilisateur (finger est un utilitaire permettant de consulter les informations relatives aux utilisateurs système, il n'est pas installé par défaut dans ubuntu)
 <code>finger postgres</code>
+==== Installation manuelle avec l'installeur interactif EnterpriseDB ====
+Téléchargez le dernier fichier .run sur [[http://www.enterprisedb.com/products-services-training/pgdownload|cette page]] (elle est référencée sur [[https://www.postgresql.org/download/linux/ubuntu/|le site officiel]]) - vous aurez probablement besoin de la version //Linux x86-64//.
+  - rendez le fichier executable :
+<code>sudo chmod +x postgresql-10.6-1-linux-x64.run</code>
+  - puis exécutez-le en tant que [[:sudo|super utilisateur]] avec la commande :
+<code>sudo ./postgresql-10.6-1-linux-x64.run</code>
+Suivez les étapes affichées par l'interface graphique de l'installeur.
+Une fois l'installation terminée, PostgreSQL devrait être installé ainsi que pgAdmin.
+Cependant, l'icône de pgAdmin n'est pas visible dans le menu (enfin c'était mon cas).
+Dans ce cas, allez dans le dossier** /usr/share/applications/** et recherchez l'icône de pgadmin.
+Changer les droit en écriture (sudo chmod o+w pgadminIII), puis l'ouvrir avec un éditeur de texte.
+Dans mon cas, j'ai dû y ajouter la ligne :**Categories=Development;Engineering;**
+Pour le rendre visible dans mon menu sous la catégorie Développement.
+==== Mise à jour Postgres EnterpriseDB avec conservation des Bases de Données ====
+Supprimer l'ancienne version en lançant le désinstalleur Postgres (dans le dossier Postgres):
+<code>
+$ sudo ./uninstall-postgresql
+</code>
+Le dossier **Datas/** n'est pas supprimé.\\ Cela permet de garder les anciennes bases de données et la configuration utilisateur postgres.\\
+Lancer le programme d'installation de postgres, après l'avoir téléchargé sur le site ci-dessus.
+<code>
+$ sudo ./postgresql-version-linux-x64.run
+</code>
+Lors de la pré-installation, choisir le chemin du dossier **Datas/** qui n'a pas été supprimé.\\
+Confirmation de l'existence d'un dossier **Datas/** déjà existant.\\
+Lancer l'installation.
-==== L'utilisateur postgres ====
+==== Désinstallation ====
+Si vous souhaitez désinstaller, par exemple lors du passage à l'utilisation de conteneurs [[:docker]], il faut :
+<code bash>
+# arrêtez le service s'il est lancé
+sudo systemctl stop postgresql
+# lister les paquets installés liés à postgres :
+dpkg -l | grep postgres
+# et les supprimer :
+sudo apt --purge remove postgresql-*
+</code>
+===== Configuration =====
+==== L'utilisateur postgres et psql ====
 PostgreSQL est un serveur qui permet de se connecter à différentes bases de données. Par défaut, seul l'utilisateur //postgres// peut se connecter.
@@ Ligne 54: / Ligne 79: @@
 <code>
-$ sudo -su postgres
+$ sudo -i -u postgres
 Password:
 </code>
@@ Ligne 62: / Ligne 87: @@
 <note tip>Il se peut que cette authentification retourne une erreur liée au fait qu'il est impossible pour //postgres// de lire, par exemple, le fichier ~/.bashrc (si un terminal bash est utilisé). La commande //chmod ug+r ~/.bashrc// exécutée avant la connexion postgres réglera ce problème.</note>
-Désormais, l'invite de commande doit mentionner que vous êtes actif en tant que //postgres//. Pour lancer l'outil d'administration de PostgreSQL, tapez simplement :
+Désormais, l'invite de commande doit mentionner que vous êtes actif en tant que //postgres//. Pour lancer l'invite de commande SQL de PostgreSQL, tapez simplement :
 <code>
 psql
@@ Ligne 69: / Ligne 94: @@
 Vous devriez obtenir quelque chose comme :
 <code>
-psql (9.1.3)
+psql (13.1-1)
 Type "help" for help.
 postgres=#
 </code>
+<note tip>
+Si vous obtenez le message suivant :
+<code>
+psql: could not connect to server: Aucun fichier ou dossier de ce type
+</code>
+Reportez-vous à la section "Erreur psql au premier démarrage" pour résoudre le problème.
+ </note>
 Notez que vous avez une interface en ligne de commande, et que vous êtes connecté en tant que "root", comme l'indique le ''#'' en bout de ligne.
@@ Ligne 79: / Ligne 112: @@
 Cette ligne de commande sera bien sûr utilisée pour exécuter des requêtes SQL, et aussi des commandes internes à postgreSQL (elles commencent toutes par un antislash)
-Petit guide de survie pour le client psql
+==== Guide de survie pour le client psql ====
+^ \h | pour l'aide-mémoire des commandes SQL|
+^ \? | pour l'aide-mémoire des commandes psql|
+^ \g | ou point-virgule en fin d'instruction pour exécuter la requête|
+^ \q | pour quitter|
+^ \l | liste les bases de données existantes|
+^ \du | liste les rôles (utilisateur ou groupe)|
+^ \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 !-)|
+^ \d | liste les tables du schéma courant|
+^ \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|
+^ \sv nom_vue | à la différence de \d+, n'affiche que le code source de la vue|
+^ \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 :
+<code sql>
+select t.table_name
+from information_schema.tables t
+where t.table_schema = 'nom_schema'  -- remplacer le nom du schema ici
+and t.table_type = 'BASE TABLE'
+order by t.table_name;
+</code>
+Lister les vues :
+<code sql>
+select schemaname, viewname from pg_catalog.pg_views where schemaname NOT IN ('pg_catalog', 'information_schema') order by schemaname, viewname;
+</code>
+Lister les fonctions d'un schéma (**monSchema** dans l'exemple ci-dessous, à adapter) ((src: https://stackoverflow.com/a/1347639/6614155)) :
+<code sql>
+SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position
+FROM information_schema.routines
+    LEFT JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
+WHERE routines.specific_schema='monSchema'
+ORDER BY routines.routine_name, parameters.ordinal_position;
+</code>
+Lister les droits (//\du// est très limité !) :
 <code>
-        \h pour l'aide-mémoire des commandes SQL
+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;
-        \? pour l'aide-mémoire des commandes psql
-        \g ou point-virgule en fin d'instruction pour exécuter la requête
-        \q pour quitter
 </code>
-<note tip>
+et si besoin :
-L'installateur initialise le cluster postgresql avec le support de jeux de caractères suivant :
+<code sql>
+REVOKE ALL ON SCHEMA mon_schema FROM mon_role;
+</code>
+====Supprimer l'utilisateur Postgres du greeter sous xubuntu 14.04.1====
+La suppression de l'utilisateur postgres dans le cas d'un greeter lightdm-webkit-greeter.\\
+Aller dans le répertoire **/var/lib/AccountsService/users/** .\\
+Créer un fichier text puis nommez-le **postgres** (nom de l'utilisateur postgres).\\
+Puis y ajouter le contenu suivant :
+<code>
+[User]
+XSession=xubuntu
+SystemAccount=true
+</code>
+Sauvegarder et re-démarrer le greeter.
+==== Erreur psql au premier démarrage ====
+Si vous obtenez une erreur du type :
+<code>
+psql: could not connect to server: Aucun fichier ou dossier de ce type
+Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
+</code>
+Déconnectez-vous du compte postgres :
+<code>
+logout
+</code>
+Puis vérifiez que le dossier de configuration PostgreSQL existe :
+<code>
+stat /etc/postgresql
+</code>
+Si la réponse est du type :
+<code>
+  File: ‘/etc/postgresql’
+  Size: 4096      	Blocks: 8          IO Block: 4096   directory
+Device: fd01h/64769d	Inode: 131535      Links: 3
+Access: (0755/drwxr-xr-x)  Uid: (    0/    root)   Gid: (    0/    root)
+Access: 2015-10-29 20:15:04.091560419 +0100
+Modify: 2015-10-29 20:15:04.091560419 +0100
+Change: 2015-10-29 20:15:04.091560419 +0100
+ Birth: -
+</code>
+Il peut s'agir d'une mauvaise configuration du port.
+[[:tutoriel:comment_modifier_un_fichier|Modifiez le fichier]] **/etc/postgresql/9.4/main/postgresql.conf**
+<code>
+...
+port = 5432
+...
+</code>
+Puis redémarrez le serveur PostgreSQL:
+<code>
+$ sudo service postgresql restart
+</code>
+**Sinon**, si la réponse de ''stat'' est du type :
+<code>
+stat: cannot stat ‘/etc/postgresql’: No such file or directory
+</code>
+Il faut construire le cluster PostgreSQL avant de pouvoir le démarrer. Affichez la version installée de psql avec la comande suivante :
+<code>
+psql --version
+</code>
+Puis reportez les 2 premiers nombres du numéro de version dans la commande suivante : ''sudo pg_createcluster X.Y main''. Par exemple si le résultat de ''psql --version'' est ''psql (PostgreSQL) 9.3.10'', entrez la commande suivante :
+<code>
+sudo pg_createcluster 9.3 main
+</code>
+Puis redémarrez le serveur PostgreSQL:
+<code>
+$ sudo service postgresql restart
+</code>
+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>
+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.
-Connexion en tant qu'utilisateur postgres
+=== jeux de caractères par défaut ===
+  * Connexion en tant qu'utilisateur postgres
-  sudo -su postgres
+  sudo -i -u postgres
   psql -l
@@ Ligne 104: / Ligne 254: @@
             |          |           |         |       | postgres=CTc/postgres
-Pour ceux qui souhaitent privilégier l'encodage UTF-8 / locale FR-fr, il faut supprimer le cluster et le recréer.
+=== supprimer le cluster et le recréer ===
-Identifier la version et le nom de votre cluster
+  * Identifier la version et le nom de votre cluster
+<code>
-  pg_lsclusters
+$ 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
+  * Supprimer le cluster
   pg_dropcluster 9.1 main --stop
-Créer le cluster
+  * Créer le cluster
-  pg_createcluster 9.1 main
+  pg_createcluster --locale fr_FR.UTF-8 9.1 main
+  * Démarrage
-Démarrage
   pg_ctlcluster 9.1 main start
 Les bases systèmes seront alors conformes aux locales système
+<code>
-  psql -l
+$ psql -l
                                   List of databases
     Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
@@ Ligne 134: / Ligne 285: @@
   template1 | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
+</code>
-</note>
 ==== Créer un utilisateur PostgreSQL ====
-=== Méthode rapide, non sécurisée ===
+=== Méthode rapide, non sécurisée (-> privilégier la méthode décrite dans le chapitre suivant)===
 ''postgres'' est à psql ce que ''root'' est à Ubuntu. Il ne faut l'utiliser que pour les tâches d'administration, en aucun cas s'en servir pour un applicatif, sinon vous allez créer une faille de sécurité.
@@ Ligne 145: / Ligne 296: @@
 Par facilité, nous allons assimiler les utilisateurs du système à ceux qui se connecteront avec psql, mais ce n'est nullement obligatoire. Par la suite, nous supposerons que votre login système est ''nom_utilisateur''.
-Commencez par créer un nouvel utilisateur, qui portera par exemple le même nom que vous. Dans la ligne de commande ci-dessous, tapez (rappel : la partie « postgres=# » est l'invite de commande, il ne faut pas la retaper ! Ainsi, la commande ci-dessous requiert de taper « CREATE USER <nom_utilisateur>; », en remplaçant <nom_utilisateur> par un identifiant bien choisi) :
+Commencez par créer un nouvel utilisateur, qui portera par exemple le même nom que vous. Dans la ligne de commande ci-dessous, tapez (rappel : la partie « postgres=# » est l'invite de commande, il ne faut pas la retaper ! Ainsi, la commande ci-dessous requiert de taper « CREATE ROLE <nom_utilisateur>; », en remplaçant <nom_utilisateur> par un identifiant bien choisi) :
 <code>
-postgres=# CREATE USER <nom_utilisateur>;
+postgres=# CREATE ROLE <nom_utilisateur> LOGIN;
 </code>
 N'oubliez pas le point-virgule à la fin...
@@ Ligne 154: / Ligne 305: @@
 Donnez-lui la possibilité de créer de nouvelles bases de données :
 <code>
-postgres=# ALTER ROLE <nom_utilisateur> WITH CREATEDB;
+postgres=# ALTER ROLE <nom_utilisateur> CREATEDB;
 </code>
@@ Ligne 169: / Ligne 320: @@
 Attribuer un mot de passe à l'utilisateur pour qu'il puisse se connecter à la base (le ENCRYPTED permet l'utilisation de md5 dans le pg_hba.conf) :
 <code>
-postgres=# ALTER USER <nom_utilisateur> WITH ENCRYPTED PASSWORD 'mon_mot_de_passe';
+postgres=# ALTER ROLE <nom_utilisateur> WITH ENCRYPTED PASSWORD 'mon_mot_de_passe';
 </code>
@@ Ligne 175: / Ligne 326: @@
 <code>
 postgres=# \q
-postgres@ubuntu:~$ exit
+postgres@ubuntu:~$ psql nom_base_de_donnee
-nom_utilisateur@ubuntu:~$ psql nom_base_de_donnee
 </code>
 La ligne de commande est devenue :
 <code>
 nom_base_de_donnee=>
@@ Ligne 186: / Ligne 336: @@
 Notez la transformation du # en > : vous n'êtes plus superutilisateur...
+<note tip>Il peut être plus simple pour commencer d'utiliser la commande **createuser --interactive <nom-utilisateur>** en ligne de commande (hors psql) qui est interactive et vous demande les autorisations à y associer (LOGIN, SUPERUSER, CREATEDB, CREATEROLE…)</note>
 ==== Pour aller plus loin ====
@@ Ligne 191: / Ligne 342: @@
 === Méthode alternative pour créer un utilisateur ===
-Vous pouvez aussi créer vos utilisateurs directement en ligne de commande, depuis le terminal standard, mais en étant connecté en tant que postgres, bien sûr.
+Vous pouvez aussi créer vos utilisateurs directement en ligne de commande, depuis le terminal standard. Pour cela, vous allez d'abord autoriser les connexions avec mot de passe chiffré, puis vous vous connecterez en tant que postgres (l'administrateur par défaut de Postgres) et créerez un nouvel utilisateur.
 Cette méthode est beaucoup plus sûre que la précédente.
@@ Ligne 197: / Ligne 348: @@
 (Ce qui n'est pas le cas lorsque l'on emploie la méthode précédente)
-Il faut d'abord modifier le fichier de configuration pour autoriser les connexions via mot de passe chiffré :
+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 205: / Ligne 356: @@
 Les utilisateurs "avancés" noteront que le fichier est amplement commenté.
-Voir la [[http://www.postgresql.org/docs/8.2/static/index.html|documentation]] pour plus d'informations.
+Voir la [[https://www.postgresql.org/docs/8.2/static/index.html|documentation]] pour plus d'informations.
-<note important>Si vous odifiez les paramètres de la sécurité, avant de relancer postgresql pensez à donner un mot de passe à l'utilisateur postgres (voir plus bas)</note>
+<note important>Si vous modifiez les paramètres de la sécurité, avant de relancer postgresql pensez à donner un mot de passe à l'utilisateur postgres (voir plus bas)</note>
 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 228: / Ligne 379: @@
 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 /etc/init.d/postgresql restart
+$ sudo service postgresql restart
 </code>
 ou
-<code>
+<code bash>
-$ sudo pg_ctlcluster 9.1 main restart
+$ sudo pg_ctlcluster 9.5 main restart
 </code>
 On peut ensuite créer notre utilisateur :
-<code>
+<code bash>
-$ sudo -su postgres
+$ sudo -i -u postgres
-$ createuser -P <nom_utilisateur>
+$ createuser -P --interactive <nom_utilisateur>
 Enter password for new role:
 Enter it again:
@@ Ligne 249: / Ligne 400: @@
 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>
-Par précaution, on ajoute un mot de passe postgres :
+<note>
-(C'est un mot de passe POUR une connexion au serveur postgresql)
+Si vous avez recréé le groupe de bases de de données (cluster) avec les locales UTF8, il ne sera pas nécessaire de préciser l'option //-E UTF8// car la base modèle par défaut (//template1//) présente déjà cet encodage
-Les utilisateurs postgres n'ont a priori rien à voir avec les utilisateurs système, mais il peut être intéressant de garder un parallèle entre les deux :
-<code>
+L'option //-O <nom_utilisateur>//  est là pour préciser l'identité du propriétaire de la base <ma_database>.
-$ psql -d postgres -c "ALTER USER postgres WITH PASSWORD 'mot_de_passe'"
+</note>
+Ensuite, on peut se connecter via :
+<code bash>
+$ psql -U <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 :
-Il faut enfin relancer le serveur psql pour que les modifications de mots de passe soient prises en compte :
+<code bash>
-<code>
+$ psql -U <nom_utilisateur> <nom_database_de_l_utilisateur>
-$ sudo /etc/init.d/postgresql restart
+Mot de passe pour l’utilisateur <nom_utilisateur> :
 </code>
-Attention ! Le mot de passe à entrer est celui de postgres si vous êtes connecté sous ce nom-ci. (postgres a les droits d'administration standards)
+=== Commodités d'usage ===
+Le fichier pg_hba.conf tel que défini ci-avant exige qu'un mot de passe soit fourni pour autoriser la connexion d'un rôle postgresql (dans notre cas <nom_utilisateur>). Si vous administrez une base de données et que vous êtes amenés à effectuer fréquemment des opérations, il peut être intéressant de se faciliter les connexions.
-Ensuite, on peut se connecter via :
+== Les variables d'environnement ==
-<code>
+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.
-$ psql -U <nom_utilisateur>
+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//).
-Mot de passe pour l'utilisateur <nom_utilisateur> :
+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é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 à
+<code bash>
+  psql <nom_database>
+</code>
+et si vous définissez PGDATABASE, votre connexion express sera simplement
+<code bash>
+  psql
 </code>
-ou, si le nom de la base de l'utilisateur n'est pas le même que celui de l'utilisateur :
+<note>
-<code>
+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.
-$ psql -U <nom_utilisateur> <nom_database_de_l_utilisateur>
+</note>
-Mot de passe pour l'utilisateur <nom_utilisateur> :
+== Le fichier .pgpass ==
+Une autre manière, ou une manière complémentaire, de se simplifier les connexions est possible avec la définition du fichier [[http://docs.postgresqlfr.org/9.0/libpq-pgpass.html|~/.pgpass]].
+Ce fichier n'existe pas par défaut, il vous faut donc le créer.
+  nano ~/.pgpass
+Son contenu est simplement défini par les chaînes de connexions à utiliser.
+  localhost:5432:*:nomutilisateur:motdepasse
+Ceci indiquera à votre client psql que l'utilisateur //nomutilisateur// accompagné du mot de passe //modepasse// est à utiliser pour la connexion à toutes //*// les bases de données de votre serveur //localhost// sur le port //5432// (port standard postgresql).
+Ce fichier étant défini, la saisie du mot de passe n'est plus effectuées par l'utilisateur car il est directement fourni par les paramètres contenu dans ce fichier.
+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>
-=== Tuning ===
+=== Personnalisation ===
-Le tuning d'un serveur postgreSQL se fait grâce au fichier **postgresql.conf** généralement positionné dans **/etc/postgresql/8.3/main/**
+Le tuning d'un serveur postgreSQL se fait grâce au fichier **postgresql.conf** généralement positionné dans **/etc/postgresql/X.x/main/**, X.x étant la version utilisée de postgreSQL.
 Plusieurs paramètres sont faciles à modifier et peuvent apporter un gain de performance important:
@@ Ligne 294: / Ligne 485: @@
 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 ===
+Cette notion a été effleurée car les utilisateurs dont il a été question dans les lignes ci-avant sont des rôles au sens de postgresql.
+Les droits affectés à ces rôles permet une gestion fine des autorisations d'accès aux différents objets de la base de données (tables, vues, fonctions, champs...)
+La  [[https://docs.postgresql.fr/9.1/user-manag.html|documentation postgresql]] est explicite à ce sujet, le mieux est de la consulter !
+=== Gestion des connexions ===
+Postgresql permet en amont des droits attribués aux rôles sur la base de données, une gestion des connexions. Celle ci est définie dans les fichiers de configuration ///etc/postgresql/x.x/main/pg_hba.conf // et ///etc/postgresql/x.x/main/postgresql.conf// dans le chapitre //# CONNECTIONS AND AUTHENTICATION//
+La documentation complète est disponible à ces adresses
+//[[https://docs.postgresql.fr/9.1/runtime-config-connection.html|/etc/postgresql/x.x/main/postgresql.conf]]//
-=== Rôles, privilèges ===
+//[[https://docs.postgresql.fr/9.1/client-authentication.html|/etc/postgresql/x.x/main/pg_hba.conf]]//
->En cours de rédaction ... On attend !!!!!!!!!!!!!
-=== Gestion des connexions ===
->En cours de rédaction ...
 === ajout de contributions ===
-Différentes contributions sont disponibles (cf la [[http://docs.postgresqlfr.org/8.4/contrib.html|documentation PostgreSQL]]). Leur installation (et désinstallation) est facilitée par des scripts que l'on peut retrouver dans **/usr/share/postgresql/*.*/contrib/*.sql**
+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 bash>
+sudo apt-get install postgresql-contrib
+Les paquets supplémentaires suivants seront installés :
+  libossp-uuid16 postgresql-contrib-9.1
+</code>
+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 bash>
+$ psql -U <nom_utilisateur> <nom_database> -f /usr/share/postgresql/9.1/extension/<nom_extension>.sql
+</code>
+=== Créer un utilisateur en lecture seul ===
+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 sql>
+ALTER USER <nom_utilisateur>  set default_transaction_read_only = on;
+</code>
+===== Installation de la cartouche spatiale PostGIS =====
+Reportez-vous à la page [[:postgis]]
+===== Clients graphiques =====
+==== DBeaver ====
+Si vous utilisez d'autres types de base de données, cette solution a l'avantage de les supporter,\\ voir la page [[:dbeaver]]
+==== Le client pgadmin4 ====
+Outils web graphique d'administration de vos bases
+<code bash>
-===== Clients graphiques =====
+sudo apt install postgresql-common
+sudo sh /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
+sudo apt update
+sudo apt install postgresql-11 pgadmin4
+</code>((source (EN) [[https://wiki.postgresql.org/wiki/Apt]]))
+puis simplement lancer pgadmin4 via le lanceur qui a été ajouté ou en CLI
 ==== Le client pgadmin3 ====
@@ Ligne 332: / Ligne 548: @@
 PgAdmin III est un outil graphique d'administration de votre serveur PostgreSQL. L'application pgAdmin III peut être utilisé pour administrer les serveurs PostgreSQL 7.3 et les versions supérieures. PgAdmin III existe pour toutes les plateformes dont, évidemment, Ubuntu, KUbuntu, XUbuntu.
 PgAdmin III a été conçu pour répondre aux besoins de tous les utilisateurs, depuis la rédaction de simple requêtes SQL au développement complexe de base de données. L'interface graphique supporte toutes les fonctionnalités de PostGreSQL et permet une administration simple. L'application inclut aussi un éditeur de requête avec coloration syntaxique, un éditeur de code, un agent de gestion de tâche automatique, un support pour les réplication via Slony-I et bien d'autres fonctionnalités.
 == Installation du client pgadmin3 ==
@@ Ligne 341: / Ligne 557: @@
 Cela va également installer les paquets suivants :
   * libpq4
   * libwxbase2.6-0
   * libwxgtk2.6-0
   * pgadmin3-data
@@ Ligne 351: / Ligne 567: @@
 == Lancement du client pgadmin3 ==
 Sous Ubuntu, pour lancer PgAdmin III, aller dans le menu **Applications** => **programmation** => **pgAdmin III**
 <note>**Remarque:**
@@ Ligne 362: / Ligne 578: @@
 <code>ssh -L 5555:localhost:5432 utilisateur@adresse_du_serveur</code>
-Le port 5555 correspond au port de redirection sur le port local (vous pouvez en choisir un autre)
+Le port 5555 correspond au port de redirection sur le port local (vous pouvez en choisir un autre). Le port 5432 correspond au port d'écoute de postgres (ne pas modifier).
-Le port 5432 correspond au port d'écoute de postgres (ne pas modifier)
+Cet exemple s'entend en connexion ssh sur le port standard 22.
-Cet exemple s'entend en connexion ssh sur le port standard 22
+Si vous avez modifié le port d'écoute ssh (et vous devriez le faire pour des raisons de sécurité), utilisez cette commande : (1444 est un exemple, vous pouvez en chosir un autre).
-Si vous avez modifié et vous devriez le faire pour des raisons de sécurité le port d'écoute ssh, utilisez cette commande : (1444 est un exemple, vous pouvez en chosir un autre)
 <code>ssh -L 5555:localhost:5432 utilisateur@nom_du_serveur -p 1444</code>
-Le serveur vous demandera votre mot de passe
+Le serveur vous demandera votre mot de passe. Ouvrez maintenant pgadmin3
-Ouvrez maintenant pgadmin3
-Nom : nom_de_votre_database
+<code>Nom : nom_de_votre_database
 Hôte : localhost
+Port TCP : 5555 (selon l'exemple ci-dessus)</code>
-Port TCP : 5555 (selon l'exemple ci-dessus)
 Vous devriez être maintenant connecté
@@ Ligne 397: / Ligne 606: @@
 | [[apt://phppgadmin|{{ :apt.png |Installer en 1 clic.}}]] |
+Réaliser ensuite les opérations suivantes : FIXME (sous [[bionic]], pas de fichier //apache.conf// mais //config.inc.php// !
-<note>Sur mon installation, apache2/php5, pgsql n'était pas reconnu.  J'ai du rajouter la ligne ''extension=pgsql.so'' à la main (après celle sur mysql) dans le fichier ** /etc/php5/apache2/php.ini**. A confirmer ou infirmer.
+<code>
-Confirmé, chez moi idem. Et sous gutsy, comme pour phpmyadmin, il faut aussi créer le lien symbolique vers /usr/share/phppgadmin.
+sudo ln -s /etc/phppgadmin/apache.conf /etc/apache2/conf.d/phppgadmin.conf
+sudo mv /etc/apache2/conf.d/phppgadmin /etc/apache2/conf-available/phppgadmin.conf
-<code>sudo ln -s /etc/phppgadmin/apache.conf /etc/apache2/conf.d/phppgadmin.conf
+sudo a2enconf phppgadmin
+sudo service apache2 restart
 </code>
-puis redemarrer apache:
-<code>sudo /etc/init.d/apache2 reload
-</code>
-</note>
 === Lancement du client phppgadmin ===
 Lance votre navigateur Web préféré à l'adresse [[http://localhost/phppgadmin/]].
-<note>
-Si le lien ci-dessus ne fonctionne pas (ce que j'ai eu comme problème), créer le lien symbolique suivant :
-<code>
-sudo ln -s /usr/share/phppgadmin/ /var/www/
-</code>
-</note>
 === Utilisation du client phppgadmin ===
@@ Ligne 431: / Ligne 629: @@
 ====Connexions vers PHP====
-PHP 4 : [[:tutoriel/comment_installer_un_paquet|installer]] php4-pgsql.
+[[:tutoriel/comment_installer_un_paquet|installer|Installer le paquet]] [[apt://php-pgsql|php-pgsql]]
-PHP 5 : [[:tutoriel/comment_installer_un_paquet|installer]] php5-pgsql.
-<note>Il faut recharger Apache après avoir installé ce module : sudo /etc/init.d/apache2 reload</note>
-php5-pdo (voir la documentation officiel pour [[http://php.net/pdo|PDO]]) :
-<code>sudo pear install pecl-pdo_pgsql</code>
-PHP 5.2 : Cela se fait automatiquement.
-> FIXME : A compléter
-=====  Postgresql 8.3 =====
-Pour ceux n'ayant pas upgradé leur Ubuntu, ils peuvent installer PostgreSQL 8.3 grâce aux backports.
-<note warning>
-Il est important de remarquer que lors de l'installation, les bases de données sont créées en Unicode et qu'à cause de cela, il ne sera pas possible de créer une base de données dans un autre encodage.
-</note>
-La solution à ce problème est assez simple.
-. Faites un backup avec pg_dumpall
-<code>
-mkdir /tmp/backup
-chown postgres /tmp/backup
-cd /tmp/backup/
-su postgres
-pg_dumpall > backup.dmp
-</code>
-. Recréez les fichiers d'initialisation à un autre endroit avec POSIX comme locale. Cet autre endroit doit appartenir à postgres et avoir 700 comme permission.
-<note tip>Si vous utilisez un backport donnez le chemin complet vers initdb</note>
-<code>
- /usr/lib/postgresql/8.3/bin/initdb  --locale=POSIX -D/var/lib/postgresql/8.3-posix/main
-The files belonging to this database system will be owned by user "postgres".
-This user must also own the server process.
-The database cluster will be initialized with locale POSIX.
-The default database encoding has accordingly been set to SQL_ASCII.
-The default text search configuration will be set to "english".
-fixing permissions on existing directory /var/lib/postgresql/8.3-posix/main ... ok
-creating subdirectories ... ok
-selecting default max_connections ... 100
-selecting default shared_buffers/max_fsm_pages ... 24MB/153600
-creating configuration files ... ok
-creating template1 database in /var/lib/postgresql/8.3-posix/main/base/1 ... ok
-initializing pg_authid ... ok
-initializing dependencies ... ok
-creating system views ... ok
-loading system objects' descriptions ... ok
-creating conversions ... ok
-creating dictionaries ... ok
-setting privileges on built-in objects ... ok
-creating information schema ... ok
-vacuuming database template1 ... ok
-copying template1 to template0 ... ok
-copying template1 to postgres ... ok
-WARNING: enabling "trust" authentication for local connections
-You can change this by editing pg_hba.conf or using the -A option the
-next time you run initdb.
-Success. You can now start the database server using:
-    /usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3-posix/main
-or
-    /usr/lib/postgresql/8.3/bin/pg_ctl -D /var/lib/postgresql/8.3-posix/main -l logfile start
-</code>
-. Changez dans le fichier de configuration le data_directory
-<code>
-cd /etc/postgresql/8.3/main
-sudo nano postgresql.conf
-</code>
-Dans postgresql.conf, vous devez avoir la ligne suivante
-<code>
-data_directory = '/var/lib/postgresql/8.3-posix/main'
-</code>
-. Relancez postgresql  après avoir créer les liens des certificates et clefs (pour le SSL)
-<code>
-sudo -s
-Password:
-cd /var/lib/postgresql/8.3-posix/main
-ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
-ln -s /etc/postgresql-common/root.crt root.crt
-ln -s /etc/ssl/private/ssl-cert-snakeoil.key server.key
-/etc/init.d/postgresql-8.3 restart
-</code>
-. restaurez le backup
-<code>
-sudo -s -u postgres
-/usr/lib/postgresql/8.3/bin/psql -f /tmp/backup/backup.dmp
-</code>
-. Si vous avez utilisé les backports: Postgreql 8.3 fonctionne sur le port 5433 et la version 8.2 sur le port 5432. Vous souhaitez pouvoir utiliser par défaut la version 8.3, crééez le fichier ~/.postgresqlrc, il devrait contenir
-<code>
-.3     localhost:5433  *
-</code>
-<note warning>Le fait que le server tourne en unicode et que les bases de données soient en latin1 amène des petits bugs, migrez au plus tôt  vos bases de données vers unicode </note>
+<note>Il faut recharger Apache après avoir installé ce module : sudo service apache2 reload</note>
+Activer ce module
+<code>sudo phpenmod pdo_pgsql</code>
+Redémarrer apache2
+====Pool de connexion : PgBouncer====
+[[pgbouncer|PgBouncer]]
 ===== Récupération du contenu d'une base de données après un crash serveur =====
@@ Ligne 562: / Ligne 665: @@
 Maintenant, connectez-vous en tant que postgres, et effectuez la sauvegarde.
 <code>
-sudo su postgres                                                # change l'utilisateur courant (root) pour l'utilisateur postgres
+sudo -i -u postgres                                                # change l'utilisateur courant (root) pour l'utilisateur postgres
 pg_dump -f /chemin/de/sauvegarde/choisi/NOM_BDD.sql NOM_BDD     # effectue la sauvegarde de la base de données
 </code>
@@ Ligne 582: / Ligne 685: @@
 <code>
-sudo su postgres                  # change l'utilisateur courant (root) pour l'utilisateur postgres
+sudo -i -u postgres                  # change l'utilisateur courant (root) pour l'utilisateur postgres
 dropdb NOM_BDD                    # efface la base de données existante
 createdb --owner=NOM_APPLI --encoding=utf-8 NOM_BDD         # recréée une base de données vide
@@ Ligne 588: / Ligne 691: @@
 </code>
-<note tip>L'option ''--owner=NOM_APPLI'' de la troisième commande suppose que vous avez configuré un utilisateur système pour votre application</note>
+<note tip>L'option ''%%--%%owner=NOM_APPLI'' de la troisième commande suppose que vous avez configuré un utilisateur système pour votre application</note>
+===== Formations =====
+La société Dalibo mets ses support de cours à disposition ([[https://creativecommons.org/licenses/by-nc-sa/2.0/fr/|CC BY NC SA]]):\\
+[[https://www.dalibo.com/formations]]\\
+sous l'intitulé de chaque formation, vous trouvez les liens pour télécharger le support au format [[pdf|PDF]] ou [[epub|EPUB]]\\
+exemple: lien directe au support PDF de la formation //DBA1 - PostgreSQL Administration// : [[https://dali.bo/dba1_pdf]]
+===== 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>
 ----
-//Contributeurs : [[utilisateurs:yannick_LM|Yannick]], [[utilisateurs:sparky|Sparky]], [[utilisateurs:elemmire|Elemmire]], [[utilisateurs:aldian|Aldian]], [[utilisateurs:fred|Fred]]. //
+//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]] //