Thread: Postmaster Uptime

Postmaster Uptime

From
"Tim Knowles"
Date:
Hi All,

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


Best Regards,

Tim Knowles


Re: Postmaster Uptime

From
Bruce Momjian
Date:
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

Re: Postmaster Uptime

From
Robert Treat
Date:
Could we store this as an environment variable that gets set once
postmaster leaves "start up" mode?

Robert Treat

On Tue, 2002-09-24 at 10:47, Bruce Momjian wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org



Re: Postmaster Uptime

From
Neil Conway
Date:
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

Re: Postmaster Uptime

From
"Tim Knowles"
Date:
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


Re: Postmaster Uptime

From
Thomas O'Dowd
Date:
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


Re: Postmaster Uptime

From
Lee Kindness
Date:
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:
 > 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)

Re: Postmaster Uptime

From
Lee Kindness
Date:
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.

Re: Postmaster Uptime

From
"Tim Knowles"
Date:
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.


Re: Postmaster Uptime

From
"Tim Knowles"
Date:
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.


Re: Postmaster Uptime

From
"Shridhar Daithankar"
Date:
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.


Re: Postmaster Uptime

From
Lee Kindness
Date:
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.