Postgres 1с проверка таблиц на целостность

Обновлено: 01.07.2024

Сразу оговорюсь, что мои познания в T-SQL не сильно велики т. к. по большей части пишу код для конфигураций на платформе 1С:Предприятие, и предложенное решение может быть не совсем оптимальным.

Оптимизация и улучшения предложенного скрипта приветствуется.

Небольшое предисловие.

Часто случается, что базы данных повреждаются, по различным причинам, и мы не всегда это вовремя замечаем.

Что бы проверить базу данных надо зайти в интерфейс, запустить скрипт, получить результат. И уже в зависимости от результата принимать какие-то решения и действия. Возможно это нормально, когда есть свободно время и не лень запустить скрипт вручную. Но что делать когда, к примеру, на поддержке с 10-к и более баз и находятся они на разных серверах. Подключаться к каждому серверу и запускать скрипт руками занимает много времени, да делать это вручную лень.

Для разработчика, администратора и т. п. л ень это двигатель его прогресса, настроил систему как надо и читай логи, письма и прочее оповещения.

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

Это было небольшое предисловие, теперь перейдем к самой задаче.

Целью задачи было: проверять целостность баз данных 1с на сервере SQL по регламенту и при обнаружении поврежденной базы оповещать по почте. Оповещение только если найдена поврежденная база. Состав письма краткий, все данные результата проверки высылать не требуется.

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

На просторах интернета прочитал, что данные можно вывести в таблицу.

DBCC CHCKDB WITH TABLERESULTS выведет данные в таблицу, но просто так взять и сделать выборку из из этой таблицы нельзя, но выход все же нашелся.

В поисках нужной информации для решения моей задачи наткнулся на публикацию Спасибо тебе R odert Pearl , что ты ее когда то написал.

1. Создаем временную таблицу.

Исходное описание таблицы немного изменено.

Добавлена колонка "DatabaseName".

Изменил типы данных в некоторых колонках, т.к. при первом же тесте получил ошибки о невозможности преобразования типов данных

Колонки: PartitionID, AllocUnitID изменил тип данных с INT на BIGINT ,

Колонка: RepairLevel с INT на VARCHAR(300)

В каких колонках нужно менять тип данных искал методом тыка и исключения.

2. В ранее созданную временную таблицу, при помощи CURSOR , по списку баз, поместим выходные данные DBCC CHCKDB .

У меня есть база 'Recovery', использую для разных целей. Сегодя она играет роль поврежденной базы данных. Результ ее проверки и поместим во временную таблицу.

Результаты DBCC CHECKDB вставляем во временную таблицу

DECLARE @database_name NVARCHAR(50)
DECLARE database_cursor CURSOR FOR

SELECT name
FROM sys.databases db
WHERE name = 'Recovery'
AND db.state_desc = 'ONLINE'
AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots)
AND is_read_only = 0

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database_name
WHILE @@FETCH_STATUS=0
BEGIN

FETCH NEXT FROM database_cursor INTO @database_name
END

CLOSE database_cursor
DEALLOCATE database_cursor

Результаты DBCC CHECKDB. Выборка уже из временной таблицы

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

Мне на выходе нужна одна строка с описанием ошибок. Строку собираю из колонок: DatabaseName , MessageText при помощи конкатенации. Дополнительно накладываю условия на ' MessageText ', что бы получить нужные строки, т.к. если база не повреждена данные в выходном наборе все равно будут. Только в тексте будет количество ошибок "0". Мне эти данные не нужны.

DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 10 ошибок согласованности, не связанных ни с одним объектом.
DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 30 ошибок согласованности в таблице "_InfoRg23950" (идентификатор объекта 469889041).
DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 9 ошибок согласованности в таблице "_Reference12359" (идентификатор объекта 956790716).
DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 5 ошибок согласованности в таблице "_InfoRg24673" (идентификатор объекта 1015882886).
DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 3 ошибок согласованности в таблице "_InfoRg9101" (идентификатор объекта 1179359466).
DB Recovery: CHECKDB обнаружил 0 ошибок размещения и 57 ошибок согласованности в базе данных "Recovery".

