Héberger un serveur PostgreSQL
Voici quelques manipulations de base pour démarrer avec PostgreSQL. Je m’adresse à ceux qui savent ce qu’est une base, une table… et qui souhaitent prendre en main leur hébergement.
Ce tutoriel est tenu à jour régulièrement. Dernière révision : 13 août 2016.
Je n’ai utilisé PostgreSQL que sous Debian, mais j’imagine que ce que je décris ici doit s’adapter aux autres distributions sans trop de différences.
Les commandes préfixées d’un # sont à lancer en root. Les commandes préfixées d’un % sont à lancer en simple utilisateur.
Installation
On commence par installer PostgreSQL :
# apt-get install postgresql postgresql-client
Il faut ajuster les réglages de la mémoire partagée. Modifiez /etc/sysctl.d/30-postgresql-shm.conf :
# Maximum size of shared memory segment in bytes kernel.shmmax = 151798690 # Maximum total size of shared memory in pages (normally 4096 bytes) kernel.shmall = 2097152
Pour éviter de redémarrer le système, vous pouvez faire :
# sysctl -w kernel.shmmax=151798690
# sysctl -w kernel.shmall=2097152
# service postgresql restart
Si vous faites tourner PostgreSQL dans un conteneur OpenVZ, il faut éditer le fichier /etc/vz/conf/1XX.conf correspondant au conteneur, pour changer ces paramètres :
KMEMSIZE="unlimited" LOCKEDPAGES="unlimited" PRIVVMPAGES="unlimited" SHMPAGES="unlimited" OOMGUARPAGES="unlimited"
Les mises à jour
La compatibilité du format de cluster n’est pas garantie d’une version de PostgreSQL à l’autre, et lors des mises à jour les clusters ne sont pas convertis. Il faut le faire manuellement.
Voici les commandes pour passer de la version de wheezy (9.1) à celle de jessie (9.4) :
# pg_dropcluster 9.4 main --stop
# pg_upgradecluster 9.1 main
# service postgresql restart
Mais je vous recommande de lire /usr/share/doc/postgresql-common/README.Debian.gz et la manpage de pg_upgradecluster pour comprendre ce que vous faites. Et, bien entendu, faites une sauvegarde de vos bases avant toute opération de maintenance.
Les manipulations en CLI
Pour les manipulations en ligne de commande, éditez /etc/postgresql/X.Y/main/pg_hba.conf, commentez la ligne déjà présente pour l’utilisateur postgres, et insérez cette ligne à la place :
local all postgres trust
Puis redémarrez PostgreSQL :
# service postgresql restart
Une fois que vous aurez fini vos manipulations, il faudra annuler ce changement.
Les accès
Pour changer le mot de passe d’un rôle :
# psql -U postgres
psql (version)
Saisissez « help » pour l'aide.
postgres=# ALTER USER '_Nom_rôle_' PASSWORD '_Nouveau_mdp_';
Pour changer le nom d’un rôle :
# psql -U postgres
postgres=# ALTER USER '_Nom_rôle_' RENAME TO '_Nouveau_nom_';
Pour créer un rôle admin :
# su postgres -c "createuser -s -d -r -P -E _Nom_rôle_"
Pour supprimer un rôle :
# psql -U postgres
postgres=# DROP ROLE '_Nom_rôle_';
Pour changer le rôle auquel une base appartient :
# psql -U postgres
postgres=# ALTER DATABASE '_Nom_base_' OWNER TO '_Nom_rôle_';
Cas d’usage : phpBB 3
J’ai installé phpBB pour une association. Comme le serveur était bien chargé, j’ai préféré éviter le classique MySQL : il n’était pas installé, alors que PostgreSQL était déjà là. Voilà quelques manips que j’ai eu à faire.
Il faut ajouter la ligne suivante dans /etc/postgresql/X.Y/main/pg_hba.conf :
local phpbb3 phpbb3 md5
Cette ligne doit être placée avant la ligne décrivant la méthode de connexion (md5, ou ident, ou trust…) employée par défaut pour un type de connexion (local, ou host, ou hostssl…). C’est-à-dire pour tous les utilisateurs qui n’ont pas été mentionnés dans les lignes précédentes. Si ce passage n’est pas clair, référez-vous à /usr/share/doc/phpbb3/README.postgresql et aux commentaires au début du fichier /etc/postgresql/X.Y/main/pg_hba.conf.
Pour les mises à jour du paquet phpbb3, il faut modifier le shell de l’utilisateur système postgres, à cause d’un bug dans dbconfig-common. Avant de lancer apt-get, éditez /etc/passwd pour mettre « /bin/sh » à la place de « /bin/false ». Une fois la mise à jour terminée, annulez ce changement.
