![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>Patroni<\/strong> est une solution de Haute disponibilit\u00e9 pour PostgreSQL \u00e9crite en Python, et d\u00e9crite d\u00e8s l\u2019introduction sur la page officielle du projet comme un \u00ab\u00a0template\u00a0\u00bb\u00a0:<\/p>\n We call Patroni a \u201ctemplate\u201d because it is far from being a one-size-fits-all or plug-and-play replication system. It will have its own caveats. Use wisely. There are many ways to run high availability with PostgreSQL; for a list, see the PostgreSQL Documentation.<\/em><\/strong><\/p>\n Il s\u2019agit d\u2019une contribution datant de 2016 par le d\u00e9partement IT de Zalando (Zalando\u2019s Patroni: a Template for High Availability PostgreSQL<\/a>)<\/p>\n Site du projet\u00a0:<\/u><\/p>\n Introduction \u2014 Patroni 2.0.1 documentation<\/a><\/p>\n Patroni ne se contente pas des fonctions de supervision et de switchover\/failover, il g\u00e8re aussi la synchronisation, le \u00ab\u00a0reload\u00a0\u00bb des configurations des instances PostgreSQL\u2026 Il est m\u00eame capable d\u2019ex\u00e9cuter un \u00ab\u00a0pg_rewind\u00a0\u00bb sur un primaire incident\u00e9 avant de le remettre en ligne dans le cluster. Le \u00ab\u00a0split brain\u00a0\u00bb est \u00e9vit\u00e9, idem un standby ne sera pas promu si un lag existe au-del\u00e0 d\u2019une quantit\u00e9 param\u00e9tr\u00e9e. Un standby peut ne jamais \u00eatre r\u00e9pliqu\u00e9 s\u2019il est pens\u00e9 seulement pour de l\u2019acc\u00e8s en lecture seule.<\/p>\n Architecture\u00a0:<\/strong><\/p>\n (R\u00e9f\u00e9rence : Cybertec, High availability with Patroni – Protect your database – CYBERTEC (cybertec-postgresql.com)<\/a>)<\/em><\/p>\n <\/p>\n <\/p>\n A des fins de POC, on installe l’ETCD, postgreSQL et patroni sur 3 conteneurs LXC instanci\u00e9es sur une seule machine virtuelle. (on pourrait faire de m\u00eame sur des conteneurs docker). La distribution Linux de ce POC est Ubuntu.<\/p>\n Sur chaque conteneur LXC\u00a0:<\/u><\/strong><\/p>\n <\/p>\n <\/p>\n <\/p>\n sur Ubuntu, la configuration de l’ETCD est dans \/etc\/default\/etcd<\/strong><\/p>\n <\/p>\n Configuration des 3 etcd avec pour valeurs de IP_HOTE:<\/strong><\/p>\n IP_HOTE:<\/strong><\/p>\n postgres01\u00a0: 10.234.76.28<\/strong><\/p>\n postgres02\u00a0: 10.234.76.45\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong><\/p>\n postgres03\u00a0: 10.234.76.13\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong><\/p>\n \u00a0<\/strong><\/p>\n \u00a0<\/strong><\/p>\n <\/p>\n D\u00e9marrage de l\u2019etcd sur les 3 n\u0153uds\u00a0:<\/u><\/strong><\/p>\n <\/p>\n Edition de la configuration \/etc\/patroni.yml :<\/strong><\/p>\n Configuration du service syst\u00e8me patroni \/etc\/systemd\/system\/patroni.service<\/p>\n <\/p>\n D\u00e9marrage de patroni sur un premier noeud:<\/u><\/strong><\/p>\n \u00a0<\/strong><\/p>\n \u00a0<\/strong><\/p>\n Mon instance PostgreSQL \u201cmy-pg-cluster\u201d a \u00e9t\u00e9 cr\u00e9\u00e9e\u00a0par patroni :<\/strong><\/p>\n <\/p>\n Log patroni <\/u><\/strong>avec promotion du seul serveur actif:<\/p>\n \u00a0<\/strong><\/p>\n \u00a0<\/strong><\/p>\n <\/p>\n Parmi les actions inh\u00e9rentes \u00e0 la cr\u00e9ation de mon instance PostgreSQL par patroni, nous remarquerons que le fichier de configuration postgresql.conf automatiquement g\u00e9n\u00e9r\u00e9 par patroni<\/strong> est non \u00e9ditable car il sera maintenu par patroni :<\/p>\n \u00a0<\/strong><\/p>\n \u00a0<\/strong><\/p>\n \u00a0<\/strong><\/p>\n Cela a un impact \u00e9vident sur la maintenance du param\u00e9trage (proc\u00e9dures \u00e0 adapter). La configuration exhaustive est conserv\u00e9e dans \u00ab\u00a0\/var\/lib\/postgresql\/12\/data\/postgresql.base.conf<\/strong>\u00a0\u00bb, fichier qui est inclus au d\u00e9marrage par clause \u00ab\u00a0include ‘postgresql.base.conf’<\/strong> \u00bb. Les param\u00e8tres contr\u00f4l\u00e9s par patroni le sont avec patronictl.<\/strong><\/p>\n \u00a0<\/u><\/strong><\/p>\n Je d\u00e9marre patroni sur les 2 autres serveurs\u00a0:<\/u><\/strong><\/p>\n \u00a0<\/strong><\/p>\n \u00a0<\/strong>On voit bien dans les processus la mise en place progressive du premier standby\u00a0:<\/u><\/p>\n \u00a0<\/strong><\/u><\/p>\n <\/p>\n La trace de patroni est claire en indiquant que le second membre suit le leader d\u00e9j\u00e0 \u00e9tabli :<\/p>\n \u00a0<\/strong><\/p>\n <\/p>\n Configuration recovery.conf :<\/strong><\/p>\n <\/p>\n D\u00e9marrage de patroni sur le 3\u00e8me noeud postgres03<\/strong> :<\/u><\/p>\n <\/p>\n <\/p>\n Arr\u00eats successifs, tout d\u2019abord les 2 replica \/ standby :<\/u><\/p>\n \u00a0<\/u><\/p>\n \u00a0<\/strong><\/p>\n Apr\u00e8s red\u00e9marrage des machines et des etcd, on red\u00e9marre patroni en premier sur postgres03. Ceci a pour effet de d\u00e9signer postgres03 en \u00ab\u00a0leader\u00a0\u00bb<\/u> et promouvoir postgres en primaire (avec passage \u00e0 la timeline num\u00e9ro 3)\u00a0:<\/strong><\/p>\n <\/p>\n \u00a0<\/strong><\/p>\n Malgr\u00e9 des erreurs au d\u00e9marrage, patroni sur postgres02 finit par raccrocher le flux sur la timeline 3\u00a0:<\/strong><\/p>\n <\/p>\n On proc\u00e8de \u00e0 un arr\u00eat brutal de l\u2019instance sur postgres01 par interruption de son postmaster\u00a0:<\/p>\n <\/p>\n On constate le red\u00e9marrage imm\u00e9diat de l\u2019instance\u00a0:<\/strong><\/p>\n <\/p>\n Le log patroni est clair sur l\u2019encha\u00eenement des \u00e9v\u00e9nements.<\/u><\/strong> Apr\u00e8s d\u00e9tection de l\u2019arr\u00eat de PostgreSQL, la v\u00e9rification de l\u2019\u00e9tat de l\u2019instance est faite avec pg_controldata, puis avant de la red\u00e9marrer, le verrou partag\u00e9 est aussi v\u00e9rifi\u00e9. Il est d\u00e9tenu par patroni03<\/strong> alors patroni01 est red\u00e9marr\u00e9 au m\u00eame \u00e9tat que pr\u00e9c\u00e9demment, en secondary. Evidemment, nous restons sur la timeline 3, aucune promotion n\u2019ayant \u00e9t\u00e9 n\u00e9cessaire.<\/strong><\/p>\n <\/p>\n <\/p>\n M\u00eame arr\u00eat brutal, cette fois sur l\u2019instance patroni03 actuelle leader (\u00ab lock owner \u00bb).<\/p>\n On constate l\u00e0 aussi un red\u00e9marrage r\u00e9tablissant le service toujours avec patroni03 en instance primary mais avec changement de timeline, passant \u00e0 la num\u00e9ro 4. Pass\u00e9e la v\u00e9rification pg_controldata que le statut de l\u2019instance \u00ab\u00a0in production\u00a0\u00bb est erron\u00e9e, celle-ci est red\u00e9marr\u00e9e pour ex\u00e9cuter un \u00ab\u00a0crash recovery\u00a0\u00bb puis de nouveau stopp\u00e9e correctement pour \u00eatre au statut \u00ab\u00a0shut down\u00a0\u00bb. <\/strong><\/p>\n \u00a0<\/strong>Poss\u00e9dant toujours le \u00ab\u00a0leader lock\u00a0\u00bb, patroni03 fait une auto-promotion de son instance PostgreSQL passant ainsi \u00e0 la timeline num\u00e9ro 4, ceci comme pour chaque red\u00e9marrage et sans lien avec l\u2019incident<\/u>.\u00a0 <\/strong><\/p>\n \u00a0<\/strong><\/p>\n <\/p>\n Log patroni :<\/strong><\/p>\n <\/p>\n La trace des 2 autres instances patroni 01 et 02 ne montre aucune incidence et aucun changement sur les instances standby (si ce n\u2019est le changement de timeline).<\/strong><\/p>\n <\/p>\n <\/p>\n \u00a0<\/strong><\/p>\n Le fichier postgresql.conf est non \u00e9ditable dans le contexte patroni car maintenu automatiquement par patroni. On a la main sur le fichier postgresql.base.conf qui est inclu au d\u00e9marrage.<\/strong><\/p>\n Sur les 3 instances, on active logging_collector=on<\/strong> en laissant le param\u00e9trage log_% \u00e0 ses valeurs par d\u00e9faut\u00a0:<\/p>\n Au red\u00e9marrage (<\/strong>systemctl start patroni<\/u><\/strong>), on voit le dossier $PGDATA\/log cr\u00e9\u00e9 et un premier fichier log\u00a0g\u00e9n\u00e9r\u00e9:<\/strong><\/p>\n root@postgres03:\/var\/lib\/postgresql\/12\/data# ls -l \/var\/lib\/postgresql\/12\/data\/log<\/p>\n total 2<\/p>\n -rw——- 1 postgres postgres 1110 Feb\u00a0 1 13:55 postgresql-2021-02-01_135506.log<\/p>\n <\/p>\n <\/p>\n <\/p>\n On stoppe le serveur postgres03, ce qui aura pour effet de stopper les 2 composants etcd et patroni+PostgreSQL\u00a0:<\/p>\n On constate avec les processus de l\u2019instance sur postgres02 qu\u2019elle a \u00e9t\u00e9 promue et d\u2019apr\u00e8s la trace patroni, que l\u2019absence de r\u00e9ponse de patroni03 est relev\u00e9e, la pr\u00e9sence de patroni01 est \u00e9galement signifi\u00e9e.<\/p>\n Du coup, patroni02 se promeut leader (nouvelle timeline 6) et acqui\u00e8re le verrou en ce sens\u00a0:<\/strong><\/p>\n \u00a0<\/strong><\/p>\n Du c\u00f4t\u00e9 de patroni01, la m\u00eame d\u00e9tection a \u00e9t\u00e9 faite. Patroni01 \u00e9tait en concurrence pour obtenir le verrou (\u00ab\u00a0leader race\u00a0\u00bb)<\/u> et a \u00e9chou\u00e9\u00a0:<\/strong><\/p>\n <\/p>\n On en profite pour tester la commande de supervision patronictl\u00a0\u00ab\u00a0<\/strong>patronictl -c \/etc\/patroni.yml list\u00a0\u00bb:<\/strong><\/p>\n <\/p>\n Notre serveur patroni03, rest\u00e9 \u00e9teint, va \u00eatre red\u00e9marr\u00e9. Etant l\u2019ancien leader, son instance postgresql devra possiblement subir une op\u00e9ration \u00ab\u00a0pg_rewind\u00a0\u00bb avant d\u2019\u00eatre remis dans le cluster.<\/strong><\/p>\n <\/p>\n Peu de temps apr\u00e8s le red\u00e9marrage, patroni03<\/strong> est r\u00e9int\u00e9gr\u00e9 en \u00ab\u00a0replica\u00a0\u00bb sur la timeline (TL) num\u00e9ro 5\u00a0avant de passer sur la TL 6:<\/p>\n <\/p>\n <\/p>\n Le log de patroni03 met en \u00e9vidence le passage de patroni03 en secondary, mais pas d\u2019allusion \u00e0 une action de \u00ab\u00a0rewind\u00a0\u00bb du fait que l\u2019instance postgresql se sera arr\u00eat\u00e9e proprement et qu\u2019il n\u2019y aura pas eu de divergence par rapport au point de basculement sur patroni02 :<\/strong><\/p>\n <\/p>\n <\/p>\n <\/p>\n Patroni ayant le contr\u00f4le sur les membres du cluster et les instances PostgreSQL, beaucoup de t\u00e2ches de maintenance doivent passent par l\u2019utilitaire de contr\u00f4le \u00ab\u00a0patronictl<\/strong>\u00a0\u00bb. Ainsi des commandes postgresql usuelles telle qu\u2019un \u00ab\u00a0reload<\/strong>\u00a0\u00bb sont prises en charge par patroni.<\/p>\n Exemple de modification dynamique d\u2019un param\u00e8tre. <\/strong>Dans le fichier postgresql.base.conf des 3 serveurs, on modifie work_mem de 4MB par d\u00e9faut \u00e0 10MB\u00a0:<\/strong><\/p>\n <\/p>\n Avant rechargement, la valeur est bien \u00e0 4MB sur toutes les instances PostgreSQL\u00a0:<\/p>\n <\/p>\n On ex\u00e9cute un \u00ab\u00a0reload\u00a0\u00bb, mais seulement sur l\u2019instance \u00ab\u00a0master\u00a0\u00bb\u00a0:<\/strong><\/p>\n <\/p>\n <\/p>\n Dans le log postgresql de patroni02, le reload a bien \u00e9t\u00e9 re\u00e7u\u00a0:<\/p>\n <\/p>\n <\/p>\n Le changement est effectif sur patroni02 (leader):<\/p>\n <\/p>\n Alors qu\u2019il n\u2019en est rien sur patroni01\u00a0(replica)\u00a0:<\/p>\n <\/p>\n <\/p>\n Cette fois on refait un \u00ab\u00a0reload\u00a0\u00bb sur tous les membres\u00a0:<\/p>\n <\/p>\n Le changement est bien effectif sur patroni01 (replica)\u00a0:<\/p>\n <\/p>\n Patroni est une solution robuste pr\u00e9sentant un niveau \u00e9lev\u00e9 d’int\u00e9gration de PostgreSQL dans sa mise en place des instances sur les diff\u00e9rents membres du cluster et de la r\u00e9plication entre elles, ainsi que dans ses fonctions de supervision et de maintenance. Le fonctionnement avec un DCS (tel l’ETCD) garantit la haute disponibilit\u00e9 de service et \u00e9vite les situations de “split brain” avec plusieurs “leader\/master”.<\/p>\n Au contraire de Corosync\/Pacemaker, patroni ne g\u00e8re pas les instances PostgreSQL en “ressources” et ne n\u00e9cessite pas pour ce faire d’agent tel que PAF requis pour pacemaker ou d’un agent pour de l’\u00e9vincement de noeuds (fencing agent). L’int\u00e9grit\u00e9 du cluster est garantie par le DCS et la donn\u00e9e de leader en acquisition mutuellement exclusive par les membres du cluster.<\/p>\n Une contrepartie de cette non-gestion de ressources est l’absence de ressource pour une adresse IP virtuelle (VIP), comme cela est le cas avec Corosync\/Pacemaker. Il faut songer \u00e0 impl\u00e9menter une gestion de la VIP en point d’entr\u00e9e unique pour la connexion au cluster soit via un script s’ex\u00e9cutant apr\u00e8s un changement de r\u00f4le d’un membre et instanciant la VIP localement au serveur si celui-ci est leader ou au contraire supprimant cette adresse si le membre est pass\u00e9 de leader \u00e0 replica (section callbacks de la configuration Patroni); ou via une solution externalis\u00e9e de gestion des adresses IP virtuelles telle Netscaler ou F5 (pouvant interroger l’interface API REST des clusters patroni pour savoir qui est leader et modifier l’adresse VIP en cons\u00e9quence, adresse caract\u00e9ris\u00e9e par un nom d’adresse unique dans le domaine).<\/p>\n – Comparatif des gestionnaires de VIP dans un cluster Patroni : \u00e9pisode 1 (KEEPALIVED)<\/a> Patroni est une solution de Haute disponibilit\u00e9 pour PostgreSQL \u00e9crite en Python, et d\u00e9crite d\u00e8s l\u2019introduction sur la page officielle du projet comme un \u00ab\u00a0template\u00a0\u00bb\u00a0: We call Patroni a \u201ctemplate\u201d because it is far from being a one-size-fits-all or plug-and-play… Continuer la lecture 1. Description de la solution<\/h1>\n
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/02\/archi-patroni-300x206.png\")
<\/p>\n\n
2. Installation et initialisation progressive du cluster<\/h1>\n
\n
\r\n\r\nsudo sh -c 'echo "deb http:\/\/apt.postgresql.org\/pub\/repos\/apt $(lsb_release -cs)-pgdg main" &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; \/etc\/apt\/sources.list.d\/pgdg.list'\r\n\r\nwget --quiet -O - https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc | sudo apt-key add -\r\n\r\nsudo apt-get update\r\n\r\napt-get install postgresql-12 postgresql-client-12 postgresql-server-dev-12\r\n\r\n<\/pre>\n
\n
\r\n\r\napt install python3-pip python3-psycopg2\r\n\r\n(python3-dev libpq-dev d\u00e9j\u00e0 install\u00e9s)\r\n\r\npip3 install python-etcd\r\n\r\npip3 install patroni\r\n\r\n<\/pre>\n
\n
\r\napt-get install etcd -y\r\n<\/pre>\n
\r\n\r\nETCD_DATA_DIR="\/var\/lib\/etcd\/default"\/strong\r\n\r\nETCD_LISTEN_PEER_URLS : http:\/\/127.0.0.1:2380,http:\/\/[IP_HOTE]:2380\r\n\r\nETCD_LISTEN_CLIENT_URLS : http:\/\/127.0.0.1:2379,http:\/\/[IP_HOTE]:2379\r\n\r\nETCD_NAME : [NOM_HOTE]\r\n\r\nETCD_INITIAL_ADVERTISE_PEER_URLS : http:\/\/[IP_HOTE]:2380\r\n\r\nETCD_ADVERTISE_CLIENT_URLS : http:\/\/[IP_HOTE]:2379\r\n\r\nETCD_INITIAL_CLUSTER : [NOM_HOTE1]=http:\/\/[IP_HOTE1]:2380,[NOM_HOTE2]=http:\/\/[IP_HOTE2]:2380,[NOM_HOTE3]=http:\/\/[IP_HOTE3]:2380\r\n\r\nETCD_INITIAL_CLUSTER_TOKEN="pg-cluster"\r\n\r\nETCD_INITIAL_CLUSTER_STATE="new"\r\n\r\n<\/pre>\n
\r\n\r\n# systemctl start --now etcd\r\n\r\nroot@postgres03:~# ps -fe|grep etcd\r\n\r\netcd 12868 1 0 10:14 ? 00:00:00 \/usr\/bin\/etcd\r\n\r\nroot 12880 12793 0 10:14 ? 00:00:00 grep --color=auto etcd\r\n\r\nroot@postgres03:~#\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres01:~# more \/etc\/patroni.yml\r\n\r\nscope: my-pg-cluster\r\n\r\n#namespace: \/service\/\r\n\r\nname: patroni01\r\n\r\nrestapi:\r\n\r\nlisten: 10.234.76.28:8008\r\n\r\nconnect_address: 10.234.76.28:8008\r\n\r\nlog:\r\n\r\nlevel: INFO\r\n\r\ndir: \/var\/log\/patroni\/my-pg-cluster\r\n\r\netcd:\r\n\r\nhosts:\r\n\r\n- 10.234.76.28:2379\r\n\r\n- 10.234.76.45:2379\r\n\r\n- 10.234.76.13:2379\r\n\r\nbootstrap:\r\n\r\ndcs:\r\n\r\nttl: 30\r\n\r\nloop_wait: 10\r\n\r\nretry_timeout: 10\r\n\r\nmaximum_lag_on_failover: 1048576\r\n\r\npostgresql:\r\n\r\nuse_pg_rewind: true\r\n\r\nparameters:\r\n\r\nwal_level: replica\r\n\r\nhot_standby: "on"\r\n\r\nwal_keep_segment: 8\r\n\r\nmax_wal_senders: 5\r\n\r\ncheckpoint_timeout: 30\r\n\r\ninitdb: # Note: It needs to be a list (some options need values, others are switches)\r\n\r\n- encoding: UTF8\r\n\r\n- data-checksums\r\n\r\npg_hba: # Add following lines to pg_hba.conf after running 'initdb'\r\n\r\n- host replication replicator 10.234.76.0\/24 md5\r\n\r\n- host all all 0.0.0.0\/0 md5\r\n\r\nusers:\r\n\r\nadmin:\r\n\r\npassword: admin\r\n\r\noptions:\r\n\r\n- createrole\r\n\r\n- createdb\r\n\r\npostgresql:\r\n\r\nlisten: "*:5433"\r\n\r\nconnect_address: 10.234.76.28:5433\r\n\r\ndata_dir: \/var\/lib\/postgresql\/12\/data\r\n\r\nbin_dir: \/usr\/lib\/postgresql\/12\/bin\r\n\r\npgpass: \/tmp\/pgpass0\r\n\r\nauthentication:\r\n\r\nreplication:\r\n\r\nusername: replicator\r\n\r\npassword: capdata\r\n\r\nsuperuser:\r\n\r\nusername: postgres\r\n\r\npassword: capdata\r\n\r\nrewind: # Has no effect on postgres 10 and lower\r\n\r\nusername: rewind_user\r\n\r\npassword: capdata\r\n\r\nparameters:\r\n\r\nunix_socket_directories: '.'\r\n\r\ntags:\r\n\r\nnofailover: false\r\n\r\nnoloadbalance: false\r\n\r\nclonefrom: false\r\n\r\nnosync: false\r\n\r\n<\/pre>\n
\n
<\/pre>\n
\r\n\r\n# install -o postgres -g postgres -m 0750 -d \/var\/log\/patroni\/my-pg-cluster\r\n\r\n<\/pre>\n
\r\n\r\n[Unit]\r\n\r\nDescription=Patroni instance for high-availability of PostgreSQL\r\n\r\nAfter=syslog.target network.target\r\n\r\n[Service]\r\n\r\nType=simple\r\n\r\nUser=postgres\r\n\r\nGroup=postgres\r\n\r\nExecStart=\/usr\/local\/bin\/patroni \/etc\/patroni.yml\r\n\r\nKillMode=process\r\n\r\nTimeoutSec=30\r\n\r\nRestart=no\r\n\r\n[Install]\r\n\r\nWantedBy=multi-user.targ\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres01:~# systemctl start patroni\r\n\r\nroot@postgres01:~# systemctl status patroni\r\n\r\npatroni.service - Patroni instance for high-availability of PostgreSQL\r\n\r\nLoaded: loaded (\/etc\/systemd\/system\/patroni.service; disabled; vendor preset: enabled)\r\n\r\nActive: active (running) since Fri 2021-01-29 10:31:33 UTC; 4s ago\r\n\r\nMain PID: 1501 (patroni)\r\n\r\nTasks: 13 (limit: 2348)\r\n\r\nCGroup: \/system.slice\/patroni.service\r\n\r\n\u251c\u25001501 \/usr\/bin\/python3 \/usr\/local\/bin\/patroni \/etc\/patroni.yml\r\n\r\n\u251c\u25001537 \/usr\/lib\/postgresql\/12\/bin\/postgres -D \/var\/lib\/postgresql\/12\/data --config-file=\/var\/lib\/postgresql\/12\/data\/postgresql\r\n\r\n\u251c\u25001541 postgres: my-pg-cluster: checkpointer\r\n\r\n\u251c\u25001542 postgres: my-pg-cluster: background writer\r\n\r\n\u251c\u25001543 postgres: my-pg-cluster: walwriter\r\n\r\n\u251c\u25001544 postgres: my-pg-cluster: autovacuum launcher\r\n\r\n\u251c\u25001545 postgres: my-pg-cluster: stats collector\r\n\r\n\u251c\u25001546 postgres: my-pg-cluster: logical replication launcher\r\n\r\n\u2514\u25001549 postgres: my-pg-cluster: postgres postgres 127.0.0.1(42324) idle\r\n\r\nJan 29 10:31:36 postgres01 patroni[1501]: \/usr\/lib\/postgresql\/12\/bin\/pg_ctl -D \/var\/lib\/postgresql\/12\/data -l logfile start\r\n\r\nJan 29 10:31:36 postgres01 patroni[1501]: 2021-01-29 10:31:36.968 UTC [1537] LOG: starting PostgreSQL 12.5 (Ubuntu 12.5-1.pgdg18.04+1) on x\r\n\r\nJan 29 10:31:36 postgres01 patroni[1501]: 2021-01-29 10:31:36.972 UTC [1537] LOG: listening on IPv4 address "0.0.0.0", port 5433\r\n\r\nJan 29 10:31:36 postgres01 patroni[1501]: 2021-01-29 10:31:36.972 UTC [1537] LOG: listening on IPv6 address "::", port 5433\r\n\r\nJan 29 10:31:36 postgres01 patroni[1501]: 2021-01-29 10:31:36.974 UTC [1537] LOG: listening on Unix socket ".\/.s.PGSQL.5433"\r\n\r\nJan 29 10:31:37 postgres01 patroni[1501]: 2021-01-29 10:31:37.016 UTC [1539] LOG: database system was shut down at 2021-01-29 10:31:35 UTC\r\n\r\nJan 29 10:31:37 postgres01 patroni[1501]: 2021-01-29 10:31:37.024 UTC [1537] LOG: database system is ready to accept connections\r\n\r\nJan 29 10:31:37 postgres01 patroni[1501]: 2021-01-29 10:31:37.025 UTC [1540] FATAL: the database system is starting up\r\n\r\nJan 29 10:31:37 postgres01 patroni[1501]: localhost:5433 - rejecting connections\r\n\r\nJan 29 10:31:37 postgres01 patroni[1501]: localhost:5433 - accepting connections\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres01:~# ps -fu postgres\r\n\r\nUID PID PPID C STIME TTY TIME CMD\r\n\r\npostgres 289 1 0 08:15 00:00:00 \/usr\/lib\/postgresql\/12\/bin\/postgres -D \/var\/lib\/postgresql\/12\/main -c config_file=\/etc\/po\r\n\r\npostgres 304 289 0 08:15 00:00:00 postgres: 12\/main: checkpointer\r\n\r\npostgres 305 289 0 08:15 00:00:00 postgres: 12\/main: background writer\r\n\r\npostgres 306 289 0 08:15 00:00:00 postgres: 12\/main: walwriter\r\n\r\npostgres 307 289 0 08:15 00:00:00 postgres: 12\/main: autovacuum launcher\r\n\r\npostgres 308 289 0 08:15 00:00:00 postgres: 12\/main: stats collector\r\n\r\npostgres 309 289 0 08:15 00:00:00 postgres: 12\/main: logical replication launcher\r\n\r\npostgres 1501 1 0 10:31 00:00:00 \/usr\/bin\/python3 \/usr\/local\/bin\/patroni \/etc\/patroni.yml\r\n\r\npostgres 1537 1 0 10:31 00:00:00 \/usr\/lib\/postgresql\/12\/bin\/postgres -D \/var\/lib\/postgresql\/12\/data --config-file=\/var\/lib\r\n\r\npostgres 1541 1537 0 10:31 00:00:00 postgres: my-pg-cluster: checkpointer\r\n\r\npostgres 1542 1537 0 10:31 00:00:00 postgres: my-pg-cluster: background writer\r\n\r\npostgres 1543 1537 0 10:31 00:00:00 postgres: my-pg-cluster: walwriter\r\n\r\npostgres 1544 1537 0 10:31 00:00:00 postgres: my-pg-cluster: autovacuum launcher\r\n\r\npostgres 1545 1537 0 10:31 00:00:00 postgres: my-pg-cluster: stats collector\r\n\r\npostgres 1546 1537 0 10:31 00:00:00 postgres: my-pg-cluster: logical replication launcher\r\n\r\npostgres 1549 1537 0 10:31 00:00:00 postgres: my-pg-cluster: postgres postgres 127.0.0.1(42324) idle\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres01:~# tail -100 \/var\/log\/patroni\/my-pg-cluster\/patroni.log\r\n\r\n2021-01-29 10:31:34,687 INFO: Selected new etcd server http:\/\/10.234.76.28:2379\r\n\r\n2021-01-29 10:31:34,696 INFO: No PostgreSQL configuration items changed, nothing to reload.\r\n\r\n2021-01-29 10:31:34,710 INFO: Lock owner: None; I am patroni01\r\n\r\n2021-01-29 10:31:34,714 INFO: trying to bootstrap a new cluster\r\n\r\n2021-01-29 10:31:36,980 INFO: postmaster pid=1537\r\n\r\n2021-01-29 10:31:37,052 INFO: establishing a new patroni connection to the postgres cluster\r\n\r\n2021-01-29 10:31:37,062 INFO: running post_bootstrap\r\n\r\n2021-01-29 10:31:37,081 WARNING: Could not activate Linux watchdog device: "Can't open watchdog device: [Errno 2] No such file or directory: '\/dev\/watchdog'"\r\n\r\n2021-01-29 10:31:37,094 INFO: initialized a new cluster\r\n\r\n2021-01-29 10:31:47,082 INFO: Lock owner: patroni01; I am patroni01\r\n\r\n2021-01-29 10:31:47,088 INFO: no action. i am the leader with the lock\r\n\r\n2021-01-29 10:31:47,116 INFO: Lock owner: patroni01; I am patroni01\r\n\r\n2021-01-29 10:31:47,123 INFO: no action. i am the leader with the lock\r\n\r\n<\/pre>\n
\r\nroot@postgres01:~# more \/var\/lib\/postgresql\/12\/data\/postgresql.conf\r\n\r\n# Do not edit this file manually!\r\n\r\n# It will be overwritten by Patroni!\r\n\r\ninclude 'postgresql.base.conf'\r\n\r\ncheckpoint_timeout = '30'\r\n\r\ncluster_name = 'my-pg-cluster'\r\n\r\nhot_standby = 'on'\r\n\r\nlisten_addresses = '*'\r\n\r\nmax_connections = '100'\r\n\r\nmax_locks_per_transaction = '64'\r\n\r\nmax_prepared_transactions = '0'\r\n\r\nmax_replication_slots = '10'\r\n\r\nmax_wal_senders = '5'\r\n\r\nmax_worker_processes = '8'\r\n\r\nport = '5433'\r\n\r\ntrack_commit_timestamp = 'off'\r\n\r\nunix_socket_directories = '.'\r\n\r\nwal_keep_segments = '8'\r\n\r\nwal_level = 'replica'\r\n\r\nwal_log_hints = 'on'\r\n\r\nhba_file = '\/var\/lib\/postgresql\/12\/data\/pg_hba.conf'\r\n\r\nident_file = '\/var\/lib\/postgresql\/12\/data\/pg_ident.conf'\r\n\r\n# recovery.conf\r\n\r\nrecovery_target = ''\r\n\r\nrecovery_target_lsn = ''\r\n\r\nrecovery_target_name = ''\r\n\r\nrecovery_target_time = ''\r\n\r\nrecovery_target_timeline = 'latest'\r\n\r\nrecovery_target_xid = ''\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres02:~# systemctl start patroni\r\n\r\nroot@postgres02:~# systemctl status patroni\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres02:~# ps -fu postgres\r\n\r\n\u2026\r\n\r\npostgres 1571 1 0 11:19 ? 00:00:00 \/usr\/bin\/python3 \/usr\/local\/bin\/patroni \/etc\/patroni.yml\r\n\r\nroot@postgres02:~# ps -fu postgres\r\n\r\nUID PID PPID C STIME TTY TIME CMD\r\n\r\n\u2026\r\n\r\npostgres 1571 1 0 11:19 ? 00:00:00 \/usr\/bin\/python3 \/usr\/local\/bin\/patroni \/etc\/patroni.yml\r\n\r\npostgres 1590 1 0 11:19 ? 00:00:00 \/usr\/lib\/postgresql\/12\/bin\/postgres -D \/var\/lib\/postgresql\/12\/data --config-file=\/var\/lib\/postgresql\/1\r\n\r\npostgres 1592 1590 0 11:19 ? 00:00:00 postgres: my-pg-cluster: lt;strong gt;startup waiting for 000000020000000000000003 lt;\/strong gt;\r\n\r\npostgres 1593 1590 0 11:19 ? 00:00:00 postgres: my-pg-cluster: checkpointer\r\n\r\npostgres 1594 1590 0 11:19 ? 00:00:00 postgres: my-pg-cluster: background writer\r\n\r\npostgres 1595 1590 0 11:19 ? 00:00:00 postgres: my-pg-cluster: stats collector\r\n\r\npostgres 1603 1590 0 11:19 ? 00:00:00 postgres: my-pg-cluster: postgres postgres 127.0.0.1(42670) idle\r\n\r\n&amp;amp;amp;amp;amp;amp;amp;nbsp;\r\n\r\nroot@postgres02:~# ps -fu postgres\r\n\r\nUID PID PPID C STIME TTY TIME CMD\r\n\r\n\u2026\r\n\r\npostgres 1571 1 0 11:19 ? 00:00:00 \/usr\/bin\/python3 \/usr\/local\/bin\/patroni \/etc\/patroni.yml\r\n\r\npostgres 1590 1 0 11:19 ? 00:00:00 \/usr\/lib\/postgresql\/12\/bin\/postgres -D \/var\/lib\/postgresql\/12\/data --config-file=\/var\/lib\/postgresql\/1\r\n\r\npostgres 1592 1590 0 11:19 ? 00:00:00 postgres: my-pg-cluster: startup recovering 000000020000000000000003\r\n\r\npostgres 1593 1590 0 11:19 ? 00:00:00 postgres: my-pg-cluster: checkpointer\r\n\r\npostgres 1594 1590 0 11:19 ? 00:00:00 postgres: my-pg-cluster: background writer\r\n\r\npostgres 1595 1590 0 11:19 ? 00:00:00 postgres: my-pg-cluster: stats collector\r\n\r\npostgres 1603 1590 0 11:19 ? 00:00:00 postgres: my-pg-cluster: postgres postgres 127.0.0.1(42670) idle\r\n\r\npostgres 1608 1590 0 11:19 ? 00:00:00 postgres: my-pg-cluster: walreceiver streaming 0\/3000148\r\n\r\nroot@postgres02:~#\r\n\r\n<\/pre>\n
\r\nroot@postgres02:~# vi \/var\/log\/patroni\/my-pg-cluster\/patroni.log\r\n\r\n2021-01-29 11:19:05,551 INFO: Selected new etcd server http:\/\/10.234.76.28:2379\r\n\r\n2021-01-29 11:19:05,559 INFO: No PostgreSQL configuration items changed, nothing to reload.\r\n\r\n2021-01-29 11:19:05,568 INFO: Lock owner: patroni01; I am patroni02\r\n\r\n2021-01-29 11:19:05,570 INFO: trying to bootstrap from leader 'patroni01'\r\n\r\n2021-01-29 11:19:06,861 INFO: replica has been created using basebackup\r\n\r\n2021-01-29 11:19:06,862 INFO: bootstrapped from leader 'patroni01'\r\n\r\n2021-01-29 11:19:06,875 WARNING: Removing unexpected parameter=wal_keep_segment value=8 from the config\r\n\r\n2021-01-29 11:19:07,160 INFO: postmaster pid=1590\r\n\r\n2021-01-29 11:19:08,219 INFO: Lock owner: patroni01; I am patroni02\r\n\r\n2021-01-29 11:19:08,219 INFO: does not have lock\r\n\r\n2021-01-29 11:19:08,219 INFO: establishing a new patroni connection to the postgres cluster\r\n\r\n2021-01-29 11:19:08,268 INFO: no action. i am a secondary and i am following a leader\r\n\r\n2021-01-29 11:19:15,183 INFO: Lock owner: patroni01; I am patroni02\r\n\r\n2021-01-29 11:19:15,183 INFO: does not have lock\r\n\r\n2021-01-29 11:19:15,186 INFO: no action. i am a secondary and i am following a leader\r\n\r\n2021-01-29 11:19:25,183 INFO: Lock owner: patroni01; I am patroni02\r\n\r\n2021-01-29 11:19:25,183 INFO: does not have lock\r\n\r\n2021-01-29 11:19:25,186 INFO: no action. i am a secondary and i am following a leader\r\n\r\n2021-01-29 11:19:35,183 INFO: Lock owner: patroni01; I am patroni02\r\n\r\n2021-01-29 11:19:35,183 INFO: does not have lock\r\n\r\n<\/pre>\n
\nLa configuration de postgresql sur postgres02 met en \u00e9vidence le param\u00e9trage sp\u00e9cifique dans le fichier de configuration patroni pour que le standby se connecte au primary en reprenant les \u00e9l\u00e9ments de configuration choisis tel l\u2019utilisateur \u00ab <\/span>replicator<\/strong>\u00a0\u00bb ou le fichier mot de passe \u00ab\u00a0<\/span>\/tmp\/pgpass0<\/strong>\u00a0\u00bb. L\u2019adresse IP du host \u00ab\u00a0<\/span>primary<\/strong>\u00a0\u00bb auquel se connecter est bien celle de postgres01\u00a0(10.234.76.13):<\/span><\/p>\n<\/pre>\n
\r\n\r\nroot@postgres02:~# more \/var\/lib\/postgresql\/12\/data\/postgresql.conf\r\n\r\n# Do not edit this file manually!\r\n\r\n# It will be overwritten by Patroni!\r\n\r\ninclude 'postgresql.base.conf'\r\n\r\n\r\n\r\ncheckpoint_timeout = '30'\r\n\r\ncluster_name = 'my-pg-cluster'\r\n\r\nhot_standby = 'on'\r\n\r\nlisten_addresses = '*'\r\n\r\nmax_connections = '100'\r\n\r\nmax_locks_per_transaction = '64'\r\n\r\nmax_prepared_transactions = '0'\r\n\r\nmax_replication_slots = '10'\r\n\r\nmax_wal_senders = '5'\r\n\r\nmax_worker_processes = '8'\r\n\r\nport = '5433'\r\n\r\ntrack_commit_timestamp = 'off'\r\n\r\nunix_socket_directories = '.'\r\n\r\nwal_keep_segments = '8'\r\n\r\nwal_level = 'replica'\r\n\r\nwal_log_hints = 'on'\r\n\r\nhba_file = '\/var\/lib\/postgresql\/12\/data\/pg_hba.conf'\r\n\r\nident_file = '\/var\/lib\/postgresql\/12\/data\/pg_ident.conf'\r\n\r\n<\/pre>\n
\r\n\r\nprimary_conninfo = 'user=replicator passfile=\/tmp\/pgpass0 host=10.234.76.13 port=5433 sslmode=prefer application_name=patroni02 gssencmode=prefer\r\n\r\nprimary_slot_name = 'patroni02'\r\n\r\nrecovery_target = ''\r\n\r\nrecovery_target_lsn = ''\r\n\r\nrecovery_target_name = ''\r\n\r\nrecovery_target_time = ''\r\n\r\nrecovery_target_timeline = 'latest'\r\n\r\nrecovery_target_xid = ''\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres03:~# systemctl start patroni\r\n\r\nroot@postgres03:~# systemctl status patroni\r\n\r\n...\r\npatroni.service - Patroni instance for high-availability of PostgreSQL\r\nLoaded: loaded (\/etc\/systemd\/system\/patroni.service; disabled; vendor preset: enabled)\r\n\r\nActive: active (running) since Fri 2021-01-29 11:30:35 UTC; 57s ago\r\n\r\nMain PID: 1579 (patroni)\r\n\r\nTasks: 12 (limit: 2348)\r\n\r\nCGroup: \/system.slice\/patroni.service\r\n\r\n\u251c\u25001579 \/usr\/bin\/python3 \/usr\/local\/bin\/patroni \/etc\/patroni.yml\r\n\r\n\u251c\u25001599 \/usr\/lib\/postgresql\/12\/bin\/postgres -D \/var\/lib\/postgresql\/12\/data --config-file=\/var\/lib\/postgresql\/12\/data\/postgresql.conf --liste\r\n\r\n\u251c\u25001602 postgres: my-pg-cluster: startup recovering 000000020000000000000007\r\n\r\n\u251c\u25001603 postgres: my-pg-cluster: checkpointer\r\n\r\n\u251c\u25001604 postgres: my-pg-cluster: background writer\r\n\r\n\u251c\u25001605 postgres: my-pg-cluster: stats collector\r\n\r\n\u251c\u25001614 postgres: my-pg-cluster: postgres postgres 127.0.0.1(42770) idle\r\n\r\n\u2514\u25001620 postgres: my-pg-cluster: walreceiver streaming 0\/7000148\r\n\r\n<\/pre>\n
3<\/a>. Sc\u00e9narios d\u2019exploitation<\/h1>\n
<\/a>3.1.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Red\u00e9marrage des instances patroni<\/h2>\n
\r\n\r\n# systemctl stop patroni\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres03:~# systemctl start patroni\r\n\r\nroot@postgres03:~# systemctl status patroni\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres02:~# systemctl status patroni\r\n\r\npatroni.service - Patroni instance for high-availability of PostgreSQL\r\nLoaded: loaded (\/etc\/systemd\/system\/patroni.service; disabled; vendor preset: enabled)\r\n\r\nActive: active (running) since Mon 2021-02-01 08:31:29 UTC; 18s ago\r\n\r\nMain PID: 1187 (patroni)\r\n\r\nTasks: 12 (limit: 2348)\r\n\r\nCGroup: \/system.slice\/patroni.service\r\n\r\n\u251c\u25001187 \/usr\/bin\/python3 \/usr\/local\/bin\/patroni \/etc\/patroni.yml\r\n\r\n\u251c\u25001210 \/usr\/lib\/postgresql\/12\/bin\/postgres -D \/var\/lib\/postgresql\/12\/data --config-file=\/var\/lib\/postgresql\/12\/data\/postgresql.conf --liste\r\n\r\n\u251c\u25001212 postgres: my-pg-cluster: startup recovering 000000030000000000000008\r\n\r\n\u251c\u25001213 postgres: my-pg-cluster: checkpointer\r\n\r\n\u251c\u25001214 postgres: my-pg-cluster: background writer\r\n\r\n\u251c\u25001215 postgres: my-pg-cluster: stats collector\r\n\r\n\u251c\u25001224 postgres: my-pg-cluster: postgres postgres 127.0.0.1(56750) idle\r\n\r\n\u2514\u25001234 postgres: my-pg-cluster: walreceiver streaming 0\/8000310\r\n\r\nFeb 01 08:31:31 postgres02 patroni[1187]: 2021-02-01 08:31:31.244 UTC [1212] LOG: invalid record length at 0\/8000148: wanted 24, got 0\r\n\r\nFeb 01 08:31:31 postgres02 patroni[1187]: 2021-02-01 08:31:31.256 UTC [1216] LOG: fetching timeline history file for timeline 3 from primary server\r\n\r\nFeb 01 08:31:31 postgres02 patroni[1187]: 2021-02-01 08:31:31.260 UTC [1216] FATAL: could not start WAL streaming: ERROR: replication slot "patroni02"\r\n\r\nFeb 01 08:31:31 postgres02 patroni[1187]: 2021-02-01 08:31:31.262 UTC [1212] LOG: new target timeline is 3\r\n\r\nFeb 01 08:31:31 postgres02 patroni[1187]: 2021-02-01 08:31:31.269 UTC [1217] FATAL: could not start WAL streaming: ERROR: replication slot "patroni02"\r\n\r\nFeb 01 08:31:31 postgres02 patroni[1187]: 2021-02-01 08:31:31.275 UTC [1218] FATAL: could not start WAL streaming: ERROR: replication slot "patroni02"\r\n\r\nFeb 01 08:31:32 postgres02 patroni[1187]: localhost:5433 - accepting connections\r\n\r\nFeb 01 08:31:32 postgres02 patroni[1187]: localhost:5433 - accepting connections\r\n\r\nFeb 01 08:31:36 postgres02 patroni[1187]: 2021-02-01 08:31:36.278 UTC [1230] FATAL: could not start WAL streaming: ERROR: replication slot "patroni02"\r\n\r\nFeb 01 08:31:41 postgres02 patroni[1187]: 2021-02-01 08:31:41.283 UTC [1234] LOG: started streaming WAL from primary at 0\/8000000 on timeline 3\r\n\r\n<\/pre>\n
3<\/span>.2.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Incident sur un secondary<\/h2>\n
\r\nroot@postgres01:~# kill -9 1281\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres01:~# date\r\n\r\nMon Feb 1 10:37:58 UTC 2021\r\n\r\nroot@postgres01:~# systemctl status patroni\r\npatroni.service - Patroni instance for high-availability of PostgreSQL\r\n\r\nLoaded: loaded (\/etc\/systemd\/system\/patroni.service; disabled; vendor preset: enabled)\r\n\r\nActive: active (running) since Mon 2021-02-01 08:56:26 UTC; 1h 41min ago\r\n\r\nMain PID: 1249 (patroni)\r\n\r\nTasks: 12 (limit: 2348)\r\n\r\nCGroup: \/system.slice\/patroni.service\r\n\r\n\u251c\u25001249 \/usr\/bin\/python3 \/usr\/local\/bin\/patroni \/etc\/patroni.yml\r\n\r\n\u251c\u25001482 \/usr\/lib\/postgresql\/12\/bin\/postgres -D \/var\/lib\/postgresql\/12\/data --config-file=\/var\/lib\/postgresql\/12\/data\/postgresql.conf --liste\r\n\r\n\u251c\u25001484 postgres: my-pg-cluster: startup recovering 000000030000000000000009\r\n\r\n\u251c\u25001488 postgres: my-pg-cluster: checkpointer\r\n\r\n\u251c\u25001489 postgres: my-pg-cluster: background writer\r\n\r\n\u251c\u25001490 postgres: my-pg-cluster: stats collector\r\n\r\n\u251c\u25001491 postgres: my-pg-cluster: walreceiver\r\n\r\n\u2514\u25001494 postgres: my-pg-cluster: postgres postgres 127.0.0.1(57620) idle\r\n\r\nFeb 01 10:37:20 postgres01 patroni[1249]: localhost:5433 - rejecting connections\r\n\r\nFeb 01 10:37:20 postgres01 patroni[1249]: 2021-02-01 10:37:20.911 UTC [1487] FATAL: the database system is starting up\r\n\r\nFeb 01 10:37:20 postgres01 patroni[1249]: localhost:5433 - rejecting connections\r\n\r\nFeb 01 10:37:20 postgres01 patroni[1249]: 2021-02-01 10:37:20.925 UTC [1484] LOG:&amp;amp;amp;amp;amp;amp;amp;amp;nbsp; entering standby mode\r\n\r\nFeb 01 10:37:20 postgres01 patroni[1249]: 2021-02-01 10:37:20.928 UTC [1484] LOG:&amp;amp;amp;amp;amp;amp;amp;amp;nbsp; redo starts at 0\/9000060\r\n\r\nFeb 01 10:37:20 postgres01 patroni[1249]: 2021-02-01 10:37:20.929 UTC [1484] LOG:&amp;amp;amp;amp;amp;amp;amp;amp;nbsp; consistent recovery state reached at 0\/9000148\r\n\r\nFeb 01 10:37:20 postgres01 patroni[1249]: 2021-02-01 10:37:20.929 UTC [1482] LOG:&amp;amp;amp;amp;amp;amp;amp;amp;nbsp; database system is ready to accept read only connections\r\n\r\nFeb 01 10:37:20 postgres01 patroni[1249]: 2021-02-01 10:37:20.929 UTC [1484] LOG:&amp;amp;amp;amp;amp;amp;amp;amp;nbsp; invalid record length at 0\/9000148: wanted 24, got 0\r\n\r\nFeb 01 10:37:20 postgres01 patroni[1249]: 2021-02-01 10:37:20.936 UTC [1491] LOG:&amp;amp;amp;amp;amp;amp;amp;amp;nbsp; started streaming WAL from primary at 0\/9000000 on timeline 3\r\n\r\nFeb 01 10:37:21 postgres01 patroni[1249]: localhost:5433 - accepting connections\r\n\r\n<\/pre>\n
\r\n\r\n2021-02-01 10:37:10,617 INFO: no action. i am a secondary and i am following a leader\r\n\r\n2021-02-01 10:37:20,611 WARNING: Postgresql is not running.\r\n\r\n2021-02-01 10:37:20,611 INFO: Lock owner: patroni03; I am patroni01\r\n\r\n2021-02-01 10:37:20,619 INFO: pg_controldata:\r\n\r\npg_control version number: 1201\r\n\r\nCatalog version number: 201909212\r\n\r\nDatabase system identifier: 6923127770418349556\r\n\r\nDatabase cluster state: in archive recovery\r\n\r\n\u2026\r\n2021-02-01 10:37:20,635 INFO: Lock owner: patroni03; I am patroni01\r\n\r\n2021-02-01 10:37:20,637 INFO: starting as a secondary\r\n\r\n2021-02-01 10:37:20,638 INFO: closed patroni connection to the postgresql cluster\r\n\r\n<\/pre>\n
3<\/span>.3.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 incident sur le primary<\/h2>\n
\r\n\r\nroot@postgres03:~# systemctl status patroni\r\npatroni.service - Patroni instance for high-availability of PostgreSQL\r\nLoaded: loaded (\/etc\/systemd\/system\/patroni.service; disabled; vendor preset: enabled)\r\n\r\nActive: active (running) since Mon 2021-02-01 08:10:56 UTC; 3h 16min ago\r\n\r\nMain PID: 1137 (patroni)\r\n\r\nTasks: 15 (limit: 2348)\r\n\r\nCGroup: \/system.slice\/patroni.service\r\n\r\n\u251c\u25001137 \/usr\/bin\/python3 \/usr\/local\/bin\/patroni \/etc\/patroni.yml\r\n\r\n\u251c\u25001863 \/usr\/lib\/postgresql\/12\/bin\/postgres -D \/var\/lib\/postgresql\/12\/data --config-file=\/var\/lib\/postgresql\/12\/data\/postgresql.conf --liste\r\n\r\n\u251c\u25001869 postgres: my-pg-cluster: checkpointer\r\n\r\n\u251c\u25001870 postgres: my-pg-cluster: background writer\r\n\r\n\u251c\u25001871 postgres: my-pg-cluster: stats collector\r\n\r\n\u251c\u25001873 postgres: my-pg-cluster: postgres postgres 127.0.0.1(57950) idle\r\n\r\n\u251c\u25001878 postgres: my-pg-cluster: walwriter\r\n\r\n\u251c\u25001879 postgres: my-pg-cluster: autovacuum launcher\r\n\r\n\u251c\u25001880 postgres: my-pg-cluster: logical replication launcher\r\n\r\n\u251c\u25001885 postgres: my-pg-cluster: walsender replicator 10.234.76.28(45066) streaming 0\/B000268\r\n\r\n\u2514\u25001886 postgres: my-pg-cluster: walsender replicator 10.234.76.45(44232) streaming 0\/B000268\r\n...\r\nFeb 01 11:23:51 postgres03 patroni[1137]: 2021-02-01 11:23:51.281 UTC [1866] LOG: received promote request\r\n\r\nFeb 01 11:23:51 postgres03 patroni[1137]: 2021-02-01 11:23:51.282 UTC [1866] LOG: redo is not required\r\n\r\nFeb 01 11:23:51 postgres03 patroni[1137]: server promoting\r\n\r\nFeb 01 11:23:51 postgres03 patroni[1137]: 2021-02-01 11:23:51.285 UTC [1866] LOG: selected new timeline ID: 4\r\n\r\nFeb 01 11:23:51 postgres03 patroni[1137]: 2021-02-01 11:23:51.400 UTC [1866] LOG: archive recovery complete\r\n\r\nFeb 01 11:23:51 postgres03 patroni[1137]: 2021-02-01 11:23:51.412 UTC [1863] LOG: database system is ready to accept connections\r\n\r\n<\/pre>\n
\r\n\r\n2021-02-01 11:23:40,597 INFO: Lock owner: patroni03; I am patroni03\r\n\r\n2021-02-01 11:23:40,601 INFO: no action. i am the leader with the lock\r\n\r\n2021-02-01 11:23:50,597 WARNING: Postgresql is not running.\r\n\r\n2021-02-01 11:23:50,597 INFO: Lock owner: patroni03; I am patroni03\r\n\r\n2021-02-01 11:23:50,604 INFO: pg_controldata:\r\n\r\npg_control version number: 1201\r\n\r\nCatalog version number: 201909212\r\n\r\nDatabase system identifier: 6923127770418349556\r\n\r\nDatabase cluster state: in production\r\n\r\npg_control last modified: Mon Feb 1 11:18:39 2021\r\n\r\nLatest checkpoint location: 0\/A000098\r\n\r\nLatest checkpoint's REDO location: 0\/A000060\r\n\r\nLatest checkpoint's REDO WAL file: 00000003000000000000000A\r\n\r\n\u2026\r\n\r\n2021-02-01 11:23:50,874 INFO: Lock owner: patroni03; I am patroni03\r\n\r\n2021-02-01 11:23:50,874 INFO: Lock owner: patroni03; I am patroni03\r\n\r\n2021-02-01 11:23:50,885 INFO: starting as readonly because i had the session lock\r\n\r\n2021-02-01 11:23:50,886 INFO: closed patroni connection to the postgresql cluster\r\n\r\n2021-02-01 11:23:50,896 WARNING: Removing unexpected parameter=wal_keep_segment value=8 from the config\r\n\r\n2021-02-01 11:23:51,163 INFO: postmaster pid=1863\r\n\r\n2021-02-01 11:23:51,219 INFO: Lock owner: patroni03; I am patroni03\r\n\r\n2021-02-01 11:23:51,219 INFO: establishing a new patroni connection to the postgres cluster\r\n\r\n2021-02-01 11:23:51,260 INFO: promoted self to leader because i had the session lock\r\n\r\n2021-02-01 11:23:51,283 INFO: cleared rewind state after becoming the leader\r\n\r\n2021-02-01 11:23:52,304 INFO: Lock owner: patroni03; I am patroni03\r\n\r\n2021-02-01 11:23:52,337 INFO: no action. i am the leader with the lock\r\n\r\n2021-02-01 11:23:52,339 INFO: Lock owner: patroni03; I am patroni03\r\n\r\n2021-02-01 11:23:52,344 INFO: no action. i am the leader with the lock\r\n\r\n<\/pre>\n
3<\/span>.4.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Modification de param\u00e8tre statique<\/h2>\n
\r\n\r\nroot@postgres03:\/var\/lib\/postgresql\/12\/data# grep logging_collector \/var\/lib\/postgresql\/12\/data\/postgresql.base.conf\r\n\r\n# requires logging_collector to be on.\r\n\r\n#logging_collector = off# Enable capturing of stderr and csvlog\r\n\r\nlogging_collector = on\r\n<\/pre>\n
3.5.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Arr\u00eat du serveur de l\u2019instance leader (et changement de leader)<\/h2>\n
\r\n\r\n2021-02-01 14:45:57,498 INFO: no action. i am a secondary and i am following a leader\r\n\r\n2021-02-01 14:46:07,494 INFO: Lock owner: patroni03; I am patroni02\r\n\r\n2021-02-01 14:46:07,494 INFO: does not have lock\r\n\r\n2021-02-01 14:46:07,498 INFO: no action. i am a secondary and i am following a leader\r\n\r\n2021-02-01 14:46:15,845 INFO: Got response from patroni01 http:\/\/10.234.76.28:8008\/patroni: {"state": "running", "postmaster_start_time": "2021-02-01 14:04:08.646 UTC", "role": "replica", "server_version": 120005, "cluster_unlocked": true, "xlog": {"received_location": 218104256, "replayed_location": 218104256, "replayed_timestamp": null, "paused": false}, "timeline": 5, "database_system_identifier": "6923127770418349556", "patroni": {"version": "2.0.1", "scope": "my-pg-cluster"}}\r\n\r\n2021-02-01 14:46:15,864 WARNING: Request failed to patroni03: GET http:\/\/10.234.76.13:8008\/patroni (HTTPConnectionPool(host='10.234.76.13', port=8008): Max retries exceeded with url: \/patroni (Caused by ProtocolError('Connection aborted.', ConnectionResetError(104, 'Connection reset by peer'))))\r\n\r\n2021-02-01 14:46:15,955 WARNING: Could not activate Linux watchdog device: "Can't open watchdog device: [Errno 2] No such file or directory: '\/dev\/watchdog'"\r\n\r\n2021-02-01 14:46:15,976 INFO: promoted self to leader by acquiring session lock\r\n\r\n2021-02-01 14:46:15,992 INFO: cleared rewind state after becoming the leader\r\n\r\n2021-02-01 14:46:17,043 INFO: Lock owner: patroni02; I am patroni02\r\n\r\n2021-02-01 14:46:17,101 INFO: no action. i am the leader with the lock\r\n\r\n2021-02-01 14:46:17,109 INFO: Lock owner: patroni02; I am patroni02\r\n\r\n2021-02-01 14:46:17,126 INFO: no action. i am the leader with the lock\r\n\r\n2021-02-01 14:46:27,109 INFO: Lock owner: patroni02; I am patroni02\r\n\r\n2021-02-01 14:46:27,113 INFO: no action. i am the leader with the lock\r\n\r\n2021-02-01 14:46:37,109 INFO: Lock owner: patroni02; I am patroni02\r\n\r\n2021-02-01 14:46:37,113 INFO: no action. i am the leader with the lock\r\n\r\n<\/pre>\n\r\n\r\n2021-02-01 14:45:57,499 INFO: no action. i am a secondary and i am following a leader\r\n\r\n2021-02-01 14:46:07,492 INFO: Lock owner: patroni03; I am patroni01\r\n\r\n2021-02-01 14:46:07,492 INFO: does not have lock\r\n\r\n2021-02-01 14:46:07,499 INFO: no action. i am a secondary and i am following a leader\r\n\r\n2021-02-01 14:46:15,852 INFO: Got response from patroni02 http:\/\/10.234.76.45:8008\/patroni: {"state": "running", "postmaster_start_time": "2021-02-01 14:03:51.153 UTC", "role": "replica", "server_version": 120005, "cluster_unlocked": true, "xlog": {"received_location": 218104256, "replayed_location": 218104256, "replayed_timestamp": null, "paused": false}, "timeline": 5, "database_system_identifier": "6923127770418349556", "patroni": {"version": "2.0.1", "scope": "my-pg-cluster"}}\r\n\r\n2021-02-01 14:46:15,866 WARNING: Request failed to patroni03: GET http:\/\/10.234.76.13:8008\/patroni (HTTPConnectionPool(host='10.234.76.13', port=8008): Max retries exceeded with url: \/patroni (Caused by ProtocolError('Connection aborted.', ConnectionResetError(104, 'Connection reset by peer'))))\r\n\r\n2021-02-01 14:46:15,977 INFO: Could not take out TTL lock\r\n\r\n2021-02-01 14:46:16,048 INFO: following new leader after trying and failing to obtain lock\r\n\r\n2021-02-01 14:46:21,111 INFO: closed patroni connection to the postgresql cluster\r\n\r\n2021-02-01 14:46:21,115 WARNING: Removing unexpected parameter=wal_keep_segment value=8 from the config\r\n\r\n2021-02-01 14:46:21,351 INFO: postmaster pid=2027\r\n\r\n2021-02-01 14:46:21,418 INFO: Lock owner: patroni02; I am patroni01\r\n\r\n2021-02-01 14:46:21,419 INFO: does not have lock\r\n\r\n2021-02-01 14:46:21,419 INFO: establishing a new patroni connection to the postgres cluster\r\n\r\n2021-02-01 14:46:21,432 INFO: Local timeline=6 lsn=0\/D0001C0\r\n\r\n2021-02-01 14:46:21,436 INFO: master_timeline=6\r\n\r\n2021-02-01 14:46:21,452 INFO: no action. i am a secondary and i am following a leader\r\n\r\n2021-02-01 14:46:27,123 INFO: Lock owner: patroni02; I am patroni01\r\n\r\n<\/pre>\n\r\n\r\nroot@postgres01:~# patronictl -c \/etc\/patroni.yml list\r\n\r\n+ Cluster: my-pg-cluster (6923127770418349556) -----+----+-----------+\r\n\r\n| Member | Host | Role | State | TL | Lag in MB |\r\n\r\n+-----------+-------------------+---------+---------+----+-----------+\r\n\r\n| patroni01 | 10.234.76.28:5433 | Replica | running | 6 | 0 |\r\n\r\n| patroni02 | 10.234.76.45:5433 | Leader | running | 6 | |\r\n\r\n+-----------+-------------------+---------+---------+----+-----------+\r\n\r\n<\/pre>\n
\r\n\r\nroot@ip-192-1-1-7:~# lxc start postgres03\r\n\r\nroot@postgres03:~# systemctl start etcd\r\n\r\nroot@postgres03:~# systemctl start patroni\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres01:~# patronictl -c \/etc\/patroni.yml list\r\n\r\n+ Cluster: my-pg-cluster (6923127770418349556) -----+----+-----------+\r\n\r\n| Member | Host | Role | State | TL | Lag in MB |\r\n\r\n+-----------+-------------------+---------+---------+----+-----------+\r\n\r\n| patroni01 | 10.234.76.28:5433 | Replica | running | 6 | 0 |\r\n\r\n| patroni02 | 10.234.76.45:5433 | Leader | running | 6 | |\r\n\r\n| patroni03 | 10.234.76.13:5433 | Replica | running | 5 | 0 |\r\n\r\n+-----------+-------------------+---------+---------+----+-----------+\r\n\r\nroot@postgres01:~# patronictl -c \/etc\/patroni.yml list\r\n\r\n+ Cluster: my-pg-cluster (6923127770418349556) -----+----+-----------+\r\n\r\n| Member | Host | Role | State | TL | Lag in MB |\r\n\r\n+-----------+-------------------+---------+---------+----+-----------+\r\n\r\n| patroni01 | 10.234.76.28:5433 | Replica | running | 6 | 0 |\r\n\r\n| patroni02 | 10.234.76.45:5433 | Leader | running | 6 | |\r\n\r\n| patroni03 | 10.234.76.13:5433 | Replica | running | 6 | 0 |\r\n\r\n+-----------+-------------------+---------+---------+----+-----------+\r\n\r\n<\/pre>\n
\r\n\r\n2021-02-01 14:45:57,478 INFO: Lock owner: patroni03; I am patroni03\r\n\r\n2021-02-01 14:45:57,483 INFO: no action. i am the leader with the lock\r\n\r\n2021-02-01 14:46:07,478 INFO: Lock owner: patroni03; I am patroni03\r\n\r\n2021-02-01 14:46:07,482 INFO: no action i am the leader with the lock\r\n\r\n2021-02-01 16:12:41,998 INFO: Selected new etcd server http:\/\/10.234.76.28:2379\r\n\r\n2021-02-01 16:12:42,022 INFO: No PostgreSQL configuration items changed, nothing to reload.\r\n\r\n2021-02-01 16:12:42,045 WARNING: Postgresql is not running.\r\n\r\n2021-02-01 16:12:42,045 INFO: Lock owner: patroni02; I am patroni03\r\n\r\n2021-02-01 16:12:42,049 INFO: pg_controldata:\r\n\r\npg_control version number: 1201\r\n\r\nCatalog version number: 201909212\r\n\r\nDatabase system identifier: 6923127770418349556\r\n\r\nDatabase cluster state: shut down\r\n\r\npg_control last modified: Mon Feb 1 14:46:15 2021\r\n\r\n\u2026\r\n\r\n2021-02-01 16:12:42,051 INFO: Lock owner: patroni02; I am patroni03\r\n\r\n2021-02-01 16:12:42,066 INFO: Local timeline=5 lsn=0\/D000148\r\n\r\n2021-02-01 16:12:42,103 INFO: master_timeline=6\r\n\r\n2021-02-01 16:12:42,116 INFO: master: history=2 0\/8000148 no recovery target specified 0\/B0000A0 no recovery target specified\r\n...\r\n2021-02-01 16:12:42,119 INFO: Lock owner: patroni02; I am patroni03\r\n\r\n2021-02-01 16:12:42,122 INFO: starting as a secondary\r\n\r\n2021-02-01 16:12:42,127 WARNING: Removing unexpected parameter=wal_keep_segment value=8 from the config\r\n\r\n2021-02-01 16:12:42,352 INFO: postmaster pid=494\r\n\r\n2021-02-01 16:12:43,465 INFO: Lock owner: patroni02; I am patroni03\r\n\r\n2021-02-01 16:12:43,465 INFO: does not have lock\r\n\r\n2021-02-01 16:12:43,465 INFO: establishing a new patroni connection to the postgres cluster\r\n\r\n2021-02-01 16:12:43,622 INFO: no action. i am a secondary and i am following a leader\r\n\r\n2021-02-01 16:12:47,136 INFO: Lock owner: patroni02; I am patroni03\r\n\r\n2021-02-01 16:12:47,136 INFO: does not have lock\r\n\r\n2021-02-01 16:12:47,146 INFO: no action. i am a secondary and i am following a leader\r\n\r\n<\/pre>\n
<\/a>3.6.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Maintenance<\/h2>\n
\r\n\r\nroot@postgres01:~# grep work_mem \/var\/lib\/postgresql\/12\/data\/postgresql.base.conf\r\n\r\n#work_mem = 4MB # min 64kB\r\n\r\nwork_mem = 10MB\r\n<\/pre>\n
\r\n\r\npostgres@postgres02:~$ psql -p 5432\r\n\r\npsql (12.5 (Ubuntu 12.5-1.pgdg18.04+1))\r\n\r\nType "help" for help.\r\n\r\npostgres=# show work_mem ;\r\n\r\nwork_mem\r\n\r\n----------\r\n\r\n4MB\r\n\r\n(1 row)\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres01:~# patronictl -c \/etc\/patroni.yml reload -r master my-pg-cluster\r\n\r\n+ Cluster: my-pg-cluster (6923127770418349556) -----+----+-----------+\r\n\r\n| Member | Host | Role | State | TL | Lag in MB |\r\n\r\n+-----------+-------------------+---------+---------+----+-----------+\r\n\r\n| patroni01 | 10.234.76.28:5433 | Replica | running | 7 | 0 |\r\n\r\n| patroni02 | 10.234.76.45:5433 | Leader | running | 7 | |\r\n\r\n| patroni03 | 10.234.76.13:5433 | Replica | running | 7 | 0 |\r\n\r\n+-----------+-------------------+---------+---------+----+-----------+\r\n\r\nAre you sure you want to reload members patroni02? [y\/N]: y\r\n\r\nReload request received for member patroni02 and will be processed within 10 seconds\r\n\r\n<\/pre>\n
\r\n\r\npostgres@postgres02:~\/12\/data\/log$ tail -2f postgresql-2021-02-05_102121.log\r\n\r\n2021-02-05 11:16:03.337 UTC [8313] LOG: received SIGHUP, reloading configuration files\r\n\r\n2021-02-05 11:16:03.340 UTC [8313] LOG: parameter "work_mem" changed to "10MB"\r\n\r\n<\/pre>\n
\r\n\r\npostgres@postgres02:~\/12\/data\/log$ psql -p 5433 -h localhost\r\n\r\npsql (12.5 (Ubuntu 12.5-1.pgdg18.04+1))\r\n\r\nType "help" for help.\r\n\r\npostgres=# show work_mem ;\r\n\r\nwork_mem\r\n\r\n----------\r\n\r\n10MB\r\n\r\n(1 row)\r\n\r\n<\/pre>\n
\r\n\r\npostgres@postgres01:~$ psql -p 5433 -h localhost\r\n\r\npsql (12.5 (Ubuntu 12.5-1.pgdg18.04+1))\r\n\r\nType "help" for help.\r\n\r\npostgres=# show work_mem;\r\n\r\nwork_mem\r\n\r\n----------\r\n\r\n4MB\r\n\r\n(1 row)\r\n\r\n<\/pre>\n
\r\n\r\nroot@postgres01:~# patronictl -c \/etc\/patroni.yml reload -r any my-pg-cluster\r\n\r\n+ Cluster: my-pg-cluster (6923127770418349556) -----+----+-----------+\r\n...\r\nAre you sure you want to reload members patroni01, patroni03, patroni02? [y\/N]: y\r\n\r\nReload request received for member patroni01 and will be processed within 10 seconds\r\n\r\nReload request received for member patroni03 and will be processed within 10 seconds\r\n\r\nReload request received for member patroni02 and will be processed within 10 seconds\r\n\r\n<\/pre>\n
\r\n\r\npostgres=# show work_mem;\r\n\r\nwork_mem\r\n\r\n----------\r\n\r\n10MB\r\n\r\n(1 row)\r\n\r\n<\/pre>\n
Conclusion<\/h1>\n
Pour aller plus loin avec Patroni:<\/h2>\n
\n– Comparatif des gestionnaires de VIP dans un cluster Patroni : \u00e9pisode 2 (VIP-MANAGER)<\/a> <\/p>\n<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"