Feature request: pgbackrest support

[MannerMan]

Hello!

First of all, thanks for developing cloudnative-pg operator, it's a fantastic well-designed operator for Postgres on Kubernetes.

We have a large deployment of postgres servers (900+) which we're evaluating moving to Kubernetes. We have looked into the various operators available and have found cloudnative-pg to be the best one available. We do have one thing blocking us from using it however, and that is the barman-cloud backup. Unfortunately barman-cloud is not on-par with pgbackrest features and performance. We have tried using barman for our setup previously and it simply cannot handle our amount of data-files. In pgbackrest they were able to solve this however, and improve performance with their bundle feature. Barman is also missing features like multi-repo support, backup encryption which we use currently.

It would be fantastic if cloudnative-pg could support pgbackrest, which is arguably the most robust and feature-complete backup tool for Postgres currently available!

[gbartolini]

Without entering the "pgbackrest vs Barman" debate (BTW, I am one of the creators of Barman), as of today you can actually rely on the Kubernetes way to take backups, using the standard Kubernetes API for Volume Snapshots: https://cloudnative-pg.io/documentation/current/backup_volumesnapshot/

CloudNativePG is the first Postgres operator that natively supports this amazing feature that Kubernetes has standardized, abstracting and automatically bringing features like incremental and differential backup and restore.

FYI, I will be writing a blog article about this feature in the next few days, showing how I have been able to restore a 4.5 TB database in 2 minutes.

[MannerMan]

Thank you for creating barman, we used the traditional barman version 1 and 2 without issue a few years back for some pg clusters before moving all our backups to object storage!

--

Cool, we actually did the combined approach of physical backups in combination with WAL for a while before pgbackrest had its bundle feature. Combining an external system (block-level snapshots) with an integrated postgres-aware backup solution was not very convenient however. We dropped this approach due to issues related to retention, backup validation, encryption and multi-repo support that's all managed & controlled by a single tool (pgbackrest) now.

I think pgbackrest could add some really nice capabilities to CloudNativePG, including differential and incremental backup support without requiring the underlying storage to support snapshots.

[shusaan]

Hi, is there any plan to include pgbackrest?

[gbartolini]

Not at the moment. We are still committed to continue our progress with Kubernetes native solutions. I am however curious to understand which storage solutions you are using. This is a list of all storage drivers that natively support snapshots: https://kubernetes-csi.github.io/docs/drivers.html (look for the 'Snapshot' column).

[gbartolini]

@shusaan any updates?

[till]

I also wanted to comment in favour of pgbackrest.

The incremental backups using the WAL, multi repo support, selective restore etc. all exceed volume snapshots for me, which are as others mentioned really icky to handle. In terms of retention, capabilities, etc..

[ajrpayne]

I have an existing cluster that uses pgbackrest for backups and wal archiving. Without support for pgbackrest, I don't see an easy solution for us moving over to cloudnative-pg.

[gbartolini]

I have an existing cluster that uses pgbackrest for backups and wal archiving. Without support for pgbackrest, I don't see an easy solution for us moving over to cloudnative-pg.

Can you please elaborate why you say it is not an easy solution? Is this existing cluster already in Kubernetes or outside Kubernetes?

[ajrpayne]

It is outside k8s and very large. Was hoping to migrate to cloudnative-pg, but our wal-archive is managed by pgbackrest(appears to be in a format barman can't work with?). If we use pg_basebackup the new cluster can't keep up with the amount of WAL, and during restore reachs a point where WAL has been removed from the source before it has caught up.

[gbartolini]

If you move inside Kubernetes, you have to consider that the database will be different (different system ID). You cannot easily import via physical replication an existing cluster in Kubernetes - with any operator. Please read this article and the referenced one to get more details: https://www.enterprisedb.com/blog/current-state-major-postgresql-upgrades-cloudnativepg-kubernetes

[ajrpayne]

thanks for the link! I am still a bit confused, cloudnative_pg seems to support bootstrapping and running standby clusters using pg_basebackup. Is cloudnative_pg tracking or doing something with the system id so that only other cloudnative_pg clusters can be bootstrapped from?

[gbartolini]

I also wanted to comment in favour of pgbackrest.

The incremental backups using the WAL, multi repo support, selective restore etc. all exceed volume snapshots for me, which are as others mentioned really icky to handle. In terms of retention, capabilities, etc..

Can you please provide more information about "incremental backups using WAL"? Also, interested in the reasons behind 'selective restore' capability.

[gbartolini]

Hi,

Can you please provide more information about "incremental backups using WAL"?

Can you clarify the question?

The different options (full, differential and incremental) are explained here: https://pgbackrest.org/user-guide.html#concept/backup

Ok, that's clearer as 'incremental backups using WAL' didn't make any sense, as we are talking about base backups here. For this purpose, we have decided to delegate, for the time being, incremental and differential to Kubernetes itself. I cannot talk for the entire group of maintainers (we will have a meeting and decide), but to be honest I'd rather prioritize different features at the moment, than add another way (non Kubernetes way) to perform incremental/differential backup of instances. However, I won't stop anyone willing to contribute to this feature. My view on this has been clear for many years (and you can easily find public evidence on the topic): incremental/differential backup (and restore) should be part of Postgres' pg_basebackup (there's work going on by Robert Haas in this direction to add this to the core in fact).

