Re: Fastest Backup Method?

On Tue, Sep 5, 2017 at 12:50 PM, Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 9/4/17 13:23, Artem Tomyuk wrote:
> > I'am wondering, what is the fastest backup method do you know, for me it
> is:
> > (Assume that we running on 16 * CPU box)
> >
> > pg_dump -Z 4 -j 16 -F c -F d
> >
> > -Z 4 compression level
> >
> > -j 16 number of parallel jobs
> >
> > -F c, d custom+directory format
>
> I think you misunderstood that last part. There can only be one output
> format, and the last option wins. So you are really running in
> directory format only.
>
> As for performance, you generally have the right idea. Specifics depend
> on the hardware.
>
> My experience is that any compression at all can be bad for performance.
> But the I/O cost might balance that in some cases.
>
>

In my experience and experimentation, using a hot standby in conjunction
with PITR almost completely insulates the master from backup load. The
recipe is simply obviously configure for WAL archiving, have a hot standby
for backups, execute pg_start_backup(), wait on hot standby for the LSN on
the hot standby, execute pg_xlog_replay_pause() on hot standby, execute
pg_stop_backup() on master, do the necessary backup on hot standby, and
wrap it up with pg_xlog_replay_resume() on hot standby. It's been a while
so that was from memory.

I have performed this test in high transaction environment and tested the
restore with much success. Aside from the checkpoint on the master and
archive_command, minimal to no load incurred on the master. I don't
believe xlog replay pause/resume is strictly necessary but seemed like a
good idea.

HTH,
-Greg