Postmaster Uptime

Tim Knowles wrote:

Hi All,

Is their any way to find out the uptime of the postmaster via a SQL
function?

No, not really. You can do a 'ps' on the postmaster pid and run that
somehow from pltcl.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Could we store this as an environment variable that gets set once
postmaster leaves "start up" mode?

Robert Treat

Robert Treat <rtreat@webmd.net> writes:

Could we store this as an environment variable that gets set once
postmaster leaves "start up" mode?

Whose environment? If the postgres user, how would another user access
it? And if only the postgres user could access it, it's not clear to
me why that would have any advantages over just storing it in a static
C variable.

More generally, why is this functionality needed in the first place?

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

To be honest my reason for requiring an uptime is really just comestic. My
company uses a VBA app with PostgreSQL as the backend, an administrator only
screen shows database activity (number of backends, running queries -
bascially custom versions of some of the pg_stat views), I thought the
uptime would be an interesting addition to that.

I also think that having easy access to the uptime could help in the
advocacy of PostgreSQL. My best is just over 4 months (based on the date
given by the ps command), stopped only by an upgrade to 7.2.2! If people are
willing to publish/share that sort of info it can only further impress
people about PostgreSQL's stability (btw I've never had a crash/problem in
the 2 years I've been using PostgreSQL)

Best Regards,

Tim Knowles

Robert Treat <rtreat@webmd.net> writes:

Could we store this as an environment variable that gets set once
postmaster leaves "start up" mode?

Whose environment? If the postgres user, how would another user access
it? And if only the postgres user could access it, it's not clear to
me why that would have any advantages over just storing it in a static
C variable.

More generally, why is this functionality needed in the first place?

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

Hmmm if its just cosmetic then just create a table to store the start
time. Then as part of your start script for postgresql, have it update
or insert a new timestamp in that table after starting postgresql. You
wanna make sure and always use that script or the values won't make
sense. You can calc approx uptime then from that and display it. Its not
a great solution or anything but it should work for your purpose.

Tom.

On Wed, 2002-09-25 at 18:19, Tim Knowles wrote:

To be honest my reason for requiring an uptime is really just comestic. My
company uses a VBA app with PostgreSQL as the backend, an administrator only
screen shows database activity (number of backends, running queries -
bascially custom versions of some of the pg_stat views), I thought the
uptime would be an interesting addition to that.

I also think that having easy access to the uptime could help in the
advocacy of PostgreSQL. My best is just over 4 months (based on the date
given by the ps command), stopped only by an upgrade to 7.2.2! If people are
willing to publish/share that sort of info it can only further impress
people about PostgreSQL's stability (btw I've never had a crash/problem in
the 2 years I've been using PostgreSQL)

Best Regards,

Tim Knowles

Robert Treat <rtreat@webmd.net> writes:

Could we store this as an environment variable that gets set once
postmaster leaves "start up" mode?

Whose environment? If the postgres user, how would another user access
it? And if only the postgres user could access it, it's not clear to
me why that would have any advantages over just storing it in a static
C variable.

More generally, why is this functionality needed in the first place?

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs

Tim, given this is purely cosmetic I'm sure you'd be hardpushed
getting it added to PostgreSQL proper.

As others have said it is certainly do-able, but you're going to have
to do it! In the past you've got the uptime using ps - what you need
to do is wrap this into a script and add it to a database as a
function (using pg/tcl, pg/perlu, whatever). Or you could do it as a C
function...

Lee.

Tim Knowles writes:

Lee Kindness writes:

As others have said it is certainly do-able, but you're going to have
to do it! In the past you've got the uptime using ps - what you need
to do is wrap this into a script and add it to a database as a
function (using pg/tcl, pg/perlu, whatever). Or you could do it as a C
function...

Actually pl/sh would be the obvious choice:

http://www.ca.postgresql.org/~petere/plsh.html

Lee.

Cheers for the link, I'll give pl/sh a go too. I was just hoping someone
else may have done it already.

Best Regards,

Tim

-----Original Message-----
From: Lee Kindness [mailto:lkindness@csl.co.uk]
Sent: 25 September 2002 10:39
To: Tim Knowles
Cc: Lee Kindness; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postmaster Uptime

Lee Kindness writes:

As others have said it is certainly do-able, but you're going to have
to do it! In the past you've got the uptime using ps - what you need
to do is wrap this into a script and add it to a database as a
function (using pg/tcl, pg/perlu, whatever). Or you could do it as a C
function...

Actually pl/sh would be the obvious choice:

http://www.ca.postgresql.org/~petere/plsh.html

Lee.

Following on from Lee's mail I have written the following two functions to
return the uptime. I had a look at the other pl's and pl/sh seemed to be
the easiest to route to take. PostgreSQL's internal time/date routines do a
lot of the heavy lifting in re-arranging the date output from the ps command
into a timestamp.

CREATE FUNCTION postmasterstarttime() RETURNS timestamp AS '
#!/bin/bash
ps -o lstart,command | grep ''[p]ostmaster'' | cut -c 5-24
' LANGUAGE 'plsh';

CREATE FUNCTION uptime() RETURNS interval AS '
SELECT current_timestamp - postmasterstarttime();
' LANGUAGE 'sql';

A simple 'SELECT uptime();' should return

uptime
------------------------
2 days 04:40:24.016406
(1 row)

I have tried this on a Linux Mandrake 8.1 box and it seems to work fine. I'd
imagine that the output of ps may need to be tweaked for different platforms
depending on the version of ps used (I have procps version 2.0.7).

Best Regards,

Tim Knowles

-----Original Message-----
From: Lee Kindness [mailto:lkindness@csl.co.uk]
Sent: 25 September 2002 10:39
To: Tim Knowles
Cc: Lee Kindness; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Postmaster Uptime

Lee Kindness writes:

As others have said it is certainly do-able, but you're going to have
to do it! In the past you've got the uptime using ps - what you need
to do is wrap this into a script and add it to a database as a
function (using pg/tcl, pg/perlu, whatever). Or you could do it as a C
function...

Actually pl/sh would be the obvious choice:

http://www.ca.postgresql.org/~petere/plsh.html

Lee.

On 25 Sep 2002 at 13:44, Tim Knowles wrote:

Following on from Lee's mail I have written the following two functions to
return the uptime. I had a look at the other pl's and pl/sh seemed to be
the easiest to route to take. PostgreSQL's internal time/date routines do a
lot of the heavy lifting in re-arranging the date output from the ps command
into a timestamp.

Can we have a CPAN like repository of such resources? It would be extremely
valuable..

Or it's there already and I am missing the trivial.. as usual..

Bye
Shridhar

--
sugar daddy, n.: A man who can afford to raise cain.

Shridhar Daithankar writes:

On 25 Sep 2002 at 13:44, Tim Knowles wrote:

Following on from Lee's mail I have written the following two functions to
return the uptime.

Can we have a CPAN like repository of such resources? It would be extremely
valuable..

Minor snippets of procedural code like this are perfect for the
PostgreSQL Cookbook:

http://www.brasileiro.net/postgres/cookbook/

Or it's there already and I am missing the trivial.. as usual..

Easily done, I mean where's the developer branch hidden on gborg ;)

Lee.