Message-ID: <4EC1892F.2050506@peak6.com>
Date: Mon, 14 Nov 2011 15:33:35 -0600
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: <sthomas@peak6.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: <sthomas@peak6.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Using incrond for archiving
References: <4EBD9FDE.4000104@peak6.com>
In-Reply-To: <4EBD9FDE.4000104@peak6.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

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@peak6.com

______________________________________________

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