Installation et configuration de PostgreSQL

Software version	8.3+
Operating System	Debian 6
Website	PostgreSQL Website
Last Update	16/11/2012
Others

1 Introduction

PostgreSQL est un système de gestion de base de données relationnelle et objet (SGBDRO). C'est un outil libre disponible selon les termes d'une licence de type BSD.

Ce système est concurrent d'autres systèmes de gestion de base de données, qu'ils soient libres (comme MySQL et Firebird), ou propriétaires (comme Oracle, Sybase, DB2 et Microsoft SQL Server). Comme les projets libres Apache et Linux, PostgreSQL n'est pas contrôlé par une seule entreprise, mais est fondé sur une communauté mondiale de développeurs et d'entreprises.

2 Installation

Pour installer PostgreSQL :

apt-get

apt-get install postgresql postgresql-client

Cela crée l'utilisateur postgres qui a les droits sur la base de données.
Pour initialiser une base de données PostgreSQL dans le $HOME de l'utilisateur postgres (/var/lib/postgres sous Debian)(l'installation sous Debian fait cela automatiquement) :

> su postgres > cd > /usr/lib/postgresql/8.3/bin/initdb -D data Les fichiers de ce cluster appartiendront à l'utilisateur « postgres ». Le processus serveur doit également lui appartenir.   Le cluster sera initialisé avec la locale fr_FR@euro. L'encodage par défaut des bases de données a été configuré en conséquence avec LATIN9.   création du répertoire data... ok création des sous-répertoires... ok sélection de la valeur par défaut de max_connections... 100 sélection des valeurs par défaut de shared_buffers/max_fsm_pages... 24MB/153600 création des fichiers de configuration... ok création de la base de données template1 dans data/base/1... ok initialisation de pg_authid... ok initialisation des dépendances... ok création des vues système... ok chargement de la description des objets système... ok création des conversions... ok initialisation des droits sur les objets internes... ok création du schéma d'informations... ok lancement du vacuum sur la base de données template1... ok copie de template1 vers template0... ok copie de template1 vers postgres... ok   ATTENTION : active l'authentification « trust » pour les connexions locales. Vous pouvez modifier ceci en éditant pg_hba.conf ou en utilisant l'option -A au prochain lancement d'initdb.   Succès. Vous pouvez maintenant lancer le serveur de bases de données par :   /usr/lib/postgresql/bin/postgres -D data ou /usr/lib/postgresql/bin/pg_ctl -D data -l journaltrace start

Note : il faut avoir des droits corrects sur /tmp/

Sur Debian 5 (lenny), pour initialiser la base principale de postgres, il faut lancer la commande suivante en root

pg_createcluster

pg_createcluster 8.3 main

Les fichiers de configuration de trouvent dans $HOME/data et notamment pg_hba.conf pour le gestion des droits et postgresql.conf pour la configuration générale du service. Sous Debian ces fichiers sont des liens symboliques vers des fichiers se trouvant dans /etc/postgresql/.

3 Configuration

3.1 Gestion des utilisateurs

3.1.1 Admin

Dans un premier temps, nous allons changer le mot de passe :

passwd

passwd postgres

Ceci va donc modifier le mot de passe postgres sur la machine, mais pas sur la base de donnée. Vous n'êtes donc pas obliger de le faire si vous en avez pas la nécessité (ex: passer par root puis postgres tout le temps).
Maintenant, nous allons donc avoir besoin de lui définir un mot de passe pour la base de donnée :

psql -d template1 -c "alter user postgres with password 'motdepasse'" ou \password postgres

Ceci le rendra admin de la base template1.

Note : Pour faire fonctionner les clients Web ou les clients graphiques présentés aux chapitres suivant, il est obligatoire de définir un mot de passe pour "postgres".

3.1.2 Authentification

Par défaut, pour accéder à PostgreSQL, il faut se connecter sous l’utilisateur "postgres".
Pour créer un nouvel utilisateur, il faudrait commencer par lui créer un compte sur le système, ce qui n’est peut-être pas souhaitable.
Pour changer cette configuration, il faut modifier le fichier "/etc/postgresql/pg_hba.conf" et mettre "password" à la place de "ident sameuser" sur les deux lignes suivantes :

/etc/postgresql/pg_hba.conf

[...] local all all ident sameuser host all all 127.0.0.1 255.255.255.255 ident sameuser

