HowtoPostgreSQL/Slony
Réplication PostgreSQL avec Slony
Documentations : http://slony.info/documentation/2.0/tutorial.html
Limitations
http://slony.info/documentation/2.0/limitations.html
- pas de réplication des champs BLOB
- pas de réplication des modifications de schéma
Prérequis
- Avoir une clé primaire sur chaque table à répliquer (peut être contourné dans certaines conditions, mais fortement déconseillé par les dév. de Slony)
- Ne pas utiliser la commande SQL “TRUNCATE” (avant PG 8.4)
Introduction et principe
Slony (éléphants en russe, en rapport avec le logo de PostgreSQL entre autre) permet de faire de la réplication en mode master-slave. La réplication fonctionne à l’aide de triggers ajoutés au niveau des tables à répliquer. La réplication peut donc se paramétrer individuellement pour chaque table et est asynchrone (lors d’une modification sur le master, le changement est effectué dans un second temps sur le slave). Slony ne rejoue pas la requête entière sur le slave, il se contente de copier les nouvelles valeurs (intéressant dans le cas de requêtes imbriquées).
Installation
# apt install postgresql-9.6 postgresql-9.6-slony1-2 slony1-2-binPréparation PostgreSQL
Soit deux machines, test1 et test2.
- Configurer PostgreSQL pour écouter en réseau (_listen_addresses = ’*’ _) et autoriser les connexions de l’autre machine (fichier pg_hba.conf) :
host dbrepl slony 192.0.2.1/32 md5- Sur chaque machine, avec le compte postgres :
# compte propriétaire de la base de test qui sera répliquée
createuser -D -R -A dbrepl
# compte SUPERUSER
createuser -s slony -P
# base répliquée de test
createdb -O dbrepl dbrepl
# installation du langage PLPGSQL dans la base de test, nécessaire pour Slony -> nécessaire ?
#createlang plpgsql dbrepl
# Création d'une table de test (avec une primary key !)
echo "create table table1 (id serial primary key, t int);" | psql dbreplConfiguration de Slony
Slonik (petit éléphant en russe) est un langage de script permettant de contrôler Slony (les éléphants donc). Il peut soit être édité manuellement, soit être généré à l’aide de scripts Perl fournit avec le paquet Debian.
Première méthode : édition manuelle des scripts Slonik
- Sur test1 :
# On charge le script slonik qui va servir à préparer le namespace slony dans les bases dbrepl de chaque machine
slonik <<EOT
#--
# define the namespace the replication system uses in our example it is
# slony_example
#--
cluster name = slony_namespace;
#--
# admin conninfo's are used by slonik to connect to the nodes one for each
# node on each side of the cluster, the syntax is that of PQconnectdb in
# the C-API
# --
node 1 admin conninfo = 'dbname=dbrepl host=test1 user=slony';
node 2 admin conninfo = 'dbname=dbrepl host=test2 user=slony';
#--
# init the first node. Its id MUST be 1. This creates the schema
# _$CLUSTERNAME containing all replication system specific database
# objects.
#--
init cluster ( id=1, comment = 'Master Node');
#--
# Slony-I organizes tables into sets. The smallest unit a node can
# subscribe is a set. The following commands create one set containing
# all 4 pgbench tables. The master or origin of the set is node 1.
#--
create set (id=1, origin=1, comment='All replicated tables');
set add table (set id=1, origin=1, id=1, fully qualified name = 'public.table1', comment='table1');
#set add table (set id=1, origin=1, id=2, fully qualified name = 'public.branches', comment='branches table');
#set add table (set id=1, origin=1, id=3, fully qualified name = 'public.tellers', comment='tellers table');
#set add table (set id=1, origin=1, id=4, fully qualified name = 'public.history', comment='history table');
#--
# Create the second node (the slave) tell the 2 nodes how to connect to
# each other and how they should listen for events.
#--
store node (id=2, comment = 'Slave node', event node=1);
store path (server # 1, client 2, conninfo='dbname=dbrepl host=test1 user=slony');
store path (server # 2, client 1, conninfo='dbname=dbrepl host=test2 user=slony');- Sur chaque machine, lancer “slon”, le démon qui se charge de transmettre les requêtes de machine en machine :
slon slony_namespace "dbname=dbrepl user=slony host=HOSTNAME"- Puis exécuter le script slonik suivant sur test1, pour déclencher la réplication du replica set défini précédemment :
slonik <<EOT
cluster name = slony_namespace;
node 1 admin conninfo = 'dbname=dbrepl host=test1 user=slony';
node 2 admin conninfo = 'dbname=dbrepl host=test2 user=slony';
# ----
# Node 2 subscribes set 1
# ----
subscribe set ( id # 1, provider 1, receiver # 2, forward no);
EOTSeconde méthode : utilisation des outils Perl (recommandé)
On utilise ici les fichiers de configuration et outils Perl pour générer automatiquement les scripts Slonik. Cela permet également d’utiliser le script d’init fourni par Debian, pour démarrer automatiquement la réplication.
- Sur les 2 machines, copier le fichier d’exemple fourni puis configurer les hosts et sets de la même façon que dans la méthode manuelle :
zcat /usr/share/doc/slony1-2-bin/examples/slon_tools.conf-sample.gz >/etc/slony1/slon_tools.conf# $Id: slon_tools.conf-sample,v 1.8.2.4 2009-08-17 22:21:38 devrim Exp $
# Author: Christopher Browne
# Copyright 2004-2009 Afilias Canada
# Revised extensively by Steve Simms
# Keeping the following three lines for backwards compatibility in
# case this gets incorporated into a 1.0.6 release.
#
# TODO: The scripts should check for an environment variable
# containing the location of a configuration file. That would
# simplify this configuration file and allow Slony-I tools to still work
# in situations where it doesn't exist.
#
if ($ENV{"SLONYNODES"}) {
require $ENV{"SLONYNODES"};
} else {
# The name of the replication cluster. This will be used to
# create a schema named _$CLUSTER_NAME in the database which will
# contain Slony-related data.
$CLUSTER_NAME = 'slony_namespace';
# The directory where Slony should record log messages. This
# directory will need to be writable by the user that invokes
# Slony.
$LOGDIR = '/var/log/slony1';
# SYNC check interval (slon -s option)
# $SYNC_CHECK_INTERVAL = 1000;
# Which node is the default master for all sets?
$MASTERNODE = 1;
# Which debugging level to use? [0-4]
$DEBUGLEVEL = 2;
# Include add_node lines for each node in the cluster. Be sure to
# use host names that will resolve properly on all nodes
# (i.e. only use 'localhost' if all nodes are on the same host).
# Also, note that the user must be a superuser account.
add_node(node => 1,
host => 'test1',
dbname => 'dbrepl',
port => 5432,
user => 'slony',
password => 'PASSWORD');
add_node(node => 2,
host => 'test2',
dbname => 'dbrepl',
port => 5432,
user => 'slony',
password => 'PASSWORD');
}
# The $SLONY_SETS variable contains information about all of the sets
# in your cluster.
$SLONY_SETS = {
# A unique name for the set
"set1" => {
# The set_id, also unique
"set_id" => 1,
"table_id" => 1,
"sequence_id" => 1,
# This array contains a list of tables that already have
# primary keys.
"pkeyedtables" => [
'table1',
],
},
};
# Keeping the following three lines for backwards compatibility in
# case this gets incorporated into a 1.0.6 release.
#
# TODO: The scripts should check for an environment variable
# containing the location of a configuration file. That would
# simplify this configuration file and allow Slony tools to still work
# in situations where it doesn't exist.
#
if ($ENV{"SLONYSET"}) {
require $ENV{"SLONYSET"};
}
# Please do not add or change anything below this point.
1;
Explications : ce fichier de conf permet de décrire la réplication (le cluster et ses machines (nodes), les ensembles de tables (sets) à répliquer, etc…). Les paramètres importants sont : * $MASTERNODE_ : la machine qui sera le master par défaut lors du démarrage de Slony (on peut ensuite changer facilement le « sens » de réplication) ; * _add_node()_ : cette fonction permet d'ajouter une base de données dans le cluster, en indiquant ses paramètres de connexion classique ; * _$SLONY_SETS : ce tableau contient une liste de sets décrivant les tables à répliquer. Pour chaque set, on peut redéfinir quel node est le master avec la clé origin. Autrement, c’est $MASTERNODE qui est utilisé. La clé pkeyedtables contient la liste des tables à répliquer. * On peut maintenant faire appel aux scripts Perl pour mettre en place la réplication. Les scripts Perl ne font que générer le code slonik, il est nécessaire de les piper dans ~~~slonik}} (l’interpréteur) pour qu’il l’exécute. Sur une des deux machines :
# Initialise le cluster Slony
slonik_init_cluster | slonik
# Initialise le set de réplication (ensemble de tables à répliquer), notamment installation des triggers.
slonik_create_set set1 | slonik- Démarrage du démon slon sur les 2 machines : on crée le fichier de conf pour le démon slon et on ajuste le paramètre SLON_TOOLS_START_NODES
# zcat /usr/share/doc/slony1-2-bin/examples/slon.conf-sample.gz >/etc/slony1/slon.conf
# /etc/init.d/slony1 start- Enfin, abonner la machine test2 aux modifications faite sur le set1 :
$ slonik_subscribe_set set1 node2 | slonikAdministration
Passer un node de slave à master
slonik_move_set <set_id> <node_id ancien master> <node_id nouveau master> | slonikNote : ça ne marche pas terrible pour l’instant, si lors d’une écriture sur une table répliquée cette erreur apparait :
ERREUR: Slony-I: Table table1 is currently locked against updates because of MOVE_SET operation in progressIl faut délocker manuellement le set :
(slonik_print_preamble; echo "unlock set(ID=<set_id>, ORIGIN=<node_id du master>);") |slonikAutoriser les écritures sur le slave
Dans le cas où le master est down, et donc que le
slonik_move_set ne marche pas (il a besoin de se connecter
au futur slave pour le passer en slave) :
slonik_failover <node_id du master> <node_id du slave> | slonikModifier de la structure d’une table répliquée
Slony ne répliquant que les données, en cas de modification de la structure d’une table sur un node, il faut la répercuter manuellement sur tous les autres. Un script Perl existe pour le faire sur tout les nodes à la fois :
slonik_execute_script <set_id> script.sql |slonikAjouter une nouvelle table
- Dans le cas où elles ne sont présentes uniquement sur le master :
postgres@master$ pg_dump --schema-only -t table1 -t table2 [...] base >new-tables.sql
postgres@slave$ psql base <new-tables.sql- créer un nouveau set contenant les nouvelles tables dans le slon_tools.conf :
"set2" => {
"set_id" => 2,
"table_id" => 296,
"sequence_id" => 1,
"pkeyedtables" => [
'table1',
'table2',
[...]
],
},table_id et sequence_id indiquent à partir de quel ID Slony peut commencer à numéroter les nouvelles tables/sequences à ajouter. Ils doivents être uniques dans tout le cluster. Pour les déterminer :
SELECT tab_id FROM _replication.sl_table ORDER BY tab_id DESC LIMIT 1; -- +1
select seq_id from _replication.sl_sequence order by seq_id desc limit 1; -- +1puis :
slonik_create_set <nouveau set> | slonik- Abonner le nouveau set au slave :
slonik_subscribe_set <nouveau set> <node slave> |slonik- Un fois que la synchro est terminée (TODO), on peut merger le nouveau set avec l’actuel :
slonik_merge_sets <node master> <set actuel> <nouveau set> |slonik- Modifier la config Slony pour supprimer le nouveau set et mettre les tables ajoutées dans le set actuel.
Pour désinstaller un node/set/toute la conf Slony
- Désactiver un set. Cela va supprimer les trigger positionnés sur toutes les tables du set, sur tous les nodes. Le schéma replication est laissé intact :
$ slonik_drop_set set1 | slonik- Désactiver un node. Cela va supprimer les triggers de tous les sets du node, et supprimer le schéma replication. La commande échoue si des nodes sont encore abonnés au node à supprimer :
$ slonik_drop_node node2 | slonik- Désinstaller Slony. Cela va supprimer entièrement tous les triggers et le schéma Slony sur tous les nodes :
$ slonik_uninstall_nodes set1 node1 | slonikpurger
les tables de réplication sl_log_1 et
sl_log_2
La purge des tables de réplication est un peu floue. Si l’on a besoin de purger les lignes inutiles des tables, on peut faire cela :
Afficher l’état de la réplication :
=# select ev_origin,ev_seqno,"pg_catalog".txid_snapshot_xmin(ev_snapshot) from _replication.sl_event where
(ev_origin, ev_seqno) in (select ev_origin, min(ev_seqno) from _replication.sl_event where ev_type = 'SYNC' group by ev_origin);
ev_origin | ev_seqno | txid_snapshot_xmin
-----------+------------+--------------------
1 | 5000179913 | 13214118
2 | 5000000016 | 163352
(2 lignes)Puis purge en fonction des chiffres :
=# delete from _replication.sl_log_1 where log_origin=1 and log_txid < 5000179913;
DELETE 7020095
=# delete from _replication.sl_log_2 where log_origin=1 and log_txid < 5000179913;
DELETE 7020095
=# vacuum _replication.sl_log_1;
=# vacuum _replication.sl_log_2;À faire sur tout les nodes indépendamment.
purger
complètement les tables de réplication sl_log_1 et
sl_log_2
Cela ne doit être fait que dans des cas particuliers :
=# truncate _replication.sl_log_1;
=# truncate _replication.sl_log_2;Voir http://venublog.com/tag/how-to-purge-slony-log-tables/
En cas de duplicate keys lors de la réplication
Note : théoriquement, cela devrait se produire très rarement.
Dans les logs du slave, si un message de ce type aparrait :
insert into "public"."nom_de_la_table" (...) values (...);
" ERROR: duplicate key value violates unique constraint "..."
- qualification was: where log_origin = 1 and ( (
log_tableid in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295)
and (log_txid < '12782788' and "pg_catalog".txid_visible_in_snapshot(log_txid, '12782168:12782788:12782168,12782782'))
and (log_txid >= '12782762' or log_txid IN (select * from "pg_catalog".txid_snapshot_xip('12782168:12782762:12782168')))
) )Si on s’est assuré après vérification qu’il n’y a pas besoin de
rejouer cette requête, on peut la supprimer manuellement des logs de
réplication :
arrêter les démons slonrechercher l’ID de la table concernée par l’erreur de réplication :select tab_id from _replication.sl_table where tab_relname='nom_de_la_table';sur le master, rechercher la requête dans les logs :select * from _replication.sl_log_{1,2} where log_tableid=id_table+ rajouter les conditions sur log_txid indiquées par les 2 dernières lignes des logs ci-dessus peut aiderrécupérer la valeur du champ log_actionseq, puisdelete from _replication.sl_log_{1,2} where log_actionseq=id_query;relancer les démons slon
Une manière plus radicale de faire, notamment si il y a
plusieurs erreurs sur une même table, peut être de désabonner le slave
du master, vider la table sur le slave, et réabonner le slave.
On peut faire simplement un TRUNCATE table sur le slave
à chaud, sans arrêter les démons slon. Cela va débloquer rapidement la
réplication, et les éventuelles lignes manquantes seront
automatiquement recopiées depuis le master. -> pas sûr, à
tester !
En cas de transaction bloquée
Message d’erreur dans les logs :
transactions earlier than XID 12806955 are still in progress
data copy for set 1 failed 11 times - sleep 60 secondsEn gros Slony est bloqué à cause d’un ExclusiveLock sur la table en question. Voir http://slony.info/documentation/faq.html#AEN4615
Rien de particulier à faire, si ce n’est d’attendre que la requête ayant posé ce verrou se termine.
Ajouter une machine dans un cluster existant
- Ajouter la chaine de connexion PostgreSQL dans les fichier slon_tools.conf de toutes les machines membres du cluster ;
- ajouter les autorisations firewall/PostgreSQL nécessaire depuis/vers toutes les machines du cluster ;
- réinjecter la structure de la base de données à répliquer, à l’exception du schéma Slony ;
- sur la nouvelle machine, exécuter les commandes suivantes :
# slonik_store_node node3 |slonik
# slonik_subscribe_set set1 node3 | slonikLa nouvelle machine se voit attribuer le numéro « node3 » et est abonnée au set1.
set mal supprimé
Si un set est toujours présent dans la table
sl_set :
=# select * from _replication.sl_set;
set_id | set_origin | set_locked | set_comment
--------+------------+------------+-----------------------
1 | 4 | | Set 1 for replication
2 | 4 | | Another setEt que Slony ne le voit plus :
# slonik_drop_set 2
Non-existent set specified.Il faut alors le supprimer à la main :
=# delete from _replication.sl_set where set_id=2;Fonctionnement des tables
sl_log_{1,2}
- Opération d’écriture sur le nœud maître -> les données modifiées
vont être insérées dans la table
sl_log_1, grace au trigger replication_logtrigger positionné sur la table ayant subie les modifications. - Sur le nœud esclave, le démon slon lit à intervalles
réguliers la table de log active, ici
sl_log_1. Il va alors récupérer les nouvelles entrées pour les copier dans sa propre table de log. À noter qu’avant de se connecter à la base PostgreSQL sur le nœud maître, il « pingue » le démon slon sur le master, et si il ne répond pas, ne fait rien. Par conséquent, si un des deux démon slon ne tourne pas, la réplication ne se fait pas. - Les nouvelles modifications sont alors rejouer sur le slave.
Par défaut (option cleanup_deletelogs=false), les entrées
dans les tables de logs ne sont pas supprimées, étant donné qu’un
DELETE à chaque fois peut être assez coûteux en terme de
ressources pour la base de données. Un basculement de tables est alors
opéré (par défaut toutes les 10 minutes, modifiable par l’option
cleanup_interval), de sl_log_1 vers
sl_log_2 ou inversement, ce qui permet de repartir sur une
table vide. Un TRUNCATE est alors exécuté sur l’ancienne table
de log pour la nettoyer. Tous les 3 cycles de basculement, un
VACUUM est exécuté sur la table de log (définit par la variable
vac_frequency).
Pour connaître la table de log active :
Monitoring
Des informations sur la réplication en temps réel sont disponibles
dans les tables sl_* du schéma CLUSTERNAME (par
défaut slony_namespace. Le détail de ces tables est
disponible ici : http://slony.info/documentation/monitoring.html
Un script fournit avec Slony, _check_slony_cluster.sh_peut être utilisé comme check Nagios pour vérifier que la réplication est ok. Pour qu’il puisse être utilisé en tant que postgres sans demander de mot de passe, il est nécessaire de l’adapter un peu :
--- /usr/share/doc/slony1-2-bin/examples/check_slony_cluster.sh.orig 2012-03-27 15:57:45.000000000 +0200
+++ /usr/share/doc/slony1-2-bin/examples/check_slony_cluster.sh 2012-03-27 15:57:47.000000000 +0200
@@ -11,7 +11,6 @@
# script requires two parameters:
# CLUSTERNAME - name of slon cluster to be checked
# DBNAME - name of master database
-# DBHOST - host name of master database
#
# It also depends on PGPORT being set to the appropriate port
#
@@ -22,14 +21,13 @@
# check parameters are valid
if [ $# -ne 3 ]
then
- echo "Invalid parameters need CLUSTERNAME DBNAME DBHOST"
+ echo "Invalid parameters need CLUSTERNAME DBNAME"
exit 2
fi
# assign parameters
CLUSTERNAME=$1
DBNAME=$2
-DBHOST=$3
# setup the query to check the replication status
SQL="select case
@@ -56,7 +54,7 @@
) as t2"
# query the master database
-CHECK=`psql -c "$SQL" --tuples-only -U postgres -h $DBHOST $DBNAME`
+CHECK=`psql -c "$SQL" --tuples-only $DBNAME`
if [ ! -n "$CHECK" ]
then$ /usr/share/doc/slony1-2-bin/examples/check_slony_cluster.sh slony_namespace dbrepl2 localhost