Fork me on GitHub

daydayup863

人生就像一杯茶,不会苦一辈子,但总会苦一阵子。

0%

日志中打印pgbouncer中client<-> server <-> remote server的对应关系

如下流程是应用通过pgbouncer连接PostgreSQL的链路,可以看到中间有三个网络层,想要对应PostgreSQL连接到应用连接并不是很容易,尤其连接断开后,查询日志更难。

app -> pgbouncer client -> pgbouncer server -> remote PostgreSQL

实时连接中可以通过client中link指向server中ptr地址完成对应关系.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
pgbouncer=# show servers; show clients;
type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_needed | ptr | link | remote_pid | tls
------+----------+----------+--------+-----------+------+------------+------------+-------------------------+-------------------------+------+---------+--------------+----------------+----------------+------------+-----
S | postgres | mydb | active | 127.0.0.1 | 5557 | 127.0.0.1 | 56182 | 2022-05-12 17:23:19 CST | 2022-05-12 17:41:33 CST | 0 | 0 | 0 | 0x55eee319ef70 | 0x55eee31995a0 | 90997 |
S | postgres | postgres | used | 127.0.0.1 | 5557 | 127.0.0.1 | 56380 | 2022-05-12 17:25:59 CST | 2022-05-12 17:34:10 CST | 0 | 0 | 0 | 0x55eee319f830 | | 91127 |
S | postgres | postgres | used | 127.0.0.1 | 5557 | 127.0.0.1 | 56916 | 2022-05-12 17:31:31 CST | 2022-05-12 17:34:10 CST | 0 | 0 | 0 | 0x55eee31a00f0 | | 91495 |
S | postgres | postgres | used | 127.0.0.1 | 5557 | 127.0.0.1 | 56914 | 2022-05-12 17:31:31 CST | 2022-05-12 17:34:10 CST | 0 | 0 | 0 | 0x55eee319fec0 | | 91494 |
S | postgres | postgres | used | 127.0.0.1 | 5557 | 127.0.0.1 | 56378 | 2022-05-12 17:25:59 CST | 2022-05-12 17:34:10 CST | 0 | 0 | 0 | 0x55eee319f600 | | 91126 |
S | postgres | postgres | used | 127.0.0.1 | 5557 | 127.0.0.1 | 56382 | 2022-05-12 17:25:59 CST | 2022-05-12 17:34:10 CST | 0 | 0 | 0 | 0x55eee319fa60 | | 91128 |
S | postgres | postgres | used | 127.0.0.1 | 5557 | 127.0.0.1 | 56384 | 2022-05-12 17:25:59 CST | 2022-05-12 17:34:10 CST | 0 | 0 | 0 | 0x55eee319fc90 | | 91129 |
S | postgres | postgres | used | 127.0.0.1 | 5557 | 127.0.0.1 | 56920 | 2022-05-12 17:31:31 CST | 2022-05-12 17:34:10 CST | 0 | 0 | 0 | 0x55eee31a0550 | | 91497 |
S | postgres | postgres | used | 127.0.0.1 | 5557 | 127.0.0.1 | 56918 | 2022-05-12 17:31:31 CST | 2022-05-12 17:34:10 CST | 0 | 0 | 0 | 0x55eee31a0320 | | 91496 |
S | postgres | postgres | used | 127.0.0.1 | 5557 | 127.0.0.1 | 56374 | 2022-05-12 17:25:59 CST | 2022-05-12 17:34:10 CST | 0 | 0 | 0 | 0x55eee319f1a0 | | 91124 |
S | postgres | postgres | used | 127.0.0.1 | 5557 | 127.0.0.1 | 56376 | 2022-05-12 17:25:59 CST | 2022-05-12 17:34:10 CST | 0 | 0 | 0 | 0x55eee319f3d0 | | 91125 |
(11 rows)

type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | wait | wait_us | close_needed | ptr | link | remote_pid | tls
------+--------+-----------+--------+------+------+------------+------------+-------------------------+-------------------------+------+---------+--------------+----------------+----------------+------------+-----
C | jintao | mydb | active | unix | 6432 | unix | 6432 | 2022-05-12 17:41:32 CST | 2022-05-12 17:41:33 CST | 0 | 0 | 0 | 0x55eee31995a0 | 0x55eee319ef70 | 92056 |
C | jintao | pgbouncer | active | unix | 6432 | unix | 6432 | 2022-05-12 17:40:42 CST | 2022-05-12 17:41:36 CST | 45 | 241515 | 0 | 0x55eee3198420 | | 91989 |
(2 rows)

本文尝在pgbouncer中增加几行代码,打印server与client和remote的对应关系。

