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 !!
Via un client graphique
Ouvrez DBeaver.
Cliquez sur la petite icône « prise » en haut à gauche « Nouvelle connexion ».
Choisissez « PostgreSQL » puis faites suivant.
Renseignez les éléments suivants :
- Host : adresse IP de la machine sur laquelle est installé PostgreSQL (si c’est sur votre ordinateur alors renseignez
127.0.0.1
) - Port : le port de postgreSQL (par défaut
5432
mais si vous avez renseignez un autre port lors de l’installation, indiquez le port spécifié). - Database : le nom de votre base de données (
postgres
si c’est votre premier démarrage). - User :
postgres
- Password : le mot de passe spécifié lors de l’installation.
- Pensez à cocher :
- Save password locally : pour ne pas retaper votre mot de passe à chaque connexion.
- Show all database : pour voir toutes les BDD du cluster.
Laissez les autres paramètres tels quels puis cliquez sur « Terminer».
Pour vous connecter, double-cliquez sur la connexion nouvellement créée dans le panneau de gauche. Vous voilà maintenant connecté à votre serveur, félicitation !
Vous pouvez maintenant regarder ce que contient votre serveur.
Pour lancer des requêtes SQL :
- Déployez votre connexion pour l’activer (avec la petite flèche avant son nom).
- Sélectionnez une base de données en cliquant dessus.
- Cliquez sur « Editeur SQL » puis » Editeur SQL » ou sur l’icône « parchemin ».
Ouvrez pgAdmin.
Cliquez sur « Objet » puis « Créer » puis « Serveur ».
Renseignez les éléments suivants :
- Général – Nom : le nom par lequel vous souhaitez voir votre connexion désignée dans pgAdmin.
- Connexion – Nom d’hôte : adresse IP de la machine sur laquelle est installé PostgreSQL (si c’est sur votre ordinateur alors renseignez
127.0.0.1
) - Connexion – Port : le port de postgreSQL (par défaut
5432
mais si vous avez renseignez un autre port lors de l’installation, indiquez le port spécifié). - Connexion – Nom d’utilisateur :
postgres
- Connexion – Mot de passe : le mot de passe spécifié lors de l’installation.
- Pensez à cocher :
- Enregistrer le mot de passe : pour ne pas retaper votre mot de passe à chaque connexion.
Laissez les autres paramètres tels quels puis cliquez sur « connexion ».
Pour vous connecter, double-cliquez sur la connexion nouvellement créée dans le panneau de gauche. Vous voilà maintenant connecté à votre serveur, félicitation !
Vous pouvez maintenant regarder ce que contient votre serveur.
Pour lancer des requêtes SQL :
- Déployez votre connexion pour l’activer (avec la petite flèche avant son nom).
Sélectionnez une base de données en cliquant dessus. - Cliquez sur « Outil » puis « Éditeur de requêtes » ou sur l’icône « loupe avec SQL dedans » .
Ouvrez pgAdmin.
Cliquez sur « Fichier » puis « Ajouter un serveur » ou sur la petite icône « prise » en haut à gauche.
Renseignez les éléments suivants :
- Nom : le nom par lequel vous souhaitez voir votre connexion désignée dans pgAdmin.
- Hôte : adresse IP de la machine sur laquelle est installé PostgreSQL (si c’est sur votre ordinateur alors renseignez
127.0.0.1
) - Port TCP : le port de postgreSQL (par défaut
5432
mais si vous avez renseignez un autre port lors de l’installation, indiquez le port spécifié). - Nom d’utilisateur :
postgres
- Mot de passe : le mot de passe spécifié lors de l’installation.
Laissez les autres paramètres tels quels puis cliquez sur « connexion ».
Pour vous connecter, double-cliquez sur la connexion nouvellement créée dans le panneau de gauche. Vous voilà maintenant connecté à votre serveur, félicitation !
Vous pouvez maintenant regarder ce que contient votre serveur.
Pour lancer des requêtes SQL :
- Double cliquez sur votre connexion pour l’activer.
- Sélectionnez une base de données en cliquant dessus
- Cliquez sur « Outil » puis « Éditeur de requêtes » ou sur l’icône « loupe avec SQL dedans » .
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 actuellementPROMPT1
,PROMPT2
etPROMPT3
= 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.
java.com
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.
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
(danspublic
) - Les vues :
geography_columns
,geometry_columns
,raster_columns
,raster_overview
(danspublic
)
- La table :
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