Re: identify database process given client process

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

On Mon, Mar 17, 2008 at 6:58 AM, hogcia <hogcia(at)gmail(dot)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

Joey K. wrote:
> On Mon, Mar 17, 2008 at 6:58 AM, hogcia <hogcia(at)gmail(dot)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

On Mon, Mar 17, 2008 at 8:28 PM, hogcia <hogcia(at)gmail(dot)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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ 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