GitLab Database Team, 4 Jan 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Database migration testing run through (preview version)

Description

Talking through a CI-based workflow that allows us to test database migrations on a thin-cloned production dataset (@abrandl).

For details and to leave feedback, please see https://gitlab.com/gitlab-org/database-team/gitlab-com-migrations/-/merge_requests/1.

Apologies for the screen sharing quality, looks like my CPU has been maxed out in the end while recording this - also hadn't expected my camera stream show up next to the screenshare. Next time... ;)

Here are a few links:
1. The CI job kicking off the automation: https://gitlab.com/gitlab-org/gitlab/-/jobs/943411439
1. The migration job: https://ops.gitlab.net/gitlab-com/database-team/migration-testing/-/jobs/2768158
1. The comment posted back on the original MR: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/50730#note_477762322

A

Hi, this is andreas from the database group, so one of our hot topics has been to improve our abilities in testing database migrations and in this video I'd like to walk through the aci based solution and give a rough overview of how this could look like, but first establishing some context for that. So the problem that we typically face with database migrations is that we don't have enough test data in development or also in staging compared to the size of the production database.

A

So development is typically pretty empty on your machine and then staging is today at five percent of the production database size, and this basically means that there is a mismatch um between what we test with and production and that we deploy database migrations in production without having tested them before on any comparable data set.

A

What we do instead is. We rely a lot on on our code review process which works very well, and there are a few tools around that help with reviewers and maintainers to make a call on those changes.

A

But a lot of this work is kind of manual um and not everybody has access to it. So the uh ci based solution we talked about here basically adds to this toolbelt and it allows us to automatically execute any database migration during a gitlab ci run on a data set that is very close uh to production and the I think minutes here, like the difference shouldn't be very large.

A

um This is on the back end. This is based on on database labs, which is a thin cloning technology based on zfs snapshots. So we can use that to gather a full clone of the production database very quickly and then use that to test migrations on it and, of course this is also the security side of it, um and this is a very important one for us, so we're working we're working with production data there, and that makes things more complicated, but it's also. This is also addressed in the solution that I'd like to share.

A

I'm not going to go into the details too much, but rather show the workflow and how these things could go together um and especially for the security concerns, we'll make sure to perform a security review before this becomes available. More broadly, so, to start off with what we typically see is um a change with a with a database migration inside, so I've prepared a very small one.

A

This is just a migration um that is removing an index which would be very quick in the end, um and we use that as an example to see the workflow I already pushed that to um so there is already a pipeline um on the way. So let me take a look if I can find that again and yeah. This is the one. So this is the pipeline.

A

I'm also opening up the merge request that I created, um and this is the job that we're waiting to see so that we added one job to the ci pipeline that basically triggers the migration testing in the background, and we expect once that is run, we expect the automation to run in the background and come back with the results for that. So we can watch out for that. um The whole pipeline takes a while so uh yeah. We can probably use that time to run through a couple of explanations as well.

A

A

In terms of components, what we have is a gitlab project under the database team namespace. This is a public project.

A

um There is no secrets in there anyways and this project is basically being been mirrored over to the ops instance. This is a private instance that we run. um That also has the ci pipelines and the secrets for that pipeline, and we can. We can limit the access to that project in that way.

A

So, basically, when we look at the ci pipeline for this project, there is three components involved. We have two private runners, so those are gitlab runners.

A

One is the builder, the other one is the worker, and the idea is that the the worker is the one that executes the the application code, and this is the migration code that, where that we'd like to test- and we don't have to have a lot of control over this- so basically you can send any any um database migration or even any code for that matter, and we run that on the production database or a copy of it, and in order to address the security concerns.

A

What we do here is we really limit down the network connectivity for the component that executes the code so that it can't talk to anybody else except locally, and this is also true for the runner. So this, the runner network is also much much more limited than than you would usually do that and in order to still be able to prepare docker images, pull install gems and everything else.

A

We have the builder runner that is basically building all the docker images, sending them to a local registry, and then the worker takes over, which is much more isolated on the network.

A

It runs the application code to test the database migrations and then we can be sure that no no data escapes from from that run. The third component that is involved is database labs.

A

