Re: Setting up continuous archiving - Mailing list pgsql-general

On 10/15/18 5:09 PM, Benoit Lobréau wrote:
> 
>     By the way, do/can they both use streaming to receive WAL records? Or
>     streaming is only for standby servers. For backups you have only
>     file-based log shipping?
> 
> barman supports streaming but it's not as magical as one might think.
> See pgbarman's documentation for how to manager .partial files.
> 
> pgbackrest archives only wal files when postgres uses the 
> archive_command. You also have the option to do paralllel async wal 
> push/get.
> It can be useful if you write wals quicker than you can archive them or 
> if you want to restore more quickly.

We have focused on archive_command because the performance is much 
better because it can be parallelized.

>     Then, I suppose they both don't support partial PITR
>     (http://docs.pgbarman.org/release/2.4/#scope), where there are
>     standalone backups that extends to points in time for which there are
>     no WAL files. I'm not sure if this matters, but I assume that it might
>     be effective in terms of disk space.
> 
> It's a hot backup so you have to have wals files so that your backup is 
> consistent at the end of the backup.
> You can build something like what you describe with pgbackrest, 
> archive-copy and a copy of the backup directory to another place.

The --repo1-retention-archive-type and --repo1-retention-archive options 
allow you do keep WAL for a smaller number of backups in order to save 
space.

https://pgbackrest.org/configuration.html#section-repository/option-repo-retention-archive

So, for example:

[global]
repo1-retention-full=4
# The following option is the default but included for clarity
repo1-retention-archive-type=full
repo1-retention-archive=2

This configuration will retain 4 full backups but only keep PITR WAL for 
2 of them, i.e. the WAL generated between backups.  The WAL required to 
make a backup consistent is always retained so the 2 older backups can 
be played to consistency but no further.

>     I'd like to be able to handle db failure as fast as possible. Ideally,
>     automatically. Which probably means either repmgr, or corosync +
>     pacemaker + PAF. Is that what you mean by HA here? 
> 
> 
> yes. You can also look into patrony for an alternative. It's a matter of 
> preference and requirements.

pgBackRest certainly works with Pacemaker/Corosync and Patroni.

> If your standby is lagging too far behind (and you dont use slots) you 
> can use pgbackrest's archive to fill the gap in wal files and catchup 
> with the master.

Recovering WAL out of the archive is safer than using slots.  Since the 
WAL is transferred over the network compressed it can also save a lot of 
bandwidth.

Regards,
-- 
-David
david@pgmasters.net