Поваренная книга миграции данных между БД или как перенести данные из одной БД в другую с минимальной болью

Одно я могу сказать точно: миграция данных между двумя БД - это одна из, если не самая сложная часть при смене СУБД или схемы базы данных. И что-то мне подсказывает, что Вы не фанат громоздких SQL конструкций.

Варианты основаны на использовании PostgreSQL и EF Core но сохраняют свою актуальность даже если на проекте нет ни строчки .Net кода или стоит другая СУБД.

Варианты без C# кода.

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

Вариант 1: Бэкап данных на источнике с восстановлением данных из бэкапа на БД получателе

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

Пример команды:

pg_dump.exe --file "D:\\Backups\\books" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs --section=data --table "dbo.books" --table "dbo.flowers" "SourceDatabase"

Восстановить бэкап можно через консоль с помощью pg_restore, либо через UI, используя такие инструменты как pgAdmin.

Минусы

1. Требует выгрузки на диск, что при использовании с серверами клиентов может создать проблем с допусками, пропускной способность и т.д. Но как самый примитивный вариант - он самый надёжный.

2. Нехватка гибкости.

– Некоторый уровень гибкости можно получить через аргументы pg_dump, но они не слишком помогают если например нужно сменить тип ID.

– Как альтернативу можно рассмотреть написание SQL скрипта который будет сохранять в CSV. Если таблица достаточно проста - может сработать.

Вариант 2: SQL скрипт с экспортом и импортом через CSV

Формат CSV используется для обмена данных в системах 50 лет(с 1972). И имеет все шансы пережить первого человека на Марсе. Так что вполне вероятно поддерживается вашей СУБД.

В PostgreSQL выгружать в CSV и выгружать из него можно через команду COPY.

COPY "dbo.books" TO '/usr1/proj/bray/sql/all_the_books.csv'  WITH DELIMITER ',' CSV HEADER;
COPY (SELECT * FROM "dbo.books") to '/usr1/proj/bray/sql/all_the_books.csv'  WITH DELIMITER ',' CSV HEADER;

Минусы

1. Требует выгрузки на диск, что при использовании с серверами клиентов может создать проблем с допусками, пропускной способность и т.д.

2. Нужно писать SQL. Со всеми вытекающими.

Вариант 3: SQL скрипт с dblink

Команда dblink в PostgreSQL позволяет выполнить запрос в удалённой БД. Она позволяет переносить данные между двумя бд без промежуточных файлов. В сути своей - это вариант с классической связкой INSERT SELECT, но SELECT выполняется на другой БД.

Минусы

1. Да, это гибко, но растёт сложность SQL скрипта, а с ней и возможность выстрелить в себе ногу.

2. На БД клиентов сбоила при переносе БД более 15 ГБ за раз. Что б исправить эту проблему можно переносить данные через dblink по частям.

Варианты с использованием C# кода

Эти подходы требуют наличия контекста БД и для исходной БД и для БД получателя Простейший способ создания таковых в пункте "Создание контекста БД на основе существующей БД"

Вариант 1. dblink но с генерацией через код

Для использования команды требуется установить расширение через вызов SQL либо C# код который вызывает SQL.

Пример:

_destination.Database.ExecuteSqlRaw($"CREATE EXTENSION dblink");

Пример функции которая на основе существующего DbContext позволяет сформировать строку SQL запроса на перенос всех данных, либо с фильтром по полю yearofpublication, дополнительно отсортировав по этому полю.

