[pgpool-general: 5911] Re: Master Election - Priority

Fri Feb 2 15:48:12 JST 2018

 Hi,    please find below an explample of one of our config files, about the log, unfortunately it is not available  

 # ----------------------------

 # pgPool-II configuration file

 # ----------------------------

 #

 # This file consists of lines of the form:

 #

 #      name = value

 #

 # Whitespace may be used.    Comments are introduced with "#" anywhere on a line.

 # The complete list of parameter names and allowed values can be found in the

 # pgPool-II documentation.

 #

 # This file is read on server startup and when the server receives a SIGHUP

 # signal.    If you edit the file on a running system, you have to SIGHUP the

 # server for the changes to take effect, or use "pgpool reload".    Some

 # parameters, which are marked below, require a server shutdown and restart to

 # take effect.

 #

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - PGPOOL CONNECTIONS

 #------------------------------------------------------------------------------

 #

 listen_addresses = '*'

 port = 50000

 socket_dir = '/pgpool_path/run'

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - pgpool Communication Manager

 #------------------------------------------------------------------------------

 #

 pcp_listen_addresses = '*'

 pcp_port = 50060

 pcp_socket_dir = '/pgpool_path/run'

 listen_backlog_multiplier = 4

 serialize_accept = off

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - Backend Connection

 #------------------------------------------------------------------------------

 #

 backend_hostname0 = 'dbserver43.domain'

 backend_port0 = '50000'

 backend_weight0 = 1

 backend_data_directory0 = '/db_path/data'

 backend_flag0 = 'ALLOW_TO_FAILOVER'

 backend_hostname1 = 'dbserver44.domain'

 backend_port1 = '50000'

 backend_weight1 = 1

 backend_data_directory1 = '/db_path/data'

 backend_flag1 = 'ALLOW_TO_FAILOVER'

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - Authentication

 #------------------------------------------------------------------------------

 #

 enable_pool_hba = on

 pool_passwd = 'pool_md5.conf'

 authentication_timeout = 60

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - SSL Connections

 #------------------------------------------------------------------------------

 #

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - POOLS

 #------------------------------------------------------------------------------

 #

 num_init_children = 1200

 max_pool = 2

 child_life_time = 60

 child_max_connections = 0

 connection_life_time = 0

 client_idle_limit = 0

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - LOGS

 #------------------------------------------------------------------------------

 log_destination = 'syslog,stderr'

 log_line_prefix = '%t: pid %p: '

 log_connections = on

 log_hostname = on

 log_statement = on

 log_per_node_statement = on

 log_standby_delay = 'if_over_threshold'

 syslog_facility = 'LOCAL6'

 syslog_ident = 'pgpool'

 log_error_verbosity = default

 client_min_messages = notice

 log_min_messages = warning

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - FILE LOCATIONS

 #------------------------------------------------------------------------------

 #

 pid_file_name = '/pgpool_path/run/pgpool_peskffr1.pid'

 logdir =    '/pgpool_path/run'

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - CONNECTION POOLING

 #------------------------------------------------------------------------------

 #

 reset_query_list = 'ABORT; DISCARD ALL'

 connection_cache = on

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - REPLICATION MODE

 #------------------------------------------------------------------------------

 replication_mode = off

 replicate_select = off

 insert_lock = off

 lobj_lock_table = ''

 replication_stop_on_mismatch = off

 failover_if_affected_tuples_mismatch = off

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - LOAD BALANCING MODE

 #------------------------------------------------------------------------------

 load_balance_mode = off

 ignore_leading_white_space = on

 white_function_list = ''

 black_function_list = 'currval,lastval,nextval,setval'

 database_redirect_preference_list = ''

 app_name_redirect_preference_list = ''

 allow_sql_comments = off

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - MASTER/SLAVE MODE

 #------------------------------------------------------------------------------

 master_slave_mode = on

 master_slave_sub_mode = 'stream'

 sr_check_period = 10

 sr_check_user = 'pgpool'

 sr_check_password = 'password'

 sr_check_database = 'postgres'

 delay_threshold =   10000000 (tel:10000000)

 follow_master_command = '/pgpool_path/scripts/follow_master.sh %d %P %m %H %D %r %h'

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - HEALTH CHECK

 #------------------------------------------------------------------------------

 #

 health_check_period = 5

 health_check_timeout = 3

 health_check_user = 'pgpool'

 health_check_password = 'password'

 health_check_database = 'postgres'

 health_check_max_retries = 3

 health_check_retry_delay = 2

 connect_timeout = 4000

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - FAILOVER AND FAILBACK

 #------------------------------------------------------------------------------

 failover_command = '/pgpool_path/scripts/failover.sh %d %P %H %R'

 failback_command = ''

 fail_over_on_backend_error = on

 search_primary_node_timeout = 0

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - ONLINE RECOVERY

 #------------------------------------------------------------------------------

 #

 recovery_user = 'nobody'

 recovery_password = ''

 recovery_1st_stage_command = ''

 recovery_2nd_stage_command = ''

 recovery_timeout = 90

 client_idle_limit_in_recovery = 0

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - WATCHDOG

 #------------------------------------------------------------------------------

 #

 #------------------------------------------------------------------------------

 # ACTUAL SETTINGS - WATCHDOG

 #------------------------------------------------------------------------------

 #

 use_watchdog = on

 delegate_IP = '100.01.01.200 (tel:100.01.01.200)'

 wd_port = 60011

 wd_authkey = 'db_name'

 wd_ipc_socket_dir = '/pgpool_path/run'

 wd_lifecheck_method = 'heartbeat'

 wd_lifecheck_dbname = 'template1'

 wd_heartbeat_port = 60051

 wd_heartbeat_deadtime = 30

 wd_heartbeat_keepalive = 2

 wd_monitoring_interfaces_list = 'eth0'

 wd_interval = 10

 wd_life_point = 3

 clear_memqcache_on_escalation = on

 wd_escalation_command = ''

 wd_de_escalation_command = ''

 #

 #Heartbeat settings for pgpool00.domain

 #Watchdog settings for    pgpool00.domain

 wd_hostname = 'pgpool00'

 wd_priority = 5

 heartbeat_destination0 = 'pgpool01.domain'

 heartbeat_destination_port0 = '50151'

 heartbeat_device0 = 'eth0'

 heartbeat_destination1 = 'pgpool02.domain'

 heartbeat_destination_port1 = '50151'

 heartbeat_device1 = 'eth0'

 heartbeat_destination2 = 'pgpool03.domain'

 heartbeat_destination_port2 = '50151'

 heartbeat_device2 = 'eth0'

 heartbeat_destination3 = 'pgpool04.domain'

 heartbeat_destination_port3 = '50151'

 heartbeat_device3 = 'eth0'

 heartbeat_destination4 = 'pgpool05.domain'

 heartbeat_destination_port4 = '50151'

 heartbeat_device4 = 'eth0'

 heartbeat_destination5 = 'pgpool06.domain'

 heartbeat_destination_port5 = '50151'

 heartbeat_device5 = 'eth0'

 #--

 #-- Other pgpool settings for srvmobiivlsk05

 #--

 other_pgpool_hostname0 = 'pgpool01'

 other_pgpool_port0 = 50000

 other_wd_port0 = 50111

 other_pgpool_hostname1 = 'pgpool02'

 other_pgpool_port1 = 50000

 other_wd_port1 = 50111

 other_pgpool_hostname2 = 'pgpool03'

 other_pgpool_port2 = 50000

 other_wd_port2 = 50111

 other_pgpool_hostname3 = 'pgpool90'

 other_pgpool_port3 = 50000

 other_wd_port3 = 50111

 other_pgpool_hostname4 = 'pgpool91'

 other_pgpool_port4 = 50000

 other_wd_port4 = 50111

 other_pgpool_hostname5 = 'pgpool92'

 other_pgpool_port5 = 50000

 other_wd_port5 = 50111

 if_cmd_path = '/pgpool_software/bin'

 if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'

 if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'

 arping_path = '/ pgpool_software/bin'

 arping_cmd = 'arping -I eth0 -U $_IP_$ -w 1'

 ##------------------------------------------------------------------------------

 # OTHERS

 #------------------------------------------------------------------------------

 #

 relcache_expire = 0

                                                                       # Life time of relation cache in seconds.

                                                                       # 0 means no cache expiration(the default).

                                                                       # The relation cache is used for cache the

                                                                       # query result against PostgreSQL system

                                                                       # catalog to obtain various information

                                                                       # including table structures or if it's a

                                                                       # temporary table or not. The cache is

                                                                       # maintained in a pgpool child local memory

                                                                       # and being kept as long as it survives.

                                                                       # If someone modify the table by using

                                                                       # ALTER TABLE or some such, the relcache is

                                                                        # not consistent anymore.

                                                                       # For this purpose, cache_expiration

                                                                       # controls the life time of the cache.

 relcache_size = 256

                                                                       # Number of relation cache

                                                                       # entry. If you see frequently:

                                                                                                                                       # "pool_search_relcache: cache replacement happend"

                                                                                                                                        # in the pgpool log, you might want to increate this number.

 check_temp_table = on

                                                                       # If on, enable temporary table check in SELECT statements.

                                                                       # This initiates queries against system catalog of primary/master

                                                                                                                                       # thus increases load of master.

                                                                                                                                       # If you are absolutely sure that your system never uses temporary tables

                                                                                                                                       # and you want to save access to primary/master, you could turn this off.

                                                                                                                                       # Default is on.

 check_unlogged_table = on

                                                                       # If on, enable unlogged table check in SELECT statements.

                                                                       # This initiates queries against system catalog of primary/master

                                                                       # thus increases load of master.

                                                                       # If you are absolutely sure that your system never uses unlogged tables

                                                                       # and you want to save access to primary/master, you could turn this off.

                                                                       # Default is on.

 #------------------------------------------------------------------------------

 # IN MEMORY QUERY MEMORY CACHE

 #------------------------------------------------------------------------------

 memory_cache_enabled = off

                                                                                                                                       # If on, use the memory cache functionality, off by default

 memqcache_method = 'shmem'

                                                                                                                                       # Cache storage method. either 'shmem'(shared memory) or

                                                                                                                                       # 'memcached'. 'shmem' by default

                                                                       # (change requires restart)

 memqcache_memcached_host = 'localhost'

                                                                                                                                        # Memcached host name or IP address. Mandatory if

                                                                                                                                       # memqcache_method = 'memcached'.

                                                                                                                                        # Defaults to localhost.

                                                                       # (change requires restart)

 memqcache_memcached_port = 11211

                                                                                                                                       # Memcached port number. Mondatory if memqcache_method = 'memcached'.

                                                                                                                                       # Defaults to 11211.

                                                                       # (change requires restart)

 memqcache_total_size =   67108864 (tel:67108864)

                                                                                                                                        # Total memory size in bytes for storing memory cache.

                                                                                                                                       # Mandatory if memqcache_method = 'shmem'.

                                                                                                                                       # Defaults to 64MB.

                                                                       # (change requires restart)

 memqcache_max_num_cache =   1000000 (tel:1000000)

                                                                                                                                       # Total number of cache entries. Mandatory

                                                                                                                                        # if memqcache_method = 'shmem'.

                                                                                                                                       # Each cache entry consumes 48 bytes on shared memory.

                                                                                                                                       # Defaults to 1,000,000(45.8MB).

                                                                       # (change requires restart)

 memqcache_expire = 0

                                                                                                                                       # Memory cache entry life time specified in seconds.

                                                                                                                                       # 0 means infinite life time. 0 by default.

                                                                       # (change requires restart)

 memqcache_auto_cache_invalidation = on

                                                                                                                                       # If on, invalidation of query cache is triggered by corresponding

                                                                                                                                       # DDL/DML/DCL(and memqcache_expire).    If off, it is only triggered

                                                                                                                                       # by memqcache_expire.    on by default.

                                                                       # (change requires restart)

 memqcache_maxcache = 409600

                                                                                                                                        # Maximum SELECT result size in bytes.

                                                                                                                                       # Must be smaller than memqcache_cache_block_size. Defaults to 400KB.

                                                                       # (change requires restart)

 memqcache_cache_block_size =   1048576 (tel:1048576)

                                                                                                                                       # Cache block size in bytes. Mandatory if memqcache_method = 'shmem'.

                                                                                                                                       # Defaults to 1MB.

                                                                       # (change requires restart)

 memqcache_oiddir = '/var/log/pgpool/oiddir'

                                                                                                                                       # Temporary work directory to record table oids

                                                                        # (change requires restart)

 white_memqcache_table_list = ''

                                                                       # Comma separated list of table names to memcache

                                                                       # that don't write to database

                                                                        # Regexp are accepted

 black_memqcache_table_list = ''

                                                                       # Comma separated list of table names not to memcache

                                                                       # that don't write to database

                                                                       # Regexp are accepted

