Hi,
Thanks for taking a look at the patch, and for your feedback.
On Wed, 5 Mar 2025 at 03:22, Fujii Masao <masao.fu...@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. https://www.postgresql.org/message-id/Z7J_UZYfvtPiNMSy%40paquier.xyz
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
