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

Yugo Nagata nagata at sraoss.co.jp
Mon Nov 9 14:20:25 JST 2015


On Sun, 8 Nov 2015 21:16:00 -0800
Alexandru Cardaniuc <cardaniuc at gmail.com> wrote:

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

Yes, default is 30. This is an omission. I'll fix the document.

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


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


More information about the pgpool-general mailing list