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

Yugo Nagata nagata at sraoss.co.jp
Tue Jul 1 18:10:09 JST 2014


On Sun, 29 Jun 2014 22:27:20 -0700
Alexandru Cardaniuc <cardaniuc at gmail.com> wrote:

> 
> wd_heartbeat_deadtime V3.3 -
>     If there are no heartbeat signal for the period specified by this option, watchdog regards it as failure of the remote pgpool-II. This works only heartbeat mode.
>     You need to restart pgpool-II if you change this value.
> 
> I don't have that in my config and the documentation doesn't say it's 30
> sec by default. Is it? Should I indicate it? What's a good default, I
> guess 30 sec? :)

Oh, the default value is missing from document. I'll fix this.
This is in pgpool.conf.sample;

wd_heartbeat_deadtime = 30              
                                    # Deadtime interval for heartbeat signal (sec)

I think this is a reasonable value as default since this is the same as 
Heartbeat (Pacemaker), which is a major clustering software.

> 
> 
> 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
> 
> -- 
> "The man who has gotten everything he wants is all in favor of peace
> and order."  
> - Jawaharlal Nehru
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


-- 
Yugo Nagata <nagata at sraoss.co.jp>


More information about the pgpool-general mailing list