# Réplication physique avancée > Superviser les secondaires, gérer les conflits, la synchronisation, les slots et le log shipping. --- Index LLMS : [llms.txt](/fr/llms.txt) --- ## Réplication physique avancée {#dba3-ch7} Le contenu comprend la supervision, les conflits, la réplication synchrone, les slots, le log shipping et le TP avec solution. **Figure 7-1.** Figure 7\-1 \- Diapositive d\'ouverture du chapitre 7\. ![Diapositive d\'ouverture du chapitre 7\.](/images/dba3/manual/dba3-p237-01.png) ## 7/ Réplication physique avancée ### 7.1 Introduction - Supervision - Fonctionnalités avancées En complément du module précédent, il est important de bien superviser un cluster en réplication, d’en connaître les limites mais aussi d’appréhender toutes les possibilités offertes par la réplication physique. #### 7.1.1 Au menu - Supervision - Gestion des conflits - Asynchrone ou synchrone - Réplication en cascade - Slot de réplication - Log shipping ### 7.2 Supervision (streaming) - Quelles vues et fonctions utilitaires? - Comment voir et calculer le retard des secondaires? #### 7.2.1 Utilitaires pour le streaming - pg_is_in_recovery() : instance en réplication? - Calcul du retard en octets : -- primaire ```sql SELECT pg_wal_lsn_diff ( pg_current_wal_lsn(), '0/73D3C1F0' ); ``` - et en temps -- secondaire ```sql SELECT now() - pg_last_xact_replay_timestamp() ; -- si activité ``` Étant donné qu’il est possible de se connecter sur le serveur primaire comme sur le serveur secondaire, il est important de pouvoir savoir sur quel type de serveur un utilisateur est connecté. Pour cela, il existe une fonction appelée pg_is_in_recovery() : elle renvoie la valeur true si l’utilisateur se trouve sur un serveur en hot standby et false sinon. Retard en octets : Le calcul de la différence de volumétrie de données entre le primaire et ses secondaires (lag) peut-être effectué avec la fonction ```text pg_wal_lsn_diff() . La fonction pg_current_wal_lsn() ``` fournit la position dans le flux de données du primaire. En récupérant la position sur le secondaire (au choix, dernière réception avec ou dernier rejeu avec ```text pg_last_wal_receive_lsn() pg_last_wal_replay_lsn() ), le calcul du lag en octet devient : ``` -- sur le secondaire ```sql SELECT pg_last_wal_replay_lsn () ; ``` ```text pg_last_wal_replay_lsn ------------------------ ``` 13/A7DD670 -- sur le primaire ```sql SELECT pg_size_pretty ( pg_wal_lsn_diff( pg_current_wal_lsn(), '13/A7DD670') ``` ) ; ```text pg_size_pretty ---------------- ``` 1939 kB Mais nous allons voir qu’il y a plus pratique. Retard en durée : Quand le retard d’un serveur secondaire sur son primaire est exprimé en octets, il n’est pas simple d’en appréhender l’amplitude. Le retard en durée est plus parlant. La fonction pg_last_xact_replay_timestamp() indique la date et l’heure de la dernière transaction rejouée. Soustraire la date et l’heure actuelle à cette fonction permet d’avoir une estimation sur le retard au rejeu d’un serveur secondaire sous la forme d’une durée. ```sql SELECT now() - pg_last_xact_replay_timestamp() ; -- si activité ``` Attention, si le primaire ne reçoit que des transactions en lecture, le flux de journaux n’est pas forcément complètement vide, mais pg_last_xact_replay_timestamp() ne s’incrémente alors pas sur le secondaire! now() - pg_last_xact_replay_timestamp() donnera alors une durée croissante dans le temps, même si le serveur secondaire n’a aucun retard. #### 7.2.2 Pg_stat_replication ```sql SELECT … FROM pg_stat_replication; -[ RECORD 1 ]----+-----------------------------pid | 286511 usesysid | 10 usename | postgres application_name | secondaire2 client_addr| 192.168.0.55 ``` client_hostname ```text | client_port| ``` backend_start ```text | 2023-12-19 10:41:47.431471+01 ``` backend_xmin ```text | state | streaming sent_lsn | 14/C402A000 write_lsn | 14/C402A000 flush_lsn | 14/C402A000 replay_lsn | 14/C311D460 write_lag | 00:00:00.032183 flush_lag | 00:00:00.032601 replay_lag | 00:00:02.984354 ``` sync_priority ```text | 1 sync_state | sync reply_time | 2023-12-19 11:05:37.903584+01 ``` Pour connaître l’état des différents serveurs secondaires connectés au serveur primaire, le plus simple est de récupérer les informations provenant de la vue pg_stat_replication du primaire. Elle permet de connaître l’état de tous les serveurs secondaires connectés en streaming (mais pas ceux déconnectés!). Il y a une ligne pour chacun d’entre eux, l’exemple ci-dessus porte donc sur un seul secondaire. La plupart des colonnes se comprennent aisément. L’adresse IP du serveur est l’information principale pour distinguer les secondaires s’il y en a plusieurs. ```text application_name peut être fourni par le secondaire dans sa chaîne de connexion primary_conninfo . ``` Il est conseillé de le renseigner pour la supervision, (ou bien cluster_name ). stateest à streaming quand tout va bien. Quand un secondaire vient de se connecter, state affiche catchup le temps de revenir au moins une fois à un lag nul. sync_state vaut async dans le cas d’une réplication asynchrone. Avec une réplication synchrone, ce sera sync , potential ou quorum . Si la connection a échoué, la ligne n’existe simplement pas. backend_start indique l’heure de connexion, et reply_time l’heure du dernier message envoyé par le secondaire. Les quatre LSN permettent de suivre la réception, l’enregistrement et le rejeu sur le secondaire, grâce aux fonctions évoquées plus haut : ```sql SELECT application_name, pg_size_pretty(pg_wal_lsn_diff( pg_current_wal_lsn(), replay_lsn )) AS retard_rejeu ``` FROM pg_stat_replication ; ```text application_name | retard_rejeu ------------------+---------------secondaire3| 15 MB secondaire2| 0 bytes ``` Le service streaming_delta de la sonde check_pgactivity 1 ne fait pas autrement pour suivre les volumétries à recevoir, à appliquer et à rejouer. Les différents champs *_lag indiquent le retard temporel des secondaires, ce qui est très pratique pour repérer un secondaire en retard ou en pause. mesure le délai entre l’enregistrement dans les journaux en local et la notification de write_lag l’enregistrement dans le cache disque du secondaire (ce délai est important en mode synchrone avec ```text synchronous_commit à remote_write ) mais sans attendre l’écriture physique ( sync ). ``` flush_lag mesure le délai jusqu’à confirmation que les données modifiées soient bien écrites sur disque au niveau du serveur standby (ce délai est celui à suivre en mode synchrone avec ```text synchronous_commit à on ). ``` mesure le délai jusqu’au rejeu des transactions sur le secondaire, les rendant replay_lag est à visibles aux requêtes des utilisateurs (ce délai est à surveiller si ```text synchronous_commit ``` remote_apply ) La sortie d’écran plus haut indique que la réception des données sur le secondaire est rapide, mais le rejeu a 3 secondes de retard. - Source 1 : https://github.com/OPMDG/check_pgactivity #### 7.2.3 Autres vues pour le streaming - S’il y a un slot - pg_replication_slots - Sur le secondaire - pg_stat_wal_receiver pg_replication_slots : Toujours depuis le primaire, pour savoir où en sont les serveurs secondaires, éventuellement déconnectés, utilisant un slot de réplication, consulter aussi la vue pg_replication_slots : ```sql SELECT * FROM pg_replication_slots \gx ``` ```text -[ RECORD 1 ]-------+-------------slot_name | secondaire1 plugin | slot_type | physical datoid | database | temporary | f active | f active_pid | xmin | catalog_xmin | restart_lsn | 0/A5F8310 confirmed_flush_lsn | safe_wal_size| 5374099280 two_phase | f conflicting | -[ RECORD 2 ]-------+-------------slot_name | secondaire2 plugin | slot_type | physical datoid | database | temporary | f active | t active_pid | 29287 xmin | catalog_xmin | restart_lsn | 0/AEC3B40 confirmed_flush_lsn | safe_wal_size| 5374099280 two_phase | f conflicting | ``` pg_stat_wal_receiver : Sur le secondaire, on peut consulter aussi la vue pg_stat_wal_receiver pour voir la connexion en cours : ```sql SELECT * FROM pg_stat_wal_receiver \gx ``` ```text -[ RECORD 1 ]---------+---------------------------------------------------------- pid | 696088 status | streaming receive_start_lsn | 14/AC000000 receive_start_tli | 1 written_lsn | 15/78CB1F8 flushed_lsn | 15/78CB1F8 received_tli | 1 last_msg_send_time | 2023-12-19 12:05:45.275257+01 last_msg_receipt_time | 2023-12-19 12:05:45.275532+01 latest_end_lsn | 15/78CB1F8 latest_end_time | 2023-12-19 12:05:45.273271+01 slot_name | secondaire3 sender_host | /var/run/postgresql sender_port | 5432 conninfo | user=postgres passfile=/var/lib/postgresql/.pgpass ↪ channel_binding=prefer dbname=replication host=/var/run/postgresql port=5432 ↪ application_name=secondaire3 fallback_application_name=16/secondaire3 ↪ sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ↪ ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres ↪ gssdelegation=0 target_session_attrs=any load_balance_hosts=disable ``` Noter que le primary_conninfo d’origine est complété de nombreux paramètres par défaut. ### 7.3 Supervision (log shipping) ### 7.4 Supervision du log shipping - Le primaire ne sait rien - Supervision de l’archivage comme pour du PITR - pg_stat_archiver - Secondaire : - pg_wal_lsn_diff() - traces - calcul du retard manuel ( pg_last_wal_replay_lsn() ) Si le secondaire est en log shipping (par choix ou parce que le secondaire a trop de retard et a basculé dans ce mode), la supervision est plus compliquée. Le primaire étant déconnecté du secondaire, pg_stat_replication ne contiendra rien sur ce secondaire. Côté primaire, on vérifiera que l’archivage se fait correctement, notamment avec la vue pg_stat_archiver . Côté secondaire, les traces permettent de vérifier que les journaux sont récupérés et appliqués, ou de connaître la cause des erreurs : restore_command mal paramétrée, problème d’accès aux journaux, etc. Le calcul du retard ci-dessus reste possible, mais il faudra aller chercher où est le secondaire dans le flux des WAL en y exécutant la fonction pg_last_wal_replay_lsn() . Le service hot_standby_delta de la sonde check_pgactivity 2 facilite cela : elle se connecte au primaire et au secondaire, et calcule l’écart, pour lever une alerte au besoin. Il peut être utile de la déployer même sur une instance habituellement en streaming pour suivre un rattrapage long. - Source 2 : https://github.com/OPMDG/check_pgactivity **Figure 7-2.** Figure 7\-2 \- Conflits de réplication sur un serveur secondaire\. ![Conflits de réplication sur un serveur secondaire\.](/images/dba3/manual/dba3-p246-01.png) ### 7.5 Conflits de réplication #### 7.5.1 Qu’est-ce qu’un conflit de réplication? #### 7.5.2 Détection des conflits de réplication - Une requête en lecture pose des verrous - conflit possible avec changements répliqués! - Vue pg_stat_database_conflicts (secondaires) - Traces : - log_recovery_conflict_waits Source des conflits : Sur un primaire, le MVCC garantit qu’une requête ne sera pas gênée si elle lit des lignes dans des blocs qu’une autre requête est en train de modifier. Mais le primaire ne sait à priori rien des requêtes sur un secondaire. Sur ce dernier, un conflit peut survenir entre l’application des modifications provenant du primaire d’une part, et l’exécution d’une requête (en lecture seule) d’autre part. Comme les modifications de la réplication doivent s’enregistrer dans l’ordre de leur émission, si une requête bloque l’application d’une modification, elle bloque en fait l’application de toutes les modifications suivantes pour ce serveur secondaire. Un exemple simple de conflit est l’exécution d’une requête sur une base que la réplication veut supprimer. PostgreSQL attend un peu avant de forcer l’application des modifications. S’il doit forcer, il sera contraint d’annuler les requêtes en cours, voire de déconnecter les utilisateurs. Évidemment, cela ne concerne que les requêtes et/ou les utilisateurs gênants. Suivi des conflits : La table pg_stat_database_conflicts du catalogue système n’est renseignée que sur les serveurs secondaires d’une réplication. Elle contient le nombre de conflits détectés sur ce secondaire par type de conflit (conflit sur un tablespace, conflit sur un verrou, etc.). Elle contient une ligne par base de données : ```sql SELECT * FROM pg_stat_database_conflicts ``` WHERE datname='postgres' ; ```text -[ RECORD 1 ]----+--------datid | 12857 datname | postgres confl_tablespace | 0 confl_lock | 0 ``` confl_snapshot ```text | 3 ``` confl_bufferpin ```text | 2 ``` confl_deadlock ```text | 0 ``` Le total se retrouve dans pg_stat_database . Le paramètre log_recovery_conflict_waits est à activer : il permet de tracer toute attente due à un conflit de réplication. Il n’est donc valable et pris en compte que sur un serveur secondaire. #### 7.5.3 Prévenir les conflits de réplication - ```text wal_standby_streaming_delay hot_standby_feedback à on + wal_receiver_status_interval (10s) ``` - gênent le vacuum! Gestion fine des délais pour réduire les conflits : Avant d’appliquer un journal, ou extrait de journal, qui entre en conflit avec des requêtes en cours sur le secondaire, PostgreSQL attend un certain délai. et max_standby_archive_delay max_standby_streaming_delay sont des délais d’attente provenant respectivement du log shipping ou du streaming, Par défaut, c’est 30 secondes. Monter l’un ou l’autre de ces paramètres peut être suffisant si l’on peut tolérer que la réplication soit brièvement bloquée. Avant la version 16, il existait un paramètre vacuum_defer_cleanup_age qui demande au VACUUM d’attendre un certain nombre de transactions avant de recycler des lignes mortes. Ce paramètre a été supprimé car le calcul était délicat, et il générait de la fragmentation. Il était désactivé par défaut. ```text hot_standby_feedback : ``` De manière plus fine (et plus simple), les serveurs secondaires peuvent envoyer des informations au serveur primaire sur les requêtes en cours d’exécution, pour tenter de prévenir au moins les conflits lors du nettoyage des enregistrements (action effectuée par le VACUUM ). Il faut pour cela activer le paramètre hot_standby_feedback (à off par défaut). Le serveur secondaire envoie alors des informations au serveur primaire à une certaine fréquence, configurée par le paramètre ```text wal_receiver_status_interval , soit 10 secondes par défaut. ``` Ces paramètres doivent être maniés avec précaution, car ils peuvent causer une fragmentation des tables sur le serveur primaire, certes pas forcément plus importante que si les requêtes sur le secondaire avaient été lancées sur le primaire. Attention notamment s’il y a un slot de réplication et ```text hot_standby_feedback à on (voir plus bas)! ``` Gestion des déconnexions du secondaire Grâce à cet envoi d’informations, PostgreSQL peut savoir si un serveur secondaire est indisponible, par exemple suite à une coupure réseau ou à un arrêt brutal du serveur secondaire. Rappelons que si jamais le serveur secondaire est indisponible, le primaire coupe la connexion avec le secondaire après un temps déterminé par le paramètre wal_sender_timeout (1 minute par défaut), Pour éviter des coupures intempestives, il faut donc conserver wal_receiver_status_interval à une valeur inférieure à celle de wal_sender_timeout . ### 7.6 Contrôle de la réplication - ```text pg_wal_replay_pause() : mettre en pause le rejeu pg_wal_replay_resume() : reprendre ``` - pg_is_wal_replay_paused() : statut - Utilité : - requêtes longues - pg_dump depuis un secondaire Lancer un pg_dump depuis un serveur secondaire est souvent utile pour ne pas charger le primaire, mais ce n’est pas simple à cause des risques d’annulation de requêtes en cas de conflits. L’exécution d’un pg_dump peut durer très longtemps et ce dernier travaille en exécutant des requêtes, parfois très longues (notamment COPY ) et donc facilement annulées même après configuration des paramètres max_standby_*_delay . Il faut donc pouvoir mettre en pause l’application de la réplication avec les fonctions suivantes : - pg_wal_replay_pause() , pour mettre en pause la réplication sur le serveur secondaire où est exécutée cette commande; - pg_wal_replay_resume() , pour relancer la réplication sur un serveur secondaire où la réplication avait été précédemment mise en pause; - pg_is_wal_replay_paused() , pour savoir si la réplication est en pause sur le serveur secondaire où est exécutée cette commande. Ces fonctions s’exécutent uniquement sur les serveurs secondaires et la réplication n’est en pause que sur le serveur secondaire où la fonction est exécutée. Il est donc possible de laisser la réplication en exécution sur certains secondaires et de la mettre en pause sur d’autres. Plus généralement, cette technique est applicable pour figer des secondaires et y effectuer de très longues requêtes qui n’ont pas besoin des données les plus récentes enregistrées sur le primaire. Noter qu’il s’agit bien de figer le rejeu des journaux, pas leur transmission. Le serveur secondaire ainsi figé stocke les journaux et pourra les réappliquer plus tard. Même une réplication synchrone, dans sa version la moins stricte, reste ainsi possible. ### 7.7 Réplication synchrone - Comment configurer? - Comment limiter l’impact sur les performances? #### 7.7.1 Principe de la réplication synchrone En synchrone, lors d’un COMMIT : - le primaire attend l’enregistrement sur le secondaire : - latence réseau - temps de rejeu des journaux - synchronisation sur le secondaire - le secondaire est à jour en cas de bascule - permet des secondaires qui renvoient le même résultat - mais ralentit les écritures! La réplication synchrone est fréquemment demandée sur tous les moteurs de bases de données. En réplication asynchrone, quand une transaction est validée, le serveur primaire rend la main à l’utilisateur lorsqu’il a fini d’enregistrer les données dans ses journaux de transactions sur disque. Il n’attend donc pas de savoir si le serveur secondaire a reçu les données, et encore moins si elles sont enregistrées sur son disque. Le problème survient quand le serveur primaire s’interrompt soudainement et qu’il faut basculer le serveur secondaire en serveur primaire. Les dernières données enregistrées sur le serveur primaire n’ont peut-être pas eu le temps d’arriver sur le serveur secondaire. Par conséquent, on peut se trouver dans une situation où le serveur indique une transaction comme enregistrée, alors qu’après le failover elle n’est plus visible. Avec une réplication synchrone, le serveur primaire ne valide la transaction auprès de l’utilisateur qu’à partir du moment où le serveur secondaire synchrone a lui aussi reçu/écrit/rejoué la donnée sur disque (selon le mode). Le premier intérêt de la réplication synchrone est donc de s’assurer qu’en cas de failover, aucune donnée ne soit perdue. Le second intérêt peut être d’avoir des serveurs secondaires renvoyant exactement la même chose au même moment que le primaire. Cependant, le coût peut être prohibitif. L’immense inconvénient de la réplication synchrone est la latence supplémentaire due aux échanges entre les serveurs pour chaque transaction. En effet, il ne faut pas seulement attendre que le serveur primaire fasse l’écriture, il faut aussi attendre l’écriture sur le serveur secondaire, sans parler des interactions et des latences réseau. Même si le coût semble minime, il reste cependant présent, et dépend aussi de la qualité du réseau : la durée d’un aller-retour réseau est souvent du même ordre de grandeur (milliseconde) que bien des petites transactions, voire plus élevée. Pour des serveurs réalisant beaucoup d’écritures, le coût n’en sera que plus grand. Le mode peut se choisir transaction par transaction, ce qui peut mitiger le problème. Mais la réplication d’une transaction synchrone doit attendre la réception, voire le rejeu, de toutes les transactions précédentes. Une grosse opération asynchrone peut donc ralentir la transmission ou le rejeu de transactions synchrones. Enfin, la réplication synchrone a un autre danger : si le serveur synchrone ne répond pas, la transaction ne sera pas validée sur le primaire. Du point de vue de l’application, un COMMIT ne rendra pas la main. PostgreSQL permet de déclarer plusieurs serveurs synchrones pour réduire le risque. Ce sera donc du cas par cas. Pour certains, la réplication synchrone sera obligatoire (due à un cahier des charges interdisant aucune perte de données en cas de failover). Pour d’autres, malgré l’intérêt de la réplication synchrone, la pénalité à payer sera intolérable. Nous allons voir les différentes options pour limiter les inconvénients. #### 7.7.2 Secondaires synchrones - Par défaut : réplication physique asynchrone - Secondaires synchrones : ```bash # s1 synchrone, s2 en dépannage synchronous_standby_names = 'FIRST 1 (s1, s2)' # 2 synchrones au moins synchronous_standby_names = 'ANY 2 (s1,s2,s3)' # n'importe quel secondaire synchronous_standby_names = '*' ``` - Plusieurs synchrones simultanés possibles - ou un quorum Par défaut, la réplication fonctionne en asynchrone. La mise en place d’un mode synchrone est très simple. Sur le(s) secondaire(s) synchrone(s) : Il n’y a rien à configurer de plus. Par contre il est crucial de sécuriser la disponibilité de l’instance. Sur le primaire : Comme le paramètre synchronous_commit est déjà à on par défaut, il ne reste qu’à déclarer les serveurs secondaires synchrones avec le paramètre synchronous_standby_names , en séparant par des virgules les différentes instances secondaires synchrones. Il est possible d’indiquer le nombre de serveurs synchrones simultanés. Les serveurs surnuméraires sont des synchrones potentiels. Pour que s1 soit un secondaire synchrone, et que s2 et s3 le deviennent si s1 ne répond pas, on a plusieurs syntaxes au choix : ```text synchronous_standby_names = '1 (s1,s2,s3)' synchronous_standby_names = 'FIRST 1 (s1, s2, s3)' ``` -- syntaxe à ne plus utiliser ```text synchronous_standby_names = 's1,s2,s3' ``` Dans l’exemple suivant, s1ets2 seront tous les deux synchrones, s3 ne le sera pas, sauf défaillance d’un des premiers. ```text synchronous_standby_names = '2 (s1,s2,s3)' synchronous_standby_names = 'FIRST 2 (s1, s2, s3)' ``` * remplace la liste des secondaires : ```text # un secondaire désigné synchrone dans la liste, les autres en secours synchronous_standby_names = '1 (*)' synchronous_standby_names = 'FIRST 1(*)' synchronous_standby_names = '*' ``` Il est possible de se baser sur un quorum. Par exemple, pour que la transaction synchrone s’achève dès que 2 serveurs sur les 3 indiqués l’ont enregistrée, et quels qu’il soient, on écrira : ```text synchronous_standby_names = 'ANY 2 (s1,s2,s3)' ``` Si l’on ne veut pas spécifier les secondaires manuellement, cette syntaxe est très pratique : ```text synchronous_standby_names = 'ANY 2 (*)' ``` Il est parfois nécessaire d’utiliser des guillemets droits : ```text synchronous_standby_names = 'ANY 2 (sec1,"sec-2","sec 3")' ``` La comparaison entre l’ application_name des connexions de réplication et la liste de serveurs spécifiée dans synchronous_standby_names n’est pas sensible à la casse, que l’on utilise des guillemets droits ou non. Il n’y a pas de validation des noms. En cas de faute de frappe, PostgreSQL cherchera donc à être synchrone avec un serveur non connecté, ce qui va bloquer les transactions. S’il existe des serveurs secondaires non listés dans synchronous_standby_names , ils seront implicitement répliqués de manière asynchrone, donc sans impact sur les performances. Mais comment indiquer le nom d’un serveur secondaire? Ce nom dépend d’un paramètre de connexion appelé application_name , que le client définit librement. Il doit apparaître dans la chaîne de connexion du serveur secondaire au serveur primaire, c’est-à-dire primary_conninfo , et différer pour chaque secondaire. Par exemple : ```ini primary_conninfo = 'user=user_repli host=prod application_name=s2' ``` Sur le primaire, le nom apparaîtra dans la vue pg_stat_replication , champ application_name . Ce nom est indépendant de l’éventuel slot de réplication ( primary_slot_name ), même s’ils sont souvent identiques. #### 7.7.3 Niveau de synchronicité & performances - Niveau de synchronicité : SET synchronous_commit = off / local / remote_write / on / remote_apply - Ajustable par base/utilisateur/session/transaction - Risque de blocage du primaire à cause des secondaires! Pour définir le mode de fonctionnement exact, synchronous_commit peut prendre plusieurs valeurs. En ordre croissant de sécurité, ce sont les suivantes : off : La transaction est directement validée dans le cache du serveur primaire, mais elle sera être écrite plus tard dans les journaux et sur le disque. Évidement, les secondaires ne sont pas synchrones non plus. Ce paramétrage peut causer la perte des transactions non encore écrites dans les journaux si le serveur se crashe. La durée d’activité potentiellement perdue est d’au maximum 3 fois la valeur de wal_writer_delay (soit au total 0,6 s par défaut). Par contre, il n’y a pas de risque de corruption. Même sans réplication, synchronous_commit = off offre de gros gains de performance dans le cas de nombreuses petites transactions, C’est à savoir pour tous les cas où la perte des dernières transactions validées ne porte pas à conséquence grave (au milieu d’un batch que l’on relancera par exemple). On réduit en effet l’impact en performance de l’opération de synchronisation sur disque des journaux, sans risque de corruption de données. ```text local : ``` On force le mode asynchrone. La transaction est validée lorsque les données ont été écrites et synchronisées sur le disque de l’instance primaire. En revanche, l’instance primaire ne s’assure pas que le secondaire a reçu la transaction. S’il n’y a pas de secondaire synchrone, on et local sont équivalents. Si le primaire disparaît, il peut y avoir perte de transactions validées et non reçues par un secondaire. remote_write : Le primaire synchronise ses journaux, bien sûr, et attend que les journaux soient écrits sur le disque du secondaire via le système d’exploitation, mais sans avoir demandé le vidage du cache système sur disque ( fsync ). Les informations sont donc écrites sur le disque du primaire, mais uniquement dans la mémoire système du secondaire. Il est donc possible de perdre des données si l’instance secondaire crashe en même temps que le primaire. remote_write impacte beaucoup moins les performances que la valeur on , et la fenêtre de perte de données est bien moins importante que le mode asynchrone, mais toujours présente. L’instance primaire ne s’assure pas non plus que le secondaire a rejoué la transaction. Le rejeu des journaux peut effectivement durer un certain temps. Deux requêtes exécutées au même moment sur le primaire et un secondaire peuvent renvoyer des résultats différents. Ce peut être important dans certains cas. Le délai que remote_write impose se mesure dans pg_stat_replication , champ write_lag . on (défaut) : Sans réplication synchrone, il s’agit du fonctionnement normal, où les journaux de transaction sont synchronisés sur disque ( fsync ) avant que la transaction soit considérée comme validée. Avec des secondaires synchrones, PostgreSQL attend que l’enregistrement associé au COMMIT soit écrit durablement dans les journaux de transactions des instances primaire et secondaire(s). L’impact en performances est donc assez lourd. Il n’y a donc pas de perte de données en cas de crash (sauf pertes des disques des deux, ou plus, ou des machines). La sécurité étant assurée par l’enregistrement des journaux, le primaire n’attend pas que le secondaire ait réellement rejoué les données pour rendre la main à son client. Le secondaire peut accuser un certain retard (voire avoir mis le rejeu de pause). Là encore, deux requêtes exécutées au même moment sur le primaire et un secondaire peuvent renvoyer des résultats différents. Le délai que synchronous_commit à on impose se mesure dans pg_stat_replication , champ flush_lag . remote_apply : C’est le mode de synchronisation le plus poussé. Non seulement les modifications doivent être enregistrées dans les journaux du secondaire, et synchronisées sur son disque, mais le secondaire doit les avoir rejouées pour que PostgreSQL confirme la validation de la transaction au client. Cette méthode est la seule garantissant qu’une transaction validée sur le serveur primaire sera visible sur le secondaire. Évidemment, elle rajoute encore une latence supplémentaire. remote_apply n’est pas une garantie absolue que les serveurs primaire et secondaires renverront tous la même information au même moment : si un secondaire ne répond pas ou a du retard, la session sera bloquée sur le primaire, et son résultat n’y sera pas encore visible ; mais les secondaires qui fonctionnent bien auront déjà rejoué les données modifiées et les afficheront! Il n’y a aucune synchronisation entre différents secondaires, et un secondaire ne peut pas savoir que le primaire attend un autre secondaire avant de valider la transaction. Ce problème est rare car une réplication synchrone est à éviter sur une liaison instable. Le délai que remote_apply entraîne se mesure dans pg_stat_replication , champ replay_lag . Tableau récapitulatif : **Tableau 7-1.** Tableau 7\-1 \- Durée de retour du COMMIT et facteur principal selon synchronous\_commit\. | Valeur | Durée | Facteur contraignant (primaire) | Facteur contraignant (secondaire) | | --- | --- | --- | --- | | `off` | 0 | Aucun | Aucun | | `local` | Selon disque | Écriture dans `pg_wal` | Aucun | | `remote_write` | `write_lag` | (idem) | Écriture dans la RAM du secondaire | | `on` | `flush_lag` | (idem) | Écriture dans `pg_wal` du secondaire | | `remote_apply` | `write_lag` | (idem) | Rejeu des données en RAM du secondaire | Les valeurs en *_lag sont des champs de pg_stat_replication . Synchronicité différente suivant les cas : `synchronous_commit` peut être défini dans `postgresql.conf` bien sûr, mais aussi par utilisateur, par base, par session, voire par transaction : ```sql ALTER ROLE batch_user SET synchronous_commit TO off ; ALTER DATABASE audit SET synchronous_commit TO local ; SET synchronous_commit TO on ; -- dans la session SET LOCAL synchronous_commit TO remote_apply ; -- dans la transaction ``` Il est conseillé de n’utiliser la synchronisation que pour les modifications les plus importantes et vitales, et la désactiver pour les cas où la performance en écriture prime, ou si vous pouvez relancer l’opération en cas de crash. À vous de définir la bonne valeur par défaut pour synchronous_commit , selon les données, les utilisateurs, les applications, et bien sûr l’impact sur les performances. Par contre, pour modifier synchronous_standby_names , il vous faudra modifier postgresql.conf ou passer par ALTER SYSTEM , puis recharger la configuration. En cas de problème : Il faut savoir qu’en cas d’indisponibilité du ou des secondaire(s) synchrone(s), pour que des transactions synchrones bloquées puissent se terminer, le plus simple est de retirer le secondaire problématique de synchronous_standby_names depuis une autre session : ```sql SHOW synchronous_standby_names ; ``` ```text synchronous_standby_names --------------------------- ``` 2 (s2,s3) -- s2 ne répond plus ```sql ALTER SYSTEM SET synchronous_standby_names TO 's3'; SELECT pg_reload_conf(); ``` Une alternative est de débrayer le mode synchrone. Cela désactivera aussi le mode synchrone vers d’autres secondaires encore en place. ```sql ALTER SYSTEM SET synchronous_commit TO 'local' ; SELECT pg_reload_conf(); ``` Mais les sessions bloquées ne verront pas tout de suite le changement de configuration. Il faudra leur envoyer un signal pour qu’elles se terminent. Elles seront bien validées, dans les journaux du primaire au moins. ```sql SELECT pg_cancel_backend(2868749) ; ``` ```text pg_cancel_backend ------------------t ``` Apparaît alors le message suivant dans les traces : WARNING: canceling wait for synchronous replication due to user request DETAIL: The transaction has already committed locally, but might not have been replicated to the standby. ### 7.8 Réplication en cascade #### 7.8.1 Principe de la réplication en cascade - Un secondaire peut fournir les informations de réplication - Décharger le serveur primaire de ce travail - Diminuer la bande passante du serveur primaire Imaginons un système PostgreSQL installé à Paris et un serveur secondaire installé à Marseille. Il s’avère que le site de Marseille devient plus important et qu’un deuxième serveur secondaire doit y être installé. Si ce deuxième serveur secondaire se connecte directement sur le primaire à Paris, la consommation de la bande passante va doubler. PostgreSQL permet au deuxième serveur secondaire de se connecter au premier (donc en local dans notre exemple) pour récupérer les informations de réplication. La bande passante est ainsi mieux maîtrisée. La configuration d’un tel système est très simple. Il suffit d’indiquer l’adresse IP ou l’alias du serveur secondaire à la place de celui du serveur primaire dans le paramètre primary_conninfo du fichier postgresql.conf du deuxième serveur secondaire. Si un secondaire est promu et devient primaire, cela n’a pas d’impact sur ses propres secondaires. ### 7.9 Délai à la réplication #### 7.9.1 Délai à la réplication - Le secondaire peut appliquer les modifications avec un retard - recovery_min_apply_delay = 1h - Nombreux inconvénients Si une durée est fournie à recovery_min_apply_delay , le secondaire peut attendre un certain temps avant d’appliquer les transactions. Le secondaire récupère toujours les journaux par streaming (à la rigueur log shipping) aussi vite que possible, et les stocke le temps nécessaire. Par contre, une entrée correspondant à un COMMIT n’est appliquée que si elle a l’âge requis, et les journaux suivants restent en attente. Il est donc possible de lire sur le secondaire des données déjà effacées sur le primaire. Les inconvénients sont assez nombreux : - en cas de bascule, le secondaire promu a déjà les journaux et peut les restaurer, mais, selon le retard, cela peut être long et allonger le temps de bascule ; - les transactions synchrones deviennent en pratique impossible ; - les conflits de réplication sont potentiellement plus fréquents, car activer hot_standby_feedback rend intolérable l’impact sur la fragmentation du primaire; - une sauvegarde PITR depuis le secondaire est allongée (car l’appel à pg_backup_start() n’y arrive qu’après le délai) ; - le stockage des journaux sur le secondaire peut devenir problématique. Certains utilisent cette fonctionnalité « au cas où » il y ait eu une fausse manipulation en production, pour récupérer des données sans avoir à restaurer la base ou l’instance entière. C’est possible, mais plutôt déconseillé sur un secondaire utilisé pour autre chose. En effet, hors le côté bricolage, il faut tenir compte de la loi de Murphy : le délai prévu sera souvent trop court. Cette technique ne doit pas se substituer à la maîtrise de la restauration d’une instance complète. Pour d’autres détails, voir la documentation officielle 3 . - Source 3 : https://docs.postgresql.fr/current/runtime-config-replication.html#GUC-RECOVERY-MIN-APPLY-DELAY ### 7.10 Décrochage d’un secondaire #### 7.10.1 Causes d’un décrochage - Par défaut, le primaire n’attend pas les secondaires pour recycler ses WAL - « Décrochage » si : - liaison trop lente - secondaire déconnecté - secondaire trop lent à rejouer - Le primaire ne peut plus alimenter le secondaire - Reconstruction du secondaire nécessaire Par défaut, le primaire n’attend pas que le serveur secondaire ait obtenu tous les journaux avant de recycler ses journaux. Le secondaire peut donc se retrouver à demander au principal des informations que celui-ci n’a même plus à disposition, car il a recyclé les journaux concernés. Cela peut arriver si la liaison est mauvaise, instable, ou si le secondaire a peiné à réappliquer les journaux pour une raison ou une autre, voire s’il a été déconnecté un certain temps. Le secondaire ne peut alors plus continuer la réplication : il « décroche » (tout comme après la perte d’un journal en log shipping). Ce phénomène peut intervenir même sur un serveur fraîchement copié, si le primaire évolue trop vite. Il faut reconstruire le secondaire, ce qui est peut être très gênant avec une base volumineuse. Une réplication synchrone ne protège pas de ce problème, car toutes les transactions ne sont pas forcément synchrones. De plus, l’impact en performance est sévère. Les paramètres ```text hot_standby_feedback et vacuum_defer_cleanup_age (<= v15) ne protègent pas non plus. ``` #### 7.10.2 Éviter le décrochange 3 solutions : - Archivage en plus du streaming - utilisation automatique - mutualisation avec sauvegarde PITR - Slot de réplication - Garder des journaux - wal_keep_size Il existe plusieurs moyens pour éviter le décrochage : L’archivage comme sécurisation du streaming Une réplication par log shipping peut être configurée en plus de la réplication par flux. Comme une sauvegarde PITR du principal est très souvent en place, il ne coûte pas grand-chose de permettre au secondaire d’y puiser les journaux manquants. Ainsi, si la réplication par streaming décroche, le secondaire bascule sur la restauration par log shipping et va puiser dans le dépôt d’archives, dont l’historique couvre généralement plusieurs jours, voire semaines. Une fois le retard rattrapé, le secondaire ne trouvera plus de nouveaux journaux et rebasculera sur la réplication par streaming, qui fonctionnera à nouveau. Un inconvénient est qu’il faut bien penser à tester les deux modes de réplication pour ne pas avoir de mauvaise surprise le jour où le streaming décroche. Cette configuration est très fréquente, et même recommandée, surtout avec une sauvegarde PITR déjà en place. Slots de réplication Un secondaire peut informer son primaire de là où il en est au moyen d’un « slots de réplication ». Le primaire sait ainsi quels journaux sont encore nécessaires à ses secondaires et ne les recycle pas. Cette technique est également très courante. Nous allons la voir plus bas. Garder des journaux La dernière méthode est moins recommandée mais peut être utile : elle consiste à paramétrer ```text wal_keep_size sur le primaire, par exemple : ``` ```ini wal_keep_size = '16GB' ``` Les journaux de transaction bons à recycler seront en fait conservés temporairement à hauteur de la volumétrie indiquée. Un secondaire en retard a alors plus de chances que le primaire n’ait pas déjà effacé les journaux dont il a besoin. C’est le moyen le plus simple, mais il gaspille du disque de façon permanente. Surtout, il ne garantit pas d’éviter un décrochage si la quantité à conserver a été sous-estimée. #### 7.10.3 Sécurisation par log shipping - archive_command / restore_command - script par l’outil PITR - ou cp , scp , lftp , rsync , script… - Nettoyage - rétention des journaux si outil PITR - ou outil dédié : ```ini archive_cleanup_command = '/usr/pgsql-14/bin/pg_archivecleanup -d rep_archives/ %r' ``` La sécurisation par l’archivage consiste donc à permettre au serveur secondaire de rattraper son retard avant de redémarrer sa connexion de réplication. Manuellement : Il suffit qu’ archive_command et restore_command soient correctement configurés et indiquent où copier les archives, et comment les récupérer. La mise en place est la même que lors de la mise en place d’une sauvegarde physique 4 . La restore_command est ignorée si le secondaire a rebasculé en streaming. Les serveurs secondaires ont cependant la responsabilité de supprimer les journaux devenus inutiles pour ne pas saturer l’espace disque. Afin d’automatiser ce nettoyage, on définit sur le secondaire le paramètre archive_cleanup_command . La commande qui s’y trouve est appelée périodiquement (même si le streaming fonctionne), après chaque restartpoint (l’équivalent d’un checkpoint sur un secondaire), afin de supprimer les archives devenues inutiles pour le secondaire. Généralement, on se contente d’appeler un outil dédié, nommé pg_archivecleanup 5 : ```ini archive_cleanup_command = '/usr/pgsql-16/bin/pg_archivecleanup depot_archives/ %r' ``` La situation se complique si un même dépôt d’archives est partagé par plusieurs secondaires… Avec un outil de sauvegarde PITR : La situation est plus simple s’il existe déjà une sauvegarde PITR par un outil comme pgBackRest ou barman 6 : archive_command comme restore_command sont fournies dans leur documentation. La purge des journaux étant aussi gérée par cet outil on ne configurera bien sûr pas archive_cleanup_command ! #### 7.10.4 Slot de réplication : mise en place - Slot de réplication sur le primaire : - max_replication_slots - NB : non répliqué! - création manuelle : ```sql SELECT pg_create_physical_replication_slot ('nomsecondaire') ; ``` - Secondaire : - dans postgresql.conf ```ini primary_slot_name = 'nomsecondaire' ``` - et rechargement de configuration Le paramètre max_replication_slots doit être supérieur à 0. Par défaut il vaut 10, ce qui suffit souvent. S’il faut le modifier, un redémarrage est nécessaire. - Source 4 : https://dali.bo/i2_html - Source 5 : https://docs.postgresql.fr/current/pgarchivecleanup.html - Source 6 : https://dali.bo/i4_html Un slot de réplication se crée sur le primaire par un appel de fonction et en lui attribuant un nom : ```sql SELECT pg_create_physical_replication_slot ('nomsecondaire'); ``` Traditionnellement le nom est celui du secondaire qui va l’utiliser. Cela facilite la supervision mais n’a rien d’obligatoire. Sur le secondaire, on ajoute dans postgresql.conf la mention du slot à utiliser : ```ini primary_slot_name = 'nomsecondaire' ``` Puis il suffit de recharger la configuration du secondaire. Un slot est propre à l’instance et ne fait pas partie des objets répliqués. Lors d’une restauration PITR ou une bascule, il doit fréquemment être recréé manuellement. En cas de réplication en cascade, un secondaire peut avoir ses propres slots de réplication dédiés à ses propres secondaires. #### 7.10.5 Slot de réplication : avantages & risques - Avantages : - plus de risque de décrochage des secondaires - supervision facile : pg_replication_slots - utilisable par pg_basebackup - Risque : accumulation des journaux - danger pour le primaire! - sécurité : max_slot_wal_keep_size , idle_replication_slot_timeout - Risque : vacuum bloqué - hot_standby_feedback ? Le slot de réplication garantit au secondaire que son primaire ne recyclera pas les journaux dont il aura encore besoin. Le secondaire peut donc prendre un retard conséquent sans risque de décrochage. Il est facile de voir à quel point se trouve un secondaire avec la vue pg_replication_slots (noter les champs active et restart_lsn ), qui complète pg_stat_replication : ```sql SELECT * FROM pg_replication_slots \gx ``` ```text -[ RECORD 1 ]+----------slot_name | s3 plugin | ø slot_type | physical datoid | ø database | ø temporary | f ``` ```text active | t active_pid | 3951267 xmin | 3486363 catalog_xmin | ø restart_lsn | 14/ACDD9E10 confirmed_flush_lsn | ø wal_status | reserved safe_wal_size| 5370962416 two_phase | f conflicting | ø ``` Avec pg_basebackup : pg_basebackup, déjà évoqué plus haut, utilise les slots pour garantir que sa sauvegarde sera complète. Ses options exactes varient suivant les versions. pg_basebackup est capable de créer ce slot (option --create-slot ) qui sera conservé ensuite. Risque d’accumulation des journaux : Par contre, les slots ont un grave inconvénient : en cas de problème prolongé sur un secondaire, les journaux vont s’accumuler sur le primaire, sans limitation de taille ni de durée, déclenchant une saturation de la partition de pg_wal dans le pire des cas - et l’arrêt du primaire. Certes, la supervision de l’espace disque fait partie des bases de la supervision, mais les journaux s’accumulent parfois très vite lors d’une mise à jour massive. Il est donc important de détruire tout slot dont le secondaire a été désactivé ou est hors ligne pour un certain temps (quitte à devoir reconstruire le secondaire) : ```sql SELECT pg_drop_replication_slot ('nomsecondaire'); ``` Les plus prudents se limiteront donc à une réplication par streaming classique sans slot, couplée au log shipping pour éviter le décrochage. Rappelons que l’archivage peut lui aussi laisser les journaux s’accumuler en cas de problème sur le serveur cible de l’archivage. Le paramètre max_slot_wal_keep_size permet de limiter la quantité de WAL conservés par les slots de réplication. Au-delà, le primaire ne garantit plus la conservation. Le secondaire risque à nouveau de décrocher, mais une longue indisponibilité ne risque plus de saturer le disque du primaire. C’était un souci fréquent avant PostgreSQL 13. À partir de la version 18, il est également possible d’invalider un slot s’il n’a pas été utilisé depuis une durée configurable avec le paramètre idle_replication_slot_timeout . En production, il est conseillé de toujours définir max_slot_wal_keep_size (à une valeur élevée au besoin) si l’on crée un slot de réplication. En effet, l’expérience montre que les slots de réplication sont souvent oubliés. Risque sur le vacuum sur le primaire : Le slot permet au primaire de mémoriser durablement la transaction où s’est arrêté le secondaire ( pg_replication_slots.xmin est renseigné), à condition que soit à ```text hot_standby_feedback ``` on . Avec un slot de réplication actif et hot_standby_feedback à on , si le secondaire est durablement déconnecté, non seulement les journaux de transaction vont s’accumuler sur le primaire, mais le vacuum y sera inefficace jusqu’au rétablissement de la réplication de ce secondaire ou la destruction du slot! Ce problème de vacuum persiste même si l’on a paramétré max_slot_wal_keep_size ou idle_replication_slot_timeout pour éviter la saturation des journaux… Selon l’utilisation, on peut donc préférer monter max_standby_streaming_delay plutôt que de laisser hot_standby_feedback à on . ### 7.11 Divergence entre instances Nous avons évoqué jusqu’ici des instances qui suivaient le même flux de données, et comment y parvenir. Ce n’est plus possible en cas de « divergence », c’est-à-dire que le primaire et celui qui doit (re)devenir son secondaire n’ont pas suivi le même flux de journaux. #### 7.11.1 Quand y a-t-il divergence? Il existe des enregistrements de journaux différents pour les mêmes LSN. Cas : - Secondaire ouvert un temps en écriture (timeline propre) - Ancien primaire qui a écrit après la promotion d’un secondaire - En général sur timelines séparées Un secondaire qui a bien « accroché » son primaire se synchronise automatiquement avec lui, que ce soit par streaming ou log shipping. C’est notamment le cas si l’on vient de le construire depuis une sauvegarde ou avec pg_basebackup , et que l’archivage ou le streaming alimentent correctement le secondaire. Cependant, il y a des cas où un secondaire ne peut être simplement raccroché à un primaire, notamment si le secondaire se croit plus avancé que le primaire dans le flux des journaux. Cela peut arriver pour plusieurs raisons. Un premier exemple est un secondaire qui est créé, et démarré en tant que primaire par erreur, même très brièvement, suite à une erreur de configuration (oubli du standby.signal notamment). Il a ouvert sa propre timeline et progressé de son côté dans le flux de journaux. Même reconfiguré comme secondaire, il ne pourra pas appliquer ce qui vient du primaire : les deux instances ont vécu des événements différents pour les mêmes LSN, dans deux timelines différentes. Un autre cas problématique est un ancien primaire que l’on veut transformer en secondaire de son ancien secondaire qui a été promu dans des conditions non idéales. Lors d’une bascule propre, l’ancien primaire a envoyé tous ses journaux par streaming au secondaire, ou les a au moins archivés, avant de s’arrêter ou d’être arrêté, et le secondaire promu les a récupérés puis tous rejoués, avant de s’ouvrir en écriture : les deux serveurs sont arrivés au même LSN dans le flux de données. L’ancien primaire, redémarré avec un standby.signal et la bonne configuration, peut donc appliquer directement les nouveaux LSN qui arrivent du nouveau primaire, même sur la nouvelle timeline. Mais si le primaire a été arrêté violemment, ou a été déconnecté sans revenir en ligne avant que son secondaire soit promu, donc sans pouvoir transmettre tous ses journaux, l’ancien secondaire n’a rejoué que ce qu’il a reçu, puis s’est ouvert en écriture sur une nouvelle timeline depuis un LSN moins avancé que celui où le primaire était finalement arrivé avant son arrêt définitif. Les deux serveurs ont donc « divergé », même pendant très peu de temps. Les modifications présentes dans les journaux non envoyés au nouveau primaire doivent être considérées comme perdues. Quand l’ancien primaire est reconfiguré comme secondaire et redémarré, il voit que sa timeline est plus avancée que la version qu’en a gardée le nouveau primaire. Là encore, pour un même LSN le contenu des journaux diffère entre les deux instances, et la réconciliation est impossible. La principale solution, et la plus simple, reste alors la reconstruction du secondaire à raccrocher. #### 7.11.2 Corriger une divergence - Reconstruire les serveurs secondaires à partir du nouveau primaire : - rsync , restauration PITR, pg_basebackup … - Pour les divergences faibles : - pg_rewind Reconstruire un serveur secondaire passe par son arrêt et par les mêmes méthodes que la construction : - recopier les données depuis le primaire avec pg_basebackup est envisageable, mais c’est souvent problématique pour les gros volumes; - un outil de restauration PITR est une option préférable, car il permet de recréer le secondaire à un point précédant la divergence, et le processus de copie est optimisé (parallélisme, restauration d’un snapshot de baie…); - si la divergence est très récente, on peut réduire la durée de reconstruction, grâce à la faible différence entre les deux instances, si l’outil gère les écarts ( rsync --whole-file , option ```text --delta de pgBackRest…). ``` Une alternative à la reconstruction est l’utilisation de l’outil pg_rewind (livré avec PostgreSQL) pour « rembobiner » le secondaire, si tous les journaux nécessaires à l’opération sont disponibles et que la configuration de l’instance le permet. Ces outils et la reconstruction sont traités plus en détail dans W3 - Les outils de réplication physique 7 . - Source 7 : https://dali.bo/w3_html ### 7.12 Synthèse des paramètres #### 7.12.1 Serveur primaire Log shipping Streaming ```ini wal_level = replica * wal_level = replica * archive_mode = on * archive_command * archive_library archive_timeout wal_sender_timeout ``` max_wal_senders max_replication_slots ```text wal_keep_size ``` max_slot_wal_keep_size * idle_replication_slot_timeout (*) paramètres indispensables, généralement modifiés par rapport à l’installation par défaut, ou d’utilisation fortement conseillés Ne figurent pas les paramètres disparus dans les toutes dernières versions, généralement inutilisés auparavant. #### 7.12.2 Serveur secondaire Log shipping Streaming ```ini wal_level = replica * wal_level = replica * restore_command * archive_cleanup_command ``` (selon outil) primary_conninfo * ```text wal_receiver_timeout hot_standby primary_slot_name * ``` max_standby_archive_delay max_standby_streaming_delay ```text hot_standby_feedback wal_receiver_status_interval ``` (*) paramètres indispensables, généralement modifiés par rapport à l’installation par défaut, ou d’utilisation fortement conseillés Ne figurent pas les paramètres disparus dans les toutes dernières versions, généralement inutilisés auparavant. ### 7.13 Conclusion - Système de réplication fiable… - et très complet PostgreSQL possède de nombreuses fonctionnalités de réplication très avancées, telle que le choix du synchronisme de la réplication à la transaction près, ce qui en fait un système aujourd’hui très complet. #### 7.13.1 Questions N’hésitez pas, c’est le moment ! ### 7.14 Quiz - https://dali.bo/w2b_quiz ### 7.15 Travaux pratiques La version en ligne des solutions de ces TP est disponible sur https://dali.bo/w2b_solutions. #### 7.15.1 Réplication asynchrone en flux avec deux secondaires But : Mettre en place une réplication asynchrone en flux avec deux secondaires. (Ce qui suit suppose qu’il existe déjà deux instances instance1 et instance2 issues du TP précédent.) Créer un deuxième serveur secondaire instance3, avec son service systemd, avec l’outil pg_basebackup . S’assurer que la réplication fonctionne bien et que les processus adéquats sont bien présents. #### 7.15.2 Slots de réplication But : Mettre en place un slot de réplication. Les slots de réplication permettent au serveur principal de connaître les journaux encore nécessaires aux serveurs secondaires. - Créer un slot de réplication sur le primaire. - Configurer le deuxième secondaire pour utiliser ce slot. - Contrôler que le slot est bien actif. - Arrêter le deuxième secondaire instance3 - Générer de l’activité (créer une table d’un million de lignes, par exemple). - Où sont conservés les journaux de transaction? - Forcer un CHECKPOINT . Quel est le journal le plus ancien sur le serveur principal? - Que se serait-il passé sans slot de réplication? - Démarrer le deuxième secondaire instance3. - Contrôler que les deux secondaires instance2 et instance3 sont bien en réplication par streaming avec la vue système adéquate. - Détruire le slot de réplication. #### 7.15.3 Log shipping But : Mettre en place une réplication par log shipping. - Plutôt que d’utiliser un slot de réplication, configurer l’instance primaire pour qu’elle archive ses journaux de transactions dans /var/lib/pgsql/17/archives . - Vérifier que l’archivage fonctionne. - Configurer les instances secondaires pour utiliser ces archives en cas de désynchronisation. Ne pas oublier de redémarrer. - Simuler un décrochage de la deuxième instance secondaire. - Vérifier les journaux de transactions au niveau du serveur principal ainsi que les archives. - Redémarrer l’instance secondaire et observer ses traces pour suivre ce qu’il se passe. - Actuellement il n’y a aucun nettoyage des journaux archivés même s’ils ont été rejoués sur les secondaires. Quel paramètre modifier pour supprimer les anciens journaux ? - Si les deux secondaires puisaient leur journaux depuis le même répertoire d’archive, quel pourrait être le risque ? #### 7.15.4 Réplication synchrone en flux avec trois secondaires But : Mettre en place une réplication synchrone en flux avec trois secondaires. - Créer un troisième serveur secondaire instance4 avec l’outil pg_basebackup , en streaming replication. - Passer la réplication en synchrone pour un seul secondaire (instance2). - Arrêter le secondaire synchrone. - Modifier des données sur le principal. Que se passe-t-il? - Redémarrer le secondaire synchrone. - Passer le deuxième secondaire comme également synchrone, en indiquant sur le primaire : ```text synchronous_standby_names = 'FIRST 1 (instance2,instance3)' ``` - Arrêter le premier secondaire synchrone. - Modifier des données sur le principal. Que se passe-t-il? - Quel paramètre modifier pour avoir deux secondaires synchrones simultanément? - Vérifier que les deux secondaires sont synchrones. - Pour la suite du TP, configurer le paramètre application_name de l’instance instance4. - Ensuite, se baser sur la notion de quorum pour obtenir deux serveurs synchrones parmi les trois instances secondaires en utilisant : ```text synchronous_standby_names = ANY 2 (instance2,instance3,instance4) ``` #### 7.15.5 Réplication synchrone : cohérence des lectures (optionnel) But : Découvrir les mécanismes de mise en pause et de reprise de réplication. - Exécuter la commande sur le premier secondaire ```sql SELECT pg_wal_replay_pause(); ``` synchrone. - Ajouter des données sur le principal et contrôler leur présence sur le secondaire. Que constatez-vous? - Est-ce que les instances sont bien synchrones (utiliser la vue pg_stat_replication )? - Relancer le rejeu et contrôler la présence des enregistrements sur les trois instances. - Quel paramètre modifier pour obtenir les mêmes résultats sur les trois instances? - Appliquer ce paramètre et effectuer la même opération (pause du rejeu puis insertion d’enregistrements sur le principal). Que se passe-t-il? ### 7.16 Travaux pratiques (solutions) NB : Ce TP a été mis à jour pour RockyLinux 8. Adapter les numéros de version de PostgreSQL dans les chemins au besoin. #### 7.16.1 Réplication asynchrone en flux avec deux secondaires (Ce qui suit suppose qu’il existe déjà deux instances instance1 et instance2 issues du TP précédent.) Créer un deuxième serveur secondaire instance3, avec son service systemd, avec l’outil pg_basebackup . À part les appels à systemctl , les opérations se font toutes en tant que postgres. Le service se crée comme les deux précédents, en copiant et modifiant le fichier service par défaut : ```bash # cp /lib/systemd/system/postgresql-17.service \ /etc/systemd/system/instance3.service ``` ```bash # sed -i "s|/var/lib/pgsql/17/data/|/var/lib/pgsql/17/instance3/|" \ /etc/systemd/system/instance3.service ``` ou modifier à la main cette ligne dans /etc/systemd/system/instance3.service : ```text Environment=PGDATA=/var/lib/pgsql/17/instance3/ ``` Nous allons utiliser la même méthode que précédemment pour créer le deuxième secondaire : ```bash $ pg_basebackup -D /var/lib/pgsql/17/instance3 -P -h 127.0.0.1 -U repli -R -c fast ``` 104425/104425 kB (100%), 1/1 tablespace Créer le fichier standby.signal s’il n’existe pas déjà et adapter le port de l’instance primaire dans le postgresql.auto.conf de la nouvelle instance : ```bash $ touch /var/lib/pgsql/17/instance3/standby.signal $ cat /var/lib/pgsql/17/instance3/postgresql.auto.conf primary_conninfo = 'user=repli passfile=''/var/lib/pgsql/.pgpass'' host=127.0.0.1 port=5432…' ``` Dans le fichier /var/lib/pgsql/17/instance3/postgresql.conf , modifier le numéro de port de l’instance : ```text port = 5434 ``` Et on peut enfin démarrer le deuxième secondaire : ```bash # systemctl start instance3 ``` S’assurer que la réplication fonctionne bien et que les processus adéquats sont bien présents. Vérifions la liste des processus : ```bash $ ps -o pid,cmd fx ``` PID CMD 6845 /usr/pgsql-17/bin/postmaster -D /var/lib/pgsql/17/instance3/ 6847_ postgres: logger 6848_ postgres: startup recovering 00000003000000000000000E 6849_ postgres: checkpointer 6850_ postgres: background writer 6851_ postgres: stats collector 6852_ postgres: walreceiver streaming 0/E000060 5841 /usr/pgsql-17/bin/postmaster -D /var/lib/pgsql/17/instance2/ 5843_ postgres: logger 5844_ postgres: startup recovering 00000003000000000000000E 5845_ postgres: checkpointer 5846_ postgres: background writer 5847_ postgres: stats collector 5848_ postgres: walreceiver streaming 0/E000060 5684 /usr/pgsql-17/bin/postmaster -D /var/lib/pgsql/17/instance1/ 5686_ postgres: logger 5688_ postgres: checkpointer 5689_ postgres: background writer 5690_ postgres: stats collector 5792_ postgres: walwriter 5793_ postgres: autovacuum launcher 5794_ postgres: logical replication launcher 5849_ postgres: walsender repli 127.0.0.1(48230) streaming 0/E000060 6853_ postgres: walsender repli 127.0.0.1(48410) streaming 0/E000060 L’instance principale ( /var/lib/pgsql/17/instance1/ ) a bien deux processus et walsender chaque instance secondaire a son walreceiver . #### 7.16.2 Slots de réplication - Créer un slot de réplication sur le primaire. - Configurer le deuxième secondaire pour utiliser ce slot. - Contrôler que le slot est bien actif. Le nombre de slots de réplication par défaut suffit généralement : ```sql SHOW max_replication_slots ; ``` max_replication_slots ```text ----------------------- ``` 10 La commande suivante permet de créer un slot de réplication sur le serveur principal : ```sql SELECT * FROM pg_create_physical_replication_slot('slot_instance3') ; ``` ```text slot_name| lsn ----------------+----slot_instance3 | ``` Il faut ensuite spécifier le slot dans le fichier postgresql.auto.conf du serveur secondaire : ```ini primary_slot_name = 'slot_instance3' ``` Puis redémarrer le serveur secondaire : ```bash # systemctl restart instance3 ``` Sur le primaire, la vue pg_replication_slots permet de s’assurer que le slot est bien actif : ```sql SELECT * FROM pg_replication_slots \gx ``` ```text -[ RECORD 1 ]-------+--------------slot_name | slot_instance3 plugin | slot_type | physical datoid | database | temporary | f active | t active_pid | 6900 xmin | catalog_xmin | restart_lsn | 0/E000148 confirmed_flush_lsn | ``` - Arrêter le deuxième secondaire instance3 - Générer de l’activité (créer une table d’un million de lignes, par exemple). - Où sont conservés les journaux de transaction? - Forcer un CHECKPOINT . Quel est le journal le plus ancien sur le serveur principal? ```bash # systemctl stop instance3 ``` Pour générer de l’activité : ```bash psql -d b1 -c "INSERT INTO t1 SELECT * FROM generate_series(1,1000000);" ``` Cette table fait 35 Mo, et va donc nécessiter l’écriture ou la création d’au moins 3 journaux de transactions de 16 Mo. On les voit en regardant les journaux au niveau du serveur principal : ```bash $ ls -alh /var/lib/pgsql/17/instance1/pg_wal/ ``` (...) -rw-------. 1 postgres postgres 16M Nov 27 16:40 00000003000000000000000E -rw-------. 1 postgres postgres 16M Nov 27 16:40 00000003000000000000000F -rw-------. 1 postgres postgres 16M Nov 27 16:40 000000030000000000000010 -rw-------. 1 postgres postgres 16M Nov 27 16:40 000000030000000000000011 -rw-------. 1 postgres postgres 16M Nov 27 16:40 000000020000000000000012 -rw-------. 1 postgres postgres 16M Nov 27 16:40 000000020000000000000013 (Au niveau SQL, la fonction SELECT * FROM pg_ls_waldir() ORDER BY 1 ; fonctionne aussi.) On constate que le principal conserve les anciens journaux dans le répertoire pg_wal même après un checkpoint, alors qu’il n’en a plus besoin : ```bash $ psql -c "CHECKPOINT;" ``` Le journal le plus ancien (ici 00000003000000000000000E ) est toujours présent. - Que se serait-il passé sans slot de réplication? Le serveur primaire aurait recyclé ses journaux inutiles après le checkpoint suivant (plus précisément, il aurait renommé puis écrasé les fichiers). À son redémarrage, le deuxième secondaire n’aurait pas pu récupérer des journaux indispensables à la réplication et les aurait attendu indéfiniment. - Démarrer le deuxième secondaire instance3. - Contrôler que les deux secondaires instance2 et instance3 sont bien en réplication par streaming avec la vue système adéquate. ```bash # systemctl start instance3 ``` ```sql SELECT * FROM pg_stat_replication \gx ``` ```text -[ RECORD 1 ]----+-----------------------------pid | 5849 usesysid | 16384 usename | repli application_name | walreceiver client_addr| 127.0.0.1 ``` client_hostname ```text | client_port| 48230 ``` backend_start ```text | ... ``` backend_xmin ```text | state | streaming sent_lsn | 0/13FFE6D8 write_lsn | 0/13FFE6D8 flush_lsn | 0/13FFE6D8 replay_lsn | 0/13FFE6D8 write_lag | flush_lag | replay_lag | ``` sync_priority ```text | 0 sync_state | async reply_time | ... -[ RECORD 2 ]----+-----------------------------pid | 7044 usesysid | 16384 usename | repli application_name | walreceiver client_addr| 127.0.0.1 ``` client_hostname ```text | client_port| 48434 ``` backend_start ```text | ... ``` backend_xmin ```text | state | streaming sent_lsn | 0/13FFE6D8 write_lsn | 0/13FFE6D8 flush_lsn | 0/13FFE6D8 replay_lsn | 0/13FFE6D8 write_lag | 00:00:00.942356 flush_lag | 00:00:00.964213 replay_lag | 00:00:01.378381 ``` sync_priority ```text | 0 sync_state | async reply_time | ... ``` La synchronisation a pu se faire rapidement. Forcer un CHECKPOINT et revérifier le répertoire des WALs : ```sql CHECKPOINT ; ``` ```bash $ ls -alh /var/lib/pgsql/17/instance1/pg_wal/ ``` (...) -rw-------. 1 postgres postgres 16M Nov 27 16:44 000000030000000000000013 -rw-------. 1 postgres postgres 16M Nov 27 16:40 000000030000000000000014 -rw-------. 1 postgres postgres 16M Nov 27 16:40 000000030000000000000015 -rw-------. 1 postgres postgres 16M Nov 27 16:40 000000030000000000000016 -rw-------. 1 postgres postgres 16M Nov 27 16:41 000000030000000000000017 -rw-------. 1 postgres postgres 16M Nov 27 16:41 000000030000000000000018 -rw-------. 1 postgres postgres 16M Nov 27 16:41 000000030000000000000019 Le deuxième serveur secondaire ayant rattrapé son retard, le primaire peut enfin recycler ses anciens journaux : ceux affichés sont prêts à recevoir des données. - Détruire le slot de réplication. Les slots de réplication ont un grave inconvénient : en cas de problème prolongé sur un secondaire, les journaux vont s’accumuler sur le primaire, sans limitation de taille ni de durée, déclenchant une saturation de la partition de pg_wal dans le pire des cas - et l’arrêt du primaire. Il est donc conseillé de détruire tout slot dont le secondaire a été désactivé ou est hors ligne pour un certain temps (quitte à devoir reconstruire le secondaire). Ici, après avoir supprimé primary_slot_name du fichier de configuration et redémarré l’instance instance3 : ```bash $ psql -c "SELECT pg_drop_replication_slot('slot_instance3');" ``` Une alternative à la destruction est le paramètre max_slot_wal_keep_size , qui définit à partir de quelle volumétrie PostgreSQL renonce à garder de vieux journaux conservés à cause d’un slot. #### 7.16.3 Log shipping - Plutôt que d’utiliser un slot de réplication, configurer l’instance primaire pour qu’elle archive ses journaux de transactions dans /var/lib/pgsql/17/archives . - Vérifier que l’archivage fonctionne. NB : l’archivage sera configuré ici manuellement. En production, on conseille plutôt de profiter de la présence d’une sauvegarde PITR (pgBackRest, barman…). Tout d’abord, il faut créer le répertoire d’archivage : ```bash $ mkdir /var/lib/pgsql/17/archives ``` L’utilisateur postgres doit avoir le droit de lire et écrire dans ce répertoire. Modifions maintenant le fichier /var/lib/pgsql/17/instance1/postgresql.conf pour que l’instance primaire archive les journaux de transactions : ```ini archive_mode = on archive_command = 'rsync %p /var/lib/pgsql/17/archives/%f' ``` La commande rsync n’est pas installée par défaut, il faut souvent l’installer. Le paquet se nomme simplement rsync dans toutes les distributions Linux habituelles. Le paramètre archive_mode étant modifié, il nous faut redémarrer PostgreSQL : ```bash # systemctl restart instance1 ``` Forçons PostgreSQL à changer de journal de transactions, pour voir si l’archivage fonctionne bien : ```sql SELECT pg_walfile_name (pg_switch_wal()) ; ``` ```text pg_walfile_name -------------------------- ``` 000000030000000000000013 ```bash $ ls -l /var/lib/pgsql/17/archives/ ``` total 16384 -rw-------. 1 postgres postgres 16777216 Nov 27 16:59 000000030000000000000013 Au fil de l’activité, les journaux vont s’accumuler à cet endroit. Pour superviser le bon déroulement de l’archivage, on peut suivre aussi le vue pg_stat_archiver : le dernier journal archivé doit être postérieur au dernier échec d’archivage. - Configurer les instances secondaires pour utiliser ces archives en cas de désynchronisation. Ne pas oublier de redémarrer. Maintenant que l’archivage fonctionne, configurons nos instances secondaires pour utiliser ces archives en cas de désynchronisation en ajoutant dans leurs postgresql.conf ou postgresql.auto.conf : ```ini restore_command = 'cp /var/lib/pgsql/17/archives/%f %p' ``` Penser à redémarrer ensuite les deux instances : ```bash # systemctl restart instance2 # systemctl restart instance3 ``` - Simuler un décrochage de la deuxième instance secondaire. - Vérifier les journaux de transactions au niveau du serveur principal ainsi que les archives. Pour simuler un décrochage de la deuxième instance secondaire, arrêter le service, générer de l’activité et forcer un CHECKPOINT : ```bash # systemctl stop instance3 ``` ```bash $ psql -d b1 -c "INSERT INTO t1 SELECT * FROM generate_series(1,2000000);" $ psql -c "CHECKPOINT;" ``` Vérifier les journaux au niveau du serveur principal ainsi que les archives : ```bash $ ls -alh /var/lib/pgsql/17/instance1/pg_wal/ ``` (...) -rw-------. 1 postgres postgres 16M Nov 27 17:21 00000003000000000000001B -rw-------. 1 postgres postgres 16M Nov 27 17:21 00000003000000000000001C -rw-------. 1 postgres postgres 16M Nov 27 17:21 00000003000000000000001D -rw-------. 1 postgres postgres 16M Nov 27 17:21 00000003000000000000001E -rw-------. 1 postgres postgres 16M Nov 27 17:21 00000003000000000000001F -rw-------. 1 postgres postgres 16M Nov 27 17:21 000000030000000000000020 -rw-------. 1 postgres postgres 16M Nov 27 17:21 000000030000000000000021 -rw-------. 1 postgres postgres 16M Nov 27 17:21 000000030000000000000022 -rw-------. 1 postgres postgres 16M Nov 27 17:21 000000030000000000000023 ```bash $ ls -alh /var/lib/pgsql/17/archives/ ``` (...) -rw-------. 1 postgres postgres 16M Nov 27 17:21 00000003000000000000001B -rw-------. 1 postgres postgres 16M Nov 27 17:21 00000003000000000000001C -rw-------. 1 postgres postgres 16M Nov 27 17:21 00000003000000000000001D -rw-------. 1 postgres postgres 16M Nov 27 17:21 00000003000000000000001E - Redémarrer l’instance secondaire et observer ses traces pour suivre ce qu’il se passe. ```bash # systemctl start instance3 ``` ```bash $ tail -f /var/lib/pgsql/17/instance3/log/postgresql-*.log ``` (...) LOG:restored log file "00000003000000000000001B" from archive LOG:restored log file "00000003000000000000001C" from archive LOG:restored log file "00000003000000000000001D" from archive LOG:restored log file "00000003000000000000001E" from archive ```bash cp: cannot stat ‘/var/lib/pgsql/17/archives/00000003000000000000001F’: ``` No such file or directory LOG:started streaming WAL from primary at 0/1F000000 on timeline 3 Une fois le retard de réplication rattrapé grâce aux archives, l’instance secondaire se reconnecte automatiquement à l’instance primaire. - Actuellement il n’y a aucun nettoyage des journaux archivés même s’ils ont été rejoués sur les secondaires. Quel paramètre modifier pour supprimer les anciens journaux ? Le paramètre archive_cleanup_command permet de spécifier une commande exécutée à la fin d’un restartpoint (équivalent d’un checkpoint sur un secondaire). L’outil pg_archivecleanup est utilisé pour supprimer les journaux inutiles. Il faut évidemment penser à recharger la configuration des instances après changement de paramétrage. NB : archive_cleanup_command n’a d’intérêt que si les archives ne sont pas nettoyées autrement, par exemple par l’outil de sauvegarde PITR. ```ini archive_cleanup_command = '/usr/pgsql-17/bin/pg_archivecleanup -d /var/lib/pgsql/17/archives/ %r' ``` En générant de l’activité et en forçant des CHECKPOINT , le moteur va recycler ses journaux : ```bash $ psql -d b1 -c "INSERT INTO t1 SELECT * FROM generate_series(1,1000000);" $ psql -c "CHECKPOINT;" $ psql -p 5433 -c "CHECKPOINT;" $ psql -p 5434 -c "CHECKPOINT;" ``` L’option -d permet d’avoir des informations supplémentaires dans les traces : … pg_archivecleanup: keeping WAL file "/var/lib/pgsql/17/archives//000000030000000000000029" and later pg_archivecleanup: removing file "/var/lib/pgsql/17/archives//000000030000000000000013" pg_archivecleanup: removing file "/var/lib/pgsql/17/archives//000000030000000000000014" … - Si les deux secondaires puisaient leur journaux depuis le même répertoire d’archive, quel pourrait être le risque ? Le premier secondaire pourrait supprimer des journaux indispensables au deuxième secondaire. Sans ces journaux, la réplication du deuxième secondaire serait impossible et nécessiterait la reconstruction de celui-ci. Pour éviter cela, chaque secondaire doit posséder ses propres copies des journaux, ou la purge doit être opérée par un outil tiers (sauvegarde PITR généralement). #### 7.16.4 Réplication synchrone en flux avec trois secondaires - Créer un troisième serveur secondaire instance4 avec l’outil pg_basebackup , en streaming replication. Nous allons utiliser la même méthode que précédemment pour créer le deuxième secondaire : ```bash $ pg_basebackup -D /var/lib/pgsql/17/instance4 -P -h 127.0.0.1 -U repli -p 5432 -R ``` -c fast 104425/104425 kB (100%), 1/1 tablespace Créons le service systemd de la même manière que précédemment : ```bash # cp /lib/systemd/system/postgresql-17.service \ /etc/systemd/system/instance4.service ``` ```bash # sed -i "s|/var/lib/pgsql/17/data/|/var/lib/pgsql/17/instance4/|" \ /etc/systemd/system/instance4.service ``` ou modifier à la main cette ligne dans /etc/systemd/system/instance4.service : ```text Environment=PGDATA=/var/lib/pgsql/17/instance4/ ``` Créer le fichier standby.signal s’il n’existe pas déjà et adapter le port de l’instance primaire dans postgresql.auto.conf créé par la commande précédente : ```bash $ touch /var/lib/pgsql/17/instance4/standby.signal $ cat /var/lib/pgsql/17/instance4/postgresql.auto.conf primary_conninfo = 'user=repli passfile=''/var/lib/pgsql/.pgpass'' host=127.0.0.1 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' ``` Dans le fichier /var/lib/pgsql/17/instance4/postgresql.conf , modifier le numéro de port de l’instance : ```text port = 5435 ``` Et on peut enfin démarrer le deuxième secondaire : ```bash # systemctl start instance4 ``` - Passer la réplication en synchrone pour un seul secondaire (instance2). Nous allons passer le premier secondaire en tant que secondaire synchrone. Pour cela, il doit (configuration dans avoir un nom, indiqué par le paramètre de connexion ```text application_name ``` postgresql.conf ou postgresql.auto.conf ) : ```ini primary_conninfo = 'user=repli passfile=''/var/lib/pgsql/.pgpass'' host=127.0.0.1 port=5432 application_name=instance2' ``` Ensuite, nous devons indiquer dans la liste des serveurs synchrones dans ```bash /var/lib/pgsql/17/instance1/postgresql.conf : ``` ```text synchronous_standby_names = 'instance2' ``` Il ne reste plus qu’à recharger la configuration pour les deux serveurs : ```bash # systemctl reload instance1 # systemctl reload instance2 ``` Vérifions l’état de la réplication pour les trois secondaires : ```bash $ psql -p 5432 ``` ```sql SELECT application_name, backend_start, state, sync_state ``` FROM pg_stat_replication \gx ```text -[ RECORD 1 ]----+-----------------------------application_name | walreceiver ``` backend_start ```text | … state | streaming sync_state | async -[ RECORD 2 ]----+-----------------------------application_name | instance2 ``` backend_start ```text | … state | streaming sync_state | sync -[ RECORD 3 ]----+-----------------------------application_name | walreceiver ``` backend_start ```text | … state | streaming sync_state | async ``` Nous avons bien un serveur synchrone (au milieu, avec l’état sync ) et deux serveurs asynchrones (état async ) non nommés. - Arrêter le secondaire synchrone. - Modifier des données sur le principal. Que se passe-t-il? Exécutons une requête de modification : ```bash $ psql -p 5432 b1 ``` ```sql CREATE TABLE t3(id integer); ``` La table est bien créée, sans attendre. Maintenant, arrêtons le serveur secondaire synchrone et faisons une nouvelle modification sur le principal : ```bash # systemctl stop instance2 ``` ```bash $ psql -p 5432 b1 ``` ```sql BEGIN ; INSERT INTO t3 SELECT 42 ; COMMIT ; ``` La requête reste bloquée. En effet, le secondaire ne peut pas répondre à la demande de la réplication car il est éteint. Du coup, le principal est bloqué en écriture. Il faut soit démarrer le secondaire, soit modifier la configuration du paramètre synchronous_standby_names . - Redémarrer le secondaire synchrone. Démarrer le secondaire synchrone à partir d’un autre terminal : la requête sur le principal se termine. - Passer le deuxième secondaire comme également synchrone, en indiquant sur le primaire : ```text synchronous_standby_names = 'FIRST 1 (instance2,instance3)' ``` Nous allons maintenant passer le deuxième secondaire en synchrone avec le application_name positionné à instance3 afin de les différencier. Ensuite ajoutons instance3 à la liste des secondaires synchrones sur l’instance principale. ```text synchronous_standby_names = 'FIRST 1 (instance2,instance3)' ``` Rechargeons la configuration du primaire : ```bash # systemctl reload instance1 ``` ```bash $ psql -x -p 5432 -c "SELECT application_name, backend_start, state, sync_state ``` FROM pg_stat_replication;" ```text -[ RECORD 1 ]----+-----------------------------application_name | instance3 ``` backend_start ```text | … state | streaming sync_state | potential -[ RECORD 2 ]----+-----------------------------application_name | instance2 ``` backend_start ```text | … state | streaming sync_state | sync -[ RECORD 3 ]----+-----------------------------application_name | walreceiver ``` backend_start ```text | … state | streaming sync_state | async ``` ```bash $ psql -p 5432 -c "SHOW synchronous_standby_names" ``` ```text synchronous_standby_names ---------------------------------- ``` FIRST 1 (instance2,instance3) Cette fois, les deux serveurs correspondent au synchronous_standby_names , et on peut constater qu’un serveur est sync et l’autre potential . On a demandé un seul serveur synchrone avec le principal. Si les deux serveurs avaient le même application_name , il n’y aurait eu qu’un seul serveur sync . - Arrêter le premier secondaire synchrone. - Modifier des données sur le principal. Que se passe-t-il? Arrêt du premier secondaire synchrone : ```bash # systemctl stop instance2 ``` ```bash $ psql -x -p 5432 -c "SELECT application_name, backend_start, state, sync_state ``` FROM pg_stat_replication;" ```text -[ RECORD 1 ]----+-----------------------------application_name | instance3 ``` backend_start ```text | … state | streaming sync_state | sync -[ RECORD 2 ]----+-----------------------------application_name | walreceiver ``` backend_start ```text | … state | streaming sync_state | async ``` Et faisons une modification sur le principal : ```bash $ psql -p 5432 -c "CREATE TABLE t4(id integer);" b1 ``` ```sql CREATE TABLE ``` Cette fois, tout se passe bien. Le premier secondaire n’est pas disponible mais le second l’est. Il prend donc la suite du premier secondaire en tant que secondaire synchrone. ```bash # systemctl start instance2 ``` - Quel paramètre modifier pour avoir deux secondaires synchrones simultanément? - Vérifier que les deux secondaires sont synchrones. Dans notre cas : ```text synchronous_standby_names = 'FIRST 2 (instance2,instance3)' ``` Après un reload du principal on constate bien que les deux serveurs sont synchrones : ```bash $ psql -x -c "SELECT application_name, backend_start, state, sync_state FROM ``` pg_stat_replication;" ```text -[ RECORD 1 ]----+-----------------------------application_name | instance3 ``` backend_start ```text | … state | streaming sync_state | sync -[ RECORD 2 ]----+-----------------------------application_name | instance2 ``` backend_start ```text | … state | streaming sync_state | sync -[ RECORD 3 ]----+-----------------------------application_name | walreceiver ``` backend_start ```text | … state | streaming sync_state | async ``` L’indisponibilité d’un seul des deux secondaires génére une attente lors d’écritures sur le primaire. - Pour la suite du TP, configurer le paramètre application_name de l’instance instance4. - Ensuite, se baser sur la notion de quorum pour obtenir deux serveurs synchrones parmi les trois instances secondaires en utilisant : ```text synchronous_standby_names = ANY 2 (instance2,instance3,instance4) ``` Configurer le application_name de l’instance instance4. Ensuite, configurer dans /var/lib/pgsql/17/instance1/postgresql.conf : ```text synchronous_standby_names = 'ANY 2 (instance2,instance3,instance4)' ``` On obtient alors un sync_state à la valeur quorum : ```bash $ psql -x -c "SELECT application_name, backend_start, state, sync_state ``` FROM pg_stat_replication;" ```text -[ RECORD 1 ]----+-----------------------------application_name | instance3 ``` backend_start ```text | … state | streaming sync_state | quorum -[ RECORD 2 ]----+-----------------------------application_name | instance2 ``` backend_start ```text | … state | streaming sync_state | quorum -[ RECORD 3 ]----+-----------------------------application_name | instance4 ``` backend_start ```text | … state | streaming sync_state | quorum ``` #### 7.16.5 Réplication synchrone : cohérence des lectures (optionnel) - Exécuter la commande sur le premier secondaire ```sql SELECT pg_wal_replay_pause(); ``` synchrone instance2. - Ajouter des données sur le principal et contrôler leur présence sur le secondaire. Que constatez-vous? ```bash $ psql -p 5433 -c "SELECT pg_wal_replay_pause()" ``` ```bash $ psql -p 5432 -d b1 ``` ```sql INSERT INTO t4 VALUES (1); ``` ```sql SELECT * FROM t4; ``` c1 ---- 1 ```bash $ psql -d b1 -p 5433 -c "SELECT * FROM t4;" ``` id ---- ```text (0 rows) ``` ```bash $ psql -p 5434 -c "SELECT * FROM t4;" b1 ``` id ---- 1 ```bash $ psql -p 5435 -c "SELECT * FROM t4;" b1 ``` id ---- 1 La table est vide sur le premier secondaire synchrone mais elle est bien remplie sur les autres! - Est-ce que les instances sont bien synchrones (utiliser la vue pg_stat_replication )? - Relancer le rejeu et contrôler la présence des enregistrements sur les trois instances. ```bash $ psql -x -p 5432 -c "SELECT application_name, backend_start, state, sent_lsn, ``` write_lsn, flush_lsn, replay_lsn, sync_state FROM pg_stat_replication;" ```text -[ RECORD 1 ]----+-----------------------------application_name | instance3 ``` backend_start ```text | … state | streaming sent_lsn | 0/2D02A0C8 write_lsn | 0/2D02A0C8 flush_lsn | 0/2D02A0C8 replay_lsn | 0/2D02A0C8 sync_state | quorum -[ RECORD 2 ]----+-----------------------------application_name | instance2 ``` backend_start ```text | … state | streaming sent_lsn | 0/2D02A0C8 write_lsn | 0/2D02A0C8 flush_lsn | 0/2D02A0C8 replay_lsn | 0/2D028030 sync_state | quorum -[ RECORD 3 ]----+-----------------------------application_name | instance4 ``` backend_start ```text | … state | streaming sent_lsn | 0/2D02A0C8 write_lsn | 0/2D02A0C8 flush_lsn | 0/2D02A0C8 replay_lsn | 0/2D02A0C8 sync_state | quorum ``` Les serveurs secondaires sont bien en réplication synchrone avec la notion de quorum. On constate que tous ont bien reçu les enregistrements mais instance2 n’a pas rejoué les journaux. On réactive le rejeu sur le premier secondaire : ```bash $ psql -p 5433 -c "SELECT pg_wal_replay_resume()" ``` ```text pg_wal_replay_resume ----------------------- ``` ```text (1 row) ``` ```bash $ psql -x -p 5432 -c "SELECT application_name, backend_start, state, sent_lsn, ``` write_lsn, flush_lsn, replay_lsn, sync_state FROM pg_stat_replication;" ```text -[ RECORD 1 ]----+-----------------------------application_name | instance3 ``` backend_start ```text | … state | streaming sent_lsn | 0/2D02A1B0 write_lsn | 0/2D02A1B0 flush_lsn | 0/2D02A1B0 replay_lsn | 0/2D02A1B0 sync_state | quorum -[ RECORD 2 ]----+-----------------------------application_name | instance2 ``` backend_start ```text | … state | streaming sent_lsn | 0/2D02A1B0 write_lsn | 0/2D02A1B0 flush_lsn | 0/2D02A1B0 replay_lsn | 0/2D02A1B0 sync_state | quorum -[ RECORD 3 ]----+-----------------------------application_name | instance4 ``` backend_start ```text | … state | streaming sent_lsn | 0/2D02A1B0 write_lsn | 0/2D02A1B0 flush_lsn | 0/2D02A1B0 replay_lsn | 0/2D02A1B0 sync_state | quorum ``` Cette fois, instance2 a bien rejoué les journaux. Les enregistrements sont bien présents dans la table t4 : ```bash $ psql -p 5433 -c "SELECT * FROM t4;" b1 ``` id ---- 1 - Quel paramètre modifier pour obtenir les mêmes résultats sur les trois instances? Par défaut la réplication synchrone garantit qu’aucune transaction n’est perdue, mais elle ne s’assure pas que le secondaire synchrone a bien rejoué la transaction. Pour cela, il faut placer le paramètre ```text synchronous_commit à remote_apply sur le principal. ``` - Appliquer ce paramètre et effectuer la même opération (pause du rejeu puis insertion d’enregistrements sur le principal). Que se passe-t-il? Dans /var/lib/pgsql/17/instance1/postgresql.conf : ```text synchronous_commit = remote_apply ``` Faire un rechargement de la configuration du serveur principal : ```bash # systemctl reload instance1 ``` ```bash $ psql -p 5433 -c "SELECT pg_wal_replay_pause()" ``` ```text pg_wal_replay_pause ---------------------- ``` ```text (1 row) ``` ```bash $ psql -p 5434 -c "SELECT pg_wal_replay_pause()" ``` ```text pg_wal_replay_pause ---------------------- ``` ```text (1 row) ``` ```bash $ psql -d b1 -p 5435 -c "SELECT pg_wal_replay_pause()" ``` ```text pg_wal_replay_pause ---------------------- ``` ```text (1 row) ``` ```bash $ psql -d b1 -p 5432 -c "INSERT INTO t4 VALUES (2);" ``` Cette fois la requête est bloquée, il faut relancer le rejeu sur au moins deux secondaires pour qu’elle puisse s’effectuer puisque nous avons demandé à avoir deux secondaires synchrones : ```text synchronous_standby_names = 'ANY 2 (instance2,instance3,instance4)' ```