Ce qui donne :

/etc/postgresql/pg_hba.conf

[...] local all all password host all all 127.0.0.1 255.255.255.255 password

Cette modification permet d’éviter de passer par des comptes du système et oblige à fournir un mot de passe à chaque connexion.

Pour autoriser une autre machine à se connecter sur ce serveur Postgres il faut ajouter une ligne dans ce genre au fichier "/etc/postgresql/pg_hba.conf" :

host all all 192.168.0.1 255.255.255.255 password

WARNING

L'ordre d'insertion des lignes est très importante ! La première qui match sera la première ligne qui prendra la règle. Attention donc à l'ordre d'insertion dans ce fichier

et modifier le fichier "/etc/postgresql/postgresql.conf" en ajoutant l'option suivante pour que postgres écoute sur toutes ses adresses et non uniquement le "localhost" :

/etc/postgresql/postgresql.conf

listen_addresses = '*'

On redémarre un coup PostgreSQL :

/etc/init.d/postgresql restart

3.1.3 Création

Pour créer un utilisateur :

> createuser toto Le nouvel rôle est-il superutilisateur ? (o/n) n Le nouvel utilisateur a-t'il le droit de créer des bases de données ? (y/n) y Le nouvel utilisateur a-t'il le droit de créer des utilisateurs ? (y/n) n CREATE USER

Par défaut cet utilisateur n'as pas de mot de passe. Pour lui en assigner un :

psql

psql -d template1 -c "alter user toto with password <motdepasse>"

3.1.4 Suppression

Pour supprimer un utilisateur :

dropuser

drop user toto

3.2 Gestion des bases de données

3.2.1 Création

La commande suivante, permet de créer la base de données "mabase" pour l’utilisateur "toto" en utilisant l’encodage "UNICODE" :

createdb

CREATE DATABASE <database_name> owner <username>;

On prendra bien garde à l'encodage de la table : LATIN9, LATIN1, UNICODE, etc...

3.2.2 Suppression

Pour supprimer une base de données :

dropdb

DROP DATABASE <mabase>

3.2.3 Lister

Pour lister les bases de données existantes :

psql

psql -l

3.2.4 Sauvegarder une base

Pour sauvegarder une base de données :

pg_dump

pg_dump NOM_BASE > NOM_FICHIER

3.2.5 Sauvegarder toutes les bases

Sauvegarder toutes les bases de données d'un coup :

pg_dumpall

pg_dumpall > NOM_FICHIER

3.2.6 Sauvegardes sur de très grosses bases

Il semblerait qu'en passant certains paramètres, il soit plus facile de restaurer les bases :

pg_dump

pg_dump -Ft NOM_BASE > NOM_FICHIER

http://www.postgresql.org/docs/8.1/static/app-pgdump.html
Note : Attention, il y a certaines limitations dans la sauvegarde "à chaud".

3.2.7 Restauration

Pour restaurer une base de données, on créer d'abord une base vide, puis on fait l'import :

psql

psql <newbase> < NOM_FICHIER

3.3 Afficher toutes les requêtes

Il est possible d'augmenter le niveau de log afin de voir toutes les requêtes qui passent. Ces requêtes seront enregistrées dans un fichier.

WARNING

Ceci peut ralentir votre système si vous avez beaucoup d'activité

Pour mettre en place un niveau de log plus élevé, éditez ces lignes dans la configuration de PostgreSQL :

/etc/postgresql/<postgresql_version>/main/postgresql.conf

[...] logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_statement = 'all' [...]

Il ne reste plus qu'à redémarrez votre PostgreSQL pour voir les logs apparaitre dans /var/lib/postgresql/<postgresql_version>/main/pg_log

4 Utilisation

On peut maintenant "utiliser" notre base de données avec le client en ligne PostgreSQL en ligne de commande :

psql

> psql -h hostname -U user -d database mabase=#

Voici quelques commandes pratiques à retenir :

\l = liste des bases
\d = liste des tables
\q = quitter
\h = aide
SELECT version(); = version PostgreSQL
SELECT current_date; = date actuelle
\i fichier.sql = lit les instructions du fichier fichier.sql
\d table = décrit une table (comme DESCRIBE avec MySQL)

4.1 Création et suppression de table

Voici les différents types de données pour les champs d'une table :

CHAR(n)
VARCHAR(n)
INT
REAL
DOUBLE PRECISION
DATE
TIME
TIMESTAMP
INTERVAL

