{"id":15390,"date":"2020-12-11T09:43:40","date_gmt":"2020-12-11T08:43:40","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/"},"modified":"2020-12-11T09:43:40","modified_gmt":"2020-12-11T08:43:40","slug":"pg_auto_failover-failover-and-switchover-scenarios","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/","title":{"rendered":"pg_auto_failover: Failover and switchover scenarios"},"content":{"rendered":"<p>In the <a href=\"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/\" target=\"_blank\" rel=\"noopener noreferrer\">last post<\/a> we had a look at the installation and setup of pg_auto_failover. We currently have one primary cluster and two replicas synchronizing from this primary cluster. But we potentially also have an issue in the setup: The monitor is running beside the primary instance on the same node and if that nodes goes down the monitor is gone. What happens in that case and how can we avoid that? We also did not look at controlled switch-overs, and this is definitely something you want to have in production. From time to time you&#8217;ll need to do some maintenance on one of the nodes, and switching the primary cluster to another node is very handy in such situations. Lets start with the simple case and have a look at switch-overs first.<\/p>\n<p><!--more--><\/p>\n<p>This is the current state of the setup:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgaf1:~$ pg_autoctl show state --pgdata \/u02\/pgdata\/13\/monitor\/\n  Name |  Node |                      Host:Port |       LSN | Reachable |       Current State |      Assigned State\n-------+-------+--------------------------------+-----------+-----------+---------------------+--------------------\nnode_1 |     1 | pgaf1.it.dbi-services.com:5432 | 0\/6002408 |       yes |             primary |             primary\nnode_2 |     2 | pgaf2.it.dbi-services.com:5432 | 0\/6002408 |       yes |           secondary |           secondary\nnode_3 |     3 | pgaf3.it.dbi-services.com:5432 | 0\/6002408 |       yes |           secondary |           secondary\n<\/pre>\n<p>Before we attempt to do a switch-over you should be aware of your replication settings:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgaf1:~$ pg_autoctl get formation settings --pgdata \/u02\/pgdata\/13\/monitor\/\n  Context |    Name |                   Setting | Value                                                       \n----------+---------+---------------------------+-------------------------------------------------------------\nformation | default |      number_sync_standbys | 1                                                           \n  primary |  node_1 | synchronous_standby_names | 'ANY 1 (pgautofailover_standby_2, pgautofailover_standby_3)'\n     node |  node_1 |        candidate priority | 50                                                          \n     node |  node_2 |        candidate priority | 50                                                          \n     node |  node_3 |        candidate priority | 50                                                          \n     node |  node_1 |        replication quorum | true                                                        \n     node |  node_2 |        replication quorum | true                                                        \n     node |  node_3 |        replication quorum | true                                     \n<\/pre>\n<p>What does this tell us:<\/p>\n<ul>\n<li>synchronous_standby_names: We&#8217;re using <a href=\"https:\/\/www.postgresql.org\/docs\/current\/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES\" target=\"_blank\" rel=\"noopener noreferrer\">synchronous replication<\/a> and at least one of the two replicas need to confirm a commit (This is a PostgreSQL setting)<\/li>\n<li>number_sync_standbys=1: That means at least one standby needs to confirm the commit (This is a pg_auto_failover setting)<\/li>\n<li>candidate priority=50: This specifies which replica gets promoted. At the default setting of 50 all replicas have the same chance to be selected for promotion and the monitor will pick the one with the most advanced <a href=\"https:\/\/www.postgresql.org\/docs\/current\/datatype-pg-lsn.html\" target=\"_blank\" rel=\"noopener noreferrer\">LSN<\/a>. (This is a pg_auto_failover setting)<\/li>\n<li>replication quorum=true: This mean synchronous replication, a values of false mean asynchronous replication. (This is a pg_auto_failover setting)<\/li>\n<\/ul>\n<p>You maybe have noticed the &#8220;formation&#8221; keyword above. A formation is a set of PostgreSQL clusters that are managed together and that means you can use the same monitor to manage multiple sets of PostgreSQL clusters. We are using the default formation in this example.<\/p>\n<p>Lets assume we need to do some maintenance on our primary node and therefore want to switch-over the primary instance to another node. The command to do that is simple:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1; highlight: [1]\">\npostgres@pgaf1:~$ pg_autoctl perform switchover --pgdata \/u02\/pgdata\/13\/PG1\/\n16:10:05 15960 INFO  Targetting group 0 in formation \"default\"\n16:10:05 15960 INFO  Listening monitor notifications about state changes in formation \"default\" and group 0\n16:10:05 15960 INFO  Following table displays times when notifications are received\n    Time |   Name |  Node |                      Host:Port |       Current State |      Assigned State\n---------+--------+-------+--------------------------------+---------------------+--------------------\n16:10:05 | node_1 |     1 | pgaf1.it.dbi-services.com:5432 |             primary |            draining\n16:10:05 | node_1 |     1 | pgaf1.it.dbi-services.com:5432 |            draining |            draining\n16:10:05 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |           secondary |          report_lsn\n16:10:05 | node_3 |     3 | pgaf3.it.dbi-services.com:5432 |           secondary |          report_lsn\n16:10:06 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |          report_lsn |          report_lsn\n16:10:06 | node_3 |     3 | pgaf3.it.dbi-services.com:5432 |          report_lsn |          report_lsn\n16:10:06 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |          report_lsn |   prepare_promotion\n16:10:06 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |   prepare_promotion |   prepare_promotion\n16:10:06 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |   prepare_promotion |    stop_replication\n16:10:06 | node_1 |     1 | pgaf1.it.dbi-services.com:5432 |            draining |      demote_timeout\n16:10:06 | node_3 |     3 | pgaf3.it.dbi-services.com:5432 |          report_lsn |      join_secondary\n16:10:06 | node_1 |     1 | pgaf1.it.dbi-services.com:5432 |      demote_timeout |      demote_timeout\n16:10:06 | node_3 |     3 | pgaf3.it.dbi-services.com:5432 |      join_secondary |      join_secondary\n16:10:07 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |    stop_replication |    stop_replication\n16:10:07 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |    stop_replication |        wait_primary\n16:10:07 | node_1 |     1 | pgaf1.it.dbi-services.com:5432 |      demote_timeout |             demoted\n16:10:07 | node_1 |     1 | pgaf1.it.dbi-services.com:5432 |             demoted |             demoted\n16:10:07 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |        wait_primary |        wait_primary\n16:10:07 | node_3 |     3 | pgaf3.it.dbi-services.com:5432 |      join_secondary |           secondary\n16:10:07 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |        wait_primary |             primary\n16:10:07 | node_1 |     1 | pgaf1.it.dbi-services.com:5432 |             demoted |          catchingup\n16:10:07 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |        wait_primary |        join_primary\n16:10:07 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |        join_primary |        join_primary\n16:10:08 | node_3 |     3 | pgaf3.it.dbi-services.com:5432 |           secondary |           secondary\n16:10:08 | node_1 |     1 | pgaf1.it.dbi-services.com:5432 |          catchingup |          catchingup\n16:10:08 | node_1 |     1 | pgaf1.it.dbi-services.com:5432 |          catchingup |           secondary\n16:10:08 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |        join_primary |             primary\n16:10:08 | node_1 |     1 | pgaf1.it.dbi-services.com:5432 |           secondary |           secondary\n16:10:08 | node_2 |     2 | pgaf2.it.dbi-services.com:5432 |             primary |             primary\npostgres@pgaf1:~$ \n<\/pre>\n<p>You&#8217;ll get the progress messages to the screen so you can actually see what happens. As the services are started with systemd you can also have a look at the journal:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n-- Logs begin at Thu 2020-12-10 15:17:38 CET, end at Thu 2020-12-10 16:11:26 CET. --\nDec 10 16:10:08 pgaf1 pg_autoctl[327]: 16:10:08 399 INFO  FSM transition from \"catchingup\" to \"secondary\": Convinced the monitor that I'm\nDec 10 16:10:08 pgaf1 pg_autoctl[327]: 16:10:08 399 INFO  Transition complete: current state is now \"secondary\"\nDec 10 16:10:08 pgaf1 pg_autoctl[341]: 16:10:08 397 INFO  node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432) reported new state \"secondary\"\nDec 10 16:10:08 pgaf1 pg_autoctl[341]: 16:10:08 397 INFO  New state for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432): secondary \u279c sec\nDec 10 16:10:08 pgaf1 pg_autoctl[327]: 16:10:08 399 INFO  New state for this node (node 1, \"node_1\") (pgaf1.it.dbi-services.com:5432): se\nDec 10 16:10:08 pgaf1 pg_autoctl[341]: 16:10:08 397 INFO  node 2 \"node_2\" (pgaf2.it.dbi-services.com:5432) reported new state \"primary\"\nDec 10 16:10:08 pgaf1 pg_autoctl[341]: 16:10:08 397 INFO  New state for node 2 \"node_2\" (pgaf2.it.dbi-services.com:5432): primary \u279c prima\nDec 10 16:10:08 pgaf1 pg_autoctl[327]: 16:10:08 399 INFO  New state for node 2 \"node_2\" (pgaf2.it.dbi-services.com:5432): primary \u279c prima\n<\/pre>\n<p>The second second node was selected as the new primary, and we can of course confirm that:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgaf1:~$ pg_autoctl show state --pgdata \/u02\/pgdata\/13\/monitor\/\n  Name |  Node |                      Host:Port |       LSN | Reachable |       Current State |      Assigned State\n-------+-------+--------------------------------+-----------+-----------+---------------------+--------------------\nnode_1 |     1 | pgaf1.it.dbi-services.com:5432 | 0\/60026F8 |       yes |           secondary |           secondary\nnode_2 |     2 | pgaf2.it.dbi-services.com:5432 | 0\/60026F8 |       yes |             primary |             primary\nnode_3 |     3 | pgaf3.it.dbi-services.com:5432 | 0\/60026F8 |       yes |           secondary |           secondary\n\npostgres@pgaf1:~$ \n<\/pre>\n<p>Next test: What happens when we reboot a node that currently is running a replica? Lets reboot pgaf3 as this one is currently a replica, and it does not run the monitor:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgaf3:~$ sudo reboot\npostgres@pgaf3:~$ Connection to 192.168.22.192 closed by remote host.\nConnection to 192.168.22.192 closed.\n<\/pre>\n<p>Watching at the state the &#8220;Reachable&#8221; status changes to &#8220;no&#8221; for the third instance and the LSN falls behind:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgaf1:~$ pg_autoctl show state --pgdata \/u02\/pgdata\/13\/monitor\/\n  Name |  Node |                      Host:Port |       LSN | Reachable |       Current State |      Assigned State\n-------+-------+--------------------------------+-----------+-----------+---------------------+--------------------\nnode_1 |     1 | pgaf1.it.dbi-services.com:5432 | 0\/60026F8 |       yes |           secondary |           secondary\nnode_2 |     2 | pgaf2.it.dbi-services.com:5432 | 0\/60026F8 |       yes |             primary |             primary\nnode_3 |     3 | pgaf3.it.dbi-services.com:5432 | 0\/6000000 |        no |           secondary |           secondary\n<\/pre>\n<p>Once it is back, the replica is brought back to the configuration and all is fine:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgaf1:~$ pg_autoctl show state --pgdata \/u02\/pgdata\/13\/monitor\/\n  Name |  Node |                      Host:Port |       LSN | Reachable |       Current State |      Assigned State\n-------+-------+--------------------------------+-----------+-----------+---------------------+--------------------\nnode_1 |     1 | pgaf1.it.dbi-services.com:5432 | 0\/60026F8 |       yes |           secondary |           secondary\nnode_2 |     2 | pgaf2.it.dbi-services.com:5432 | 0\/60026F8 |       yes |             primary |             primary\nnode_3 |     3 | pgaf3.it.dbi-services.com:5432 | 0\/6000000 |       yes |           secondary |           secondary\n\n...\npostgres@pgaf1:~$ pg_autoctl show state --pgdata \/u02\/pgdata\/13\/monitor\/\n  Name |  Node |                      Host:Port |       LSN | Reachable |       Current State |      Assigned State\n-------+-------+--------------------------------+-----------+-----------+---------------------+--------------------\nnode_1 |     1 | pgaf1.it.dbi-services.com:5432 | 0\/6013120 |       yes |           secondary |           secondary\nnode_2 |     2 | pgaf2.it.dbi-services.com:5432 | 0\/6013120 |       yes |             primary |             primary\nnode_3 |     3 | pgaf3.it.dbi-services.com:5432 | 0\/6013120 |       yes |           secondary |           secondary\n<\/pre>\n<p>But what happens if we shutdown the monitor node? <\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgaf1:~$ sudo systemctl poweroff\npostgres@pgaf1:~$ Connection to 192.168.22.190 closed by remote host.\nConnection to 192.168.22.190 closed.\n<\/pre>\n<p>Checking the status on the node which currently hosts the primary cluster:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgaf2:~$ pg_autoctl show state --pgdata \/u02\/pgdata\/13\/PG1\/\n10:26:52 1293 WARN  Failed to connect to \"postgres:\/\/autoctl_node@pgaf1.it.dbi-services.com:5433\/pg_auto_failover?sslmode=require\", retrying until the server is ready\n10:26:52 1293 ERROR Connection to database failed: timeout expired\n10:26:52 1293 ERROR Failed to connect to \"postgres:\/\/autoctl_node@pgaf1.it.dbi-services.com:5433\/pg_auto_failover?sslmode=require\" after 1 attempts in 2 seconds, pg_autoctl stops retrying now\n10:26:52 1293 ERROR Failed to retrieve current state from the monitor\n<\/pre>\n<p>As the monitor is down we cannot anymore ask for status. The primary and the remaining replica cluster are still up and running but we lost the possibility to interact with pg_auto_failover. Booting up the monitor node brings is back into the game:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgaf2:~$ pg_autoctl show state --pgdata \/u02\/pgdata\/13\/PG1\/\n  Name |  Node |                      Host:Port |       LSN | Reachable |       Current State |      Assigned State\n-------+-------+--------------------------------+-----------+-----------+---------------------+--------------------\nnode_1 |     1 | pgaf1.it.dbi-services.com:5432 | 0\/6000000 |       yes |           secondary |           secondary\nnode_2 |     2 | pgaf2.it.dbi-services.com:5432 | 0\/6013240 |       yes |             primary |             primary\nnode_3 |     3 | pgaf3.it.dbi-services.com:5432 | 0\/6013240 |       yes |           secondary |           secondary\n<\/pre>\n<p>This has a consequence: The monitor should not run on any of the PostgreSQL nodes but on a separate node which is dedicated to the monitor. As you can manage more than one HA setup with the same monitor this should not an issue, though. But this also means that the monitor is a single point of failure and the health of the monitor is critical for pg_auto_failover.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the last post we had a look at the installation and setup of pg_auto_failover. We currently have one primary cluster and two replicas synchronizing from this primary cluster. But we potentially also have an issue in the setup: The monitor is running beside the primary instance on the same node and if that nodes [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[77],"type_dbi":[],"class_list":["post-15390","post","type-post","status-publish","format-standard","hentry","category-application-integration-middleware","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>pg_auto_failover: Failover and switchover scenarios - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"pg_auto_failover: Failover and switchover scenarios\" \/>\n<meta property=\"og:description\" content=\"In the last post we had a look at the installation and setup of pg_auto_failover. We currently have one primary cluster and two replicas synchronizing from this primary cluster. But we potentially also have an issue in the setup: The monitor is running beside the primary instance on the same node and if that nodes [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-12-11T08:43:40+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pg_auto_failover-failover-and-switchover-scenarios\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pg_auto_failover-failover-and-switchover-scenarios\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"pg_auto_failover: Failover and switchover scenarios\",\"datePublished\":\"2020-12-11T08:43:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pg_auto_failover-failover-and-switchover-scenarios\\\/\"},\"wordCount\":609,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Application integration &amp; Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pg_auto_failover-failover-and-switchover-scenarios\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pg_auto_failover-failover-and-switchover-scenarios\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pg_auto_failover-failover-and-switchover-scenarios\\\/\",\"name\":\"pg_auto_failover: Failover and switchover scenarios - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-12-11T08:43:40+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pg_auto_failover-failover-and-switchover-scenarios\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pg_auto_failover-failover-and-switchover-scenarios\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pg_auto_failover-failover-and-switchover-scenarios\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"pg_auto_failover: Failover and switchover scenarios\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"pg_auto_failover: Failover and switchover scenarios - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/","og_locale":"en_US","og_type":"article","og_title":"pg_auto_failover: Failover and switchover scenarios","og_description":"In the last post we had a look at the installation and setup of pg_auto_failover. We currently have one primary cluster and two replicas synchronizing from this primary cluster. But we potentially also have an issue in the setup: The monitor is running beside the primary instance on the same node and if that nodes [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/","og_site_name":"dbi Blog","article_published_time":"2020-12-11T08:43:40+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"pg_auto_failover: Failover and switchover scenarios","datePublished":"2020-12-11T08:43:40+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/"},"wordCount":609,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Application integration &amp; Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/","url":"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/","name":"pg_auto_failover: Failover and switchover scenarios - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-12-11T08:43:40+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/pg_auto_failover-failover-and-switchover-scenarios\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"pg_auto_failover: Failover and switchover scenarios"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15390","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=15390"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15390\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15390"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15390"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15390"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15390"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}