代码如下

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
+static void adr2txt(const PgAddr *adr, char *dst, unsigned dstlen)
+{
+ pga_ntop(adr, dst, dstlen);
+}
+
+
static inline struct PStr *get_value(VarCache *cache, const struct var_lookup *lk)
{
return cache->var_list[lk->idx];
@@ -121,6 +127,8 @@ bool varcache_apply(PgSocket *server, PgSocket *client, bool *changes_p)
const struct var_lookup *lk;
int sql_ofs;
struct PktBuf *pkt = pktbuf_temp();
+ char s_l_addr[PGADDR_BUF], s_r_addr[PGADDR_BUF];
+ char c_l_addr[PGADDR_BUF], c_r_addr[PGADDR_BUF];

pktbuf_start_packet(pkt, 'Q');

@@ -139,7 +147,14 @@ bool varcache_apply(PgSocket *server, PgSocket *client, bool *changes_p)
pktbuf_put_char(pkt, 0);
pktbuf_finish_packet(pkt);

+ adr2txt(&server->remote_addr, s_r_addr, sizeof(s_r_addr));
+ adr2txt(&server->local_addr, s_l_addr, sizeof(s_l_addr));
+
+ adr2txt(&client->remote_addr, c_r_addr, sizeof(c_r_addr));
+ adr2txt(&client->local_addr, c_l_addr, sizeof(c_l_addr));
+
slog_debug(server, "varcache_apply: %s", pkt->buf + sql_ofs);
+ log_info("app: %s:%d client: %p %s:%d server: %p %s:%d remote: %s:%d", c_l_addr, pga_port(&client->local_addr), client, c_r_addr, pga_port(&client->remote_addr), server, s_l_addr, pga_port(&server->local_addr), s_r_addr, pga_port(&server->remote_addr));
return pktbuf_send_immediate(pkt, server);
}

日志输出如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2022-05-12 17:56:49.265 CST [92984] LOG app: 127.0.0.1:6432 client: 0x5575e2fa8420 127.0.0.1:57640 server: 0x5575e2faf1a0 127.0.0.1:59030 remote: 127.0.0.1:5557
2022-05-12 17:56:49.265 CST [92984] LOG S-0x5575e2faf3d0: postgres/postgres@127.0.0.1:5557 new connection to server (from 127.0.0.1:59094)
2022-05-12 17:56:49.273 CST [92984] LOG app: 127.0.0.1:6432 client: 0x5575e2fa9140 127.0.0.1:57648 server: 0x5575e2faf3d0 127.0.0.1:59094 remote: 127.0.0.1:5557
2022-05-12 17:56:49.273 CST [92984] LOG S-0x5575e2faf600: postgres/postgres@127.0.0.1:5557 new connection to server (from 127.0.0.1:59096)
2022-05-12 17:56:49.279 CST [92984] LOG app: 127.0.0.1:6432 client: 0x5575e2fa8ab0 127.0.0.1:57644 server: 0x5575e2faf600 127.0.0.1:59096 remote: 127.0.0.1:5557
2022-05-12 17:56:49.280 CST [92984] LOG S-0x5575e2faf830: postgres/postgres@127.0.0.1:5557 new connection to server (from 127.0.0.1:59098)
2022-05-12 17:56:49.295 CST [92984] LOG app: 127.0.0.1:6432 client: 0x5575e2fa8880 127.0.0.1:57642 server: 0x5575e2faf830 127.0.0.1:59098 remote: 127.0.0.1:5557
2022-05-12 17:56:49.296 CST [92984] LOG S-0x5575e2fafa60: postgres/postgres@127.0.0.1:5557 new connection to server (from 127.0.0.1:59100)
2022-05-12 17:56:49.312 CST [92984] LOG app: 127.0.0.1:6432 client: 0x5575e2fa8ce0 127.0.0.1:57646 server: 0x5575e2fafa60 127.0.0.1:59100 remote: 127.0.0.1:5557
2022-05-12 17:56:49.312 CST [92984] LOG S-0x5575e2fafc90: postgres/postgres@127.0.0.1:5557 new connection to server (from 127.0.0.1:59102)
2022-05-12 17:56:49.316 CST [92984] LOG app: 127.0.0.1:6432 client: 0x5575e2fa8f10 127.0.0.1:57650 server: 0x5575e2fafc90 127.0.0.1:59102 remote: 127.0.0.1:5557
2022-05-12 17:56:49.317 CST [92984] LOG S-0x5575e2fafec0: postgres/postgres@127.0.0.1:5557 new connection to server (from 127.0.0.1:59104)
2022-05-12 17:56:49.323 CST [92984] LOG app: 127.0.0.1:6432 client: 0x5575e2fa8650 127.0.0.1:57652 server: 0x5575e2fafec0 127.0.0.1:59104 remote: 127.0.0.1:5557
2022-05-12 17:56:49.324 CST [92984] LOG S-0x5575e2fb00f0: postgres/postgres@127.0.0.1:5557 new connection to server (from 127.0.0.1:59106)
2022-05-12 17:56:49.343 CST [92984] LOG app: 127.0.0.1:6432 client: 0x5575e2fa9370 127.0.0.1:57698 server: 0x5575e2fb00f0 127.0.0.1:59106 remote: 127.0.0.1:5557
2022-05-12 17:56:49.349 CST [92984] LOG S-0x5575e2fb0320: postgres/postgres@127.0.0.1:5557 new connection to server (from 127.0.0.1:59108)
2022-05-12 17:56:49.359 CST [92984] LOG app: 127.0.0.1:6432 client: 0x5575e2fa97d0 127.0.0.1:57700 server: 0x5575e2fb0320 127.0.0.1:59108 remote: 127.0.0.1:5557
2022-05-12 17:56:49.360 CST [92984] LOG S-0x5575e2fb0550: postgres/postgres@127.0.0.1:5557 new connection to server (from 127.0.0.1:59110)
2022-05-12 17:56:49.379 CST [92984] LOG app: 127.0.0.1:6432 client: 0x5575e2fa95a0 127.0.0.1:57696 server: 0x5575e2fb0550 127.0.0.1:59110 remote: 127.0.0.1:5557

-------------本文结束感谢您的阅读-------------
听说,打赏我的人都找到了真爱

欢迎关注我的其它发布渠道