Pgadmin восстановление базы из backup

Во время работы с базой данных postgresql в 1С необходимо периодически выполнять задачи по бэкапу базы данных и ее восстановлению. Для увеличения производительности рекомендуется регулярно проводить очистку, анализ и переиндексацию базы 1С. Для автоматизации процессов по архивации и обслуживанию я написал небольшие скрипты с пояснениями. Ими и хочу поделиться с вами.

Данная статья является частью единого цикла статьей про сервер Debian.

Содержание

Введение
Бэкап и восстановление базы 1C в бд postgresql
Использование программы PostgreSQL Backup
Обновление статистики и реиндексация в postgresql
Заключение
Создание резервных копий

Введение

Ранее я рассказывал о том, как установить и настроить postgresql для работы с 1С, а затем как провести анализ производительности базы 1С и по возможности увеличить быстродействие. После успешного выполнения первых двух задач, мы можем приступать к эксплуатации системы. Когда рабочая база 1С уже на сервере, обязательно нужно настроить ее регулярный бэкап. Желательно так же периодически проводить очистку и переиндексацию sql базы. Это увеличит ее быстродействие. Выполнять эти операции лучше всего автоматически, в нерабочее время. Именно этим мы и займемся в этой статье.

Бэкап и восстановление базы 1C в бд postgresql

Способов бэкапа базы данных postgresql много. Я буду использовать самый простой — выгрузка базы данных в обычный текстовый sql скрипт с помощью pg_dump. Подробно о работе этой утилиты и ее настройках можно прочитать вот тут — https://postgrespro.ru/docs/postgrespro/9.6/app-pgdump. В сети много примеров и готовых скриптов для решения вопроса архивации баз postgresql. Например, есть вот такой скрипт. Когда я его увидел, мне просто стало лень с ним разбираться. Написать простенький свой мне гораздо проще.

Прежде чем делать непосредственно архив 1С базы, нам нужно разрешить подключаться локально к серверу бд без авторизации. Я единственный пользователь сервера, доступа к нему никто больше не имеет, в интернет он не опубликован, поэтому я сознательно иду на этот шаг, чтобы упростить себе работу. Если для вас этот вариант не подходит, то все дальнейшие скрипты вам нужно будет самим изменить, добавив в них авторизацию. Это не сложно, в описании команд все есть. Я буду все команды выполнять локально от пользователя root.

Редактируем в файле /etc/postgresql/9.6/main/pg_hba.conf строку, приведя ее к такому виду:

После этого надо перезапустить постгрес, чтобы изменения вступили в силу.

Бэкап базы данных выполняется простой командой:

postgres	имя пользователя базы данных
base1c	название бд с 1С
pigz	архиватор
base1c.sql.gz	файл, куда будет сохранена резервная копия

Обращаю внимание на архиватор pigz. Я его использую, потому что он умеет жать данные, нагружая все ядра процессора, в отличие от gzip. Прирост производительности 2-3 раза. Рекомендую. На debian он ставится из стандартного репозитория:

В centos из epel:

Попробуйте вручную в консоли выполнить команду и посмотреть на результат. Вы должны получить заархивированный файл с текстовыми sql командами в открытом виде. Теперь попробуем восстановить базу данных 1С из архива. Тут будут нюансы. Первым делом разархивируем файл:

Файл будет распакован, а архив удален. Чтобы сохранить архив, можно использовать такую команду:

Создадим на сервере новую базу данных, в которую будем восстанавливать резервную копию. Перед этим посмотрим список баз данных на сервере:

Создаем новую базу данных:

Смотрим, что получилось:

Базу данных создали. Теперь загружаем в нее наш бэкап 1с:

Ждем приличное время. Оно будет зависеть от размера базы. После того, как восстановление завершено, можно идти в консоль кластера 1С и добавлять новую базу, указывая в качестве базы postgresql только что созданную базу с загруженным архивом.

Я сначала пошел по другому пути. Создал в консоли пустую базу, загрузил в нее бэкап через консоль postgresql. Архив вроде бы загрузился, но в базу я не мог войти, не проходила авторизация. То есть возникли какие-то проблемы. Когда сделал, как описал выше, без проблем все заработало сразу. Проверил базу, все было в порядке.

После того, как вы убедитесь, что все в порядке, можно написать небольшой скрипт, который мы добавим в cron для регулярного бэкапа нашей базы 1С. Я его сделал совсем простым, ничего лишнего, только 1 база. Я считаю, что если баз не много и вручную не представляет труда написать несколько строчек скрипта, то лучше все сделать без циклов и лишних переменных. Если у вас больше одной базы, то просто скопируйте строки и замените названия баз. Вот сам скрипт:

