This week, PGSQL Phriday #2 is focused on backups, thanks to Andreas Scherbaum’s blog post.
It is not a secret that I’m all in favor of using pgbackrest. I think that it is today the best backup tool for PostgreSQL. It does not mean others are bad, it means they are less good. If you feel the need to have a way to restore your data, I guess you’d rather use the best tool to achieve that goal.
What’s the problem with pg_basebackup? 🔗
pg_basebackup is a tool that should be renamed pg_clonecluster,
because that’s exactly what it does. That’s why I’m in favor of using
pg_basebackup to create (or reinstate) a standby.
A proper backup tool should care about how to restore your data. Having a backup is just a convenient way to perform a restore, but that’s just a step in the journey, not a destination.
As pg_basebackup does not care about restoring, it is missing important
features like:
- restoring (obviously)
- archiving WALs
- maintaining a backup catalog
- managing backups and WALs retention policy
- testing the backup files as not been corrupted (before Postgres 13 and this is not automatic)
I’m sure I could find others, but those should be enough not to consider using
pg_basebackup as a backup tool.
Barman can help 🔗
Barman was created to try to add the features missing in pg_basebackup, like
the missing catalog, the missing WAL archiving problem, and the missing restore
feature… Barman also has a mode rsync where it does not rely on
pg_basebackup anymore.
I still deeply think that when there’s a design flaw, like pg_basebackup
has, re-creating something new from a blank page is the way to go to fix the
problem.
The only way to find out your
backup is corrupted is to use the verify command which will call
pg_verifybackup(only available from Postgres 13) which will fail should you
use the rsync method without launching the generate-manifest command.
For a long time, this was not even possible!
Also, I think at one point, we need to discuss Barman documentation. Back in
2017, I tried to use Barman to back up a Postgres cluster. I wanted to do it by
myself without any help, just by reading the documentation. I found out that the
documentation was not good, with contradictions in different paragraphs and, in
the end, I was not able to back up (or restore) a standalone Postgres cluster.
As said, Barman is good, but not good enough.
I recommend pgBackRest 🔗
So, why is pgBackRest my recommended tool? As said, pgBackRest will try to
save you time. Back in 2017, when I was experimenting with backup tools, I was able
to follow the documentation and create a backup of my test standalone Postgres
cluster and restore it very easily, following the tutorial.
Since then, I created a practical training for my students at Université Lyon I: without
previous knowledge of Postgres or pgBackRest, they are able in 4 hours to
backup, restore, PITR, and even dig in the WALs to find the timestamp or xID of
a mistake to PITR just before the mistake. It’s that good that when I suggested
they use pgBackRest to create a replication, I couldn’t find out if they had
stupidly used the tool without understanding anything or if they had completely
understood how setting a replication works (I added questions for the
replication training, don’t worry)!
The feature I love with pgBackRest is the “delta” feature which will
copy over only the files which changed since the backup. This feature is very
useful for huge clusters. You can first issue a messy rsync command (or restore
a storage snapshot) and then restore with the delta option.
Another great feature of pgBackRest is the ability to restore just one
database on a dummy cluster so that you can then choose which data you want to
copy over to another cluster.
Of course, those features are only the cherry on the top. pgBackRest was
created with secure restore in mind, so that the first feature of the first
version of pgBackRest was the creation of a manifest file and check-summing of all
files.
The only case when I don’t recommend pgBackRest is when you’re running Postgres
under Windows (which I don’t recommend either), because there is no Windows
version of pgBackRest for Windows. I still consider having a great
backup/restore tool for Windows is a must for the community and we’re trying to
work on it.
Other tools 🔗
Other tools are barely used in the community. You might want to favor a tool that is used by the majority of people running Postgres so that you reduce the risk to find a bug, but this is your choice.
Bonus question: is pg_dump a backup tool 🔗
It depends. If you can allow losing all transactions between the beginning of your
dump and the time you need to import your dump, then pg_dump can be considered
as a backup tool. I’d rather use the term “export” than “backup” so that people
don’t have a false sense of security.
On top of that, you need to consider the time to re-import all those data which can be very long as importing is slower than simply copying files to the file system. This operation needs to be performed frequently to make sure the downtime is affordable.
Most of the time, you won’t be fine with losing that much data or having such
a downtime to re-import your dump, but if you fully understand the consequences
and risks and are ok with them, please use pg_dump as a backup tool.
Use pgBackRest and relax 🔗
This is my TL; TD for this post: just use pgBackRest and you’ll be fine.
Remember also that you should never trust a tool and always try to complete
a restore to make sure your backup is usable. I guess you don’t want to discover
what “Backup of Shrödinger means”!