PostgreSQL

Installation et préparation

L'installation de PostgreSQL ne devrait pas poser de problème. Si PostgreSQL n'est pas déjà installé sur votre système, passez par Synaptic ou apt-get selon votre préférence. Si vous êtes sous CentOs RH Fedora voyez ici

Durant l'installation un utilisateur "postgres" est créé sans mot de passe, nous allons devoir lui en attribuer un pour pouvoir se connecter.

Cette commande nécessite d'être root:

# passwd postgres

Il faudra ensuite taper 2 fois le mot de passe que vous aurez choisi.

L'utilisateur "postgres" n'est pas destiné à devenir l'utilisateur de vos bases de données mais il est le seul à pouvoir créer de tels utilisateurs. Il va donc falloir ouvrir une session avec ses privilèges.

Il existe un utilisateur "postgres" au sein du système et un administrateur "postgres" au sein de PostgreSQL. Il est important de bien distinguer les deux. Par défaut PostgreSQL ne crée pas de mot de passe pour l'administrateur.

$ su postgres

Ou si vous utilisez sudo

$ sudo postgres

On vous demande alors de saisir un mot de passe pour vous connecter, c'est le mot de passe que vous venez de définir.

Il faut maintenant créer un utilisateur "bibi", ceci se fait grâce à la commande createuser.

$ createuser bibi -SdPR
Saisir le mot de passe pour le nouveau rôle : 
Le saisir de nouveau : 

pensez à mémoriser les mots de passe d'une manière ou d'une autre.

Les options -SdPR signifient respectivement que l'utilisateur ne sera pas un super-utilisateur, qu'il sera autorisé à créer des bases de données, qu'il faut lui demander un mot de passe et qu'il n'est pas autorisé à créer de nouveaux rôles.

Voila notre utilisateur est créer pour PostgreSQL mais il n'existe pas d'utilisateur "bibi" sur le système d'exploitation, or par défaut, le serveur est configurer pour autoriser les utilisateurs unix du domaine, ce qui n'est pas le cas de bibi. Il faut modifier ce comportement dans le fichier de configuration "pg_hba.conf" que l'on trouve généralement dans "/etc/postgresql/x.x/main/", x.x représentant la version de PostgreSQL que vous utilisez.

Ce chemin est propre à Debian et peut être différent selon votre distribution.

Ouvrez le fichier avec l'éditeur de votre choix, il faudra être "root" ou "postgres" pour ce faire.

# nano /etc/postgresql/x.x/main/pg_hba.conf

Ce fichier contient un certain nombre de lignes, la plupart sont commentées. Repérez vers la fin du fichier les lignes suivantes:

# "local" is for Unix domain socket connections only
local   all         all                               ident

Le mot clé ident signifie ce que nous avons expliqué plus haut, que la méthode d'identification doit être faite par un utilisateur du système. On remplacera donc ce mot clé par md5. Vous devriez obtenir ceci:

# "local" is for Unix domain socket connections only
local   all         all                               md5

Enregistrez et fermez le fichier.

Pour que ces nouveaux paramètres soient pris en compte il est nécessaire de redémarrer le serveur PostgreSQL comme ceci:

$ /etc/init.d/postgresql reload

Voila, notre serveur est configuré pour l'utilisateur bibi qui va désormais pouvoir manipuler ses propres bases de données.
Si vous souhaitez en apprendre plus à propos des méthodes d'authentification je vous invite à lire ceci.

Créer une base de données

Il ne nous reste plus qu'à créer une nouvelle base pour l'utilisateur bibi, on peut faire cela de la manière suivante. N'oubliez pas que vous devez être connecté en tant qu'utilisateur "postgres".

$ createdb -O bibi -E UNICODE dbtest

L'option -O permet de désigner bibi comme propriétaire de la base, -E permet de choisir l'encodage pour cette base, (UNICODE pour UTF-8). dbtest est le nom de la base, vous pouvez bien sur en choisir un autre.

On peut vérifier que tout c'est bien passé avec psql.

$ psql -l
                                Liste des bases de données
    Nom    | Propriétaire | Encodage |     Tri     | Type caract. |    Droits d'accès     
-----------+--------------+----------+-------------+--------------+-----------------------
 dbtest    | bibi         | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8  | 

Je n'ai recopié que les lignes qui nous intéresse. Tapez "q" pour quitter.

Supprimer une base de donnée et/ou un utilisateur

On supprimera une base de donnée simplement comme ça:

$ dropdb dbtest

