{"id":9820,"date":"2017-03-16T17:21:52","date_gmt":"2017-03-16T16:21:52","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/"},"modified":"2017-03-16T17:21:52","modified_gmt":"2017-03-16T16:21:52","slug":"vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/","title":{"rendered":"Vertically scale your PostgreSQL infrastructure with pgpool – 1 – Basic setup and watchdog configuration"},"content":{"rendered":"

I have written some posts on how you can make your PostgreSQL deployment high available by using PostgreSQL’s streaming replication<\/a> feature in the past ( 1<\/a>, 2<\/a> ). The main issue you’ll have to resolve with such a setup is how the application can be made aware of a new master when a fail over happened. You could use EDB Failover Manager (1<\/a>, 2<\/a>, 3<\/a>, 4<\/a>) for that because it provides the functionality to move a VIP from one host to another so the application can always connect to the very same IP address no matter where the current master is running (EDB EFM requires a subscription). You could also use Pacemaker<\/a> and Corosync<\/a> for that. But, which is the scope of this post, you can also use pgpool<\/a> which is widely known in the PostgreSQL community. When you configure it the right way you can even spread your read operations over all hot standby servers in your configuration and only write operations go to the master. This allows you to vertically scale your PostgreSQL deployment by adding more standby nodes when you need more resources. Lets go …<\/p>\n

<\/p>\n

To start with a picture is always a good idea. This is what we want to setup:<\/p>\n

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

We will have two nodes dedicated to pgpool (centos7_pgpool_m1\/m2). pgpool will be running in a watchdog configuration so that one node can take over in case the other goes down. pgpool will provide a virtual IP address for the clients to connect to (which fails over to the surviving node in case a node goes down for any reason). In the background there are two nodes which host the PostgreSQL 9.6.2 primary and hot standby instances (centos7_pgpool_1\/2). At the very beginning the master is running on centos7_pgpool_1 although that does not really matter once the whole setup is completed.<\/p>\n

I’ll not describe the setup of the PostgreSQL master->standby setup. When you need assistance there take a look here<\/a>, here<\/a> or search the web, there are many great howtos.<\/p>\n

Lets start by installing pgpool onto the hosts dedicated for pgpool (centos7_pgpool_m1\/m2):<\/p>\n

You can download pgpool here<\/a>. As pgpool requires libpq<\/a> we’ll just install the PostgreSQL binaries on the hosts dedicated for pgpool as well before proceeding with the installation of pgpool. Of course these steps need to be done on both hosts (centos7_pgpool_m1\/m2):<\/p>\n

\n[root@centos7_pgpool_m1 ~]$ groupadd postgres\n[root@centos7_pgpool_m1 ~]$ useradd -g postgres postgres\n[root@centos7_pgpool_m1 ~]$ passwd postgres\n[root@centos7_pgpool_m1 ~]$ mkdir -p \/u01\/app\/postgres\/software\n[root@centos7_pgpool_m1 ~]$ chown -R postgres:postgres \/u01\/app\/postgres\n[root@centos7_pgpool_m1 ~]$ su - postgres\n[postgres@centos7_pgpool_m1 ~]$ cd \/u01\/app\/postgres\/software\/\n[postgres@centos7_pgpool_m1 software]$ wget https:\/\/ftp.postgresql.org\/pub\/source\/v9.6.2\/postgresql-9.6.2.tar.bz2\n[postgres@centos7_pgpool_m1 software]$ tar -axf postgresql-9.6.2.tar.bz2\n[postgres@centos7_pgpool_m1 software]$ cd postgresql-9.6.2\n[postgres@centos7_pgpool_m1 postgresql-9.6.2]$ PGHOME=\/u01\/app\/postgres\/product\/96\/db_2\n[postgres@centos7_pgpool_m1 postgresql-9.6.2]$ SEGSIZE=2\n[postgres@centos7_pgpool_m1 postgresql-9.6.2]$ BLOCKSIZE=8\n[postgres@centos7_pgpool_m1 postgresql-9.6.2]$ WALSEGSIZE=16\n[postgres@centos7_pgpool_m1 postgresql-9.6.2]$ .\/configure --prefix=${PGHOME} \\\n>             --exec-prefix=${PGHOME} \\\n>             --bindir=${PGHOME}\/bin \\\n>             --libdir=${PGHOME}\/lib \\\n>             --sysconfdir=${PGHOME}\/etc \\\n>             --includedir=${PGHOME}\/include \\\n>             --datarootdir=${PGHOME}\/share \\\n>             --datadir=${PGHOME}\/share \\\n>             --with-pgport=5432 \\\n>             --with-perl \\\n>             --with-python \\\n>             --with-tcl \\\n>             --with-openssl \\\n>             --with-pam \\\n>             --with-ldap \\\n>             --with-libxml \\\n>             --with-libxslt \\\n>             --with-segsize=${SEGSIZE} \\\n>             --with-blocksize=${BLOCKSIZE} \\\n>             --with-wal-segsize=${WALSEGSIZE}  \\\n>             --with-extra-version=\" dbi services build\"\n[postgres@centos7_pgpool_m1 postgresql-9.6.2]$ make world\n[postgres@centos7_pgpool_m1 postgresql-9.6.2]$ make install-world\n[postgres@centos7_pgpool_m1 postgresql-9.6.2]$ cd ..\n[postgres@centos7_pgpool_m1 software]$ rm -rf postgresql-9.6.2*\n### download pgpool\n[postgres@centos7_pgpool_m1 software]$ ls\npgpool-II-3.6.1.tar.gz\n[postgres@centos7_pgpool_m1 software]$ tar -axf pgpool-II-3.6.1.tar.gz \n[postgres@centos7_pgpool_m1 software]$ cd pgpool-II-3.6.1\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ export PATH=\/u01\/app\/postgres\/product\/96\/db_2\/bin\/:$PATH\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ .\/configure --prefix=\/u01\/app\/postgres\/product\/pgpool-II\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ make\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ make install\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ cd src\/sql\/pgpool-recovery\/\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ make\n[postgres@centos7_pgpool_m1 pgpool-recovery]$ make install\n[postgres@centos7_pgpool_m1 pgpool-recovery]$ cd ..\/pgpool-regclass\/\n[postgres@centos7_pgpool_m1 pgpool-regclass]$ make\n[postgres@centos7_pgpool_m1 pgpool-regclass]$ make install\n<\/pre>\n
Copy the generated extensions to the PostgreSQL master and standby servers:<\/p>\n
\n[postgres@centos7_pgpool_m1 ~]$ cd \/u01\/app\/postgres\/software\/pgpool-II-3.6.1\n# master node\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_recovery.control 192.168.22.34:\/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_recovery.control\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_recovery--1.1.sql 192.168.22.34:\/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_recovery--1.1.sql\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool-recovery.sql 192.168.22.34:\/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool-recovery.sql\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/lib\/pgpool-recovery.so 192.168.22.34:\/u01\/app\/postgres\/product\/96\/db_2\/lib\/pgpool-recovery.so\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_regclass--1.0.sql 192.168.22.34:\/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_regclass--1.0.sql\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_regclass.control 192.168.22.34:\/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_regclass.control\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/lib\/pgpool-regclass.so 192.168.22.34:\/u01\/app\/postgres\/product\/96\/db_2\/lib\/pgpool-regclass.so\n# standby node\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_recovery.control 192.168.22.35:\/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_recovery.control\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_recovery--1.1.sql 192.168.22.35:\/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_recovery--1.1.sql\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool-recovery.sql 192.168.22.35:\/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool-recovery.sql\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/lib\/pgpool-recovery.so 192.168.22.35:\/u01\/app\/postgres\/product\/96\/db_2\/lib\/pgpool-recovery.so\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_regclass--1.0.sql 192.168.22.35:\/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_regclass--1.0.sql\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_regclass.control 192.168.22.35:\/u01\/app\/postgres\/product\/96\/db_2\/share\/extension\/pgpool_regclass.control\n[postgres@centos7_pgpool_m1 pgpool-II-3.6.1]$ scp \/u01\/app\/postgres\/product\/96\/db_2\/lib\/pgpool-regclass.so 192.168.22.35:\/u01\/app\/postgres\/product\/96\/db_2\/lib\/pgpool-regclass.so\n<\/pre>\n
Install the extensions on the master node only (this will be replicated to the standby node automatically as the PostgreSQL instances already operate in hot_standby mode):<\/p>\n
\npostgres@pgpool1:\/u01\/app\/postgres\/product\/96\/db_2\/ [PG1] psql template1\npsql (9.6.2 dbi services build)\nType \"help\" for help.\n\n(postgres@[local]:5432) [template1] > create extension pgpool_recovery;\nCREATE EXTENSION\n(postgres@[local]:5432) [template1] > create extension pgpool_regclass;\nCREATE EXTENSION\n(postgres@[local]:5432) [template1] > \\dx\n                                List of installed extensions\n      Name       | Version |   Schema   |                    Description                     \n-----------------+---------+------------+----------------------------------------------------\n pgpool_recovery | 1.1     | public     | recovery functions for pgpool-II for V3.4 or later\n pgpool_regclass | 1.0     | public     | replacement for regclass\n plpgsql         | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(3 rows)\n<\/pre>\n
Create the pgpool.conf configuration file on both nodes. For node 1 (centos7_pgpool_m1):<\/p>\n
\necho \"echo \"listen_addresses = '*'\nport = 5432\nsocket_dir = '\/tmp'\npcp_port = 9898\npcp_socket_dir = '\/tmp'\nbackend_hostname0 = '192.168.22.34'\nbackend_port0 = 5432\nbackend_weight0 = 1\nbackend_data_directory0 = '\/u02\/pgdata\/PG1'\nbackend_flag0 = 'ALLOW_TO_FAILOVER'\nbackend_hostname1 = '192.168.22.35'\nbackend_port1 = 5432\nbackend_weight1 = 1\nbackend_data_directory1 = '\/u02\/pgdata\/PG1'\nbackend_flag1 = 'ALLOW_TO_FAILOVER'\nenable_pool_hba = off\npool_passwd = 'pool_passwd'\nauthentication_timeout = 60\nssl = off\nnum_init_children = 32\nmax_pool = 4\nchild_life_time = 300\nchild_max_connections = 0\nconnection_life_time = 0\nclient_idle_limit = 0\nlog_destination = 'stderr'\nprint_timestamp = on\nlog_connections = off\nlog_hostname = off\nlog_statement = off\nlog_per_node_statement = off\nlog_standby_delay = 'none'\nsyslog_facility = 'LOCAL0'\nsyslog_ident = 'pgpool'\ndebug_level = 0\npid_file_name = '\/tmp\/pgpool.pid'\nlogdir = '\/tmp\/pgpool'\nconnection_cache = on\nreset_query_list = 'ABORT; DISCARD ALL'\nreplication_mode = off\nreplicate_select = off\ninsert_lock = on\nlobj_lock_table = ''\nreplication_stop_on_mismatch = off\nfailover_if_affected_tuples_mismatch = off\nload_balance_mode = off\nignore_leading_white_space = on\nwhite_function_list = ''\nblack_function_list = 'nextval,setval'\nmaster_slave_mode = on\nmaster_slave_sub_mode = 'stream'\nsr_check_period = 0\nsr_check_user = 'postgres'\nsr_check_password = ''\ndelay_threshold = 0\nfollow_master_command = ''\nparallel_mode = off\npgpool2_hostname = 'centos7_pgpool_m2'\nsystem_db_hostname  = 'localhost'\nsystem_db_port = 5432\nsystem_db_dbname = 'pgpool'\nsystem_db_schema = 'pgpool_catalog'\nsystem_db_user = 'pgpool'\nsystem_db_password = ''\nhealth_check_period = 20\nhealth_check_timeout = 20\nhealth_check_user = 'postgres'\nhealth_check_password = ''\nhealth_check_max_retries = 0\nhealth_check_retry_delay = 1\nfailover_command = '\/home\/postgres\/failover.sh %d \"%h\" %p %D %m %M \"%H\" %P'\nfailback_command = ''\nfail_over_on_backend_error = on\nsearch_primary_node_timeout = 10\nrecovery_user = 'postgres'\nrecovery_password = ''\nrecovery_1st_stage_command = 'resync_master.sh'\nrecovery_2nd_stage_command = ''\nrecovery_timeout = 90\nclient_idle_limit_in_recovery = 0\nuse_watchdog = on\ntrusted_servers = ''\nping_path = '\/usr\/bin'\nwd_hostname = 'centos7_pgpool_m1'\nwd_port = 9000\nwd_authkey = ''\nother_pgpool_hostname0 = 'centos7_pgpool_m2'\nother_pgpool_port0 = 5432\nother_wd_port0 = 9000\ndelegate_IP = '192.168.22.38'\nifconfig_path = '\/usr\/bin'\nif_up_cmd = 'ifconfig enp0s8:0 inet \\$_IP_\\$ netmask 255.255.255.0'\nif_down_cmd = 'ifconfig enp0s8:0 down'\narping_path = '\/usr\/sbin'\narping_cmd = 'arping -U \\$_IP_\\$ -w 1'\nclear_memqcache_on_escalation = on\nwd_escalation_command = ''\nwd_lifecheck_method = 'heartbeat'\nwd_interval = 10\nwd_heartbeat_port = 9694\nwd_heartbeat_keepalive = 2\nwd_heartbeat_deadtime = 30\nheartbeat_destination0 = 'host0_ip1'\nheartbeat_destination_port0 = 9694\nheartbeat_device0 = ''\nwd_life_point = 3\nwd_lifecheck_query = 'SELECT 1'\nwd_lifecheck_dbname = 'template1'\nwd_lifecheck_user = 'nobody'\nwd_lifecheck_password = ''\nrelcache_expire = 0\nrelcache_size = 256\ncheck_temp_table = on\nmemory_cache_enabled = off\nmemqcache_method = 'shmem'\nmemqcache_memcached_host = 'localhost'\nmemqcache_memcached_port = 11211\nmemqcache_total_size = 67108864\nmemqcache_max_num_cache = 1000000\nmemqcache_expire = 0\nmemqcache_auto_cache_invalidation = on\nmemqcache_maxcache = 409600\nmemqcache_cache_block_size = 1048576\nmemqcache_oiddir = '\/var\/log\/pgpool\/oiddir'\nwhite_memqcache_table_list = ''\nblack_memqcache_table_list = ''\n\" > \/u01\/app\/postgres\/product\/pgpool-II\/etc\/pgpool.conf\n<\/pre>\n
For node 2 (centos7_pgpool_m2):<\/p>\n
\necho \"echo \"listen_addresses = '*'\nport = 5432\nsocket_dir = '\/tmp'\npcp_port = 9898\npcp_socket_dir = '\/tmp'\nbackend_hostname0 = '192.168.22.34'\nbackend_port0 = 5432\nbackend_weight0 = 1\nbackend_data_directory0 = '\/u02\/pgdata\/PG1'\nbackend_flag0 = 'ALLOW_TO_FAILOVER'\nbackend_hostname1 = '192.168.22.35'\nbackend_port1 = 5432\nbackend_weight1 = 1\nbackend_data_directory1 = '\/u02\/pgdata\/PG1'\nbackend_flag1 = 'ALLOW_TO_FAILOVER'\nenable_pool_hba = off\npool_passwd = 'pool_passwd'\nauthentication_timeout = 60\nssl = off\nnum_init_children = 32\nmax_pool = 4\nchild_life_time = 300\nchild_max_connections = 0\nconnection_life_time = 0\nclient_idle_limit = 0\nlog_destination = 'stderr'\nprint_timestamp = on\nlog_connections = off\nlog_hostname = off\nlog_statement = off\nlog_per_node_statement = off\nlog_standby_delay = 'none'\nsyslog_facility = 'LOCAL0'\nsyslog_ident = 'pgpool'\ndebug_level = 0\npid_file_name = '\/tmp\/pgpool.pid'\nlogdir = '\/tmp\/pgpool'\nconnection_cache = on\nreset_query_list = 'ABORT; DISCARD ALL'\nreplication_mode = off\nreplicate_select = off\ninsert_lock = on\nlobj_lock_table = ''\nreplication_stop_on_mismatch = off\nfailover_if_affected_tuples_mismatch = off\nload_balance_mode = off\nignore_leading_white_space = on\nwhite_function_list = ''\nblack_function_list = 'nextval,setval'\nmaster_slave_mode = on\nmaster_slave_sub_mode = 'stream'\nsr_check_period = 0\nsr_check_user = 'postgres'\nsr_check_password = ''\ndelay_threshold = 0\nfollow_master_command = ''\nparallel_mode = off\npgpool2_hostname = 'centos7_pgpool_m2'\nsystem_db_hostname  = 'localhost'\nsystem_db_port = 5432\nsystem_db_dbname = 'pgpool'\nsystem_db_schema = 'pgpool_catalog'\nsystem_db_user = 'pgpool'\nsystem_db_password = ''\nhealth_check_period = 20\nhealth_check_timeout = 20\nhealth_check_user = 'postgres'\nhealth_check_password = ''\nhealth_check_max_retries = 0\nhealth_check_retry_delay = 1\nfailover_command = '\/home\/postgres\/failover.sh %d \"%h\" %p %D %m %M \"%H\" %P'\nfailback_command = ''\nfail_over_on_backend_error = on\nsearch_primary_node_timeout = 10\nrecovery_user = 'postgres'\nrecovery_password = ''\nrecovery_1st_stage_command = 'resync_master.sh'\nrecovery_2nd_stage_command = ''\nrecovery_timeout = 90\nclient_idle_limit_in_recovery = 0\nuse_watchdog = on\ntrusted_servers = ''\nping_path = '\/usr\/bin'\nwd_hostname = 'centos7_pgpool_m2'\nwd_port = 9000\nwd_authkey = ''\nother_pgpool_hostname0 = 'centos7_pgpool_m1'\nother_pgpool_port0 = 5432\nother_wd_port0 = 9000\ndelegate_IP = '192.168.22.38'\nifconfig_path = '\/usr\/sbin'\nif_up_cmd = 'ifconfig enp0s8:0 inet \\$_IP_\\$ netmask 255.255.255.0'\nif_down_cmd = 'ifconfig enp0s8:0 down'\narping_path = '\/usr\/sbin'\narping_cmd = 'arping -U \\$_IP_\\$ -w 1'\nclear_memqcache_on_escalation = on\nwd_escalation_command = ''\nwd_lifecheck_method = 'heartbeat'\nwd_interval = 10\nwd_heartbeat_port = 9694\nwd_heartbeat_keepalive = 2\nwd_heartbeat_deadtime = 30\nheartbeat_destination0 = 'host0_ip1'\nheartbeat_destination_port0 = 9694\nheartbeat_device0 = ''\nwd_life_point = 3\nwd_lifecheck_query = 'SELECT 1'\nwd_lifecheck_dbname = 'template1'\nwd_lifecheck_user = 'nobody'\nwd_lifecheck_password = ''\nrelcache_expire = 0\nrelcache_size = 256\ncheck_temp_table = on\nmemory_cache_enabled = off\nmemqcache_method = 'shmem'\nmemqcache_memcached_host = 'localhost'\nmemqcache_memcached_port = 11211\nmemqcache_total_size = 67108864\nmemqcache_max_num_cache = 1000000\nmemqcache_expire = 0\nmemqcache_auto_cache_invalidation = on\nmemqcache_maxcache = 409600\nmemqcache_cache_block_size = 1048576\nmemqcache_oiddir = '\/var\/log\/pgpool\/oiddir'\nwhite_memqcache_table_list = ''\nblack_memqcache_table_list = ''\n\" > \/u01\/app\/postgres\/product\/pgpool-II\/etc\/pgpool.conf\n<\/pre>\n
For switching the VIP from one host to another pgpool must be able to bring up and shutdown the virtual interface. You could use sudo for that or change the suid<\/a> bit on the ifconfig and arping binaries: <\/p>\n
\n[postgres@centos7_pgpool_m1 pgpool-II]$ sudo chmod u+s \/usr\/sbin\/arping\n[postgres@centos7_pgpool_m1 pgpool-II]$ sudo chmod u+s \/sbin\/ifconfig\n<\/pre>\n
The other important configuration file for pgpool is the pcp.conf<\/a> file. This file holds the authentication for pgpool itself and requires a user name and a md5 hashed password. To generate the password you can use the pg_md5 utility which comes with the installation of pgpool:<\/p>\n
\n[postgres@centos7_pgpool_m1 ~]$ \/u01\/app\/postgres\/product\/pgpool-II\/bin\/pg_md5 --prompt --username postgres\npassword: \ne8a48653851e28c69d0506508fb27fc5\n<\/pre>\n
Once you have the hashed password we can create the pcp.conf file (on both pgpool nodes of course):<\/p>\n
\n[postgres@centos7_pgpool_m1 ~]$ echo \"postgres:e8a48653851e28c69d0506508fb27fc5\" > \/u01\/app\/postgres\/product\/pgpool-II\/etc\/pcp.conf\n<\/pre>\n
Before doing anything else we need to allow connections from the pgpool nodes to the database nodes by adjusting the pg_hba.conf<\/a> file for both PostgreSQL instances. On both nodes:<\/p>\n
\npostgres@pgpool1:\/home\/postgres\/ [PG1] echo \"host    all             postgres        192.168.22.36\/32         trust\" >> \/u02\/pgdata\/PG1\/pg_hba.conf\npostgres@pgpool1:\/home\/postgres\/ [PG1] echo \"host    all             postgres        192.168.22.37\/32         trust\" >> \/u02\/pgdata\/PG1\/pg_hba.conf\npostgres@pgpool1:\/home\/postgres\/ [PG1] pg_ctl -D \/u02\/pgdata\/PG1\/ reload\n<\/pre>\n
Before we start pgpool on both pgpool nodes lets take a look at the important watchdog parameters on node 1:<\/p>\n
\nping_path = '\/usr\/bin'\nwd_hostname = 'centos7_pgpool_m2'\nwd_port = 9000\nwd_authkey = ''\nother_pgpool_hostname0 = 'centos7_pgpool_m1'\nother_pgpool_port0 = 5432\nother_wd_port0 = 9000\ndelegate_IP = '192.168.22.38'\nifconfig_path = '\/usr\/sbin'\nif_up_cmd = 'ifconfig enp0s8:0 inet \\$_IP_\\$ netmask 255.255.255.0'\nif_down_cmd = 'ifconfig enp0s8:0 down'\narping_path = '\/usr\/sbin'\narping_cmd = 'arping -U \\$_IP_\\$ -w 1\n<\/pre>\n
The various *path* variables are obvious, they tell pgpool where to find the binaries for ping<\/a>, arping<\/a> and ifconfig<\/a> (you can also use the ip<\/a> command instead). The other0* variables specify which other host runs a pgpool instance on which pgpool and watchdog ports. This is essential for the communication between the two pgpool hosts. And then we have the commands to bring up the virtual interface and to bring it down (if_up_cmd,if_down_cmd). In addition we need an address for the virtual interface which is specified by the “delegate_IP” variable. Lets see if it works and start pgpool on both nodes:<\/p>\n
\n# node 1\n[postgres@centos7_pgpool_m1 ~]$ \/u01\/app\/postgres\/product\/pgpool-II\/bin\/pgpool\n[postgres@centos7_pgpool_m1 ~]$ \n# node 2\n[postgres@centos7_pgpool_m2 ~]$ \/u01\/app\/postgres\/product\/pgpool-II\/bin\/pgpool\n[postgres@centos7_pgpool_m2 ~]$ \n<\/pre>\n
Looks not so bad as no issues are printed to the screen. When everything went fine we should see the a new virtual IP (192.168.22.38) on one of the nodes (node2 in my case):<\/p>\n
\n[postgres@centos7_pgpool_m2 ~]$ ip a\n1: lo:  mtu 65536 qdisc noqueue state UNKNOWN qlen 1\n    link\/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00\n    inet 127.0.0.1\/8 scope host lo\n       valid_lft forever preferred_lft forever\n    inet6 ::1\/128 scope host \n       valid_lft forever preferred_lft forever\n2: enp0s3:  mtu 1500 qdisc pfifo_fast state UP qlen 1000\n    link\/ether 08:00:27:d6:95:ab brd ff:ff:ff:ff:ff:ff\n    inet 10.0.2.15\/24 brd 10.0.2.255 scope global dynamic enp0s3\n       valid_lft 85216sec preferred_lft 85216sec\n    inet6 fe80::a00:27ff:fed6:95ab\/64 scope link \n       valid_lft forever preferred_lft forever\n3: enp0s8:  mtu 1500 qdisc pfifo_fast state UP qlen 1000\n    link\/ether 08:00:27:5c:b0:e5 brd ff:ff:ff:ff:ff:ff\n    inet 192.168.22.37\/24 brd 192.168.22.255 scope global enp0s8\n       valid_lft forever preferred_lft forever\n    inet 192.168.22.38\/24 brd 192.168.22.255 scope global secondary enp0s8:0\n       valid_lft forever preferred_lft forever\n    inet6 fe80::a00:27ff:fe5c:b0e5\/64 scope link tentative dadfailed \n       valid_lft forever preferred_lft forever\n<\/pre>\n
When we shutdown pgpool on the node where the VIP is currently running it should be switched to the other node automatically, so shutdown pgpool on the node where it is running currently:<\/p>\n
\n[postgres@centos7_pgpool_m2 ~]$ \/u01\/app\/postgres\/product\/pgpool-II\/bin\/pgpool -m fast stop\n2017-03-16 17:54:02: pid 2371: LOG:  stop request sent to pgpool. waiting for termination...\n.done.\n<\/pre>\n
Check the other host for the VIP:<\/p>\n
\n[postgres@centos7_pgpool_m1 ~]$ ip a\n1: lo:  mtu 65536 qdisc noqueue state UNKNOWN qlen 1\n    link\/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00\n    inet 127.0.0.1\/8 scope host lo\n       valid_lft forever preferred_lft forever\n    inet6 ::1\/128 scope host \n       valid_lft forever preferred_lft forever\n2: enp0s3:  mtu 1500 qdisc pfifo_fast state UP qlen 1000\n    link\/ether 08:00:27:d6:95:ab brd ff:ff:ff:ff:ff:ff\n    inet 10.0.2.15\/24 brd 10.0.2.255 scope global dynamic enp0s3\n       valid_lft 85067sec preferred_lft 85067sec\n    inet6 fe80::a00:27ff:fed6:95ab\/64 scope link \n       valid_lft forever preferred_lft forever\n3: enp0s8:  mtu 1500 qdisc pfifo_fast state UP qlen 1000\n    link\/ether 08:00:27:5c:b0:e5 brd ff:ff:ff:ff:ff:ff\n    inet 192.168.22.36\/24 brd 192.168.22.255 scope global enp0s8\n       valid_lft forever preferred_lft forever\n    inet 192.168.22.38\/24 brd 192.168.22.255 scope global secondary enp0s8:0\n       valid_lft forever preferred_lft forever\n    inet6 fe80::a00:27ff:fe5c:b0e5\/64 scope link tentative dadfailed \n       valid_lft forever preferred_lft forever\n<\/pre>\n
Cool, now we have a VIP the application can connect to which switches between the pgpool hosts automatically in case the host where it currently runs on experiences an issue or is shutdown intentionally. There is a pcp command<\/a> which shows you more details in regards to the watchdog:<\/p>\n
\n[postgres@centos7_pgpool_m1 ~]$ \/u01\/app\/postgres\/product\/pgpool-II\/bin\/pcp_watchdog_info \nPassword: \n2 YES centos7_pgpool_m1:5432 Linux centos7_pgpool_m1 centos7_pgpool_m1\n\ncentos7_pgpool_m1:5432 Linux centos7_pgpool_m1 centos7_pgpool_m1 5432 9000 4 MASTER\ncentos7_pgpool_m2:5432 Linux centos7_pgpool_m2 centos7_pgpool_m2 5432 9000 7 STANDBY\n<\/pre>\n
As we now have a VIP we should be able to connect to the PostgreSQL backends by connecting to this VIP:<\/p>\n
\n[postgres@centos7_pgpool_m1 ~]$ psql -h 192.168.22.38\npsql (9.6.2 dbi services build)\nType \"help\" for help.\n\npostgres=# \\l\n                                  List of databases\n   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   \n-----------+----------+----------+-------------+-------------+-----------------------\n postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n(3 rows)\n<\/pre>\n
Ok, that works as well. What do we see on the PostgreSQL instances? On the master:<\/p>\n
\n(postgres@[local]:5432) [postgres] > select datname,client_addr,client_hostname from pg_stat_activity where client_addr is not null;\n datname  |  client_addr  | client_hostname \n----------+---------------+-----------------\n postgres | 192.168.22.36 | NULL\n(1 row)\n<\/pre>\n
We see one connection from the first pgpool node. What do we see on the standby?<\/p>\n
\n(postgres@[local]:5432) [postgres] > select datname,client_addr,client_hostname from pg_stat_activity where client_addr is not null;\n datname  |  client_addr  | client_hostname \n----------+---------------+-----------------\n postgres | 192.168.22.36 | NULL\n(1 row)\n<\/pre>\n
One connection as well. Looks good.<\/p>\n
When you connect the PostgreSQL instances though pgpool there is a sql like syntax<\/a> for displaying pgpool stuff as well:<\/p>\n
\npostgres=# show pool_nodes;\n node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replicati\non_delay \n---------+---------------+------+--------+-----------+---------+------------+-------------------+----------\n---------\n 0       | 192.168.22.34 | 5432 | up     | 0.500000  | primary | 1          | true              | 0\n 1       | 192.168.22.35 | 5432 | up     | 0.500000  | standby | 0          | false             | 0\n(2 rows)\n<\/pre>\n
To summarize: We now have a pgpool instance running on two nodes. Only one of these nodes hosts the VIP and the VIP switches to the other host in case there is an issue. Client connections from now on can go the VIP and pgpool will redirect the connection to one of the PostgreSQL nodes (depending if it is a write or a pure read operation).<\/p>\n
In the next post we’ll dig deeper into the pgpool configuration, how you can tell on which instance you actually landed and how we can instruct pgpool to automatically promote a new master, dsiconnect the old master and the rebuild the old master as a new standby that follows the new master.<\/p>\n","protected":false},"excerpt":{"rendered":"
I have written some posts on how you can make your PostgreSQL deployment high available by using PostgreSQL’s streaming replication feature in the past ( 1, 2 ). The main issue you’ll have to resolve with such a setup is how the application can be made aware of a new master when a fail over […]<\/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-9820","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 - 1 - Basic setup and watchdog configuration - 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-1-basic-setup-and-watchdog-configuration\/\" \/>\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 - 1 - Basic setup and watchdog configuration\" \/>\n<meta property=\"og:description\" content=\"I have written some posts on how you can make your PostgreSQL deployment high available by using PostgreSQL’s streaming replication feature in the past ( 1, 2 ). The main issue you’ll have to resolve with such a setup is how the application can be made aware of a new master when a fail over […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-03-16T16:21:52+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=\"18 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-1-basic-setup-and-watchdog-configuration\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Vertically scale your PostgreSQL infrastructure with pgpool – 1 – Basic setup and watchdog configuration\",\"datePublished\":\"2017-03-16T16:21:52+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/\"},\"wordCount\":1048,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/#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-1-basic-setup-and-watchdog-configuration\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/\",\"name\":\"Vertically scale your PostgreSQL infrastructure with pgpool - 1 - Basic setup and watchdog configuration - 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-1-basic-setup-and-watchdog-configuration\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-architecture.png\",\"datePublished\":\"2017-03-16T16:21:52+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-1-basic-setup-and-watchdog-configuration\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/#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-1-basic-setup-and-watchdog-configuration\/#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 – 1 – Basic setup and watchdog configuration\"}]},{\"@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 - 1 - Basic setup and watchdog configuration - 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-1-basic-setup-and-watchdog-configuration\/","og_locale":"en_US","og_type":"article","og_title":"Vertically scale your PostgreSQL infrastructure with pgpool - 1 - Basic setup and watchdog configuration","og_description":"I have written some posts on how you can make your PostgreSQL deployment high available by using PostgreSQL’s streaming replication feature in the past ( 1, 2 ). The main issue you’ll have to resolve with such a setup is how the application can be made aware of a new master when a fail over […]","og_url":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/","og_site_name":"dbi Blog","article_published_time":"2017-03-16T16:21:52+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":"18 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Vertically scale your PostgreSQL infrastructure with pgpool – 1 – Basic setup and watchdog configuration","datePublished":"2017-03-16T16:21:52+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/"},"wordCount":1048,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/#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-1-basic-setup-and-watchdog-configuration\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/","url":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/","name":"Vertically scale your PostgreSQL infrastructure with pgpool - 1 - Basic setup and watchdog configuration - 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-1-basic-setup-and-watchdog-configuration\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/pgpool-architecture.png","datePublished":"2017-03-16T16:21:52+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-1-basic-setup-and-watchdog-configuration\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/vertically-scale-your-postgresql-infrastructure-with-pgpool-1-basic-setup-and-watchdog-configuration\/#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-1-basic-setup-and-watchdog-configuration\/#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 – 1 – Basic setup and watchdog configuration"}]},{"@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\/9820","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=9820"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9820\/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=9820"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9820"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9820"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9820"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}