Миграция микросервиса с геоданными с MS SQL на PostgreSQL

Проблема

Не секрет, что тема перехода в IT-сфере на технологии, не требующие дорогостоящего лицензирования становится всё более актуальной. В то же время, очевидно и стремление компаний попасть в Реестр отечественного ПО, чтобы получить разного рода преференции для себя и своих сотрудников.

С точки зрения обновления технологического стека, мы воспользовались методическими рекомендациями Реестра отечественного ПО и приняли решение о переводе наших проектов на технологии со свободными лицензиями, в частности .NET 6 и PostgreSQL. Это открыло нам путь как к оптимизации производительности приложений и уменьшению расходов на лицензирование, так и добавлению решений компании в реестр.

В данной статье предлагаю рассмотреть путь по миграции географического микросервиса с MS SQL на PostgreSQL с фокусом на пространственные (spatial) типы данных.

Вопрос стоимости лицензий и непосредственного сравнения MS SQL vs PostgreSQL опустим, т.к. эта тема весьма хорошо раскрыта в DotNext-докладе моего коллеги, Станислава Флусова (@stus). Рекомендую к просмотру!

Пути миграции

Поскольку типы данных и встроенные функции в MS SQL и PostgreSQL зачастую имеют свои особенности, нельзя просто так взять и накатить бэкап/скрипт одной СУБД на другую.

Задача осложнялась еще и необходимостью перевести 150 ГиБ пространственных данных (типы GEOGRAPHY и GEOMETRY из MS SQL) в PostgreSQL.

На рынке уже есть готовые решения, позволяющие передавать схему или данные из MS SQL в PostgreSQL:

• Babelfish от AWS

  • Устанавливается как плагин на PostgreSQL и делает его совместимым с T-SQL синтаксисом, заставляя клиентское приложение думать, что оно работает с MS SQL.

  • В то же время, данное решение рассматривалось лишь как средство для быстрого перехода на PostgreSQL, которое имеет некоторые ограничения в совместимости с СУБД.

  • Babelfish пока что не поддерживает пространственные типы данных GEOMETRY и GEOGRAPHY.

• AWS Schema Conversion tool

  • Утилита генерации SQL-скриптов со схемой БД для заданной СУБД на основе другой СУБД.

  • Мигрирует данные через специальные data extraction agents.

  • Миграция данных рассчитана на облако AWS.

• Некоторое количество вариаций утилит по, непосредственно, импорту-экспорту пространственных данных. Например, ogr2ogr.

  • Утилита специализируется исключительно на пространственных данных и пришлось бы разделить процессы выгрузки их и других типов данных, что усложнило бы разработку.

  • Часть процесса выглядела бы так: MS SQL → файл с геоданными → PostgreSQL.

  • Довольно тернистый путь, как показалось.

Рассмотрев различные варианты утилит и инструментов по миграции схем и данных, взвесив их плюсы и минусы, все же, решили обратиться к технологии, которую уже использовали в проекте, Entity Framework, и написать свой велосипед мигратор.

О проекте Geography

Данный микросервис был написан еще в 2016 году, основывается на .NET, использует MS SQL для хранения данных и Entity Framework в качестве ORM.

Некоторое время назад было принято решение адаптировать данный проект под требования Реестра отечественного ПО, в том числе — обновить версии .NET, EF Core и, наконец, перевести хранение данных в PostgreSQL.

Современные версии .NET предлагают не только свободную лицензию MIT, но также и существенный рост производительности (на эту тему есть целый цикл статей) вместе с новыми возможностями для разработки. В то же время, PostgreSQL предлагает версионность данных «из коробки», которая эффективно решает проблему блокировок уровня манипуляций с данными (DML, при условии штатного функционирования сервера), бесплатное High Availability, вплоть до кластера, не говоря уже о read only и логических репликах. В добавок, получаем полную совместимость со стеком Linux и развитое сообщество. Да и кто же не любит новые технологии? Таков путь.

Стоит отметить, что и Entity Framework Core уже достаточно зрелая ORM-технология.

