Thread: 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)(1row) 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: statscollector 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 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 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 > > > -- > 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
Re: testing cvs HEAD - HS/SR - xlog timeline 0 pg_xlogfile_name_offset
From
Heikki Linnakangas
Date:
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
Re: testing cvs HEAD - HS/SR - xlog timeline 0 pg_xlogfile_name_offset
From
Heikki Linnakangas
Date:
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