Installation et configuration de PostgreSQL

From Deimos.fr / Bloc Notes Informatique
Jump to: navigation, search
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 :

Command 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) :

Command
> 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 [email protected]
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

Command 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 :

Command 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 :

Command
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 :

Configuration File /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 :

Configuration File /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" :

Configuration File
host   all     all    192.168.0.1       255.255.255.255   password

Warning 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" :

Configuration File /etc/postgresql/postgresql.conf
listen_addresses = '*'

On redémarre un coup PostgreSQL :

Command
/etc/init.d/postgresql restart

3.1.3 Création

Pour créer un utilisateur :

Command
> 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 :

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

3.1.4 Suppression

Pour supprimer un utilisateur :

Command 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" :

Command 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 :

Command dropdb
DROP DATABASE <mabase>

3.2.3 Lister

Pour lister les bases de données existantes :

Command psql
psql -l

3.2.4 Sauvegarder une base

Pour sauvegarder une base de données :

Command 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 :

Command 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 :

Command 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 :

Command 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 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 :

Configuration File /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 :

Command 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 :

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

La suppression :

Command
DROP TABLE ma_table;

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

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

Notes 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 :

Command 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 :

Command 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 [email protected] 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 :

Command dpkg-reconfigure
dpkg-reconfigure locales

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

Command
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 :

Command 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 :

Command
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