10

I am working with postgresql-9.4 and for support and monitoring tasks I need to know how long my server has been up for? Is there any function or way to know this? Is there an sql way to know when my server started?

I really need to know the elapsed time since it started?

2 Answers 2

16

Use the built-in function pg_postmaster_start_time

select current_timestamp - pg_postmaster_start_time() as uptime

More details in the manual: http://www.postgresql.org/docs/current/static/functions-info.html

If you want seconds, use extract:

select extract(epoch from current_timestamp - pg_postmaster_start_time()) as uptime
4
  • Nice one - I was convinced that there was only one approach - the Unix one. Of course, doing it through the database might be exactly what the OP had in mind. Just as a matter of interest, how would you convert the result into a duration of, say, seconds?
    – Vérace
    May 2, 2015 at 21:15
  • @Vérace: the expression returns an interval so you can use all the functions available, specifically extract(epoch from ...) to get seconds
    – user1822
    May 2, 2015 at 21:19
  • I've played around with it - got as far as SELECT EXTRACT(EPOCH FROM SELECT CURRENT_TIMESTAMP - pg_postmaster_start_time()); (and variations), but just get an error ERROR: syntax error at or near "SELECT" LINE 1: SELECT EXTRACT(EPOCH FROM SELECT CURRENT_TIMESTAMP - pg_post... Would you be able to tell me how to perform this operation?
    – Vérace
    May 2, 2015 at 21:51
  • @Vérace: see my edit
    – user1822
    May 2, 2015 at 22:22
2

With thanks to threads 1, 2 and 3.

The following examples are taken from an instance running postgresql.

Method 1: (provides start time to the second and other details)

`ls -l ./data/postmaster.pid` 

The postmaster.pid file is created when you kick off the server.

SAMPLE OUTPUT:

[pol@localhost inst]$ ls -l ./data/postmaster.pid
-rw-------. 1 pol pol 106 May  4 20:53 ./data/postmaster.pid
[pol@localhost inst]$

Method 2: (cumbersome and included for completeness)

If you're a real bash guru, you could use something like this

- step 1

First, get the pid of the "root" postgres process - in this case it's 7382.

[pol@localhost inst]$ `ps -ef | grep post`

SAMPLE OUTPUT:

[pol@localhost inst]$ ps -ef | grep post
pol       7382     1  0 20:53 pts/2    00:00:00 /home/pol/Downloads/software/postgres/inst/bin/postgres -D ./data
pol       7384  7382  0 20:53 ?        00:00:00 postgres: checkpointer process   
pol       7385  7382  0 20:53 ?        00:00:00 postgres: writer process   
pol       7386  7382  0 20:53 ?        00:00:00 postgres: wal writer process   
pol       7387  7382  0 20:53 ?        00:00:00 postgres: autovacuum launcher process   
pol       7388  7382  0 20:53 ?        00:00:00 postgres: stats collector process   
pol       9741  5477  0 23:49 pts/1    00:00:00 grep --color=auto post
[pol@localhost inst]$ 

- step 2(a)

then run the following command

[pol@localhost inst]$ `ps -p 7382 -o lstart`
                 STARTED
Fri May  1 18:03:44 2015
[pol@localhost inst]$ 

- step 2(b)

or (perhaps more suited to scripting)

[pol@localhost inst]$ `ps -p 7382 -o lstart=`
Fri May  1 18:03:44 2015
[pol@localhost inst]$ 

The = at the end supresses the header that might muddy the waters when using the various bash utilities to pass the output down a chain of scripts.

Method 3:

If you want the time to the nanosecond! I'd have my doubts as to the accuracy of the last 3-4 digits (lots of fun Googling)

[pol@localhost inst]$ `stat /proc/7382`
  File: /proc/7382
  Size: 0           Blocks: 0          IO Block: 1024   directory
Device: 3h/3d   Inode: 154004      Links: 9
Access: (0555/dr-xr-xr-x)  Uid: ( 1000/     pol)   Gid: ( 1000/     pol)
Context: unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
Access: 2015-05-01 18:03:55.473814036 +0100
Modify: 2015-05-01 18:03:55.473814036 +0100
Change: 2015-05-01 18:03:55.473814036 +0100
 Birth: -
[pol@localhost inst]$ 

Method 4:

Obviously, all of these methods can be turned into executables, but I thought that these next two were particularly elegant.

This script outputs the pid's name and the no. of seconds which it has been running. The script's input ($1) is the pid.

#file: sincetime
#!/bin/bash
init=`stat -t /proc/$1 | awk '{print $14}'`
curr=`date +%s`
seconds=`echo $curr - $init| bc`
name=`cat /proc/$1/cmdline`
echo $name $seconds

To make it executable save the above as sincetime and then issue the command chmod 755 sincetime and to use it from anywhere, put it on your path.

Method 5:

This method builds on Method 4 - greptime, it calls sincetime and outputs the number of seconds per process which matches the pattern.

#file: greptime
#!/bin/bash
pidlist=`ps ax | grep -i -E $1 | grep -v grep | awk '{print $1}' | grep -v PID | xargs echo`
for pid in $pidlist; do
    sincetime $pid
done

Method 6:

This will return the number of seconds in the current epoch at which the process started - useful for databases like MySQL which support UNIX time data types.

stat -t /proc/7382 | awk '{print $14}'

A variant of this will return the time in seconds

echo `date +%s` - `stat -t /proc/7382 | awk '{print $14}'` | bc

Method 7:

These commands provides examples of what can be done with the ps, ls and awk commands. See the respective man pages for the various commands.

ps -eo pid,cmd,etime,lstart  | grep <pid> | grep -v grep

ps -p <pid> -wo pid,lstart,cmd

ps -eo pid,etime,cmd | grep <pid | pname> |sort -n -k2

ps -p <pid> -o etime=

ps -eo pid,etime | awk '{print $1, $2}' | grep 7382 | grep -v grep

SAMPLE OUTPUTS:

[pol@localhost inst]$     ps -eo pid,cmd,etime,lstart  | grep 7382 | grep -v grep
 7382 /home/pol/Downloads/softwar    03:00:16 Mon May  4 20:53:24 2015

[pol@localhost inst]$ ps -p 7382 -wo pid,lstart,cmd
  PID                  STARTED CMD
 7382 Mon May  4 20:53:24 2015 /home/pol/Downloads/software/postgres/inst/bin/postgres -D ./data

[pol@localhost inst]$  ps -eo pid,etime,cmd | grep 7382 |sort -n -k2 | grep -v grep
 7382    03:01:29 /home/pol/Downloads/software/postgres/inst/bin/postgres -D ./data

[pol@localhost inst]$  ps -eo pid,etime,cmd | grep post |sort -n -k2 | grep -v grep
 7382    03:01:42 /home/pol/Downloads/software/postgres/inst/bin/postgres -D ./data
 7384    03:01:41 postgres: checkpointer process   
 7385    03:01:41 postgres: writer process   
 7386    03:01:41 postgres: wal writer process   
 7387    03:01:41 postgres: autovacuum launcher process   
 7388    03:01:41 postgres: stats collector process   
[pol@localhost inst]$ 

[pol@localhost inst]$ ps -eo pid,etime | awk '{print $1, $2}' | grep 7382 | grep -v grep
7382 03:12:49
[pol@localhost inst]$

The /proc virtual filesystem can also be very useful.

ls -ld /proc/7382 | awk '{print $8}'

OUTPUT

20:53

EXAMPLE:

ls -ltrh /proc | grep 7382 | awk '{print "Time started: " $8}'

OUTPUT:

Time started: 20:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.