Re: Using streaming replication as log archiving

On Wed, Sep 29, 2010 at 23:45, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>
>> Comments and contributions are most welcome.
>
> This is probably too esoteric to be worked on yet, but for this to
> be useful for us we would need to pass the resulting files through
> pg_clearxlogtail and gzip in an automated fashion.  And we would
> need to do regular log file archiving in parallel with it.
>
> As background, our databases around the state archive to a directory
> which is then pushed via rsync to a "dumb" backup location in the
> same room as the database server (we're lucky to have rsync on the
> target of this copy; any other executable is out of the question),
> and the same directory is pulled via rsync to a central location.
> We would be interested in using streaming replication to a tool such
> as you describe for the copy to the central location, but since we
> would still be forcing a wal-file switch once per hour we would need
> the current capability to shrink an "empty" file from 16MB to 16kB
> using the above-mentioned tools.

You could just have one stream going local and one stream going to the
other location in parallell, though?

Or use the stream to the local directory and rsync that off? While I
haven't tested it, rsyncing the partial WAL files *should* be fine, I
think...

> Also, a the ability to limit bandwidth would be a nice feature for
> us, preferably in a way which could be changed on the fly.
>
> If you could keep the development "friendly" to such features, I may
> get around to adding them to support our needs....

Would it be enough to have kind of an "archive_command" switch that
says "whenever you've finished a complete wal segment, run this
command on it"? Then that command could clear the tail, compress, and
send off?

And in that case, should it run inline or in the background with the
streaming? I would assume just fork it off and leave it to it's own
business would be best?

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