From: Shaun Thomas <sthomas@peak6.com>
To: sthomas@peak6.com
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Using incrond for archiving
Date: Mon, 14 Nov 2011 15:33:35 -0600
Message-ID: <4EC1892F.2050506@peak6.com> (raw)
In-Reply-To: <4EBD9FDE.4000104@peak6.com>
References: <4EBD9FDE.4000104@peak6.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@peak6.com
Subject: Re: Using incrond for archiving
In-Reply-To: <4EC1892F.2050506@peak6.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox