Hello, I want to suggest a client-side little function, implemented
in the attached patch.
Function pg_oldest_xlog_location gets us the oldest LSN (Log Sequence
Number) in xlog.
It is useful additional tool for DBA (we can get replicationSlotMinLSN,
so why not in master), it can show us, if xlog replication or wal-sender
is working properly or indicate if replication on startup can get up to
date with master, or after long turnoff must be recovered from archive.
Anyway, does it look useful enough to be part of postgres?
I guess I should push this to commitfest if that's the case.
Best regards,
--
Ivan Kartyshov
Postgres Professional: www.postgrespro.com
Russian Postgres Company
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f9eea76..f774233 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -16908,6 +16908,8 @@ SELECT set_config('log_statement_stats', 'off', false);
</indexterm>
<indexterm>
<primary>pg_current_xlog_location</primary>
+ <indexterm>
+ <primary>pg_oldest_xlog_location</primary>
</indexterm>
<indexterm>
<primary>pg_start_backup</primary>
@@ -16981,6 +16983,13 @@ SELECT set_config('log_statement_stats', 'off', false);
</row>
<row>
<entry>
+ <literal><function>pg_oldest_xlog_location()</function></literal>
+ </entry>
+ <entry><type>pg_lsn</type></entry>
+ <entry>Get the oldest WAL LSN (log sequence number)</entry>
+ </row>
+ <row>
+ <entry>
<literal><function>pg_start_backup(<parameter>label</> <type>text</> <optional>, <parameter>fast</> <type>boolean</> </optional>)</function></literal>
</entry>
<entry><type>pg_lsn</type></entry>
@@ -17096,6 +17105,7 @@ postgres=# select pg_start_backup('label_goes_here');
</para>
<para>
+ <function>pg_oldest_xlog_location</> displays the oldest WAL LSN.
<function>pg_current_xlog_location</> displays the current transaction log write
location in the same format used by the above functions. Similarly,
<function>pg_current_xlog_insert_location</> displays the current transaction log
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index 6cb690c..5a0e887 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -860,6 +860,8 @@ primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
The last WAL receive location in the standby is also displayed in the
process status of the WAL receiver process, displayed using the
<command>ps</> command (see <xref linkend="monitoring-ps"> for details).
+ Also we can get the oldest WAL LSN (Log Sequence Number)
+ <function>pg_oldest_xlog_location</>, it can give us a useful tool for DBA, additionally it can show us, if xlog replication or wal-sender is working properly or indicate if replication on startup can can get up to date with master, or after long turnoff must be recovered from archive.
</para>
<para>
You can retrieve a list of WAL sender processes via the
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 94b79ac..067d51c 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -10669,6 +10669,18 @@ GetXLogWriteRecPtr(void)
}
/*
+ * Get oldest WAL write pointer
+ */
+XLogRecPtr
+GetXLogOldestLSNPtr(void)
+{
+ XLogRecPtr result;
+
+ XLogSegNoOffsetToRecPtr(XLogGetLastRemovedSegno()+1, 1, result);
+ return result;
+}
+
+/*
* Returns the redo pointer of the last checkpoint or restartpoint. This is
* the oldest point in WAL that we still need, if we have to restart recovery.
*/
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 31cbb01..44e01e1 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -195,6 +195,27 @@ pg_current_xlog_location(PG_FUNCTION_ARGS)
}
/*
+ * Report the oldest WAL write location (same format as pg_start_backup etc)
+ *
+ * This is useful for determining the first LSN in existing sequences
+ */
+Datum
+pg_oldest_xlog_location(PG_FUNCTION_ARGS)
+{
+ XLogRecPtr oldest_recptr;
+
+ if (RecoveryInProgress())
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("recovery is in progress"),
+ errhint("WAL control functions cannot be executed during recovery.")));
+
+ oldest_recptr = GetXLogOldestLSNPtr();
+
+ PG_RETURN_LSN(oldest_recptr);
+}
+
+/*
* Report the current WAL insert location (same format as pg_start_backup etc)
*
* This function is mostly for debugging purposes.
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index b24e434..3c2cefb 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5202,6 +5202,10 @@ DESCR("get an individual replication origin's replication progress");
DATA(insert OID = 6014 ( pg_show_replication_origin_status PGNSP PGUID 12 1 100 0 0 f f f f f t v r 0 0 2249 "" "{26,25,3220,3220}" "{o,o,o,o}" "{local_id, external_id, remote_lsn, local_lsn}" _null_ _null_ pg_show_replication_origin_status _null_ _null_ _null_ ));
DESCR("get progress for all replication origins");
+
+DATA(insert OID = 6015 ( pg_oldest_xlog_location PGNSP PGUID 12 1 0 0 0 f f f f t f v s 0 0 3220 "" _null_ _null_ _null_ _null_ _null_ pg_oldest_xlog_location _null_ _null_ _null_ ));
+DESCR("pg oldest xlog location");
+
/* rls */
DATA(insert OID = 3298 ( row_security_active PGNSP PGUID 12 1 0 0 0 f f f f t f s s 1 0 16 "26" _null_ _null_ _null_ _null_ _null_ row_security_active _null_ _null_ _null_ ));
DESCR("row security for current context active on table by table oid");
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
