Re: pgBackRest backup from standby

On Mon, Feb 19, 2018 at 8:53 AM, David Steele <david(at)pgmasters(dot)net> wrote:

> Anything *not* in global (except pg_control), base, pg_tblspc,
> pg_xact/pg_clog, and pg_multixact are copied from the primary.
>
> For example, pg_stat is copied from the primary so these stats are
> preserved on a standby backup.
>

So if I have tablespaces outside of $PGDATA (but symlinked from within
pg_tblspc, of course), those will still be backed up from the standby,
right?

Is it right to say that the files that would be copied from primary are
very small, typically? So it isn't a huge transfer over the WAN (in my
case)?

> pgBackRest uses all the same exclusions as pg_basebackup, so many
> dirs/files are not copied at all: pg_dynshmem, pg_notify, pg_replslot,
> pg_serial, pg_snapshots, pg_stat_tmp, pg_subtrans, etc.
>
> Full list here
> https://www.postgresql.org/docs/10/static/protocol-replication.html.
>
> > it's best to archive from the primary so a replication
> > failure does not affect your archiving.
> >
> > Understood, just not something I can change in production primary at the
> > moment. Hence looking to see about a quick one-off backup from standby.
>
> For a quick one-off, pg_basebackup is your friend.

One of the requirements of this backup is encryption, which I don't see any
notes for with pg_basebackup. Also due to the size, parallel workers.
pgBackRest gives me both of these. I need compression as well but that
pg_basebackup does do.

I did come up with a sort of Rube Goldberg-esque workaround for now
involving using a clone of the prod standby VM from Veeam backup to use as
the backup source (after stopping recovery and opening it as a standalone
DB).

Don.

--
Don Seiler
www.seiler.us