Я указал в названии файла с бэкапом 1с базы использовать текущую дату с точностью до минуты. В лог я пишу информацию с точностью до секунды, чтобы было точно видно, сколько длился бэкап. Просто для справки информация, можно обойтись и без лога совсем. В конце удаляю из папки все архивы старше 3-х дней. Я обычно сервером с бэкапами забираю информацию с целевых хостов. То есть я буду подключаться к sql серверу и забирать с него архивы и уже на сервере бэкапов буду их хранить и ротировать в зависимости от желаемой глубины архива. А здесь я удаляю почти сразу архивы, не храню их, чтобы не занимать место. Если вы будете хранить их долгосрочно на этом же сервере, то просто измените цифру 3 на нужное вам число дней, за которые вы хотите иметь архивную копию своей базы 1С.

Использование программы PostgreSQL Backup

Для бэкапа базы данных постгрес есть удобная и бесплатная для двух баз программа под windows — PostgreSQL Backup. Я ее установил, проверил, сделал бэкап, потом восстановил из бэкапа. Все отлично работает. Из полезных функций:

встроенный планировщик
автоматическое сжатие бэкапа
отправка оповещений на email

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

Обновление статистики и реиндексация в postgresql

С бэкапами разобрались, теперь настроим регламентные операции на уровне субд, чтобы поддерживать быстродействие базы данных. Тут особых комментариев не будет, в интернете очень много информации на тему регламентных заданий для баз 1С. Я просто приведу пример того, как это выглядит в postgresql.

Выполняем очистку и анализ базы данных 1С:

Реиндексация таблиц базы данных:

Завернем все это в скрипт с логированием времени выполнения команд:

Сохраняем скрипт и добавляем в планировщик. Хотя я для удобства сделал еще один скрипт, который объединяет бэкап и обслуживание и уже его добавил в cron:

Добавялем в /etc/crontab:

Проверяем лог файл и наличие бэкапа. Не забывайте делать проверочное регулярное восстановление бд из архива.

Описанные выше операции очистки и переиндексации можно делать в ручном режиме в программе под windows — pgAdmin. Рекомендую ее установить на всякий случай. Достаточно удобно и быстро можно посмотреть информацию или выполнить какие-то операции с базой данных посгрес.

Заключение

Вот и все, что я хотел рассказать по поводу бэкапа и обслуживания баз postgresql в связке с 1С. Если у кого есть еще полезная информация, прошу поделиться в комментариях. Возможно с бэкапом есть какие-то нюансы, особенно на больших базах. Но мне негде тестировать, больших рабочих баз более 10 гб у меня нет под рукой, а с теми что были, все отлично работает.

Напоминаю, что данная статья является частью единого цикла статьей про сервер Debian.

Вводная

В СУБД PostgreSQL есть такое интересное техническое решение — перед тем как собственно начать что то менять в файлах самой базы данных СУБД пишет уже переведенные во внутренний формат команды в специальный журнал — Write-Ahead Log, а после успешного завершения транзакции делает в этом журнале пометку. Сделано это было для восстановления после сбоев, но в итоге пытливый ум разработчиков дошел до идеи использовать этот журнал для резервирования и репликации. В принципе логично, все ходы в нём записаны, более того можно не просто восстановить данные из бэкапа, но и восстановить состояние базы на определенный момент времени, прервав проигрывание записей WAL-лога в нужный момент.

Однако давайте рассмотрим такой сценарий — допустим в понедельник вы сделали базовый бэкап и запустили архивацию WAL-логов, в среду вы выполнили запрос на удаление с ошибочной маской, а обнаружили это только в пятницу, когда менеджер сообщил об исчезновении какой то нужной ему записи. В данной ситуации мы можем только восстановиться из бэкапа до среды, потеряв всю работу менеджеров за четверг и пятницу.

Возникает логичный вопрос, а нельзя ли сделать проигрывание WAL-логов с понедельника по пятницу, при этом исключив наш «ошибочный» запрос?

В обычной ситуации я ограничился бы вопросом на форум, но у меня было 2 дистрибутива FreeBSD, 10 тарболлов с исходниками PostgreSQL разных версий, 10Гб места на винте, gcc, две относительно незагруженных недели, а также текила, ром, ящик пива и обрывочные воспоминания о синтаксисе языка C. Не то чтобы это был необходимый запас для решения, но раз уж заглянул в исходные коды, то сложно остановиться…

