Thread: Re: Add pg_accept_connections_start_time() for better uptime calculation

On 2025/02/16 16:05, Robins Tharakan wrote:
> 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.

Shouldn't this function also handle the time when the postmaster
starts accepting read-only connections? With the patch, it doesn’t
seem to cover that case, and it looks like an unexpected timestamp
is returned when run on a standby server. Maybe the function should
return a record with two columns — one for when the postmaster
starts accepting read-only connections and another for normal
connections?

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

On 2025/03/06 21:55, Robins Tharakan wrote:
> Hi,
> 
> Thanks for taking a look at the patch, and for your feedback.
> 
> On Wed, 5 Mar 2025 at 03:22, Fujii Masao <masao.fujii@oss.nttdata.com <mailto:masao.fujii@oss.nttdata.com>> wrote:
> 
>     On 2025/02/16 16:05, Robins Tharakan wrote:
>      > This patch introduces a new function pg_accept_connections_start_time().
> 
>     Shouldn't this function also handle the time when the postmaster
>     starts accepting read-only connections? With the patch, it doesn’t
>     seem to cover that case, and it looks like an unexpected timestamp
>     is returned when run on a standby server. Maybe the function should
>     return a record with two columns — one for when the postmaster
>     starts accepting read-only connections and another for normal
>     connections?
> 
> 
> 
> I am not sure if I understand the question. For a given (re)start, a
> database user would either be looking for a read-only or a read-write
> start time (based on whether the server is a standby or not). Are you
> saying that for a given instance of start, a database user would be
> interested in two timestamps (once when the database became
> ready to accept read-only connections, and then quickly thereafter
> also began accepting read-writes?) Even if possible, that feels
> unnecessary - but I may be misunderstanding here.

With the v1 patch, running pg_accept_connections_start_time() on
a standby returned an unexpected timestamp:

=# select * from pg_accept_connections_start_time();
  pg_accept_connections_start_time
----------------------------------
  2000-01-01 09:00:00+09

So my comment meant that this seems odd and should be fixed.
Since I've not fully understood how this function is used,
I'm not sure what timestamp should be returned in the standby.
But I just thought it seems intuitive to return the timestamp
when the standby started accepting read-only connections, in that case.


> But you bring up a good point around standbys. Attached is v2 of
> the patch that returns a more accurate time on a standby (ie. it
> captures the time just after emitting a message that it's ready for
> read-only connections).

Thanks for updating the patch!

With v2 patch. when the standby is promoted to primary,
the result of pg_postmaster_open_time() appears to switch to
the time when the primary began accepting normal connections.
If this is intentional, it's better to clarify this behavior
in the documentation.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION