[pgpool-general: 2790] Re: both pgpool servers are master and using the same virtual IP

Attila Heidrich attila.heidrich at gmail.com
Wed Apr 23 16:23:03 JST 2014


Thanks the advices, I have modified the config file generation!

Earlier I have asked several times about recommendations on the backend
list, but somehow I must not have been clean enough, never got an answer.
Now it's OK, "trusted servers" are different than the pool members, and the
backend list is the same for all.

will be back soon with the results!

Thanks again!

Regards,
Attila


2014-04-23 5:10 GMT+02:00 Yugo Nagata <nagata at sraoss.co.jp>:

> Hi,
>
> On Tue, 22 Apr 2014 15:24:42 +0200
> Attila Heidrich <attila.heidrich at gmail.com> wrote:
>
> > Hi!
> >
> > The summary is generated by a script called "pool" :
> > https://gist.github.com/dansimau/1582492
> > Unfortunately there are no logs at all, logging was temporary disabled.
>
> Without logs, I cannot analyze how and why the problem occured,
> though, in general, it can occur when heartbeat connection is broken.
>
> > > > This isn't the first time, usually happens in a high load situation.
>
> The high load situation means that pgpool is receiving a lot of queries
> from clients?
>
> BTW, I find trusted_servers as the each other server and this is not
> recommended. When one server goes down, the other server also goes down
> since there is no alive trusted_servers.
>
> In addition, configurations about backend_hostname are different between
> pgpool.confs. These should be same.
>
> >
> > The current (normal) status is:
> >
> > root at control-1:~# salt postg\* cmd.run "pool status"
> > postgres-1:
> >     Node: 0
> >     Host: postgres-1
> >     Port: 5433
> >     Weight: 0.500000
> >     Status: Up, in pool (1)
> >     Role: Master
> >
> >     Node: 1
> >     Host: postgres-2
> >     Port: 5433
> >     Weight: 0.500000
> >     Status: Up, in pool (1)
> >     Role: Master
> > postgres-2:
> >     Node: 0
> >     Host: postgres-2
> >     Port: 5433
> >     Weight: 0.500000
> >     Status: Up, in pool and connected (2)
> >     Role: Master
> >
> >     Node: 1
> >     Host: postgres-1
> >     Port: 5433
> >     Weight: 0.500000
> >     Status: Up, in pool and connected (2)
> >     Role: Master
> >
> > IP address status
> >
> > postgres-2:
> >     2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP
> > qlen 1000
> >         link/ether 00:50:56:8f:7e:7b brd ff:ff:ff:ff:ff:ff
> >         inet 10.6.14.11/24 brd 10.6.14.255 scope global eth0
> >            valid_lft forever preferred_lft forever
> >         inet 10.6.14.15/24 scope global secondary eth0
> >            valid_lft forever preferred_lft forever
> >         inet6 fe80::250:56ff:fe8f:7e7b/64 scope link
> >            valid_lft forever preferred_lft forever
> > postgres-1:
> >     2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP
> > qlen 1000
> >         link/ether 00:50:56:8f:58:ab brd ff:ff:ff:ff:ff:ff
> >         inet 10.6.14.10/24 brd 10.6.14.255 scope global eth0
> >            valid_lft forever preferred_lft forever
> >         inet6 fe80::250:56ff:fe8f:58ab/64 scope link
> >            valid_lft forever preferred_lft forever
> >
> > Config files with modified passwords:
> >
> > postgres-2:
> >     listen_addresses = '*'
> >     port = 5432
> >     socket_dir = '/var/run/postgresql'
> >     pcp_port = 9898
> >     pcp_socket_dir = '/var/run/postgresql'
> >     backend_hostname0 = 'postgres-2'
> >     backend_port0 = 5433
> >     backend_weight0 = 1
> >     backend_data_directory0 = '/var/lib/postgresql/9.3'
> >     backend_flag0 = 'ALLOW_TO_FAILOVER'
> >     backend_hostname1 = 'postgres-1'
> >     backend_port1 = 5433
> >     backend_weight1 = 1
> >     backend_data_directory1 = '/var/lib/postgresql/9.3'
> >     backend_flag1 = 'ALLOW_TO_FAILOVER'
> >     enable_pool_hba = on
> >     pool_passwd = ''
> >     authentication_timeout = 60
> >     ssl = off
> >     num_init_children = 32
> >     max_pool = 4
> >     child_life_time = 300
> >     child_max_connections = 0
> >     connection_life_time = 0
> >     client_idle_limit = 0
> >     log_destination = 'syslog'
> >     print_timestamp = on
> >     log_connections = off
> >     log_hostname = on
> >     log_statement = off
> >     log_per_node_statement = on
> >     log_standby_delay = 'none'
> >     syslog_facility = 'LOCAL0'
> >     syslog_ident = 'pgpool'
> >     debug_level = 0
> >     pid_file_name = '/var/run/postgresql/pgpool.pid'
> >     logdir = '/var/log/postgresql'
> >     connection_cache = on
> >     reset_query_list = 'ABORT; DISCARD ALL'
> >     replication_mode = on
> >     replicate_select = off
> >     insert_lock = on
> >     lobj_lock_table = ''
> >     replication_stop_on_mismatch = off
> >     failover_if_affected_tuples_mismatch = off
> >     health_check_period = 15
> >     health_check_timeout = 5
> >     health_check_user = 'pg_admin'
> >     health_check_password = 'P4ssw0rd'
> >     health_check_max_retries = 2
> >     health_check_retry_delay = 1
> >     failover_command = 'echo $(date): host:%h, new master id:%m, old
> master
> > id:%M >> /var/lib/pgsql/failover.log'
> >     failback_command = 'echo $(date): host:%h, new master id:%m, old
> master
> > id:%M >> /var/lib/pgsql/failback.log'
> >     fail_over_on_backend_error = on
> >     recovery_user = 'pg_admin'
> >     recovery_password = 'P4ssw0rd'
> >     recovery_1st_stage_command = 'copy_base_backup'
> >     recovery_2nd_stage_command = 'switch_xlog'
> >     recovery_timeout = 90
> >     client_idle_limit_in_recovery = 0
> >     use_watchdog = on
> >     trusted_servers = 'postgres-1'
> >     delegate_IP = '10.6.14.15'
> >     wd_hostname = 'postgres-2'
> >     wd_port = 9000
> >     wd_interval = 5
> >     ping_path = '/bin'
> >     ifconfig_path = '/var/lib/postgresql'
> >     if_up_cmd = 'ip add add 10.6.14.15/24 dev eth0'
> >     if_down_cmd = 'ip add del 10.6.14.15/24 dev eth0'
> >     arping_path = '/var/lib/postgresql'
> >     arping_cmd = 'arping -U 10.6.14.15 -w 1'
> >     wd_life_point = 3
> >     wd_lifecheck_query = 'SELECT 1'
> >     wd_escalation_command = ''
> >     wd_lifecheck_method = 'heartbeat'
> >     wd_interval = 15
> >     wd_heartbeat_port = 9694
> >     wd_heartbeat_keepalive = 5
> >     wd_heartbeat_deadtime = 30
> >     heartbeat_destination0 = 'postgres-1'
> >     heartbeat_destination_port0 = 9694
> >     heartbeat_device0 = ''
> >     other_pgpool_hostname0 = 'postgres-1'
> >     other_pgpool_port0 = 5432
> >     other_wd_port0 = 9000
> >     relcache_expire = 0
> >     relcache_size = 256
> >     check_temp_table = on
> >     memory_cache_enabled = off
> >     memqcache_method = 'shmem'
> >     memqcache_memcached_host = 'localhost'
> >     memqcache_memcached_port = 11211
> >     memqcache_total_size = 67108864
> >     memqcache_max_num_cache = 1000000
> >     memqcache_expire = 0
> >     memqcache_auto_cache_invalidation = on
> >     memqcache_maxcache = 409600
> >     memqcache_cache_block_size = 1048576
> >     memqcache_oiddir = '/var/log/pgpool/oiddir'
> >     white_memqcache_table_list = ''
> >     black_memqcache_table_list = ''
> > postgres-1:
> >     listen_addresses = '*'
> >     port = 5432
> >     socket_dir = '/var/run/postgresql'
> >     pcp_port = 9898
> >     pcp_socket_dir = '/var/run/postgresql'
> >     backend_hostname0 = 'postgres-1'
> >     backend_port0 = 5433
> >     backend_weight0 = 1
> >     backend_data_directory0 = '/var/lib/postgresql/9.3'
> >     backend_flag0 = 'ALLOW_TO_FAILOVER'
> >     backend_hostname1 = 'postgres-2'
> >     backend_port1 = 5433
> >     backend_weight1 = 1
> >     backend_data_directory1 = '/var/lib/postgresql/9.3'
> >     backend_flag1 = 'ALLOW_TO_FAILOVER'
> >     enable_pool_hba = on
> >     pool_passwd = ''
> >     authentication_timeout = 60
> >     ssl = off
> >     num_init_children = 32
> >     max_pool = 4
> >     child_life_time = 300
> >     child_max_connections = 0
> >     connection_life_time = 0
> >     client_idle_limit = 0
> >     log_destination = 'syslog'
> >     print_timestamp = on
> >     log_connections = off
> >     log_hostname = on
> >     log_statement = off
> >     log_per_node_statement = on
> >     log_standby_delay = 'none'
> >     syslog_facility = 'LOCAL0'
> >     syslog_ident = 'pgpool'
> >     debug_level = 0
> >     pid_file_name = '/var/run/postgresql/pgpool.pid'
> >     logdir = '/var/log/postgresql'
> >     connection_cache = on
> >     reset_query_list = 'ABORT; DISCARD ALL'
> >     replication_mode = on
> >     replicate_select = off
> >     insert_lock = on
> >     lobj_lock_table = ''
> >     replication_stop_on_mismatch = off
> >     failover_if_affected_tuples_mismatch = off
> >     health_check_period = 15
> >     health_check_timeout = 5
> >     health_check_user = 'pg_admin'
> >     health_check_password = 'P4ssw0rd'
> >     health_check_max_retries = 2
> >     health_check_retry_delay = 1
> >     failover_command = 'echo $(date): host:%h, new master id:%m, old
> master
> > id:%M >> /var/lib/pgsql/failover.log'
> >     failback_command = 'echo $(date): host:%h, new master id:%m, old
> master
> > id:%M >> /var/lib/pgsql/failback.log'
> >     fail_over_on_backend_error = on
> >     recovery_user = 'pg_admin'
> >     recovery_password = 'P4ssw0rd'
> >     recovery_1st_stage_command = 'copy_base_backup'
> >     recovery_2nd_stage_command = 'switch_xlog'
> >     recovery_timeout = 90
> >     client_idle_limit_in_recovery = 0
> >     use_watchdog = on
> >     trusted_servers = 'postgres-2'
> >     delegate_IP = '10.6.14.15'
> >     wd_hostname = 'postgres-1'
> >     wd_port = 9000
> >     wd_interval = 5
> >     ping_path = '/bin'
> >     ifconfig_path = '/var/lib/postgresql'
> >     if_up_cmd = 'ip add add 10.6.14.15/24 dev eth0'
> >     if_down_cmd = 'ip add del 10.6.14.15/24 dev eth0'
> >     arping_path = '/var/lib/postgresql'
> >     arping_cmd = 'arping -U 10.6.14.15 -w 1'
> >     wd_life_point = 3
> >     wd_lifecheck_query = 'SELECT 1'
> >     wd_escalation_command = ''
> >     wd_lifecheck_method = 'heartbeat'
> >     wd_interval = 15
> >     wd_heartbeat_port = 9694
> >     wd_heartbeat_keepalive = 5
> >     wd_heartbeat_deadtime = 30
> >     heartbeat_destination0 = 'postgres-2'
> >     heartbeat_destination_port0 = 9694
> >     heartbeat_device0 = ''
> >     other_pgpool_hostname0 = 'postgres-2'
> >     other_pgpool_port0 = 5432
> >     other_wd_port0 = 9000
> >     relcache_expire = 0
> >     relcache_size = 256
> >     check_temp_table = on
> >     memory_cache_enabled = off
> >     memqcache_method = 'shmem'
> >     memqcache_memcached_host = 'localhost'
> >     memqcache_memcached_port = 11211
> >     memqcache_total_size = 67108864
> >     memqcache_max_num_cache = 1000000
> >     memqcache_expire = 0
> >     memqcache_auto_cache_invalidation = on
> >     memqcache_maxcache = 409600
> >     memqcache_cache_block_size = 1048576
> >     memqcache_oiddir = '/var/log/pgpool/oiddir'
> >     white_memqcache_table_list = ''
> >     black_memqcache_table_list = ''
> >
> >
> >
> > 2014-04-18 10:36 GMT+02:00 Yugo Nagata <nagata at sraoss.co.jp>:
> >
> > > Hi,
> > >
> > > On Tue, 15 Apr 2014 12:24:51 +0200
> > > Attila Heidrich <attila.heidrich at gmail.com> wrote:
> > >
> > > > Dear guys!
> > > >
> > > > Where to find the problem in the situation above?
> > > >
> > > > No logs at all, for some reason pgpool stopped logging (usually it
> uses
> > > > syslog).
> > >
> > > The followings seem a summarised results of some command (pcp command
> or
> > > show pool_nodes?). I want to konw the actual command results. Could you
> > > please send me these, log messages, and pgpool.conf?
> > >
> > > >
> > > > root at postgres-1:/etc/pgpool2# pool status
> > > > Node: 0
> > > > Host: postgres-1
> > > > Port: 5433
> > > > Weight: 0.500000
> > > > Status: Up, in pool (1)
> > > > Role: Master
> > > >
> > > > Node: 1
> > > > Host: postgres-2
> > > > Port: 5433
> > > > Weight: 0.500000
> > > > Status: Up, in pool (1)
> > > > Role: Master
> > > >
> > > > root at postgres-2:/etc/pgpool2# pool status
> > > > Node: 0
> > > > Host: postgres-2
> > > > Port: 5433
> > > > Weight: 0.500000
> > > > Status: Up, in pool and connected (2)
> > > > Role: Master
> > > >
> > > > Node: 1
> > > > Host: postgres-1
> > > > Port: 5433
> > > > Weight: 0.500000
> > > > Status: Up, in pool and connected (2)
> > > > Role: Master
> > > >
> > > > This isn't the first time, usually happens in a high load situation.
> > > >
> > > > Attila
> > >
> > >
> > > --
> > > Yugo Nagata <nagata at sraoss.co.jp>
> > >
>
>
> --
> Yugo Nagata <nagata at sraoss.co.jp>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140423/7c9cdcde/attachment-0001.html>


More information about the pgpool-general mailing list