Thread: Postmaster Uptime
Hi All, Is their any way to find out the uptime of the postmaster via a SQL function? Best Regards, Tim Knowles
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 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
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: > 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)
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.