testing cvs HEAD - HS/SR - xlog timeline 0 pg_xlogfile_name_offset
I have two 9.0devel machines (*not* alpha but cvs as of 2010.02.19 22.48).
One primary, one slave.
In an attempt to keep track of standby progression (versus primary), I ran slave-side:
replicas=# select
pg_last_xlog_replay_location()
pg_xlogfile_name_offset(pg_last_xlog_replay_location())
, pg_last_xlog_receive_location()
, pg_xlogfile_name_offset(pg_last_xlog_receive_location())
;
pg_last_xlog_replay_location | pg_xlogfile_name_offset |
------------------------------+------------------------------------+-
E2/C012AD90 | (00000000000000E2000000C0,1224080) |
pg_last_xlog_receive_location | pg_xlogfile_name_offset
-------------------------------+-----------------------------------
E2/C012AD90 | (00000000000000E2000000C0,1224080)
(1 row)
These zero-timeline filenames look suspicious, no?
I understand timeline-count to normally start at 1, not 0?
The replication seems to be running fine (680 GB).
ps seems to report the right xlog filename (slave):
/var/data1/pg_stuff/pg_installations/pgsql.sr_hotslave/bin/postgres -D
/var/data1/pg_stuff/pg_installations/pgsql.sr_hotslave/data
\_ postgres: startup process recovering 00000001000000E2000000C0
\_ postgres: wal receiver process streaming E2/C012AE28
\_ postgres: writer process
\_ postgres: stats collector process
replicas=# select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.0devel on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.3, 64-bit
(1 row)
Is the filename that pg_xlogfile_name_offset( pg_last_xlog_(replay|receive)_location() ) reports a
bug, or expected as shown?
thanks,
Erik Rijkers
I should have added that (on the slave) all pg_xlog/ files have timeline 1
like: 00000001000000E2000000C0
Show quoted text
On Wed, February 24, 2010 20:40, Erikruary 24, 2010 20:40, Er Rijkers wrote:
I have two 9.0devel machines (*not* alpha but cvs as of 2010.02.19 22.48).
One primary, one slave.
In an attempt to keep track of standby progression (versus primary), I ran slave-side:
replicas=# select
pg_last_xlog_replay_location()
pg_xlogfile_name_offset(pg_last_xlog_replay_location())
, pg_last_xlog_receive_location()
, pg_xlogfile_name_offset(pg_last_xlog_receive_location())
;
pg_last_xlog_replay_location | pg_xlogfile_name_offset |
------------------------------+------------------------------------+-
E2/C012AD90 | (00000000000000E2000000C0,1224080) |pg_last_xlog_receive_location | pg_xlogfile_name_offset
-------------------------------+-----------------------------------
E2/C012AD90 | (00000000000000E2000000C0,1224080)
(1 row)These zero-timeline filenames look suspicious, no?
I understand timeline-count to normally start at 1, not 0?The replication seems to be running fine (680 GB).
ps seems to report the right xlog filename (slave):
/var/data1/pg_stuff/pg_installations/pgsql.sr_hotslave/bin/postgres -D
/var/data1/pg_stuff/pg_installations/pgsql.sr_hotslave/data
\_ postgres: startup process recovering 00000001000000E2000000C0
\_ postgres: wal receiver process streaming E2/C012AE28
\_ postgres: writer process
\_ postgres: stats collector processreplicas=# select version();
version
------------------------------------------------------------------------------------------
PostgreSQL 9.0devel on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.3, 64-bit
(1 row)Is the filename that pg_xlogfile_name_offset( pg_last_xlog_(replay|receive)_location() ) reports a
bug, or expected as shown?thanks,
Erik Rijkers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, February 24, 2010 20:40, Erik Rijkers wrote:
pg_last_xlog_receive_location | pg_xlogfile_name_offset
-------------------------------+-----------------------------------
E2/C012AD90 | (00000000000000E2000000C0,1224080)
(1 row)These zero-timeline filenames look suspicious, no?
Sorry, I've only now noticed that this issue is already on the v9.0 TODO for Streaming Replication:
"pg_xlogfile_name(pg_last_xlog_receive/replay_location()) might report the wrong name. Because a
backend cannot know the actual timeline which is related to the location.
"
http://archives.postgresql.org/message-id/3f0b79eb1001190135vd9f62f1sa7868abc1ea61d12@mail.gmail.com
so, nevermind..
Erik Rijkers
On Thu, Feb 25, 2010 at 9:31 AM, Erik Rijkers <er@xs4all.nl> wrote:
On Wed, February 24, 2010 20:40, Erik Rijkers wrote:
pg_last_xlog_receive_location | pg_xlogfile_name_offset
-------------------------------+-----------------------------------
E2/C012AD90 | (00000000000000E2000000C0,1224080)
(1 row)These zero-timeline filenames look suspicious, no?
Sorry, I've only now noticed that this issue is already on the v9.0 TODO for Streaming Replication:
"pg_xlogfile_name(pg_last_xlog_receive/replay_location()) might report the wrong name. Because a
backend cannot know the actual timeline which is related to the location.
"http://archives.postgresql.org/message-id/3f0b79eb1001190135vd9f62f1sa7868abc1ea61d12@mail.gmail.com
so, nevermind..
Yeah, since I thought that the current behavior that you reported
would annoy many users, I added it to the TODO list.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
Fujii Masao wrote:
On Thu, Feb 25, 2010 at 9:31 AM, Erik Rijkers <er@xs4all.nl> wrote:
On Wed, February 24, 2010 20:40, Erik Rijkers wrote:
pg_last_xlog_receive_location | pg_xlogfile_name_offset
-------------------------------+-----------------------------------
E2/C012AD90 | (00000000000000E2000000C0,1224080)
(1 row)These zero-timeline filenames look suspicious, no?
Sorry, I've only now noticed that this issue is already on the v9.0 TODO for Streaming Replication:
"pg_xlogfile_name(pg_last_xlog_receive/replay_location()) might report the wrong name. Because a
backend cannot know the actual timeline which is related to the location.
"http://archives.postgresql.org/message-id/3f0b79eb1001190135vd9f62f1sa7868abc1ea61d12@mail.gmail.com
so, nevermind..
Yeah, since I thought that the current behavior that you reported
would annoy many users, I added it to the TODO list.
Yeah, returning a filename with TLI 0 sure doesn't seem right.
A quick fix would be to just throw an error if you try to use
pg_xlog_filename() during hot standby. But there seems to be good
reasons to call pg_xlog_filename() during hot standby, given that both
of you ran into the same issue. What exactly were you trying to achieve
with it?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Thu, Feb 25, 2010 at 5:10 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
A quick fix would be to just throw an error if you try to use
pg_xlog_filename() during hot standby. But there seems to be good
reasons to call pg_xlog_filename() during hot standby, given that both
of you ran into the same issue. What exactly were you trying to achieve
with it?
Nothing ;) Frankly I just found that problem while testing the
combination of SR and system administration functions. But on
second thought, calling pg_xlogfile_name() during HS seems useful
to remove old WAL files from the archive that is shared from
multiple standbys. In this case, '%r' in restore_command cannot
be used, so we would need to calculate the name of the WAL files
that are not required for the subsequent recovery yet by using
pg_xlogfile_name() and pg_controldata etc.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
Fujii Masao wrote:
On Thu, Feb 25, 2010 at 5:10 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:A quick fix would be to just throw an error if you try to use
pg_xlog_filename() during hot standby. But there seems to be good
reasons to call pg_xlog_filename() during hot standby, given that both
of you ran into the same issue. What exactly were you trying to achieve
with it?Nothing ;) Frankly I just found that problem while testing the
combination of SR and system administration functions. But on
second thought, calling pg_xlogfile_name() during HS seems useful
to remove old WAL files from the archive that is shared from
multiple standbys. In this case, '%r' in restore_command cannot
be used, so we would need to calculate the name of the WAL files
that are not required for the subsequent recovery yet by using
pg_xlogfile_name() and pg_controldata etc.
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?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Thu, Feb 25, 2010 at 7:22 PM, Heikki Linnakangas
<heikki.linnakangas@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
It would be useful if we can know that location via SQL rather
than pg_controldata. Which should that function return, filename
or location? If we'll prevent pg_xlogfile_name() from being called
during recovery according to your suggestion, it should return the
filename.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
On Thu, Feb 25, 2010 at 7:48 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
On Thu, Feb 25, 2010 at 7:22 PM, Heikki Linnakangas
<heikki.linnakangas@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
Attachments:
last_ckpt_start_location_v1.patchtext/x-patch; charset=US-ASCII; name=last_ckpt_start_location_v1.patchDownload
*** 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");