This is basically providing us with the copy of the production database and then looking at the worker workflow a little bit more. We have this good live rails container.

A

That is very much limited on the network, so internally it has ip tables installed. It drops basically all the traffic except for traffic to service containers.

A

uh Those are containers on the same docker network and we have two of them. There is the redis service that we, this is kind of a dependency for the database migrations, and then this is the more interesting one is um typically, you would find um postcards running here in ci, a postcode container, but instead of doing that, we basically run a container that performs ssh port forwarding to the database lab instance, and this is for two purposes.

A

One is to talk to the api to the database labs api and also then to to create a thin clone from the production database and then also forward the postgres thin clone. That is running on that instance, and expose that, as the standard post transport to the docker container, to get libraries.

A

Right, so this is how that um goes together and then what we do uh so basically, the security concern is that we, basically we don't want to allow running arbitrary code on the production database on the copy of the production database and to access results of that code. So what can that mean there is? You can have? You can send arbitrary code and you can have some output. You can print stuff, for example, we don't want to allow you seeing that output. Basically, the same is true for network access.

A

We don't want that code to be able to send something over the network. That's why we limit it very, very tightly on in the docker environment and also on the runner.

A

And how do we get feedback from from that code because we still allow you to run arbitrary code on the production database and a copy of it?

A

What we do is we provide a sort of a clean interface through a json file that comes out of that run. So basically that gitlab rails component, it runs the database migrations and we can have some. We have some instrumentation on that.

A

So in this version we only track the runtime of the migrations very basic, but basically that spits out a hsn file in the end, and this is an artifact of the of the build and we can use that to um notify the merch request, and at the moment we send a comment back to the original merge request, explaining how things went and currently that contains a summary of the run times for the migration. We can see that later.

A

So this is basically.

A

In summary, we allow you to run code arbitrary code from those migrations we very tightly control the way where we basically lock down access to any results of that run, and instead we provide a a data export that we control um in order to report back to the um to the the merge request with details on that.

A

There is more in this readme file if you want to take a look, so this is in the um good lab, comm migrations repository and now, hopefully uh the job has been picked up and um actually has so. This is the um it loves the eye job and that triggered a run of the downstream pipeline.

A

We can take a look at that pipeline as well, so this is on the on the ops instance, and there is nothing secret in those. So that's, that's all fine. So let's take a look at the pipeline as well. uh We can see those components again, so this is building the images. This is on the builder runner.

A

This is the one that is locked down and it runs the database migrations, and then we have a final phase where we basically send back a ping to the merge request with some details on the database, migrations looking at the running the migrations. So this is the typical output that you would see from running, running rails, migrations, and so it starts off here and then it runs apparently longer longer. Migration.

A

And then a few more and we can see our migration in here. The reason we see more migrations is because, right now there is um these exist in master, so in the master branch, but they haven't been deployed yet to production. So since we merged in master we'll also see those execute, um we can remove them from the summary later, but there can be dependencies between migration and that's why we execute them before our migration can run.

A

So this was successful. We see this is the json file that was created. This is an artifact um to the from the job, and then we can look at the um upstream job that sends I'm sorry to notify upstream that sends the notification back to the metroquest. That is also successful and then.

A

We can go back to the original merge request, which is this one, and if everything runs smoothly, then we should see a comment in there indicating the run times for the database. Migrations.

A

Which is not the case so um really sure why I can show you a example. This is kind of the demo mode right. Something has to fail.

A

So I tried this before, and this is what you basically got back. So um these are the details for the database, migrations that were run on the on the testing job um you can see. This was the one that is a bit took a bit longer in the beginning, and this is the one that we're talking about.

A

We just report back to runtimes.

A

This can be like this can get very detailed, so you can get back um query statistics um we've had some when we did that manually, we created some graphs that were useful to have um and that kind of stuff can be extended later, but this is kind of kind of the idea of that project: cool yeah! That's that's all I have um if you want to leave feedback on that, um it's very much appreciated. So uh there's this uh merch request.

A

Now I'm also going to link that in the youtube, description and um yeah drop by in the slack channel, if you'd like to in the chi underscore database channel- and let us know what you think of that thanks.