持续更新中…
1 pg_hba中的项不匹配
假设您的pgpool集群中有2个节点,您更新了其中一个节点中的pg_hba.conf条目,但忘记在其他节点上应用相同的条目,您会看到如下错误:
psql -d postgres -U postgres -h 127.0.0.1 -p 5432 -c "show pool_nodes" ;
psql: error: could not connect to server: ERROR: unable to read message kind
DETAIL: kind does not match between master(52) slot[1] (45)
解决方案:
- 上面的错误消息很难理解,因为它没有在任何地方说明是pg_hba.conf文件导致的错误.
- 此类错误请检查2个节点中的pg_hba.conf是否完全一致.
2 pgpool流式复制检查错误
2.1 连接参数配置错误
在pgpool.conf文件中,您需要确保具有正确的参数来设置流式传输复制运行状况检查,否则您会看到类似于下面所述的错误:
pgpool find_primary_node: make_persistent_db_connection_noerror failed on node+
解决方案:
请检查以下参数(其中最后一个字符x表示0,1,…台主机):
backend_hostnamex
backend_portx
#流复制检查的数据库
sr_check_database
#流复制检查使用的用户,注意此用户为连接至数据库的用户,不能是replication类型的用户
sr_check_user
#流复制检查使用的用户密码
sr_check_password
2.2 pg_hba.conf配置问题
2022-09-22 18:19:18.301: main pid 1304: LOG: find_primary_node: make_persistent_db_connection_noerror failed on node 0
错误原因:检查流复制延迟时,连接数据库失败
解决方案:
检查的配置
sr_check_user = 'postgres'
sr_check_password = ''
sr_check_database = 'test'
psql -h 随机一台数据库主机 -U sr_check_user -d sr_check_database
#然后输入sr_check_password中设置的密码可得到详细的错误信息
#示例
psql -h pgser02 -U postgres -d test
示例报告的错误
psql: error: connection to server at "pgser02" (192.168.1.68), port 5432 failed: FATAL: no pg_hba.conf entry for host "192.168.1.67", user "postgres", database "test", no encryption
示例出错的原因是pg_hba.conf不允许postgres用户登录test数据库.只需要重新配置 pg_hba.conf即可
vim ${PGDATA}/pg_hba.conf
修改postgres用户可以本地或本网段登录所有数据库
host all postgres 127.0.0.1/32 trust
host all postgres 192.168.1.0/24 trust
测试
psql -h pgser01 -U postgres -d test -c "\q" && \
psql -h pgser02 -U postgres -d test -c "\q" && \
psql -h pgser03 -U postgres -d test -c "\q" && \
psql -h pgser04 -U postgres -d test -c "\q"
3 SCRAM方法错误
当您使用scram身份验证时,您可能会看到如下错误:
psql: error: could not connect to server: ERROR: failed to authenticate with backend using SCRAM
DETAIL: valid password not found
解决方案:
您需要在pgpool.conf中的pool_passwd参数指定的文件中定义密码
pool_passwd = 'pool_passwd'
pool_passwd文件的创建方法
自Pgpool-II 4.0起支持scram-sha-256、证书和明文密码
su - postgres
#将pgpoolkey保存至 ~/.pgpoolkey
echo '123' > ~/.pgpoolkey
chmod 600 ~/.pgpoolkey
sudo rm /usr/local/pgpool/etc/pool_passwd
sudo /usr/local/pgpool/bin/pg_enc -m -k ~/.pgpoolkey -u test -p
cat /usr/local/pgpool/etc/pool_passwd
/usr/local/pgpool/bin/pg_md5 test
4 pgpool_node_id
Pgpool-II 4.1或更早版本,因为需要指定自己的pgpool节点信息和目标pgpool节点信息,因此每个pgpool节点的设置是不同的.
从Pgpool-II 4.2开始,所有主机上的所有配置参数都是相同的.如果启用了监视程序功能,为了区分哪个主机需要创建pgpool_node_id文件.pgpool_node_id文件并指定 pgpool(看门狗)节点号来标识pgpool(看门狗)主机。
必须手动创建pgpool_node_id文件,否则报异常:
FATAL: Pgpool node id file /usr/local/pgpool/etc/pgpool_node_id does not exist
解决方案:创建pgpool_node_id
su postgres
vim /usr/local/pgpool/etc/pgpool_node_id
0
cat</usr/local/pgpool/etc/pgpool_node_id
5 Connection refused
ERROR: connection to host "pgser01" failed with error "Connection refused"
解决方案:
pcp进程未启动或正在启动中.
- 请检查pgpool日志,如pcp进程未启动,请检查不能启动的原因.
- 稍等片刻等待pcp进程启动后再执行命令.
6 find_primary_node_repeatedly: waiting for finding a primary node
2022-09-20 14:48:32.393: main pid 9958: LOG: find_primary_node_repeatedly: waiting for finding a primary node
2022-09-20 14:48:32.471: main pid 9958: LOG: find_primary_node: standby node is 1
2022-09-20 14:48:32.473: main pid 9958: LOG: find_primary_node: standby node is 2
解决方案:
可能的原因为启动顺序不正确.查看pgpool.conf中logdir的配置,假设为/pfast,那么执行
rm /pfast/pgpool_status
7 failed to acquire the delegate IP address
2022-09-22 19:09:59.598: watchdog_utility pid 1712: LOG: failed to acquire the delegate IP address
2022-09-22 19:09:59.599: watchdog_utility pid 1712: DETAIL: 'if_up_cmd' failed
2022-09-22 19:09:59.599: watchdog_utility pid 1712: WARNING: watchdog escalation failed to acquire delegate IP
错误原因:添加虚拟ip后执行arping命令,arping在本机执行会发生错误
src\watchdog\wd_if.c
rtn = exec_if_cmd(path, pool_config->arping_cmd); 调用arping失败
int
wd_IP_up(void)
{
int rtn = WD_OK;
char path[WD_MAX_PATH_LEN];
char *command;
int i;
if (strlen(pool_config->delegate_IP) == 0)
{
ereport(LOG,
(errmsg("trying to acquire the delegate IP address, but delegate IP is not configured")));
return WD_OK;
}
command = wd_get_cmd(pool_config->if_up_cmd);
if (command)
{
/* If if_up_cmd starts with "/", the setting specified in "if_cmd_path" will be ignored */
if (command[0] == '/')
snprintf(path, sizeof(path), "%s", command);
else
snprintf(path, sizeof(path), "%s/%s", pool_config->if_cmd_path, command);
rtn = exec_if_cmd(path, pool_config->if_up_cmd);
pfree(command);
}
else
{
ereport(LOG,
(errmsg("failed to acquire the delegate IP address"),
errdetail("unable to parse the if_up_cmd:\"%s\"", pool_config->if_up_cmd)));
return WD_NG;
}
//添加虚拟ip已经成功
if (rtn == WD_OK)
{
command = wd_get_cmd(pool_config->arping_cmd);
if (command)
{
/* If arping_cmd starts with "/", the setting specified in "arping_path" will be ignored */
if (command[0] == '/')
snprintf(path, sizeof(path), "%s", command);
else
snprintf(path, sizeof(path), "%s/%s", pool_config->arping_path, command);
rtn = exec_if_cmd(path, pool_config->arping_cmd); //在本机执行arping肯定失败,return的返回值为arping的返回值
pfree(command);
}
else
{
rtn = WD_NG;
ereport(LOG,
(errmsg("failed to acquire the delegate IP address"),
errdetail("unable to parse the arping_cmd:\"%s\"", pool_config->arping_cmd)));
}
}
if (rtn == WD_OK)
{
for (i = 0; i < WD_TRY_PING_AT_IPUP; i++)
{
if (wd_is_ip_exists(pool_config->delegate_IP) == true)
break;
ereport(LOG,
(errmsg("waiting for the delegate IP address to become active"),
errdetail("waiting... count: %d", i + 1)));
}
if (i >= WD_TRY_PING_AT_IPUP)
rtn = WD_NG;
}
if (rtn == WD_OK)
ereport(LOG,
(errmsg("successfully acquired the delegate IP:\"%s\"", pool_config->delegate_IP),
errdetail("'if_up_cmd' returned with success")));
else
ereport(LOG,
(errmsg("failed to acquire the delegate IP address"),
errdetail("'if_up_cmd' failed")));
return rtn;
}
解决方案:
忽略这个错误或修改pgpool.conf
将原来的
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ens32 label ens32:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ens32'
修改为
# $_IP_$修改为delegate_IP设置的ip
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add 192.168.1.65/24 dev ens32 label ens32:0'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del 192.168.1.65/24 dev ens32'
