{"id":15389,"date":"2020-12-10T14:28:31","date_gmt":"2020-12-10T13:28:31","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/"},"modified":"2020-12-10T14:28:31","modified_gmt":"2020-12-10T13:28:31","slug":"easy-failover-and-switchover-with-pg_auto_failover","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/","title":{"rendered":"Easy failover and switchover with pg_auto_failover"},"content":{"rendered":"

One the really cool things with PostgreSQL is, that you have plenty of choices when it comes to tooling. For high availability we usually go with Patroni<\/a>, but there is also pg_auto_failover<\/a> and this will be the topic of this post. Because of the recent announcement around CentOS<\/a> we’ll go with Debian<\/a> this time. What is already prepared is the PostgreSQL installation (version 13.1), but nothing else. We start from scratch to see, if “is optimized for simplicity and correctness”, as it is stated on the GitHub page holds true.<\/p>\n

<\/p>\n

This is the setup we’ll start with:<\/p>\n\n\n\n\n\n
Hostname<\/th>\nIP-Address<\/th>\nInitial role<\/th>\n<\/tr>\n
pgaf1.it.dbi-services.com<\/td>\n192.168.22.190<\/td>\nPrimary and pg_auto_failover monitor<\/td>\n<\/tr>\n
pgaf2.it.dbi-services.com<\/td>\n192.168.22.191<\/td>\nFirst replica<\/td>\n<\/tr>\n
pgaf3.it.dbi-services.com<\/td>\n192.168.22.192<\/td>\nSecond replica<\/td>\n<\/tr>\n<\/table>\n

As said above, all three nodes have PostgreSQL 13.1 already installed at this location (PostgreSQL was installed from source code, but that should not really matter):<\/p>\n

\npostgres@pgaf1:~$ ls \/u01\/app\/postgres\/product\/13\/db_1\/\nbin  include  lib  share\n<\/pre>\n
What I did in addition, is to create ssh keys and then copy those from each machine to all nodes so password-less ssh connections are available between the nodes (here is the example from the first node):<\/p>\n
\npostgres@pgaf1:~$ ssh-keygen\npostgres@pgaf1:~$ ssh-copy-id postgres@pgaf1\npostgres@pgaf1:~$ ssh-copy-id postgres@pgaf2\npostgres@pgaf1:~$ ssh-copy-id postgres@pgaf3\n<\/pre>\n
For installing pg_auto_failover from source make sure that pg_config<\/a> is in your path:<\/p>\n
\npostgres@pgaf1:~$ which pg_config\n\/u01\/app\/postgres\/product\/13\/db_1\/\/bin\/pg_config\n<\/pre>\n
Once that is ready, getting pg_auto_failover installed is quite simple:<\/p>\n
\npostgres@pgaf1:~$ git clone https:\/\/github.com\/citusdata\/pg_auto_failover.git\nCloning into 'pg_auto_failover'...\nremote: Enumerating objects: 252, done.\nremote: Counting objects: 100% (252\/252), done.\nremote: Compressing objects: 100% (137\/137), done.\nremote: Total 8131 (delta 134), reused 174 (delta 115), pack-reused 7879\nReceiving objects: 100% (8131\/8131), 5.07 MiB | 1.25 MiB\/s, done.\nResolving deltas: 100% (6022\/6022), done.\npostgres@pgaf1:~$ cd pg_auto_failover\/\npostgres@pgaf1:~$ make\nmake -C src\/monitor\/ all\nmake[1]: Entering directory '\/home\/postgres\/pg_auto_failover\/src\/monitor'\ngcc -std=c99 -D_GNU_SOURCE -g -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -Wformat -Wall -Werror=implicit-int -Werror=implicit-function-declaration -Werror=return-type -Wno-declaration-after-statement -Wno-missing-braces  -fPIC -std=c99 -Wall -Werror -Wno-unused-parameter -Iinclude -I\/u01\/app\/postgres\/product\/13\/db_1\/include -g -I. -I.\/ -I\/u01\/app\/postgres\/product\/13\/db_1\/include\/server -I\/u01\/app\/postgres\/product\/13\/db_1\/include\/internal  -D_GNU_SOURCE -I\/usr\/include\/libxml2   -c -o metadata.o metadata.c\n...\nmake[2]: Leaving directory '\/home\/postgres\/pg_auto_failover\/src\/bin\/pg_autoctl'\nmake[1]: Leaving directory '\/home\/postgres\/pg_auto_failover\/src\/bin'\npostgres@pgaf1:~$ make install\nmake -C src\/monitor\/ all\nmake[1]: Entering directory '\/home\/postgres\/pg_auto_failover\/src\/monitor'\nmake[1]: Nothing to be done for 'all'.\n...\n<\/pre>\n
This needs to be done on all hosts, of course. You will notice a new extension and new binaries in your PostgreSQL installation:<\/p>\n
\npostgres@pgaf1:~$ ls \/u01\/app\/postgres\/product\/13\/db_1\/share\/extension\/*pgauto*\n\/u01\/app\/postgres\/product\/13\/db_1\/share\/extension\/pgautofailover--1.0--1.1.sql\n\/u01\/app\/postgres\/product\/13\/db_1\/share\/extension\/pgautofailover--1.0.sql\n\/u01\/app\/postgres\/product\/13\/db_1\/share\/extension\/pgautofailover--1.1--1.2.sql\n\/u01\/app\/postgres\/product\/13\/db_1\/share\/extension\/pgautofailover--1.2--1.3.sql\n\/u01\/app\/postgres\/product\/13\/db_1\/share\/extension\/pgautofailover--1.3--1.4.sql\n\/u01\/app\/postgres\/product\/13\/db_1\/share\/extension\/pgautofailover--1.4--dummy.sql\n\/u01\/app\/postgres\/product\/13\/db_1\/share\/extension\/pgautofailover--1.4.sql\n\/u01\/app\/postgres\/product\/13\/db_1\/share\/extension\/pgautofailover.control\npostgres@pgaf1:~$ ls \/u01\/app\/postgres\/product\/13\/db_1\/bin\/*auto*\n\/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_autoctl\n<\/pre>\n
Having that available we’ll need to initialize the pg_auto_failover monitor which is responsible for assigning roles and health-checking. We’ll do that in the first node:<\/p>\n
\npostgres@pgaf1:~$ export PGDATA=\/u02\/pgdata\/13\/monitor\npostgres@pgaf1:~$ export PGPORT=5433\npostgres@pgaf1:~$ pg_autoctl create monitor --ssl-self-signed --hostname pgaf1.it.dbi-services.com --auth trust --run\n14:45:40 13184 INFO  Using default --ssl-mode \"require\"\n14:45:40 13184 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic\n14:45:40 13184 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.\n14:45:40 13184 WARN  See https:\/\/www.postgresql.org\/docs\/current\/libpq-ssl.html for details\n14:45:40 13184 INFO  Initialising a PostgreSQL cluster at \"\/u02\/pgdata\/13\/monitor\"\n14:45:40 13184 INFO  \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_ctl initdb -s -D \/u02\/pgdata\/13\/monitor --option '--auth=trust'\n14:45:42 13184 INFO   \/usr\/bin\/openssl req -new -x509 -days 365 -nodes -text -out \/u02\/pgdata\/13\/monitor\/server.crt -keyout \/u02\/pgdata\/13\/monitor\/server.key -subj \"\/CN=pgaf1.it.dbi-services.com\"\n14:45:42 13184 INFO  Started pg_autoctl postgres service with pid 13204\n14:45:42 13184 INFO  Started pg_autoctl listener service with pid 13205\n14:45:42 13204 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_autoctl do service postgres --pgdata \/u02\/pgdata\/13\/monitor -v\n14:45:42 13209 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/postgres -D \/u02\/pgdata\/13\/monitor -p 5433 -h *\n14:45:42 13205 ERROR Connection to database failed: could not connect to server: No such file or directory\n14:45:42 13205 ERROR    Is the server running locally and accepting\n14:45:42 13205 ERROR    connections on Unix domain socket \"\/tmp\/.s.PGSQL.5433\"?\n14:45:42 13205 ERROR Failed to connect to local Postgres database at \"port=5433 dbname=postgres\", see above for details\n14:45:42 13205 ERROR Failed to create user \"autoctl\" on local postgres server\n14:45:42 13184 ERROR pg_autoctl service listener exited with exit status 12\n14:45:42 13184 INFO  Restarting service listener\n14:45:42 13204 INFO  Postgres is now serving PGDATA \"\/u02\/pgdata\/13\/monitor\" on port 5433 with pid 13209\n14:45:43 13221 WARN  NOTICE:  installing required extension \"btree_gist\"\n14:45:43 13221 INFO  Granting connection privileges on 192.168.22.0\/24\n14:45:43 13221 INFO  Your pg_auto_failover monitor instance is now ready on port 5433.\n14:45:43 13221 INFO  Monitor has been successfully initialized.\n14:45:43 13221 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_autoctl do service listener --pgdata \/u02\/pgdata\/13\/monitor -v\n14:45:43 13221 INFO  Managing the monitor at postgres:\/\/autoctl_node@pgaf1.it.dbi-services.com:5433\/pg_auto_failover?sslmode=require\n14:45:43 13221 INFO  Reloaded the new configuration from \"\/home\/postgres\/.config\/pg_autoctl\/u02\/pgdata\/13\/monitor\/pg_autoctl.cfg\"\n14:45:44 13221 INFO  The version of extension \"pgautofailover\" is \"1.4\" on the monitor\n14:45:44 13221 INFO  Contacting the monitor to LISTEN to its events.\n<\/pre>\n
This created a standard PostgreSQL cluster in the background:<\/p>\n
\npostgres@pgaf1:~$ ls \/u02\/pgdata\/13\/monitor\/\nbase              pg_dynshmem    pg_notify     pg_stat_tmp  pg_wal                         postmaster.opts\ncurrent_logfiles  pg_hba.conf    pg_replslot   pg_subtrans  pg_xact                        postmaster.pid\nglobal            pg_ident.conf  pg_serial     pg_tblspc    postgresql.auto.conf           server.crt\nlog               pg_logical     pg_snapshots  pg_twophase  postgresql-auto-failover.conf  server.key\npg_commit_ts      pg_multixact   pg_stat       PG_VERSION   postgresql.conf                startup.log\npostgres@pgaf1:~$ ps -ef | grep \"postgres -D\"\npostgres 13209 13204  0 14:45 pts\/0    00:00:00 \/u01\/app\/postgres\/product\/13\/db_1\/bin\/postgres -D \/u02\/pgdata\/13\/monitor -p 5433 -h *\n<\/pre>\n
Before we can initialize the primary instance we need to get the connection string to the monitor:<\/p>\n
\npostgres@pgaf1:~$ pg_autoctl show uri --monitor --pgdata \/u02\/pgdata\/13\/monitor\/\npostgres:\/\/autoctl_node@pgaf1.it.dbi-services.com:5433\/pg_auto_failover?sslmode=require\n<\/pre>\n
Create the primary:<\/p>\n
\npostgres@pgaf1:~$ pg_autoctl create postgres \n>     --hostname pgaf1.it.dbi-services.com \n>     --auth trust \n>     --ssl-self-signed \n>     --monitor 'postgres:\/\/autoctl_node@pgaf1.it.dbi-services.com:5433\/pg_auto_failover?sslmode=require' \n>     --run\n14:52:11 13354 INFO  Using default --ssl-mode \"require\"\n14:52:11 13354 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic\n14:52:11 13354 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.\n14:52:11 13354 WARN  See https:\/\/www.postgresql.org\/docs\/current\/libpq-ssl.html for details\n14:52:11 13354 INFO  Started pg_autoctl postgres service with pid 13356\n14:52:11 13354 INFO  Started pg_autoctl node-active service with pid 13357\n14:52:11 13356 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_autoctl do service postgres --pgdata \/u02\/pgdata\/13\/PG1 -v\n14:52:11 13357 INFO  Registered node 1 (pgaf1.it.dbi-services.com:5432) with name \"node_1\" in formation \"default\", group 0, state \"single\"\n14:52:11 13357 INFO  Writing keeper state file at \"\/home\/postgres\/.local\/share\/pg_autoctl\/u02\/pgdata\/13\/PG1\/pg_autoctl.state\"\n14:52:11 13357 INFO  Writing keeper init state file at \"\/home\/postgres\/.local\/share\/pg_autoctl\/u02\/pgdata\/13\/PG1\/pg_autoctl.init\"\n14:52:11 13357 INFO  Successfully registered as \"single\" to the monitor.\n14:52:11 13357 INFO  FSM transition from \"init\" to \"single\": Start as a single node\n14:52:11 13357 INFO  Initialising postgres as a primary\n14:52:11 13357 INFO  Initialising a PostgreSQL cluster at \"\/u02\/pgdata\/13\/PG1\"\n14:52:11 13357 INFO  \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_ctl initdb -s -D \/u02\/pgdata\/13\/PG1 --option '--auth=trust'\n14:52:14 13357 INFO   \/usr\/bin\/openssl req -new -x509 -days 365 -nodes -text -out \/u02\/pgdata\/13\/PG1\/server.crt -keyout \/u02\/pgdata\/13\/PG1\/server.key -subj \"\/CN=pgaf1.it.dbi-services.com\"\n14:52:14 13385 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/postgres -D \/u02\/pgdata\/13\/PG1 -p 5432 -h *\n14:52:14 13357 INFO  CREATE DATABASE postgres;\n14:52:14 13356 INFO  Postgres is now serving PGDATA \"\/u02\/pgdata\/13\/PG1\" on port 5432 with pid 13385\n14:52:14 13357 INFO  The database \"postgres\" already exists, skipping.\n14:52:14 13357 INFO  CREATE EXTENSION pg_stat_statements;\n14:52:14 13357 INFO   \/usr\/bin\/openssl req -new -x509 -days 365 -nodes -text -out \/u02\/pgdata\/13\/PG1\/server.crt -keyout \/u02\/pgdata\/13\/PG1\/server.key -subj \"\/CN=pgaf1.it.dbi-services.com\"\n14:52:14 13357 INFO  Contents of \"\/u02\/pgdata\/13\/PG1\/postgresql-auto-failover.conf\" have changed, overwriting\n14:52:14 13357 INFO  Transition complete: current state is now \"single\"\n14:52:14 13357 INFO  keeper has been successfully initialized.\n14:52:14 13357 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_autoctl do service node-active --pgdata \/u02\/pgdata\/13\/PG1 -v\n14:52:14 13357 INFO  Reloaded the new configuration from \"\/home\/postgres\/.config\/pg_autoctl\/u02\/pgdata\/13\/PG1\/pg_autoctl.cfg\"\n14:52:14 13357 INFO  pg_autoctl service is running, current state is \"single\"\n<\/pre>\n
Repeating the same command on the second node (with a different –hostname) will initialize the first replica:<\/p>\n
\npostgres@pgaf2:~$ export PGDATA=\/u02\/pgdata\/13\/PG1\npostgres@pgaf2:~$ export PGPORT=5432\npostgres@pgaf2:~$ pg_autoctl create postgres \n>     --hostname pgaf2.it.dbi-services.com \n>     --auth trust \n>     --ssl-self-signed \n>     --monitor 'postgres:\/\/autoctl_node@pgaf1.it.dbi-services.com:5433\/pg_auto_failover?sslmode=require' \n>     --run\n14:54:09 13010 INFO  Using default --ssl-mode \"require\"\n14:54:09 13010 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic\n14:54:09 13010 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.\n14:54:09 13010 WARN  See https:\/\/www.postgresql.org\/docs\/current\/libpq-ssl.html for details\n14:54:09 13010 INFO  Started pg_autoctl postgres service with pid 13012\n14:54:09 13010 INFO  Started pg_autoctl node-active service with pid 13013\n14:54:09 13012 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_autoctl do service postgres --pgdata \/u02\/pgdata\/13\/PG1 -v\n14:54:09 13013 INFO  Registered node 2 (pgaf2.it.dbi-services.com:5432) with name \"node_2\" in formation \"default\", group 0, state \"wait_standby\"\n14:54:09 13013 INFO  Writing keeper state file at \"\/home\/postgres\/.local\/share\/pg_autoctl\/u02\/pgdata\/13\/PG1\/pg_autoctl.state\"\n14:54:09 13013 INFO  Writing keeper init state file at \"\/home\/postgres\/.local\/share\/pg_autoctl\/u02\/pgdata\/13\/PG1\/pg_autoctl.init\"\n14:54:09 13013 INFO  Successfully registered as \"wait_standby\" to the monitor.\n14:54:09 13013 INFO  FSM transition from \"init\" to \"wait_standby\": Start following a primary\n14:54:09 13013 INFO  Transition complete: current state is now \"wait_standby\"\n14:54:09 13013 INFO  New state for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432): single \u279c wait_primary\n14:54:09 13013 INFO  New state for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432): wait_primary \u279c wait_primary\n14:54:09 13013 INFO  Still waiting for the monitor to drive us to state \"catchingup\"\n14:54:09 13013 WARN  Please make sure that the primary node is currently running `pg_autoctl run` and contacting the monitor.\n14:54:09 13013 INFO  FSM transition from \"wait_standby\" to \"catchingup\": The primary is now ready to accept a standby\n14:54:09 13013 INFO  Initialising PostgreSQL as a hot standby\n14:54:09 13013 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_basebackup -w -d application_name=pgautofailover_standby_2 host=pgaf1.it.dbi-services.com port=5432 user=pgautofailover_replicator sslmode=require --pgdata \/u02\/pgdata\/13\/backup\/node_2 -U pgautofailover_replicator --verbose --progress --max-rate 100M --wal-method=stream --slot pgautofailover_standby_2\n14:54:09 13013 INFO  pg_basebackup: initiating base backup, waiting for checkpoint to complete\n14:54:15 13013 INFO  pg_basebackup: checkpoint completed\n14:54:15 13013 INFO  pg_basebackup: write-ahead log start point: 0\/2000028 on timeline 1\n14:54:15 13013 INFO  pg_basebackup: starting background WAL receiver\n14:54:15 13013 INFO      0\/23396 kB (0%), 0\/1 tablespace (...ta\/13\/backup\/node_2\/backup_label)\n14:54:16 13013 INFO   1752\/23396 kB (7%), 0\/1 tablespace (...ata\/13\/backup\/node_2\/base\/1\/2610)\n14:54:16 13013 INFO  23406\/23406 kB (100%), 0\/1 tablespace (...\/backup\/node_2\/global\/pg_control)\n14:54:16 13013 INFO  23406\/23406 kB (100%), 1\/1 tablespace                                         \n14:54:16 13013 INFO  pg_basebackup:\n14:54:16 13013 INFO   \n14:54:16 13013 INFO  write-ahead log end point: 0\/2000100\n14:54:16 13013 INFO  pg_basebackup:\n14:54:16 13013 INFO   \n14:54:16 13013 INFO  waiting for background process to finish streaming ...\n14:54:16 13013 INFO  pg_basebackup: syncing data to disk ...\n14:54:17 13013 INFO  pg_basebackup: renaming backup_manifest.tmp to backup_manifest\n14:54:17 13013 INFO  pg_basebackup: base backup completed\n14:54:17 13013 INFO  Creating the standby signal file at \"\/u02\/pgdata\/13\/PG1\/standby.signal\", and replication setup at \"\/u02\/pgdata\/13\/PG1\/postgresql-auto-failover-standby.conf\"\n14:54:17 13013 INFO   \/usr\/bin\/openssl req -new -x509 -days 365 -nodes -text -out \/u02\/pgdata\/13\/PG1\/server.crt -keyout \/u02\/pgdata\/13\/PG1\/server.key -subj \"\/CN=pgaf2.it.dbi-services.com\"\n14:54:17 13021 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/postgres -D \/u02\/pgdata\/13\/PG1 -p 5432 -h *\n14:54:19 13013 INFO  PostgreSQL started on port 5432\n14:54:19 13013 INFO  Fetched current list of 1 other nodes from the monitor to update HBA rules, including 1 changes.\n14:54:19 13013 INFO  Ensuring HBA rules for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432)\n14:54:19 13013 INFO  Transition complete: current state is now \"catchingup\"\n14:54:20 13012 INFO  Postgres is now serving PGDATA \"\/u02\/pgdata\/13\/PG1\" on port 5432 with pid 13021\n14:54:20 13013 INFO  keeper has been successfully initialized.\n14:54:20 13013 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_autoctl do service node-active --pgdata \/u02\/pgdata\/13\/PG1 -v\n14:54:20 13013 INFO  Reloaded the new configuration from \"\/home\/postgres\/.config\/pg_autoctl\/u02\/pgdata\/13\/PG1\/pg_autoctl.cfg\"\n14:54:20 13013 INFO  pg_autoctl service is running, current state is \"catchingup\"\n14:54:20 13013 INFO  Fetched current list of 1 other nodes from the monitor to update HBA rules, including 1 changes.\n14:54:20 13013 INFO  Ensuring HBA rules for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432)\n14:54:21 13013 INFO  Monitor assigned new state \"secondary\"\n14:54:21 13013 INFO  FSM transition from \"catchingup\" to \"secondary\": Convinced the monitor that I'm up and running, and eligible for promotion again\n14:54:21 13013 INFO  Creating replication slot \"pgautofailover_standby_1\"\n14:54:21 13013 INFO  Transition complete: current state is now \"secondary\"\n14:54:21 13013 INFO  New state for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432): primary \u279c primary\n<\/pre>\n
The last lines of the output confirm, that pgaf1 is the primary cluster and pgaf2 now hosts a replica. Lets do the same on the third node:<\/p>\n
\npostgres@pgaf3:~$ pg_autoctl create postgres \n>     --hostname pgaf3.it.dbi-services.com \n>     --auth trust \n>     --ssl-self-signed \n>     --monitor 'postgres:\/\/autoctl_node@pgaf1.it.dbi-services.com:5433\/pg_auto_failover?sslmode=require' \n>     --run\n14:57:19 12831 INFO  Using default --ssl-mode \"require\"\n14:57:19 12831 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic\n14:57:19 12831 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.\n14:57:19 12831 WARN  See https:\/\/www.postgresql.org\/docs\/current\/libpq-ssl.html for details\n14:57:19 12831 INFO  Started pg_autoctl postgres service with pid 12833\n14:57:19 12831 INFO  Started pg_autoctl node-active service with pid 12834\n14:57:19 12833 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_autoctl do service postgres --pgdata \/u02\/pgdata\/13\/PG1 -v\n14:57:19 12834 INFO  Registered node 3 (pgaf3.it.dbi-services.com:5432) with name \"node_3\" in formation \"default\", group 0, state \"wait_standby\"\n14:57:19 12834 INFO  Writing keeper state file at \"\/home\/postgres\/.local\/share\/pg_autoctl\/u02\/pgdata\/13\/PG1\/pg_autoctl.state\"\n14:57:19 12834 INFO  Writing keeper init state file at \"\/home\/postgres\/.local\/share\/pg_autoctl\/u02\/pgdata\/13\/PG1\/pg_autoctl.init\"\n14:57:19 12834 INFO  Successfully registered as \"wait_standby\" to the monitor.\n14:57:19 12834 INFO  FSM transition from \"init\" to \"wait_standby\": Start following a primary\n14:57:19 12834 INFO  Transition complete: current state is now \"wait_standby\"\n14:57:19 12834 INFO  New state for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432): primary \u279c join_primary\n14:57:20 12834 INFO  New state for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432): join_primary \u279c join_primary\n14:57:20 12834 INFO  Still waiting for the monitor to drive us to state \"catchingup\"\n14:57:20 12834 WARN  Please make sure that the primary node is currently running `pg_autoctl run` and contacting the monitor.\n14:57:20 12834 INFO  FSM transition from \"wait_standby\" to \"catchingup\": The primary is now ready to accept a standby\n14:57:20 12834 INFO  Initialising PostgreSQL as a hot standby\n14:57:20 12834 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_basebackup -w -d application_name=pgautofailover_standby_3 host=pgaf1.it.dbi-services.com port=5432 user=pgautofailover_replicator sslmode=require --pgdata \/u02\/pgdata\/13\/backup\/node_3 -U pgautofailover_replicator --verbose --progress --max-rate 100M --wal-method=stream --slot pgautofailover_standby_3\n14:57:20 12834 INFO  pg_basebackup: initiating base backup, waiting for checkpoint to complete\n14:57:20 12834 INFO  pg_basebackup: checkpoint completed\n14:57:20 12834 INFO  pg_basebackup: write-ahead log start point: 0\/4000028 on timeline 1\n14:57:20 12834 INFO  pg_basebackup: starting background WAL receiver\n14:57:20 12834 INFO      0\/23397 kB (0%), 0\/1 tablespace (...ta\/13\/backup\/node_3\/backup_label)\n14:57:20 12834 INFO  23406\/23406 kB (100%), 0\/1 tablespace (...\/backup\/node_3\/global\/pg_control)\n14:57:20 12834 INFO  23406\/23406 kB (100%), 1\/1 tablespace                                         \n14:57:20 12834 INFO  pg_basebackup: write-ahead log end point: 0\/4000100\n14:57:20 12834 INFO  pg_basebackup: waiting for background process to finish streaming ...\n14:57:20 12834 INFO  pg_basebackup: syncing data to disk ...\n14:57:22 12834 INFO  pg_basebackup: renaming backup_manifest.tmp to backup_manifest\n14:57:22 12834 INFO  pg_basebackup: base backup completed\n14:57:22 12834 INFO  Creating the standby signal file at \"\/u02\/pgdata\/13\/PG1\/standby.signal\", and replication setup at \"\/u02\/pgdata\/13\/PG1\/postgresql-auto-failover-standby.conf\"\n14:57:22 12834 INFO   \/usr\/bin\/openssl req -new -x509 -days 365 -nodes -text -out \/u02\/pgdata\/13\/PG1\/server.crt -keyout \/u02\/pgdata\/13\/PG1\/server.key -subj \"\/CN=pgaf3.it.dbi-services.com\"\n14:57:22 12841 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/postgres -D \/u02\/pgdata\/13\/PG1 -p 5432 -h *\n14:57:22 12834 INFO  PostgreSQL started on port 5432\n14:57:22 12834 INFO  Fetched current list of 2 other nodes from the monitor to update HBA rules, including 2 changes.\n14:57:22 12834 INFO  Ensuring HBA rules for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432)\n14:57:22 12834 INFO  Ensuring HBA rules for node 2 \"node_2\" (pgaf2.it.dbi-services.com:5432)\n14:57:22 12834 ERROR Connection to database failed: could not connect to server: No such file or directory\n14:57:22 12834 ERROR    Is the server running locally and accepting\n14:57:22 12834 ERROR    connections on Unix domain socket \"\/tmp\/.s.PGSQL.5432\"?\n14:57:22 12834 ERROR Failed to connect to local Postgres database at \"port=5432 dbname=postgres\", see above for details\n14:57:22 12834 ERROR Failed to reload the postgres configuration after adding the standby user to pg_hba\n14:57:22 12834 ERROR Failed to update the HBA entries for the new elements in the our formation \"default\" and group 0\n14:57:22 12834 ERROR Failed to update HBA rules after a base backup\n14:57:22 12834 ERROR Failed to transition from state \"wait_standby\" to state \"catchingup\", see above.\n14:57:22 12831 ERROR pg_autoctl service node-active exited with exit status 12\n14:57:22 12831 INFO  Restarting service node-active\n14:57:22 12845 INFO  Continuing from a previous `pg_autoctl create` failed attempt\n14:57:22 12845 INFO  PostgreSQL state at registration time was: PGDATA does not exists\n14:57:22 12845 INFO  FSM transition from \"wait_standby\" to \"catchingup\": The primary is now ready to accept a standby\n14:57:22 12845 INFO  Initialising PostgreSQL as a hot standby\n14:57:22 12845 INFO  Target directory exists: \"\/u02\/pgdata\/13\/PG1\", stopping PostgreSQL\n14:57:24 12833 INFO  Postgres is now serving PGDATA \"\/u02\/pgdata\/13\/PG1\" on port 5432 with pid 12841\n14:57:24 12833 INFO  Stopping pg_autoctl postgres service\n14:57:24 12833 INFO  \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_ctl --pgdata \/u02\/pgdata\/13\/PG1 --wait stop --mode fast\n14:57:24 12845 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_basebackup -w -d application_name=pgautofailover_standby_3 host=pgaf1.it.dbi-services.com port=5432 user=pgautofailover_replicator sslmode=require --pgdata \/u02\/pgdata\/13\/backup\/node_3 -U pgautofailover_replicator --verbose --progress --max-rate 100M --wal-method=stream --slot pgautofailover_standby_3\n14:57:24 12845 INFO  pg_basebackup:\n14:57:24 12845 INFO   \n14:57:24 12845 INFO  initiating base backup, waiting for checkpoint to complete\n14:57:24 12845 INFO  pg_basebackup:\n14:57:24 12845 INFO   \n14:57:24 12845 INFO  checkpoint completed\n14:57:24 12845 INFO  pg_basebackup:\n14:57:24 12845 INFO   \n14:57:24 12845 INFO  write-ahead log start point: 0\/5000028 on timeline 1\n14:57:24 12845 INFO  pg_basebackup:\n14:57:24 12845 INFO   \n14:57:24 12845 INFO  starting background WAL receiver\n14:57:24 12845 INFO      0\/23397 kB (0%), 0\/1 tablespace (...ta\/13\/backup\/node_3\/backup_label)\n14:57:25 12845 INFO  16258\/23397 kB (69%), 0\/1 tablespace (...3\/backup\/node_3\/base\/12662\/12512)\n14:57:25 12845 INFO  23406\/23406 kB (100%), 0\/1 tablespace (...\/backup\/node_3\/global\/pg_control)\n14:57:25 12845 INFO  23406\/23406 kB (100%), 1\/1 tablespace                                         \n14:57:25 12845 INFO  pg_basebackup: write-ahead log end point: 0\/5000100\n14:57:25 12845 INFO  pg_basebackup: waiting for background process to finish streaming ...\n14:57:25 12845 INFO  pg_basebackup: syncing data to disk ...\n14:57:27 12845 INFO  pg_basebackup:\n14:57:27 12845 INFO   \n14:57:27 12845 INFO  renaming backup_manifest.tmp to backup_manifest\n14:57:27 12845 INFO  pg_basebackup:\n14:57:27 12845 INFO   \n14:57:27 12845 INFO  base backup completed\n14:57:27 12845 INFO  Creating the standby signal file at \"\/u02\/pgdata\/13\/PG1\/standby.signal\", and replication setup at \"\/u02\/pgdata\/13\/PG1\/postgresql-auto-failover-standby.conf\"\n14:57:27 12845 INFO   \/usr\/bin\/openssl req -new -x509 -days 365 -nodes -text -out \/u02\/pgdata\/13\/PG1\/server.crt -keyout \/u02\/pgdata\/13\/PG1\/server.key -subj \"\/CN=pgaf3.it.dbi-services.com\"\n14:57:27 12881 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/postgres -D \/u02\/pgdata\/13\/PG1 -p 5432 -h *\n14:57:29 12845 INFO  PostgreSQL started on port 5432\n14:57:29 12845 INFO  Fetched current list of 2 other nodes from the monitor to update HBA rules, including 2 changes.\n14:57:29 12845 INFO  Ensuring HBA rules for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432)\n14:57:29 12845 INFO  Ensuring HBA rules for node 2 \"node_2\" (pgaf2.it.dbi-services.com:5432)\n14:57:29 12845 INFO  Transition complete: current state is now \"catchingup\"\n14:57:29 12845 INFO  keeper has been successfully initialized.\n14:57:29 12845 INFO   \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_autoctl do service node-active --pgdata \/u02\/pgdata\/13\/PG1 -v\n14:57:29 12845 INFO  Reloaded the new configuration from \"\/home\/postgres\/.config\/pg_autoctl\/u02\/pgdata\/13\/PG1\/pg_autoctl.cfg\"\n14:57:29 12845 INFO  pg_autoctl service is running, current state is \"catchingup\"\n14:57:29 12845 INFO  Fetched current list of 2 other nodes from the monitor to update HBA rules, including 2 changes.\n14:57:29 12845 INFO  Ensuring HBA rules for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432)\n14:57:29 12845 INFO  Ensuring HBA rules for node 2 \"node_2\" (pgaf2.it.dbi-services.com:5432)\n14:57:29 12845 INFO  Monitor assigned new state \"secondary\"\n14:57:29 12845 INFO  FSM transition from \"catchingup\" to \"secondary\": Convinced the monitor that I'm up and running, and eligible for promotion again\n14:57:29 12833 WARN  PostgreSQL was not running, restarted with pid 12881\n14:57:29 12845 INFO  Creating replication slot \"pgautofailover_standby_1\"\n14:57:29 12845 INFO  Creating replication slot \"pgautofailover_standby_2\"\n14:57:29 12845 INFO  Transition complete: current state is now \"secondary\"\n14:57:29 12845 INFO  New state for node 1 \"node_1\" (pgaf1.it.dbi-services.com:5432): primary \u279c primary\n<\/pre>\n
That really was quite simple. We now have two replicas synchronizing from the same primary:<\/p>\n
\npostgres=# select usename,application_name,client_hostname,sent_lsn,write_lsn,flush_lsn,replay_lsn,write_lag from pg_stat_replication ;\n          usename          |     application_name     |      client_hostname      | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag \n---------------------------+--------------------------+---------------------------+-----------+-----------+-----------+------------+-----------\n pgautofailover_replicator | pgautofailover_standby_2 | pgaf2.it.dbi-services.com | 0\/6000148 | 0\/6000148 | 0\/6000148 | 0\/6000148  | \n pgautofailover_replicator | pgautofailover_standby_3 | pgaf3.it.dbi-services.com | 0\/6000148 | 0\/6000148 | 0\/6000148 | 0\/6000148  | \n(2 rows)\n<\/pre>\n
If you prepare that well, it is a matter of a few minutes and a setup like this is up and runnning. For the setup part, one bit is missing: All these pg_autoctl commands did not detach from the console, but run in the foreground and everything stops if we cancel the commands or close the terminal.<\/p>\n
Luckily pg_auto_failover comes with a handy command to create a systemd service file:<\/p>\n
\npostgres@pgaf1:~$ pg_autoctl -q show systemd --pgdata \/u02\/pgdata\/13\/monitor\/ > pgautofailover.service\npostgres@pgaf1:~$ cat pgautofailover.service\n[Unit]\nDescription = pg_auto_failover\n\n[Service]\nWorkingDirectory = \/home\/postgres\nEnvironment = 'PGDATA=\/u02\/pgdata\/13\/monitor\/'\nUser = postgres\nExecStart = \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_autoctl run\nRestart = always\nStartLimitBurst = 0\n\n[Install]\nWantedBy = multi-user.target\n<\/pre>\n
This can easily be added to systemd so the monitor will start automatically:<\/p>\n
\npostgres@pgaf1:~$ sudo mv pgautofailover.service \/etc\/systemd\/system\npostgres@pgaf1:~$ sudo systemctl daemon-reload\npostgres@pgaf1:~$ sudo systemctl enable pgautofailover.service\nCreated symlink \/etc\/systemd\/system\/multi-user.target.wants\/pgautofailover.service \u2192 \/etc\/systemd\/system\/pgautofailover.service.\npostgres@pgaf1:~$ sudo systemctl start pgautofailover.service\n<\/pre>\n
From now the service will start automatically when the node boots up. Lets do the same for the PostgreSQL clusters:<\/p>\n
\npostgres@pgaf1:~$ pg_autoctl -q show systemd --pgdata \/u02\/pgdata\/13\/PG1\/ > postgresp1.service\npostgres@pgaf1:~$ cat postgresp1.service\n[Unit]\nDescription = pg_auto_failover\n\n[Service]\nWorkingDirectory = \/home\/postgres\nEnvironment = 'PGDATA=\/u02\/pgdata\/13\/PG1\/'\nUser = postgres\nExecStart = \/u01\/app\/postgres\/product\/13\/db_1\/bin\/pg_autoctl run\nRestart = always\nStartLimitBurst = 0\n\n[Install]\nWantedBy = multi-user.target\npostgres@pgaf1:~$ sudo mv postgresp1.service \/etc\/systemd\/system\npostgres@pgaf1:~$ sudo systemctl daemon-reload\npostgres@pgaf1:~$ sudo systemctl enable postgresp1.service\nCreated symlink \/etc\/systemd\/system\/multi-user.target.wants\/postgresp1.service \u2192 \/etc\/systemd\/system\/postgresp1.service.\npostgres@pgaf1:~$ sudo systemctl start postgresp1.service\n<\/pre>\n
Do the same on the remaing two nodes and reboot all systems. If all went fine pg_auto_failover and the PostgreSQL cluster will come up automatically:<\/p>\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\/6002320 |       yes |             primary |             primary\nnode_2 |     2 | pgaf2.it.dbi-services.com:5432 | 0\/6002320 |       yes |           secondary |           secondary\nnode_3 |     3 | pgaf3.it.dbi-services.com:5432 | 0\/6002320 |       yes |           secondary |           secondary\n<\/pre>\n
That’s it for the first part. In the next post we’ll look at how robust pg_auto_failover is, e.g. what happens when the first node, which also runs the monitor, goes down?<\/p>\n","protected":false},"excerpt":{"rendered":"
One the really cool things with PostgreSQL is, that you have plenty of choices when it comes to tooling. For high availability we usually go with Patroni, but there is also pg_auto_failover and this will be the topic of this post. Because of the recent announcement around CentOS we’ll go with Debian this time. What […]<\/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":[229],"tags":[77],"type_dbi":[],"class_list":["post-15389","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nEasy failover and switchover with pg_auto_failover - 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\/easy-failover-and-switchover-with-pg_auto_failover\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Easy failover and switchover with pg_auto_failover\" \/>\n<meta property=\"og:description\" content=\"One the really cool things with PostgreSQL is, that you have plenty of choices when it comes to tooling. For high availability we usually go with Patroni, but there is also pg_auto_failover and this will be the topic of this post. Because of the recent announcement around CentOS we’ll go with Debian this time. What […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-12-10T13:28:31+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=\"19 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\/easy-failover-and-switchover-with-pg_auto_failover\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Easy failover and switchover with pg_auto_failover\",\"datePublished\":\"2020-12-10T13:28:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/\"},\"wordCount\":533,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/\",\"name\":\"Easy failover and switchover with pg_auto_failover - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2020-12-10T13:28:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Easy failover and switchover with pg_auto_failover\"}]},{\"@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 &\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":"Easy failover and switchover with pg_auto_failover - 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\/easy-failover-and-switchover-with-pg_auto_failover\/","og_locale":"en_US","og_type":"article","og_title":"Easy failover and switchover with pg_auto_failover","og_description":"One the really cool things with PostgreSQL is, that you have plenty of choices when it comes to tooling. For high availability we usually go with Patroni, but there is also pg_auto_failover and this will be the topic of this post. Because of the recent announcement around CentOS we’ll go with Debian this time. What […]","og_url":"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/","og_site_name":"dbi Blog","article_published_time":"2020-12-10T13:28:31+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"19 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Easy failover and switchover with pg_auto_failover","datePublished":"2020-12-10T13:28:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/"},"wordCount":533,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/","url":"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/","name":"Easy failover and switchover with pg_auto_failover - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-12-10T13:28:31+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/easy-failover-and-switchover-with-pg_auto_failover\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Easy failover and switchover with pg_auto_failover"}]},{"@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 &\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\/15389","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=15389"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15389\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15389"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15389"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15389"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15389"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}