Archivage continu Postgresql avec WAL-E

La sauvegarde de sa base de données est une chose essentielle pour la pérennité de son site. Actuellement je fonctionne sur le principe très basique du dump (pg_dump) de toute ma base de données. Mais cette pratique est loin d’être optimum, et il faut effectuer des dumps assez régulièrement s’il l’on ne veut pas perdre trop de données en cas de crash.

Pour la sauvegarde, il existe un autre moyen avec Postgresql, qui consiste dans un premier temps à enregistrer une sauvegarde complète de la base, et ensuite de ne sauvegarder que les changements apportés à la base sous forme de fichiers WAL. Ainsi « terminé » les gros backups entiers de la base de données, et nous pouvons garder une trace de chaque action effectuée sur la base de données et les rejouer à n’importe quel moment (Point-In-Time Recovery).

Faire des sauvegardes c’est bien, mais faut aussi les sauvegarder quelque part ailleurs que sur le serveur. Sinon en cas de problème avec le disque dur, bye bye la BDD et bye bye les sauvegardes. Et pour cela il existe un très bon outil: Amazon S3. Je ne vais pas détailler ici comment utiliser S3, mais c’est vraiment simple.

Du coup, maintenant nous avons un outil pour effectuer un archivage continu de la base de données, et un outil pour le stockage.

Et il n’existerais pas un outil pour simplifier l’utilisation de ces choses ? Et bien si: WAL-E, à ne pas confondre avec Wall-E 😉

Toute la suite de l’installation se fait sur une Ubuntu 12.04.2 LTS avec Postgresql 9.1.

Installation de WAL-E

On commence avec l’installation des dépendances:

Téléchargement de WAL-E. Pour moi c’est la version 0.6:

Et installation:

Installation de Daemontools

Afin d’exécuter WAL-E dans un environnement sécurisé, nous avons besoin de envdir présent dans la suite Deamontools de D.J. Bernstein.

Préparation:

Téléchargement:

Décompression:

Attention, il est nécessaire de modifier un fichier de configuration afin de pouvoir compiler les daemontools sur Ubuntu.

Modifiez le fichier /package/admin/daemontools/src/conf-cc et ajouter « -include /usr/include/errno.h » à la fin de la première ligne (commande gcc):

Nous pouvons maintenant compiler:

Configuration de WAL-E

Dans un premier temps, nous allons préparer les variables d’environnement nécessaires à l’utilisation de WAL-E. Ces variables sont les informations de connexion à votre S3.

Pour retrouver access-key et secret-key-content, allez ici et connectez-vous.

Nous allons maintenant modifier le fichier /etc/postgresql/9.1/main/postgresql.conf avec les informations suivantes:

J’utilise le chemin entier pour appeler wal-e car sinon la commande ne fonctionne pas.

Il faut maintenant recharger Postgresql afin que la nouvelle configuration soit prise en compte:

Et voila la configuration est terminée !

Sauvegarde complète de la base

Maintenant il est nécessaire de faire un premier backup de votre base de données vers S3, et donc d’exécuter votre première commande WAL-E à partir de l’environnement sécurisé grâce à envdir.

Cela peut prendre quelques secondes à quelques dizaines de minutes en fonction de la taille de votre base de données et de la vitesse d’upload entre votre serveur et Amazon.

A la fin de cette commande, vous devriez trouver 2 nouveaux dossiers dans votre bucket S3. Un dossier pour les backups de base et un dossier pour tous les fichiers WAL.

De nouveaux fichiers WAL sont envoyés très régulièrement vers votre s3.

Restauration des données

Pour récupérer sa base après un crash, il suffit de créer un fichier recovery.conf, qui sera lu au démarrage de Postgresql. Se fichier se place dans /var/lib/postgresql/9.1/main/.

Il est possible de définir dans ce fichier une heure de restauration. Ainsi nous pouvons récupérer l’état de la base à moment bien précis. Si cette variable n’est pas définie, WAL-E essaie de restaurer l’état de la base au plus proche du dernier état.

Vérifier que le fichier recovery.conf appartient bien à postgresql. Sinon:

Maintenant il suffit de démarrer Postgresql

Vous pouvez suivre l’évolution du démarrage à partir des logs:

Une fois la restauration terminée, le fichier recovery.conf devient recovery.done afin d’éviter tout problème en cas de redémarrage du serveur.

Et voilà, votre base a été restaurée.

Créer une nouvelle base de données à partir des sauvegardes

Par exemple pour une machine de développement, il peut être nécessaire de recréer une machine avec une base de données.

Dans un premier temps, il faut bêtement installer Postgresql.

Ensuite supprimer toutes les données présentes dans cette base:

On récupère la dernière version de base avec WAL-E:

Une fois cela terminé, il suffit de reprendre toute la partie Restauration des données, juste au dessus, afin de récupérer la dernière version de la base.

Sauvegarde de base quotidienne

Il est peut être bien d’effectuer une sauvegarde totale de la base tous les jours, afin d’accélérer les remises en production en cas de crash. Ajoutez cette ligne à cron:

Sources:

