On Thu, Feb 25, 2010 at 7:48 PM, Fujii Masao <masao.fu...@gmail.com> wrote:
> On Thu, Feb 25, 2010 at 7:22 PM, Heikki Linnakangas
> <heikki.linnakan...@enterprisedb.com> wrote:
>> Yeah. The current pg_*_last_location() functions don't cut it though,
>> you need to retain logs back to the redo location of the last
>> restartpoint. That's what %r returns. Maybe we should add another function?
>
> +1
The attached patch introduces new function 'pg_last_checkpoint_start_location'
(better name?) that reports the XLOG location where the last checkpoint or
restartpoint started (i.e., the REDO starting location). This would be useful
to truncate the archived files to just the minimum required for recovery.
Is it worth applying this patch?
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 13048,13053 **** SELECT set_config('log_statement_stats', 'off', false);
--- 13048,13056 ----
<primary>pg_current_xlog_insert_location</primary>
</indexterm>
<indexterm>
+ <primary>pg_last_checkpoint_start_location</primary>
+ </indexterm>
+ <indexterm>
<primary>pg_xlogfile_name_offset</primary>
</indexterm>
<indexterm>
***************
*** 13110,13115 **** SELECT set_config('log_statement_stats', 'off', false);
--- 13113,13125 ----
</row>
<row>
<entry>
+ <literal><function>pg_last_checkpoint_start_location</function>()</literal>
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>Get transaction log location where last checkpoint started</entry>
+ </row>
+ <row>
+ <entry>
<literal><function>pg_xlogfile_name_offset</function>(<parameter>location</> <type>text</>)</literal>
</entry>
<entry><type>text</>, <type>integer</></entry>
***************
*** 13186,13191 **** postgres=# select pg_start_backup('label_goes_here');
--- 13196,13211 ----
</para>
<para>
+ <function>pg_last_checkpoint_start_location</> displays the transaction log location
+ where the last checkpoint (or restartpoint) started. The return location indicates
+ the earliest file that must be kept to allow the server to be restartable, so this
+ information can be used to truncate the archive to just the minimum required to
+ support restarting from the current database cluster. This function may be executed
+ during both recovery and in normal running. Also this function is read-only operation
+ and does not require superuser permission.
+ </para>
+
+ <para>
You can use <function>pg_xlogfile_name_offset</> to extract the
corresponding transaction log file name and byte offset from the results of any of the
above functions. For example:
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***************
*** 8316,8321 **** pg_last_xlog_replay_location(PG_FUNCTION_ARGS)
--- 8316,8343 ----
}
/*
+ * Report the last checkpoint (or restartpoint) start location
+ * (same format as pg_start_backup etc)
+ *
+ * This is useful for determining which archived WAL files are
+ * not required for the server and can be removed.
+ */
+ Datum
+ pg_last_checkpoint_start_location(PG_FUNCTION_ARGS)
+ {
+ XLogRecPtr recptr;
+ char location[MAXFNAMELEN];
+
+ LWLockAcquire(ControlFileLock, LW_SHARED);
+ recptr = ControlFile->checkPointCopy.redo;
+ LWLockRelease(ControlFileLock);
+
+ snprintf(location, sizeof(location), "%X/%X",
+ recptr.xlogid, recptr.xrecoff);
+ PG_RETURN_TEXT_P(cstring_to_text(location));
+ }
+
+ /*
* Compute an xlog file name and decimal byte offset given a WAL location,
* such as is returned by pg_stop_backup() or pg_xlog_switch().
*
*** a/src/include/access/xlog_internal.h
--- b/src/include/access/xlog_internal.h
***************
*** 268,273 **** extern Datum pg_current_xlog_location(PG_FUNCTION_ARGS);
--- 268,274 ----
extern Datum pg_current_xlog_insert_location(PG_FUNCTION_ARGS);
extern Datum pg_last_xlog_receive_location(PG_FUNCTION_ARGS);
extern Datum pg_last_xlog_replay_location(PG_FUNCTION_ARGS);
+ extern Datum pg_last_checkpoint_start_location(PG_FUNCTION_ARGS);
extern Datum pg_xlogfile_name_offset(PG_FUNCTION_ARGS);
extern Datum pg_xlogfile_name(PG_FUNCTION_ARGS);
extern Datum pg_is_in_recovery(PG_FUNCTION_ARGS);
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 3322,3327 **** DATA(insert OID = 3820 ( pg_last_xlog_receive_location PGNSP PGUID 12 1 0 0 f f
--- 3322,3329 ----
DESCR("current xlog flush location");
DATA(insert OID = 3821 ( pg_last_xlog_replay_location PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_last_xlog_replay_location _null_ _null_ _null_ ));
DESCR("last xlog replay location");
+ DATA(insert OID = 3822 ( pg_last_checkpoint_start_location PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 "" _null_ _null_ _null_ _null_ pg_last_checkpoint_start_location _null_ _null_ _null_ ));
+ DESCR("last checkpoint start location");
DATA(insert OID = 2621 ( pg_reload_conf PGNSP PGUID 12 1 0 0 f f f t f v 0 0 16 "" _null_ _null_ _null_ _null_ pg_reload_conf _null_ _null_ _null_ ));
DESCR("reload configuration files");
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