I can backup an entire postgresql server with pgbackrest but it gives me options to selectively restore certain databases. Much closer to my use case than restoring an entire server. From my experience, a full restore is mostly for DR purposes. More often, people will want to extract a part of a backup.

Which is also why snapshots are not that helpful in that case, as they likely recover another server and then people need to go in and find stuff and transfer them over. It's not like it's not possible and if that's the only option available, sure, but a more selective option is much nicer.

Another advantage of a selective restore is the ability to move a database to another cluster, etc.. We also have an "in-house" thing that allows me to fork databases using backups from pgbackrest which is really helpful to work with a smaller amount of data.

An example is here: https://pgbackrest.org/user-guide.html#restore/option-db-include

Cool, thanks for confirming this (I am well aware of this feature as we wanted to implement it in Barman too many years ago).

The direction we've taken is to actually eliminate the issue at the source, by advocating for the microservice database. Ideally, you should have a single database in CloudNativePG (backup and restore is one of the reasons for separating databases in different instances, not the only one). This is explained in the FAQ section.

[till]

I didn't know we were only discussing base/full backups.

What k8s-native solutions are you referring to for differential and incremental backups?

[gbartolini]

I am referring to Kubernetes native volume snapshotting. Storage is a standard component in Kubernetes, through the Container Storage Interface. Our approach is simple: we are in Kubernetes, we delegate the storage to Kubernetes. As of CloudNativePG 1.21, we are the first Postgres operator that brings the native Postgres way of managing base backups into Kubernetes using a declarative approach for volume snapshotting, which normally implements out of the box transparent incremental/differential backup at block level.

[till]

I couldn't find much about incremental snapshots for OpenStack. Not sure how these are supported somewhere else.

[leonardoce]

Usually, snapshot backups are incremental/differential at the block level.
This applies to EBS, LongHorn, and many other implementations.
I would expect the CSI driver of OpenStack to implement them as incremental, but that needs a check.

As Gabriele said, supporting incremental/differential is a property of the Storage implementation.

[gbartolini]

Hi,

Can you please provide more information about "incremental backups using WAL"?

Can you clarify the question?

The different options (full, differential and incremental) are explained here: https://pgbackrest.org/user-guide.html#concept/backup

Ok, that's clearer as 'incremental backups using WAL' didn't make any sense, as we are talking about base backups here. For this purpose, we have decided to delegate, for the time being, incremental and differential to Kubernetes itself. I cannot talk for the entire group of maintainers (we will have a meeting and decide), but to be honest I'd rather prioritize different features at the moment, than add another way (non Kubernetes way) to perform incremental/differential backup of instances. However, I won't stop anyone willing to contribute to this feature. My view on this has been clear for many years (and you can easily find public evidence on the topic): incremental/differential backup (and restore) should be part of Postgres' pg_basebackup (there's work going on by Robert Haas in this direction to add this to the core in fact).

I can backup an entire postgresql server with pgbackrest but it gives me options to selectively restore certain databases. Much closer to my use case than restoring an entire server. From my experience, a full restore is mostly for DR purposes. More often, people will want to extract a part of a backup.

Which is also why snapshots are not that helpful in that case, as they likely recover another server and then people need to go in and find stuff and transfer them over. It's not like it's not possible and if that's the only option available, sure, but a more selective option is much nicer.

Another advantage of a selective restore is the ability to move a database to another cluster, etc.. We also have an "in-house" thing that allows me to fork databases using backups from pgbackrest which is really helpful to work with a smaller amount of data.

An example is here: https://pgbackrest.org/user-guide.html#restore/option-db-include

Cool, thanks for confirming this (I am well aware of this feature as we wanted to implement it in Barman too many years ago).

The direction we've taken is to actually eliminate the issue at the source, by advocating for the microservice database. Ideally, you should have a single database in CloudNativePG (backup and restore is one of the reasons for separating databases in different instances, not the only one). This is explained in the FAQ section.