http://jamesreubenknowles.com/centos5-daemontools-143
https://github.com/wal-e/wal-e
https://gist.github.com/ruckus/2293434

Migration MySQL vers Postgresql

J’ai été amené à effectuer un changement de base de données, et après des journées de recherche/modifications/tests, je suis enfin arrivé à mes fins.

Pourquoi changer de base de données?

C’est vrai que je n’avais aucun problème avec MySQL, mais j’ai découvert PostGIS, un plugin qui transforme Postgresql en base de données spatiale. Et ce plugin très puissant va s’avérer très utile dans mon projet. C’est donc la principale raison qui m’a poussée à migrer.

De plus, le projet n’étant pas encore en production, le changement devait se faire maintenant. Mais des données importantes étaient quand même présentes, et il n’était pas question de juste faire une adaptation de schéma.

Préparation du terrain

Afin d’effectuer le transfert proprement, j’ai utilisé deux machines.

La machine avec MySQL 5.1 qui tourne sous Debian 6.0

La nouvelle machine avec Postgresql 9.1 qui tourne sous Ubuntu 12.04 (Afin d’avoir des paquets plus récents qu’avec une Debian)

Installation basique de la nouvelle machine

Création de la base de données

Posgresql utilise un utilisateur nommé postgres afin d’effectuer toutes les taches d’administration. C’est le superutilisateur.

Afin de protéger cet utilisateur, il est nécessaire de rajouter un mot de passe à celui-ci.

Ensuite nous pouvons nous connecter avec cet utilisateur à l’aide de la commande suivante:

Et accéder à la base de données:

