{"id":9884,"date":"2017-03-24T15:18:27","date_gmt":"2017-03-24T14:18:27","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/"},"modified":"2017-03-24T15:18:27","modified_gmt":"2017-03-24T14:18:27","slug":"vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/","title":{"rendered":"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 2 \u2013 Automatic failover and reconfiguration"},"content":{"rendered":"

In the first post<\/a> of this little series we did the basic setup of pgpool<\/a> and configured the watchdog. So, as of now, pgpool uses a VIP<\/a> which is active on one of the pgpool nodes and failovers to the other node in case the node where the VIP currently runs on goes down for any reason. This provides the very same address for the application or clients to connect and eliminates the single point of failure<\/a> that you have when there is only one pgpool instance running. What is still missing is a mechanism on how we can automatically failover to a promoted standby instance in case the PostgreSQL master goes down. We even want to be able to reconfigure the old master as new standby that follows the new master automatically. This is what this post is about …<\/p>\n

<\/p>\n

Lets take a look at the picture again:<\/p>\n

\"pgpool-architecture\"<\/a><\/p>\n

What happens when we stop the standby instance? Currently both instances are in status “up” when you ask pgpool (notice that I connect to the 192.168.22.38 address which is the VIP that is managed by pgpool):<\/p>\n

[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c \"show pool_nodes\" postgres\n node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay \n---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------\n 0       | 192.168.22.34 | 5432 | up     | 0.500000  | primary | 0          | true              | 0\n 1       | 192.168.22.35 | 5432 | up     | 0.500000  | standby | 0          | false             | 0\n(2 rows)\n<\/pre>\n
Lets create a new connection first to see what happens to existing connections:<\/p>\n
[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 postgres\npsql (9.6.2 dbi services build)\nType \"help\" for help.\n\npostgres= select now();\n              now              \n-------------------------------\n 2017-03-24 10:53:16.077351+01\n(1 row)\n\n\npostgres=#\n<\/pre>\n
Shutdown the standby instance:<\/p>\n
postgres@pgpool2:\/home\/postgres\/ [PG1] psql -c \"select pg_is_in_recovery()\" postgres\n pg_is_in_recovery \n-------------------\n t\n(1 row)\n\npostgres@pgpool2:\/home\/postgres\/ [PG1] pg_ctl -D \/u02\/pgdata\/PG1\/ stop -m fast\nwaiting for server to shut down.... done\nserver stopped\n<\/pre>\n
What happened to our connection?<\/p>\n
postgres=# select now();\n              now              \n-------------------------------\n 2017-03-24 10:54:46.802984+01\n(1 row)\n\n<\/pre>\n
Nothing, we can just keep using it. What is pgpool telling us about our instances?<\/p>\n
[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c \"show pool_nodes\" postgres\n node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay \n---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------\n 0       | 192.168.22.34 | 5432 | up     | 0.500000  | primary | 4          | true              | 0\n 1       | 192.168.22.35 | 5432 | down   | 0.500000  | standby | 0          | false             | 0\n(2 rows)\n<\/pre>\n
As expected the standby node is now down. This is the easy case, just start the standby again and you are fine:<\/p>\n
postgres@pgpool2:\/home\/postgres\/ [PG1] pg_ctl -D \/u02\/pgdata\/PG1\/ start -m fast\nserver starting\npostgres@pgpool2:\/home\/postgres\/ [PG1] psql -c \"select pg_is_in_recovery()\" postgres\n pg_is_in_recovery \n-------------------\n t\n(1 row)\n<\/pre>\n
… and pgpool should detect this as well:<\/p>\n
[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c \"show pool_nodes\" postgres\n node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay \n---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------\n 0       | 192.168.22.34 | 5432 | up     | 0.500000  | primary | 5          | true              | 0\n 1       | 192.168.22.35 | 5432 | down   | 0.500000  | standby | 0          | false             | 0\n(2 rows)\n<\/pre>\n
No? What we need to do is to attach the node again:<\/p>\n
[postgres@centos7_pgpool_m1 ~]$ pcp_attach_node -n 1\nPassword: \npcp_attach_node -- Command Successful\n[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c \"show pool_nodes\" postgres\n node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay \n---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------\n 0       | 192.168.22.34 | 5432 | up     | 0.500000  | primary | 5          | true              | 0\n 1       | 192.168.22.35 | 5432 | up     | 0.500000  | standby | 0          | false             | 0\n<\/pre>\n
Now comes the not so easy part: What happens if the primary instance goes down? This is where the failover_command specified in pgpool.conf comes into the game:<\/p>\n
[postgres@centos7_pgpool_m1 etc]$ cat pgpool.conf | grep failover_command\nfailover_command = '\/home\/postgres\/failover.sh %d \"%h\" %p %D %m %M \"%H\" %P'\n<\/pre>\n
When pgpool detects that the master is gone the failover_command will be executed. This is a very simple failover script (located on the pgpool hosts). The parameters are passed into the script by pgpool automatically:<\/p>\n
#!\/bin\/sh -x\n# Execute command by failover.\n# special values:  %d = node id\n#                  %h = host name\n#                  %p = port number\n#                  %D = database cluster path\n#                  %m = new master node id\n#                  %M = old master node id\n#                  %H = new master node host name\n#                  %P = old primary node id\n#                  %% = '%' character\nfailed_node_id=$1\nfailed_host_name=$2\nfailed_port=$3\nfailed_db_cluster=$4\nnew_master_id=$5\nold_master_id=$6\nnew_master_host_name=$7\nold_primary_node_id=$8\n\nif [ $failed_node_id = $old_primary_node_id ];then\t# master failed\n    ssh -T postgres@$new_master_host_name \/home\/postgres\/promote.sh\nfi \n<\/pre>\n
The promote.sh script is very simple as well and must be available on all PostgreSQL nodes:<\/p>\n
#!\/bin\/bash\nPGDATA=\"\/u02\/pgdata\/PG1\"\nPATH=\"\/u01\/app\/postgres\/product\/96\/db_2\/bin\/:$PATH\"\nexport PATH PGDATA\npg_ctl promote -D ${PGDATA} >> \/var\/tmp\/failover.log\npsql -c \"select * from pg_create_physical_replication_slot('standby1')\" postgres >> \/var\/tmp\/failover.log\n<\/pre>\n
So once we shutdown the master this gets triggered and the standby is promoted. How can we re-attach the old master as a new standby? The magic is in here:<\/p>\n
[postgres@centos7_pgpool_m1 ~]$ grep recovery_1st_stage_command \/u01\/app\/postgres\/product\/pgpool-II\/etc\/pgpool.conf\nrecovery_1st_stage_command = 'recover.sh'\n<\/pre>\n
In the pgpool.conf file a script is specified which is called when a recovery is triggered. This script needs to be in PGDATA, otherwise it will not work. It looks like this (on both nodes, of course):<\/p>\n
postgres@pgpool1:\/u02\/pgdata\/PG1\/ [PG1] cat $PGDATA\/recover.sh \n#!\/bin\/sh\nset -x\nmaster_node_host_name=`hostname`\nmaster_db_cluster=$1\nrecovery_node_hostname=$2\nrecovery_db_cluster=$3\ntmp=\/tmp\/mytemp$$\ntrap \"rm -f $tmp\" 0 1 2 3 15\n\nPGDATA=\"\/u02\/pgdata\/PG1\"\nPATH=\"\/u01\/app\/postgres\/product\/96\/db_2\/bin\/:$PATH\"\n\nexport PATH PGDATA\n\n# make sure the old master is down\nssh -T $recovery_node_hostname \/home\/postgres\/stop_instance.sh\n# rewind the old master\nssh -T ${recovery_node_hostname} \/home\/postgres\/rewind.sh ${master_node_host_name}\n\ncat > $tmp <<EOF\nstandby_mode = 'on'\nprimary_conninfo = 'host=$master_node_host_name user=postgres'\nprimary_slot_name = 'standby1'\nrecovery_target_timeline = 'latest'\ntrigger_file='\/u02\/pgdata\/PG1\/failover_trigger'\nEOF\n\nscp $tmp $recovery_node_hostname:$recovery_db_cluster\/recovery.conf\n\nssh -T ${recovery_node_hostname} \/home\/postgres\/start_instance.sh\n<\/pre>\n
The “set -x” on the top is quite important because this script is execute from inside PostgreSQL and you’ll see the output in the PostgreSQL logfile. The scripts executed within the recovery.sh script here are:<\/p>\n
postgres@pgpool1:\/home\/postgres\/ [PG1] cat stop_instance.sh \n#!\/bin\/bash\nPGDATA=\"\/u02\/pgdata\/PG1\"\nPATH=\"\/u01\/app\/postgres\/product\/96\/db_2\/bin\/:$PATH\"\npg_ctl -D $PGDATA stop -m immediate \n\npostgres@pgpool1:\/home\/postgres\/ [PG1] cat rewind.sh \n#!\/bin\/bash\nmaster_node_host_name=$1\nPGDATA=\"\/u02\/pgdata\/PG1\"\nPATH=\"\/u01\/app\/postgres\/product\/96\/db_2\/bin\/:$PATH\"\npg_rewind --target-pgdata=${PGDATA} --source-server=\"port=5432 user=postgres dbname=postgres host=${master_node_host_name}\" >> \/var\/tmp\/recover.log\n\npostgres@pgpool1:\/home\/postgres\/ [PG1] cat start_instance.sh \n#!\/bin\/bash\nPGDATA=\"\/u02\/pgdata\/PG1\"\nPATH=\"\/u01\/app\/postgres\/product\/96\/db_2\/bin\/:$PATH\"\npg_ctl -w -s -D $PGDATA start 2>\/dev\/null 1>\/dev\/null < \/dev\/null &\n<\/pre>\n
Having this in place we can rebuild the old master with:<\/p>\n
[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c \"show pool_nodes\" postgres\n node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay \n---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------\n 0       | 192.168.22.34 | 5432 | down   | 0.500000  | standby | 0          | false             | 0\n 1       | 192.168.22.35 | 5432 | up     | 0.500000  | primary | 0          | true              | 0\n\n[postgres@centos7_pgpool_m1 ~]$ pcp_recovery_node -n 0 -w\npcp_recovery_node -- Command Successful\n\n[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38 -c \"show pool_nodes\" postgres\n node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay\n \n---------+---------------+------+--------+-----------+---------+------------+-------------------+------------------\n-\n 0       | 192.168.22.34 | 5432 | up     | 0.500000  | standby | 0          | false             | 0\n 1       | 192.168.22.35 | 5432 | up     | 0.500000  | primary | 0          | true              | 0\n<\/pre>\n
The output in the PostgreSQL logfile of the master looks then similar to this:<\/p>\n
++ hostname\n+ master_node_host_name=pgpool2\n+ master_db_cluster=\/u02\/pgdata\/PG1\n+ recovery_node_hostname=192.168.22.34\n+ recovery_db_cluster=\/u02\/pgdata\/PG1\n+ tmp=\/tmp\/mytemp10901\n+ trap 'rm -f \/tmp\/mytemp10901' 0 1 2 3 15\n+ PGDATA=\/u02\/pgdata\/PG1\n+ PATH=\/u01\/app\/postgres\/product\/96\/db_2\/bin\/:\/u01\/app\/postgres\/product\/96\/db_2bin:\/u01\/app\/postgres\/product\/96\/db_2\/bin:\/usr\/lib64\/qt-3.3\/bin:\/usr\/local\/bin:\/usr\/bin:\/usr\/local\/sbin:\/usr\/sbin:\/home\/postgres\/.local\/bin:\/home\/postgres\/bin:\/sbin:\/usr\/edb-bart\/bin:\/usr\/edb-efm\/bin:\/u01\/app\/postgres\/product\/pgpool-II\/bin:\/u01\/app\/postgres\/local\/dmk\/bin\n+ export PATH PGDATA\n+ ssh -T 192.168.22.34 \/home\/postgres\/stop_instance.sh\npg_ctl: PID file \"\/u02\/pgdata\/PG1\/postmaster.pid\" does not exist\nIs server running?\n+ ssh -T 192.168.22.34 \/home\/postgres\/rewind.sh pgpool2\n+ cat\n+ scp \/tmp\/mytemp10901 pgpool1:\/u02\/pgdata\/PG1\/recovery.conf\n+ ssh 192.168.22.34 \/home\/postgres\/start_instance.sh\n+ psql -c 'select '\\''done'\\''' postgres\n ?column? \n----------\n done\n(1 row)\n\nTime: 0.140 ms\n+ rm -f \/tmp\/mytemp10901\n<\/pre>\n
I have to admit that I needed a small hack: At the end of the recovery pgpool tries to execute $PGDATA\/pgpool_remote_start and I do not want to do it that way so I just created this file on both nodes:<\/p>\n
postgres@pgpool1:\/u02\/pgdata\/PG1\/ [PG1] cat $PGDATA\/pgpool_remote_start\nexit 0\n<\/pre>\n
In the next post we’ll add a second standby system and then teach pgpool how a remaining standby can be told to follow a new master automatically. We’ll need adjust the scripts here to be more dynamic as well then.<\/p>\n","protected":false},"excerpt":{"rendered":"
In the first post of this little series we did the basic setup of pgpool and configured the watchdog. So, as of now, pgpool uses a VIP which is active on one of the pgpool nodes and failovers to the other node in case the node where the VIP currently runs on goes down for […]<\/p>\n","protected":false},"author":29,"featured_media":9821,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[84,1043,77],"type_dbi":[],"class_list":["post-9884","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","tag-high-availability","tag-pgpool","tag-postgresql"],"acf":[],"yoast_head":"\nVertically scale your PostgreSQL infrastructure with pgpool \u2013 2 \u2013 Automatic failover and reconfiguration - 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\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 2 \u2013 Automatic failover and reconfiguration\" \/>\n<meta property=\"og:description\" content=\"In the first post of this little series we did the basic setup of pgpool and configured the watchdog. So, as of now, pgpool uses a VIP which is active on one of the pgpool nodes and failovers to the other node in case the node where the VIP currently runs on goes down for […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-03-24T14:18:27+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-architecture.png\" \/>\n\t<meta property=\"og:image:width\" content=\"884\" \/>\n\t<meta property=\"og:image:height\" content=\"691\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\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\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 2 \u2013 Automatic failover and reconfiguration\",\"datePublished\":\"2017-03-24T14:18:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/\"},\"wordCount\":569,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/pgpool-architecture.png\",\"keywords\":[\"High availability\",\"pgpool\",\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/\",\"name\":\"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 2 \u2013 Automatic failover and reconfiguration - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/pgpool-architecture.png\",\"datePublished\":\"2017-03-24T14:18:27+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/pgpool-architecture.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/pgpool-architecture.png\",\"width\":884,\"height\":691},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 2 \u2013 Automatic failover and reconfiguration\"}]},{\"@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":"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 2 \u2013 Automatic failover and reconfiguration - 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\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/","og_locale":"en_US","og_type":"article","og_title":"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 2 \u2013 Automatic failover and reconfiguration","og_description":"In the first post of this little series we did the basic setup of pgpool and configured the watchdog. So, as of now, pgpool uses a VIP which is active on one of the pgpool nodes and failovers to the other node in case the node where the VIP currently runs on goes down for […]","og_url":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/","og_site_name":"dbi Blog","article_published_time":"2017-03-24T14:18:27+00:00","og_image":[{"width":884,"height":691,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-architecture.png","type":"image\/png"}],"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\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 2 \u2013 Automatic failover and reconfiguration","datePublished":"2017-03-24T14:18:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/"},"wordCount":569,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-architecture.png","keywords":["High availability","pgpool","PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/","url":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/","name":"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 2 \u2013 Automatic failover and reconfiguration - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-architecture.png","datePublished":"2017-03-24T14:18:27+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-architecture.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-architecture.png","width":884,"height":691},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 2 \u2013 Automatic failover and reconfiguration"}]},{"@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\/9884","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=9884"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9884\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/9821"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9884"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9884"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9884"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9884"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}