Как перенести 1,4 ТБ с MS SQL на PostgresSQL за 13 часов

[nikolz]

И в целом бинарный формат при переносе данных оказался более эффективным по сравнению с текстовым,

Прикольно, но всегда считал это аксиомой. Что не так?

[pabel0071]

Преимущества бинарных форматов становятся очевидными, когда речь идет о больших объемах данных и высоких требованиях к производительности.

[dude_sam]

А чем православный SSIS не угодил то?

[maksim_sitnikov]

На нем, имхо, вообще должны дата/системные аналитики уметь работать, и такие штуки самостоятельно делать при необходимости. Тем более тут логик в селектах нет чтоб разраба привлекать.

[Turbcool]

Пробовали у себя через SSIS реализовать перенос. В нём для каждой таблицы, для каждого столбца надо по десятку кликов сделать. В нашем случае почти сотню таблиц надо было перенести и мы отказались просто из-за неудобства и неочевидности простых вещей. Многие жалуются на перегруженность интерфейса, и это не просто так, уж проще импорт в другом инструменте сделать. Мы решили проблему утилитой sqlserver2pgsql - рекомендую, очень удобная и рабочая штука.

[dude_sam]

Ну, в контексте того, что SSIS де-факто закопан в пользу ADF, то чисто в качестве ремарки: SSIS не про накликиваете мышкой, но начиная с того, что T-SQL и сами таски в пакете можно генерить из мета информации о таблицах и, заканчивая о возможности впихнуть кастомный C# в процесс обработки.

[mentin]

Тоже вспомнил SSIS, лет 15 назад Microsoft публиковали статью как они им терабайт CSV за полчаса в MSSQL загрузили.

[JBird]

Говнище он, как по мне. Куча ошибок в визарде уже два десятка лет живет и никем не фиксится.

[AgentFire]

Звездочка в селекте не гарантирует какой-то там порядок. Лучше всегда всё явно прописывать, что требуется от сервера.

[trepix]

А что насчёт copy from to у psql?

Не знаю как быстро работает выгрузка в csv в mssql, но вот копирование из csv в postgresql достаточно быстро

[pabel0071]

Перенос csv на сервак из за его размера занимал много времени и нужно что бы еще было столько места дополнительно.

[VADemon]

На ум приходят варианты: сразу писать по сети CSV на подключенный диск сервера или одновременно скриптом копировать уже записанную часть файла (но тут нужно +1x к месту)?

[ptr128]

1. TDS_FDW работает неплохо, но есть проблемы с лишней цифрой в datetime2, которую надо отрезать.

2. Если установить MS SQL клиент на Linux, то весьма бодро данные переливаются COPY ... FROM PROGRAM 'bcp ...' Но проблема с datetime2 там тоже есть.

ODBC к PostgreSQL жутко медленные. Так что это сразу не вариант.

[vmalyutin]

Из-за того что объем файла получался очень большой, перенос csv на сервер занимал значительное время.

Я все же не понял. 1,4 это одна таблица или вся БД?

Я таскал из greenplum в ванильную версию ручками. Там выполнял copy to, а на целевом сервере copy from. При перетаскивании файлов пользовался сжатием.

Вообще говоря copy to умеет в zip писать. Удобненько было. И быстро.

Но это все через диск. И получается в вашем случае потребовалось бы дополнительные 1,4

Я вот чего не понял. Вы открываете датасет из ms sql. Как вам памяти хватило на 1,4?

А разве bcp не умеет форматом даты управлять?

У pg нет команды bulk insert.

[pabel0071]

Я все же не понял. 1,4 это одна таблица или вся БД?

Всей БД.

Я вот чего не понял. Вы открываете датасет из ms sql. Как вам памяти хватило на 1,4?

При использовании `SqlDataReader` мы не загружаем весь обьем данных в оперативную память сразу.

А разве bcp не умеет форматом даты управлять?

Думаю можно, но на тот момент уже поняли, что из за размера csv и его переноса этот вариант нам не подойдет.

У pg нет команды bulk insert.

По сути ее аналог

COPY {tableNameLower} FROM STDIN (FORMAT BINARY)

[egribanov]

А чего все на пстгрес переезжают? Чем старая бд не устраивала?

[vanxant]

Санкциями

[4chemist]

Читаю эти ваши WIN-WIN решения о переносе за "30 миллисекунд" и меня удивляет, что в софте работающем с большой БД не предусмотрен вариант работы сразу с двумя независимыми БД, если в мастере нету данных, то лезем в слейв и переносим в мастер. БД сама перельется в новый мастер, а остатки данных перелить ручками. Такой функционал можно реализовать хранимыми процедурами? Если нет на мастере данных процедура лезет за данными в слейв, переносит их себе, и возвращает ответ на запрос?

[leotsarev]

Есть вот такая штука

https://pgloader.readthedocs.io/

[pabel0071]

Скорость миграции медленная при больших объёмах данных.

При миграции дополнительно понадобиться сопоставление данных между MSSQL и PostgreSq.

[vlmonk]

А распараллелить? Ну хотя-бы банально, не по 1 таблице за раз, а одновременно N таблиц тащить?

[pabel0071]

Перелив данных идет одновременно во все таблицы.

[quickhabr]

Делал похожую задачу, только надо непрерывно перекачивать из mysql в pg порядка 1 млн строк за 20 мин круглые сутки. Реализация на python, используется copy через pipe. Таблицы для приема данных unlogged с использованием партиционирования. Иногда упирался в nvme диски на стороне PG, но это решаемая задача. С производительностью python проблем небыло. Такое решение позволяло так же на лету преобразовывать некоторые таблицы и загружать в нужном виде. Видимо надо тоже написать как нибудь об этом статью, возможно кому то поможет, т.к. применимо для непрерывного трансфера данных между любыми базами.