Configuration de PostgreSQL

Il est maintenant temps de configurer correctement (et d’optimiser un peu) le serveur PostgreSQL. CentOS 6 fournit par défaut la version 8.4. Qui fonctionne très bien, mais la récente version 9.2 apporte de sympathiques optimisations que je ne veux pas rater. N’oublions que cette petite machine a une puissance limitée et un disque lent. Installons donc ce merveilleux dépôt:

yum localinstall http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm

Editez /etc/yum.repos.d/CentOS-Base.repo et ajoutez exclude=postgresql* à la fin des sections base et updates, puis:

yum install postgresql92-server
service postgresql-9.2 initdb
chkconfig postgresql-9.2 on

Editez /var/lib/pgsql/9.2/data/postgresql.conf en vue de modifier quelques valeurs. Ici, j’ai fais confiance à pgtune en utilisant la commande suivante:

pgtune -T Web -M 2097152 -i /var/lib/pgsql/9.2/data/postgresql.conf -o /var/lib/pgsql/9.2/data/postgresql.conf.pgtune

Nous declarons 2Go de mémoire vive disponible (2048*1024) pour PostgreSQL car les ressources seront partagées avec le serveur web. Les valeurs proposées par pgtune sont:

maintenance_work_mem = 131072
checkpoint_completion_target = 0.7
effective_cache_size = 1536kB
work_mem = 10240
wal_buffers = 4MB
checkpoint_segments = 8
shared_buffers = 512kB
max_connections = 200

Comme je ne suis point un spécialiste de l’optimisation de PostgreSQL, je ferais confiance à ces valeurs, et modifie en conséquence postgresql.conf. Mais l’établissement des connections étant assez coûteux, nous utiliserons donc un connection pooler nommé pgbouncer (disponible dans le dépôt que nous avons installé quelques lignes plus haut) afin d’alléger ce coût. Donc:

yum install pgbouncer
chkconfig pgbouncer on

Comme PostgreSQL n’accepte que les connections locales par default, nous n’avons rien à faire de plus de ce côté-là. Le seul point est, qu’en tant que super utilisateur, vous pouvez taper su - postgres puis psql et faire ce que bon vous semble avec les données. Comme l’on n’est jamais protégé contre une faille exploitable à distance à venir dans linux, ssh, apache ou quoi que ce soit d’autre tournera sur cette machine, nous ajoutons donc une authentification par mot de passe pour accéder au super utilisateur PostgreSQL.

service postgresql-9.2 start
su - postgres
psql
alter user postgres with password 'MOTDEPASSE';

Il faut ensuite éditer /var/lib/pgsql/9.2/data/pg_hba.conf and modifier les lignes suivantes de cette manière:

local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Une autre petie chose est le port par défaut de PostgreSQL (5432) que l’on ne peut pas configurer avec certains logiciels (impossible de me souvenir d’un nom, mais je suis persuadé d’en avoir déjà croisé). Ce qui signifie l’impossibilité d’utiliser le pooler de connection qui lui utilise le post 6432 par défaut. Inversons donc les ports afin de contourner cette limitation de certains logiciels. Rien de compliqué: éditez le (nouveau) fichier /etc/sysconfig/pgsql/postgresql-9.2 et mettez-y:

 PGPORT=6432 

Éditez maintenant /etc/pgbouncer/pgbouncer.ini et modifiez à partir de la ligne 39 de cette manière.

listen_addr = 127.0.0.1, ::1 #add ipv6 localhost
listen_port = 5432

Modifiez auth_type à md5. Ensuite, juste après databases au début du fichier, ajoutez:

 postgres = dbname=postgres host=localhost port=6432 user=postgres 

Enfin, éditez le (nouveau) fichier /etc/pgbouncer/userlist.txt pour y mettre:

 "postgres" "MOTDEPASSE" 

C’est fait ! Vous devrez à l’avenir bien sûr ajouter une nouvelle ligne dans la section databases du fichier pgbouncer.ini, ainsi qu’une ligne avec l’utilisateur et mot de passe dans userlist.txt. N’oubliez pas de créer l’utilisateur correspondant (et de lui donner un mot de passe !) sur le serveur PostgreSQL.
Maintenant, redémarrez PostgreSQL puis démarrez pgbouncer:

service postgresql-9.2 restart
service pgbouncer start

À partir de là, pgbouncer prendra en charge de manière transparente les demandes de connection :) (car nous sommes en mode session par défaut, faites attention si vous en changez !). C’est tout pour le moment, nous y reviendrons quand nous en serons à la conversion de base de données.
MAJ 20121203: correction de listen_addr pour pgbouncer (une seule ligne pour déclarer les adresses), et de maintenance_work_mem et work_mem pour lesquelles pgbouncer donne des valeurs non valables.

Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

Ajouter un rétrolien

URL de rétrolien : https://www.quelquesmots.fr/dotclear/index.php?trackback/67

Haut de page