HOWTO PostgreSQL

Cette documentation est désormais rendue obsolète par une version plus récente : http://trac.evolix.net/infogerance/wiki/HowtoPostgreSQL

Première prise en main pour un administrateur de base de données PostgreSQL sous Unix/Linux.

Je passe les détails de l'installation d'un serveur PostgreSQL. Sous Debian on fera :

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

# su postgres
$ cd
$ /usr/lib/postgresql/bin/initdb -D data

(l'installation sous Debian fait cela automatiquement)

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

Les fichiers de configuration de trouvent dans $HOME/data et notamment pg_hba.cong 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/

Voici un exemple simple de fichier data/pg_hba.conf qui règle certaines permissions :

host    all         all         192.168.0.2     255.255.255.255   password
host    all         all         1.2.3.4     255.255.255.255   password
local   all         postgres                                        ident sameuser
local   all         all                                             password
host    all         all         127.0.0.1         255.255.255.255   password
host    all         all         0.0.0.0           0.0.0.0           reject

Sous Debian, les journaux de PosgreSQL se trouvent dans /var/log/postgresql/postgresql.log

La première chose à faire est de mettre un mot de passe à l'admin postgres :

# su postgres
$ psql -d template1
template1=# alter user postgres with password 'MDP';

Puis éditer data/pg_hba.conf et mettre une ligne ressemblant à :

local  all  postgres  password

Ensuite pour gérer une base de données.
On crée un utilisateur :

$ createuser login -P -D -A

Puis on crée une base de données "pour" cet utilisateur :

$ createdb -O login -E LATIN1 base

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

Pour supprimer une base de données :

$ dropdb base

Pour supprimer un utilisateur :

$ dropuser login

Pour lister les base de données :

$ psql -l

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

$ psql base login
ma_base=#

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)

Création 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

claSiQuaL, la syntaxe :

CREATE TABLE ma_table (col1 type, [...], coln type);
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
);

Rq : deux tirets -- introduisent des commentaires...

Insertion de données

Insertion de tous les champs d'une table :

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

Insertion en précisant les champs :

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

Insertion à partir d'un fichier externe :

COPY weather FROM '/home/user/weather.txt';

Rq : voir www.postgresql.org/docs/7.4/interactive/sql-copy.html

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.

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';

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;

PostgreSQL et les sauvegardes

Pour sauvegarder une base de données :

$ pg_dump NOM_BASE > NOM_FICHIER

Pour restaurer une base de données :

$ createdb -O login -E LATIN1 newbase
psql newbase < NOM_FICHIER

Sauvegarder une base complète :

$ pg_dumpall > NOM_FICHIER

Rq : Attention, il y a certaines limitations dans la sauvegarde "à chaud". Voir http://traduc.postgresqlfr.org/pgsql-fr/backup.html

Liens

Documentation officielle
http://www.postgresql.org/docs/7.4/interactive/tutorial.html