Remarque : on peut aussi définir ses propres types de données.

La syntaxe de création :

CREATE TABLE ma_table (col1 TYPE, [...], coln TYPE);

La suppression :

DROP TABLE ma_table;

Pour la forme un petit exemple tiré de la doc de PostgreSQL :

CREATE TABLE weather ( city VARCHAR(80), temp_lo INT, -- low temperature temp_hi INT, -- high temperature prcp REAL, -- precipitation DATE DATE );

Notes

Deux tirets -- introduisent des commentaires...

4.2 Extraction de données

Rien ne vaut des exemples :

SELECT * FROM weather;
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, DATE FROM weather;
SELECT * FROM weatherWHERE city = 'San Francisco' AND prcp > 0.0;
SELECT DISTINCT city FROM weather ORDER BY city;

Avec des jointures :

SELECT * FROM weather, cities WHERE city = name;
SELECT weather.city, weather.temp_lo, cities.location 
FROM weather, cities WHERE cities.name = weather.city;
SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
SELECT * FROM weather LEFT OUTER JOIN cities ON 
(weather.city = cities.name);
SELECT * FROM weather w, cities c WHERE w.city = c.name;

Avec des fonctions (Aggregate Functions) :

SELECT MAX(temp_lo) FROM weather;

Attention, les "Aggregate Functions" ne peuvent être utilsées dans la clause WHERE Ainsi la requête suivante est fausse :

SELECT city FROM weather WHERE temp_lo = MAX(temp_lo);

On devra donc faire :

SELECT city FROM weather WHERE 
temp_lo = (SELECT MAX(temp_lo) FROM weather);

On pourra bien sûr utilise "GROUP BY ...", "HAVING ...", etc.

4.3 Mise à jour des données

Toujours avec un exemple :

UPDATE weather SET temp_hi = temp_hi - 2, 
temp_lo = temp_lo - 2 WHERE DATE > '1994-11-28';

4.4 Suppression des données

Encore avec un exemple :

DELETE FROM weather WHERE city = 'Hayward';

Pour effacer toutes les données d'une table :

DELETE FROM weather;

4.5 Intérroger la taille d'une base de donnée

SELECT pg_size_pretty(pg_database_size('nom_de_la_base'));

5 Faq

5.1 Tsearch

Si pour une application, il vous demande d'avoir tsearch2 par exemple, vous devez installer un package :

aptitude

aptitude install postgresql-contrib-'''8.2

Mettez le numéro de votre version correspondante à la fin du package.

Et enfin, il faut patcher la base base de donnée en question :

psql

psql wikidb < /usr/share/postgresql/8.2/contrib/tsearch2.sql

Wikidb est ici ma base de donnée, et 8.2 est la version encore une fois de postgres.

5.2 L'encodage UTF8 ne correspond pas à la locale

L'erreur de merde qui m'a bien soulé. Ca arrive quand on veut créer une base de donnée qui n'est pas dans l'encoding actuel du serveur :

createdb : la création de la base de données a échoué : ERREUR:  l'encodage UTF8 ne correspond pas a la locale fr_FR@euro du serveur
DETAIL:  Le parametre LC_CTYPE du serveur necessite l'encodage LATIN9.

Pour corriger le problème, il faut déjà vérifier que les locales sont bonnes au niveau de l'OS :

dpkg-reconfigure

dpkg-reconfigure locales

Ensuite, admettons que je veuilles de l'UTF-8, je met donc mes variables d'environnement comme il faut :

export LC_ALL=fr_FR.UTF-8 export LANG=fr_FR.UTF-8 export PGCLIENTENCODING=fr_FR.UTF-8

Maintenant je peux lister la variable d'environnement de postgres :

psql

$ postgres=# show lc_ctype; lc_ctype ------------- fr_FR.UTF-8 (1 ligne)

Normalement si vous créez une nouvelle base, l'encodage sera maintenant en UTF-8. Dans le cas ou ça ne change toujours rien, vous devez lancer ceci mais qui aura pour effet de vous mettre toutes vos futures bases dans ce format :

initdb -E UTF-8

Maintenant réessayez de créer une base de données.

6 Ressources

Site Officiel de PostgreSQL
http://www.postgresql.org/docs/7.3/static/multibyte.html
Les vues système sous PostgreSQL 8.3