Осталось проверить есть ли у нас в сформированной строке данные, при их наличии отправляем данные на почту.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Profilename,
@recipients = @Recipients,
@body = @MSG,
@subject = @Msubject;
END
GO

Проверяем скрипт, все работает.

Создаем Job, настраиваем расписание и готово

DECLARE @database_name NVARCHAR(50)
DECLARE database_cursor CURSOR FOR

SELECT name
FROM sys.databases db
WHERE name = 'Recovery'
AND db.state_desc = 'ONLINE'
AND source_database_id IS NULL -- REAL DBS ONLY (Not Snapshots)
AND is_read_only = 0

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @database_name
WHILE @@FETCH_STATUS=0
BEGIN

FETCH NEXT FROM database_cursor INTO @database_name
END

CLOSE database_cursor
DEALLOCATE database_cursor

Используя транзакции Read Committed, очень сложно обеспечить целостность данных с точки зрения бизнес-логики, так как представление данных смещается с каждым оператором и даже один оператор может не ограничиваться своим снимком состояния в случае конфликта записи.

Как было сказано в Подразделе 13.2.3, сериализуемые транзакции представляют собой те же транзакции Repeatable Read, но дополненные неблокирующим механизмом отслеживания опасных условий конфликтов чтения/записи. Когда выявляется условие, приводящее к циклу в порядке транзакций, одна из этих транзакций откатывается и этот цикл таким образом разрывается.

13.4.1. Обеспечение согласованности в сериализуемых транзакциях

Если для всех операций чтения и записи, нуждающихся в согласованном представлении данных, используются транзакции уровня изоляции Serializable, это обеспечивает необходимую согласованность без дополнительных усилий. Приложения из других окружений, применяющие сериализуемые транзакции для обеспечения целостности, в Postgres Pro в этом смысле будут « просто работать » .

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

Рекомендации по увеличению быстродействия приведены в Подразделе 13.2.3.

Предупреждение

Защита целостности с применением сериализуемых транзакций пока ещё не поддерживается в режиме горячего резерва (Раздел 25.5). Поэтому там, где применяется горячий резерв, следует использовать уровень Repeatable Read и явные блокировки на главном сервере.

13.4.2. Применение явных блокировок для обеспечения согласованности

Когда возможны несериализуемые операции записи, для обеспечения целостности строк и защиты от одновременных изменений, следует использовать SELECT FOR UPDATE , SELECT FOR SHARE или соответствующий оператор LOCK TABLE . ( SELECT FOR UPDATE и SELECT FOR SHARE защищают от параллельных изменений только возвращаемые строки, тогда как LOCK TABLE блокирует всю таблицу.) Это следует учитывать, перенося в Postgres Pro приложения из других СУБД.

Мигрируя в Postgres Pro из других СУБД также следует учитывать, что команда SELECT FOR UPDATE сама по себе не гарантирует, что параллельная транзакция не изменит или не удалит выбранную строку. Для получения такой гарантии в Postgres Pro нужно именно изменить эту строку, даже если никакие значения в ней менять не требуется. SELECT FOR UPDATE временно блокирует другие транзакции, не давая им получить ту же блокировку или выполнить команды UPDATE или DELETE , которые бы повлияли на заблокированную строку, но как только транзакция, владеющая этой блокировкой, фиксируется или откатывается, заблокированная транзакция сможет выполнить конфликтующую операцию, если только для данной строки действительно не был выполнен UPDATE , пока транзакция владела блокировкой.