Она принимает тип из контекста и сам контекст содержащий данный тип. Написана в рассчёте на то что данная таблица в исходной БД и получателе идентична по схеме.

 public string GetEntityMigrationString(Type type, DbContext context, int? year = null)
 {
     var tableName = GetTableName(context, type);

     var propertiesDictionary = GetTypeProperties(context, type);
     var propertiesString = string.Join(", ", propertiesDictionary.Select(p => p.Key));
     var propertiesWithTypesString = string.Join(", ", propertiesDictionary.Select(p => p.Value));

     var sqlString =
         $"INSERT INTO {tableName}({propertiesString}) SELECT {propertiesString} FROM dblink('{_sourceString}', 'SELECT {propertiesString} from {tableName}";
     if (year != null)
     {
         sqlString +=
             $" where yearofpublication >= {year} and yearofpublication <= {year} ORDER BY yearofpublication ASC";
     }

     sqlString +=
         $"') AS x({propertiesWithTypesString})";

     return sqlString;
 }

 private static string GetTableName(DbContext context, Type type)
 {
     var entityType = context.Model.FindEntityType(type);
     var tableSchema = entityType.GetSchema();

     var tableName = tableSchema == null
         ? entityType.GetTableName()
         : tableSchema + '.' + entityType.GetTableName();
     return tableName;
 }

 private static Dictionary<string, string> GetTypeProperties(DbContext context, Type type)
 {
     var entityType = context.Model.FindEntityType(type);

     var propertiesDictionary = new Dictionary<string, string>();
     foreach (var property in entityType.GetProperties())
     {
         var propertyColumnName = property.GetColumnName();
         var propertyColumnNamePlusType = property.GetColumnName() + " " + property.GetColumnType();
         propertiesDictionary.Add(propertyColumnName, propertyColumnNamePlusType);
     }

     return propertiesDictionary;
 }

Эту строку можно вызвать в БД через ExecuteSqlRaw.

 public void MigrateTable(Type type, DbContext referenceContext)
 {
     var tableName = GetTableName(referenceContext, type);
     var dataMigrationString = GetEntityMigrationString(type, referenceContext);
     Console.WriteLine($"Started importing {tableName} at {DateTime.Now:h:mm:ss tt}");
     _destination.Database.ExecuteSqlRaw(dataMigrationString);
     Console.WriteLine($"Imported {tableName} at {DateTime.Now:h:mm:ss tt}");
 }

Минусы

1. Это гибридный подход и всё ещё нужен SQL.  
   Контекст БД содержит данные по схеме для классов. C# позволяет использовать это и засыпать синтаксическим сахаром и типизацией сверху. Например можно передавать поля классов как аргументы в функции и вытягивать их схему из контекста. Эта особенность и ряд других позволяют упростить написание, но далеко не всегда решить проблему с использованием SQL.

2. На БД клиентов сбоила при переносе БД более 15 гб за раз. Что б исправить эту проблему можно переносить данные через dblink по частям.

Референсы: Официальная документация PostgreSQL по dblink

Вариант 2. Перенос средствами EF Core

Этот подход максимально опирается на EF Core.

