What is the server uptime?

You may be wondering, how long has it been since the server started?

For instance, you might want to verify that there was no server crash if your server is not monitored, or to see when the server was last restarted, for instance, to change the configuration. We will find this out by asking the database server.

How to do it…

Issue the following SQL from any interface:

postgres=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;

You should get the output as follows:

     uptime 
--------------------------------------
 2 days 02:48:04

How it works…

Postgres stores the server start time, so we can access it directly, as follows:

postgres=# SELECT pg_postmaster_start_time(); 
pg_postmaster_start_time 
----------------------------------------------
2021-10-01 19:37:41.389134+00

Then, we can write a SQL query to get the uptime, like this:

postgres=# SELECT current_timestamp - pg_postmaster_start_time(); 
?column? 
-----------------
 02:59:18.925917

Finally, we can apply some formatting:

postgres=# SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime; 
     uptime 
----------
 03:00:26

See also

This is simple stuff. Further monitoring and statistics are covered in Chapter 8, Monitoring and Diagnosis.