Configurer le serveur Postgresql
Version minimale : 9.4
La base de données comprend deux schémas :
- gacl : schéma contenant les tables nécessaires à la gestion des droits, la création des utilisateurs locaux, les traces ;
- col : schéma contenant les données de l’application.
Le nom des schémas est modifiable si nécessaire, mais vous devrez adapter les scripts en conséquence (décrit dans les entêtes des fichiers).
Première installation
Installer en mode manuel
- Créez la base de données, un compte de connexion qui devra avoir les droits de modification des informations dans les tables, pré-positionnez les droits par défaut pour ce compte
- exécutez successivement les deux scripts de création des schémas :
- install/gacl_create-1.1.sql
- install/col_create-1.1.sql
Installer en mode semi-automatique
La base de données va être créée, avec le compte collec qui ne pourra se connecter que localement.
Éditez le fichier /etc/postgresql/version/main/pg_hba.conf, et insérez les lignes suivantes :
host collec collec 127.0.0.1/32 md5 host all collec 0.0.0.0/0 reject
En étant connecté root, à la racine de l’application, lancez les commandes suivantes :
cd install su postgres -c "psql -f init_by_psql.sql"
Mise à jour
Repérez la version précédente de votre base de données :
- variable $APPLI_dbversion au début du fichier param/param.default.inc.php (versions 1.0.8 et ultérieures)
- dans l’application web, ? > A propos
Dans le dossier install, recherchez la présence éventuelle de scripts nommés ainsi :
col_alter_1.0.8-1.1.sql
où 1.0.8 correspond à la version courante, et 1.1 à la version suivante.
Exécutez tous les scripts nécessaires pour réaliser la montée de version.
Sauvegarde de la base de données
Dans le serveur, basculez vers l’utilisateur postgres :
su – postgres
Créez le fichier /var/lib/postgresql/backup/dumpall.sh :
#!/bin/bash
#!This is local_pg_dumpall
#Script de backup sequentiel avec support des gros blobs des bases postgres
PSQL_VERSION=`psql --version | cut -f 3 -d ' ' | cut -d '.' -f 1,2`
DUMPPATH=/var/lib/postgresql/backup
#cree le repertoire de backup s'il n'existe pas
[ -d $DUMPPATH ] && mkdir -p $DUMPPATH
$DUMPPATHLOG=$DUMPPATH/pgbackup.log
echo `date` > $LOG
echo "Sauvegarde des bases de donnees - dumpall" >>$LOG
/usr/bin/pg_dumpall |gzip -c > $DUMPPATH/pg.out.gz 2>>$LOG
# Pour chaque base de données
LIST=$(psql -tl | cut -d '|' -f1 | grep -v ' : ')
echo Liste des bases : $LIST
for DBNAME in $LIST
do
# Ne backup pas les bases "modèles" de postgresql
[ "$DBNAME" = "template0" -o "$DBNAME" = "template1" ] && continue
# Dump la base :
echo -n Dumping "$DBNAME" : >> $LOG
if pg_dump --blobs ${DBNAME} | gzip -9 -c >${DUMPPATH}/${DBNAME}.gz; then
echo "ok" > /dev/null
else
rm -f ${DUMPPATH}/${DBNAME}.tgz
echo "FAILED!" >>$LOG
fi
# nettoyage de la base
vacuumdb -d "$DBNAME" --analyze
done
cp /etc/postgresql/${PSQL_VERSION}/main/pg_hba.conf $DUMPPATH/
Ajoutez une entrée dans la crontab :
crontab -e
puis ajoutez la ligne
0 20 * * * /var/lib/postgresql/backup/local_pg_dumpall
Programmez ensuite une sauvegarde des dump dans votre système de sauvegarde.
Mettre en place une réplication de la base en temps réel
Si vous n’acceptez pas de perdre une information dans la base (échantillon étiqueté dont les informations initiales ne sont plus retrouvables, par exemple), vous devrez répliquer la base de données vers un second serveur en temps réel.
La procédure a été décrite par Alexandra Darrieutort, stagiaire à Irstea en 2016, et complété par Jacques Foury, responsable informatique du centre Irstea de Cestas (33), qui se sont inspirés de divers documents (https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins http://connect.ed-diamond.com/GNU-Linux-Magazine/GLMF-184/Configurer-la-replication-d-un-serveur-PostgreSQL https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial).
Principe
Le mode de réplication correspondant au besoin est maître/esclave. On peut lire et écrire sur le maître et seulement lire sur l’esclave s’il est configuré en hot standby. Ici, le serveur maître est citerne-8 et le serveur esclave est chappie.
Les modifications de données sont enregistrées dans des journaux de transactions appelés WAL (Write-Ahead Log) xlogs. Ces WAL sont transférés à l’esclave qui les rejoue continuellement de sorte à se retrouver dans le même état que le maître. Il sera alors prêt à prendre la relève en cas d’indisponibilité du maître.
Grâce au principe de Streaming replication, on n’attend plus que le fichier WAL (16 Mio) soit rempli mais il sera transmis sans délai du maître à l’esclave.
Limitations et précautions
Dans la configuration, comme on va conserver 256 xlogs à l’aide du paramètre wal_keep_segments, il faut prévoir assez d’espace disque disponible.
La réplication entre deux serveurs de versions différentes de postgresql est impossible.
Installation de postgreSQL sur chappie et mise en place des clés ssh
root@chappie:~# apt-get install postgresql-9.4 root@chappie:~# su - postgres postgres@chappie:~$ mkdir /var/lib/postgresql/.ssh/ postgres@chappie:~$ ssh-keygen
Pour la connexion ssh entre les deux serveurs, il faut mettre la clé de l’utilisateur postgres contenue dans le fichier id_rsa.pub} sur chappie dans le fichier authorized_keys de citerne-8 et inversement.
Mise en place de la réplication
Maître
Création de l’utilisateur posgresql chargé de la réplication :
root@citerne-8:~# su - postgres postgres:~$ psql -c "CREATE USER rep REPLICATION LOGIN ENCRYPTED PASSWORD 'desperados';"
Dans le fichier pg_hba.conf (/etc/postgresql/9.4/main/) ajoutez :
host replication rep 10.33.192.31/32 md5
Pour le paramètre {wal_keep_segments, on lui donne une valeur assez grande pour éviter d’accumuler un retard trop important entre les deux serveurs en cas d’indisponibilité de l’esclave.
Dans le fichier postgresql.conf ajoutez ces lignes :
listen_address = 'localhost,10.33.192.36' wal_level = hot_standby max_wal_senders = 3 max_wal_size = 436MB wal_keep_segments = 256
Redémarrez ensuite le service postgresql.
Esclave
Arrêtez le service postgresql, puis ajoutez ces lignes dans le fichier postgresql.conf :
wal_level = hot_standby max_wal_senders = 3 max_wal_size = 384MB wal_keep_segments = 256 hot_standby = on max_locks_per_transaction = 128
Modifiez le fichier \textbf{pg\_hba.conf} :
host replication rep 10.33.192.36/32 md5
Effectuez la sauvegarde complète des bases du serveur maître (depuis l’esclave, toujours) avec l’utilisateur postgres :
pg_dropcluster 9.5 main pg_basebackup -h 10.33.192.36 -D /var/lib/postgresql/9.5/main -U rep -v -P --xlog
L’option –xlog est ajoutée pour garder les derniers journaux de transactions.
Créez le fichier recovery.conf dans /var/lib/postgresql/9.5/main/ pour configurer la restauration continue.
La restauration en continu s’active à l’aide du paramètre standby_mode. Pour se connecter au maître et récupérer les WAL, on définit les informations nécessaires dans le paramètre primary_conninfo.
Le paramètre trigger_file indique si la restauration doit être interrompue (si le fichier indiqué est présent, le processus est arrêté).
standby_mode = on primary_conninfo = 'host=10.33.192.36 port=5432 user=rep password=desperados' trigger_file = '/var/lib/postgresql/9.4/postgresql.trigger'
Pour finir, démarrez le service postgresql.
Informations de monitoring
Le fichier de logs postgresql-9.4-main.log se trouve dans le répertoire /var/log/postgresql/
Pour savoir où en est la réplication du côté du maître :
sudo -u postgres psql -x -c "select * from pg_stat_replication;"
Pour savoir à quand remonte la dernière synchronisation du côté de l’esclave :
sudo -u postgres psql -x -c "SELECT now() - pg_last_xact_replay_timestamp() AS time_lag;"
Pour voir le numéro du snapshot actuel :
sudo -u postgres psql -x -c "SELECT txid_current_snapshot();"
Pour tester le failover ou gérer une interruption
Le serveur maître est indisponible.
Il faut arrêter la restauration continue sur l’esclave pour qu’il devienne le maître, en créant le fichier rigger. Les bases vont alors passer en mode read/write et le fichier recovery.conf sera renommé recovery.done.
sudo touch /var/lib/postgresql/9.4/postgresql.trigger
Lorsque le maître sera de retour, la réplication ne fonctionnera plus. Vous devrez restaurer les données provenant du serveur esclave dans le serveur maître, puis relancer la réplication, en recréant le fichier recovery.conf, comme décrit dans la section esclave.