>  
> On Jan 30, 2018 at 10:31 am,  <Muhammad Usama (mailto:m.usama at gmail.com)>  wrote:
>  
>  
>  
>
>  
> Hi Carlos,
>  
>
>  
> Can you please share the pgpool log and pgpool.conf files. Since you have 7 pgpool servers in the cluster so sending the log of old Master and newly elected Master would be enough.
>  
>
>  
> Thanks
>  
> Best Regards
>  
> Muhammad Usama
>  
>  
> >  
> >  From: Carlos Cosa  <carlos.cosa at yahoo.com (mailto:carlos.cosa at yahoo.com)>
> >  Subject: [pgpool-general: 5899] Master Election - Priority
> >  Date: Sat, 27 Jan 2018 03:15:11 +0000
> >  Message-ID:  <34c93dd6-5cfb-4fc7-a6ae-28d3cacf158c at E1194759-iPhone-iOS-1122-F17Q5M4GG5MN>
> >  
> >   >
> >   >
> >   >
> >   >    Hi all,
> >   >
> >   >
> >   >
> >   >  I have a system with 7 pgpool servers and 4 of them have wd_priority = 5 and the other 3 have wd_priority = 1. When I stop the Master to test the HA I have seen that sometimes a pgpool with priority 1 is elected even when there are still pgpools with priority 5 available, and I would expect to have elected one of the higher priority.
> >   >
> >   >
> >   >
> >   >  Am I missing something? Is anyway to exclude pgpool servers from master election to use them exclusively as quorums?
> >   >
> >   >
> >   >
> >   >  I am using 3.6.7
> >   >
> >   >
> >   >
> >   >  Thank you!!
>    
>                
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20180202/cd37cace/attachment.htm>