📜 ⬆️ ⬇️

Migration from Mongo to Postgres: The Guardian newspaper experience

image

The Guardian is one of the largest British newspapers, it was founded in 1821. For nearly 200 years of existence, the archive has accumulated a fair amount. Fortunately, not all of it is stored on the site - in just some past couple of decades. In the database, which the British themselves called the "source of truth" for all online content, about 2.3 million items. And at one point, they realized the need to migrate from Mongo to Postgres SQL - after one hot July day in 2015, the emergency transfer procedures were put to a severe test. Migration took almost 3 years! ..

We have translated an article that describes how the migration process went and what difficulties the administrators faced. The process is long, but the summary is simple: when embarking on a big task, accept that mistakes will be necessary. But in the end, 3 years later, the British colleagues managed to celebrate the end of the migration. And sleep.

Part one: the beginning


In Guardian, much of the content, including articles, blogs, photo galleries and videos, is produced inside our own CMS - Composer. Until recently, Composer interacted with Mongo DB running on AWS. This database was essentially a “source of truth” for all Guardian online content — about 2.3 million items. And we just completed the migration from Mongo to Postgres SQL.

Composer and its database were originally located in the Guardian Cloud - a data center in the basement of our office near Kings Cross, with an emergency switch elsewhere in London. One hot July day in 2015, our emergency transfer procedures were subjected to a rather severe test.

image
Heat: good for dancing at the fountain, destructive for the data center. Photo: Sarah Lee / Guardian

After that, the Guardian migration to AWS became a matter of life and death. To migrate to the cloud, we decided to purchase OpsManager , the management software for Mongo DB, and signed a contract for Mongo technical support. We used OpsManager to manage backups, orchestration, and monitoring our database cluster.

Due to editorial requirements, we needed to run a database cluster and OpsManager on our own AWS infrastructure, rather than using Mongo's managed solution. We had to work hard, as Mongo did not provide any tools for easy configuration on AWS: we manually filled out the entire infrastructure and wrote hundreds of Ruby skrits to install monitoring / automation agents and orchestrating new database instances. As a result, we had to organize in the team literacy sessions on database management - what we had hoped OpsManager would take on.

Since the transition to AWS, we have had two significant failures due to problems with the database, each of which did not allow us to publish on theguardian.com for at least an hour. In both cases, neither OpsManager nor the Mongo technical support staff could provide us with sufficient assistance, and we solved the problem ourselves - in one case, thanks to a member of our team who was able to deal with the situation by calling from the desert on the outskirts of Abu Dhabi.

Each of the problematic issues deserves a separate post, but here are the general points:


OpsManager did not keep its promises about simple database management. For example, the actual management of OpsManager itself - in particular, upgrading from OpsManager version 1 to version 2 - took a lot of time and specialized knowledge about our OpsManager setup. He also failed to fulfill his promise of “one-click update” due to changes in the authentication scheme between different versions of Mongo DB. We lost at least two months of engineers time per year on database management.

All these problems, combined with a significant annual fee, which we paid for the support contract and OpsManager, forced us to look for an alternative database with the following characteristics:


Since all of our other services run on AWS, Dynamo, the NoSQL database from Amazon, was the obvious choice. Unfortunately, at that time Dynamo did not support encryption of data at rest (encryption at rest). After waiting for about nine months until this feature is added, we eventually abandoned this idea, deciding to use Postgres on AWS RDS.
“But Postgres is not a repository of documents!” - you resent ... well, yes, it is not a repository of docks, but it has tables similar to JSONB columns, with support for indexes in the Blob JSON toolbox. We hoped that using JSONB we could migrate from Mongo to Postgres with minimal changes to our data model. In addition, if we wanted to move to a more relational model in the future, we would have such an opportunity. Another great thing about Postgres is how well it worked out: for every question we had, in most cases it was answered in Stack Overflow.

