Triplea: we need automated systematic backups of DB data

[DanVanAtta]

To my knowledge there is no automated job to run postgres dump and get a copy of DB as backup.

We need to do this manually until we have a systematic way to do this in place. The mechanism should be installed via the infrastructure scripts.

This is a p1 as it's really dangerous to not have an active copy of DB handy. I have a dump used for test data that is maybe 2 weeks old, that is nothing we should be using if the excrement were to per chance hit the fan - we should have a few copies in a well known backed-up location.

[DanVanAtta]

Requirement note: the backups can be stored locally, but must also/or be sent off-server. Assume in the disaster scenario where we need these backups that the DB server is physically dead.

[RoiEXLab]

@DanVanAtta An option would be to spend some money on linode to have them automatically create backups of the complete image for us.
This way applying the backup would be super simple.
The same is true for the forum server as well.

[DanVanAtta]

What would the cost be?

The scripting for a DB backup is pretty minimal, I think we can get that done and avoid the ongoing cost.

The forum I less confident on. @RoiEXLab do you know if a backup mechanism exists that would be relatively easy to do? The real acceptance criteria here is that we can restore from a backup (no real requirements on how or where that is stored, and that is relatively recent, within a day)

Given that, it may make sense to do a linode backup of the server node only. Short and medium term a backup of the lobby node (that notably has the DB) on it would be a candidate for a backup plan until we can get something in place.

[RoiEXLab]

@DanVanAtta just checked, the price for daily backups that remains up to 2 weeks (a daily backup, an up-to-a-week-old backup, a backup that's between a week and 2 weeks old, and a user initiated backup) would be 2.50$ a month

[DanVanAtta]

Pretty reasonable. Any quick thoughts on level of effort to do a manual backup of forum @RoiEXLab ?

[RoiEXLab]

@DanVanAtta All we'd need to do is to backup the nodebb database (MongoDB based) and push it to another server.
Everything else (except maybe for forum uploads, profile pictures etc. and the config file that tells the server which port and database credentials to use) isn't crucial data and would not be 100% necessary to backup.

[prastle]

@DanVanAtta @RoiEXLab @ron-murhammer If I am understanding the above correctly by removing one bot server we would cover these costs np. Since often we have 8 empty bots lately and many seem to be hosting again this should not be a problem. Just my two cents. Seems like a good idea to let Linode do it for us.

[DanVanAtta]

Having a data backup is more comprehensive, not necessarily mutually exclusive to a host image snapshot.

Half if not more than half of the problem is being able to restore from backup, so linode backup image does not necessarily fully solve this for us. So on the one side while automating this, likely the extraction of data will be similar to insertion. Second side of the coin, it's not necessarily that much effort to extract some data and scp (copy) it to another machine.

[RoiEXLab]

@DanVanAtta @prastle Just checked the stats, the forum seems to have ~3000 visitors daily.
To be safe I enabled the automatic Linode backups which will from now on do backups daily and keep one backup from yesterday, one week ago, one two weeks ago, and a manual backup.
If there are any objections on this decision (which costs us 2.50$ a month as mentioned earlier), feel free to disable them again.

[DanVanAtta]

This was not a p1 as soon as we started doing the linode backup. The reason this was a p1 was because of a glaring gap in DR. If the DB crashed for whatever reason, we would have been without any data to restore from.

The fact this was a p1 for a few months and not actively investigated is a miss on my part. @RoiEXLab @ron-murhammer , to be sure, p1 means "active impact" and pain is being felt. We should not let "p1's" sit, or else they mean nothing and we ought to remove the label.

[DanVanAtta]

$2.50/ month = $25/yr, and adds +50% cost to yearly cost of forum server to at least $75/yr.

All in all I'm okay with that math. I'm only really concerned when we start paying $2.50/month for many servers and suddenly we have increased our server cost by +%50%. We are currently sitting at 10 servers for $550/yr, a +50% hit would be talking an additional $200/yr. Having just one or two target servers with linode image backups as our backup strategy is IMO okay, but kinda expensive if we do that for everything.

For now dice server is on same server as forum which benefits from image backup. When we move dice server to a new server, we can deal with the backup consideration at that time. There is no current plan to do a backup of forum DB outside of linode backup. With that and https://github.com/triplea-game/infrastructure/pull/65, seems we should be covered and if any one server were to be lost, we could recover.

[prastle]

From what I read we still need to do an update for it to taker effect. Yes I am ok with this as well.

[prastle]

either way I have np bumping funds from paypal for another 120 till end of the year. Lets get er done gents :)

[DanVanAtta]

@prastle at this point mostly moot as it seems only forum will need long-term extra cost. I would do the calculation at a 3 or 5 year time frame. If we are not likely to update this soon, we are even less likely to update it as time goes on. So in part we are looking at the long term backup strategy here to ensure we are covered.

This ticket is pending any new areas to be discovered that we should build a back up for in case we have a data/server crash.