Re: Using streaming replication as log archiving

On Tue, Sep 28, 2010 at 06:25, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Mon, Sep 27, 2010 at 9:07 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> As has been previously mentioned a couple of times, it should be
>> perfectly possible to use streaming replication to get around the
>> limitations of archive_command/archive_timeout to do log archiving for
>> PITR (being that you either keep archive_timeout high and risk data
>> loss or you set it very low and generate a huge log archive without
>> need).
>>
>> I've put together a tool to do this. The basic idea is to just stream
>> down replication and write it to regular WAL files, which can then be
>> used for recovery. You'll still need to use archive_command together
>> with it to ensure that the backups are complete. Streaming replication
>> doesn't guarantee that - in fact, regular replication will fallback to
>> using whatever archive_command created when wal_keep_segments isn't
>> enough.
>>
>> I've put up an early version of the tool at
>> http://github.com/mhagander/pg_streamrecv
>
> Great! This also might be useful for users who want something like
> Oracle redo log mirroring.

Thanks.

>> Comments and contributions are most welcome. And frankly, a good
>> review is very much required before I'd trust it ;) Hopefully, I
>> didn't overlook something critical :D
>
> When I ran that, the size of the WAL file in inprogress directory
> became more than 16MB. Obviously something isn't right.

Wow, that's weird. I'm unable to reproduce that here - can you try to
figure out why that happened?

> When I requested immediate shutdown to the master, segmentation
> fault occurred in pg_streamrecv. I guess that the return value 0
> of PQgetCopyData would not be handled correctly.

Almost right - it actually returns -2 - which isn't handled. I've
added a fix for that - and while att it, covering anything that's so
small it doesn't contain the streaming replication header.

> After I repeated Ctrl+C and start of pg_streamrecv some times,
> I encountered the following error and pg_streamrecv was never up.
> Is this intentional?
>
>    In progress directory contains more than one file!
>
>    $ ls foo/inprogress/
>    00000001000000000000000D  00000001000000000000000D.save

Yes, that is actually intentional.

When it finds the ..0D file there the first time, it gets renamed to
".save", and it retries the transmission from the beginning of that
segment. as soon as the retransmission has passed the point that 0D
was at, the .save file is removed. If you Ctrl-C the process again
*before* it has reached that point, it will leave both files around -
it's up to you to clean them up. This is to make sure we don't
overwrite a file that contains more log data than is currently
available on the master.

> When there is inprogress or archived WAL file, pg_streamrecv should
> not execute pg_current_xlog_location because that result is not used?

Yeah, that's just a bit of lazy programming that I should fix :-)

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/