PostgreSQL – Tout comprendre – 3/3 – Connexion et structuration

Voici maintenant de quoi administrer sereinement votre serveur.

Connexion à PostgreSQL

Bon ! PostgreSQL est installé, nous allons pouvoir entrez dans le vif du sujet : accédons à nos bases.

Pour rappel, nous sommes dans une architecture client-serveur. Ce qui veut dire que pour accéder aux données présentes dans PostgreSQL nous devons utiliser un logiciel spécialisé (le client) qui va contacter PostgreSQL (le serveur) pour faire ce qu’on souhaite.

Voici quelques logiciels clients :

• PSQL : fournit avec PostgreSQL : client en ligne de commande
• PgAdmin : fournit avec PostgreSQL : client graphique
• DBeaver : indépendant, celui que je recommande
• Omnidb : indépendant, très bon également

Je fais un peu de pub pour le client graphique que j’utilise : DBeaver.
Je n’ai pas de lien avec le développeur mais je trouve ce client particulièrement efficace.
Il peut se connecter à plusieurs type de base de données et en plus d’avoir un développement dynamique et en constante évolution, il est particulièrement stable et plein d’options particulièrement pratiques.
Alors allez-y, c’est gratuit !!

Download

Via un client graphique

Via PSQL

L’intérêt d’un client graphique est de pouvoir visualiser votre base de données. Mais si vous êtes « un vrai », vous pouvez utiliser PSQL, un autre client entièrement en ligne de commande. Voici comment faire.

Ouvrez l’invite de commande Windows (recherchez « cmd » dans le menu démarrer). Si vous êtes sous linux, les lignes de commande ne devraient pas vous faire trop peur et sont presque identiques à windows.

# Ouvrir PSQL et lui passer les paramètres de connexion
psql -h 127.0.0.1 -p 5432 -U postgres -d postgres -W

Mais qu’est-ce qu’on fait exactement là ?

D’abord on ouvre le programme « PSQL.exe » en l’appelant, tout simplement. Ceci fonctionne car lors de l’installation, le chemin des exécutables est placé dans le « PATH » (le fonctionnement du « PATH » est expliqué plus bas).

Ensuite on lui envoi tout une série de paramètres qui vont indiquer à PSQL sur quels éléments il doit se baser pour démarrer. Pour indiquer un paramètre, il faut l’appeler avec un mot-clé puis indiquer la valeur de celui-ci.
Par exemple : --parametre=valeur ou -p valeur.

Voici quelques paramètres utiles avec un exemple de valeur :

• --host=127.0.0.1 : hôte du serveur.
• --port=5432 : port du serveur.
• --username=postgres : utilisateur avec lequel on souhaite se connecter.
• --dbname=postgres : base à laquelle on souhaite se connecter.
• --password : force psql à demander un mot de passe.
• --list : liste les BDD du serveur puis quitte PSQL.
• --log-file=nomfichier : Écrit tous les résultats des requêtes dans le fichier « nomfichier ».
• --version : indique la version du serveur PostgreSQL.

On peut maintenant se connecter à notre serveur avec quelques paramétrages spécifiques. Lorsque vous ne spécifiez pas un argument, celui-ci prend alors la valeur par défaut stockée dans les variables d’environnement (voir plus bas).

Méta-commandes

Une fois connecté, vous pouvez utiliser des méta-commandes (qui ne sont pas vraiment des requêtes SQL), en voici quelques une

• \l = liste des bases
• \dx = liste de donnée en rapport avec x :
• \dt = liste des tables
• \dv = liste des vues
• \ds = liste des séquences
• \dp = liste des tables, vues et séquences avec les droits associés = \z
• \di = liste des index
• \dg = liste des rôles groupes
• \du = liste des rôles utilisateurs
• \dn = liste des schémas
• \dx = liste des extensions
• \c = établi une nouvelle connexion à un serveur. Il est possible de spécifier les paramètres précédemment utilisés, par exemple :
  • \c ma_base mon_user 127.0.0.1 5432
  • \c « host=localhost port=5432 dbname=mydb »
  • \c ma_base : se connecte à ma_base avec les paramètre utilisé lors de la première connexion : on change simplement de base au sein du même serveur.
• \copy = permet de copier des données, par exemple :
  • \copy ma_table from delimiter as ';' mon_fichier.csv = importer le contenu de mon_fichier.csv dans ma_table.
  • \copy ma_table to delimiter as ';' mon_fichier.csv = importer le contenu de m_table dans le fichier mon_fichier.csv.
  • \copy ma_requete to delimiter as ';' mon_fichier.csv = copier le résultat de ma_requete dans le fichier mon_fichier.csv.
• \h commande = aide sur la commande spécifiée.
• \i mon_fichier = lit un fichier comme s’il avait été tapé dans psql.
• \password utilisateur = change le mot de passe de l’utilisateur spécifié.
• \s mon_fichier = sauvegarde l’historique des commande de psql dans un fichier.
• \q = quitter
• \? = affiche l’aide sur les méta-commandes.

L’environnement

Voici quelques astuces concernant l’environnement logiciel entourant l’utilisation de PostgreSQL.

Les variables :

Vous pouvez utiliser des variables avec PSQL, se sont de simples paires nom/valeur.

Pour définir une variable :

\set ma_variable ma_valeur

Pour afficher une variable :

