FAQ
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

Search Discussions

  • Florian G. Pflug at Jul 14, 2006 at 3:37 pm

    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.
  • Martijn van Oosterhout at Jul 14, 2006 at 3:46 pm

    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.
  • Florian G. Pflug at Jul 14, 2006 at 3:58 pm

    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_SIZE zero 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
  • Dave Page at Jul 14, 2006 at 4:17 pm

    -----Original Message-----
    From: 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.
  • Tom Lane at Jul 14, 2006 at 4:09 pm

    "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
  • Simon Riggs at Jul 14, 2006 at 4:40 pm

    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
  • Florian G. Pflug at Jul 15, 2006 at 4:06 am

    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
  • Hannu Krosing at Jul 15, 2006 at 10:05 pm

    Ü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
  • Tom Lane at Jul 16, 2006 at 2:24 am

    Hannu Krosing 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
  • Hannu Krosing at Jul 16, 2006 at 11:31 am

    Ü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
  • Simon Riggs at Jul 27, 2006 at 1:00 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJul 14, '06 at 3:20p
activeJul 27, '06 at 1:00p
posts12
users6
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase