PostgreSQL : Installation et premières manipulations

Sébastien Le Ray

Ce tutoriel vous guidera à travers la compilation et l'installation de PostgreSQL sous Linux et vous donnera quelques notions de base pour l'administration.

Licence : CC-by-SA

- Titre : PostgreSQL : Installation et premières manipulations

- Auteur : Sébastien Le Ray

- Parution : 23 mai 2005

- Mise à jour : 6 juin 2005

- Licence : Le contenu de cet article est rédigé par Sébastien Le Ray et est mis à disposition selon les termes de la Licence Creative Commons Attribution 3.0 non transposé. Les logos Developpez.com, en-tête, pied de page, css, et look & feel de l'article sont Copyright ® 2013 Developpez.com.

Sommaire

- I. Introduction

- II. Installation

- II-A. Prérequis

- II-B. Compilation

- II-C. Configuration postinstallation

- II-D. Initialisation du serveur

- II-E. Passage en service

- III. Première connexion

- IV. Ajout du langage PL/PGSQL par défaut

- V. Templates de bases

- VI. Utilisateurs

- VII. Création de base et privilèges

- VIII. Tablespaces

- IX. Fichiers de configuration

- IX-A. Fichier principal

- IX-A-1. Emplacement des fichiers

- IX-A-2. Paramètres de connexion

- IX-A-3. Erreurs et journalisation

- IX-A-4. Autres directives

- IX-B. Fichier d'identification niveau hôtes

- X. Conclusion

- XI. Remerciements

- XII. Téléchargements

I - Introduction

Cet article vous expliquera dans un premier temps comment compiler et installer PostgreSQL sous Linux dans sa version 8. Il suppose quelques connaissances préalables sur ce système, mais devrait être accessible aux novices, car un effort a été fait pour expliquer les différentes notions. Après l'installation, nous verrons comment se déroule la première connexion, comment ajouter le langage PL/SQL (langage côté serveur) et finirons avec quelques notions d'administration.

Ce document n'a pas vocation à être exhaustif. Tout a été fait pour éviter les erreurs (l'installation a été réalisée au fur et à mesure de la rédaction du document). Néanmoins, si vous relevez des incohérences ou des inexactitudes, communiquez-les-moi, je les corrigerai le plus rapidement possible.

II - Installation

II-A - Prérequis

Afin de pouvoir naviguer parmi les lignes (historique, édition de la ligne en cours), la bibliothèque libreadline est nécessaire. Si vous utilisez un système de gestion de packages, il faut disposer du package libreadline-devel, sinon, téléchargez, compilez et installez les sources.

Vous aurez bien sûr également besoin d'un environnement de compilation complet (gcc, configure & Cie). Il est fort possible que cet environnement soit déjà présent sur votre système.

II-B - Compilation

Dans ce tutoriel, nous allons tout faire depuis la compilation jusqu'au paramétrage de la première base. C'est-à-dire que nous n'installons rien à partir de package (excepté éventuellement librealine). Il faut donc commencer par récupérer les sources sur http://www.postgresl.org/ftp. Vous y trouverez un répertoire v8.x.y (v8.0.3 pour moi), descendez dedans et téléchargez l'archive nommée postgresql-8.x.y.tar.gz.

Une fois l'archive téléchargée, il faut la décompresser :

su -

cd /usr/src

tar zxvf /path/vers/postgres-8.x.y.tar.gz

Le mot de passe root vous est demandé après la première commande. Ensuite, les sources sont décompressées ce qui crée un répertoire postgres-8.x.y dans /usr/src. On peut ensuite passer à la compilation proprement dite :

adduser postgres

chown postgres:postgres /usr/src/postgres-8.x.y/ -R

su - postgres

cd /usr/src/postgres-8.x.y

./configure

make

make check

logout

make install

Le script configure accepte diverses options qui permettent de configurer précisément la façon dont est construit PostgreSQL :

- --prefix=PREFIX : installe tous les fichiers dans le répertoire PREFIX au lieu de /usr/local/pgsql. Les fichiers seront en fait installés dans divers sous-répertoires ; aucun fichier ne sera installé directement dans le répertoire PREFIX ;