Также заметьте, что, применяя явные блокировки для предотвращения параллельных операций записи, следует использовать либо режим Read Committed, либо в режиме Repeatable Read обязательно получать блокировки прежде, чем выполнять запросы. Блокировка, получаемая транзакцией Repeatable Read, гарантирует, что никакая другая транзакция, изменяющая таблицу, не выполняется, но если снимок состояния, полученный транзакцией, предшествует блокировке, он может не включать на данный момент уже зафиксированные изменения. Снимок состояния в транзакции Repeatable Read создаётся фактически на момент начала первой команды выборки или изменения данных ( SELECT , INSERT , UPDATE или DELETE ), так что получить явные блокировки можно до того, как он будет сформирован.

13.4.1. Обеспечение согласованности в сериализуемых транзакциях

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

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

Рекомендации по увеличению быстродействия приведены в Подразделе 13.2.3.

13.4.2. Применение явных блокировок для обеспечения согласованности

Когда возможны не сериализуемые операции записи, для обеспечения целостности строк и защиты от одновременных изменений, следует использовать SELECT FOR UPDATE, SELECT FOR SHARE или соответствующий оператор LOCK TABLE. (SELECT FOR UPDATE и SELECT FOR SHARE защищают от параллельных изменений только возвращаемые строки, тогда как LOCK TABLE блокирует всю таблицу.) Это следует учитывать, перенося в PostgreSQL приложения из других СУБД.

Мигрируя в PostgreSQL из других СУБД также следует учитывать, что команда SELECT FOR UPDATE сама по себе не гарантирует, что параллельная транзакция не изменит или не удалит выбранную строку. Для получения такой гарантии в PostgreSQL нужно именно изменить эту строку, даже если никакие значения в ней менять не требуется. SELECT FOR UPDATE временно блокирует другие транзакции, не давая им получить ту же блокировку или выполнить команды UPDATE или DELETE, которые бы повлияли на заблокированную строку, но как только транзакция, владеющая этой блокировкой, фиксируется или откатывается, заблокированная транзакция сможет выполнить конфликтующую операцию, если только для данной строки действительно не был выполнен UPDATE, пока транзакция владела блокировкой.

Также заметьте, что, применяя явные блокировки для предотвращения параллельных операций записи, следует использовать либо режим Read Committed, либо в режиме Repeatable Read обязательно получать блокировки прежде, чем выполнять запросы. Блокировка, получаемая транзакцией Repeatable Read, гарантирует, что никакая другая транзакция, изменяющая таблицу, не выполняется, но если снимок состояния, полученный транзакций, предшествует блокировке, он может не включать на данный момент уже зафиксированные изменения. Снимок состояния в транзакции Repeatable Read создаётся фактически на момент начала первой команды выборки или изменения данных (SELECT, INSERT, UPDATE или DELETE), так что получить явные блокировки можно до того, как он будет сформирован.

PostgerSQL заточен под Linux и в своей среде он будет работать лучше и быстрее (как рыба в воде), но есть и адаптированный под Windows, требующий чуть больших настроек для оптимизации, чем просто "далее-далее-далее" в MSSQL. Хотя на небольших БД на первых этапах хватает и стандартной настройки задаваемой при установке.

Тесты о работе и производительности на разных системах разных продуктов MS SQL, PostgerSQL, под Linux, Windows легко можно найти в интернете, тут же мы рассмотрим простую установку и базовую настройку для работы 1С 8 на PostgerSQL 11.5 под Windows Server 2008 R2.

Постановка задачи:

1С Предприятие 8.3.16.1063, 1С БД Бухгалтерия 3.0.75.58 – размер файла

Сервер: i5-9400, ОЗУ DDR4 16 Гб, SSD 256, ОС Windows Server 2008R2 x64

Установка и настройка PostgreSQL:

1. Подготовка:

Перед установкой и настройкой рекомендуется отключить протокол IPv6, иначе это может затруднить дальнейшую настройку.

Также необходимо установить Microsoft Visual C++ 2015 (на сайте 1С он идет в комплекте)

Также заранее рекомендуется включить службу "Вторичный вход в систему", иначе при установке будет ошибка.

2. Процесс установки