Итак, для экспериментов взяты FreeBSD 10 и PostgreSQL 9.2.8 из её портов. Клиент соответствующей версии можно поставить с помощью pkg, в нем ничего менять не нужно. Заранее извиняюсь за возможное капитанство, но текст писался как для новичков, так и для того чтобы быстро всё освежить в голове в случае необходимости, поэтому все команды расписаны подробно.

Установка и базовая настройка сервера

Скачанный файл с исходниками разворачивается в папку work в директории порта. Я честно говоря так и не понял как пересобирать исходники после изменений, какого то make rebuild вроде нету, make clean в свою очередь просто сносит эту папку со всеми изменениями. Поэтому я просто скопировал папку work в свою домашнюю директорию, вносил изменения там, затем копировал в папку порта и запускал make install.

Пока что ничего не меняем, просто ставим постгрес:

Создаем папки для архивов:

Постгрес требует чтобы у директории с данными был доступ только для юзера поэому меняем права:

Делаем примитивную настройку. Здесь имеет смысл перейти под постгресовую учетку pgsql чтобы было меньше возни с правами на файлы.

Раскомментируем и правим параметры архивации WAL-логов в /usr/local/pgsql/data/postgresql.conf:
archive_mode=on
wal_level = archive
archive_command = ‘test! -f /usr/db_archive/wal/%f && cp %p /usr/db_archive/wal/%f’
(пример там рядом в камментах)
max_wal_senders = 1

В /usr/local/pgsql/data/pg_hba.conf раскомментируем строку
local replication pgsql trust

Делаем базовый бэкап

Проверяем, в папке /usr/db_archive/data/ должна лежать копия директории данных, в /usr/db_archive/wal/ должны лежать WAL файлы вида примерно 000000010000000000000003

Копируем в папку с бэкапом директории данных конфиг для восстановления

и в нём раскомменитруем и правим команду восстановления (пример тоже рядом в комментах).
restore_command = ‘cp /usr/db_archive/data/%f %p’

Изменяем записи, вносим новые, дискотека

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

и начинаем думать что же делать.

Идём в исходники

Как вы помните, после make extract я скопировал папку work из директории порта в свою домашнюю папку, и делал изменения в ней. Поэтому переходим туда. Если кто то может подсказать как делать изменения в исходниках в самой папке порта чтобы всё нормально пересобиралось после внесённых в код изменений буду крайне благодарен.

Вначале я поставил себе цель найти то место где считываются из файла записи WAL-логов.

Файл с кодом относящимся к WAL я нашел с помощью поиска строки «WAL» в содержимом файлов директории work/postgresql-9.2.8/src и здравого смысла, это оказался файл xlog.c

Я не умею в трассировку программ на C, поэтому просто в начале каждой функции добавил запись её названия в файл, собрал и запустил.

В файле получился такой вот результат:

В общем, у меня сложилось впечатление что основное действие происходит в цикле ReadRecord -> XLogPageRead -> RecordIsValid -> RecoveryStopsHere -> CheckRecoveryConsistency.

Более близкое знакомтсво с функицей ReadRecord показало что она возвращает запись в двух местах — как return record и как return (XLogRecord *) buffer, вышеуказанным нехитрым способом уточняем что в процессе восстановления с WAL-логов возврат идёт через return (XLogRecord *) buffer. Прекрасно! Пишем результат в файл.

Структуру типа XLogRecord можно посмотреть в файле xlog.h и она достаточно лаконична:

Отлично, если у нас есть длина, то и используем её для вывода содержимого записи в файл, перед return (XLogRecord *) buffer добавляем:

Сносим старый Постгрес, собираем и устаналиваем новый:

Напоминаю что мы скопировали директорию work в домашнюю папку и все изменения кода вносили там. Теперь копируем её на место папки work в директории порта.

Удаляем файлы базы данных и копируем на их место базовый бэкап. WAL-файлы сами подтянутся.

Смотрим содержимое файла log3.txt, вначале идем много больших записей, видимо создание служебных таблиц и данных, ближе к концу видим:

Видим что между знакомыми фамилиями Николаев и Петрова есть 4 похожие записи и одна непохожая, под одним номером транзакции. Видимо, это команды удаления, значит в WAL-лог записываются уже команды типа «стереть строку 50 в таблице 64822». В принципе, как и ожидалось. Дописываем проверку, которая при значении xl_x >
Опять удаляем старый Постгрес, собираем и устанавливаем новый, запускаем восстановление…

Удаленные записи на месте! Правда все что должно было произойти после удаления не произошло 🙁 Что ж, с наскока взять не получилось. В общем то понятно, ведь перед проигрыванием записи проходят проверки целостности и всего такого.

