Одно я могу сказать точно: миграция данных между двумя БД - это одна из, если не самая сложная часть при смене СУБД или схемы базы данных. И что-то мне подсказывает, что Вы не фанат громоздких 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.
Минусы
-
Требует выгрузки на диск, что при использовании с серверами клиентов может создать проблем с допусками, пропускной способность и т.д. Но как самый примитивный вариант - он самый надёжный.
-
Нехватка гибкости.
– Некоторый уровень гибкости можно получить через аргументы 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;
Минусы
-
Требует выгрузки на диск, что при использовании с серверами клиентов может создать проблем с допусками, пропускной способность и т.д.
-
Нужно писать SQL. Со всеми вытекающими.
Вариант 3: SQL скрипт с dblink
Команда dblink в PostgreSQL позволяет выполнить запрос в удалённой БД. Она позволяет переносить данные между двумя бд без промежуточных файлов. В сути своей - это вариант с классической связкой INSERT SELECT, но SELECT выполняется на другой БД.
Минусы
-
Да, это гибко, но растёт сложность SQL скрипта, а с ней и возможность выстрелить в себе ногу.
-
На БД клиентов сбоила при переносе БД более 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}");
}
Минусы
-
Это гибридный подход и всё ещё нужен SQL.
Контекст БД содержит данные по схеме для классов. C# позволяет использовать это и засыпать синтаксическим сахаром и типизацией сверху. Например можно передавать поля классов как аргументы в функции и вытягивать их схему из контекста. Эта особенность и ряд других позволяют упростить написание, но далеко не всегда решить проблему с использованием SQL. -
На БД клиентов сбоила при переносе БД более 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);
}
}
Минусы
-
Этот метод загружает данные в память, что ресурсоёмко. Смягчается разделением блоки фиксированной величины с последовательной обработкой.
-
by design™ EF Core отслеживает изменения в моделях, что б упростить сохранение изменений в БД. Это уменьшает производительность чтения если нет намерения изменить загруженные данные.
Решается использованием AsNoTracking() и его альтернативой AsNoTrackingWithIdentityResolution(), доступной с EF Core 5+. -
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:
-
Создайте проект библиотеки классов в вашем Solution
-
Добавьте его в зависимости на него в вашем Startup проекте
-
Добавьте в него пакет Microsoft.EntityFrameworkCore.Design
-
Создайте контекст через вызов 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 провайдер. Список провайдеров
Наблюдения
-
Созданный Scaffold контекст организован не очень эффективно, читабельно, чисто, но для одноразового решения замечательно подходит. Если вы мигрируете на EF Core - может стать неплохой отправной точкой.
-
В PostgreSQL валидация по внешним ключам отключается через
ALTER TABLE {table} DISABLE TRIGGER ALLно, это работает не для всех клиентов. Так что по возможности лучше обойтись без выключения этой валидации.
Выводы
Как я и говорил в начале материала - перенос данных между двумя БД в рамках миграции, далеко не самая лёгкая задача. Для миграции на своём проекте я сочетал варианты, что б использовать лучшее от каждого.
В процессе поиска этих вариантов я пробовал и другие. Например написание SQL скриптов, я отказался от него в пользу переноса данных и генерации SQL скриптов с помощью EF Core, в силу того в какую проблему превращалось поддержание и проверка SQL скриптов.
Автор:
Depact