Составные части решения на момент начала перехода к PosgtreSQL:

• Api — ASP .NET web API

• Api.Client — Клиент API

• Business Logic Services — Слой бизнес-логики

• EF.Sql — EF Core контекст БД, миграции схемы и данных БД, репозитории

• Migration utility — консольная утилита для миграций схемы и данных. Используется для CI/CD.

• Проекты с тестами API, сервисов, интеграционными тестами

Технологический стек

• .NET Core 3.1

• MS SQL Server 2016

• Entity Framework Core 5

  • Все сущности конфигурируются через Data Annotations.

• ASP .NET Core 3.1

• Autofac в качестве DI-контейнера

Рефакторинг проекта

Для начала, из проекта контекста БД было выделено несколько слоёв:

• Cущности EF в виде POCO-классов, без каких-либо ссылок на EF и без аннотаций/атрибутов, описывающих ограничения, ключи и связи между сущностями.

• Интерфейсы

  • Для контекста EF.

  • Для конфигурирования контекста EF.

  • Для чтения/записи в БД, UnitOfWork — часть унифицированного подхода компании к работе с Data Access Layer

    Интерфейсы чтения/записи в БД. Фрагмент библиотеки Fortis.Common.DAL

    // Copyright © 2022 ООО "Фортис", ГК "Монополия" namespace Fortis.Common.DataAccessLayer.Contracts;  /// <summary> /// Предоставляет запрос на получение записей из хранилища /// Абстрагирован от конкретной СУБД и ORM /// </summary> public interface IDbReader { /// <summary> /// Предоставляет запрос на получение записей из хранилища /// </summary> IQueryable<TEntity> Read<TEntity>() where TEntity : class, IDbEntity; }  /// <summary> /// Интерфейс создания и модификации записей в хранилище /// Абстрагирован от конкретной СУБД и ORM /// </summary> public interface IDbWriter { /// <summary>Добавить новую запись</summary> void Add<TEntity>(TEntity entity) where TEntity : class, IDbEntity;  /// <summary>Изменить запись</summary> void Update<TEntity>(TEntity entity) where TEntity : class, IDbEntity;  /// <summary>Удалить запись</summary> void Delete<TEntity>(TEntity entity) where TEntity : class, IDbEntity; }  /// <summary>Сохранение изменений контекста</summary> public interface IDbUnitOfWork { /// <summary>Асинхронно сохраняет все изменения контекста в Db</summary> /// <returns>Число записей о состоянии, записанных в базу данных</returns> Task<int> SaveChangesAsync(CancellationToken cancellationToken);  /// <summary>Создает новую транзакцию</summary> IDbTransaction InTransaction(); }

    Реализация интерфейсов

    // Copyright © 2022 ООО "Фортис", ГК "Монополия" using System.Linq;
 using Fortis.Common.DataAccessLayer.Contracts;
 using Fortis.GeographyEF.Abstract;
 using Microsoft.EntityFrameworkCore;  namespace Fortis.GeographyEF;  partial class GeographyDbContext : IMsSqlGeographyDbReader, IMsSqlGeographyDbWriter, IDbUnitOfWork { void IDbWriter.Add<TEntity>(TEntity entity) => Entry(entity).State = EntityState.Added;  void IDbWriter.Update<TEntity>(TEntity entity) => base.Entry(entity).State = EntityState.Modified;  void IDbWriter.Delete<TEntity>(TEntity entity) => base.Entry(entity).State = EntityState.Deleted;  IQueryable<TEntity> IDbReader.Read<TEntity>() => base.Set<TEntity>() .AsNoTracking() .AsQueryable();  Task<int> IDbUnitOfWork.SaveChangesWithoutAuditAsync(CancellationToken cancellationToken) => SaveChangesAsync(cancellationToken);  Task<int> IDbUnitOfWork.SaveChangesAsync(CancellationToken cancellationToken) => SaveChangesWithDetachAsync(cancellationToken);  IDbUnitOfWork.IDbTransaction InTransaction() { var internalTransaction = Database.BeginTransaction(); return new GeographyDbTransaction(internalTransaction); }  /// <summary> /// Метод сохраняет изменения с Detach модифицированных сущностей /// </summary> internal async Task<int> SaveChangesWithDetachAsync(CancellationToken cancellationToken) { var count = await base.SaveChangesAsync(cancellationToken); foreach (var entry in base.ChangeTracker.Entries().ToArray()) { entry.State = EntityState.Detached; }  return count; } }

• Сам DbContext для MS SQL, реализующий описанные выше интерфейсы, а также его миграции и Autofac-модуль, регистрирующий контекст в DI контейнере и подключаемый в web API приложении.

• Конфигурация контекста для MS SQL на основе Fluent API Configuration

  • Для каждой из таблиц написан отдельный класс конфигурации, реализующий интерфейс IEntityTypeConfiguration<TEnity>.

    Пример конфигурирования сущности

    // Copyright © 2022 ООО "Фортис", ГК "Монополия" namespace Fortis.GeographyEF.Entities.Configurations.MsSql; public class SettlementConfiguration : IEntityTypeConfiguration<Settlement> {     public void Configure(EntityTypeBuilder<Settlement> builder)     {     builder.ToTable("Settlements");         builder.HasKey(p =&gt; p.Id);         builder.Property(p =&gt; p.Id).ValueGeneratedOnAdd();                   builder.Property(o =&gt; o.Name)             .IsRequired()             .HasMaxLength(100)             .HasColumnType("NVARCHAR(100)");         builder.Property(o =&gt; o.Abbreviation)             .IsRequired()             .HasMaxLength(15)             .HasColumnType("NVARCHAR(15)");              builder.HasOne(p =&gt; p.Region)             .WithMany(p =&gt; p.Settlements)             .HasForeignKey(p =&gt; p.RegionId)             .OnDelete(DeleteBehavior.Cascade);     } }

  • Подключение такого рода конфигураций довольно просто осуществляется в контексте БД.

    Пример подключения конфигураций сущностей

    // Copyright © 2022 ООО "Фортис", ГК "Монополия" namespace Fortis.GeographyEF; internal sealed class GeographyDbContext: DbContext, IGeographyDbContext {     //...     protected override void OnModelCreating(ModelBuilder builder)     {         base.OnModelCreating(builder);              builder.ApplyConfigurationsFromAssembly(typeof(SettlementConfiguration).Assembly);     } }

• Слой репозиториев, зависящих не от конкретного контекста, а от абстракции (интерфейса).

Таким образом, получилась более красивая и расширяемая организация слоя данных приложения и открывшая путь к абстрагированию от реализации Entity Framework под конкретную СУБД.

Составные части решения после рефакторинга

Подключаем PostgreSQL

На данном этапе в приложение было добавлено еще два проекта, подобные реализованным ранее для MS SQL:

• DbContext для PostgreSQL, миграции схемы и Autofac-модуль.

  • Инициирующую миграцию создали тут же.

• Конфигурация контекста PostgreSQL на основе Fluent API Configuration.

Составные части решения после подключения PostgreSQL

Также, для web api приложения, в рамках Startup-класса, было добавлено чтение новой переменной конфигурации из IConfiguration, в зависимости от значения которой подключается нужный нам Autofac-модуль контекста БД. 

Т.е. то, какая СУБД будет использована, определяется на уровне конфигурации самого исполняемого приложения. 

Не обошлось и без дополнительной настройки самой PostgreSQL — было необходимо установить расширение PostGIS, включающее поддержку пространственных типов данных в СУБД.

Миграция данных и стратегия переключение на PostgreSQL

Схема деплоя сервиса Geography использует консольное приложение-мигратор для миграции схемы БД. Было решено расширить его конфигурацию возможностью выбрать текущую активную СУБД (MS SQL или PostgreSQL), а также добавить поддержку аргумента командной строки для запуска процесса миграции данных.

Поскольку уже имелась созданная ранее инициирующая миграция для PostgreSQL, она была развёрнута на пустую базу и взята за основу для будущих деплоев.

Для передачи всех 150ГиБ данных (несколько таблиц по ~10млн записей, но основной объем — это значения в полях с пространственными типами данных), было решено пойти по пути обхода каждой таблицы из EF-контекста MS SQL и записи в контекст PostgreSQL.

С этим помог алгоритм топологической сортировки со стратегией поиска в глубину для обхода всех сущностей БД так, чтобы однократно пройтись по всем сущностям с учетом их взаимосвязей — сначала выгружаются все зависимости, а затем зависимые сущности.

// Copyright © 2022 ООО "Фортис", ГК "Монополия" using System; using System.Collections.Generic; using System.Reflection; using NetTopologySuite.Geometries;  namespace Fortis.Geography.Migrate.DbSync;  internal sealed class TopSortService : ITopSortService { private static readonly HashSet<Type> excludedTypes = new HashSet<Type> { typeof(string), // don't analyze string properties typeof(Geometry), // and Geometry types typeof(LineString), };  public IEnumerable<Type> Sort(IEnumerable<Type> typesList) { var graph = new Dictionary<Type, List<Type>>(); foreach (var vertex in typesList) { var edges = new List<Type>(); graph.Add(vertex, edges); foreach (var propertyInfo in vertex.GetProperties(BindingFlags.Public | BindingFlags.Instance)) { var edge = propertyInfo.PropertyType; if (!edge.IsValueType && // don't analyze primitive types !edge.IsGenericType && // and generic types !excludedTypes.Contains(edge)    ) { edges.Add(edge); } } }  var result = new List<Type>(); var visited = new HashSet<Type>(); foreach (var vertex in graph.Keys) { Dfs(vertex, graph, visited, result); }  return result; }  private static void Dfs( Type vertex, Dictionary<Type, List<Type>> graph, HashSet<Type> visited, List<Type> result) { if (!visited.Add(vertex)) { return; }  foreach (var edge in graph[vertex]) { Dfs(edge, graph, visited, result); }  result.Add(vertex); } }

Далее, был написан сам код мигратора.

// Copyright © 2022 ООО "Фортис", ГК "Монополия" using System; using System.Linq; using System.Reflection; using System.Threading; using System.Threading.Tasks; using Fortis.Common.DataAccessLayer.Entity.Contracts; using Fortis.Common.Logging.Contracts; using Fortis.GeographyEF.Abstract; using Fortis.GeographyEF.Common; using Fortis.GeographyEF.PostgreSql.Abstract; using Microsoft.EntityFrameworkCore;  namespace Fortis.Geography.Migrate.DbSync;  internal sealed class DbSynchronizationService : IDbSynchronizationService { private static readonly MethodInfo synchronizeEntityMethodInfo = typeof(DbSynchronizationService).GetMethod(nameof(SynchronizeEntities));  private readonly ITopSortService topSortService; private readonly IMsSqlGeographyDbReader msReader; private readonly IGeographyEntitiesResolver entitiesResolver; private readonly IPgGeographyDbWriter pgWriter; private readonly IPgGeographyDbUnitOfWork pgUnitOfWork; private readonly IFortisLogger logger;  public DbSynchronizationService( ITopSortService topSortService, IMsSqlGeographyDbReader msReader, IGeographyEntitiesResolver entitiesResolver, IPgGeographyDbWriter pgWriter, IPgGeographyDbUnitOfWork pgUnitOfWork, IFortisLogManager logManager) { logger = logManager.GetLogger<DbSynchronizationService>();  this.topSortService = topSortService; this.msReader = msReader; this.entitiesResolver = entitiesResolver; this.pgWriter = pgWriter; this.pgUnitOfWork = pgUnitOfWork; }  async Task IDbSynchronizationService.Synchronize(CancellationToken cancellationToken) { var dbTypes = entitiesResolver.GetClrTypes(); var sortedDbTypes = topSortService.Sort(dbTypes); foreach (var dbType in sortedDbTypes) { await (Task)synchronizeEntityMethodInfo .MakeGenericMethod(dbType) .Invoke(this, new object[] {cancellationToken}); } }  // don't change modifier, because we use reflection public async Task SynchronizeEntities<T>(CancellationToken token) where T : class, IDbEntity, ICreatedAtEntity, IEntityWithId { const int batchSize = 4024;  var baseMsQuery = msReader.Read<T>();  var recordsTotal = await baseMsQuery.CountAsync(token); var recordsProcessed = 0;  logger.Info($"Synchronizing {typeof(T).Name}. Total records: {recordsTotal}");  try { while (recordsProcessed < recordsTotal) { var srcList = await baseMsQuery .Skip(recordsProcessed) .Take(batchSize) .ToListAsync(token);  foreach (var entity in srcList) { pgWriter.Add(entity); }  await pgUnitOfWork.SaveChangesAsync(token);  recordsProcessed += srcList.Count; } } catch (Exception e) { logger.Error($"{typeof(T).Name} synchronization failed. Processed records: {recordsProcessed}.", e); throw; }  logger.Info($"{typeof(T).Name} has been synchronized."); } }

В класс мигратора инжектируются не непосредственно контексты БД, но абстракции над ними (для чтения, операций записи и UnitOfWork). Стоит и уточнить назначение IGeographyEntitiesResolver — это интерфейс, предоставляющий список CLR типов, ассоциированных с контекстом.

// Copyright © 2022 ООО "Фортис", ГК "Монополия" public interface IGeographyEntitiesResolver { /// <summary> /// Вернуть все ассоциированные с контекстом БД CLR-типы /// </summary> IReadOnlyCollection<Type> GetClrTypes(); }  partial class PgGeographyContext : IGeographyEntitiesResolver { private static readonly Assembly entitiesAssembly = typeof(Location).Assembly;  IReadOnlyCollection<Type> IGeographyEntitiesResolver.GetClrTypes() => Model.GetEntityTypes() .Select(o => o.ClrType) .Where(o => o.Assembly == entitiesAssembly) .ToList(); }

Отмечу сразу, что данная реализация мигратора не является панацеей и может отличаться в зависимости от особенностей ваших БД. Мигратор из примера запускался для статической, отдельной, копии БД продуктивного окружения. 

Само собой, что с момента окончания первой миграции данных в PostgreSQL, БД продуктивного окружения гарантированно будет отличаться по количеству строк и необходимо реализовать механизм компенсации расхождений.

Задача облегчалась тем, что бизнес-логика сервиса Geography подразумевает только добавление новых данных по городам, точкам интереса и маршрутам. Изменение данных — только через отдельные EF-миграции данных.

Таким образом, было достаточно догрузить несколько тысяч недостающих записей на момент релиза с окончательным переходом к PostgreSQL — для этого в код мигратора были внесены изменения с поддержкой определения последней выгруженной строки для каждой таблицы и переноса только новых строк. В процессе деплоя был вызван мигратор и данные доактуализированы за небольшое время.

Да, были возможны и альтернативные варианты стратегий компенсации расхождений данных:

• Реализация одновременной записи в обе СУБД через репозитории.

• Отслеживание изменений на уровне триггеров в таблицах и уведомление об этом через реализацию Outbox-паттерна.

• и т.д.

Все варианты имеют свои плюсы и минусы и был выбран наиболее эффективный для нашей конкретной ситуации.

Итоги

Сервис Geography был успешно мигрирован на новую СУБД без потерь, сопутствующий рефакторинг улучшил структуру решения, проблем с производительностью запросов не наблюдается.

Поскольку подход миграции данных с использованием EF и топологической сортировкой показал свою состоятельность, планируется применять его, с некоторыми доработками, и в других проектах.

Надеюсь, что описанное выше поможет вам в переходе с MS SQL на PostgreSQL. Если остались вопросы, welcome в комментарии.