Vous voilà connecté à Postgresql en tant que superutilisateur (un # est présent dans la ligne de commande).

Pour commencer, nous allons créer un utilisateur:

Lui ajouter un mot de passe:

Et lui donner les droits de création de base de données:

Maintenant qu’un utilisateur existe, vous pouvez créer votre première base de données:

Afin de pouvoir vous connecter à la base avec votre nouvel utilisateur, il faut modifier le fichier /etc/postgresql/9.1/main/pg_hba.conf qui gère toutes les connexions à Postgresql.

Dans l’état actuel du fichier, vous êtes obligé d’avoir un utilisateur système du même nom que celui que vous venez de créer pour pouvoir vous connecter. Personnellement je préfère bien scinder les choses.

Il faut donc remplacer la valeur peer par md5 au niveau de la ligne local all all :

La première ligne local all postgres peer doit rester intacte, afin de garder une certaine sécurité. Cette ligne veut dire: « Je peux me connecter à Postgresql avec l’utilisateur postgres que si je suis connecté au système avec le compte postgres ».

Maintenant pour se connecter avec votre utilisateur il suffit de faire:

Maintenant que Postgresql est prêt, nous allons pouvoir commencer la migration.

Création d’un dump propre pour Postgresql

Quand je dis création d’un dump, je ferais mieux de dire créations de plusieurs dumps. Car c’est le plus simple pour éviter les erreurs.

Afin de créer ces dumps, j’ai utilisé un outil qui m’a BEAUCOUP aidé: py-mysql2pgsql

Installation de py-mysql2pgsql

Normalement toutes les dépendances s’installent avec easy_install, si vous rencontrez des problèmes, essayez de les installer manuellement.

Dans un premier temps, il faut générer un fichier de configuration avec la commande:

Le fichier mysql2pgsql.yml est maintenant présent dans le répertoire courant.

Ce fichier est extrêmement simple à comprendre. Il y a une partie pour se connecter à MySQL, une autre pour l’export (soit vers un fichier, soit directement vers Postgresql) et une partie de configuration avancée.

Afin de tout contrôler, je n’utiliserai que l’export vers un fichier durant toute la migration.

Migration du schéma

Certainement la partie la plus délicate de la migration, et c’est ici que py-mysql2pgsql est d’une très grande aide, puisqu’il va convertir tous les types de données qui diffèrent entre MySQL et Postgresql.

Je configure le fichier mysql2pgsql.yml afin de ne récupérer que le schéma:

A vous de remplacer les informations propre à votre connexion à MySQL.

Un fichier schema.sql a maintenant été généré, et il est nécessaire de tout vérifier afin d’éviter les incohérences. Personnellement j’ai dû modifier des types integer qui étaient devenus des bigint.

Une fois le schéma validé, vous pouvez l’ajouter à votre base:

Voila une bonne chose de faite, la structure est en place. Maintenant nous pouvons ajouter les données.

Migration des données

Pour cette partie il faut y aller étape par étape, et ça peut être long.

Je vais ici vous expliquer la méthodologie utilisée plutôt que de faire un tuto puisque chaque cas est différent.

Dans un premier temps j’ai exporté les tables « mères » une par une, de la plus importante à la moins importante. Ce que j’appelle ici table « mère », c’est une table dont vont dépendre d’autres tables via des clés étrangères.

Exemple: Une table personne et une table voiture. Une clé étrangère est présente dans la table voiture afin de savoir à qui appartient cette voiture. La table personne est donc une table mère par rapport à la table voiture. Je dois donc ajouter les données de la table personne avant d’ajouter celles de la table voiture afin d’éviter des erreurs de contrainte. (J’espère être clair sur cette partie, je n’ai peut-être pas le bon vocabulaire).

Pour cela, il faut modifier le fichier mysql2pgsql.yml pour chaque table:

Ensuite, vous pouvez ajouter les données de la table directement dans Postgresql:

Si des erreurs apparaissent, vérifiez les données dans votre base MySQL. Il peut exister des incohérences qui troublent l’import. Cela a été le cas pour moi, une dizaine de lignes sur plus d’un million de lignes. Dans ce cas il faut corriger l’erreur et recommencer l’export avec py-mysql2pgsql jusqu’à temps qu’il n’y ait plus d’erreur.

Une fois que toutes les tables « mères » sont importés dans Postgresql, vous pouvez ajouter les tables restantes en une fois, en utilisant la condition exclude_tables dans le fichier mysql2pgsql.yml, pour exclure les tables déjà ajoutées.

Une fois que tout cela est terminé, la migration commence à se terminer.

Après les imports

Et oui, ce n’est pas encore terminé ! Un certain nombre de choses sont maintenant à vérifier, comme la création/modification de certains index, l’ajout des triggers, de fonctions, etc…

De plus il faut modifier un certain nombre de choses dans le code votre projet, car MySQL et Postgresql n’utilisent pas les mêmes fonctions.

Modifications pour CodeIgniter

Les modifications sont ici relativement simples, et doivent être adaptables pour d’autres frameworks.

Dans un premier temps il faut installer l’extension Postgresql pour PHP:

Et enfin, modifier le fichier /application/config/database.php:

Rien de plus n’est nécessaire pour connecter votre projet à Postgresql.

Sauvegarde de la nouvelle base

Maintenant que tout est propre au niveau de la base Postgresql, n’oubliez pas de faire un dump:

 

CodeIgniter avec Nginx

Depuis un moment je voulais essayer Nginx, puisqu’il est apparemment, bien plus performant qu’Apache. Donc je me suis décidé à passer le pas, et je vais expliquer ici ma démarche.

D’abord je vous conseille de lire cet article (en anglais) qui explique les différences importantes entre Apache et Nginx. Ca permet de savoir où on met les pieds.

Pour cette installation je travaille sur Ubuntu 12.04.2 LTS.

Installation

Dans un premier, il faut arrêter tous les services qui pourrait écouter sur le port 80 (http). Dans mon cas, j’ai besoin de stopper Apache

Ensuite on installe les paquets nécessaires.

Configuration de PHP-FPM

Une petite modification au fichier /etc/php5/fpm/php.ini est nécessaire afin d’empêcher un potentiel risque de sécurité.

Et redémarrer le service

Configuration de Nginx

Créer un fichier « nom_de_votre_domaine » dans /etc/nginx/sites-available et y ajouter ceci

Modifier les valeurs de server_name (votre nom de domaine) et de root (le répertoire où se situe votre site CodeIgniter) afin qu’ils correspondent à votre configuration.

Ajouter le lien symbolique afin d’activer le nouveau site

Recharger Nginx

Et voila, vous devez avoir accès à votre site via Nginx !

Source

Bannir les bots phpMyAdmin et w00tw00t avec Fail2ban

Bannir-les-bots-phpMyAdmin-et-w00tw00t-avec-Fail2ban

Très régulièrement dans mes rapports de Logwatch je me retrouve avec des dizaines d’erreurs 404 générés par des bots qui essaient de trouver une version de phpMyAdmin, ou tout simplement une faille sur le serveur web.

Voici un exemple de rapport quotidien:

Comme vous pouvez le voir, un bon nombre de version de phpMyAdmin sont testées. Afin d’alléger mes rapports Logwatch et de bloquer un peu plus ces bots, j’ai mis en place deux nouveaux filtres sur mon Fail2ban.

Si vous ne connaissez pas Fail2ban, je vous invite à le découvrir tout de suite. Un petit outil génial qui permet de bannir (crée une règle iptables) un attaquant lorsqu’il répond à une certaine règle (par exemple quand une personne essaie de se connecter plusieurs fois à votre serveur SSH).

Filtre phpMyAdmin

Création du nouveau filtre /etc/fail2ban/filter.d/apache-phpmyadmin.conf:

Activer la règle dans /etc/fail2ban/filter.d/apache-phpmyadmin.conf

/etc/fail2ban/jail.conf

Si vous avez une version de phpMyAdmin d’installée sur votre serveur, changer la valeur de « maxretry » pour éviter de vous auto-bannir en cas d’erreur d’url.

Filtre w00tw00t

Pareil que pour phpMyAdmin, on crée notre filtre /etc/fail2ban/filter.d/apache-w00tw00t.conf:

On active maintenant notre nouveau filtre anti-w00tw00t dans /etc/fail2ban/filter.d/apache-phpmyadmin.conf

Un petit restart de fail2ban et c’est parti, on est plus (moins) embêté par ces bots.

Tester les filtres

Si vous voulez tester vos nouveaux filtres avant de les mettre en service, vous pouvez utiliser une commande fournit par fail2ban qui est fail2ban-regex:

 

Source: Banning phpMyAdmin bots using fail2ban