Automate refresh of database to staging

added deploy ~2024676 labels

@ahanselka in your role of data durability enforcer, and @yorickpeterse in your database specialist role, can either of you volunteer for this / take it on?

mentioned in issue #1743 (closed)

@ernstvn I think we should combine this with https://gitlab.com/gitlab-com/infrastructure/issues/1265, that way we have both an up-to-date staging environment and a way of testing our backups.

Having said all that, this involves mostly infrastructure work which isn't my strongest point.

@yorickpeterse that is an interesting proposal.

I'd like to see what @northrup and @omame thoughts are on this. It seems to be a good idea, combine two things into one. However, I think we would still want a special test host as well.

mentioned in issue #1265 (moved)

I like it!

mentioned in issue #1504 (closed)

That's really important for me as a developer. Staging is the place where I can test the performance of SQL queries on a dataset that is at least similar to production. If this dataset is far behind the production database then the results are no longer representative. /cc @smcgivern

Yeah, for me there are two factors here:

Some features assume recency, so they don't really make sense in staging as it is now. Anything to do with user activities is a good example here.
When a feature adds something to the DB, but will only do so when used, we won't get representative data in staging. We will get some data, but not all. An example here would be https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/12047 - once that is deployed, the table will be there, but staging will have an empty or nearly-empty table, while in production this table can get into millions of rows very quickly.

It seems like this is currently blocked on some other things that need to happen, so I'll just leave this at the statement of the main issues I have.

mentioned in issue #1211 (closed)

So this seems to be blocked by #1265 (moved) which in turn is blocked by #1211 (closed). But @pcarranza just pointed out that #1211 (closed) may not be an actual blocker for #1265 (moved)... so... @pcarranza Can you clarify the relevant dependencies here?

@ernstvn https://gitlab.com/gitlab-com/infrastructure/issues/1265 this is what we need, we can start with a single host that periodically restores the dump and records the result somewhere (during night time? if we have such a thing)

This would allow us to validate the backup, and we could be using this as a staging database.

https://gitlab.com/gitlab-com/infrastructure/issues/1211 would allow us to make the process way much more dynamic and have as many as we want, but we can take a shorter step first.

My concern about this is that it has been previously mentioned numerous times that staging has transactions and database additions in it that production doesn't have because we apply to staging and then move to production. Refreshes of staging from production on a regular schedule would bring an inconsistent state to staging as it would have newer code with an older database.

The only real time when we are certain that a refresh of staging can be automatic is at 0:00 on the 23rd of the month.

@northrup isn't that just the case between a deploy to production and a deploy to staging? I don't think staging is ever ahead of production immediately following a production deploy.

My point being though that deploys are random and sporadic, and the only sure time I know to automate was the time given. I'm also going to posit that a monthly refresh of data to staging is sufficient to get a fell for transactional speed as well as data freshness. Any objections to refreshing once a month?

@northrup ah, I understand. Once a month would be great!

@northrup you are right and we are being silly.

What we need to do is: when we test restores (that we do once a month) we do it in the staging database. For which we just need to provision a new database server with terraform and execute the restore (manually).

This will kill 2 birds with one stone:

We will be testing the restores as we are, but we will also make those restores productive.
We will start getting closer to automating the restore because we will have a chef role/recipe that will be applied by terraform in the initial bootstrap, which can easily turn into restoring automagically.

cc/ @ahanselka @ilyaf could you please review what we need to do to replace the current staging with a new one that has a fresh backup restored using Wal-E ?

and cc @omame because we are talking about provisioning moar things with terraform+chef

@ernstvn this would be a "semi-automatic" restore, which is a good first step and will make everyone happy.

@omame I'd like to use this opportunity as a crash course in terraform-foo. I know the "restore" side of things from May B.A.D., and I'd like to automate that.

Looks like we have an owner!

Woo-hoo, I won again! So much winning! /ducks and hides in the bushes

but seriously, yea, the result from last B.A.D. was just an update in documentation, but its still toil, and next person who performs this will still spend 4-6 hours. I want to leave it in much better state than that, while I still remember how I did it.

Plus, I need to restore my azure access back, got blocked or something :)

@ilyaf I'd love to work on this with you. I feel like we can learn a lot together

@ilyaf do it manually once more time, take notes, and leave a cookbook behind so we can trigger it from terraform.

The next one will be just tainting a resource.

@omame I'd like to use this opportunity as a crash course in terraform-foo. I know the "restore" side of things from May B.A.D., and I'd like to automate that.

I'd love to! It's also a good opportunity for me to learn how to restore the db.

Just heads up from last two B.A.D.s:

We need Vault to automatically grab secrets (https://gitlab.com/gitlab-com/infrastructure/issues/1864) like WAL-E env vars, GPG keys and passphrases, and AWS access tokens
I need some automatic criteria on what to consider "restored" database. For now I just make sure postgres starts after wal-e restore, and (provided I usually restore to 6:00:00 time) I just run few selects like select last_edited_at from issues order by last_edited_at desc limit 1 and make sure that time is something like 5:59:59.123. Similar checks for secondary databases.

But, point is -- except from copying secrets and fiddling with GPG, everything is already in the form of crude shell scripts, meaning we can have a fully automated check right after Vault. I can automatically restore database to desired time, and then we have to figure out how to actually verify it.

Has this issue experienced scope creep @ilyaf and @pcarranza ? Does it make sense to spawn new bite size "clean" issues?

@ernstvn I think its fine, its just blocked. We do need Vault (https://gitlab.com/gitlab-com/infrastructure/issues/1864) and after that we will need some automated criteria to check, otherwise the restoration will need human to poke around in db. But if i'm missing some context here and we need only restore db to staging w/o checking, then i'd say we only need vault.

I'll mark it as blocked now, tom make it explicit.

added blocked label

mentioned in issue #3057 (closed)

added OKR label

added OKR and removed ~2937368 labels

removed milestone

mentioned in issue #3752 (closed)

@djensen asked me about refreshing staging today since it's lagging GitLab.com by 2 years now or getting access to run analytics on a current database (i.e. what the data team has). @dawsmith Do you know which team might be owning refreshing staging?

@stanhu - I think discussion would likely be best continued on https://gitlab.com/gitlab-com/gl-infra/infrastructure/issues/6572.

cc @ansdval

@dawsmith yep, seems like #6572 (closed) is the long-term solution. But there hasn't been much activity on it, so GitLab is still performing QA in an environment that is insufficient in many ways. For example, I'm with ~"group::analytics". We are consistently unable to QA things properly in staging, and we're definitely introducing more bugs than we should

As a more immediate improvement, what about automating the refresh of only gitlab-org to staging? That wouldn't require the same scale of sanitization or processing as what #6572 (closed) proposes. And it would make the staging environment much more powerful for QA purposes. Would that be viable?

Adding @albertoramos to this discussion as the ~"team::Datastores" has been involved with staging and databases lately.

We have been talking about this on and off for quite a while already, in Datastores. Main reason is the work to achieve this "Automate refresh of database to staging" seems pretty significant, and the reliability teams are overloaded with other priorities lately.

To me, it's unlikely that this will be prioritized over many other things that are waiting in our queue. However I'll try to get more Infrastructure opinion on this and update this issue.