Пример переноса небольшой таблицы Books с изменением типа Id.

    public static class BooksMigration
    {
        public static async Task<Dictionary<int, Guid>> MigrateBooks(ContextsOptions contextsOptions)
        {
            var changedIds = new Dictionary<int, Guid>();

            await using (var destinationContext = new DestinationContext(contextsOptions.DestinationDatabaseContextOptions))
            {
                await using (var legacyContext = new SourceContext(contextsOptions.SourceDatabaseContextOptions))
                {
                    legacyContext.Books.AsNoTracking().ToList().ForEach(sourceBook =>
                    {
                        if (legacyContext == null || destinationContext == null)
                        {
                            throw new ArgumentNullException();
                        }

                        var destinationBook = InsertBookToDestination(destinationContext, sourceBook);
                        Console.WriteLine($"{sourceBook.Globalname} transferred, new id: {destinationBook.Id}(was {sourceBook.Id})");

                        changedIds.Add(sourceBook.Id, destinationBook.Id);
                    });
                }
                await destinationContext.SaveChangesAsync();
            }
            return changedIds;
        }

        private static Book InsertBookToDestination(DestinationContext destinationContext, Book sourceBook)
        {
            return destinationContext.Books.Add(new Book
            {
                Name = sourceBook.Name,
                AuthorId = sourceBook.AuthorId,
                Code = sourceBook.Code
            }).Entity;
        }

Используемый в коде ContextsOptions

    public class ContextsOptions
    {
        public DbContextOptions<SourceContext> SourceDatabaseContextOptions { get; }
        public DbContextOptions<DestinationContext> DestinationDatabaseContextOptions { get; }

        public ContextsOptions(DbContextOptions<SourceContext> sourceDatabaseContextOptions, DbContextOptions<DestinationContext> destinationDatabaseContextOptions)
        {
            SourceDatabaseContextOptions = sourceDatabaseContextOptions;
            DestinationDatabaseContextOptions = destinationDatabaseContextOptions;
        }
   
        public static ContextsOptions GetContextsOptions()
        {
            const string configPath = "appsettings.json";
            var config = new ConfigurationBuilder()
                .AddJsonFile(configPath)
                .Build();
            var sourceDatabase = config.GetConnectionString("SourceDatabase");
            var destinationDatabase = config.GetConnectionString("DestinationDatabase");

            var sourceDatabaseContextOptions = new DbContextOptionsBuilder<SourceContext>()
                .UseNpgsql(sourceDatabase)
                .Options;
            var destinationDatabaseContextOptions = new DbContextOptionsBuilder<DestinationContext>()
                .UseNpgsql(destinationDatabase)
                .Options;
            return new ContextsOptions(sourceDatabaseContextOptions, destinationDatabaseContextOptions);
        }
    }

Минусы

1. Этот метод загружает данные в память, что ресурсоёмко. Смягчается разделением блоки фиксированной величины с последовательной обработкой.

2. by design™ EF Core отслеживает изменения в моделях, что б упростить сохранение изменений в БД. Это уменьшает производительность чтения если нет намерения изменить загруженные данные.
   Решается использованием AsNoTracking() и его альтернативой AsNoTrackingWithIdentityResolution(), доступной с EF Core 5+.

3. EF Core не спроектирован для вставки больших объёмов записей в БД за запрос. Если требуется перенести большую таблицу то нужно найти способ делать bulk insert. Bulk insert функциональность не включена в EF Core и даёт разительное увеличение скорости переноса данных.
   - Можно использовать Bulk Insert из платного Entity Framework Extensions (есть пробный период)
   - Вот несколько вариантов для Npgsql провайдера используемого для работы с PostgreSQL. Бесплатно.

Нерабочие варианты

Вариант 1: Insert + Select

Так как это две разных БД, то простой insert + select не работает.

INSERT INTO TDestination (Id, Name)
SELECT Id, Name FROM TOrigin
WHERE Name ='John';

Вариант 2: Простой перенос средствами EF Core

EF Core не рассчитан на вставку большого количества записей за раз by design™. Такой запрос будет по меньшей мере не эффективен. Но для небольших таблиц пойдёт.

public void StraightforwardMigration(SourceContext sourceContext, DestinationContext destination)
{
    destination.AddRange(sourceContext.Files);
}

Создание контекста БД на основе существующей БД

Этот пункт нужен если у вас нет контекста БД для EF Core. Для этой задачи отлично подходит Scaffold. Это функция EF Core позволяет создать контекст на основе существующей БД.
Основные шаги для уже существующего solution:

1. Создайте проект библиотеки классов в вашем Solution

2. Добавьте его в зависимости на него в вашем Startup проекте

3. Добавьте в него пакет Microsoft.EntityFrameworkCore.Design

4. Создайте контекст через вызов Scaffold команды.

dotnet ef dbcontext scaffold "Host=localhost;Port=5432;Database=SourceDatabase;Username=Superuser;Password=Superuser" Npgsql.EntityFrameworkCore.PostgreSQL -Context SourceScaffoldContext -OutputDir SourceScaffold

Visual studio

Scaffold-DbContext "Host=localhost;Port=5432;Database=SourceDatabase;Username=Superuser;Password=Superuser" Npgsql.EntityFrameworkCore.PostgreSQL -Context SourceScaffoldContext -OutputDir SourceScaffold

В данном примере используется PostgreSQL, но нет никакой проблемы использовать SqlServer, Cosmos, MySql или другую БД на которую существует EF Core провайдер. Список провайдеров

Наблюдения

1. Созданный Scaffold контекст организован не очень эффективно, читабельно, чисто, но для одноразового решения замечательно подходит. Если вы мигрируете на EF Core - может стать неплохой отправной точкой.

2. В PostgreSQL валидация по внешним ключам отключается через ALTER TABLE {table} DISABLE TRIGGER ALL но, это работает не для всех клиентов. Так что по возможности лучше обойтись без выключения этой валидации.

Выводы

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

В процессе поиска этих вариантов я пробовал и другие. Например написание SQL скриптов, я отказался от него в пользу переноса данных и генерации SQL скриптов с помощью EF Core, в силу того в какую проблему превращалось поддержание и проверка SQL скриптов.