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

Alexandru Cardaniuc cardaniuc at gmail.com
Mon Nov 9 14:16:00 JST 2015 

Hi Yugo,

Is default value of wd_heartbeat_deadtime still 30? I don't see it in the
documentation manual...

On Tue, Jul 1, 2014 at 2:10 AM, Yugo Nagata <nagata at sraoss.co.jp> wrote:

> 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>
>



-- 
Sincerely yours,
Alexandru Cardaniuc
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20151108/b7a91c61/attachment.html>

More information about the pgpool-general mailing list