Postgresql backup strategies

Questions and answers regarding general SQL and backend databases

Postgresql backup strategies

Postby jdbruijn » Mon Feb 06, 2023 6:06 pm

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)
Jos de Bruijn
Focus Feedback BV
Servoy Certified Developer
Image
jdbruijn
 
Posts: 492
Joined: Sun Apr 11, 2010 6:34 pm

Re: Postgresql backup strategies

Postby omar » Wed Feb 08, 2023 10:46 am

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...
Intrasoft, Founder
Omar van Galen
omar@intrasoft.nl
+31-(0)6-21234586
Servoy Developer
omar
 
Posts: 377
Joined: Sat Feb 12, 2011 4:51 pm
Location: Intrasoft, The Netherlands

Re: Postgresql backup strategies

Postby jdbruijn » Wed Feb 08, 2023 11:11 am

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?
Jos de Bruijn
Focus Feedback BV
Servoy Certified Developer
Image
jdbruijn
 
Posts: 492
Joined: Sun Apr 11, 2010 6:34 pm

Re: Postgresql backup strategies

Postby omar » Wed Feb 08, 2023 11:22 am

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.
Intrasoft, Founder
Omar van Galen
omar@intrasoft.nl
+31-(0)6-21234586
Servoy Developer
omar
 
Posts: 377
Joined: Sat Feb 12, 2011 4:51 pm
Location: Intrasoft, The Netherlands

Re: Postgresql backup strategies

Postby mboegem » Wed Feb 08, 2023 1:31 pm

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.
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1743
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: Postgresql backup strategies

Postby steve1376656734 » Thu Feb 09, 2023 10:06 am

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
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 1 guest

cron