set up longer-term database backups

[zkat]

We've got S3 covered with #392, but fly.io only retains db snapshots for 7 days. What should we do about longer-term snapshots/storage?

For fly.io: https://community.fly.io/t/point-in-time-backups-using-postgres-and-wal-g/6867

• [x] Set up streaming backups with wal-g
• [ ] Set up periodic full backups on an app server Oban cron job

Update: Reached out to fly support and got a helpful email on how to set the cron job up!

[MordecaiMalignatus]

Some notes from $WORK where we have some compliance things, but also have a very large GCP bill so storage costs are generally considered irrelevant:

• We store a subset of "hot" snapshots (last 48h to last week depending on application) directly in GCS (S3 but GCP), and directly on disk for some more critical ones like Consul.
• We then start thinning the snapshots and move about 20% of those into "infrequent access" on a more locked-down GCS bucket (Should correspond to "cold storage" on S3). This is done via, essentially, a cronjob that wakes once a day, grabs the most recent snapshot, and shoves it to the other bucket. These expire after a year, generally.
• After anywhere from a month to six months we pick ~1 per day/week/relevant period and move it into what we charmingly dub "compliance storage". This would be glacier for AWS. I'm pretty sure these have object expiration but I'm not sure what it's set to, I don't have access to the compliance storage project.

We have a very large central MSSQL cluster that is handled differently, but i don't know how precisely they do backups/storage.