Re: Setting up continuous archiving

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(at)pgmasters(dot)net