As Yandex.Money migrated with Oracle on PostgreSQL

Yandex.Money was transferred a part of financial service from Oracle DBMS to PostgreSQL without loss and stops in work.

Yandex.Money was transferred a part of service with Oracle to PostgreSQL

Staff of Yandex.Money told about migration process in the blog on Habrahabr.ru.^[1]

For the first migration and a running in of the solution the service servicing the user profiles was selected. The history of payments, favorites, reminders and other tools not critical for system operation belongs to a profile. In service the database without the built-in logic therefore it was necessary to transport on the new platform only data and sequences was used.

The project needed to be completed in three months. The command of migration consisted of 4 people - developers of the Senior level and specialists of DBA.

That the small command could cope in a short time, most automated solution was required: without writing any code data migrations, manual assembly of the scheme DB, etc. The base contains about 50 tables therefore the probability of a human error is especially high at manual conversions.

Usually such migrations are executed with a stop of service — in time off. But payments take place in Yandex.Money round the clock therefore it was impossible to stop a system for the sake of internal "optimization". Actually for this component business approved to us suspension for one-two minutes, without data loss, - the staff of Yandex.Money notes.

In search of the suitable tool specialists of the company collected and eliminated the list of products for migration: Oracle GoldenGate, SymmetricDS, Full Convert, Oracle to PostgreSQL Migration, ESF Database Migration Toolkit, Ora2Pg and SQLData Tool.

The solutions Ora2Pg for transfer of the database scheme and SymmetricDS for data migration were as a result selected.

During a migration running in on the copy of production-base, the whole set of "features" and strangenesses was found in the migration solution. PostgreSQL not correctly fulfilled some requests which went without problems in Oracle. There were difficulties with transactions, with the wrong sorting of the displaid values, etc. Nevertheless all these defects were successfully solved.

Data migration was step-by-step — according to in advance approved list from 50 tables. Switching was made at the level of each table in base that allowed to reach high flexibility and decomposition of process. So at some point, after data transfer, for the table the special flag on which the service "Ya. Deneg" switched to a copy of data in PostgreSQL was exposed.

In the course of migration a part from 50 tables worked for Oracle, another — for PostgreSQL. Here SymmetricDS which migrated data from Oracle in PostgreSQL was useful and by that provided consistency both for logic with the postponed tables, and that else worked according to the old scheme.

After data transfer in service the checkbox "work with PostgreSQL" for each specific table was selected, and requests passed to new DBMS. At first switching was checked at the dev-stand, then on acceptance. Further there was a switching on production and renaming of the table into Oracle (to understand whether the old table is used still somewhere).

But it was correct to most difficult to pick up the switching moment. Actually specialists of Yandex.Money had 3 options of migration, depending on criticality and complexity of the table:

1. Transfer of the table entirely with the subsequent switching. The option approached when a part of requests could be lost (the user it is possible to ask to click once again, or automatic continuation of transaction will work).

2. At the level of data center (all them 2). The method for transfer of critical tables at which at first the first DPC is transferred to PostgreSQL and in the course of its inclusion is disconnected the second (with Oracle). For the period of start — stops of Oracle and PostgreSQL could work in parallel therefore here and mechanisms of synchronization and switching of data were useful. Idle time in work of services at the same time was not.

3. By a technique "residual a dozhatiya". Preserving of 2 copies: Oracle for processing of the requests which were late to switching and new PostgreSQL. New tasks were processed in new base, and old were removed after accomplishment in the remained Oracle. So queues of base — autopayments, reminders, etc. moved.

It was not also without difficulties. At primary formation of the scheme DB under PostgreSQL the converter issued not for 100% ready option, as it was expected specialists. It was necessary to change manually some types of columns, to correct sequences and to break the scheme according to tables and indexes.

It turned out a bit later that SymmetricDS does not synchronize the table with a capacity more than 150 GB. Therefore it was necessary to create bypass option of transfer on such cases. Besides, SymmetricDS did not transfer the CLOB\BLOB field if because of them the total volume of the table therefore it was necessary to write manual queues of migration was exceeded.

Staff of Yandex.Money also faced absolutely exotic cases when migration with Oracle on PostgreSQL led to sharp shrinkage of performance. In this case nothing remained, except how manually to analyze each separate incident. For example, for one table it was necessary to select the CLOB field in the separate table, to transfer it to a SSD disk and to read this field only if necessary.

As it was necessary to support at the same time active old and new copies of a DB, for new the stock on sequences that there was no stratification and attempts of transfer in PostgreSQL of the duplicated keys was made. So if in the table the last key was 100, then at transfer was added to this value 1000 more that SymmetricDS could synchronize freely keys 101, 102 and all others without rewriting of new data.

For planned quarter the command of Yandex.Money transferred 80% of tables for PostgreSQL. The remained 20% are big tables (on average more than 150 GB), including the combined table with the volume CLOB\BLOB fields. All this was necessary to complete manually the next 1.5 months. Nevertheless the linking of SymmetricDS and Ora2Pg made the most part of routine work, as it was required under the terms of a task.

Read Also

• Migration with Oracle on the alternative systems

Notes