Re: pgBackRest backup from standby

[Don Seiler]

On Mon, Feb 19, 2018 at 2:53 PM, David Steele  wrote:

>
> Also, relocating the log directory is easy using the log_directory
> setting, so that's what I recommend if it's an issue.  Some users do
> want to backup their logs.
>
>
That's probably a lot better idea than symlinking anyway. I'll look to do
that in my next round of config changes.



-- 
Don Seiler
www.seiler.us

Re: pgBackRest backup from standby

[David Steele]

On 2/19/18 2:05 PM, Don Seiler wrote:
> On Mon, Feb 19, 2018 at 12:39 PM, David Steele  > wrote:
> 
> 
> I read "open it for testing (or backups in this case)" as letting
> recovery complete and promoting the cluster to a master before taking
> the backup.
> 
> Don, is that the case?  If it is, I think there's a problem with or
> without a timeline switch.  If you confirm the backup is being taken as
> above then I'll detail my concerns.
> 
> 
> Note that this is just for creating a couple of one-off backups to
> restore for our dev and pre-prod environments. Given that, I was going
> to open a new clone as its own cluster and take backups from that. The
> data would be the same though and suit purposes of the dev and pre-prod
> refreshes.
> 
> If I were taking backups for the purpose of production backups, I would
> not do things this way. That is the eventual plan but right now we
> aren't ready to make the changes necessary in the production environment.

OK, that's fine then.  You can play these to consistency and they'll be
fine.  I just wouldn't try to do any PITR using the production WAL archive.

-- 
-David
da...@pgmasters.net

Re: pgBackRest backup from standby

[David Steele]

On 2/19/18 10:32 AM, Don Seiler wrote:
> On Mon, Feb 19, 2018 at 9:21 AM, David Steele  > wrote:
> 
> 
> Yes, they are typically very small.  The general exception to this rule
> is if logs are stored in pg_log.  I recommend storing logs out of the
> PGDATA dir as they can be quite large and don't really make sense to
> restore to another server.
> 
> Files copied from the master will be marked as such in backup.manifest
> (master:true) so you can check for yourself.
> 
> 
> Good to know. And fortunately for this DB we do have pg_log (and
> pg_xlog) symlinked to different volumes outside of $PGDATA.

If pg_log is symlinked to PGDATA it will be copied.  pg_xlog is not
copied in any backup.

> 
> > 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).
> 
> You don't get PITR that way, of course, but at least it's a backup.  As
> long as your clone is consistent.
> 
> 
> Yes it's a crash-consistent snapshot-based backup. I've done quite a few
> restores from it and it works great. It can do PITR as well since I
> would have all the WAL files from prod needed to keep recovering. But
> for these cases I just recover it to the first consistent point and open
> it for testing (or backups in this case). 

I don't think it would be safe to do PITR on a backup taken in this way.
 The WAL diverges even if you suppress a timeline switch.

-- 
-David
da...@pgmasters.net

Re: pgBackRest backup from standby

[David Steele]

Hi Don,

On 2/19/18 10:01 AM, Don Seiler wrote:
> On Mon, Feb 19, 2018 at 8:53 AM, David Steele  > 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?

Correct.

> 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)?

Yes, they are typically very small.  The general exception to this rule
is if logs are stored in pg_log.  I recommend storing logs out of the
PGDATA dir as they can be quite large and don't really make sense to
restore to another server.

Files copied from the master will be marked as such in backup.manifest
(master:true) so you can check for yourself.

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

You don't get PITR that way, of course, but at least it's a backup.  As
long as your clone is consistent.

-- 
-David
da...@pgmasters.net

Re: pgBackRest backup from standby

[David Steele]

Hi Don,

On 2/19/18 9:25 AM, Don Seiler wrote:
> On Mon, Feb 19, 2018 at 8:18 AM, David Steele  > wrote:
> 
> It copies files that are not replicated from the primary so that a
> primary-style backup is created. Anything that is replicated (which is
> by far the bulk of the data) is copied from the standby.
> 
> OK so all data files would be copied from standby. Can you give me an
> example of the types of files that need to be copied from primary?
>  

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.

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.

Regards,
-- 
-David
da...@pgmasters.net

Re: pgBackRest backup from standby

[Don Seiler]

On Mon, Feb 19, 2018 at 8:18 AM, David Steele  wrote:

> It copies files that are not replicated from the primary so that a
> primary-style backup is created. Anything that is replicated (which is
> by far the bulk of the data) is copied from the standby.
>

OK so all data files would be copied from standby. Can you give me an
example of the types of files that need to be copied from primary?


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


> Configuring pgBackRest, SSH, standby, and backup from standby are all
> covered in the user guide.


Thanks, I've been through it a few times and played with some test backups
from primary clones. I just ditched my master/replica clone setup but I'll
test there as well. I just had a couple questions about the mechanics.



-- 
Don Seiler
www.seiler.us

Re: pgBackRest backup from standby

[David Steele]

Hi Don,

On 2/18/18 7:34 PM, Don Seiler wrote:
> 
> Looking to use pgBackRest to take a backup from a hot standby. I'm
> reading that pgBackRest still needs to connect to the primary and copy
> some files. My questions are:
> 
>  1. What files does it need to copy? Config files? WAL files?

It copies files that are not replicated from the primary so that a
primary-style backup is created. Anything that is replicated (which is
by far the bulk of the data) is copied from the standby.

>  2. How does it connect? SSH?

Yes.

>  3. Does pgBackRest need to be installed and configured on the primary
> as well?

Yes.  Anyway, it's best to archive from the primary so a replication
failure does not affect your archiving.

Configuring pgBackRest, SSH, standby, and backup from standby are all
covered in the user guide.

http://pgbackrest.org/user-guide.html

In particular:

http://pgbackrest.org/user-guide.html#backup-host
http://pgbackrest.org/user-guide.html#replication
http://pgbackrest.org/user-guide.html#standby-backup

Regards,
-- 
-David
da...@pgmasters.net

Re: pgBackRest backup from standby

[Michael Paquier]

On Sun, Feb 18, 2018 at 06:34:46PM -0600, Don Seiler wrote:
> Looking to use pgBackRest to take a backup from a hot standby. I'm reading
> that pgBackRest still needs to connect to the primary and copy some files.
> My questions are:
> 
> 
>1. What files does it need to copy? Config files? WAL files?
>2. How does it connect? SSH?
>3. Does pgBackRest need to be installed and configured on the primary as
>well?

I am adding in CC: Stephen Frost and David Steele who work on the took.
You may want to contact the maintainers directly through github where
the project is maintained:
https://github.com/pgbackrest/pgbackrest
--
Michael


signature.asc
Description: PGP signature

pgBackRest backup from standby

[Don Seiler]

Evening all.

Looking to use pgBackRest to take a backup from a hot standby. I'm reading
that pgBackRest still needs to connect to the primary and copy some files.
My questions are:


   1. What files does it need to copy? Config files? WAL files?
   2. How does it connect? SSH?
   3. Does pgBackRest need to be installed and configured on the primary as
   well?

Thanks,
Don.

-- 
Don Seiler
www.seiler.us