pgpool-II常见错误

持续更新中…

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)
1
2
3

解决方案:

• 上面的错误消息很难理解,因为它没有在任何地方说明是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+
1

解决方案:

请检查以下参数(其中最后一个字符x表示0,1,…台主机):

backend_hostnamex
backend_portx

#流复制检查的数据库
sr_check_database
#流复制检查使用的用户,注意此用户为连接至数据库的用户,不能是replication类型的用户
sr_check_user
#流复制检查使用的用户密码
sr_check_password
1
2
3
4
5
6
7
8
9

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
1

错误原因:检查流复制延迟时,连接数据库失败

解决方案:

检查的配置

sr_check_user = 'postgres'
sr_check_password = ''
sr_check_database = 'test'
1
2
3

psql -h 随机一台数据库主机 -U  sr_check_user -d sr_check_database
#然后输入sr_check_password中设置的密码可得到详细的错误信息

#示例
psql -h pgser02 -U postgres -d test
1
2
3
4
5

示例报告的错误

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
1

示例出错的原因是pg_hba.conf不允许postgres用户登录test数据库.只需要重新配置 pg_hba.conf即可

 vim ${PGDATA}/pg_hba.conf
1

修改postgres用户可以本地或本网段登录所有数据库

host    all             postgres        127.0.0.1/32            trust
host    all             postgres        192.168.1.0/24          trust
1
2

测试

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"
1
2
3
4

3 SCRAM方法错误

当您使用scram身份验证时,您可能会看到如下错误:

psql: error: could not connect to server: ERROR:  failed to authenticate with backend using SCRAM
DETAIL:  valid password not found
1
2

解决方案:
您需要在pgpool.conf中的pool_passwd参数指定的文件中定义密码

pool_passwd = 'pool_passwd'
1

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

1
2
3
4
5
6
7
8
9
10
11
12
13

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
1

解决方案:创建pgpool_node_id

su postgres

vim /usr/local/pgpool/etc/pgpool_node_id
1
2
3

0
1

cat</usr/local/pgpool/etc/pgpool_node_id
1

5 Connection refused

ERROR: connection to host "pgser01" failed with error "Connection refused"
1

解决方案:

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
1
2
3

解决方案:

可能的原因为启动顺序不正确.查看pgpool.conf中logdir的配置,假设为/pfast,那么执行

rm /pfast/pgpool_status
1

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
1
2
3

错误原因:添加虚拟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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

解决方案:
忽略这个错误或修改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'
1
2

修改为

# $_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'
1
2
3