pgstef’s blog

2023-07-03

pgBackRest differential vs incremental backup

One of the most frequent questions I get is to actually explain the difference between differential and incremental backups in pgBackRest.

As everyone might easily imagine, an incremental backup will only copy the database files (to keep it simple) that have been modified since the last successful backup. If you have a full backup every Sunday, and then an incremental every other day, the backup from Wednesday will be based on the one from Tuesday. That also means that if the backup from Monday or Tuesday is broken/corrupted (for any reason), you might not be able to recover from your Wednesday backup.

Now, what is a differential backup then? It is an incremental backup but it will be based on the last successful full backup. So if you have a full backup every Sunday, and then a differential every other day, the backup from Wednesday will be based on the one from Sunday! It would then doesn’t matter if the other differential backups get broken/corrupted, but since you’ll always compare what has been modified since Sunday, the size of the differential backups will be bigger over time.

Let’s have a concrete example.

2023-05-26

pgBackRest SFTP support

SFTP support has been added in the 2.46 release on 22 May 2022.

In this demo setup, the SFTP host will be called sftp-srv and the PostgreSQL node pg-srv. Both nodes will be running on Rocky Linux 8.

If you’re familiar with Vagrant, here’s a simple Vagrantfile to initiate 3 virtual machines using those names:

# Vagrantfile
Vagrant.configure(2) do |config|
    config.vm.box = 'rockylinux/8'
    config.vm.provider 'libvirt' do |lv|
        lv.cpus = 1
        lv.memory = 1024
    end
    config.vm.synced_folder ".", "/vagrant", disabled: true

    nodes  = 'sftp-srv', 'pg-srv'
    nodes.each do |node|
        config.vm.define node do |conf|
            conf.vm.hostname = node
        end
    end
end

When using Vagrant boxes, it might be needed to enable the SSH password authentication to proceed with SSH key exchange:

$ sudo -i
root# sed -i 's/PasswordAuthentication no/PasswordAuthentication yes/g' /etc/ssh/sshd_config    
root# systemctl restart sshd.service
root# passwd

2022-09-20

pgBackRest 2.41 released

With pgBackRest 2.41 just released, a new feature called backup annotations is now available. Let’s see in this blog post what this is about.

2022-07-12

Patroni and pgBackRest combined

I see more and more questions about pgBackRest in a Patroni cluster on community channels. So, following yesterday’s post about Patroni on pure Raft, we’ll see in this post an example about how to setup pgBackRest in such cases.

To prepare this post, I followed most of the instructions given by Federico Campoli at PGDAY RUSSIA 2021 about Protecting your data with Patroni and pgbackrest. The video recording might even be found here.

2022-07-11

Patroni on pure Raft

Since September 2020 and its 2.0 release, Patroni is able to rely on the pysyncobj module in order to use python Raft implementation as DCS.

In this post, we will setup a demo cluster to illustrate that feature.

2022-04-15

pgBackRest multi-repositories tips and tricks

Since April 2021 and the 2.33 release, pgBackRest allows using multiple repositories at the same time. This brings a lot of benefits like, for example, redundancy and the ability to define various retention policies.

I had the chance to talk about this feature recently at pgDay Paris to highlight the impact of this new feature on the existing pgBackRest commands.

A detailed example can also be found in an EDB docs page I wrote last year when this feature was released.

In this post, we’ll see the most frequent questions I get (in conferences or on community channels) and some tips and tricks.

2022-02-23

pgBackRest preview - Bundle files in the repository during backup

A nice new feature has been added on 14 Feb 2022: Bundle files in the repository during backup. Details can be found in commits 34d6495 and efc09db.

This feature combines smaller files during backup to reduce the number of files written to the repository (enabled with --bundle). Files are batched up to --bundle-size and then compressed/encrypted individually and stored sequentially in the bundle. --bundle-limit limits which files can be added to bundles. Files larger than this size will be stored separately. On backup resume, the bundles are removed and any remaining file is eligible to be resumed.

IMPORTANT NOTE: this feature is still experimental and will not be released in pgBackRest 2.38.

2022-02-21

pgBackRest and TLS connections

The TLS server is an alternative to using SSH for protocol connections to remote hosts. It has been pushed in the 2.37 release on 3 Jan 2022.

In this demo setup, the repository host will be called backup-srv and the 3 PostgreSQL nodes in Streaming Replication: pg1-srv, pg2-srv, pg3-srv. All the nodes will be running on Rocky Linux 8.

If you’re familiar with Vagrant, here’s a simple Vagrantfile to initiate 4 virtual machines using those names:

# Vagrantfile
Vagrant.configure(2) do |config|
    config.vm.box = 'rockylinux/8'
    config.vm.provider 'libvirt' do |lv|
        lv.cpus = 1
        lv.memory = 1024
    end

    # share the default vagrant folder
    config.vm.synced_folder ".", "/vagrant", type: "nfs"

    nodes  = 'backup-srv', 'pg1-srv', 'pg2-srv', 'pg3-srv'
    nodes.each do |node|
        config.vm.define node do |conf|
            conf.vm.hostname = node
        end
    end
end

2021-06-03

pgBackRest and SUSE

I got the question the other day from a friend: “does pgBackRest work on SUSE?”. Having to admit I never really used SUSE, and not knowing what to answer, I decided to give it a try. Let’s see in this short post how far we can go.

2021-02-10

check_pgbackrest 2.0 has been released

check_pgbackrest is designed to monitor pgBackRest backups, relying on the status information given by the info command.

The biggest change in this new release is that the tool will now only support pgBackRest 2.32 and above in order to only use its internal commands. This remove Perl dependencies no-longer needed to reach repository hosts or S3 compatible object stores. This also brings Azure compatible object stores support. The repo-* arguments have then been deprecated.

pgBackRest 2.32 has been released this week and brings official support for the repository commands: repo-ls and repo-get.

Let’s find out in this post how nice this feature is.

pgstef's blog

SELECT * FROM pgstef