Re: Using streaming replication as log archiving

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.

> 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.

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.

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

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

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center