A few days ago I had the interesting mission to build a two Node Patroni Postgres 9.6 Cluster using etcd in our openDB Appliance environment. Sounds easy, one leader, one secondary, both running etcd, that’s it. But that’s only the first impression.
\n<\/p>\n
So we start with two newly created OpenDB Appliance servers <\/a> and deploy the database home using the openDB Appliance. When we start the etcd and patroni services, everything looks fine. Both Members up and running, replication also worked fine. Let’s try a manual failover – this works as expected: Do another check, if opendb_stby is the leader now, looks perfect. As a next step, let’s try to stop the Patroni service on the leading node to force the automatic failover. Yeah, this also works as expected!<\/p>\n Now, let us try the “worst case” and shutdown the leading node. Using some search engines bring lot of nice ideas, e.g errors in configuration. But nothing helps…<\/p>\n There is one main failure: We need a third etcd host. An etcd cluster needs an extra node to agree on updates to the cluster state. Means: when one node failes, the third node recognizes that one is missing and the secondary becomes the new leader.<\/p>\n We have to do some changes on the two existing hosts and on the third host we need to install etcd.<\/p>\n Afterwards we have to create the etcd configuration file on the third host and change the files on the both other hosts to include the new host. Create the etcd.service on the third node Enable etcd service and reboot the third node Now patroni can be installed on the third node as well Afterwards we have to change the patroni.yml file on both database hosts and recreate the database, that everything works as expected. Otherwise the third etcd won’t be recognized in the cluster.<\/p>\n 1. Shutdown patroni on both nodes 2. Clean up data directories 3. Clean up cluster configuration in etcd 4. Change the patroni.yml file 5. Start patroni on both nodes where the database should run. This will build a new cluster. 6. Start etcd on all THREE nodes Everything works as before. In the list you only see your two node patroni The etcd Cluster looks also good Now let’s try another shutdown of the leader node and see what happens: Also in \/var\/log\/messages you can see the successful failover to opendb_stby Running and especially failover a Patroni Cluster in two node modus is only possible when having a third etcd hosts. Otherwise the automatic failover won’t work when one node crashes.<\/p>\n","protected":false},"excerpt":{"rendered":" A few days ago I had the interesting mission to build a two Node Patroni Postgres 9.6 Cluster using etcd in our openDB Appliance environment. Sounds easy, one leader, one secondary, both running etcd, that’s it. But that’s only the first impression.<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[1618,1543,1002],"type_dbi":[],"class_list":["post-12450","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-etcd","tag-patroni","tag-postgresql-9-6"],"acf":[],"yoast_head":"\n
\nAfterwards we installed all the stuff for patroni and etcd. We can do all the configuration for the cluster, etcd and patroni (for more information about building a patroni cluster click here <\/a> ) .<\/p>\n
\n
\nopendb@opendb_stby:\/home\/opendb\/ [PG1] patronictl list
\n\/home\/opendb\/.local\/lib\/python2.7\/site-packages\/psycopg2\/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http:\/\/initd.org\/psycopg\/docs\/install.html#binary-install-from-pypi>.
\n\"\"\")
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| Cluster | Member | Host | Role | State | TL | Lag in MB |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| PG1 | opendb_primary | 192.168.22.33 | Leader | running | 3 | 0.0 |
\n| PG1 | opendb_stby | 192.168.22.34 | | running | 3 | 0.0 |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n<\/code><\/p>\n
\n
\nopendb@opendb_stby:\/home\/opendb\/ [PG1] patronictl switchover
\n\/home\/opendb\/.local\/lib\/python2.7\/site-packages\/psycopg2\/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http:\/\/initd.org\/psycopg\/docs\/install.html#binary-install-from-pypi>.
\n\"\"\")
\nMaster [opendb_primary]:
\nCandidate ['opendb_stby'] []: opendb_stby
\nWhen should the switchover take place (e.g. 2015-10-01T14:30) [now]:
\nCurrent cluster topology
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| Cluster | Member | Host | Role | State | TL | Lag in MB |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| PG1 | opendb_primary | 192.168.22.33 | Leader | running | 3 | 0.0 |
\n| PG1 | opendb_stby | 192.168.22.34 | | running | 3 | 0.0 |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\nAre you sure you want to switchover cluster PG1, demoting current master opendb_primary? [y\/N]: y
\n2019-04-25 01:19:07.89086 Successfully switched over to \"opendb_stby\"
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| Cluster | Member | Host | Role | State | TL | Lag in MB |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| PG1 | opendb_primary | 192.168.22.33 | | stopped | | unknown |
\n| PG1 | opendb_stby | 192.168.22.34 | Leader | running | 3 | |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n<\/code><\/p>\n
\n
\nopendb@opendb_stby:\/home\/opendb\/ [PG1] patronictl list
\n\/home\/opendb\/.local\/lib\/python2.7\/site-packages\/psycopg2\/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http:\/\/initd.org\/psycopg\/docs\/install.html#binary-install-from-pypi>.
\n\"\"\")
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| Cluster | Member | Host | Role | State | TL | Lag in MB |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| PG1 | opendb_primary | 192.168.22.33 | | running | 4 | 0.0 |
\n| PG1 | opendb_stby | 192.168.22.34 | Leader | running | 4 | 0.0 |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n<\/code><\/p>\n
\n
\nopendb@opendb_stby:\/home\/opendb\/ [PG1] patronictl list
\n\/home\/opendb\/.local\/lib\/python2.7\/site-packages\/psycopg2\/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http:\/\/initd.org\/psycopg\/docs\/install.html#binary-install-from-pypi>.
\n\"\"\")
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| Cluster | Member | Host | Role | State | TL | Lag in MB |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| PG1 | opendb_primary | 192.168.22.33 | | running | 4 | 0.0 |
\n| PG1 | opendb_stby | 192.168.22.34 | Leader | running | 4 | 0.0 |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\nopendb@opendb_stby:\/home\/opendb\/ [PG1] sudo systemctl stop patroni
\nopendb@opendb_stby:\/home\/opendb\/ [PG1] patronictl list
\n\/home\/opendb\/.local\/lib\/python2.7\/site-packages\/psycopg2\/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: <http:\/\/initd.org\/psycopg\/docs\/install.html#binary-install-from-pypi>.
\n\"\"\")
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| Cluster | Member | Host | Role | State | TL | Lag in MB |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| PG1 | opendb_primary | 192.168.22.33 | Leader | running | 5 | 0.0 |
\n| PG1 | opendb_stby | 192.168.22.34 | | stopped | | unknown |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\nopendb@opendb_stby:\/home\/opendb\/ [PG1]
\n<\/code><\/p>\n
\nWith this the problem starts! There are tons of error messages in the log file. The secondary recognizes that the leader has gone, but does not become the new leader.<\/p>\n
\nApr 24 13:10:34 opendb_stby patroni: 2019-04-24 13:10:34,124 INFO: no action. i am a secondary and i am following a leader
\nApr 24 13:10:36 opendb_stby patroni: 2019-04-24 13:10:36,766 WARNING: Request failed to opendb_primary: GET http:\/\/192.168.22.33:8008\/patroni (('Connection aborted.', error(104, 'Connection reset by peer')))
\nApr 24 13:10:36 opendb_stby etcd: lost the TCP streaming connection with peer 1154b56a13168e2a (stream MsgApp v2 reader)
\nApr 24 13:10:36 opendb_stby etcd: lost the TCP streaming connection with peer 1154b56a13168e2a (stream Message reader)
\nApr 24 13:10:36 opendb_stby etcd: failed to dial 1154b56a13168e2a on stream MsgApp v2 (peer 1154b56a13168e2a failed to find local node f13d668ae0cba84)
\nApr 24 13:10:36 opendb_stby etcd: peer 1154b56a13168e2a became inactive (message send to peer failed)
\nApr 24 13:10:36 opendb_stby patroni: 2019-04-24 13:10:36,838 WARNING: Retrying (Retry(total=0, connect=None, read=None, redirect=0, status=None)) after connection broken by 'NewConnectionError(': Failed to establish a new connection: [Errno 111] Connection refused',)': \/v2\/keys\/service\/PG1\/leader
\nApr 24 13:10:36 opendb_stby patroni: 2019-04-24 13:10:36,839 ERROR: Request to server http:\/\/192.168.22.33:2379 failed: MaxRetryError(\"HTTPConnectionPool(host='192.168.22.33', port=2379): Max retries exceeded with url: \/v2\/keys\/service\/PG1\/leader (Caused by NewConnectionError(': Failed to establish a new connection: [Errno 111] Connection refused',))\",)<\/strong>
\nApr 24 13:10:36 opendb_stby patroni: 2019-04-24 13:10:36,839 INFO: Reconnection allowed, looking for another server.
\nApr 24 13:10:36 opendb_stby patroni: 2019-04-24 13:10:36,839 INFO: Selected new etcd server http:\/\/192.168.22.34:2379
\nApr 24 13:10:37 opendb_stby etcd: lost the TCP streaming connection with peer 1154b56a13168e2a (stream Message writer)
\nApr 24 13:10:38 opendb_stby etcd: lost the TCP streaming connection with peer 1154b56a13168e2a (stream MsgApp v2 writer)
\nApr 24 13:10:38 opendb_stby etcd: f13d668ae0cba84 stepped down to follower since quorum is not active
\nApr 24 13:10:38 opendb_stby etcd: f13d668ae0cba84 became follower at term 19197
\nApr 24 13:10:38 opendb_stby etcd: raft.node: f13d668ae0cba84 lost leader f13d668ae0cba84 at term 19197
\nApr 24 13:10:39 opendb_stby patroni: 2019-04-24 13:10:39,344 WARNING: Retrying (Retry(total=0, connect=None, read=None, redirect=0, status=None)) after connection broken by 'ReadTimeoutError(\"HTTPConnectionPool(host=u'192.168.22.34', port=2379): Read timed out. (read timeout=2.5)\",)': \/v2\/keys\/service\/PG1\/leader
\nApr 24 13:10:40 opendb_stby etcd: f13d668ae0cba84 is starting a new election at term 19197
\nApr 24 13:10:40 opendb_stby etcd: f13d668ae0cba84 became candidate at term 19198
\nApr 24 13:10:40 opendb_stby etcd: f13d668ae0cba84 received MsgVoteResp from f13d668ae0cba84 at term 19198
\nApr 24 13:10:40 opendb_stby etcd: f13d668ae0cba84 [logterm: 19197, index: 447303] sent MsgVote request to 1154b56a13168e2a at term 19198
\nApr 24 13:10:41 opendb_stby etcd: f13d668ae0cba84 is starting a new election at term 19198
\nApr 24 13:10:41 opendb_stby etcd: f13d668ae0cba84 became candidate at term 19199
\nApr 24 13:10:41 opendb_stby etcd: f13d668ae0cba84 received MsgVoteResp from f13d668ae0cba84 at term 19199
\nApr 24 13:10:41 opendb_stby etcd: f13d668ae0cba84 [logterm: 19197, index: 447303] sent MsgVote request to 1154b56a13168e2a at term 19199
\nApr 24 13:10:41 opendb_stby patroni: 2019-04-24 13:10:41,938 ERROR: Request to server http:\/\/192.168.22.34:2379 failed: MaxRetryError(u'HTTPConnectionPool(host=u'192.168.22.34', port=2379): Max retries exceeded with url: \/v2\/keys\/service\/PG1\/leader (Caused by ReadTimeoutError(\"HTTPConnectionPool(host=u'192.168.22.34', port=2379): Read timed out. (read timeout=2.5)\",))',)
\nApr 24 13:10:41 opendb_stby patroni: 2019-04-24 13:10:41,938 INFO: Reconnection allowed, looking for another server.
\nApr 24 13:10:41 opendb_stby patroni: 2019-04-24 13:10:41,939 ERROR: Machines cache is empty, no machines to try.
\nApr 24 13:10:41 opendb_stby patroni: 2019-04-24 13:10:41,939 INFO: Selected new etcd server http:\/\/192.168.22.33:2379
\nApr 24 13:10:43 opendb_stby etcd: f13d668ae0cba84 is starting a new election at term 19199
\n<\/code><\/p>\nWhy? What went wrong?<\/h3>\n
\nA two node patroni with only two etcd won’t work. How should the secondary node know, that he should become the leader?<\/p>\n
\nwget https:\/\/github.com\/etcd-io\/etcd\/releases\/download\/v3.3.10\/etcd-v3.3.10-linux-amd64.tar.gz
\ntar -axf etcd-v3.3.10-linux-amd64.tar.gz
\ncp etcd-v3.3.10-linux-amd64\/etcd* \/u01\/app\/opendb\/local\/dmk\/bin
\nmkdir -p \/u02\/pgdata\/etcd
\nrm -rf etcd-v3.3.10-linux-amd64 etcd-v3.3.10-linux-amd64.tar.gz
\n<\/code><\/p>\n
\n
\ncat > \/u01\/app\/opendb\/local\/dmk\/dmk_postgres\/etc\/etcd.conf < \/u01\/app\/opendb\/local\/dmk\/dmk_postgres\/etc\/etcd.conf < \/u01\/app\/opendb\/local\/dmk\/dmk_postgres\/etc\/etcd.conf << EOF
\nname: opendb-stby2
\ndata-dir: \/u02\/opendb\/pgdata\/etcd
\ninitial-advertise-peer-urls: http:\/\/192.168.22.35:2380
\nlisten-peer-urls: http:\/\/192.168.22.35:2380
\nlisten-client-urls: http:\/\/192.168.22.35:2379,http:\/\/localhost:2379
\nadvertise-client-urls: http:\/\/192.168.22.35:2379
\ninitial-cluster: opendb-primary=http:\/\/192.168.22.33:2380,opendb-stby=http:\/\/192.168.22.34:2380,opendb-stby2=http:\/\/192.168.22.35:2380
\nEOF
\n<\/code><\/p>\n
\n
\n[opendb@opendb_standby2 etc]$ cat \/etc\/systemd\/system\/etcd.service
\n#
\n# systemd integration for etcd
\n# Put this file under \/etc\/systemd\/system\/etcd.service
\n# then: systemctl daemon-reload
\n# then: systemctl list-unit-files | grep etcd
\n# then: systemctl enable etcd.service
\n#
\n[Unit]
\nDescription=dbi services etcd service
\nAfter=network.target
\n[Service]
\nUser=opendb
\nType=notify
\nExecStart=\/u01\/app\/opendb\/local\/dmk\/dmk_postgres\/bin\/etcd --config-file \/u01\/app\/opendb\/local\/dmk\/dmk_postgres\/etc\/etcd.conf
\nRestart=always
\nRestartSec=10s
\nLimitNOFILE=40000
\n[Install]
\nWantedBy=multi-user.target
\n<\/code><\/p>\n
\n
\nsudo systemctl daemon-reload
\nsudo systemctl list-unit-files | grep etcd
\nsudo systemctl enable etcd.service
\nsudo systemctl reboot
\n<\/code><\/p>\n
\n
\nsudo pip install --upgrade pip
\npip install --upgrade --user setuptools
\npip install --user psycopg2-binary
\npip install --user patroni[etcd]
\n<\/code><\/p>\n
\n
\nsystemctl stop patroni
\n<\/code><\/p>\n
\n
\nrm -rf \/u02\/opendb\/pgdata\/96\/PG1
\n<\/code><\/p>\n
\n
\npatronictl -c \/u01\/app\/opendb\/local\/dmk\/dmk_postgres\/etc\/patroni.yml remove PG1
\n<\/code><\/p>\n
\n
\netcd:
\n hosts: 192.168.22.33:2379,192.168.22.34:2379,192.168.22.35:2379
\n<\/code><\/p>\n
\n
\nsystemctl start patroni
\n<\/code><\/p>\n
\n
\nsystemctl start etcd
\n<\/code><\/p>\n
\n
\nopendb@opendb_primary:\/home\/opendb\/ [PG1] patronictl list
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| Cluster | Member | Host | Role | State | TL | Lag in MB |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| PG1 | opendb_primary | 192.168.22.33 | Leader | running | 7 | 0.0 |
\n| PG1 | opendb_stby | 192.168.22.34 | | running | 7 | 0.0 |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n<\/code><\/p>\n
\n
\n opendb@opendb_primary:\/home\/opendb\/ [PG1] etcdctl cluster-health
\nmember f13d668ae0cba84 is healthy: got healthy result from http:\/\/192.168.22.34:2379
\nmember 1154b56a13168e2a is healthy: got healthy result from http:\/\/192.168.22.33:2379
\nmember c8520dba9907702d is healthy: got healthy result from http:\/\/192.168.22.35:2379
\ncluster is healthy
\n<\/code><\/p>\n
\n
\nopendb@opendb_stby:\/home\/opendb\/ [PG1] patronictl list
\n\/home\/opendb\/.local\/lib\/python2.7\/site-packages\/psycopg2\/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: .
\n \"\"\")
\n2019-04-25 03:40:26,447 - WARNING - Retrying (Retry(total=0, connect=None, read=None, redirect=0, status=None)) after connection broken by 'ConnectTimeoutError(, 'Connection to 192.168.22.33 timed out. (connect timeout=2.5)')': \/v2\/machines
\n2019-04-25 03:40:28,953 - ERROR - Failed to get list of machines from http:\/\/192.168.22.33:2379\/v2: MaxRetryError(\"HTTPConnectionPool(host='192.168.22.33', port=2379): Max retries exceeded with url: \/v2\/machines (Caused by ConnectTimeoutError(, 'Connection to 192.168.22.33 timed out. (connect timeout=2.5)'))\",)
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| Cluster | Member | Host | Role | State | TL | Lag in MB |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n| PG1 | opendb_primary | 192.168.22.33 | | stopped | | unknown |
\n| PG1 | opendb_stby | 192.168.22.34 | Leader | running | 8 | 0.0 |
\n+---------+----------------+---------------+--------+---------+----+-----------+
\n03:40:29 opendb@opendb_stby:\/home\/opendb\/ [PG1]
\n<\/code><\/p>\n
\n
\nApr 25 03:40:09 opendb_stby patroni: 2019-04-25 03:40:09,096 WARNING: Request failed to opendb_primary: GET http:\/\/192.168.22.33:8008\/patroni (('Connection aborted.', error(104, 'Connection reset by peer')))
\nApr 25 03:40:09 opendb_stby etcd: f13d668ae0cba84 [term 11] received MsgTimeoutNow from 1154b56a13168e2a and starts an election to get leadership.
\nApr 25 03:40:09 opendb_stby etcd: f13d668ae0cba84 became candidate at term 12
\nApr 25 03:40:09 opendb_stby etcd: f13d668ae0cba84 received MsgVoteResp from f13d668ae0cba84 at term 12
\nApr 25 03:40:09 opendb_stby etcd: f13d668ae0cba84 [logterm: 11, index: 92328] sent MsgVote request to 1154b56a13168e2a at term 12
\nApr 25 03:40:09 opendb_stby etcd: f13d668ae0cba84 [logterm: 11, index: 92328] sent MsgVote request to c8520dba9907702d at term 12
\nApr 25 03:40:09 opendb_stby etcd: raft.node: f13d668ae0cba84 lost leader 1154b56a13168e2a at term 12
\nApr 25 03:40:09 opendb_stby etcd: f13d668ae0cba84 received MsgVoteResp from c8520dba9907702d at term 12
\nApr 25 03:40:09 opendb_stby etcd: f13d668ae0cba84 [quorum:2] has received 2 MsgVoteResp votes and 0 vote rejections
\nApr 25 03:40:09 opendb_stby etcd: f13d668ae0cba84 became leader at term 12
\nApr 25 03:40:09 opendb_stby etcd: raft.node: f13d668ae0cba84 elected leader f13d668ae0cba84 at term 12
\nApr 25 03:40:09 opendb_stby patroni: 2019-04-25 03:40:09,190 INFO: Software Watchdog activated with 25 second timeout, timing slack 15 seconds
\nApr 25 03:40:09 opendb_stby patroni: 2019-04-25 03:40:09,194 INFO: promoted self to leader by acquiring session lock
\nApr 25 03:40:09 opendb_stby patroni: server promoting
\nApr 25 03:40:09 opendb_stby patroni: 2019-04-25 03:40:09,200 INFO: cleared rewind state after becoming the leader
\nApr 25 03:40:09 opendb_stby etcd: lost the TCP streaming connection with peer 1154b56a13168e2a (stream Message reader)
\nApr 25 03:40:09 opendb_stby etcd: lost the TCP streaming connection with peer 1154b56a13168e2a (stream MsgApp v2 reader)
\nApr 25 03:40:09 opendb_stby etcd: failed to dial 1154b56a13168e2a on stream Message (peer 1154b56a13168e2a failed to find local node f13d668ae0cba84)
\nApr 25 03:40:09 opendb_stby etcd: peer 1154b56a13168e2a became inactive (message send to peer failed)
\nApr 25 03:40:09 opendb_stby etcd: lost the TCP streaming connection with peer 1154b56a13168e2a (stream Message writer)
\nApr 25 03:40:10 opendb_stby patroni: 2019-04-25 03:40:10,271 INFO: Lock owner: opendb_stby; I am opendb_stby
\nApr 25 03:40:10 opendb_stby patroni: 2019-04-25 03:40:10,304 INFO: no action. i am the leader with the lock
\n<\/code><\/p>\nConclusion<\/h3>\n