Add pg_accept_connections_start_time() for better uptime calculation

Started by Robins Tharakan11 months ago8 messages

tharakan@gmail.com

11 months ago

1 attachment(s)

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:
/messages/by-id/598d4a75-57d9-b41a-a927-7584be6278b2@rblst.info

2. IIUC knowing that a crash-recovery happened may have helped here?
/messages/by-id/954419.1623092217@sss.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)

```

Attachments:

v1-0001-Add-support-for-pg_accept_connections_start_time.patchapplication/x-patch; name=v1-0001-Add-support-for-pg_accept_connections_start_time.patchDownload

From 7521d666ce66347f894acf435d67ea69a6ecc677 Mon Sep 17 00:00:00 2001
From: Robins Tharakan <tharakan@gmail.com>
Date: Fri, 14 Feb 2025 20:51:10 +1030
Subject: [PATCH v1] Add support for pg_accept_connections_start_time()

It is not always reliable to depend on pg_postmaster_start_time()
database uptime calculations, owing to how postmaster catches
child process crashes, startup recovery etc. and continue without
a restart. This could lead to multiple seconds (or minutes/hours)
of difference when although postmaster was up, but the database
was not available for accepting connections.

This function returns the start time when the database was ready
to accept new database connections, allowing better calculation
of database availability.
---
 doc/src/sgml/func.sgml                  | 13 +++++++++++++
 src/backend/postmaster/launch_backend.c |  3 +++
 src/backend/postmaster/postmaster.c     |  5 +++++
 src/backend/tcop/postgres.c             |  5 +++++
 src/backend/utils/adt/timestamp.c       |  9 +++++++++
 src/include/catalog/pg_proc.dat         |  6 ++++++
 src/include/utils/timestamp.h           |  3 +++
 7 files changed, 44 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7efc81936ab..3bfbc384ea8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24935,6 +24935,19 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_accept_connections_start_time</primary>
+        </indexterm>
+        <function>pg_accept_connections_start_time</function> ()
+        <returnvalue>timestamp with time zone</returnvalue>
+       </para>
+       <para>
+        Returns the time when the server was ready to accept connections.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/postmaster/launch_backend.c b/src/backend/postmaster/launch_backend.c
index a97a1eda6da..985cda2a032 100644
--- a/src/backend/postmaster/launch_backend.c
+++ b/src/backend/postmaster/launch_backend.c
@@ -110,6 +110,7 @@ typedef struct
 	ProcSignalHeader *ProcSignal;
 	pid_t		PostmasterPid;
 	TimestampTz PgStartTime;
+	TimestampTz PgAcceptConnStartTime;
 	TimestampTz PgReloadTime;
 	pg_time_t	first_syslogger_file_time;
 	bool		redirection_done;
@@ -738,6 +739,7 @@ save_backend_variables(BackendParameters *param,
 
 	param->PostmasterPid = PostmasterPid;
 	param->PgStartTime = PgStartTime;
+	param->PgAcceptConnStartTime = PgAcceptConnStartTime;
 	param->PgReloadTime = PgReloadTime;
 	param->first_syslogger_file_time = first_syslogger_file_time;
 
@@ -998,6 +1000,7 @@ restore_backend_variables(BackendParameters *param)
 
 	PostmasterPid = param->PostmasterPid;
 	PgStartTime = param->PgStartTime;
+	PgAcceptConnStartTime = param->PgAcceptConnStartTime;
 	PgReloadTime = param->PgReloadTime;
 	first_syslogger_file_time = param->first_syslogger_file_time;
 
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index bb22b13adef..7bcecdbb830 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -2329,6 +2329,11 @@ process_pm_child_exit(void)
 			 */
 			StartWorkerNeeded = true;
 
+			/*
+			* Remember time when database was ready to accept connections
+			*/
+			PgAcceptConnStartTime = GetCurrentTimestamp();
+
 			/* at this point we are really open for business */
 			ereport(LOG,
 					(errmsg("database system is ready to accept connections")));
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 1149d89d7a1..308930d5d76 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -4117,6 +4117,11 @@ PostgresSingleUserMain(int argc, char *argv[],
 	 */
 	PgStartTime = GetCurrentTimestamp();
 
+	/*
+	 * Remember time when stand-alone came up to accept user commands.
+	 */
+	PgAcceptConnStartTime = GetCurrentTimestamp();
+
 	/*
 	 * Create a per-backend PGPROC struct in shared memory. We must do this
 	 * before we can use LWLocks.
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index ba9bae05069..56cd4bfd3d9 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -52,6 +52,9 @@
 /* Set at postmaster start */
 TimestampTz PgStartTime;
 
+/* Set when database is ready to accept connections */
+TimestampTz PgAcceptConnStartTime;
+
 /* Set at configuration reload */
 TimestampTz PgReloadTime;
 
@@ -1628,6 +1631,12 @@ pg_postmaster_start_time(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMPTZ(PgStartTime);
 }
 
+Datum
+pg_accept_connections_start_time(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_TIMESTAMPTZ(PgAcceptConnStartTime);
+}
+
 Datum
 pg_conf_load_time(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9e803d610d7..2a1f27ea600 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8650,6 +8650,12 @@
   prorettype => 'timestamptz', proargtypes => '',
   prosrc => 'pg_postmaster_start_time' },
 
+# accept connections start time function
+{ oid => '8600', descr => 'accept connections start time',
+  proname => 'pg_accept_connections_start_time', provolatile => 's',
+  prorettype => 'timestamptz', proargtypes => '',
+  prosrc => 'pg_accept_connections_start_time' },
+
 # config reload time function
 { oid => '2034', descr => 'configuration load time',
   proname => 'pg_conf_load_time', provolatile => 's', proparallel => 'r',
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index d26f023fb87..e1eae7ddfc8 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -89,6 +89,9 @@ IntervalPGetDatum(const Interval *X)
 /* Set at postmaster start */
 extern PGDLLIMPORT TimestampTz PgStartTime;
 
+/* Set when database is ready to accept connections */
+extern PGDLLIMPORT TimestampTz PgAcceptConnStartTime;
+
 /* Set at configuration reload */
 extern PGDLLIMPORT TimestampTz PgReloadTime;
 
-- 
2.43.0

Laurenz Albe

laurenz.albe@cybertec.at

11 months ago

In reply to: Robins Tharakan (#1)

Re: Add pg_accept_connections_start_time() for better uptime calculation

On Sun, 2025-02-16 at 17:35 +1030, Robins Tharakan wrote:

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.

I myself have never felt the need for such a function - but perhaps it
can be useful in these times of hosted database services, when accessing
the log file might be difficult.

I would probably have called the function pg_uptime(), yet maybe that
is too Unix-centric.

Would it make sense to add that information to the output of
"pg_ctl status" as well? Perhaps as a new option, so that default output
format doesn't change.

Yours,
Laurenz Albe

*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.

*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are
confidential and may be privileged or otherwise protected from disclosure
and solely for the use of the person(s) or entity to whom it is intended.
If you have received this message in error and are not the intended
recipient, please notify the sender immediately and delete this message and
any attachment from your system. If you are not the intended recipient, be
advised that any use of this message is prohibited and may be unlawful, and
you must not copy this message or attachment or disclose the contents to
any other person.

Michael Paquier

michael@paquier.xyz

11 months ago

In reply to: Laurenz Albe (#2)

Re: Add pg_accept_connections_start_time() for better uptime calculation

On Sun, Feb 16, 2025 at 11:47:46AM +0100, Laurenz Albe wrote:

I myself have never felt the need for such a function - but perhaps it
can be useful in these times of hosted database services, when accessing
the log file might be difficult.

Yeah. I'm not surprised by that.

I would probably have called the function pg_uptime(), yet maybe that
is too Unix-centric.

pg_accept_connections_start_time() is very verbose. As it is about
the postmaster being open to connections, perhaps
pg_postmaster_open_time() for consistency with the existing start
function?

Would it make sense to add that information to the output of
"pg_ctl status" as well? Perhaps as a new option, so that default output
format doesn't change.

Hmm. Sounds to me that we could just have a SQL function that's able
to parse postmaster.pid and returns its data in a format that makes
its post-processing easier if we're OK to live with the fact that this
could only be queried when the postmaster is able to accept
connections, like a JSON object with dedicated key/value pairs.

A separate function that only returns the open-for-connection time has
benefits on its own because its execution can be granted to separate
users, without knowing about the full contents of postmaster.pid.
That last part should matter for cloud vendors. And this file only
knows about MyStartTime.
--
Michael

Tom Lane

tgl@sss.pgh.pa.us

11 months ago

In reply to: Michael Paquier (#3)

Re: Add pg_accept_connections_start_time() for better uptime calculation

Michael Paquier <michael@paquier.xyz> writes:

On Sun, Feb 16, 2025 at 11:47:46AM +0100, Laurenz Albe wrote:

Would it make sense to add that information to the output of
"pg_ctl status" as well? Perhaps as a new option, so that default output
format doesn't change.

A separate function that only returns the open-for-connection time has
benefits on its own because its execution can be granted to separate
users, without knowing about the full contents of postmaster.pid.
That last part should matter for cloud vendors. And this file only
knows about MyStartTime.

Yeah. Making that happen would require extending the contents of
postmaster.pid, which is likely to break assorted peoples' tooling.
I doubt that this feature clears the bar for justifying that.

regards, tom lane

Michael Paquier

michael@paquier.xyz

11 months ago

In reply to: Tom Lane (#4)

Re: Add pg_accept_connections_start_time() for better uptime calculation

On Sun, Feb 16, 2025 at 07:53:06PM -0500, Tom Lane wrote:

Yeah. Making that happen would require extending the contents of
postmaster.pid, which is likely to break assorted peoples' tooling.
I doubt that this feature clears the bar for justifying that.

Sure, agreed to not touch postmaster.pid.

Now my point is also that I would not object to a patch that wants to
show the information of postmaster.pid in a nicer way than it is now
through SQL, as one tuple with one attribute per field written, or
something like a JSON object. With the format of postmaster.pid being
very stable across releases, perhaps one attribute per field is
better.
--
Michael

Fujii Masao

masao.fujii@oss.nttdata.com

10 months ago

In reply to: Robins Tharakan (#1)

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

Robins Tharakan

tharakan@gmail.com

10 months ago

In reply to: Fujii Masao (#6)

1 attachment(s)

Re: Add pg_accept_connections_start_time() for better uptime calculation

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>
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.

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).

Also, while at it, I also implemented Michael's suggestion [1] for
a better name pg_postmaster_open_time() which is in line with
the existing pg_postmaster_start_time().

Also, updated the documentation to reflect the above, patch
passes `make check` and applies cleanly on HEAD as of
588acf6d0ec1 (6th Mar).

-
robins

Reference:
1. /messages/by-id/Z7J_UZYfvtPiNMSy@paquier.xyz

Attachments:

v2-0001-Add-support-for-pg_postmaster_open_time.patchapplication/x-patch; name=v2-0001-Add-support-for-pg_postmaster_open_time.patchDownload

From 5e9186ca263b821ddc385259a7369fdf2d287f96 Mon Sep 17 00:00:00 2001
From: Robins Tharakan <tharakan@gmail.com>
Date: Thu, 6 Mar 2025 21:32:59 +1030
Subject: [PATCH v2] Add support for pg_postmaster_open_time()

It is not always reliable to depend on pg_postmaster_start_time()
for database uptime calculations, owing to how postmaster catches
child process crashes, startup recovery etc. and continues without
a restart. This could lead to multiple seconds (minutes or even
hours) of difference when although postmaster was up, the database
was not accepting connections.

This function returns the start time when the database was ready
to accept new database connections, allowing better calculation
of database availability.

On a standby, this time reflects the time the database was ready
to accept read-only connections, whereas in single-user mode
this time reflects the time the database was ready to accept
user commands.
---
 doc/src/sgml/func.sgml                  | 16 ++++++++++++++++
 src/backend/postmaster/launch_backend.c |  3 +++
 src/backend/postmaster/postmaster.c     | 10 ++++++++++
 src/backend/tcop/postgres.c             |  5 +++++
 src/backend/utils/adt/timestamp.c       |  9 +++++++++
 src/include/catalog/pg_proc.dat         |  6 ++++++
 src/include/utils/timestamp.h           |  3 +++
 7 files changed, 52 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f97f0ce570a..ee4cd6af638 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -24919,6 +24919,22 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_postmaster_open_time</primary>
+        </indexterm>
+        <function>pg_postmaster_open_time</function> ()
+        <returnvalue>timestamp with time zone</returnvalue>
+       </para>
+       <para>
+        Returns the time when the server was open to connections. On a standby
+        this will be the time when the server was open to read-only connections.
+        In single-user mode this returns the time the server was ready to accept
+        user commands.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/postmaster/launch_backend.c b/src/backend/postmaster/launch_backend.c
index 47375e5bfaa..f1c61f99418 100644
--- a/src/backend/postmaster/launch_backend.c
+++ b/src/backend/postmaster/launch_backend.c
@@ -110,6 +110,7 @@ typedef struct
 	ProcSignalHeader *ProcSignal;
 	pid_t		PostmasterPid;
 	TimestampTz PgStartTime;
+	TimestampTz PgOpenStartTime;
 	TimestampTz PgReloadTime;
 	pg_time_t	first_syslogger_file_time;
 	bool		redirection_done;
@@ -738,6 +739,7 @@ save_backend_variables(BackendParameters *param,
 
 	param->PostmasterPid = PostmasterPid;
 	param->PgStartTime = PgStartTime;
+	param->PgOpenStartTime = PgOpenStartTime;
 	param->PgReloadTime = PgReloadTime;
 	param->first_syslogger_file_time = first_syslogger_file_time;
 
@@ -998,6 +1000,7 @@ restore_backend_variables(BackendParameters *param)
 
 	PostmasterPid = param->PostmasterPid;
 	PgStartTime = param->PgStartTime;
+	PgOpenStartTime = param->PgOpenStartTime;
 	PgReloadTime = param->PgReloadTime;
 	first_syslogger_file_time = param->first_syslogger_file_time;
 
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index d2a7a7add6f..fe16f9ff34f 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -2329,6 +2329,11 @@ process_pm_child_exit(void)
 			 */
 			StartWorkerNeeded = true;
 
+			/*
+			 * Remember time when database was open to connections
+			 */
+			PgOpenStartTime = GetCurrentTimestamp();
+
 			/* at this point we are really open for business */
 			ereport(LOG,
 					(errmsg("database system is ready to accept connections")));
@@ -3669,6 +3674,11 @@ process_pm_pmsignal(void)
 		ereport(LOG,
 				(errmsg("database system is ready to accept read-only connections")));
 
+		/*
+		 * Remember time when database was open to connections
+		 */
+		PgOpenStartTime = GetCurrentTimestamp();
+
 		/* Report status */
 		AddToDataDirLockFile(LOCK_FILE_LINE_PM_STATUS, PM_STATUS_READY);
 #ifdef USE_SYSTEMD
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 947ffb40421..e3c502f721f 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -4119,6 +4119,11 @@ PostgresSingleUserMain(int argc, char *argv[],
 	 */
 	PgStartTime = GetCurrentTimestamp();
 
+	/*
+	 * Remember when stand-alone was open to user commands.
+	 */
+	PgOpenStartTime = GetCurrentTimestamp();
+
 	/*
 	 * Create a per-backend PGPROC struct in shared memory. We must do this
 	 * before we can use LWLocks.
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 9682f9dbdca..3e89d1e1993 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -52,6 +52,9 @@
 /* Set at postmaster start */
 TimestampTz PgStartTime;
 
+/* Set when database is open to connections */
+TimestampTz PgOpenStartTime;
+
 /* Set at configuration reload */
 TimestampTz PgReloadTime;
 
@@ -1628,6 +1631,12 @@ pg_postmaster_start_time(PG_FUNCTION_ARGS)
 	PG_RETURN_TIMESTAMPTZ(PgStartTime);
 }
 
+Datum
+pg_postmaster_open_time(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_TIMESTAMPTZ(PgOpenStartTime);
+}
+
 Datum
 pg_conf_load_time(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 134b3dd8689..a6c8ddcbbc5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8650,6 +8650,12 @@
   prorettype => 'timestamptz', proargtypes => '',
   prosrc => 'pg_postmaster_start_time' },
 
+# open to connections start time function
+{ oid => '8600', descr => 'open connections start time',
+  proname => 'pg_postmaster_open_time', provolatile => 's',
+  prorettype => 'timestamptz', proargtypes => '',
+  prosrc => 'pg_postmaster_open_time' },
+
 # config reload time function
 { oid => '2034', descr => 'configuration load time',
   proname => 'pg_conf_load_time', provolatile => 's', proparallel => 'r',
diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h
index 9963bddc0ec..9d65cad8447 100644
--- a/src/include/utils/timestamp.h
+++ b/src/include/utils/timestamp.h
@@ -89,6 +89,9 @@ IntervalPGetDatum(const Interval *X)
 /* Set at postmaster start */
 extern PGDLLIMPORT TimestampTz PgStartTime;
 
+/* Set when database is open to connections */
+extern PGDLLIMPORT TimestampTz PgOpenStartTime;
+
 /* Set at configuration reload */
 extern PGDLLIMPORT TimestampTz PgReloadTime;
 
-- 
2.39.5

Fujii Masao

masao.fujii@oss.nttdata.com

10 months ago

In reply to: Robins Tharakan (#7)

Re: Add pg_accept_connections_start_time() for better uptime calculation

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