Re: Using incrond for archiving

On 11/11/2011 04:21 PM, Shaun Thomas wrote:

> So my real question: is this safe?

So to answer my own question: no, it's not safe. The PG backends
apparently write to the xlog files periodically and *close* them after
doing so. There's no open filehandle that gets written until the file is
full and switched to the next one.

Knowing that, I used pg_xlogfile_name(pg_current_xlog_location()) to get
the most recent xlog file, and wrote a small script incrond would
launch. In the script, it gets the current xlog, and will refuse to copy
that one.

What I don't quite understand is that after calling pg_xlog_switch(), it
will sometimes still write to an old xlog several minutes later. Here's
an example (0069 is the current xlog):

2011-11-14 15:05:01 : 0000000200000ED500000069 : xlog : too current
2011-11-14 15:05:06 : 0000000200000ED500000069 : xlog : too current
2011-11-14 15:05:20 : 0000000200000ED500000069 : xlog : too current
2011-11-14 15:06:01 : 0000000200000ED500000069 : xlog : too current
2011-11-14 15:06:06 : 0000000200000ED500000069 : xlog : too current
2011-11-14 15:06:06 : 0000000200000ED500000069 : xlog : too current
2011-11-14 15:06:37 : 0000000200000ED500000069 : xlog : too current
2011-11-14 15:06:58 : 0000000200000ED500000045 : xlog : copying
2011-11-14 15:07:01 : 0000000200000ED500000069 : xlog : too current
2011-11-14 15:07:06 : 0000000200000ED500000069 : xlog : too current
2011-11-14 15:07:08 : 0000000200000ED500000069 : xlog : too current
2011-11-14 15:07:20 : 0000000200000ED500000064 : xlog : copying
2011-11-14 15:07:24 : 0000000200000ED500000014 : xlog : copying
2011-11-14 15:07:39 : 0000000200000ED500000069 : xlog : too current
2011-11-14 15:07:45 : 0000000200000ED500000061 : xlog : copying
2011-11-14 15:08:01 : 0000000200000ED500000069 : xlog : too current

Why on earth is it sending IN_CLOSE_WRITE messages for 0014, 1145, and
0061? Is that just old threads closing their old filehandles after they
realize they can't write to that particular xlog? Either way, adding
lsof or (ironically much faster pg_current_xlog_location) checking for
the most recent xlog to ignore, I can "emulate" PG archive mode using an
asynchronous background process.

On another note, watching kernel file IO messages is quite fascinating.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas(at)peak6(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email