Gestion des données avec PostgreSQL

Introduction

PostgreSQL est un système de gestion de base de données relationnelle et objet open source (en anglais object-relational database management system).

Site officiel

Vous trouverez le site officiel de PostgreSQL à l’adresse suivante : http://www.postgresql.org/

Je vous recommande aussi d’aller faire un tour sur le site de la communauté francophone de PostgreSQL sur : http://postgresql.fr/

Wikipédia

Je vous invite à regarder les articles français ou anglais de la célèbre encyclopédie Wikipédia.

Les fonctionnalités de PostgreSQL

Le site francophone http://www.postgresql.fr/ dans la préface de la documentation de PostgreSQL liste les fonctionnalités de PostgreSQL suivantes :

  • requêtes complexes
  • clés étrangères
  • triggers
  • vues
  • intégrité transactionnelle
  • contrôle des versions concurrentes (MVCC ou multiversion concurrency control)

De plus, PostgreSQL™ peut être étendu par l’utilisateur de multiples façons. En ajoutant, par exemple :

  • de nouveaux types de données
  • de nouvelles fonctions
  • de nouveaux opérateurs
  • de nouvelles fonctions d’agrégat
  • de nouvelles méthodes d’indexage
  • de nouveaux langages de procédure

Tutoriel

Nous allons maintenant présenter comment installer et utiliser PostgreSQL sous Ubuntu et Windows dans deux parties distinctes. Suivant le système d’exploitation que vous utilisez, référez-vous à la partie correspondante.

PostgreSQL sous Ubuntu

Je vous conseille de jeter un œil à la page dédiée à PostgreSQL du site de la communauté francophone des utilisateurs d’Ubuntu.

Téléchargement/Installation

Nous allons détailler ici comment installer PostgreSQL sur votre machine. Si la version de votre ubuntu est égale ou supérieure à 12.04 LTS alors vous avez peut-être déjà installé PostgreSQL lors de l’installation d’Ubuntu (faire psql -V pour voir si vous l’avez déjà) . On utilise le gestionnaire de paquet de Ubuntu qui va faire tout le travail pour nous. Pour cela on ouvre une console et on tape la commande suivante :

sudo apt-get install postgresql

Il va vous demander de saisir votre mot de passe utilisateur pour continuer l’installation. Puis télécharger les paquets, et procéder à l’installation. Une fois l’installation achevée, on va vérifier qu’on possède bien la dernière version en tapant la commande suivante dans la console :

psql -V

qui renvoie normalement un résultat du genre :

psql (PostgreSQL) x.x

contient une gestion avancée de la ligne de commande
x.x est la version de PostgreSQL installée. Retenez bien la version utilisée, vous en aurez besoin pour la suite de ce tutorial en remplaçant tous les x.x contenus dans les commandes par le numéro de version.

Utilisation

Nous allons maintenant voir les principales commandes pour utiliser PostgreSQL.
Je vous recommande, si vous voulez approfondir vos connaissances sur PostgreSQL,  le tutoriel très détaillé disponible sur le site de la communauté francophone de PostgreSQL sur cette page.

Changement du mot de passe utilisateur postgres

A l’issue de l’installation de PostgreSQL, un utilisateur postgres a été créé. La première chose à faire, c’est de définir un nouveau mot de passe pour cet utilisateur. Pour cela on saisit la commande suivante dans une console :

sudo -u postgres psql postgres

On accède alors au prompt de PostgreSQL, on va définir comme mot de passe “scub” pour le super utilisateur postgres en tapant les commandes suivantes :

ALTER USER postgres WITH ENCRYPTED PASSWORD 'scub';
 
\q
Connexion à Postgresql

Pour se connecter au SGBD PostgreSQL, il faut dans un premier temps exécuter les commandes en tant que l’utilisateur postgres. Pour cela il faut taper la commande suivante dans une console :

sudo -s -u postgres

La commande sudo permet à un utilisateur autorisé d’exécuter une commande en tant que superutilisateur (root) ou un autre utilisateur :

  • l’option -s lance le shell et non juste la commande suivante avec sudo
  • l’option -u spécifie qu’on souhaite utiliser un autre utilisateur que root, ici on souhaite utiliser l’utilisateur postgres.

On peut ensuite taper la commande suivante pour accéder à PostgreSQL :

psql

On aura alors accès au shell de postgres qui devrait afficher :

Bienvenue dans psql x.x, l'interface interactive de PostgreSQL.
 
Saisissez:
 
\copyright pour les termes de distribution
 
\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
 
postgres=#
Création d’un utilisateur

L’utilisateur par défaut postgres dans PostgreSQL est l’équivalent du super utilisateur root de Linux. Il est recommandé de l’utiliser pour l’administration du système uniquement. C’est pourquoi nous allons créer un nouvel utilisateur.
Tout d’abord, il faut modifier le fichier pg_hba.conf. On ouvre le fichier depuis une nouvelle console avec la commande :

sudo gedit /etc/postgresql/x.x/main/pg_hba.conf

Il faut remplacer x.x par la version installée de PostgreSQL.

On va à la fin du document et pour autoriser les connexions avec mots de passe cryptés on remplace ident sameuser par md5

On devrait avoir un fichier qui ressemble à :

# Database administrative login by UNIX sockets
 
local   all         postgres                          md5
 
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# "local" is for Unix domain socket connections only
 
local   all         all                               md5
 
# IPv4 local connections:
 
host    all         all         127.0.0.1/32          md5
 
# IPv6 local connections:
 
host    all         all         ::1/128               md5

On sauvegarde le fichier, et on redémarre PostgreSQL à partir de la même console avec la commande :

sudo /etc/init.d/postgresql-x.x restart

Qui affichera si tout se passe bien :

* Restarting PostgreSQL x.x database server

[ OK ]
On peut désormais créer notre utilisateur à partir de la console où est logué l’utilisateur postgres avec la commande :

createuser -PRelSED test_user

On aura alors à saisir un mot de passe pour notre nouvel utilisateur, on prendra un mot de passe simple comme “test” pour notre exemple, qu’on retape une deuxième fois, puis on saisit le mot de passe de l’utilisateur postgres, “scub”, et on valide en tapant sur la touche entrer. On devrait avoir l’affichage suivant :

postgres@scub-nprouteau:~$ createuser -PRelSED test_user
 
Saisissez le mot de passe pour le nouveau rôle :
 
Saisissez-le à nouveau :
 
Mot de passe :
 
CREATE ROLE test_user ENCRYPTED PASSWORD 'md5ce65fb6038e348eb70a17aa9a6ce13e8' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
 
postgres@scub-nprouteau:~$
Création d’une base de données

Maintenant qu’on a créé un utilisateur, on peut créer une base de données proprement avec la commande :
postgres@scub-nprouteau:~$ createdb -O test_user -E UNICODE test_base

Se connecter à une base de données en mode console

Pour se connecter avec un utilisateur en mode console, il faut modifier le fichier /etc/postgresql/X.X/main/pg_hba.conf pour y ajouter (X.X est à remplacer par la version de PostgreSQL):

local all all password

Puis relancer le serveur en faisant dans la console :

sudo  /etc/init.d/postgresql-x.x restart

Vous pouvez ensuite vous connecter à la base avec :

postgres@scub-nprouteau:~$ psql -d test_base
Sauvegarde / Restauration d’une base de données

Pour sauvegarder la base il suffit d’utiliser la commande pg_dump avec les options suivantes :

pg_dump -h localhost -U test_user -Ft test_base  > sauvegarde.tar

Pour restaurer une base, on utilisera pg_restore :

pg_restore -d test_base -U test_user sauvegarde.tar
Faire un export de base depuis un serveur distant

Il faut spécifier l’adresse ip du serveur distant avec l’option -h :

pg_dump -h 192.168.1.x base -U user > fichier.sql
Renommer un utilisateur

A lancer depuis un terminal postgres :

alter USER ancien_nom RENAME TO nouveau_nom;
Changer le mot de passe d’un utilisateur

A lancer depuis un terminal postgres :

alter USER nom_utilisateur ENCRYPTED PASSWORD nouveau_password;
Renommer une base existante

A lancer depuis un terminal postgres :

alter DATABASE ancien_nom_bdd RENAME TO nouveau_nom_bdd;

PostgreSQL sous Windows

Téléchargement/Installation

Pour l’installation sous Windows, il faut télécharger l’installer sur la page http://www.postgresql.org/download/windows. Deux installer sont disponibles, le One Click Installer et le pgInstaller. Pour ce tutoriel, on utilisera le One Click Installer qui est plus simple et suffit amplement.
Une fois le fichier exécutable téléchargé, il suffit de le lancer et de suivre les étapes pas à pas.

    1. Choisir le répertoire d’installation de PostgreSQL : On laisse l’emplacement par défaut (C:\Program Files\PostgreSQL\version)
    2. Choisir le répertoire où les données seront stockées : on laisse aussi l’emplacement par défaut
    3. Définir le mot de passe administrateur de la base : pour faire simple, on va mettre “scub”
    4. Spécifier le port d’écoute : on laisse le port par défaut (port 5432)
    5. Localisation : on laisse la localisation par défaut du système
    6. On valide enfin l’installation
    7. Une fois l’installation achevée, l’installer va vous proposer de lancer Stack Builder qui permet d’installer des outils supplémentaires. Pour notre tutoriel, nous n’en avons pas besoin.
    8. On peut maintenant aller consulter dans le menu démarrer de windows, Démarrer → Programmes → PostgreSQL 8.3 pour démarrer/arrêter/redémarrer le SGBD, ou encore l’administrer aussi bien en mode console ou avec l’outil graphique pgAdmin III.

Si vous n’arrivez pas à démarrer PostgreSQL, il se peut que l’utilisateur postgres n’ai pas été créé. On peut résoudre facilement le problème en spécifiant d’utiliser l’utilisateur local pour PostgreSQL. Voici la démarche à suivre:

  • Utilisez le raccourci clavier touche Windows + R
  • Tapez services.msc
  • Cherchez le service “PostgreSQL Server”

  • Faites un clic droit et dans le menu contextuel cliquez sur propriétés et dans la nouvelle fenêtre dans l’onglet Connexion cochez “Compte système local”