Обсуждение: need a hand with my backup strategy please...
Morning
I made the mistake of thinking that I could recover to any point in time with a logical backup plus WAL files, unfortunately that is not the case. I was rsync'ing wal files to another system, and set the archive timeout to 5 mins, and the retention to allow for 25 hours worth or so. Unfortunately though if there is heavy load, the wal files will be generated more regularly than the 5 minute max.
So I'm back to the drawing board for both reasons above.
I've set up pgbarman (seems to be an excellent project – thank you).
My question is how often the base backup should be done. Is it reasonable to specify:
minimum_redundancy = 1
retention_policy = RECOVERY WINDOW OF 1 DAY
and then set up cron to do a base backup every day? I guess also it could be done once a week with a longer retention policy, at the expense of more disk space for wal files.
And then run the "barman cron" immediately after which will take care of deleting redundant backups? In fact it doesn't seem to so not sure how to handle that.
I think I will stick with the nightly backups using pg_dump of the individual databases, as this is useful for refreshing UAT from production and other tasks.
But does this sound like a reasonable strategy for handling PITR? I have read everything I can about this but haven't found a canonical suggestion for implementing.
Bonus question is, the barman cron seems to actually apply the incoming wal segments to the base backup. If it does do this, how is it possible to restore to a point in time?
Thanks for any help.
Cheers, jamie
==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
Unfortunately though if there is heavy load, the wal files will be generated more regularly than the 5 minute max.
>> Unfortunately though if there is heavy load, the wal files will be generated more regularly than the 5 minute max. > If you wrote the WAL to an external, mounted disk, wouldn't that solve the issue? What I'm trying to say is that I configured it to keep (60/5) * 24 segments plus a few spare, because I am switching xlogevery 5 mins. But if there is heavy load then they will be generated more often than every 5 mins, so that number won'tbe enough. But I think it's a moot point because they seem to be useless unless you have all of them up to the last base backup. Whichto me indicates I need to do a base backup every day...? Cheers, jamie =============================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html ===============================================================================
Jamie Echlin wrote: > What I'm trying to say is that I configured it to keep (60/5) * 24 segments plus a few spare, because > I am switching xlog every 5 mins. But if there is heavy load then they will be generated more often > than every 5 mins, so that number won't be enough. You should delete archived WAL files by age, that way you shouldn't have a problem. > But I think it's a moot point because they seem to be useless unless you have all of them up to the > last base backup. Which to me indicates I need to do a base backup every day...? You just need any base backup plus *all* archived WALs since the beginning of the backup. Of course you want a fairly recent backup, otherwise applying the WAL files can take very long. It is a good idea to keep more than one base backup in case something goes wrong (bad backup). Another reason to keep older backups is that you may want to recover to a point in time that lies further in the past, e.g. if it takes some time to discover a problem that requires recovery (corruption, ...). Yours, Laurenz Albe
Thanks for your answer Laurenz. I was planning to rely on the disk backup (of the base backup) if I wanted to restore to a version before the last on diskbase backup. But your point about redundancy is good... I think I will keep two base backups, and do a base backup everyday. Over the network this takes 5-10 mins or so, so not a big deal. I'll retain wals for 48 hours. Having to go to a PIT before the last hour or so would be a major undertaking anyway. Cheers, jamie =============================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html ===============================================================================
Jamie Echlin wrote: > I was planning to rely on the disk backup (of the base backup) if I wanted to restore to a version > before the last on disk base backup. But your point about redundancy is good... I think I will keep > two base backups, and do a base backup every day. Over the network this takes 5-10 mins or so, so not > a big deal. > > I'll retain wals for 48 hours. Having to go to a PIT before the last hour or so would be a major > undertaking anyway. I have seen cases where a software bug in the application gradually caused data in the database to be changed. It took a while to notice that. In such a case you want to have a backup from a month ago or more so that you can extract the data as they were back then and try to repair as much as possible. Also, what if a problem was introduced right before the weekend and noticed immediately afterwards? That might be more than 48 hours ago. There are other scenarios where a backup from longer ago would really help. Yours, Laurenz Albe