- --exec-prefix=EXEC-PREFIX : installe les fichiers exécutables dépendant de l'architecture dans le répertoire EXEC-PREFIX. Dans le cas où il n'est pas précisé, EXEC-PREFIX vaut PREFIX ;

- --bindir=DIRECTORY : Indique le répertoire des fichiers exécutables. Par défaut vaut EXEC-PREFIX/bin (soit /usr/local/pgsql/bin par défaut) ;

- --datadir=DIRECTORY : indique le répertoire des fichiers en lecture seule utilisés par PostgreSQL. Par défaut vaut PREFIX/share. Cela n'a rien à voir avec l'emplacement des fichiers de base de données ;

- --sysconfdir=DIRECTORY : répertoire pour les fichiers de configuration, PREFIX/etc par défaut ;

- --libdir=DIRECTORY : emplacement des bibliothèques et des modules dynamiques. Par défaut, EXEC-PREFIX/lib ;

- --includedir=DIRECTORY : répertoire d'installation des fichiers d'en-tête C et C++. Par défaut, PREFIX/include ;

- --mandir=DIRECTORY : les pages de manuel de PostgreSQL seront installées dans ce répertoire. Par défaut, PREFIX/man ;

- --with-docdir=DIRECTORY : les fichiers de documentation, sauf les pages de manuel, seront installées dans ce répertoire. Par défaut, PREFIX/doc ;

- --without-docdir : n'installe pas la documentation lors du make install ;

- --with-includes=DIRECTORIES : DIRECTORIES est une liste de répertoires séparés par deux-points dans lesquels seront recherchés les fichiers d'include en plus des répertoires standards. Utile si vous avez des bibliothèques (comme GNU Readline) installées dans des répertoires particuliers ;

- --with-libraries=DIRECTORIES : DIRECTORIES est une liste de répertoires séparés par deux-points dans lesquels seront recherchés les bibliothèques. Comme pour --with-includes, à n'utiliser que si vous disposez de packages installés dans des emplacements non standards ;

- --enable-nls[=LANGUAGES] : active l'affichage des messages dans d'autres langues que l'anglais. LANGUAGES est une liste des codes à supporter séparés par des espaces. Si vous n'indiquez rien, toutes les traductions sont installées. Nécessite une implémentation de l'API Gettext ;

- --with-pgport=NUMBER : paramètre le numéro de port par défaut du serveur et des clients. Par défaut, vaut 5432. À n'utiliser que si vous voulez lancer plusieurs instances de PostgreSQL sur la même machine ;

- --with-perl : compile le langage PL/Perl côté serveur ;

- --with-python : compile le langage PL/Python côté serveur ;

- --with-tcl : compile le langage PL/Tcl côté serveur ;

- --with-tclconfig=DIRECTORY : chemin contenant les informations de configuration pour l'interfaçage avec Tcl situées dans le fichier tclConfig.sh. À utiliser si vous voulez utiliser une version de Tcl différente de celle installée ;

- --with-krb4, --with-krb5 : support de l'authentification Kerberos 4 ou 5, mais pas les deux ;

- --with-krb-srvnam=NAME : nom du service principal de Kerberos ;

- --with-openssl : support pour les connexions SSL (cryptées). Requiert le package OpenSSL ;

- --with-pam : support PAM (Pluggable Authentication Modules) ;

- --without-readline : évite l'utilisation de la bibliothèque Readline ce qui désactive l'historique des commandes et l'édition de la ligne en cours ;

- --with-rendezvous : support Rendezvous (recommandé sur Mac OS X) ;

- --disable-spinlocks : Permet de compiler PostgreSQL même si aucun support pour le spinlock CPU de la plateforme. Résulte en des performances médiocres ;

- --enable-thread-safety : rend les bibliothèques client thread-safe ;

- --without-zlib : évite l'utilisation de la bibliothèque Zlib. Désactive les archives compressées de pg_dump (à n'utiliser que si vous ne disposez pas de zlib) ;

- --enable-debug : inclut les informations de débogage dans les binaires et les bilbiothèques, uniquement pour le développement ;

- --enable-cassert : active les vérifications d'assertions, uniquement pour le développement ;

