[pgpool-general: 2988] Re: Pgpool cluster with PostgreSQL 9.3

Mon Jun 30 14:29:17 JST 2014

Again thanks for checking, but do you feel any config could be changed
or modified for better performance or more optimal configuration?

Yugo Nagata <nagata at sraoss.co.jp> writes:

> Hi,
>
> On Sat, 21 Jun 2014 22:49:57 -0700 Alexandru Cardaniuc
> <cardaniuc at gmail.com> wrote:
>
>>  Hi All,
>> 
>> Need some validation of the configuration that I did for the cluster
>> of 2 pgpools with a cluster of 2 PostgreSQL 9.3 databases. I went
>> through all the documentation, but in some places it's not
>> completely clear, so I am not sure if I misunderstood some of the
>> things.
>> 
>> Here's the info.
>> 
>> Pgpool version: 3.3.3 and using watchdog (2 pgpools: 1 primary, 1
>> standby)
>> 
>> PostgreSQL version 9.3 (cluster of 2 databases with streaming
>> replication and hot standby configured).
>> 
>> This is my relevant PostgreSQL 9.3 config:
>> ------------------------------------------ listen_addresses = '*'
>> port = 5432 max_connections = 550 superuser_reserved_connections = 3
>> wal_level = hot_standby checkpoint_segments = 10
>> checkpoint_completion_target = 0.9 max_wal_senders = 10
>> wal_keep_segments = 100 hot_standby = on
>> ------------------------------------------ The documentation states
>> that: "To enable read-only queries on a standby server, wal_level
>> must be set to hot_standby on the primary, and hot_standby must be
>> enabled in the standby."
>> 
>> I use the same config above on both primary database and secondary
>> with the assumption that 'wal_level = hot_standby' is ignored on the
>> slave and 'hot_standby = on' is ignored on the master.
>> 
>> Is that a correct assumption? That assumption shouldn't create any
>> issues during pgpool/postgres failover?
>
> No problem. In both nodes, you can wal_level=hot_standby and
> hot_standby=on.
>
>> 
>> 
>> Also, about the pgpool config. This is the pgpool config for the 1st
>> pgpool:
>> --------------------------------------------------------------------
>> listen_addresses = '*' port = 9999 socket_dir = '/var/run/pgpool'
>> pcp_port = 9898 pcp_socket_dir = '/var/run/pgpool' num_init_children
>> = 250 child_life_time = 300 child_max_connections = 0
>> client_idle_limit = 0 enable_pool_hba = false pool_passwd =
>> pool_passwd' authentication_timeout = 60 print_timestamp = true
>> log_connections = true log_hostname = false log_statement = true
>> log_per_node_statement = false debug_level = 0 pid_file_name =
>> /var/run/pgpool/pgpool.pid' logdir = '/var/run/pgpool'
>> connection_cache = false health_check_period = 60
>> health_check_timeout = 15 health_check_user = 'postgres'
>> health_check_password = 'password' health_check_max_retries = 3
>> health_check_retry_delay = 1 search_primary_node_timeout = 10
>> failover_command = '/store/pgdata/failover_stream.sh %d %P %H %R
>> postgresql.trigger' failback_command = 'echo $(date) host:%h, new
>> master id:%m, old master id:%M >> /var/log/pgpool/failback.log'
>> follow_master_command = 'echo $(date) host:%h, new master id:%m, old
>> master id:%M >> /var/log/pgpool/master_failover.log'
>> fail_over_on_backend_error = false ignore_leading_white_space = true
>> backend_hostname0 = '10.0.90.11' backend_port0 = 5432
>> backend_weight0 = 1 backend_data_directory0 = '/store/pgdata'
>> backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = '10.0.90.12'
>> backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 =
>> /store/pgdata' backend_flag1 = 'ALLOW_TO_FAILOVER' ssl = false
>> max_pool = 1 connection_life_time = 0 reset_query_list = 'ABORT;
>> DISCARD ALL' replication_mode = false replication_stop_on_mismatch =
>> true replicate_select = false insert_lock = true recovery_user =
>> postgres' recovery_password = 'password' recovery_1st_stage_command
>> = 'base_backup.sh' recovery_2nd_stage_command = '' recovery_timeout
>> = 120 client_idle_limit_in_recovery = 1 lobj_lock_table = ''
>> master_slave_mode = true load_balance_mode = true
>> master_slave_sub_mode = 'stream' delay_threshold = 0 sr_check_period
>> = 0 sr_check_user = 'postgres' sr_check_password = 'password'
>> log_standby_delay = 'none' parallel_mode = false system_db_hostname
>> = 'localhost' system_db_port = 5432 system_db_dbname = 'pgpool'
>> system_db_schema = 'pgpool_catalog' system_db_user = 'pgpool'
>> system_db_password = '' pgpool2_hostname = '' memory_cache_enabled =
>> off use_watchdog = on wd_hostname = '10.0.90.11' wd_port = 9000
>> delegate_IP = '10.0.90.1' ifconfig_path = '/store/pgdata/sbin'
>> if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.0.0'
>> if_down_cmd = 'ifconfig eth0:0 down' arping_path =
>> /store/pgdata/sbin' arping_cmd = 'arping -U $_IP_$ -w 1'
>> wd_lifecheck_method = 'heartbeat' wd_interval = 10 wd_heartbeat_port
>> = 9694 wd_heartbeat_keepalive = 2 heartbeat_destination0 =
>> 10.0.90.12' heartbeat_destination_port0 = 9694
>> other_pgpool_hostname0 = '10.0.90.12' other_pgpool_port0 = 9999
>> other_wd_port0 = 9000
>> --------------------------------------------------------------------
>> 
>> second pgpool config file:
>> --------------------------------------------------------------------
>> listen_addresses = '*' port = 9999 socket_dir = '/var/run/pgpool'
>> pcp_port = 9898 pcp_socket_dir = '/var/run/pgpool' num_init_children
>> = 250 child_life_time = 300 child_max_connections = 0
>> client_idle_limit = 0 enable_pool_hba = false pool_passwd =
>> pool_passwd' authentication_timeout = 60 print_timestamp = true
>> log_connections = true log_hostname = false log_statement = true
>> log_per_node_statement = false debug_level = 0 pid_file_name =
>> /var/run/pgpool/pgpool.pid' logdir = '/var/run/pgpool'
>> connection_cache = false health_check_period = 60
>> health_check_timeout = 15 health_check_user = 'postgres'
>> health_check_password = 'password' health_check_max_retries = 3
>> health_check_retry_delay = 1 search_primary_node_timeout = 10
>> failover_command = '/store/pgdata/failover_stream.sh %d %P %H %R
>> postgresql.trigger' failback_command = 'echo $(date) host:%h, new
>> master id:%m, old master id:%M >> /var/log/pgpool/failback.log'
>> follow_master_command = 'echo $(date) host:%h, new master id:%m, old
>> master id:%M >> /var/log/pgpool/master_failover.log'
>> fail_over_on_backend_error = false ignore_leading_white_space = true
>> backend_hostname0 = '10.0.90.11' backend_port0 = 5432
>> backend_weight0 = 1 backend_data_directory0 = '/store/pgdata'
>> backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = '10.0.90.12'
>> backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 =
>> /store/pgdata' backend_flag1 = 'ALLOW_TO_FAILOVER' ssl = false
>> max_pool = 1 connection_life_time = 0 reset_query_list = 'ABORT;
>> DISCARD ALL' replication_mode = false replication_stop_on_mismatch =
>> true replicate_select = false insert_lock = true recovery_user =
>> postgres' recovery_password = 'password' recovery_1st_stage_command
>> = 'base_backup.sh' recovery_2nd_stage_command = '' recovery_timeout
>> = 120 client_idle_limit_in_recovery = 1 lobj_lock_table = ''
>> master_slave_mode = true load_balance_mode = true
>> master_slave_sub_mode = 'stream' delay_threshold = 0 sr_check_period
>> = 0 sr_check_user = 'postgres' sr_check_password = 'password'
>> log_standby_delay = 'none' parallel_mode = false system_db_hostname
>> = 'localhost' system_db_port = 5432 system_db_dbname = 'pgpool'
>> system_db_schema = 'pgpool_catalog' system_db_user = 'pgpool'
>> system_db_password = '' pgpool2_hostname = '' memory_cache_enabled =
>> off use_watchdog = on wd_hostname = '10.0.90.12' wd_port = 9000
>> delegate_IP = '10.0.90.1' ifconfig_path = '/store/pgdata/sbin'
>> if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.0.0'
>> if_down_cmd = 'ifconfig eth0:0 down' arping_path =
>> /store/pgdata/sbin' arping_cmd = 'arping -U $_IP_$ -w 1'
>> wd_lifecheck_method = 'heartbeat' wd_interval = 10 wd_heartbeat_port
>> = 9694 wd_heartbeat_keepalive = 2 heartbeat_destination0 =
>> 10.0.90.11' heartbeat_destination_port0 = 9694
>> other_pgpool_hostname0 = '10.0.90.11' other_pgpool_port0 = 9999
>> other_wd_port0 = 9000
>> --------------------------------------------------------------------
>> 
>> Anything wrong or missing in the configs? I need a cluster of
>> pgpools with watchdog and healthchecks monitoring a cluster of
>> postgres databases with streaming replication setup.
>
> I think no problem. I can't find heartbeat_deadtime, but I assume this
> is default value 30.
>
>>  Any help in review or suggestions based on your knowledge or
>> experience will be appreciated.
>> 
>> Sincerely, Alexandru
>> 
>> -- "A journey of a thousand miles begins with a single step." -
>> Ancient Proverb _______________________________________________
>> pgpool-general mailing list pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general

-- 
"A journey of a thousand miles begins with a single step."  
- Ancient Proverb