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.