- --enable-depend ; active la surveillance automatique des dépendances. Les makefiles résultants forceront la recréation des binaires si un des headers a changé. Ne fonctionne que sur GCC, pour le développement ;

- comme pour les scripts configure classiques, vous pouvez paramétrer le compilateur via la variable d'environnement CC (par défaut, GCC est utilisé), les drapeaux du compilateur via CFLAGS, vous pouvez passer ces variables sur la ligne de commande (./configure CC=/opt/bin/gcc CFLAGS='-O2 -pipe').

Si vous n'êtes pas familier aux commandes Unix voici une description "en français" de ce que l'on a fait :

- on passe en root (administrateur) ;

- on ajoute l'utilisateur postgres, ce sera l'administrateur du serveur PostgreSQL (vous devrez entrer son mot de passe, choisissez-le bien) ;

- on donne la propriété des sources à cet utilisateur ;

- on se logue en tant qu'utilisateur postgres ;

- on lance la configuration automatique des sources (détection de la plate-forme, etc.), il est possible de passer plusieurs options à cette commande, pour plus d'informations, tapez ./configure --help. Si vous ne disposez pas de la bibliothèque libreadline, configure échouera, vous pouvez forcer la non-utilisation de libreadline via l'option --without-readline, mais il n'y aura plus d'historique des commandes ;

- il est également possible de configurer les différents répertoires utilisés par PostgreSQL (par défaut /usr/local/pgsql/) ;

- on lance la compilation ;

- make check effectue des tests de non-régression, cette étape est facultative ;

- on se déconnecte de l'utilisateur postgres, nous revenons donc au prompt root ;

- on installe les binaires que l'on vient de créer.

II-C - Configuration postinstallation

Une fois l'installation effectuée, il est nécessaire de paramétrer différentes variables d'environnement. Pour cela, il suffit d'ajouter les lignes suivantes au fichier /etc/profile (pour les shells type bash) :

# Modifiez les chemins si nécessaire

export LD_LIBRARY_PATH=/usr/local/pgsql/lib

# On a accès aux commandes de PostgreSQL sans les préfixer

export PATH=/usr/local/pgsql/bin :$PATH

II-D - Initialisation du serveur

Nous commençons par autoriser les utilisateurs à accéder aux binaires et à la documentation de PostgreSQL :

chmod o+rx /usr/local/pgsql

Il faut ensuite créer le répertoire qui contiendra les différentes bases de données et en donner la propriété à postgres :

mkdir /usr/local/pgsql/data

chown postgres /usr/local/pgsql/data

Puis, on initialise la base de données. L'option -A md5 demande le cryptage des mots de passe en MD5, l'option -W assigne un mot de passe au superutilisateur. Sans cela, tous les utilisateurs locaux peuvent avoir accès au serveur sans sécurité.

su postgres

initdb -D /usr/local/pgsql/data -A md5 -W

Afin de ne pas avoir à indiquer l'option -D à chaque démarrage du daemon, vous pouvez ajouter la variable PGDATA à l'environnement de l'utilisateur postgres, pour cela, il suffit d'ajouter la ligne suivante au fichier /home/postgres/.bash_profile :

export PGDATA=/usr/local/pgsql/data

Ce n'est pas nécessaire si vous lancez PostgreSQL comme service au démarrage de l'ordinateur.

II-E - Passage en service

Comme les développeurs de PostgreSQL sont gentils, ils ont préparé les scripts de démarrage pour différentes plateformes. Ceux-ci se trouvent dans le répertoire /usr/src/PG/contrib/start-scripts/. Par exemple sous Debian, on copiera le script linux vers /etc/init.d/postgres.