Значит цель номер 2 — найти где идет «проигрывание» записи. Быстрый поиск использования readRecord в том же файле привел меня к функции void StartupXLOG(void)… И вот тут я отчетливо понял что до сего момента шел не тем путем, потому что почти сразу после второго-третьего появления в этой функции вызова readRecord (они там рядом) сразу идёт во первых шикарный диагностический кусок, а во вторых, сразу после комментария «Now apply the WAL record itself» — команда проигрыша записи RmgrTable[record->xl_rmid].rm_redo(EndRecPtr, record);

Изменим этот кусок кода на

Опять пересобираем, запускаем, проверяем… Победа! Удаленные записи на месте и изменения, сделанные после удаления тоже на месте!

Ориентируемся на местности

Что ж, это, несомненно, хорошо, но задачу мы решили на крайне ограниченном наборе данных, а вот как найти нужную запись в логах рабочей базы?

Вернемся к упомянутому шикарному диагностическому куску в функции StartupXLOG:

Можно просто включить вывод в логи, раскомментировав #define WAL_DEBUG в pg_config_manual.h и добавив wal_debug=on в файл postgresql.conf, но я, по привычке, направил вывод в отдельный файл. Этот кусок, как я понял, выводит описание команды с помощью функции rm_desc (в данном случае RmgrTable является массивом функций?), выглядит оно примерно так:

Это уже знакомый нам кусок с номером транзакции 1003, и по нему мы можем увидеть что да, это четыре команды на удаление и одно подтверждение транзакции. В командах на удаление мы видим rel — идентификатор таблицы в формате «oid пространства имен/oid базы данных/oid таблицы». Соответствующие циферки можно получить запросами

SELECT oid, spcname FROM pg_catalog.pg_tablespace;
SELECT oid, datname FROM pg_catalog.pg_database;
и, внезапно,
SELECT oid, relname FROM pg_catalog.pg_class;

Второй ориентир — в описании транзакции есть отметка времени. Ну, тут ничего объяснять не надо, если мы знаем когда этот самый crime был commited, то и соответсвующие записи найдем.

Ну и, как альтернативный способ, можно вернуться к просмотру записей в кракозябрах, и ориентироваться по обрывкам текстов которые были переданы как параметры командам INSERT и UPDATE, если мы помним запросы с какими параметрами делались незадолго до или после искомого «ошибочного» запроса. В случае UPDATE, правда, можно найти только те, строки которые использовались как новое значение, если строка использовалась для поиска записей, то в WAL-логах она не встречается.

Ну и напоследок могу отметить что в контрибах PostgreSQL 9.3 появилась утилита pg_xlogdump, которая, вроде бы, как раз нацелена на решение задачи предоставления содержимого WAL-логов в человекочитаемом виде. Если вы заинтересованы в каких то фичах то имеет смысл писать разработчикам.

Вполне возможно что использование этого метода на архивах рабочей БД будет иметь какие то подводные камни. Например как отработают UPDATE-ы, если мы «пропустим» удаление части записей на базе данных в которой используется частое вакуумирование? Я не проверял. Но в любом случае в случае лучше иметь хоть какую то надежду исправить ошибку, чем совсем никакой.

В данной инструкции рассмотрены варианты создания резервных копий и восстановления баз СУБД PostgreSQL.

Все команды, которые приводятся ниже, должны выполняться из командной строки. В Linux — это окно терминала, в Windows — командная строка (cmd.exe) с переходом в папку установки PostgreSQL.

Создание резервных копий

Базовая команда

pg_dump users > /tmp/users.dump

Пользователь и пароль

Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U с указанием пользователя:

pg_dump -U dmosk -W users > /tmp/users.dump

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

Сжатие данных

Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив:

pg_dump users | gzip > users.dump.gz

Скрипт для автоматического резервного копирования

PGPASSWORD=password
export PGPASSWORD
pathB=/backup
dbUser=dbuser
database=db

find $pathB ( -name "*-1[^5].*" -o -name "*-[023]?.*" ) -ctime +61 -delete
pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date "+%Y-%m-%d").sql.gz

* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к БУБД.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

На удаленном сервере

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

pg_dump -h 192.168.0.15 users > /tmp/users.dump

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL.

Дамп определенной таблицы

Запускается с опцией -t или —table= :

pg_dump -t students users > /tmp/students.dump

* где students — таблица; users — база данных.

Размещение каждой таблицы в отдельный файл

Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -d:

pg_dump -d customers > /tmp/folder

* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.