1

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.

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.

Muhammad Karam Shehzad
  • 57
  • 1
  • 8

2 Answers2

1

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.

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.

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.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
0

Solution to my own problem:

I got the solution of my question.

I used Python code to insert queries into remote PostgreSQL database. I used following line in PostgreSQL to connect with database. con = psycopg2.connect(host="192.168.50.12", database="postgres", user="postgres", password="faban") If you use above approach then all the data will be sent in encrypted form. If you use the approach given below in python code then all the data will be sent in decrypted form. You can easily read all queries in Wireshark. con = psycopg2.connect("host=192.168.50.12 dbname=postgres user=postgres password=faban sslmode=disable") Same is the case in C-Code as well. Decrypted data sprintf(conninfo, "dbname=postgres hostaddr=192.168.50.12 user=postgres password=faban sslmode=disable"); Encrypted Data sprintf(conninfo, "dbname=postgres hostaddr=192.168.50.12 user=postgres password=faban");

Muhammad Karam Shehzad
  • 57
  • 1
  • 8