Différences

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

Lien vers cette vue comparative

Les deux révisions précédentes Révision précédente
Prochaine révision
Révision précédente
postgresql [Le 24/02/2021, 19:25]
78.242.210.192 [Supprimer l'utilisateur Postgres du greeter sous xubuntu 14.04.1] correction de 'nommé-le' en 'nommez-le'
postgresql [Le 24/03/2022, 17:14] (Version actuelle)
bcag2 [Introduction] ajout lien wpfr
Ligne 1: Ligne 1:
 +
  
 {{tag>​sgbd serveur réseau BROUILLON}} {{tag>​sgbd serveur réseau BROUILLON}}
Ligne 7: Ligne 8:
  
 ===== Introduction ===== ===== 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 ===== ===== 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é [[https://​www.postgresql.fr/​|postgresql francophone]] 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]].
  
Ligne 60: Ligne 61:
 Confirmation de l'​existence d'un dossier **Datas/** déjà existant.\\ Confirmation de l'​existence d'un dossier **Datas/** déjà existant.\\
 Lancer l'​installation. 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. 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 82: Ligne 96:
 Vous devriez obtenir quelque chose comme : Vous devriez obtenir quelque chose comme :
 <​code>​ <​code>​
-psql (9.5.10)+psql (13.1-1)
 Type "​help"​ for help. Type "​help"​ for help.
  
Ligne 100: Ligne 114:
 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) 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 ==== 
-<​code>​ +\h pour l'​aide-mémoire des commandes SQL| 
-        ​\h pour l'​aide-mémoire des commandes SQL +\? pour l'​aide-mémoire des commandes psql| 
-        \? pour l'​aide-mémoire des commandes psql +\g ou point-virgule en fin d'​instruction pour exécuter la requête| 
-        \g ou point-virgule en fin d'​instruction pour exécuter la requête +\q pour quitter
-        \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 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>​ </​code>​
 ====Supprimer l'​utilisateur Postgres du greeter sous xubuntu 14.04.1==== ====Supprimer l'​utilisateur Postgres du greeter sous xubuntu 14.04.1====
Ligne 198: Ligne 244:
  
   * 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   Version Cluster ​  Port Status Owner    Data directory ​                    Log file
   9.1     ​main ​     5432 online postgres /​var/​lib/​postgresql/​9.1/​main ​      /​var/​log/​postgresql/​postgresql-9.1-main.log   9.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
  
Ligne 212: Ligne 258:
  
   * Démarrage   * Démarrage
 +
   pg_ctlcluster 9.1 main start   pg_ctlcluster 9.1 main start
  
 Les bases systèmes seront alors conformes aux locales système Les bases systèmes seront alors conformes aux locales système
-  ​ +<​code>​ 
-  psql -l+psql -l
                                   List of databases                                   List of databases
     Name    |  Owner   | Encoding |   ​Collate ​  ​| ​   Ctype    |   ​Access privileges ​       Name    |  Owner   | Encoding |   ​Collate ​  ​| ​   Ctype    |   ​Access privileges ​  
Ligne 225: Ligne 272:
   template1 | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/​postgres ​         +   template1 | postgres | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/​postgres ​         +
             |          |          |             ​| ​            | postgres=CTc/​postgres             |          |          |             ​| ​            | postgres=CTc/​postgres
 +</​code>​
  
  
Ligne 236: Ligne 283:
 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''​. 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>​ <​code>​
-postgres=# CREATE ​USER <​nom_utilisateur>;​+postgres=# CREATE ​ROLE <​nom_utilisateur> ​LOGIN;
 </​code>​ </​code>​
 N'​oubliez pas le point-virgule à la fin... N'​oubliez pas le point-virgule à la fin...
Ligne 245: Ligne 292:
 Donnez-lui la possibilité de créer de nouvelles bases de données : Donnez-lui la possibilité de créer de nouvelles bases de données :
 <​code>​ <​code>​
-postgres=# ALTER ROLE <​nom_utilisateur> ​WITH CREATEDB;+postgres=# ALTER ROLE <​nom_utilisateur>​ CREATEDB;
 </​code>​ </​code>​
  
Ligne 260: Ligne 307:
 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) : 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>​ <​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>​ </​code>​
  
Ligne 276: Ligne 323:
 Notez la transformation du # en > : vous n'​êtes plus superutilisateur... 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 ==== ==== Pour aller plus loin ====
Ligne 295: Ligne 343:
  
 Les utilisateurs "​avancés"​ noteront que le fichier est amplement commenté. 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 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>​ <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>​
Ligne 404: Ligne 452:
  
 === Personnalisation === === Personnalisation ===
-Le tuning d'un serveur postgreSQL se fait grâce au fichier **postgresql.conf** généralement positionné dans **/​etc/​postgresql/​9.1/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: Plusieurs paramètres sont faciles à modifier et peuvent apporter un gain de performance important:
Ligne 423: Ligne 471:
 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...) 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  [[http://​docs.postgresql.fr/​9.1/​user-manag.html|documentation postgresql]] est explicite à ce sujet, le mieux est de la consulter !+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 === === Gestion des connexions ===
Ligne 431: Ligne 479:
 La documentation complète est disponible à ces adresses La documentation complète est disponible à ces adresses
  
-//[[http://​docs.postgresql.fr/​9.1/​runtime-config-connection.html|/​etc/​postgresql/​x.x/​main/​postgresql.conf]]//​+//[[https://​docs.postgresql.fr/​9.1/​runtime-config-connection.html|/​etc/​postgresql/​x.x/​main/​postgresql.conf]]//​
  
-//[[http://​docs.postgresql.fr/​9.1/​client-authentication.html|/​etc/​postgresql/​x.x/​main/​pg_hba.conf]]//​+//[[https://​docs.postgresql.fr/​9.1/​client-authentication.html|/​etc/​postgresql/​x.x/​main/​pg_hba.conf]]//​
  
  
Ligne 457: Ligne 505:
 ALTER USER <​nom_utilisateur> ​ set default_transaction_read_only = on; ALTER USER <​nom_utilisateur> ​ set default_transaction_read_only = on;
 </​code>​ </​code>​
- 
- 
- 
- 
- 
- 
- 
- 
  
 ===== Installation de la cartouche spatiale PostGIS ===== ===== Installation de la cartouche spatiale PostGIS =====
-====Ce qu'est postgis==== +Reportez-vous à la page [[:​postgis]]
-[[http://​postgis.refractions.net/​|Postgis]] est une extension de postgresql qui permet ​la gestion d'​objets géographiques. Concrètement la cartouche spatiale postgis ajoute au serveur : +
-  *  la possibilité de définir le type de champ //​geometry//​  +
-  * l'​ensemble des fonctions permettant les traitements géographiques +
-  * la base de référence des projections géographiques //​spatial_ref_sys//​ +
-  * la base de gestion des attributs géographiques des tables //​geometry_columns//​ +
-====Installation==== +
-Installer le paquet correspondant à votre version de postgres, exemples: +
-  * [[apt://​postgresql-10-postgis-2.4|postgresql-10-postgis-2.4]] sous [[:​bionic]] +
-  * [[apt://​postgresql-9.1-postgis|postgresql-9.1-postgis]]+
  
-<​code>​ +===== Clients graphiques =====
-Les paquets supplémentaires suivants seront installés :​  +
-  libgeos-3.2.2 libgeos-c1 libproj0 postgis proj-data +
-</​code>​+
  
-Les scripts nécessaires à la capacitation géographique de postgresql sont installé dans le dossier\\ +==== DBeaver ​==== 
- // /​usr/​share/​postgresql/​9.1/​contrib/​postgis-1.5///​ ou\\ +Si vous utilisez ​d'autres types de base de données, ​cette solution a l'​avantage ​de les supporter,\\ voir la page [[:dbeaver]]
- // /​usr/​share/​postgresql/​10/​contrib/​postgis-2.4///​ +
- +
-====Donner à une base la capacité géographique==== +
- +
- +
-<​note>​ +
-Les scripts donnés ci-après prennent pour hypothèse qu'un nouvel utilisateur a été créé avec la méthode [[postgresql#​pour_aller_plus_loin|recommandée]] +
-</​note>​ +
- +
-Tout d'abord, créer la base de données ​en question +
-  createdb -U <​nom_utilisateur>​ <​nom_base_geo>​ +
-Puis on utilser le rôle postgres (super user postgresql) +
-  $ sudo -i -u postgres  +
-pour exécuter dans cet ordre les scripts suivants : +
- +
-  psql -f /​usr/​share/​postgresql/​9.1/​contrib/​postgis-1.5/​postgis.sql ​ <​nom_base_geo>​ +
-  psql -f /​usr/​share/​postgresql/​9.1/​contrib/​postgis-1.5/​spatial_ref_sys.sql ​ <​nom_base_geo>​ +
- +
-puis on quitte le shell postgres +
-  exit +
-   +
-<note tip> +
-Si vous êtes amenés à créer plusieurs bases géographiquesil peut être intéressant ​de créer une base qui serve de modèlepar exemple //​template_postgis//​ +
-Il est ensuite très facile de créer une base géographique en appelant ce modèle lors de la création d'un base +
-  createdb -U <​nom_utilisateur>​ -T template_postgis <​nom_base_geo>​ +
-</​note>​ +
- +
-Pour faire des imports et des exports (car la fonction sur phppgadmin est bancale) ​: +
- +
-  $ sudo -i -u postgres +
- +
-export : +
-  pg_dump <​nom_de_la_base>​ > nom_de_la_base.pgdump +
- +
-import: +
-  cat  nom_de_la_base.pgdump | psql -d  nom_de_la_base +
- +
-===== Clients graphiques =====+
  
  
Ligne 690: Ligne 680:
 exemple: lien directe au support PDF de la formation //DBA1 - PostgreSQL Administration//​ : [[https://​dali.bo/​dba1_pdf]] 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]],​[[utilisateurs:​xavierjm|XAVIER_jean-marie]]. // //​Contributeurs : [[utilisateurs:​yannick_LM|Yannick]],​ [[utilisateurs:​sparky|Sparky]],​ [[utilisateurs:​elemmire|Elemmire]],​ [[utilisateurs:​aldian|Aldian]],​ [[utilisateurs:​fred|Fred]],​[[utilisateurs:​xavierjm|XAVIER_jean-marie]]. //
  • postgresql.1614191106.txt.gz
  • Dernière modification: Le 24/02/2021, 19:25
  • par 78.242.210.192