Далее указываем путь установки программы (его не меняем) и путь, где будут располагаться БД (его рекомендуется сменить, чтобы БД хранились не на системном диске)

Если вы не запустили службу "Вторичный вход в систему" то у вас будет ошибка, ее можно включить на этапе установки и продолжить:

После установки запускаем консоль администратора "Пуск-PostgreSQL 11.5-7.1C(x64)-pgAdmin 4"

На этом установка PostgreSQL закончена.

3. Установка 1С сервера:

Запуститься помощник установки системы «1С:Предприятия». На первой странице жмем «Далее».

На следующей странице необходимо выбрать те компоненты, которые будут устанавливаться, нам требуются компоненты:

Сервер 1С:Предприятия — компоненты сервера «1С:Предприятия»
Администрирование сервера 1С:Предприятия 8 — дополнительные компоненты для администрирования кластера серверов «1С:Предприятия»

Если сервер «1С:Предприятия» устанавливается как служба Windows рекомендуется сразу создать отдельного пользователя, из под которого будет запускаться служба "Агент сервера 1С Предприятия", либо можно выбрать существующего пользователя для запуска сервера. Для создание нового пользователя необходимо:

Выбираем флаг «Установить сервер 1С:Предприятие как сервис Windows (рекомендуется)»;
Выбираем «Создать пользователя USR1CV8» и задаем его пароль (пароль должен отвечать политики паролей Windows).

Также пользователю обязательно следует дать необходимые права на каталог служебных файлов сервера (по умолчанию C:\Program Files\1cv8\srvinfo для 64-х разрядного и C:\Program Files (x86)\1cv8\srvinfo для 32-х разрядного сервера). Созданный автоматически пользователь USR1CV8 будет обладать всеми перечисленными правами.

Заполнив соответствующие параметры, жмем «Далее».

Далее идет установка всех необходимых файлов и служб. После чего следует убедиться что появилась и запущена соответствующая служба.

На этом установка Сервера 1С Предприятия закончена.

4. Создание 1С БД для PostgreSQL

После установки 1С Сервера запускаем "Администрирование серверов 1С Предприятия x86-64", переходим в список "Информационные базы" и создаем новую БД. Заполняем основные поля:

Имя - имя БД на сервере 1с
Сервер баз данные - имя сервера где будет располагаться БД 1С SQL
Тип СУБД - выбор на какой платформе у вас будет работать ваша база (MSSQL, PostgeSQL, IBM DB2, Oracle DateBase)
База данных - имя базы которое будет создано в SQL
Пользователь и пароль БД - пользователь в SQL
Создавать базу данных в случае ее отсутствия - Создает БД в SQL если ее нет.

Если вы не отключили протокол IPv6 то у вас при создании будет ошибка:

можно отключить протокол IPv6 и продолжить создание, либо можно указать IP адрес сервера без отключение протокол IPv6:

Все на этом этапе БД готова, в принципе ее можно подключать загружать в SQL и работать. Но рекомендуется сделать настройку самого Postgre сервера для оптимизации и более стабильной работы базы 1С на PostgreSQL. Делается это в 1 файле расположенном в каталоге с базами (путь который вы указывали при установке для баз по умолчанию C:\Program Files\PostgreSQL\11.5-7.1C\data). Файл postgresql.conf

5. Настройка PostgreSQL под 1С 8

ВАЖНО. Перед любыми изменения в этом файле обязательно сделайте его копию, в противном случаем если какой то параметр указан не верно у вас не запустится служба PostgreeSQL:

Перед тем как вносить изменения в файл postgresql.conf необходимо остановить службу

Изменение параметров в postgresql.conf:

После чего запускаем службу PostgreSQL и можно работать.

Так же иногда по какой то причине после загрузки в PostgreSQL в базе отключается "Полнотекстовый поиск", поэтому после настройки рекомендуется проверить и включить если выключено и обновить индексы (все функции-стандартные-управление полнотекстовым поиском).

Читайте также: