Postgresql replication

TableOfContents

Mise en place d'une replication Postgres 9.x en Streaming Replication

• https://wiki.postgresql.org/wiki/Streaming_Replication
• PostgreSQL creer un user appelé "postgres" pour la gestion de ces propre bases.

Configuration du Master

Lorsque nous allons configurer le serveur master toutes les commandes seront a éxécutées avec l'utilisateur "postgres".

# su postgres

* En premier lieu nous allons créer un utilisateur dédié a la réplication (comme dans le cas d'une replication mysql) :

# psql -c "CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'r3pl1c4t10n';"

* Evidemment il faudra changer le password "r3pl1c4t10n" par celui de votre choix.

* Ensuite, nous allons aller dans le dossier de données postgres :

# cd /opt/applis/postgresql/data

* Nous allons modifier le ficher pg_hba.conf (toujours via l'utilisateur postgres) :

# vi pg_hba.conf
 * Ajouter cette ligne (pas tout en haut) dans le fichier en cours d'édition (en adaptant IP_du_Slave/32) et (replicate : nom donné a l'utilisateur pgsql precedemment créé) 
host    replication     replicate     IP_du_Slave/32   md5

* Sauvegarder puis fermer l'édition du fichier pg_hba.conf

* Ensuite nous allons éditer le fichier de configuration principal de postgres :

# vi postgresql.conf

* Trouver l'emplacement de ces parametres dans le fichier postgresql.conf et changer leur valeurs pour correspondre a ce qu'il est indiqué ci-dessous :
listen_addresses = 'localhost,ADDRESSE_DE_CE_SERVEUR' # (ou bien *)
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

* Sauvegarder puis fermer l'édition du fichier 

* Relancer le service (sur le master donc) afin d'appliquer les changements effectués. : service pgsql restart

Configuration du Slave

* Si le service est deja installé et tourne, nous allons l'eteindre : service pgsql stop

* Lorsque nous allons configurer le serveur master toutes les commandes seront a éxécutées avec l'utilisateur "postgres".

# su postgres

* Ensuite, nous allons aller dans le dossier de données postgres :

# cd /opt/applis/postgresql/data

* Nous allons modifier le ficher pg_hba.conf (toujours via l'utilisateur postgres) :

# vi pg_hba.conf

* Ajouter cette ligne (_pas_ tout en haut) dans le fichier en cours d'édition (en adaptant IP_du_Master/32) et (replicate : nom donné a l'utilisateur pgsql precedemment créé) cela premettra au master de se connecter au slave dans le cas d'une bascule du slave vers le role master
host    replication     replicate     IP_du_Master/32  md5

* Sauvegarder puis fermer l'édition du fichier pg_hba.conf

* Ensuite nous allons éditer le fichier de configuration principal de postgres :

# vi postgresql.conf

* Trouver l'emplacement de ces parametres dans le fichier postgresql.conf et changer leur valeurs pour correspondre a ce qu'il est indiqué ci-dessous :
listen_addresses = 'localhost,ADDRESSE_DE_CE_SERVEUR' # (ou bien *)
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

* Sauvegarder puis fermer l'édition du fichier 

Synchronisation des données

* Nous allons maintenant procéder a la synchro des données entre le master et le slave (depuis le master et en utilisateur postgres)
# psql -c "select pg_start_backup('initial_backup');"
# rsync -e ssh -cva --inplace --exclude=*pg_xlog* /opt/applis/postgresql/data root@IP_DU_SLAVE:/opt/applis/postgresql/data
# psql -c "select pg_stop_backup();"

* Une fois cette synchronisation realisée les données du master sont bien présentes sur le slave.

* Nous allons avoir besoin maintenant de configurer le fichier de recovery (recovery.conf) sur le slave (toujours en tant qu'utilisateur postgres) :

# cd /opt/applis/postgresql/data 
# vi recovery.conf 

* Il faudra y mettre les trois lignes ci-dessous (bien penser a changer l'ip du master ainsi que l'utilisateur et le password) :
standby_mode = 'on'
primary_conninfo = 'host=IP_DU_MASTER port=5432 user=replicate password=r3pl1c4t10n'
trigger_file = '/opt/applis/postgresql/data/failover_force'

* Lancer le service (sur le slave cette fois) afin d'appliquer les changements effectués : service pgsql start

* Une fois les services relancés, il faut donc regarder les logs afin de valider que tout se passe bien et qu'il n'y a aucune erreur (sur le master et le slave).
# tail -100f /opt/applis/postgresql/data/serverlog

* Vous devriez voir dans les logs des informations indiquant que le slave se connecte bien au master et inversement que le master voit bien arriver une connexion du slave.

Test de la Replication

* Nous allons voir et tester la replication entre les deux serveur master/slave (attention le slave ne peut jamais ecrire tant qu'il est slave il est en read-only)

* On se connecte au serveur master et on se positionne avec l'utilisateur system postgres, ensuite on tape ce qui suit :

# psql
* Nous allons créer une table :
CREATE TABLE rep_test (test varchar(40));

* Nous allons mettre des choses dedans (cette table) :
INSERT INTO rep_test VALUES ('data test');
INSERT INTO rep_test VALUES ('Agarik');
INSERT INTO rep_test VALUES ('tralala');
INSERT INTO rep_test VALUES ('pouf');
INSERT INTO rep_test VALUES ('blahblah');

* Une fois ces commande passées, nous pouvons quiter via la commande suivante :
\q

* Maintenant nous allons regarder en nous connectant sur le slave pour verifier si la table "rep_test" a bien été répliquée ainsi que son contenu :

* Les actions sont donc a realiser sur le slave en se positionnant avec l'utilisateur system postgres :
# psql
* Nous allons verifier que les données sont bien la en faisant un select :
SELECT * FROM rep_test;
      test       
-----------------
 data test
 Agarik
 tralala
 pouf
 blahblah
(5 rows)

* Si nous avons bien le résultat indiqué c'est que la synchronisation fonctionne correctement.
* Nous allons valider tout de meme que ne nous pouvons pas écrire depuis le slave (qui doit etre en read-only normalement) 
INSERT INTO rep_test VALUES ('test read-only slave');

* Normalement vous devriez avoir le résultat suivant :
ERROR:  cannot execute INSERT in a read-only transaction

* Une fois les vérifications faites, nous allons supprimer la table rep_test a partir du master :
DROP TABLE rep_test ;

Bascule Master/Slave

* Le fichier '''trigger''' dont la présence est surveillée par Postgres permet de forcer une bascule manuelle du slave vers le rôle master (il suffit de faire un touch du fichier, a partir du serveur slave)

* Exemple :
# touch /opt/applis/postgresql/data/failover_force