PostgreSQL Server Uptime: How to Check It
This guide shows you how to check how long your PostgreSQL server has been running, also known as its uptime. You’ll learn several methods, from simple SQL queries to checking system-level process information, and how to interpret the results. No special tools are required—just access to the PostgreSQL command line or a system shell.
What You’ll Need
- Access to a PostgreSQL server (local or remote).
- Permission to run
psql(the PostgreSQL interactive terminal) or access to a system shell (like Bash) on the server. - Basic familiarity with running commands in a terminal or command prompt.
Step-by-Step
-
Connect to PostgreSQL using psql.
Open a terminal and run:
psql -U your_username -d your_database
Replace
your_usernameandyour_databasewith your actual credentials. If you’re connecting to a remote server, add-h hostnameand-p port. -
Run the uptime query.
Once inside the psql prompt, execute:
SELECT now() - pg_postmaster_start_time() AS uptime;
This returns an interval showing how long the server has been running. For example,
3 days 04:15:22.123456. -
Get a more readable format (optional).
If you prefer days, hours, minutes, and seconds separately, use:
SELECT EXTRACT(DAY FROM now() - pg_postmaster_start_time()) AS days, EXTRACT(HOUR FROM now() - pg_postmaster_start_time()) AS hours, EXTRACT(MINUTE FROM now() - pg_postmaster_start_time()) AS minutes, EXTRACT(SECOND FROM now() - pg_postmaster_start_time()) AS seconds;
-
Check the server start time itself.
To see the exact timestamp when PostgreSQL started, run:
SELECT pg_postmaster_start_time();
This is useful if you need to know the precise start moment, not just the duration.
-
Use a system command (if you have shell access).
On Linux, you can check the process start time with:
ps -eo pid,etime,cmd | grep postgres | grep -v grep
The
etimecolumn shows elapsed time in the formatDD-HH:MM:SS. For example,3-04:15:22means 3 days, 4 hours, 15 minutes, and 22 seconds. -
Check system uptime (if PostgreSQL started with the system).
If you want to know how long the entire server has been up (not just PostgreSQL), use:
uptime
This shows system uptime, which is often similar to PostgreSQL uptime if the database starts at boot, but they can differ if PostgreSQL was restarted independently.
Common Mistakes
- Confusing system uptime with PostgreSQL uptime. The
uptimecommand shows the OS uptime, not necessarily when PostgreSQL started. Always usepg_postmaster_start_time()for the database’s own uptime. - Using
SELECT now() - pg_postmaster_start_time()without parentheses. The function name ispg_postmaster_start_time()with empty parentheses. Omitting them causes a syntax error. - Expecting a simple number. The result is an interval, not a plain integer. If you need a number, extract the days or hours as shown in step 3.
- Running the query on the wrong database. The function works on any database, but if you’re connected to a standby replica, it shows the replica’s start time, not the primary’s.
Tips
- Use
\timingin psql to see how long the query itself takes, but that’s not the server uptime. - If you need uptime for monitoring, consider using a monitoring tool that queries
pg_postmaster_start_time()periodically, but for a one-off check, the SQL method is simplest. - On Windows, you can use
net stats srvorsysteminfofor system uptime, but for PostgreSQL specifically, the SQL query is still the best method. - If you have multiple PostgreSQL instances on the same machine, each has its own start time—query each instance separately.
Troubleshooting
- Error: function pg_postmaster_start_time() does not exist. This happens if you’re using a very old PostgreSQL version (before 8.4). Upgrade or use the system process method instead.
- Permission denied when running psql. Make sure your user has the necessary privileges to connect. You might need to use a superuser account or ask your DBA to grant access.
- Connection refused. Check if PostgreSQL is running and listening on the expected port. Use
pg_isreadyto test connectivity. - The query returns
NULL. This is rare, but if the server was started in a special way (e.g., during recovery), the function might return NULL. In that case, check the PostgreSQL log for the start time. - On Windows,
pscommand not found. Use PowerShell:Get-Process postgres | Select-Object StartTimeto see the process start time.
Frequently Asked Questions
Can I check uptime without connecting to the database?
Yes, if you have shell access to the server, you can use ps or systemctl status postgresql to see the process start time. But the SQL method is more accurate and works remotely.
What does the interval result mean?
It’s a PostgreSQL interval type, e.g., 1 day 02:03:04. It represents the duration since the server started. You can use it directly in calculations or extract parts.
Is uptime the same as the server’s start time?
No, uptime is the duration since start. Start time is the exact timestamp. You can derive one from the other using now().
Does a standby replica show the same uptime as the primary?
No, each PostgreSQL instance has its own start time. A standby that was started later will show a shorter uptime, even if the primary has been up longer.
Why would I need to know PostgreSQL uptime?
It helps in troubleshooting, verifying that a restart happened as expected, or checking how long the server has been running without interruption, which can be relevant for maintenance planning.
Can I get uptime in seconds only?
Yes, use EXTRACT(EPOCH FROM now() - pg_postmaster_start_time()) to get the total number of seconds since start.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.