\echo ma variable

Quelques variables toujours présentes :

• DBNAME = BDD à laquelle vous êtes connecté
• HOST = hôte du serveur auquel vous êtes connecté
• PORT = port du serveur auquel vous êtes connecté
• USER = utilisateur que vous utilisez actuellement
• PROMPT1, PROMPT2 et PROMPT3 = spécifications sur ce à quoi doit ressembler l’invite psql.

Les variables d’environnement :

Ces variables sont stockées par le système d’exploitation (Windows, Linux, UNIX) et sont ainsi accessibles depuis n’importe quel logiciel. Vous pouvez modifier la valeur de ces variables. Dans notre cas, l’installation de PostgreSQL ajoute les variables suivantes aux variables d’environnement :

• PGHOST : host de connexion (par défaut : « 127.0.0.1 »).
• PGPORT : port de connexion (par défaut : port défini lors de l’installation).
• PGDATABASE : BDD à laquelle se connecter (par défaut : « postgres »).
• PGUSER : utilisateur à utiliser pour se connecter (par défaut : « postgres »).

Qu’est-ce que ce PATH ?

PATH est la variable système utilisée par le système d’exploitation pour localiser les fichiers exécutables indispensables depuis la ligne de commande ou la fenêtre de terminal.
La variable système PATH peut être configurée à l’aide de l’utilitaire système dans le Panneau de configuration sous Windows ou dans le fichier de démarrage du shell sous Linux et Solaris.

java.com

Ainsi, le répertoire dans lequel se trouve PSQL.exe se trouve dans la variable PATH. Ceci permet d’ouvrir ce programme lorsque vous tapez simplement « psql » dans l’invite de commande Windows.

Sachez que PATH est une variable d’environnement comme celles que nous avons vues précédemment.

Pour afficher cette variable et la modifier, rendez-vous dans « Panneau de configuration / Système / Avancé / Variables d’environnement / variables système : Variable Path ». Vous pourrez visualiser et modifier les autres variables d’environnement à cet endroit.

Contenu du serveur

Voici un descriptif des principaux éléments que vous allez trouver sur le serveur, utilisez pgAdmin ou DBeaver pour y accéder plus facilement.

Niveau serveur

• Bases de données : liste de toutes les bases de données de votre serveur.
• Tablespace : liste des emplacements de stockage disponible (fait référence  au dossier /data/base)
• Rôles de groupe : groupes d’utilisateurs.
• Rôles de connexion : utilisateurs de votre serveur PostgreSQL (par défaut il n’y a que « postgres »).

Niveau BDD

Détaillons un peu les éléments disponibles dans vos bases de données :

• Catalogues : ce sont des catalogues de fonctions disponibles
• Extensions : les extensions permettent d’ajouter des fonctionnalités à PostgreSQL (attention, certains clients listent les extensions dans les schémas car elles sont en effets installées dans le schéma public par défaut).
• Schéma : sorte de dossier dans lequel sont stockées vos données avec d’autres éléments.

Niveau Schéma

Détaillons encore un peu plus et regardons l’intérieur d’un schéma :

• Fonctions : ce sont des morceaux de code permettant de réaliser des calculs de façon simplifiés.
• Séquences : objets s’incrémentant automatiquement et permettant d’avoir une séquence logique dans une colonne lors de l’ajout de nouvelles données.
• Tables : tables contenant vos données.
• Fonctions trigger : fonctions spécifiques ne s’appliquant que lors de la modification des données (ajout, suppression, mise à jour).
• Vues : tables virtuelles créées à partir d’une formule permettant de ne faire remonter que certaines données à partir des données des autres tables.

Niveau table

Aller, dernier niveau : le contenu d’une table :

• Colonnes : liste des colonnes de la table
• Contraintes : ensemble de règles s’appliquant sur votre table (par exemple pour empêcher une colonne de comporter des valeurs nulles).
• Index : objets permettant à PostgreSQL d’aller plus vite (beaucoup beaucoup plus vite) lors de la lecture de vos tables.
• Triggers : objets déclenchant les fonctions trigger lors de la modification des données (ajout, suppression, mise à jour).
• Règles : ensemble de règles s’appliquant sur votre table.

Extensions

Prochainement

Conclusion

Nous avons maintenant une bonne vision d’ensemble de la structuration d’une base PostreSQL.

Vous avez vu qu’avec pgAdmin, nous n’avons encore pas utilisé de SQL. Pour créer un objet, il vous suffit de faire un clic droit et de cliquer sur « ajouter XX ». Vous pouvez ainsi ajouter un schéma, une table, une colonne…

Je vous recommande d’ajouter tout de suite l’extension « Postgis » qui permettra à votre serveur de gérer les données spatiales. Je vous montrerai quelques exemples d’utilisation.

Sachez que par défaut, les objets suivant sont créés :

• Le rôle de connexion : postgres
• La BDD : postgres
• Le schéma : public
• Si vous avez ajouté l’extension Postgis :
  • La table : spatiale_ref_sys (dans public)
  • Les vues : geography_columns, geometry_columns, raster_columns, raster_overview (dans public)

---

Maintenant que vous savez comment paramétrer correctement votre serveur, nous pouvons passer à la suite : les données. Et pour cela, nous allons parler SQL.

Sommaire général

Voici le sommaire général du cours :

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQL tuto