[robert-sjoblom]

Aside from taking away core postgres functionality (several databases in a PG cluster -- which can be debated), this still leaves the question of multi-repo backup and backup encryption, though?

[gbartolini]

Backup encryption can be delegated to the storage class. Multi-repo backup can be assimilated to data mobility features of the underlying classes.

[robert-sjoblom]

And what about WAL encryption? How many tools do I need to solve all use-cases that are currently covered in one tool?

[martinmarques]

I'm surprised by this request. This is more or less like asking for Patroni support in CNPG to manage failover. Of course, nobody will ask for Patroni because the operator already manages failover very well.
The correct solution here is to implement backups in the CNPG operator using k8s capabilities.

My 2 cents.

[MannerMan]

The original issue was about missing backup features in cloudnative-pg, which makes cloudnative-pg impossible for us to implement.

Pgbackrest is a suggested (and perhaps fairly quick) approach to get those features into cloudnative-pg.

Put another way, currently cloudnativepg's backup implementation is (to my knowledge) missing the following features;

• Parallel WAL push/get
• Zstd compression
• Delta restore
• Encryption at rest
• Native postgres page checksum validation
• Multi repo

We use all these features in our postgres deployment today, via pgbackrest. Without these features, or atleast a subset (multirepo, encryption and parallel wal push/get being the most important) we cannot adopt cloudnativepg.

[martinmarques]

Those 6 topics should be discussed separately and not as a whole. Some of those would be irrelevant with certain solutions that don't involve adding a tool like backrest or barman (barman-cloud is not barman, but rather a script that uses the barman libraries)

[gbartolini]

Parallel WAL push and get is actually supported.

[gbartolini]

When using volume snapshots, you can leverage the CSI driver to achieve multi repo and encryption.

[martinmarques]

Gabriele, isn't this just the Grandfather, father, son backup feature? (talking about multi-repo with different retention policies)
We were talking (with the backup team) about this yesterday

[till]

Since support or implementation of pgbackrest is not on the roadmap currently, do you think you could open the API a bit for people to experiment?

E.g. allow extensions to the spec.backup manifest to allow others to implement something else there? Or would that be possible already? Maybe a plugin infrastructure would be a good way to allow people to extend the operator.

[gbartolini]

We have honestly been thinking about this for a long time. If you look at what 1.21.1 for example will ship in terms of hot backup control for volume snapshots, you see that we've improved the code in that direction.

My main concern is more about supportability. I have been in the Postgres game for many many years, including managing 24/7 support services, and the last 4.5 I have focused primarily on Kubernetes. What happens if there's a problem with your cluster and you are using an external tool to backup? Would that tool support Kubernetes? Would that tool support CloudNativePG? What if there's a bug in that backup software that might be accentuated by a highly concurrent environment like Kubernetes?

These are all open questions. However, I have an idea. Given that you and a few others like @MannerMan and  @robert-sjoblom are quite vocal on this, how do you feel about having a specific session on this topic in one of our community dev meetings? (preferably after Kubecon NA).

[till]

I don't doubt that you and your team thought about this and I understand the need to minimize the surface from a project perspective but having said all this, for me there are features missing in barman and in a snapshot-only approach.

In most cases snapshots offer the same durability and reliability as volumes (since they are in the same system) and most providers will even go as far as telling their customers that snapshots are not backups.

I briefly talked to Ben yesterday and mentioned that I want the ability to also extract backups e.g. to have them in a different location (lots of people have that requirement) or so consumers use them to bootstrap a local environment to reproduce an issue, etc..

For the record, I also have no desire to do more than I have to.

All of that becomes very cumbersome if "snapshots" are the only path forward.

Happy to talk about this too at some point.

[gbartolini]

Just a quick update. We have started working on a prototype of generic interface for CloudNativePG, called CNPG-I. See: https://github.com/cloudnative-pg/cnpg-i

Instead of adding new backup tools to the operator, our general idea is to actually even strip Barman Cloud from it, and use plugins that adhere to a common interface. For compatibility, obviously we will maintain the Barman Cloud backup support - as either embedded or as a plugin.

[till]

I see there's lots of development still ongoing. Is there anything to look at yet to play with the API?

[Ben-Bre]

@MannerMan How did the evaluation go?

[MannerMan]

Well, lack of pgbackrest support is one thing that keeps us from proceeding at this point - but we have high hopes for the generic interface to cloudnativepg where we hope to be able to contribute a plugin!

[Ben-Bre]

Hi @MannerMan Please reach out to me at Benedikt.Breiling@enterprisedb.com. I will connect you with the EDB responsible person who will help you with this.