[pgpool-general: 2943] Pgpool cluster with PostgreSQL 9.3

[Alexandru Cardaniuc]

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?


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.

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