{"id":9885,"date":"2017-03-28T13:52:20","date_gmt":"2017-03-28T11:52:20","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/"},"modified":"2017-03-28T13:52:20","modified_gmt":"2017-03-28T11:52:20","slug":"vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/","title":{"rendered":"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 3 \u2013 Adding another standby"},"content":{"rendered":"

In the first<\/a> and second<\/a> post in this series we did the basic pgpool setup including the watchdog configuration and then did a simple setup for automatically failover from a PostgreSQL master instance in case it goes down for any reason. In addition we told pgpool how an old master instance can be reconfigured as a new standby instance that follows the new master. In this post we’ll add another standby instance and then teach pgpool how a standby can be made aware of a new master when the master fails. Sound interesting? Lets go…<\/p>\n

<\/p>\n

As reminder this is how the system looks like right now:<\/p>\n

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

What we want to have is:<\/p>\n

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

The idea behind a third node is that we always will have at least one standby server up and running in case the master node goes down. What do we need to do to bring in another instance? Once the operating system is up and running, PostgreSQL is installed it is actually quite easy. As a first step lets create the new standby database on the third node using exactly the same layout as on the other nodes:<\/p>\n

\npostgres@pgpool3:\/home\/postgres\/ [pg962] mkdir -p \/u02\/pgdata\/PG1\npostgres@pgpool3:\/home\/postgres\/ [pg962] cd \/u02\/pgdata\/PG1\npostgres@pgpool3:\/u02\/pgdata\/PG1\/ [pg962] pg_basebackup -h 192.168.22.34 -x -D \/u02\/pgdata\/PG1\/\npostgres@pgpool3:\/u02\/pgdata\/PG1\/ [pg962] echo \"standby_mode = 'on'\nprimary_conninfo = 'host=pgpool1 user=postgres'\nprimary_slot_name = 'standby2'\nrecovery_target_timeline = 'latest'\ntrigger_file='\/u02\/pgdata\/PG1\/failover_trigger'\" > recovery.conf\npostgres@pgpool3:\/u02\/pgdata\/PG1\/ [pg962] psql -h pgpool1 -c \"select * from pg_create_physical_replication_slot('standby2')\" postgres\npostgres@pgpool3:\/u02\/pgdata\/PG1\/ [PG1] pg_ctl -D \/u02\/pgdata\/PG1\/ start\n<\/pre>\n
Now we have one master instance with two standby instances attached. Lets configure the third instance into pool (the configuration change needs to be done on both pgpool nodes, of course). The lines we need to add to pgpool.conf are:<\/p>\n
\nbackend_hostname2 = '192.168.22.40'\nbackend_port2 = 5432\nbackend_weight2 = 1\nbackend_data_directory2 = '\/u02\/pgdata\/PG1'\nbackend_flag2 = 'ALLOW_TO_FAILOVER'\n<\/pre>\n
Reload pgpool (a stop is not necessary) and check the current status:<\/p>\n
\n[postgres@centos7_pgpool_m1 etc]$ pgpool reload\n[postgres@centos7_pgpool_m1 etc]$ 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.333333  | primary | 0          | true              | 0\n 1       | 192.168.22.35 | 5432 | up     | 0.333333  | standby | 0          | false             | 0\n 2       | 192.168.22.40 | 5432 | up     | 0.333333  | standby | 0          | false             | 0\n(3 rows)\n<\/pre>\n
We have a second standby database which is used to load balance read requests. In case the master fails now what we want is that one of the standby instances gets promoted and the remaining standby instance should be reconfigured to follow the new master. What do we need to do?<\/p>\n
As we are using replication slots<\/a> in this setup we need a way to make the failover scripts independent of the name of the replication slot. The first script that we need to change is “promote.sh” on all the PostgreSQL nodes because currently there is a hard coded request to create the replication slot:<\/p>\n
\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
The easiest way to do this is to create as many replication slots as you plan to add standby instances, e.g.:<\/p>\n
\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\npsql -c \"select * from pg_create_physical_replication_slot('standby2')\" postgres >> \/var\/tmp\/failover.log\npsql -c \"select * from pg_create_physical_replication_slot('standby3')\" postgres >> \/var\/tmp\/failover.log\npsql -c \"select * from pg_create_physical_replication_slot('standby4')\" postgres >> \/var\/tmp\/failover.log\n<\/pre>\n
Of course this is not a good way to do it as you would need to adjust the script every time the amount of standby instances changes. One better way to do it is to centrally manage the amount of standby instances and the relation of the standby instances to the replication slots in a configuration in the $PGDATA directory of each PostgreSQL node and on each pgpool node in the HOME directory of the postgres user:<\/p>\n
\npostgres@pgpool1:\/u02\/pgdata\/PG1\/ [PG1] cat pgpool_local.conf \n# the total amount of instances that\n# participate in this configuration\nINSTANCE_COUNT=3\n# the mapping of the hostname to\n# to the replication slot it uses for\n# the PostgreSQL instance it is running\n# in recovery mode\n192.168.22.34=standby1\n192.168.22.35=standby2\n192.168.22.40=standby3\n<\/pre>\n
Having this we can adjust the promote.sh script (sorry I have to use a screenshot as the source code destroys the formatting of this post. let me know if you want to have the script):
\n\"pgp-promote-sh\"<\/a><\/p>\n
Now the script will create all the replication slots on a promoted instance and in addition drops the replication slot it used before being promoted. What else do we need? As we now have a third instance in the configuration there is another point we need to take care of: When the master fails a new standby is promoted, so far so good. But in addition we want the second standby to follow the new master automatically, don’t we? For this we need to tell pgpool to call another script which is executed on the active pgpool node after failover:<\/p>\n
\n[postgres@centos7_pgpool_m1 ~]$ grep follow \/u01\/app\/postgres\/product\/pgpool-II\/etc\/pgpool.conf\nfollow_master_command = '\/home\/postgres\/follow_new_master.sh \"%h\" \"%H\"' \n<\/pre>\n
This will be executed when there is failover (all pgpool nodes need to have this script)<\/p>\n
\n#!\/bin\/sh\nset -x\nmaster_node_host_name=$2\ndetached_node_host_name=$1\ntmp=\/tmp\/mytemp$$\ntrap \"rm -f $tmp\" 0 1 2 3 15\nPGDATA=\"\/u02\/pgdata\/PG1\"\nPATH=\"\/u01\/app\/postgres\/product\/96\/db_2\/bin\/:$PATH\"\nRECOVERY_NODE_REPLICATION_SLOT=`cat \/home\/postgres\/pgpool_local.conf | grep ${detached_node_host_name} | awk -F '=' '{print $2}'`\nexport PATH PGDATA\n# make sure the instance is down\nssh -T $detached_node_host_name \/home\/postgres\/stop_instance.sh\ncat > $tmp <<EOF\nstandby_mode = 'on'\nprimary_conninfo = 'host=$master_node_host_name user=postgres'\nprimary_slot_name = '${RECOVERY_NODE_REPLICATION_SLOT}'\nrecovery_target_timeline = 'latest'\ntrigger_file='\/u02\/pgdata\/PG1\/failover_trigger'\nEOF\nscp $tmp $detached_node_host_name:$PGDATA\/recovery.conf\nssh ${detached_node_host_name} \/home\/postgres\/start_instance.sh\npsql -c "select 'done'" postgres\n<\/pre>\n
So, this is the status now:<\/p>\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_dela\ny \n---------+---------------+------+--------+-----------+---------+------------+-------------------+-----------------\n 0       | 192.168.22.34 | 5432 | up     | 0.333333  | primary | 4          | true              | 0\n 1       | 192.168.22.35 | 5432 | up     | 0.333333  | standby | 2          | false             | 0\n 2       | 192.168.22.40 | 5432 | up     | 0.333333  | standby | 0          | false             | 0\n(3 rows)\n<\/pre>\n
Lets shutdown the primary and see what happens:<\/p>\n
\npostgres@pgpool1:\/home\/postgres\/ [PG1] pg_ctl -D \/u02\/pgdata\/PG1\/ stop -m immediate\nwaiting for server to shut down.... done\nserver stopped\n<\/pre>\n
Pgpool is telling this:<\/p>\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_dela\ny \n---------+---------------+------+--------+-----------+---------+------------+-------------------+-----------------\n--\n 0       | 192.168.22.34 | 5432 | down   | 0.333333  | standby | 4          | false             | 0\n 1       | 192.168.22.35 | 5432 | up     | 0.333333  | primary | 4          | true              | 0\n 2       | 192.168.22.40 | 5432 | down   | 0.333333  | standby | 0          | false             | 0\n(3 rows)\n<\/pre>\n
Re-attach:<\/p>\n
\n[postgres@centos7_pgpool_m1 ~]$ pcp_attach_node -w -n 0\npcp_attach_node -- Command Successful\n[postgres@centos7_pgpool_m1 ~]$ pcp_attach_node -w -n 2\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_dela\ny \n---------+---------------+------+--------+-----------+---------+------------+-------------------+-----------------\n--\n 0       | 192.168.22.34 | 5432 | up     | 0.333333  | standby | 4          | false             | 0\n 1       | 192.168.22.35 | 5432 | up     | 0.333333  | primary | 4          | true              | 0\n 2       | 192.168.22.40 | 5432 | up     | 0.333333  | standby | 0          | false             | 0\n(3 rows)\n<\/pre>\n
Perfect. The only pain point is that we need to manually re-attach the nodes, everything else is automated. But, luckily there is way to get around this: As we are on the pgpool nodes when the script is executed we can just use pcp_attach_node at the end of the follow_new_master.sh script (and pass the node id %d into the script):<\/p>\n
\n[postgres@centos7_pgpool_m1 ~]$ grep follow \/u01\/app\/postgres\/product\/pgpool-II\/etc\/pgpool.conf\nfollow_master_command = '\/home\/postgres\/follow_new_master.sh \"%h\" \"%H\" %d' \n[postgres@centos7_pgpool_m1 ~]$ cat follow_new_master.sh \n#!\/bin\/sh\nset -x\nmaster_node_host_name=$2\ndetached_node_host_name=$1\ndetached_node_id=$3\ntmp=\/tmp\/mytemp$$\ntrap \"rm -f $tmp\" 0 1 2 3 15\nPGDATA=\"\/u02\/pgdata\/PG1\"\nPATH=\"\/u01\/app\/postgres\/product\/96\/db_2\/bin\/:$PATH\"\nRECOVERY_NODE_REPLICATION_SLOT=`cat \/home\/postgres\/pgpool_local.conf | grep ${detached_node_host_name} | awk -F '=' '{print $2}'`\nexport PATH PGDATA\n# make sure the old master is down\nssh -T $detached_node_host_name \/home\/postgres\/stop_instance.sh\ncat > $tmp <<EOF\nstandby_mode = 'on'\nprimary_conninfo = 'host=$master_node_host_name user=postgres'\nprimary_slot_name = '${RECOVERY_NODE_REPLICATION_SLOT}'\nrecovery_target_timeline = 'latest'\ntrigger_file='\/u02\/pgdata\/PG1\/failover_trigger'\nEOF\nscp $tmp $detached_node_host_name:$PGDATA\/recovery.conf\nssh ${detached_node_host_name} \/home\/postgres\/start_instance.sh\npsql -c "select 'done'" postgres\npcp_attach_node -w -n ${detached_node_id}\n<\/pre>\n
And now, when you shutdown the master everything is automatic. Hope this helps.<\/p>\n","protected":false},"excerpt":{"rendered":"
In the first and second post in this series we did the basic pgpool setup including the watchdog configuration and then did a simple setup for automatically failover from a PostgreSQL master instance in case it goes down for any reason. In addition we told pgpool how an old master instance can be reconfigured as […]<\/p>\n","protected":false},"author":29,"featured_media":9886,"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-9885","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 3 \u2013 Adding another standby - 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-3-adding-another-standby\/\" \/>\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 3 \u2013 Adding another standby\" \/>\n<meta property=\"og:description\" content=\"In the first and second post in this series we did the basic pgpool setup including the watchdog configuration and then did a simple setup for automatically failover from a PostgreSQL master instance in case it goes down for any reason. In addition we told pgpool how an old master instance can be reconfigured as […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-03-28T11:52:20+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-extended.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1015\" \/>\n\t<meta property=\"og:image:height\" content=\"564\" \/>\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-3-adding-another-standby\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 3 \u2013 Adding another standby\",\"datePublished\":\"2017-03-28T11:52:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/\"},\"wordCount\":709,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-extended.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-3-adding-another-standby\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/\",\"name\":\"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 3 \u2013 Adding another standby - 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-3-adding-another-standby\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-extended.png\",\"datePublished\":\"2017-03-28T11:52:20+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-3-adding-another-standby\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-extended.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-extended.png\",\"width\":1015,\"height\":564},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/#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 3 \u2013 Adding another standby\"}]},{\"@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 3 \u2013 Adding another standby - 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-3-adding-another-standby\/","og_locale":"en_US","og_type":"article","og_title":"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 3 \u2013 Adding another standby","og_description":"In the first and second post in this series we did the basic pgpool setup including the watchdog configuration and then did a simple setup for automatically failover from a PostgreSQL master instance in case it goes down for any reason. In addition we told pgpool how an old master instance can be reconfigured as […]","og_url":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/","og_site_name":"dbi Blog","article_published_time":"2017-03-28T11:52:20+00:00","og_image":[{"width":1015,"height":564,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-extended.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-3-adding-another-standby\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 3 \u2013 Adding another standby","datePublished":"2017-03-28T11:52:20+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/"},"wordCount":709,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-extended.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-3-adding-another-standby\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/","url":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/","name":"Vertically scale your PostgreSQL infrastructure with pgpool \u2013 3 \u2013 Adding another standby - 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-3-adding-another-standby\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-extended.png","datePublished":"2017-03-28T11:52:20+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-3-adding-another-standby\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-extended.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-extended.png","width":1015,"height":564},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby\/#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 3 \u2013 Adding another standby"}]},{"@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\/9885","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=9885"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9885\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/9886"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9885"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9885"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9885"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9885"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}