Il est nécessaire de modifier le script de démarrage afin de positionner différentes variables (des commentaires indiquent la zone où il peut être nécessaire d'apporter des modifications). Si vous n'avez rien changé, il n'est pas utile de modifier quoi que ce soit. Néanmoins, pour des questions pratiques, il peut être intéressant de personnaliser le fichier où PostgreSQL va enregistrer les différents événements :

# On crée le fichier où on va logger

touch /var/log/pgsql

chown postgres:postgres /var/log/pgsql

# On interdit l'écriture pour les autres (sécurité)

chmod go-w /var/log/pgsql

On positionne alors la variable PGLOG dans le script de démarrage pour pointer vers notre répertoire.

Ensuite il faut créer les liens symboliques pour que le service soit démarré aux bons niveaux d'exécution. Sous Debian update-rc.d postgres defaults. Sous Fedora regardez du côté de chkconfig.

Le serveur est maintenant prêt à être démarré. Pour cela, il suffit d'utiliser le script que l'on vient de mettre en place : /etc/init.d/postgres start.

III - Première connexion

Toutes les opérations d'administration se font en étant connecté à une base, notamment la création d'une base. Mais alors, comment créer la première base ?

Lors de l'initialisation du "cluster" de base de données, deux bases sont créées : template1 et template0. Les premières opérations se font donc en se connectant à la base template1 ou via le shell. Pour se connecter, on utilisera la commande :

psql [NomBase] [-U NomUtilisateur]

Si aucun paramètre n'est précisé, c'est le nom d'utilisateur système qui est utilisé (si vous êtes connecté sous le login nono, psql tentera de se connecter à la base nono avec le nom d'utilisateur nono).

Comme avant toute modification, seul l'utilisateur postgres existe, on se connectera avec la commande :

psql -U postgres template1

Il est alors possible d'effectuer différentes opérations d'administration.

IV - Ajout du langage PL/PGSQL par défaut

PostgreSQL vient avec plusieurs langages côté serveur qui permettent d'écrire des procédures stockées. Pour cela il faut les installer.

Le langage propre à PostgreSQL s'appelle PL/PGSQL. Nous allons l'installer sur template1 afin que toutes les bases en disposent (cf. la section ).

On ajoute le langage grâce à la commande shell suivante :

createlang plpgsql template1

V - Templates de bases

Lors de la création d'une base, c'est template1 qui est copiée par défaut, toute modification de template1 est donc répercutée sur les nouvelles bases de données (c'est pour cela qu'on lui a ajouté le langage PL/PGSQL).

Template0 est une base modèle qui ne doit pas être modifiée, elle contient des objets prédéfinis selon la version de PostgreSQL. Pour indiquer que l'on veut créer une base à partir d'un autre modèle, on utilise (une fois connecté à template1 par exemple, en tant que postgres) :

CREATE DATABASE db TEMPLATE template0;

ou, à partir d'un shell :

createdb -T template0 db

La table pg_database (commune à toutes les bases PostgreSQL) contient des paramètres utiles sur les bases :

- datistemplate : si true la base peut être clonée par n'importe quel utilisateur ayant les privilèges CREATEDB si false seuls les administrateurs et le propriétaire de la base le peuvent ;

- datallowconn : si false, aucune nouvelle connexion n'est acceptée, mais les sessions existantes ne sont pas détruites.

VI - Utilisateurs

Création d'un utilisateur (valable aussi pour une modification via ALTER) :

CREATE USER toto [WITH PASSWORD 'password'];

Si aucun mot de passe n'est utilisé, l'utilisateur ne pourra jamais s'identifier. Il est possible de donner le droit à un utilisateur d'en créer d'autres, il devient alors superuser, cela se fait en ajoutant la directive CREATEUSER à la suite de la commande de création.

Il est également possible d'autoriser un utilisateur à créer des bases de données en ajoutant la directive CREATEDB à la suite de la commande de création. L'ajout de privilèges est également possible via la commande ALTER USER.

VII - Création de base et privilèges

La création de base se fait via la commande suivante :

CREATE DATABASE nomdb [OWNER utilisateurpropriétaire]

Il est également possible d'utiliser, à partir du shell, la commande

createdb  [-O propriétaire] nomdb

Le propriétaire d'une base et les superuser sont les seuls à pouvoir manipuler les objets d'une base (tables, vues, etc.). Il est cependant possible d'accorder des privilèges sur une base :

GRANT ALL PRIVILEGES ON DATABASE nomdb TO nomutilisateur;

Donnera tous les privilèges à nomutilisateur sur la base nomdb, n'oubliez pas la clause ON DATABASE sinon PostgreSQL considère qu'on agit au niveau d'une table. Il est possible d'affiner les droits que l'on donne (GRANT UPDATE, SELECT, etc.), en étant connecté à la base contenant la table :

