Skip site navigation (1) Skip section navigation (2)

Re: pgtop, display PostgreSQL processes in `top' style

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Cosimo Streppone <cosimo(at)streppone(dot)it>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: pgtop, display PostgreSQL processes in `top' style
Date: 2005-05-02 03:04:49
Message-ID: 9988.1115003089@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-announcepgsql-sql
Cosimo Streppone <cosimo(at)streppone(dot)it> writes:
> 1) is it possible to know Pg backend uptime with
>     SQL queries? Or must I look at postmaster.pid file?
>     or even something else?

I suppose you mean postmaster uptime, not the lifespan of your own
connection.  We don't track that at the moment, but IIRC there is
a patch in the queue to provide a function that returns this for 8.1.
For now you could perhaps look at "ps" output to see how long the
postmaster process has been running, but that's surely ugly and fraught
with pitfalls ...

Interesting thought here: should we track the postmaster process uptime,
or the time since the last database restart?  Not the same at all.
You could argue that from a reliability standpoint the latter is the
interesting number.

> 2) how can I know the ip addresses/hostnames of clients
>     connecting to server?

You can't (again, unless you want to parse "ps" output).  But I think
someone has submitted a patch to add such columns to the
pg_stat_activity view.  We need to figure out whether the visibility
of these columns needs to be restricted for security, but pending that
discussion it'll probably be in 8.1 in some form.

> 3) Is there a way to know the number of queries performed
>     against Pg backend (by each client)? Can I distinguish
>     between selects / updates / inserts / copy / ... ?

Nope, and nope.  We could perhaps teach the stats collector to count
querystrings it's received from each backend, which'd answer the first
one to a reasonable extent.  I'm unconvinced that we should expend the
overhead to be able to do the second.

			regards, tom lane

In response to

pgsql-announce by date

Next:From: Enrico WeigeltDate: 2005-05-02 03:55:53
Subject: Re: pgtop, display PostgreSQL processes in `top' style
Previous:From: Cosimo StrepponeDate: 2005-04-30 22:10:30
Subject: pgtop, display PostgreSQL processes in `top' style

pgsql-sql by date

Next:From: Enrico WeigeltDate: 2005-05-02 03:31:34
Subject: Re: trigger/rule question
Previous:From: Patrice DroletDate: 2005-05-01 20:21:46
Subject: Discordance between the way select is called.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group