In terms of performance, we were confident that Postgres would cope: Composer is a tool for recording content only (it writes to the database every time a journalist stops typing), and usually the number of simultaneous users does not exceed a few hundred - which does not require the system super high power!

Part two: the migration of content of two decades has passed without downtime


Plan

Most database migrations involve the same actions, and ours is no exception. Here is what we did:


Considering that the database to which we migrated ensured the functioning of our CMS, it was critically important that migration caused the least possible disruption to the work of our journalists. In the end, the news never ends.

New API

Work on a new API based on Postgres began in late July 2017. This was the beginning of our journey. But in order to understand how it was, you must first clarify where we started from.

Our simplified CMS architecture was like this: a database, an API, and several applications associated with it (for example, a user interface). The stack was built and has been operating for 4 years based on Scala , Scalatra Framework and Angular.js .

After some analysis, we came to the conclusion that before we can transfer existing content, we need a way to communicate with the new PostgreSQL database, keeping the old API in working order. After all, Mongo DB is our "source of truth". She served us as a lifeline while we were experimenting with a new API.

This is one of the reasons why building on top of the old API was not part of our plans. The separation of functions in the original API was minimal, and the specific methods needed to work with Mongo DB could be found even at the controller level. As a result, the task of adding another type of database to an existing API was too risky.

We took a different path and duplicated the old API. So born APIV2. It was more or less an exact copy of the old Mongo-related API, and included the same endpoints and functionality. We used doobie , a clean functional JDBC layer for Scala, added Docker for local launch and testing, and also improved logging of operations and separation of responsibilities. APIV2 was supposed to be a fast and modern version of the API.

By the end of August 2017, we had a new API deployed, which used PostgreSQL as its database. But that was only the beginning. There are articles in Mongo DB that were first created more than two decades ago, and they all had to migrate to the Postgres database.

Migration

We should be able to edit any article on the site, regardless of when it was published, so all articles exist in our database as a single “source of truth”.

Although all articles live in the Guardian's Content API (CAPI) , which serves applications and the site, it was extremely important for us to migrate without any failures, since our database is our “source of truth”. If something happened to the Elasticsearch CAPI cluster, we would reindex it from the Composer database.
Therefore, before disabling Mongo, we had to make sure that the same request for an API running on Postgres and on an API running on Mongo would return identical answers.
To do this, we had to copy all the content into the new Postgres database. This was done using a script that directly interacted with the old and new APIs. The advantage of this method was that both APIs already provided a well-tested interface for reading and writing articles to and from databases, in contrast to writing something that directly addressed the relevant databases.

The main migration procedure was as follows:


Database migration can be considered successful only if end users have not noticed that this has happened, and a good migration script will always be the key to such success. We needed a script that could:


We started by using Ammonite . It allows you to write scripts in the Scala language, which is the main one in our team. It was a good opportunity to experiment with what we didn’t use before to see if it would be useful for us. Although Ammonite allowed us to use a familiar language, we found several flaws in our work on it. Now Intellij supports Ammonite, but during our migration he did not do this - and we lost auto-completion and auto-import. In addition, for a long period of time, it was not possible to run the Ammonite script.
Ultimately, Ammonite was not the right tool for this job, and instead we used the sbt project to do the migration. This allowed us to work in a language in which we were confident, as well as perform several 'test migrations' before running in the main working environment.

What was unexpected was how useful it was when checking the version of the API running on Postgres. We found several hard-to-find errors and limiting cases that we had not previously discovered.

Fast forward to January 2018, when it is time to test the full-scale migration in our pre-prod environment CODE.

Like most of our systems, the only similarity between CODE and PROD is the version of the application being run. The AWS infrastructure that supports the CODE environment was much less powerful than PROD, simply because it receives much less workload.

We hoped that the test migration in the CODE environment would help us:


In order to obtain accurate measurements of these indicators, we had to bring the two environments into complete mutual correspondence. This included restoring a Mongo DB backup from PROD to CODE and updating the infrastructure supported by AWS.

