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.
