Проблема

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