Перенос базы 1С с PostgreSQL на MS SQL: смещение дат, индексы и приёмка
Иван Недомолков · Опубликовано:
Перенос базы 1С с PostgreSQL на MS SQL Server штатно делается командой ibcmd infobase replicate, она есть в платформе с 8.3.23. До запуска у человека три вопроса: какое смещение дат ставить приёмнику, создадутся ли индексы и как потом убедиться, что данные доехали целыми.
Числа сняты на трёх базах и подписаны. Стенд - тестовая бухгалтерская база, PostgreSQL 17.9 на Windows, приёмник MS SQL Server 2022, платформа 8.3.27, 22 ГБ и 11 396 таблиц. УПП - база с боевыми данными на 8.3.20. УТ 11.5 - типовая на 8.3.27. Базы разного размера и возраста, их числа между собой не складываем.
Какое смещение дат ставить при переносе на MS SQL
Ставьте 2000. Это значение --target-date-offset, которое ibcmd для MS SQL подставляет сам, если его не трогать.
Минимальная дата платформы 01.01.0001, она же пустая дата. При смещении 0 платформа кладёт всё, что раньше 1753 года, в 01.01.1753. При смещении 2000 год 1 становится годом 2001 и хранится без потерь. На стенде колонки дат в приёмнике были объявлены как datetime2, который год 1 держит спокойно, так что режет именно платформа.
Первый прогон стенда мы запустили с нулём: источник на PostgreSQL живёт со смещением 0, пересчитывать даты не придётся. Для надёжности посмотрели две работающие базы MS SQL с тем же нулём. Обе оказались пустыми и доказали только то, что платформа такое смещение принимает.
Прогон вернул код 0 и “Репликация информационной базы успешно завершена”. Весь stderr занял одну строку на 208 байт:
[WARN] Таблица '_InfoRgXXXX' содержит значения типа Дата, которые не могут быть
записаны в MS SQL Server с нулевым смещением датЭто же сообщение платформа показывает окном, когда в базу с нулевым смещением пишут дату раньше 1753 года. Шаги для обоих случаев собраны на странице ошибки о нулевом смещении дат.
| В PostgreSQL | В MS SQL при смещении 0 |
|---|---|
0001-01-01 00:00:00 | 1753-01-01 00:00:00 |
0001-01-02 00:00:00 | 1753-01-01 00:00:00 |
Пустая дата и соседняя слились в одно значение, и ЗначениеЗаполнено() на ней вернёт истину. Задета одна таблица из 11 396, в ней шесть строк, и после переноса их по-прежнему шесть. Повторный прогон со смещением 2000 дал пустой stderr, а диапазон дат той же таблицы на уровне СУБД сдвинулся ровно на 2000 лет: 0001-01-01 .. 2026-07-20 стало 2001-01-01 .. 4026-07-20.
Как узнать смещение текущей базы
- В СУБД. Смещение лежит в служебной таблице
_YearOffset. У источника стенда на PostgreSQL она пустая, это ноль. У приёмника после переноса там 2000. - Средствами платформы, если база на MS SQL. Поищите даты раньше 1753 года. Нашлись - смещение у базы ненулевое: при нуле таким значениям там негде лежать.
- В кластере. Смещение указывается и при регистрации базы в кластере. Приёмник регистрируйте с тем же значением, что стоит у самой базы.
Сколько дат испортит смещение 0
Граница равна 1753 минус смещение, при смещении от 1752 проверять нечего. Прогон проверки при нуле:
| База | Что нашлось |
|---|---|
| Стенд | 1 таблица, 6 строк |
| УТ 11.5 | 41 675 значений в 119 реквизитах, 1 480 таблиц за 10 секунд. Первым идёт производственный календарь, 8 893 значения |
| УПП | 3 502 101 значение в 30 реквизитах, и это оценка снизу: прогон ограничили бюджетом в 9 секунд, он прошёл 270 таблиц из 4 493. Крупнее всех три реквизита регистра расчёта, от 412 до 978 тысяч пустых дат |
В регистрах себестоимости и производственном календаре пустая дата штатная, так что ноль ломает и чистую типовую базу.
Где обход обычно промахивается:
- даты документов и периоды регистров лежат в полях без метаданных, их узнают по имени хранения
_Date_Timeи_Period; - дата составного реквизита живёт в колонке
_Tи достаётся черезВЫРАЗИТЬ(... КАК ДАТА); - из назначений таблиц нужны
Основная,ТабличнаяЧастьиКонстанта, запрос к оборотам падает с “Поле не найдено”.
Все даты таблицы проверяются за один проход:
// Граница = Дата(1753 - Смещение, 1, 1). Псевдоним источнику не даём,
// иначе он однажды совпадёт с именем реквизита.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ РАЗРЕШЕННЫЕ
| КОЛИЧЕСТВО(ВЫБОР КОГДА Дата < &Граница ТОГДА 1 КОНЕЦ) КАК Kolvo0,
| МИНИМУМ(ВЫБОР КОГДА Дата < &Граница ТОГДА Дата КОНЕЦ) КАК Minim0,
| КОЛИЧЕСТВО(ВЫБОР КОГДА ВЫРАЗИТЬ(Значение КАК ДАТА) < &Граница ТОГДА 1 КОНЕЦ) КАК Kolvo1
|ИЗ
| Документ.ВашДокумент";
Запрос.УстановитьПараметр("Граница", Дата(1753, 1, 1)); // смещение 0
// Печатая минимум, добавьте ДП=01.01.0001, иначе Формат() вернёт пустую строку.Индексы на MS SQL: что создастся и что упадёт потом
Предел ключа индекса у MS SQL 900 байт для кластерного и 1700 для некластерного начиная с версии 2016. На 2012 и 2014 для любого индекса 900. У PostgreSQL около 2704, и ключ, который в источнике живёт годами, на приёмнике может выйти за предел.
Мы ждали, что перенос стенда на таком ключе упадёт. Он прошёл:
| Замер на стенде | Значение |
|---|---|
Создано индексов, по выводу ibcmd | 24 535, ошибок 0 |
| Ключей шире 900 байт | 37, из них 14 кластерных |
| Ключей шире 1700 байт | 0 |
| Самый широкий ключ | 1234 байта, 6 колонок |
SQL Server создаёт индекс с объявленным ключом сверх предела и выдаёт предупреждение, пока фактические значения помещаются. По документации запись длиннее предела упадёт уже в работе, руками этот отказ мы не воспроизводили. 36 ключей из 37 принадлежат таблицам конфигурации.
Что меняет версия SQL Server
| Приёмник | Стенд: 37 ключей шире 900 байт | УПП: 59 ключей шире 900 байт |
|---|---|---|
| 2012 и 2014 | за пределом все 37 | за пределом все 59 |
| 2016 и новее | 23 некластерных в пределе, 14 кластерных за 900 | 26 шире 1700 за пределом любой версии, 33 в серой зоне |
Серая зона возникает, потому что платформа не сообщает, какой индекс кластерный. Ключ от 901 до 1700 байт на 2016 скорее всего пройдёт, но у кластерного предел 900. Точный ответ даст пробный перенос копии и запрос к приёмнику:
-- Ключи шире 900 байт: объявленная ширина и вид индекса.
SELECT o.name AS tablica, i.name AS indeks, i.type_desc AS vid,
SUM(c.max_length) AS bajt, COUNT(*) AS kolonok
FROM sys.indexes AS i
JOIN sys.objects AS o ON o.object_id = i.object_id AND o.type = 'U'
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
AND ic.is_included_column = 0
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
GROUP BY o.name, i.name, i.type_desc
HAVING SUM(c.max_length) > 900
ORDER BY bajt DESC;Как посчитать ширину ключа до переноса
Колонки каждого индекса с именами хранения отдаёт ПолучитьСтруктуруХраненияБазыДанных(). Байты складываем по суффиксу имени хранения: колонки _TYPE, _RTRef, _RRRef, _S, _N составного реквизита указывают на один реквизит, и счёт по его типу прибавляет полную ширину к каждой. На одном индексе УПП так вышло 8 316 байт при настоящих 2 107.
| Колонка или суффикс | Байт | Колонка или суффикс | Байт |
|---|---|---|---|
_TYPE | 1 | _S | 2 на символ строки |
_RTRef, TRef | 4 | _N | 5, 9, 13 или 17 по разрядности |
_RRRef, RRef, _IDRRef | 16 | _L, _Marked, _Posted, _Folder | 1 |
_Period, _T, любая дата | 6 | _LineNo, _KeyField | 5 |
_Code, _Description | 2 на символ длины | _Version | 8 в объекте, 16 в служебной таблице |
Дата занимает 6 байт: платформа заводит datetime2 с такой точностью. Восьмёрка от старого datetime добавляет по 2 лишних байта на каждую дату в ключе. Этот расчёт мы сверили с самой СУБД на УПП: 59 ключей шире 900 байт против 59, 26 шире 1700 против 26.
На УТ 11.5 самый широкий ключ у регистра сведений ЛогированиеПакетовОбмена, 2 167 байт, из них 2 058 даёт строковый реквизит Идентификатор. Лечится укорачиванием строки или снятием индексирования.
Служебные таблицы платформы
Их ключи в конфигурации не поправить. На УПП (8.3.20) из 59 ключей шире 900 байт 18 пришлись на служебные таблицы: хранилища настроек по 1 152 байта (_UserId 128 + _ObjectKey 512 + _SettingsKey 512) и тела сообщений сервисов интеграции, где один _MessageBodyKey занимает 1 024. На стенде (8.3.27) хранилище индексируется по числовым хешам, ключ 556 байт. Базы различаются и платформой, и конфигурацией, так что связь с версией платформы пока наблюдение. Выхода два: обновить платформу до переноса или взять приёмник посвежее.
Чем заменить подсчёт строк при приёмке
После испорченного прогона счётчики стенда совпали по всем шести контрольным таблицам, включая пострадавшую. У каждой контрольной величины своя слепая зона:
| Величина | Ловит | Не видит |
|---|---|---|
| Число строк | потерю строк | изменение значений |
| Минимальная и максимальная дата документов и периодов | схлопывание в 1753 год | числа и строки |
| Число пустых дат по реквизитам из проверки | пустые даты, ставшие 01.01.1753 | всё прочее |
| Суммы ресурсов крупных регистров | потерю строк, порчу чисел | даты: в сумму они не входят |
| Байты двоичных данных и конфигурации | порчу хранилищ | прикладные данные |
Запросом платформы величины на обеих сторонах должны совпасть точно. На уровне СУБД стенд после прогона со смещением 2000 сошёлся по конфигурации (1 840 918 604 байта), двоичным данным (1 439 365 954 байта), схеме базы, трём регистрам и двум денежным ресурсам. Строки на PostgreSQL считайте count(*): n_live_tup у стенда показывал ноль по всем таблицам, ANALYZE после восстановления кластера никто не запускал.
Когда копия и рабочая база лежат на одном MS SQL, сверку за период через EXCEPT и контрольные суммы мы разбирали в статье про тестовую базу, которая отстала от рабочей.
Регламент переезда
| Этап | Действие | Зачем |
|---|---|---|
| До | Проверить платформу | replicate есть с 8.3.23, из файловой базы в клиент-серверную вендор миграцию не поддерживает |
| До | Проверить даты со смещением будущего приёмника | узнать, сколько значений ляжет в 1753 год |
| До | Посчитать ключи под версию SQL Server | укоротить реквизиты, обновить платформу или выбрать 2016+ |
| До | Снять контрольный срез на источнике | без величин “до” сверять не с чем |
| До | Создать базу-приёмник заранее | --target-create-database создаёт базу по model, а готовую берёт молча |
| До | Подобрать потоки под свободную память | у стенда было 2 ГБ свободных: --jobs-count=2 --target-jobs-count=2, пик ibcmd 623 МБ |
| Во время | Писать stderr в отдельный файл | предупреждение о датах уходит только туда |
| Во время | Смотреть [INFO] Перенесено таблиц и занятое место | размер предрасширенного файла стоит на месте |
| Во время | Удалённо запускать через планировщик | Start-Process внутри Invoke-Command умирает с WinRM-сессией, как было у нас при переезде на Linux |
| После | Открыть stderr при любом коде возврата | пустой файл тоже результат |
| После | Повторить срез и сравнить | таблица величин выше |
| После | Зарегистрировать базу с тем же смещением | копии заодно --scheduled-jobs-deny=on у rac infobase create |
В model на инстансе стенда стояла модель восстановления FULL, поэтому приёмник мы создали сами: 28 ГБ данных и 8 ГБ журнала сразу, Cyrillic_General_CI_AS, SIMPLE на время заливки. С мгновенной инициализацией файлов на это ушло 19,9 секунды. Настройки нового SQL Server после переезда проверьте чек-апом СУБД под 1С.
Грабли вокруг переноса
Аргументы с пробелами. Адрес вида 127.0.0.1 port=..., переданный массивом аргументов, рвётся по пробелу. С --descr у rac так же: слово из описания ушло в адрес сервера. Аргументы собираем одной строкой, значения с пробелами в кавычках, без точки с запятой после порта.
Пакетный конфигуратор для проверки базы. С /DisableStartupDialogs он отвечает “Пользователь ИБ не идентифицирован”, хотя база цела. Без ключа показывает окно входа и висит до ручного закрытия. Для автоматической приёмки не годится ни один вариант.
ibcmd infobase config save с выводом в файл. Команда требует входа в базу и при пустом stdin печатает “Имя пользователя:” в бесконечном цикле: 106 МБ одной строки за десять минут. Исходная база на PostgreSQL ведёт себя так же, это поведение утилиты.
Пользователи. Список пользователей копии мы сбросили сами, чтобы войти без пароля. Замер v8users дал ноль строк, а окно входа всё равно предлагало фамилию: имена платформа держит ещё и в таблице Params, в записи users.usr.
БСП и единственный администратор. Когда вход открылся, следующий запуск упал с сообщением, что не осталось бы ни одного пользователя с административными правами. Права при этом стояли. В УправлениеПользователями текущего пользователя вычёркивают из списка администраторов до проверки, ему ведь как раз меняют роли. Помог второй пользователь с полными правами, удалять его нельзя.
Усадка втрое. 22 ГБ на PostgreSQL стали 7 870 МБ данных на MS SQL. Полезного содержимого в двоичных данных источника было 1,44 ГБ, остальное раздутие хранилища, которое приехало вместе с физическим бэкапом кластера. Числа сняты разными способами, вычитать их бесполезно, а место под приёмник по объёму источника не планируйте.
Проверка дат и индексов одной обработкой
Обе проверки собраны в обработку “Проверка базы 1С перед переносом на MS SQL Server”, 10 SM на Инфостарте. Она ищет даты, которые не переживут выбранное смещение, и считает ключи индексов под версию SQL Server с разбором по реквизитам. К СУБД не подключается, в базу не пишет.
На пути в PostgreSQL всё наоборот: уникальный индекс становится строже, и перенос встаёт на невидимых символах. Мягче он там в одном месте: строки с пустым полем в ключе PostgreSQL дублями не считает, и служебные таблицы рядом с базой теряют защиту от повторов. Разбор в статье о миграции 1С на PostgreSQL, для того направления есть своя проверка перед переездом. А если уходите с PostgreSQL из-за скорости, сначала проверьте его настройку под 1С.
Репетицию переезда на копии, проверки до и сверку после мы делаем в рамках обслуживания баз 1С.
