Проблема

Не секрет, что тема перехода в 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 типов, ассоциированных с контекстом.

Интерфейс IGeographyEntitiesResolver и его реализация
// 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 в комментарии.