Thread: Forcing wal rotation
Hi For my warm-standby-cluster I'm now saving the currently used wal using rsync, to avoid loosing data from a few hours (or days) ago, when there is little traffic, and thus the wal isn't rotated. For online backups, the problem is even worse, because a backup might me unuseable even hours after I called pg_stop_backup(), because the wal segment needed to bring the backup to a consistent state might not have been archived at that time. I've now thought about how to fix that without doing that rather crude rsync-pg_xlog-hack. I've read through the code, and learned that wal-segments are expected to have a specific size - thus rotating them "early" is not that easy. But now I figured that another short-term solution could probably be implemented easily. I'd like to write a function that just fills the wal with "nop" records, until the current used wal is full. Since I assume that there are already different kind of wal records, adding a NOP-record that just takes up space, and does nothing else, should be quite easy. And even if wals contain only one kind of record (Write this page to that datafile), I could just repeat the last records over and over again, until the wal is filled, couldn't I? Do you think that this is feasable? If so, I'd like to try it. greetings, Florian Pflug
A.M. wrote: > On Fri, July 14, 2006 11:20 am, Florian G. Pflug wrote: >> Hi >> >> >> For my warm-standby-cluster I'm now saving the currently used wal using >> rsync, to avoid loosing data from a few hours (or days) ago, when there is >> little traffic, and thus the wal isn't rotated. For online backups, the >> problem is even worse, because a backup might me unuseable even hours >> after I called pg_stop_backup(), because the wal segment needed to bring >> the backup to a consistent state might not have been archived at that >> time. >> > How about an SQL-level function that calls the wal scripts? This would > also allow "important" transactions to push data to the standy server > regardless of the wal size. That was the idea - providing pg_rotate_wal(), which would guarantee that the wal is rotatted at least once if called. Thinking further about this, for a first prove of concept, I'd be enough to write a C function pg_current_walsegment(). pg_rotate_wal() could then be a plpgsql function, that e.g. creates a temporary table, and fills it with data, until the return value of pg_current_walsegment() changes.
On Fri, Jul 14, 2006 at 05:36:58PM +0200, Florian G. Pflug wrote: > That was the idea - providing pg_rotate_wal(), which would guarantee that > the wal is rotatted at least once if called. Thinking further about this, > for a first prove of concept, I'd be enough to write a C function > pg_current_walsegment(). pg_rotate_wal() could then be a plpgsql function, > that e.g. creates a temporary table, and fills it with data, until the > return value of pg_current_walsegment() changes. Temporary tables don't get xlogged. It would probably be easier to hook into the xlog machinery and create NOP records, like you originally suggested. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout wrote: > On Fri, Jul 14, 2006 at 05:36:58PM +0200, Florian G. Pflug wrote: >> That was the idea - providing pg_rotate_wal(), which would guarantee that >> the wal is rotatted at least once if called. Thinking further about this, >> for a first prove of concept, I'd be enough to write a C function >> pg_current_walsegment(). pg_rotate_wal() could then be a plpgsql function, >> that e.g. creates a temporary table, and fills it with data, until the >> return value of pg_current_walsegment() changes. > > Temporary tables don't get xlogged. It would probably be easier to hook > into the xlog machinery and create NOP records, like you originally > suggested. From further sourcecode reading, I got the following implementation plan .) Add new entry to RmgrTable (Should I add it at the end, or use one of the "reserved" entries?) .) Create nop_redo and nop_desc - for nop_redo an empty function should be sufficient .) Create pg_rotate_wal, which calls XLogInsert(RM_NOP_ID, XLOG_NO_TRAN, rdata) with rdata.data = "pointer to WAL_SEGMENT_SIZEzero bytes" rdata.len = WAL_SEGMENT_SIZE rdata.buffer = InvalidBuffer rdata.next = NULL Since I insert WAL_SIZE bytes, I shouldn't even have to loop, because that records has no chance to fit into the current wal segment, right? Am I overlooking something? greetings, Florian Pflug
"Florian G. Pflug" <fgp@phlo.org> writes:
> I've now thought about how to fix that without doing that rather crude rsync-pg_xlog-hack.
> I've read through the code, and learned that wal-segments are expected to have a specific size -
> thus rotating them "early" is not that easy.
Simon was working on a patch for this at the code sprint; I think it's
submitted to -patches already. Explicitly filling the segment as you
propose would be really bad for performance.
regards, tom lane
> -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of > Florian G. Pflug > Sent: 14 July 2006 16:37 > To: Postgresql-General > Cc: A.M. > Subject: Re: [HACKERS] Forcing wal rotation > > > How about an SQL-level function that calls the wal scripts? > This would > > also allow "important" transactions to push data to the > standy server > > regardless of the wal size. > That was the idea - providing pg_rotate_wal(), which would > guarantee that > the wal is rotatted at least once if called. Thinking further > about this, > for a first prove of concept, I'd be enough to write a C function > pg_current_walsegment(). pg_rotate_wal() could then be a > plpgsql function, > that e.g. creates a temporary table, and fills it with data, until the > return value of pg_current_walsegment() changes. You might wanna speak to Simon Riggs before starting anything - he was planning to work on this (log rotation) at the Code Sprint. Don't know if he actually got to it though. Regards, Dave.
On Fri, 2006-07-14 at 12:09 -0400, Tom Lane wrote: > "Florian G. Pflug" <fgp@phlo.org> writes: > > I've now thought about how to fix that without doing that rather crude rsync-pg_xlog-hack. > > I've read through the code, and learned that wal-segments are expected to have a specific size - > > thus rotating them "early" is not that easy. > > Simon was working on a patch for this at the code sprint; I think it's > submitted to -patches already. Slightly different patch. I'm working on this one still. > Explicitly filling the segment as you > propose would be really bad for performance. Yes, current approach I am taking is better than that. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > On Fri, 2006-07-14 at 12:09 -0400, Tom Lane wrote: >> "Florian G. Pflug" <fgp@phlo.org> writes: >>> I've now thought about how to fix that without doing that rather crude rsync-pg_xlog-hack. >>> I've read through the code, and learned that wal-segments are expected to have a specific size - >>> thus rotating them "early" is not that easy. >> Simon was working on a patch for this at the code sprint; I think it's >> submitted to -patches already. > > Slightly different patch. I'm working on this one still. Cool - what are the chances of this being included in 8.2? >> Explicitly filling the segment as you >> propose would be really bad for performance. > > Yes, current approach I am taking is better than that. Well, my proposal wasn't really a long-term solution - I was thinking about I quick fix that I could implement for 8.1, basically to let my warm-standby-setup feel less like as "house of cards" as someone put it ;-) I didn't care too much about the performance hit - I don't expect the database I indent to use it for to have much load, otherwise the wal segments are rotated quite often anyway. But I agree that for a general solution, my approach is not really ideal ;-) Since we just ported the application in question to 8.1, I'm not sure that we will switch to 8.2 when it is released - so I'm still interested in finding a solution for 8.1 Do you think I could backport your patch to 8.1 - or does it depend on some other new features of 8.2? greetings, Florian Pflug
Ühel kenal päeval, R, 2006-07-14 kell 17:39, kirjutas Simon Riggs: > On Fri, 2006-07-14 at 12:09 -0400, Tom Lane wrote: > > "Florian G. Pflug" <fgp@phlo.org> writes: > > > I've now thought about how to fix that without doing that rather crude rsync-pg_xlog-hack. > > > I've read through the code, and learned that wal-segments are expected to have a specific size - > > > thus rotating them "early" is not that easy. > > > > Simon was working on a patch for this at the code sprint; I think it's > > submitted to -patches already. > > Slightly different patch. I'm working on this one still. What is your approach here ? And by any chance, do you plan to backport the standby WAL playback mode patches to 8.0 and 8.1 series ? > > Explicitly filling the segment as you > > propose would be really bad for performance. > > Yes, current approach I am taking is better than that. Another thing that was discussed was adding a function to postgres that could be called to get current WAL file and offset, so an external process could do async wal-copying at the time WAL is being written instead of copying it all when it is finished. This could reduce the lag of data availability to only (fractions of) seconds. Is anyone working on it ? -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
Hannu Krosing <hannu@skype.net> writes:
> And by any chance, do you plan to backport the standby WAL playback mode
> patches to 8.0 and 8.1 series ?
That's not happening ... we do not put new features in stable branches.
regards, tom lane
Ühel kenal päeval, L, 2006-07-15 kell 22:24, kirjutas Tom Lane: > Hannu Krosing <hannu@skype.net> writes: > > And by any chance, do you plan to backport the standby WAL playback mode > > patches to 8.0 and 8.1 series ? > > That's not happening ... we do not put new features in stable branches. Not even the part that is obviously a bugfix ? I mean the fact that currently checkpoints are not done while doing playback and any failure during playback causes one to do the whole playback again from the start, which means among oyher things that you must keep all your WAL files on the destination machine. > regards, tom lane -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com
On Sun, 2006-07-16 at 01:04 +0300, Hannu Krosing wrote: > Ühel kenal päeval, R, 2006-07-14 kell 17:39, kirjutas Simon Riggs: > > On Fri, 2006-07-14 at 12:09 -0400, Tom Lane wrote: > > > "Florian G. Pflug" <fgp@phlo.org> writes: > > > > I've now thought about how to fix that without doing that rather > crude rsync-pg_xlog-hack. > > > > I've read through the code, and learned that wal-segments are > expected to have a specific size - > > > > thus rotating them "early" is not that easy. > > > > > > Simon was working on a patch for this at the code sprint; I think > it's > > > submitted to -patches already. > > > > Slightly different patch. I'm working on this one still. > > What is your approach here ? I'm just posting a prototype patch to -patches now that implements pg_switch_xlog(). This is to allow some further discussion as people have requested; I've got 3 working days between now and code freeze and these are allocated to work in this area. That isn't a last minute thing, just that my schedule has been too full mid-month with customer-related work. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com