После аварийного переключения postgres сбойная база данных не будет реплицироваться с нового повышенного мастера. ⇐ Linux

[Anonymous]

Я следую этому примеру из официальной документации pgpool
однако после того, как неисправный мастер-устройство восстановится и перейдет в режим ожидания, он не получит никакой репликации с нового мастера.
вот pgpool.conf для всех узлов:
backend_clustering_mode = 'streaming_replication'
listen_addresses = '*'
pcp_listen_addresses = '*'
port = 9999
enable_pool_hba = on

sr_check_user = 'pgpool'
sr_check_password = ''

health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3

# backend connection settings
backend_hostname0 = 'db2'
backend_application_name0 = 'db01'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/16/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'db2'
backend_application_name1 = 'db02'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/16/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = 'db3'
backend_application_name2 = 'db03'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/16/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

#logging
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool_log'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB

#debugging
log_statement = on
log_per_node_statement = on

#fail over scripts
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'

#recovery
recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'

#watchdog
use_watchdog = on
delegate_ip = '192.168.122.20'

wd_lifecheck_method = 'heartbeat'
wd_interval = 10

if_cmd_path = '/sbin'
arping_path = '/usr/sbin'

if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp1s0 label enp1s0:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp1s0'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I enp1s0'

hostname0 = 'db01.nomadesk.org'
wd_port0 = 9000
pgpool_port0 = 9999

hostname1 = 'db02.nomadesk.org'
wd_port1 = 9000
pgpool_port1 = 9999

hostname2 = 'db03.nomadesk.org'
wd_port2 = 9000
pgpool_port2 = 9999

wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30

heartbeat_hostname0 = 'db1'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = 'db2'
heartbeat_port1 = 9694
heartbeat_device1 = ''
heartbeat_hostname2 = 'db3'
heartbeat_port2 = 9694
heartbeat_device2 = ''

wd_escalation_command = '/etc/pgpool-II/escalation.sh'

и все сценарии являются примерами сценариев, представленных в примере.
единственное, что я добавил, это psotgres-ctl.sh
cat /etc/pgpool-II/postgres-ctl.sh
#!/bin/bash

# Usage
#
# $1: can be start/stop or restart
# $2: postgresql version

systemctl $1 postgresql-$2
EOF
chmod +x /etc/pgpool-II/postgres-ctl.sh


который используется вместо pg_ctl, например, в Follow_primary.sh
sudo /etc/pgpool-II/postgres-ctl.sh start 16
#instead of
${PGHOME}/bin/pg_ctl -l /dev/null -w -D ${NODE_PGDATA} start

поскольку pg_ctl не нашел postman.pid, поэтому использование systemd устранило проблему.
psql -h vip -p 9999 -U pgpool postgres -c "show pool_nodes"
| node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change |
|---------|----------|------|--------|-----------|-----------|---------|---------|------------|-------------------|-------------------|-------------------|------------------------|-----------------------|
| 0 | server1 | 5432 | down | down | 0.333333 | standby | unknown | 0 | false | 0 | | | 2023-11-10 17:05:40 |
| 1 | server2 | 5432 | up | up | 0.333333 | primary | primary | 0 | false | 0 | | | 2023-11-10 17:05:40 |
| 2 | server3 | 5432 | up | up | 0.333333 | standby | standby | 0 | true | 0 | streaming | async | 2023-11-10 17:05:51 |


Подробнее здесь: https://stackoverflow.com/questions/788 ... oted-maste