Apportez votre aide…
Ceci est une ancienne révision du document !
PostgreSQL
Introduction
PostgreSQL est un système de gestion de bases de données (SGBD) très performant sous licence BSD dont les performances sont comparables à Oracle 9.
Il propose de très nombreuses fonctionnalités, tout en respectant les standards SQL : SQL 92, 99 et en partie la norme SQL2003. En outre, il intègre plusieurs langages embarqués (Perl, Python, Java) depuis de nombreuses années. Pour en savoir plus
Documentation
La documentation ainsi que d'autres informations sont disponibles sur le site officiel de PostgreSQL. La communauté postgresqlfr.org la reprend en français.
Installation du serveur PostgreSQL
On commence par installer le paquet postgresql.
Les paquets supplémentaires suivants seront installés : libpq5 postgresql-9.1 postgresql-client-9.1 postgresql-client-common
La version installée par défaut (Precise Pangolin) est la 9.1. Il est possible d'installer la dernière version majeur (ici la 8.4). Il faut alors le préciser à l'installation. postgresql-8.4.
Notez que l'installation ajoute l'utilisateur postgres à la liste des utilisateurs. Utilisez finger si vous souhaitez de plus amples informations sur ce nouvel utilisateur (finger est un utilitaire permettant de consulter les informations relatives aux utilisateurs système, il n'est pas installé par défaut dans ubuntu)
finger postgres
L'utilisateur postgres
PostgreSQL est un serveur qui permet de se connecter à différentes bases de données. Par défaut, seul l'utilisateur postgres peut se connecter.
Toutes les opérations d'administration se font, au départ, avec l'utilisateur postgres. À la fin de l'installation, celui-ci ne possède pas de mot de passe : c'est un utilisateur bloqué et le mieux est qu'il le reste. La première chose à faire sera de créer un nouvel utilisateur, mais pour ce faire, il faut se connecter au moins une fois en tant qu'utilisateur postgres. Pour devenir postgres et faire les opérations d'administration qui suivent, utilisez sudo :
$ sudo -i -u postgres Password:
exit permettra, à la fin de cette session d'administration dans PostgreSQL, de reprendre la main en tant qu'utilisateur du système.
Désormais, l'invite de commande doit mentionner que vous êtes actif en tant que postgres. Pour lancer l'outil d'administration de PostgreSQL, tapez simplement :
psql
Vous devriez obtenir quelque chose comme :
psql (9.1.3) Type "help" for help. postgres=#
Notez que vous avez une interface en ligne de commande, et que vous êtes connecté en tant que "root", comme l'indique le #
en bout de ligne.
Cette ligne de commande sera bien sûr utilisée pour exécuter des requêtes SQL, et aussi des commandes internes à postgreSQL (elles commencent toutes par un antislash)
Petit guide de survie pour le client psql
\h pour l'aide-mémoire des commandes SQL \? pour l'aide-mémoire des commandes psql \g ou point-virgule en fin d'instruction pour exécuter la requête \q pour quitter
encodage UTF-8
L'installateur initialise le cluster postgresql avec le support de jeux de caractères SQL_ASCII
Pour ceux qui souhaitent privilégier l'encodage UTF-8 / locale FR-fr, il faut supprimer le cluster et le recréer.
jeux de caractères par défaut
- Connexion en tant qu'utilisateur postgres
sudo -i -u postgres
psql -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ----------+----------+-----------+---------+-------+----------------------- postgres | postgres | SQL_ASCII | C | C | template0 | postgres | SQL_ASCII | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | SQL_ASCII | C | C | =c/postgres + | | | | | postgres=CTc/postgres
supprimer le cluster et le recréer
- Identifier la version et le nom de votre cluster
pg_lsclusters
Version Cluster Port Status Owner Data directory Log file 9.1 main 5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
- Supprimer le cluster
pg_dropcluster 9.1 main --stop
- Créer le cluster
pg_createcluster 9.1 main
- Démarrage
pg_ctlcluster 9.1 main start
Les bases systèmes seront alors conformes aux locales système
psql -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | template0 | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres
Créer un utilisateur PostgreSQL
Méthode rapide, non sécurisée (-> privilégier la méthode décrite dans le chapitre suivant)
postgres
est à psql ce que root
est à Ubuntu. Il ne faut l'utiliser que pour les tâches d'administration, en aucun cas s'en servir pour un applicatif, sinon vous allez créer une faille de sécurité.
Par facilité, nous allons assimiler les utilisateurs du système à ceux qui se connecteront avec psql, mais ce n'est nullement obligatoire. Par la suite, nous supposerons que votre login système est nom_utilisateur
.
Commencez par créer un nouvel utilisateur, qui portera par exemple le même nom que vous. Dans la ligne de commande ci-dessous, tapez (rappel : la partie « postgres=# » est l'invite de commande, il ne faut pas la retaper ! Ainsi, la commande ci-dessous requiert de taper « CREATE USER <nom_utilisateur>; », en remplaçant <nom_utilisateur> par un identifiant bien choisi) :
postgres=# CREATE USER <nom_utilisateur>;
N'oubliez pas le point-virgule à la fin…
Par défaut, votre nouvel utilisateur n'a aucun droit. Donnez-lui la possibilité de créer de nouvelles bases de données :
postgres=# ALTER ROLE <nom_utilisateur> WITH CREATEDB;
Ensuite, créez une base de données pour votre utilisateur. Par exemple, vous pouvez prendre comme nom : nom_utilisateur
. Ainsi, la connexion sera automatique.
postgres=# CREATE DATABASE <nom_base_de_donnee> OWNER <nom_utilisateur>;
Attribuer un mot de passe à l'utilisateur pour qu'il puisse se connecter à la base (le ENCRYPTED permet l'utilisation de md5 dans le pg_hba.conf) :
postgres=# ALTER USER <nom_utilisateur> WITH ENCRYPTED PASSWORD 'mon_mot_de_passe';
Vous pouvez maintenant quitter, "redevenir vous-même" et relancer psql directement, en étant cette fois connecté sous votre propre nom :
postgres=# \q postgres@ubuntu:~$ exit nom_utilisateur@ubuntu:~$ psql nom_base_de_donnee
La ligne de commande est devenue :
nom_base_de_donnee=>
Notez la transformation du # en > : vous n'êtes plus superutilisateur…
Pour aller plus loin
Méthode alternative pour créer un utilisateur
Vous pouvez aussi créer vos utilisateurs directement en ligne de commande, depuis le terminal standard, mais en étant connecté en tant que postgres, bien sûr.
Cette méthode est beaucoup plus sûre que la précédente. Tous les utilisateurs auront des mots de passe chiffrés. (Ce qui n'est pas le cas lorsque l'on emploie la méthode précédente)
Il faut d'abord modifier le fichier de configuration pour autoriser les connexions via mot de passe chiffré :
sudo nano /etc/postgresql/x.x/main/pg_hba.conf
Remplacez "x.x" par le numéro de version de PostgreSQl installée sur votre ordinateur (exemple : 9.1).
Les utilisateurs "avancés" noteront que le fichier est amplement commenté. Voir la documentation pour plus d'informations.
Modifier le fichier en remplaçant ident sameuser par md5 afin d'obtenir les lignes suivantes :
... # Database administrative login by Unix domain socket local all postgres peer # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 ...
La modification des paramètres de connexion au serveur nécessite son redémarrage pour que ces derniers soient pris en compte.
$ sudo /etc/init.d/postgresql restart
ou
$ sudo pg_ctlcluster 9.1 main restart
On peut ensuite créer notre utilisateur :
$ sudo -i -u postgres $ createuser -P <nom_utilisateur> Enter password for new role: Enter it again: Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) y Shall the new role be allowed to create more new roles? (y/n) y
Puis, on lui crée une base de données dédiée :
$ createdb -O <nom_utilisateur> -E UTF8 <ma_database>
L'option -O <nom_utilisateur> est là pour préciser l'identité du propriétaire de la base <ma_database>.
Ensuite, on peut se connecter via :
$ psql -U <nom_utilisateur> Mot de passe pour l'utilisateur <nom_utilisateur> :
ou, si le nom de la base de l'utilisateur n'est pas le même que celui de l'utilisateur :
$ psql -U <nom_utilisateur> <nom_database_de_l_utilisateur> Mot de passe pour l'utilisateur <nom_utilisateur> :
Personnalisation
Le tuning d'un serveur postgreSQL se fait grâce au fichier postgresql.conf généralement positionné dans /etc/postgresql/9.1/main/
Plusieurs paramètres sont faciles à modifier et peuvent apporter un gain de performance important:
- shared_buffers = peut être positionné à 25% de la RAM pour un serveur dédié. Attention de ne pas trop monter ce paramètre tout de même car il faut laisser de la RAM au noyau pour gérer son cache
- wal_buffers = il est raisonnable de la passer à 8MB
- work_mem = à ajuster en fonction du nombre de connexion à la base, valeur entre 10MB et 100MB
- maintenance_work_mem = mémoire allouée aux actions de maintenance. Dépend de la taille des bases et de la RAM dispo. Perso, je l'a met à 25% de la RAM
- checkpoint_segments = mettre au moins 10
- effective_cache_size = pour un serveur dédié compter 2/3 de la RAM
Ces quelques paramètres doivent suffire dans la majorité des cas. Si vous souhaitez aller plus loin dans le tuning de PostgreSQL, je vous conseille d'aller voir là documentation PostgreSQL (qui existe en français)
Rôles et droits
Cette notion a été effleurée car les utilisateurs dont il a été question dans les lignes ci-avant sont des rôles au sens de postgresql. Les droits affectés à ces rôles permet une gestion fine des autorisations d'accès aux différents objets de la base de données (tables, vues, fonctions, champs…)
La documentation postgresql est explicite à ce sujet, le mieux est de la consulter !
Gestion des connexions
Postgresql permet en amont des droits attribués aux rôles sur la baése de données, une gestion des connexions. Celle ci est définie dans les fichiers de configuration /etc/postgresql/x.x/main/pg_hba.conf et /etc/postgresql/x.x/main/postgresql.conf dans le chapitre # CONNECTIONS AND AUTHENTICATION
La documentation complète est disponible à ces adresses
ajout de contributions
Différentes contributions sont disponibles (cf la documentation PostgreSQL). Leur installation (et désinstallation) est facilitée par l'existence de paquets dédiés
sudo apt-get install postgresql-contrib Les paquets supplémentaires suivants seront installés : libossp-uuid16 postgresql-contrib-9.1
Les scripts permettant l'application de ces extensions sont disponibles dans /usr/share/postgresql/9.1/extension/*.sql L'ajout se fait par exécution du script concerné sur la base de données souhaitée.
$ psql -U <nom_utilisateur> <nom_database> -f /usr/share/postgresql/9.1/extension/*.sql
Installation de la cartouche spatiale postgis
Installer le paquet le paquet postgresql-9.1-postgis
Les paquets supplémentaires suivants seront installés : libgeos-3.2.2 libgeos-c1 libproj0 postgis proj-data
Clients graphiques
Le client pgadmin3
PgAdmin III est un outil graphique d'administration de votre serveur PostgreSQL. L'application pgAdmin III peut être utilisé pour administrer les serveurs PostgreSQL 7.3 et les versions supérieures. PgAdmin III existe pour toutes les plateformes dont, évidemment, Ubuntu, KUbuntu, XUbuntu.
PgAdmin III a été conçu pour répondre aux besoins de tous les utilisateurs, depuis la rédaction de simple requêtes SQL au développement complexe de base de données. L'interface graphique supporte toutes les fonctionnalités de PostGreSQL et permet une administration simple. L'application inclut aussi un éditeur de requête avec coloration syntaxique, un éditeur de code, un agent de gestion de tâche automatique, un support pour les réplication via Slony-I et bien d'autres fonctionnalités.
Installation du client pgadmin3
On commence par installer les paquets nécessaires : pgadmin3
sudo apt-get install pgadmin3
Cela va également installer les paquets suivants :
- libpq4
- libwxbase2.6-0
- libwxgtk2.6-0
- pgadmin3-data
Lancement du client pgadmin3
Sous Ubuntu, pour lancer PgAdmin III, aller dans le menu Applications ⇒ programmation ⇒ pgAdmin III
En suivant la section ci-haut "Méthode rapide, non sécurisée" pgadmin3 n'acceptera pas la connexion aux bases de données. Il faudra definir un mot de passe à l'utilisateur avant (voir la procédure sécurisée)
$ psql -d template1 -c "alter user <nom_utilisateur> with password '<votre_mot_de_passe>'"
Utilisation du client pgadmin3 via ssh
La connexion à la base de donnée necessite la création d'un tunnel
ssh -L 5555:localhost:5432 utilisateur@adresse_du_serveur
Le port 5555 correspond au port de redirection sur le port local (vous pouvez en choisir un autre)
Le port 5432 correspond au port d'écoute de postgres (ne pas modifier)
Cet exemple s'entend en connexion ssh sur le port standard 22
Si vous avez modifié et vous devriez le faire pour des raisons de sécurité le port d'écoute ssh, utilisez cette commande : (1444 est un exemple, vous pouvez en chosir un autre)
ssh -L 5555:localhost:5432 utilisateur@nom_du_serveur -p 1444
Le serveur vous demandera votre mot de passe
Ouvrez maintenant pgadmin3
Nom : nom_de_votre_database
Hôte : localhost
Port TCP : 5555 (selon l'exemple ci-dessus)
Vous devriez être maintenant connecté
Le client phppgadmin
Installation du client phppgadmin
Il suffit d'installer le paquet phppgadmin, à condition que vous ayez un serveur Web fonctionnel et reconnaissant php.
extension=pgsql.so
à la main (après celle sur mysql) dans le fichier /etc/php5/apache2/php.ini. A confirmer ou infirmer.
Confirmé, chez moi idem. Et sous gutsy, comme pour phpmyadmin, il faut aussi créer le lien symbolique vers /usr/share/phppgadmin.sudo ln -s /etc/phppgadmin/apache.conf /etc/apache2/conf.d/phppgadmin.conf
puis redemarrer apache:
sudo /etc/init.d/apache2 reload
Lancement du client phppgadmin
Lance votre navigateur Web préféré à l'adresse http://localhost/phppgadmin/.
sudo ln -s /usr/share/phppgadmin/ /var/www/
Utilisation du client phppgadmin
Connexions vers PHP
PHP 4 : installer php4-pgsql.
PHP 5 : installer php5-pgsql.
php5-pdo (voir la documentation officiel pour PDO) :
sudo pear install pecl-pdo_pgsql
PHP 5.2 : Cela se fait automatiquement.
: A compléter
Récupération du contenu d'une base de données après un crash serveur
Supposons que vous ayez installé sous ubuntu server une application d'entreprise s'appuyant sur une base de données postgresql (cette procédure a été testée avec redmine), et qu'un crash se produise qui rende impossible de redémarrer le serveur. Se pose alors le problème de la récupération des données. La procédure est relativement simple. Il va vous falloir:
- Monter votre ancienne installation depuis une autre installation ou via un live cd
- chrooter dessus
- changer d'utilisateur pour l'utilisateur postgres
- démarrer la base de données
- effectuer un pg_dump pour sauvegarder la base de données.
Ceci fait, après avoir réinstallé votre application d'entreprise sur un nouveau serveur ubuntu neuf, il n'y aura plus qu'à y importer la sauvegarde. Venons en aux détails techniques.
Montage et chroot sur votre ancienne installation depuis un live CD ou depuis le nouveau serveur
sudo fdisk -l # pour vous aider à trouver la bonne partition sudo mount /dev/sdYX /mnt # montage de celle-ci en remplaçant le Y par la lettre du volume, et le X par le bon numéro de partition sudo mount --bind /dev /mnt/dev # lien symbolique du dossier /dev en cours d'utilisation vers le disque monté sudo mount -t proc /proc /mnt/proc # Étape facultative? sudo chroot /mnt # mise à la racine du disque monté
démarrage de la base de données, changement d'utilisateur, et sauvegarde de son contenu
Démarrez la base de données dans le chroot
service postgresql-8.4 start # démarre postgresql dans le chroot
service postgresql-8.4 stop
Maintenant, connectez-vous en tant que postgres, et effectuez la sauvegarde.
sudo -i -u postgres # change l'utilisateur courant (root) pour l'utilisateur postgres pg_dump -f /chemin/de/sauvegarde/choisi/NOM_BDD.sql NOM_BDD # effectue la sauvegarde de la base de données
Sortie du chroot en douceur
Au cours de ces opérations, vous avez monté des partitions et créé un chroot. Il ne faut pas oublier de les exécuter dans l'ordre inverse, si possible proprement.
exit # permet de cesser d'agir en tant qu'utilisateur postgres service postgresql-8.4 stop # permet de stopper le serveur postgresql dans le chroot exit # permet de quitter le chroot sudo umount /mnt/dev/ # désactive le lien symbolique du dossier /dev en cours d'utilisation vers le disque monté sudo umount /mnt/proc/ # désactive le lien symbolique du dossier /proc en cours d'utilisation vers le disque monté sudo umount /mnt/ # démontage final de l'ancienne partition d'installation du serveur
Restaurer la sauvegarde sur la nouvelle installation
La restauration de la sauvegarde est très simple : connectez-vous sur votre nouvelle installation, et changez d'utilisateur pour postgres. Supprimez la base de données créée par défaut pour éviter tout conflit (une petite sauvegarde préalable de celle-ci ne fera pas de mal), recréez-la, et importez-y votre base sauvegardée :
sudo -i -u postgres # change l'utilisateur courant (root) pour l'utilisateur postgres dropdb NOM_BDD # efface la base de données existante createdb --owner=NOM_APPLI --encoding=utf-8 NOM_BDD # recréée une base de données vide psql -f /chemin/de/sauvegarde/choisi/redmine.sql NOM_BDD # importe la base de données sauvegardée
–owner=NOM_APPLI
de la troisième commande suppose que vous avez configuré un utilisateur système pour votre application