Overview

I find pgbackrest, way more “stupid” (or maybe I am “stupid) than Barman. I am sorry, but I guess I just don't get it. You have a pgbackrest executable, which checks the config file, it generates the postgresql.auto.conf file and the server STILL restores till the last WAL files when you try to do PITR. But I guess I will return to this section, when I am smarter and know how to use it. Either case, let's get into it. Unlike Barman, pgbackrest REQUIRES passwordless connection for the backup / restore to the server and unlike Barman in REQUIRES you to archive the WAL Files to a common repo or global repo. P.S. Barman doesn't require passwordless connection for backup, as we can configure “streaming” server….

Either case, so in general. My setup is. 1 central backup server, 1 NFS on that backup server, which is mounted on the data nodes and the data nodes push the WAL Files to that repo using pgbackrest. Simple as that.

Setup

After you install pgbackrest on all servers: data nodes + backup server. You can set the archive command as follows:

Setup archive & restore commands

So in nutshell, my patroni.yml looks like this:

Patroni Yaml

After that, we need to create the Stanza (Configuration of server and a backup). That is done from the backup server.

Create Stanza

Create Stanza

That command will create you the stanza and then the archive command from the data nodes will work. You can also manually archive a file as follows, ran from the data node.

Manually archive a file

Backup

Now, pgbackrest has the following backups:

Full

Differential (What has changed since FULL)

Incremental (What has changed since FULL or Differential)

I know, it is the opposite of Oracle, but hey, we cannot live in perfect world. If you try to make DIfferential or Incremental without Full, pgbackrest is “smart” and it will do FULL instead.

Create Full backup

After that we can create an incremental or differential backup:

Create Incremental & Differential backup

With that at hand, we can drop and delete all database:

Restore

Now, the restore can be:

Complete

PITR: Time, LSN, Checkpoint, Transactions, etc

Don't forget that you need also the WAL Files in either case. The cluster has to be consistent after all. So make the difference between:

Restore (Moving the data files from backup to the data dir)

Recover (Applying the WAL Files, until we are consistent state and/or until we are told (recovery_target_time)

These are important concepts. To restore a backup we can use the following command.

Restore latest backup

Or we can restore only to a PITR (using time)

Restore to PITR

Despite, whatever you chose, MY BIGGEST PROBLEM was that Patroni continued to apply the WAL Files, despite what I was telling him. So my HUMBLE opinion. After restore with pgbackrest, DO NOT START IT WITH PATRONI, BUT start it outside of Patroni:

Starting outside of Patroni

Check if that is the correct state and if you are happy. Then promote it and THEN stop it:

stopping outside of Patroni

After that, you can start it from patroni and maybe failover:

Start within patroni

That is how I was able to make it work. I am 99.(9)8% (Math nerds here), sure I do something wrong, but still. If you cannot get it work with patroni initially, that way works.