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/UBUNTU 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/UBUNTU, 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 :
\d [NAME] describe table, index, sequence, or view
\d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
list tables/indexes/sequences/views/system tables
\da [PATTERN] list aggregate functions
\db [PATTERN] list tablespaces (add "+" for more detail)
\dc [PATTERN] list conversions
\dC list casts
\dd [PATTERN] show comment for object
\dD [PATTERN] list domains
\df [PATTERN] list functions (add "+" for more detail)
\dg [PATTERN] list groups
\dn [PATTERN] list schemas (add "+" for more detail)
\do [NAME] list operators
\dl list large objects, same as \lo_list
\dp [PATTERN] list table, view, and sequence access privileges
\dT [PATTERN] list data types (add "+" for more detail)
\du [PATTERN] list users
\l list all databases (add "+" for more detail)
\z [PATTERN] list table, view, and sequence access privileges (same as \dp)
\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
