PostgreSQL Uptime

Summary: in this tutorial, you will learn how to calculate the PostgreSQL uptime based on the current time and the server’s started time.

Checking PostgreSQL uptime

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the PostgreSQL server:

psql -U postgres

Second, execute the following query to get the PostgreSQL uptime:

SELECT 
  date_trunc(
    'second', 
    current_timestamp - pg_postmaster_start_time()
  ) as uptime;Code language: SQL (Structured Query Language) (sql)

How it works

PostgreSQL stores the time when it was started in the database server. To retrieve the start time, you use the pg_postmaster_start_time() function as follows:

SELECT pg_postmaster_start_time();Code language: SQL (Structured Query Language) (sql)

Output:

   pg_postmaster_start_time
-------------------------------
 2024-02-14 03:41:32.048451-07
(1 row)Code language: CSS (css)

You can then calculate the uptime based on the current time and the start time returned by the pg_postmaster_start_time() function:

SELECT current_timestamp - pg_postmaster_start_time() uptime;Code language: SQL (Structured Query Language) (sql)

Output:

         uptime
------------------------
 6 days 07:39:06.006459
(1 row)Code language: CSS (css)

You can truncate the microsecond from the uptime using the DATE_TRUNC() function to make the output more human-readable:

SELECT 
  date_trunc(
    'second', 
    current_timestamp - pg_postmaster_start_time()
  ) as uptime;Code language: SQL (Structured Query Language) (sql)

Output:

     uptime
-----------------
 6 days 07:39:24
(1 row)Code language: CSS (css)

Summary

• Calculate the PostgreSQL uptime using the current time and start time.