Migration of just over 2 million items of data should have taken much longer than the standard working day allowed. Therefore, we run the script in the screen at night.

To measure the migration, we sent structured requests (using markers) to our ELK stack (Elasticsearch, Logstash, and Kibana). From there, we could create detailed dashboards, tracking the number of articles successfully transferred, the number of failures and overall progress. In addition, all indicators were displayed on a large screen, so that the whole team could see the details.

image
Migration progress monitoring dashboard: Editorial Tools / Guardian

As soon as the migration was completed, we checked the coincidence of each document in Postgres and in Mongo.

Part three: Proxy and launch on sale


Proxy

Now that the new API running on Postgres was launched, we needed to test it with real traffic and data access patterns to make sure it was reliable and stable. There were two possible ways to do this: upgrade each client that accesses the Mongo API so that it accesses both APIs; or run a proxy that does it for us. We wrote a proxy on Scala using Akka Streams .

The work of the proxy was quite simple:


Initially, the proxy registered many discrepancies, including some difficult but important behavioral differences in the two APIs that needed to be corrected.

Structured Journaling

In the Guardian we keep a log using the ELK stack (Elasticsearch, Logstash and Kibana). Using Kibana gave us the opportunity to visualize the magazine in the most convenient way for us. Kibana uses Lucene query syntax , which is fairly easy to learn. But we soon realized that it was impossible to filter or group the log entries in the current setup. For example, we could not filter those that were sent as a result of GET requests.

We decided to send more structured data to Kibana, not just messages. A single journal entry contains several fields, such as a timestamp and the name of the stack or application that sent the request. Adding new fields is very easy. These structured fields are called markers and can be implemented using the logstash-logback-encoder library. For each request, we extracted useful information (for example, route, method, status code) and created a map with additional information needed for the log. Here is an example:

import akka.http.scaladsl.model.HttpRequest import ch.qos.logback.classic.{Logger => LogbackLogger} import net.logstash.logback.marker.Markers import org.slf4j.{LoggerFactory, Logger => SLFLogger} import scala.collection.JavaConverters._ object Logging { val rootLogger: LogbackLogger = LoggerFactory.getLogger(SLFLogger.ROOT_LOGGER_NAME).asInstanceOf[LogbackLogger] private def setMarkers(request: HttpRequest) = { val markers = Map( "path" -> request.uri.path.toString(), "method" -> request.method.value ) Markers.appendEntries(markers.asJava) } def infoWithMarkers(message: String, akkaRequest: HttpRequest) = rootLogger.info(setMarkers(akkaRequest), message) }

Additional fields in our journals allowed us to create informative dashboards and add more context regarding discrepancies, which helped us identify some minor inconsistencies between the two APIs.

Traffic replication and proxy refactoring

After transferring the contents to the CODE database, we received an almost exact copy of the PROD database. The main difference was that CODE had no traffic. To replicate real traffic to the CODE environment, we used the open source tool GoReplay (hereinafter referred to as gor). It is very easy to install and flexible to customize to your requirements.

Since all traffic entering our APIs first got on the proxy, it made sense to install gor on proxy containers. See below how to load gor into your container and how to start tracking traffic on port 80 and send it to another server.

 с wget https://github.com/buger/goreplay/releases/download/v0.16.0.2/gor_0.16.0_x64.tar.gz tar -xzf gor_0.16.0_x64.tar.gz gor sudo gor --input-raw :80 --output-http http://apiv2.code.co.uk

For a while, everything worked fine, but very soon a malfunction occurred when the proxy became unavailable for several minutes. In the analysis, we found that all three proxy containers periodically hung at the same time. At first, we thought the proxy server was down because gor was using too many resources. Upon further analysis of the AWS console, we found that proxy containers hung regularly, but not simultaneously.

