Servoy Forum

At the moment I am running daily full backups for our pg databases using commandline, but they are getting larger so I'm looking to set up a more in-depth backup strategy using differential backups and longer retention times. For instance weekly full backups, with daily differential backups. And being able to keep 3 weekly backups and 3 monthly backups.

Online I've come across the following tools that might fit the requirements:
barman
pgbackrest

I was wondering if there is anybody with experience with either of these tools (or maybe other similar tools)

I have a customer where they just use Windows versioning on the files and if there is a problem you revert to a specific snapshot which are automatically taken a few times a day. I would still make normal backups in case this does not work correctly. But we have successfully undone changes in this way without undoing a full days work...

Hi Omar,
Thanks for your reply.
Do you mean a snapshot of the Postgres data directory? Does that not pose a risk of files that are being written at time of taking the snapshot, making them corrupt?

Hi Jos,

That's what I thought at first as well. And it may not be a good idea on a database where data is continuously written to the database. So I would not rely on it as your primary backup. But if the situation does occur, you have multiple options. If it does not work go the previous snapshot. Worst case you will still need to restore the formal backup but it seems to be working nicely so far.

Hi Jos,

I think what you are looking for is creating full backups as you do now, combined with backup of WAL files.
Having these 2 type of backup files you can do point in time recovery.
As WAL files will be written continuously, you basically have a live backup.

Although having done this in a training-situation, I haven't used this in a production environment so far.
I'd recommend looking for a partner that does know the pitfalls and can help you set this up.
There are multiple companies offering these kind of services.

Hi Jos,

We use an open source product called Barman to do the type of backups you are looking for (https://pgbarman.org/). It is pretty easy to set up and uses the WAL files so you have continuous backup. You can define your own backup policy to give you the number of backups and period of time that you need and as it uses the WAL files you have point in time recovery.

If you want any advice on setup then drop me a message.

Steve