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

[Yugo Nagata]

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


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