Одно я могу сказать точно: миграция данных между двумя БД - это одна из, если не самая сложная часть при смене СУБД или схемы базы данных. И что-то мне подсказывает, что Вы не фанат громоздких 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 или FDW
Dblink и FDW в PostgreSQL позволяют выполнить запрос в удалённой БД. В сути своей - это вариант с классической связкой INSERT SELECT, но SELECT выполняется на другой БД.
Сравнение FDW и dblink
FDW более производителен
На FDW можно сделать read only доступ для выгрузки данных. Но у него есть
updatableопцияdblink доступен на более ранних версиях PostgreSQL
Это делает FDW более выигрышной альтернативой dblink для не слишком старых версий PostgreSQL
Минусы:
Да, это гибко, но растёт сложность SQL скрипта, а с ней и возможность выстрелить в себе ногу.
На БД клиентов dblink сбоил при переносе БД более 15 ГБ за раз. Что б исправить эту проблему можно переносить данные через dblink по частям.
Варианты с использованием C# кода
Эти подходы требуют наличия контекста БД и для исходной БД и для БД получателя Простейший способ создания таковых в пункте "Создание контекста БД на основе существующей БД"
Вариант 1. dblink или FDW но с генерацией через код
В сути своей то же самое что и вариант c SQL скриптом, но добавление генерации скрипта через C# снижает сложность поддержки. Так что этот вариант является логическим продолжением варианта выше.
Контекст БД EF Core содержит данные по схеме для классов представленных в БД, что является отличным подспорьем для генерации SQL скриптов. Например можно передавать поля классов как аргументы в функции и вытягивать их схему из контекста.
Пример с 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.
На БД клиентов сбоила при переносе БД более 15 гб за раз. Что б исправить эту проблему можно переносить данные через dblink по частям.
Референсы: Официальная документация PostgreSQL по dblink
Вариант 2. Перенос средствами EF Core
Этот подход максимально опирается на EF Core. И является одним из самых надёжных за счёт возможности написать код который не будет сильно спотыкаться об особенности БД разных клиентов.
EF Core не спроектирован для вставки больших объёмов записей в БД за запрос. Валидным этот вариант для больших БД делает возможность совместить переносить БД блоками по N элементов и написать/установить Bulk insert. так как эта функциональность не включена в EF Core и может дать огромное увеличение скорости переноса данных.
- Можно использовать Bulk Insert из платного Entity Framework Extensions (есть пробный период)
- Вот несколько вариантов для Npgsql провайдера используемого для работы с PostgreSQL. Бесплатно.
Пример переноса небольшой таблицы 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);
}
}
Минусы
Если требуется перенести большую таблицу то нужно найти способ делать bulk insert.
Этот метод загружает данные в память, что ресурсоёмко. Смягчается разделением блоки фиксированной величины с последовательной обработкой.
by design™ EF Core отслеживает изменения в моделях, что б упростить сохранение изменений в БД. Это уменьшает производительность чтения если нет намерения изменить загруженные данные.
Решается использованием AsNoTracking() и его альтернативой AsNoTrackingWithIdentityResolution(), доступной с EF Core 5+.
Нерабочие варианты
Вариант 1: Insert + Select
Так как это две разных БД, то простой insert + select не работает. Но если в этот вариант добавить dblink и PWD - это может сработать. Выше об этих вариантах.
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 скриптов.
Частные случаи
C PostgreSQL на более новую версию
Команда pg_upgrade позволяет обновить PostgreSQL. Аргумент -k позволяет не копировать данные.
Из Entity Framework на Entity Framework Core
Специфики в этот вариант добавляет то что при идентичных, в плане именования, конфигах Entity Framework и EF Core именование колонок имеет тенденцию отличаться. Что уж говорить если именование колонок таблиц в разных версиях EF Core может отличаться.
Также EF и EF Core используют разные типы полей в БД при схожей конфигурации.
upd. Внёс поправки в статью в соответствии с комментариями