Before we go into the problem further, we tried to find a way to run gor, but this time without additional load on the proxy. The solution came from our secondary stack for Composer. This stack is used only in case of an emergency, and our working monitoring tool constantly tests it. This time the replay of traffic from this stack in CODE with double speed worked without any problems.

New findings raised many questions. The proxy was built as a temporary tool, so it may not have been as carefully designed as other applications. In addition, it was built using Akka Http , with which none of our team was familiar. The code was chaotic and full of quick fixes. We decided to start a great work on refactoring in order to improve readability. This time we used for-generators instead of the growing nested logic that we used before. And added more logging markers.

We hoped that we would be able to prevent the proxy containers from hanging, if we examine in detail what is happening inside the system and simplify the logic of its operation. But it did not work. After two weeks of trying to make the proxy more reliable, we felt trapped. It was necessary to make a decision. We decided to take the risk and leave the proxy as it is, since it is better to spend time on the migration itself than to try to fix a piece of software that will become unnecessary in a month. We paid for this decision with two more failures — almost two minutes each — but this had to be done.

Fast forward to March 2018, when we’ve already finished migrating to CODE without sacrificing API performance or client experience in CMS. Now we could start thinking about retiring proxies from CODE.

The first step was to change the API priorities, so that the proxy first interacts with Postgres. As we said above, this was solved by changing the settings. However, there was one difficulty.

Composer sends messages to the Kinesis stream after updating the document. Only one API was supposed to send messages to prevent duplication. For this API, they have a flag in the configuration: true for the API supported by Mongo, and false for the supported Postgres. Simply changing the proxy so that it first interacts with Postgres was not enough, since the message would not be sent to the Kinesis stream until the request reached Mongo. It's been too long.

To solve this problem, we created HTTP endpoints to instantly reconfigure all instances of the load balancer on the fly. This allowed us to very quickly connect the main API without the need to edit the configuration file and redeploy. In addition, it can be automated, thereby reducing human interaction and the likelihood of errors.

Now all requests were first sent to Postgres, and API2 interacted with Kinesis. A substitution could be made permanent with a configuration change and a re-layout.

The next step was to completely remove the proxy and force clients to contact the Postgres API exclusively. Since we have a lot of customers, updating each of them individually was not possible. Therefore, we raised this task to the DNS level. That is, we created a CNAME in DNS that first pointed to the ELB proxy and would be modified to point to the ELB API. This made it possible to make only one change instead of making updates to each individual API client.

It's time to move the PROD. Although it was a bit scary, well, because it is the main working environment. The process was relatively simple, since everything was decided by changing the settings. In addition, as the stage marker was added to the logs, it became possible to repurpose previously built dashboards by simply updating the Kibana filter.

Disable Proxy and Mongo DB

After 10 months and 2.4 million transferred articles, we were finally able to disable all the infrastructure related to Mongo. But first, we had to do what we all expected: kill the proxy.

image
Logs showing the shutdown of the Flexible API Proxy. Photography: Editorial Tools / Guardian

This small piece of software caused us so many problems that we were eager to turn it off as soon as possible! All we had to do was update the CNAME record so that it points directly to the APIV2 load balancer.
The whole team gathered around one computer. It was necessary to make only one keystroke. Breath held for all! Complete silence ... Click! It is done. And nothing flew off! We all happily exhaled.

However, deleting the old Mongo DB API hid another test. Desperately removing the old code, we found that our integration tests were never adjusted to use the new API. Everything quickly turned red. Fortunately, most of the problems were related to the configuration and we fixed them easily. There were several issues with PostgreSQL queries that were caught by the tests. Thinking about what could have been done to avoid this error, we learned one lesson: when embarking on a big task, accept that mistakes will be necessary.

After that, everything worked smoothly. We disconnected all instances of Mongo from OpsManager, and then disconnected them. The only thing left to do was to celebrate. And sleep.

Source: https://habr.com/ru/post/436416/

More articles: