I have set up Postgres conintuous backup by making a base backup and archiving WAL files, as specified here.

Our database is very low use (but very important to back up) so we produce a new WAL file every couple of days. I plan to force it to generate WAL files every hour, the questions is what criteria I should consider in setting the frequency of base backups? once per day, month, 3 months? what are the benefits / problems of long vs short intervals between base backups?


It's a trade-off between the cost of storage vs the cost of losing data.

Postgres will create a new WAL file when it reaches 16 MB. If that takes a week, then you might lose that 16 MB of data if bad things happen before the new data reaches that size.

If you set archive_timeout to an hour, then you get a new 16 MB file every hour, even if there isn't 16 MB of actual data.

It's faster to restore a recent base backup and fewer/smaller WAL backups too.

How quickly do you need to recover from a catastrophic loss?

Storage is pretty cheap these days, and people's time is expensive. I would go with at least a weekly base backup and hourly or daily incremental backups.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.