How can I get the query string, starting by the process id associated to the Postgres connection running it (output of htop)?

Tms91

6/30/24, 10:11 AM

I have a machine, let's call it DB_server,
which is connected to two application machines,
let's call them application_server_1 and application_server_2.

DB_server hosts 3 postgres databases on it,
the name of one of it is centraldb,
and the name of one of the users of this db is central.

On DB_server, by using htop, I found out there are some processes run by postgres user, associated to some postgres commands (which - if I guess right - are being run by the application_server_2 connecting to server_DB and connecting to the database centraldb with user central), which consumes a lot of CPU.

Given the following output from htop, indicating which is the process consuming a lot of CPU, how can I find out which are the queries which are running on those connections, and thus consuming the CPU of DB_server?

PID     USER      ...  Command

<pid_1>   postgres  ...  postgres: 10/central centraldb <IP_application_server_2>(<pid_2>) idle

0 + 0

postgresql

htop

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: How can I get the query string, starting by the process id associated to the Postgres connection running it (output of htop)?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.