GRANT SELECT ON nomtable TO nomutilisateur;

Il est possible de faire suivre ON de TABLE, mais celui-ci est sous-entendu. La commande GRANT s'applique également aux FUNCTION, LANGUAGE, etc. pour cela voyez la référence de la commande GRANT.

Faites attention aux privilèges que vous donnez aux utilisateurs au niveau des langages (GRANT USAGE ON LANGUAGE), car il est possible via certains langages de parasiter le serveur. De plus les utilisateurs utilisant les fonctions ne connaissent pas forcément leur contenu et peuvent être 'espionnés'.

VIII - Tablespaces

La gestion des tablespaces est assez basique, on affecte un répertoire au tablespace et c'est PostgreSQL qui gère les objets qui seront placés à l'intérieur :

CREATE TABLESPACE nomtbspace LOCATION 'path/vers/le/tablespace';

Il n'y a pas de raison de créer plus d'un tablespace par système de fichiers. Pour affecter un tablespace à une table, un index ou une base entière, on utilise la commande :

CREATE TABLE tb(int id) TABLESPACE nomtbspace;

Il est possible de définir un tablespace par défaut en utilisant la commande :

SET default_tablespace = nomtbspace;

Par défaut, c'est le tablespace de template1 qui est utilisé.

IX - Fichiers de configuration

IX-A - Fichier principal

Nous ne présentons ici que les options de configuration principales, pour une description complète, référez-vous à http://www.postgresql.org/docs/8.0/interactive/runtime-config.html.

Le fichier de configuration principal se nomme postgresql.conf, il se trouve normalement dans le répertoire de données de PostgreSQL (défini par la variable d'environnement PGDATA ou par l'option -D sur la ligne de commandes) mais il est possible de redéfinir son emplacement en utilisant l'option -c config_file=/path/vers/le/fichier. Il s'agit d'une liste de couples nom/valeur, éventuellement séparés par un signe égal. Le signe dièse (#) permet d'introduire des commentaires.

Les valeurs passées via la ligne de commande (option (-c nom=valeur)*) sont prioritaires par rapport à celles mentionnées dans le fichier postgresql.conf. Il est également possible pour le client de positionner la variable d'environnement PGOPTIONS (au même format que les options en ligne de commande). De plus, il est possible de définir des paramètres au niveau utilisateur ou base via les commandes ALTER ou au niveau session avec la commande SET.

IX-A-1 - Emplacement des fichiers

On peut tout d'abord paramétrer les emplacements de divers autres fichiers et répertoires :

- data_directory : répertoire de stockage des données ;

- hba_file : indique le fichier de configuration utilisé pour la configuration de l'identification au niveau hôte (voir Fichier d'identification), pg_hba.conf ;

- ident_file : indique le fichier de configuration utilisé pour la configuration de l'identification par la méthode ident, pg_ident.conf ;

- external_pid_file : fichier supplémentaire contenant le PID du programme pour être utilisé par des applications d'administration.

IX-A-2 - Paramètres de connexion

Ces paramètres contrôlent entre autres l'adresse sur laquelle écoute le serveur (listen_adress), par défaut 'localhost', le port (port), par défaut 5432, le nombre de connexions maximum (max_connections), par défaut 100 ou le nombre de connexions réservées au superuser (superuser_reserved_connections), par défaut 2.

IX-A-3 - Erreurs et journalisation

Cette partie contrôle la méthode utilisée pour la journalisation (log_destination), par défaut stderr (que nous avons redirigé en positionnant PGLOG), mais il est possible d'utiliser syslog (et eventlog sous Windows). Au lieu de positionner la variable PGLOG dans le script de démarrage pour rediriger les messages, il est possible d'employer une méthode plus fine. La directive redirect_stderr permet de définir si l'on redirige le flux d'erreurs selon les paramètres spécifiés par la suite :

- log_directory : spécifie le répertoire où sont stockés les logs. S'il n'est pas absolu, le chemin est relatif à PGDATA ;

- log_filename : indique le nom du fichier de log, il est possible d'inclure des caractères spéciaux (%Y, %m, etc.) pour y faire figurer des données temporelles ;

