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

[Franklin Anderson de Oliveira Souza]

Hi Tatsuo Ishii

I removed the three lines from pgpool.conf:

backend_flag0 = 'ALWAYS_PRIMARY'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
backend_flag2 = 'DISALLOW_TO_FAILOVER'

changed configuration
-------
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_application_name0 = 'MASTER'

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

backend_hostname2 = 'IP_ADDR'
backend_port2 = PORT
backend_weight2 = 1
backend_data_directory2 = '/data'
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/'
-------


But unfortunately the problem still persists. Did I do something wrong ?!

Thank you for your help

Em sex., 29 de set. de 2023 às 20:00, Tatsuo Ishii
<ishii at sraoss.co.jp> escreveu:
>
> Hi Franklin,
>
> > Hi Tatsuo Ishii,
> >
> > Sorry for the delay in responding, below is pgpool.conf:
>
> No problem.
>
> > backend_flag1 = 'DISALLOW_TO_FAILOVER'
> > backend_flag2 = 'DISALLOW_TO_FAILOVER'
>
> These are the source of the issue. If DISALLOW_TO_FAILOVER is set,
> pgpool tries to connect to the PostgreSQL and fails. The flag is
> useful when PostgreSQL is managed by other systems so that the
> PostgreSQL becomes online quickly. In other case, you should not use
> the flag.
>
> If the flag is not set, pgpool will disconnect the PostgreSQL in down
> status and keep on operation with remaining PostgreSQL servers. After
> the PostgreSQL comes back online again, you can let pgpool know that
> by using pcp_attach_node.
>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp



-- 
foobar