Postgres Pro
Downloads
Home   >   mailing lists

Thread: identify database process given client process

identify database process given client process

From
hogcia
Date:
Hi,
I have to find a Postgres database process pid (or other
identification) for a given client process pid. Or client processes
for a database process. How are they connected? I was suggested maybe
netstat could give me the answer and I think those are two pf_unix
processes. But maybe there are some PostgreSQL functions that do this?
How should I approach this topic?
Thanks in advance,
--
Agata Krawcewicz

Re: identify database process given client process

From
"Joey K."
Date:


On Mon, Mar 17, 2008 at 6:58 AM, hogcia <hogcia@gmail.com> wrote:
Hi,
I have to find a Postgres database process pid (or other
identification) for a given client process pid. Or client processes
for a database process. How are they connected? I was suggested maybe
netstat could give me the answer and I think those are two pf_unix
processes. But maybe there are some PostgreSQL functions that do this?
How should I approach this topic?
Thanks in advance,

Try

select pg_stat_activity;


Joey

Re: identify database process given client process

From
Shane Ambler
Date:
Joey K. wrote:
> On Mon, Mar 17, 2008 at 6:58 AM, hogcia <hogcia@gmail.com> wrote:
>
>> Hi,
>> I have to find a Postgres database process pid (or other
>> identification) for a given client process pid. Or client processes
>> for a database process. How are they connected? I was suggested maybe
>> netstat could give me the answer and I think those are two pf_unix
>> processes. But maybe there are some PostgreSQL functions that do this?
>> How should I approach this topic?
>> Thanks in advance,
>>
>
> Try
>
> select pg_stat_activity;
>
>
> Joey
>

That would be select * from pg_stat_activity;

The columns that interest you would be datname,procpid,usename and
client_addr


The other way would be using ps (for a *nix server)

Depending on your system something similar to ps aux will give the
process details so the command column will give you something like -

postgres: mydbuser mydbname 192.168.0.3(49438) idle


which is the info you are after - pgsql is the dbusername, postgres is
the db they are connected to then the ip address and port they are
connecting from. The idle at the end will be replaced with the query
they are running.



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

Re: identify database process given client process

From
"Gurjeet Singh"
Date:
On Mon, Mar 17, 2008 at 8:28 PM, hogcia <hogcia@gmail.com> wrote:
Hi,
I have to find a Postgres database process pid (or other
identification) for a given client process pid. Or client processes
for a database process. How are they connected? I was suggested maybe
netstat could give me the answer and I think those are two pf_unix
processes. But maybe there are some PostgreSQL functions that do this?
How should I approach this topic?

If possible, execute the following query from your client, and it will show that server process the client is connected to.

select pg_backend_pid();

This is an easy, one way route to determine a client's shadow process, be it local or remote.

If you want reverse lookup, that is, trying to find out which backend process is servicing which client, it'd be a bit difficult. You have to query pg_stat_activity and the client_addr and client_port columns wil give you some information about the remote clients. For local clients using Unix sockets, these columns are null.

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device