- log_rotation_age, log_rotation_size : temps en minutes ou taille en kilo-octets au bout duquel on effectue une rotation (changement de fichier log).

Divers autres paramètres offrent la possibilité de configurer les événements à logger et les données à inclure dans les enregistrements.

IX-A-4 - Autres directives

Les autres directives sont plus pointues et il ne semble pas nécessaire de les décrire ici, cependant, selon les demandes ou les remarques, elles pourront être incluses.

IX-B - Fichier d'identification niveau hôtes

Le fichier de configuration de l'identification au niveau hôte, traditionnellement nommé pg_hba.conf, contrôle l'authentification des clients. La commande initdb en crée un par défaut.

Le format du fichier pg_hba.conf est une suite d'enregistrements (un par ligne, les lignes blanches sont ignorées, il est possible d'inclure des commentaires précédés d'un signe dièse). Les enregistrements indiquent différents paramètres de connexion, le premier enregistrement qui correspond aux paramètres de connexion du client est sélectionné et si l'authentification échoue, la connexion est refusée.

Tout d'abord, on indique la méthode de connexion : local est une connexion via les sockets Unix, host une connexion TCP/IP avec ou sans SSL, hostssl/hostnossl sont identiques à host, mais correspondent respectivement à une connexion avec SSL et une connexion sans SSL.

Vient ensuite le nom de la ou des (séparés par des virgules) base(s) de données. Les valeurs spéciales all, sameuser et samegroup indiquent respectivement toutes les bases, les bases ayant le même nom que l'utilisateur et les bases ayant le même nom que le groupe de l'utilisateur. Un fichier contenant les noms des bases de données peut être indiqué en le préfixant par @.

Il faut ensuite indiquer le ou les utilisateur(s) autorisé(s) à se connecter. La valeur spéciale all peut là aussi être utilisée. Si plusieurs utilisateurs sont spécifiés, leur nom doit être séparé par des virgules.

Dans le cas où l'on utilise une méthode de connexion autre que local, il faut indiquer soit l'adresse CIDR (Classless Inter-Domain Routing), c'est-à-dire une adresse IP suivie d'un slash et du nombre de bits pour le masque (192.168.1.0/24 par exemple) ; soit une adresse IP suivie du masque de sous-réseau (192.168.1.0 255.255.255.0).

Puis, il faut préciser la méthode d'identification, qui peut être trust (aucune identification, pas de mot de passe), reject (refus de connexion), md5 (mot de passe MD5), crypt (à n'utiliser qu'avec les clients antérieurs à la version 7.5), password (mot de passe en clair), krb4 ou krb5 (Kerberos version 4 ou 5), ident (le nom d'utilisateur est obtenu par la méthode ident puis mis en relation avec une liste d'utilisateurs autorisés) ou pam (utilisation du service PAM).

Enfin, si vous choisissez la méthode d'identification ident, il faut indiquer le nom de la map utilisée pour déterminer si un utilisateur est autorisé à se connecter ou non. La valeur spéciale sameuser peut être utilisée pour indiquer qu'un utilisateur ne peut se connecter à la base que sous le nom renvoyé par ident. Les maps sont déclarées dans le fichier pg_ident.conf (ou dans le fichier indiqué par la directive ident_file) qui contient des enregistrements au format nom_map nom_utilisateur_ident nom_utilisateur_base. Lisez la page Authentification Methods pour plus d'informations. En particulier : "The Identification Protocol is not intended as an authorization or access control protocol." (cette méthode est donc peu conseillée).

X - Conclusion

Cet article vous a permis, je l'espère, d'en apprendre un peu plus sur la mise en place d'un serveur PostgreSQL et sur quelques tâches d'administration basiques. Vous devriez maintenant être en mesure d'accéder à vos bases et d'y créer des objets. Pour plus d'informations, reportez-vous au site de PostgreSQL.

XI - Remerciements

Merci à GrandFather et hpalpha pour leurs suggestions sur la configuration. Merci à Bestiol pour sa relecture.

XII - Téléchargements

- L'article au format HTML zippé.

- L'article au format PDF.

- Les sources de PostgreSQL.

- Les sources de libreadline.