如何从Wireshark File查找（解码）PostgreSQL查询？ [英] How to find (decode) PostgreSQL query from Wireshark File?

问题描述

我在Linux平台上使用PostgreSQL 5.5。我正在尝试监视Master和Slave之间与PostgreSQL相关的所有流量。为此，我使用Wireshark监视流量。然后，我启动了PostgreSQL并运行了三个查询（创建表Hello，创建表Bye并将图像插入PostgreSQL数据库）。在查询期间，我在Master上运行Wireshark只是为了捕获Master和Slave之间的流量。

但是使用Wireshark捕获PostgreSQL流量存在一个问题。所有流量都以TCP数据包的形式发送/接收，并且该流量为编码形式。我无法读取这些数据。我想从我插入PostgreSQL数据库的Wirehsark中找出所有这三个查询。
查找PostgreSQL查询的最佳方法是什么？
另一方面，我在MySQL数据库上运行了相同的查询，并重复了上述实验。我可以轻松地在wirehark转储中读取所有这三个查询，因为它们不是编码形式。

I am on Linux platform with PostgreSQL 5.5. I am trying to monitor all traffic related to PostgreSQL between Master and Slave. To that end, I used Wireshark to monitor the traffic. Then, I started PostgreSQL and ran three queries (Create table Hello, Create table Bye & inserted an image to PostgreSQL database). During queries, I ran Wireshark on Master just to capture the traffic between Master and Slave.
But there is one problems with PostgreSQL traffic captured using Wireshark. All the traffic is sent/received in TCP packets and that traffic is in coded form. I can't read that data. I want to find out all those three queries from Wirehsark that I inserted in PostgreSQL database. What is the best way to go about finding queries of PostgreSQL? On the other hand, I ran same queries on MySQL database and repeated above mentioned experiment. I can easily read all those three queries in wireshark dump because they are not in coded form.

PostgreSQL实验的Wireshark文件位于 Wireshark文件。我需要从Wirehsark文件中找到上述三个查询。
关于文件：
192.168.50.11是我将查询插入到远程PostgreSQL主服务器的源计算机。 192.168.50.12是主服务器的IP。 192.168.50.13是从站的IP地址。从.11执行查询，然后将其插入.12，然后使用主从方法将其复制到.13。
指针将非常受欢迎。

Wireshark file of PostgreSQL experiment is available on Wireshark-File. I need to find out above three queries from Wirehsark file. About File: 192.168.50.11 is the source machine from where I inserted queries to remote PostgreSQL's Master server. 192.168.50.12 is the IP of Master's server. 192.168.50.13 is the slave's IP address. Queries were executed from .11 and inserted into .12 and then replicated to .13 using Master-Slave approach. Pointers will be very welcome.

推荐答案

您可能正在使用基于WAL的复制（默认设置），这意味着您

You are probably using WAL-based replication (the default) which means you can't.

这涉及在机器之间传送事务日志。这是数据在磁盘上的实际表示形式。

This involves shipping the transaction-logs between machines. This is actual on-disk representation of the data.

还有其他基于触发器的复制方法（如slony等）和新的逻辑复制。

There are alternative trigger-based replication methods (slony etc) and the new logical replication.

根据我的理解，它们都不会让您重新创建完整的原始查询，但是会让您更近。

Neither will let you recreate the complete original query as I understand it, but would let you get closer.

有些系统会重复节点上的查询（例如MySQL），但它们不是完全一样的。

There are systems which duplicate the queries on nodes (like MySQL) but they aren't quite the same thing.

如果您想确切地知道主服务器上正在运行的查询，请打开查询记录并监视日志。

If you want to know exactly what queries are running on the master, turn on query logging and monitor the logs instead.

这篇关于如何从Wireshark File查找（解码）PostgreSQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！