Add pg_accept_connections_start_time() for better uptime calculation

Robins Tharakan <tharakan@gmail.com>

From: Robins Tharakan <tharakan@gmail.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-02-16T07:05:18Z
Lists: pgsql-hackers

Attachments

Hi,

This patch introduces a new function pg_accept_connections_start_time().

Currently, pg_postmaster_start_time() is used to determine when the
database started. However, this is not accurate since the postmaster
process can sometimes be up whereas the database is not accepting
connections (for e.g. during child process crash [1],
long crash-recovery etc.)

This can lead to inaccurate database uptime calculations.

The new function, pg_accept_connections_start_time(), returns the
time when the database became ready to accept connections. This is
helpful, since in both of the above cases (quick crash-recovery on
child process crash, long crash-recovery on startup), this timestamp
would get reset - an example scenario given below [3].

This function can be used to tell:
1. Whether the database did a quick crash-recovery (without
   a postmaster restart) in a production setup. In particular, this would
   help a long-running client confirm whether a connection blip was a
   server restart, or a session-abort / network / client-side issue [2].
2. Calculate database uptime (more accurately)

The patch passes `make check`, adds a brief function description
in func.sgml, works in single-user mode and applies cleanly on
master as of 9e17ac997 (14th Feb).

Look forward to feedback, but in particular:
- Good to have a second opinion on a better position to capture
timestamp during startup in single-user mode.
- Function name - I think it is too verbose, but it felt most unambiguous.

-
Thanks
Robins

1. pg_postmaster_start_time() doesn't tell when db became available:
https://www.postgresql.org/message-id/598d4a75-57d9-b41a-a927-7584be6278b2%40rblst.info

2. IIUC knowing that a crash-recovery happened may have helped here?
https://www.postgresql.org/message-id/954419.1623092217%40sss.pgh.pa.us

3. Sample usage of the function - Kill 'walwriter' to force postmaster
to do a quick crash-recovery - where pg_postmaster_start_time() does
not change, pg_accept_connections_start_time() does get updated
to the time when database (once again) became available for connections:
```
robins@camry:~/proj/postgres$ psql postgres -c "select
pg_accept_connections_start_time(), pg_postmaster_start_time();"
 pg_accept_connections_start_time |     pg_postmaster_start_time
----------------------------------+----------------------------------
 2025-02-16 11:40:37.355906+10:30 | 2025-02-16 11:40:37.351776+10:30
(1 row)

robins@camry:~/proj/postgres$ ps -ef  | grep postgres
robins   2935044       1  0 11:40 ?        00:00:00
/home/robins/proj/localpg/bin/postgres -D data
robins   2935045 2935044  0 11:40 ?        00:00:00 postgres: checkpointer
robins   2935046 2935044  0 11:40 ?        00:00:00 postgres: background
writer
robins   2935048 2935044  0 11:40 ?        00:00:00 postgres: walwriter
robins   2935049 2935044  0 11:40 ?        00:00:00 postgres: autovacuum
launcher
robins   2935050 2935044  0 11:40 ?        00:00:00 postgres: logical
replication launcher
robins   2937754 1769260  0 13:57 pts/1    00:00:00 grep --color=auto
postgres

robins@camry:~/proj/postgres$ kill -9 `ps -ef | grep postgres | grep
walwriter | awk '{print $2}'`

robins@camry:~/proj/postgres$ ps -ef | grep postgres
robins   2935044       1  0 11:40 ?        00:00:00
/home/robins/proj/localpg/bin/postgres -D data
robins   2937761 2935044  0 13:57 ?        00:00:00 postgres: checkpointer
robins   2937762 2935044  0 13:57 ?        00:00:00 postgres: background
writer
robins   2937763 2935044  0 13:57 ?        00:00:00 postgres: walwriter
robins   2937764 2935044  0 13:57 ?        00:00:00 postgres: autovacuum
launcher
robins   2937766 1769260  0 13:57 pts/1    00:00:00 grep --color=auto
postgres

robins@camry:~/proj/postgres$ psql postgres -c "select
pg_accept_connections_start_time(), pg_postmaster_start_time();"
 pg_accept_connections_start_time |     pg_postmaster_start_time
----------------------------------+----------------------------------
 2025-02-16 13:57:52.914587+10:30 | 2025-02-16 11:40:37.351776+10:30
(1 row)

```