[pgpool-general: 9405] FATAL: failed to read kind from backend
Ron Johnson
ronljohnsonjr at gmail.com
Thu Mar 27 12:41:05 JST 2025
PG 16.8
PgPool 4.5.4
Three node cluster:
FISPCDSPGS302a < Primary DB node
FISPCDSPGS302b < DB replica
FISPCDSPGS302c < VIP lives here
FATAL: failed to read kind from backend
DETAIL: kind mismatch among backends. Possible last query was: ...
HINT: check data consistency among db nodes
The last query is in the first attached text file, along with pool node and
db streaming stats. It's happening about 20 times per hour.
(Load balancing is turned off.)
The only other example of this error is when someone ran pg_dump through
pgpool.
What could be causing this?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250326/f4377a16/attachment.htm>
-------------- next part --------------
2025-03-26 21:59:46.960: PostgreSQL JDBC Driver pid 1119834: FATAL: failed to read kind from backend
2025-03-26 21:59:46.960: PostgreSQL JDBC Driver pid 1119834: DETAIL: kind mismatch among backends. Possible last query was: "select ACCESS_USER.USER_ID,ACCESS_USER.TYPE_ID,ACCESS_USER.LOGIN_ID,ACCESS_USER.FIRST_NAME,ACCESS_USER.MIDDLE_NAME,ACCESS_USER.LAST_NAME,ACCESS_USER.ACCOUNT_ENABLED,ACCESS_USER.LOGIN_ON,ACCESS_USER.CREATED_ON,ACCESS_USER.MODIFIED_ON,EXTENDED_ACCESS_USER.SEARCH_HOME_DOMAIN,EXTENDED_ACCESS_USER.BENCHMARK_ENABLED,EXTENDED_ACCESS_USER.CREATED_ON
from ACCESS_USER
LEFT OUTER JOIN EXTENDED_ACCESS_USER ON ACCESS_USER.USER_ID=EXTENDED_ACCESS_USER.EXTENDED_ACCESS_USER_ID
where ACCESS_USER.ACCOUNT_ENABLED=$1 and (not ACCESS_USER.USER_ID=$2) and ACCESS_USER.LOGIN_ON<$3
order by ACCESS_USER.USER_ID asc " kind details are: 0[2] 1[Z]
2025-03-26 21:59:46.960: PostgreSQL JDBC Driver pid 1119834: HINT: check data consistency among db nodes
$ diff /etc/pgpool-II/pgpool.conf <(ssh -q FISPCDSPGS302c 'cat /etc/pgpool-II/pgpool.conf')
103c103
< memory_cache_enabled = off
---
> memory_cache_enabled = on
cdsmultip=# show pool_nodes;
-[ RECORD 1 ]----------+--------------------
node_id | 0
hostname | FISPCDSPGS302a
port | 5432
status | up
pg_status | up
lb_weight | 0.666667
role | primary
pg_role | primary
select_cnt | 57690
load_balance_node | true
replication_delay | 0
replication_state |
replication_sync_state |
last_status_change | 2025-03-26 18:35:55
-[ RECORD 2 ]----------+--------------------
node_id | 1
hostname | FISPCDSPGS302b
port | 5432
status | up
pg_status | up
lb_weight | 0.333333
role | standby
pg_role | standby
select_cnt | 0
load_balance_node | false
replication_delay | 0
replication_state | streaming
replication_sync_state | async
last_status_change | 2025-03-26 18:35:55
PRIMARY:
SELECT * from pg_replication_slots;
-[ RECORD 1 ]-------+------------
slot_name | pgstandby1
plugin |
slot_type | physical
datoid |
database |
temporary | f
active | t
active_pid | 3112451
xmin | 80045582
catalog_xmin |
restart_lsn | 8C/2704EBB0
confirmed_flush_lsn |
wal_status | reserved
safe_wal_size |
two_phase | f
conflicting |
SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 3112451
usesysid | 16389
usename | replicator
application_name | walreceiver
client_addr | 10.109.160.11
client_hostname | FISPCDSPGS302b
client_port | 52926
backend_start | 2025-03-13 07:43:14.227578-04
backend_xmin |
state | streaming
sent_lsn | 8C/2704EBB0
write_lsn | 8C/2704EBB0
flush_lsn | 8C/2704EBB0
replay_lsn | 8C/2704EBB0
write_lag | 00:00:00.000118
flush_lag | 00:00:00.000391
replay_lag | 00:00:00.000414
sync_priority | 0
sync_state | async
reply_time | 2025-03-26 23:18:43.889087-04
REPLICA:
SELECT * FROM pg_stat_wal_receiver;
pid | 1437628
status | streaming
receive_start_lsn | 7A/50000000
receive_start_tli | 1
written_lsn | 8C/2704EBB0
flushed_lsn | 8C/2704EBB0
received_tli | 1
last_msg_send_time | 2025-03-26 23:18:43.190869-04
last_msg_receipt_time | 2025-03-26 23:18:43.191014-04
latest_end_lsn | 8C/2704EBB0
latest_end_time | 2025-03-26 23:18:43.190869-04
slot_name | pgstandby1
sender_host | FISPCDSPGS302a.itocservices.com
sender_port | 5432
conninfo | user=replicator passfile=/var/lib/pgsql/.pgpass channel_binding=prefer dbname=replication host=FISPCDSPGS302a.itocservices.com port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable
-------------- next part --------------
listen_addresses = '*'
port = 9999
unix_socket_directories = '/var/run/postgresql'
backend_clustering_mode = 'streaming_replication'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/var/run/postgresql'
listen_backlog_multiplier = 2
serialize_accept = on
num_init_children = 200
max_pool = 2
reserved_connections = 3
client_idle_limit = 0
child_life_time = 0
load_balance_mode = off
backend_hostname0 = 'FISPCDSPGS302a'
backend_port0 = 5432
backend_weight0 = 2
backend_data_directory0 = '/Database/16/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'walreceiver'
backend_hostname1 = 'FISPCDSPGS302b'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/Database/16/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'walreceiver'
enable_pool_hba = on
pool_passwd = '/etc/pgpool-II/pool_passwd'
log_destination = 'stderr'
log_line_prefix = '%m: %a pid %p: '
log_connections = on
log_hostname = on
#log_statement = on
log_statement = off
#log_per_node_statement = on
log_per_node_statement = off
log_client_messages = on
#log_min_messages = info
log_min_messages = notice
logging_collector = on
log_directory = '/var/log/pgpool2'
log_filename = 'pgpool-%F_%H.log'
log_truncate_on_rotation = on
log_rotation_age = 1h
log_rotation_size = 0
sr_check_user = 'pool_health_check'
sr_check_database = 'pool_health_check'
health_check_period = 5
health_check_user = 'pool_health_check'
health_check_database = 'pool_health_check'
health_check_max_retries = 10
health_check_retry_delay = 10
use_watchdog = on
wd_priority = 1
delegate_ip = '10.109.160.20'
hostname0 = 'FISPCDSPGS302a'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = 'FISPCDSPGS302b'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = 'FISPCDSPGS302c'
wd_port2 = 9000
pgpool_port2 = 9999
wd_ipc_socket_dir = '/var/run/postgresql'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/26 dev ens192 label ens192:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/26 dev ens192'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens192'
wd_escalation_command = '/etc/pgpool-II/escalation.sh'
wd_lifecheck_method = 'heartbeat'
heartbeat_hostname0 = 'FISPCDSPGS302a'
heartbeat_port0 = 9694
heartbeat_device0 = 'ens192'
heartbeat_hostname1 = 'FISPCDSPGS302b'
heartbeat_port1 = 9694
heartbeat_device1 = 'ens192'
heartbeat_hostname2 = 'FISPCDSPGS302c'
heartbeat_port2 = 9694
heartbeat_device2 = 'ens192'
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
memory_cache_enabled = off
memqcache_oiddir = '/var/log/pgpool2/oiddir'
memqcache_total_size = 1024MB
memqcache_max_num_cache = 1048576
More information about the pgpool-general
mailing list