C'est aussi simple pour supprimer un utilisateur:

$ dropuser bibi

n'oubliez pas que ces commandes ne peuvent être faites que depuis une session "postgres"

Administrer ses bases avec psql

Le programme "psql" est un outil en ligne de commande permettant de manipuler les basses de données PostgreSQL depuis une console. Si PostgreSQL à bien été installé il est impératif que ce soit l'utilisateur "postgres" qui l'utilise. Il est normalement le seul utilisateur de votre système à pouvoir administrer vos bases de données.

Il convient donc, si ce n'est déjà le cas, dans une console d'ouvrir une session en tant que "postgres"

$ su postgres
Mot de passe : 

Comme montré plus haut on affiche les bases existantes de cette façon:

$ psql -l
                                Liste des bases de données
    Nom    | Propriétaire | Encodage |     Tri     | Type caract. |    Droits d'accès     
-----------+--------------+----------+-------------+--------------+-----------------------
 dbtest    | bibi         | UTF8     | fr_FR.UTF-8 | fr_FR.UTF-8  | 

Notre base à correctement été créée, on peut donc l'utiliser avec le client bibi. On se connectera de cette façon:

$ psql -U bibi dbtest -W
psql (8.4.16)
Saisissez « help » pour l'aide.

dbtest=> 

Nous avons désormais un prompt nous permettant d'entrer directement des commandes SQL afin de créer, modifier, supprimer de nouvelles tables et tout et tout.

l'option -W demande à ce qu'un mot de passe soit saisi pour l'utilisateur.

Notez au passage la ligne du prompt:

dbtest=>

Notre utilisateur "bibi" n'est pas un super administrateur. Si vous vous étiez connecté avec l'utilisateur "postgres" le prompt ressemblerai plutôt à celui-ci:

dbtest=#

Créer une nouvelle table est ses champs

dbtest=> CREATE TABLE film (
dbtest(> id SERIAL PRIMARY KEY,
dbtest(> titre varchar(100),
dbtest(> note real -- note du film sur 5
dbtest(> );
NOTICE:  CREATE TABLE créera des séquences implicites « film_id_seq » pour la colonne serial « film.id »
NOTICE:  CREATE TABLE / PRIMARY KEY créera un index implicite « film_pkey » pour la table « film »
CREATE TABLE
dbtest=>

Remarquez la différence entre la première ligne et les 4 suivantes, le signe "=" est remplacé par une parenthèse ouvrante ce qui indique que, malgré le saut de ligne, l'instruction n'est pas terminée. Tout ce qui suit les double tirets "--" sont des commentaires. N'oubliez pas le point virgule ";" après la parenthèse fermante pour signaler que l'instruction est terminée.

Si vous avez fait une erreur sur une ligne il est possible d'effacer le tampon de requête en tapant \r

L'instruction s'est donc bien déroulée. On peut connaitre les tables présentes dans la base dbtest grâce à la commande \d.

dbtest=> \d
              Liste des relations
 Schéma |     Nom     |   Type   | Propriétaire 
--------+-------------+----------+--------------
 public | film        | table    | bibi
 public | film_id_seq | séquence | bibi
(2 lignes)

On peut également connaitre les champs d'une tables avec \d <nom_table>

dbtest=> \d film
                                  Table « public.film »
 Colonne |          Type          |                     Modificateurs                     
---------+------------------------+-------------------------------------------------------
 id      | integer                | non NULL Par défaut, nextval('film_id_seq'::regclass)
 titre   | character varying(100) | 
 note    | real                   | 
Index :
    "film_pkey" PRIMARY KEY, btree (id)

Tapez \q pour quitter le prompt "dbtest=>" et revenir au prompt de l'utilisateur

Sauvegarde des bases de données avec "pg-dump"

Types de formats

Les sauvegardes des bases PostgreSQL se font grâce à l'utilitaire pg_dump qui en plus de ne pas bloquer les utilisateurs lors d'un dump, permet des backups sous trois formats.

Quelques exemples

Sauvegarde au format custom depuis un compte utilisateur lambda

jojo@server:~$ pg_dump --format=custom --username=bibi --file fichier.dump nom_base 
Mot de passe : 

Sauvegarde au format plain

jojo@server:~$  pg_dump --username=bibi nom_db > fichier.sql
Mot de passe :

jojo@server:~$ pg_dump -F p --username=bibi --file fichier.sql nom_db
Mot de passe : 

Téléchargez la documentation pdf Postgresql 9

^