[pgpool-general: 8939] Re: Offline replica and pgpool do not respond or connect with psql

[Franklin Anderson de Oliveira Souza]

Hi Tatsuo Ishii,

Sorry for the delay in responding, below is pgpool.conf:

backend_clustering_mode = 'streaming_replication'
port = PORT
unix_socket_directories = '/run/pgpool'
reserved_connections = 2
pcp_listen_addresses = '*'

backend_hostname0 = 'IP_ADDR'
backend_port0 = PORT
backend_weight0 = 0
backend_data_directory0 = '/data'
backend_flag0 = 'ALWAYS_PRIMARY'
backend_application_name0 = 'MASTER'

backend_hostname1 = 'IP_ADDR'
backend_port1 = PORT
backend_weight1 = 1
backend_data_directory1 = '/data'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
backend_application_name1 = 'Rep1'

backend_hostname2 = 'IP_ADDR'
backend_port2 = PORT
backend_weight2 = 1
backend_data_directory2 = '/data'
backend_flag2 = 'DISALLOW_TO_FAILOVER'
backend_application_name2 = 'Rep2'

pool_passwd = 'pool_passwd'
authentication_timeout = 1min
allow_clear_text_frontend_auth = off
process_management_mode = dynamic
process_management_strategy = gentle
num_init_children = 100
min_spare_children = 5
max_spare_children = 10
max_pool = 4
child_life_time = 5min
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = 'syslog,stderr'
log_line_prefix = '%t: appname[%a] user[%u] db[%d] pid[%p]: '
log_connections = on
log_hostname = on
log_standby_delay = 'if_over_threshold'
pid_file_name = '/run/pgpool/pgpool.pid'
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
replicate_select = off
insert_lock = off
lobj_lock_table = ''
load_balance_mode = on
ignore_leading_white_space = on
write_function_list = 'currval,lastval,nextval,setval'
allow_sql_comments = on
disable_load_balance_on_write = transaction
sr_check_period = 5
sr_check_user = 'postgres'
sr_check_database = 'postgres'
delay_threshold = *********
prefer_lower_delay_standby = off
health_check_period = 20
health_check_timeout = 20
health_check_user = 'postgres'
health_check_database = 'postgres'
health_check_max_retries = 5
hostname0 = ''
relcache_expire = 3600
relcache_size = 4096
relcache_query_target = load_balance_node
socket_dir = '/run/pgpool'
pcp_socket_dir = '/run/pgpool'
backend_socket_dir = '/run/pgpool/'

Thank you for your help !!!

Em sex., 22 de set. de 2023 às 20:35, Tatsuo Ishii
<ishii at sraoss.co.jp> escreveu:
>
> > Hello everyone,
> >
> > I'm using pgpool, a master and two replicas, but when a replica goes
> > down, pgpool no longer responds. I can't connect with psql and in the
> > log I have the following:
> >
> > LOG: failed to connect to PostgreSQL server on "ip_address:5432"
> >
> > The application becomes inoperative. Is there a way to make pgpool
> > more resilient in relation to this?
>
> In a standard setting of pgpool, it automatically disconnects the
> replica which went down and continues to provide service to clients
> like psql.
>
> It seems there's something wrong/unusual in your configuration. To
> study the issue further, the version of pgpool, pgpool.log while the
> replica went down and pgpool.conf are needed (you can mask items which
> you do not want to put in public in those files).
>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp



-- 
foobar