Резервное копирование и обслуживание баз данных PostgreSQL для 1С:Предприятие

Создание временного хранилища

Создадим отдельный диск в виртуальной машине ESXi 6.5 для временного хранения архивных копий.

Для отображения подключенных дисков выполним команду:

fdisk -l | grep "Disk /dev/sd"

Подключенный диск имеет название sdf.

Разметим пространство.  В качестве файловой системы выберем ext4:

mkfs.ext4 /dev/sdf

Создадим точку монтирования — каталог /backup

mkdir /backup

Монтируем созданный ранее диск в каталог /backup:

mount /dev/sdf /backup

Для автоматического монтирования диска при загрузке системы выполним команду (редактор mcedit входит в состав файлового менеджера MC):

mcedit /etc/fstab

в открывшемся файле добавим строчки:

/dev/sdf /backup ext4 defaults 1 2

Резервное копирование

Так как доступ к серверу имеет только администратор, то для упрощения разрешим подключаться к серверу PostgreSQL локально без авторизации.

mcedit /var/lib/pgsql/9.6/data/pg_hba.conf

в открывшемся файле изменим строки:

local all all peer на local all all trust

Для принятия изменений перезагрузим сервер СУБД:

systemctl restart postgresql-9.6

Для создания резервной копии создадим скрипт, в котором воспользуемся утилитой pg_dump, позволяющая создать дамп для указанной БД.

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

Дамп БД будем архивировать с помощью pigz, т. к. он использует все ядра процессора.

Установим архиватор pigz:

yum -y install pigz

Перед архивированием можно посмотреть список БД на сервере и для справки уточнить наименование БД и её кодировку:

psql -U postgres -l

Для хранения архивов в «облаке» воспользуемся сервисом Яндекс.Диск.

Установим консольный клиент:

rpm -Uvh http://repo.yandex.ru/yandex-disk/yandex-disk-latest.x86_64.rpm
yandex-disk setup

В ходе установки укажем: будем или нет использовать прокси сервер, логин и пароль для авторизации в сервисе, точку монтирования и автозапуск.

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

Ежедневный скрипт:

mcedit /backup/everyday-backup-pgsql.sh

Содержимое:


#!/bin/bash
# Задаем переменные:
TIME=`date +»%Y-%m-%d_%H-%M»`

# Записываем информацию о начале бэкапа в лог:
echo «`date +»%Y-%m-%d_%H-%M-%S»` Start backup» >> /backup/pgsql-everyday-backup.log

# Бэкапим и архивируем. При бэкапе еще одной базы — копируем эту команду с заменой имени базы с учетом регистра.
pg_dump -U postgres UPP | pigz > /backup/everyday/$TIME-UPP.sql.gz

# Загружаем данные на Яндекс.Диск
cp /backup/everyday/$TIME-UPP.sql.gz /Yandex.Disk/everyday

# Записываем информацию о завершении бэкапа в лог:
echo «`date +»%Y-%m-%d_%H-%M-%S»` End backup» >> /backup/pgsql-everyday-backup.log

# Удаляем файлы старше 5 дней с локального диска
find /backup/everyday -type f -mtime +5 -exec rm -rf {} \;

# Удаляем файлы старше 3 дней с Яндекс.Диск.
find /Yandex.Disk/everyday -type f -mtime +3 -exec rm -rf {} \;

Еженедельный скрипт:

mcedit /backup/everyweek-backup-pgsql.sh

Содержимое:

#!/bin/bash
# Задаем переменные:
TIME=`date +»%Y-%m-%d_%H-%M»`

# Записываем информацию о начале бэкапа в лог:
echo «`date +»%Y-%m-%d_%H-%M-%S»` Start backup» >> /backup/pgsql-everyweek-backup.log

# Бэкапим и архивируем. При бэкапе еще одной базы — копируем эту команду с заменой имени базы с учетом регистра.
pg_dump -U postgres UPP | pigz > /backup/everyweek/$TIME-UPP.sql.gz

# Загружаем данные на Яндекс.Диск
cp /backup/everyweek/$TIME-UPP.sql.gz /Yandex.Disk/everyweek

# Записываем информацию о завершении бэкапа в лог:
echo «`date +»%Y-%m-%d_%H-%M-%S»` End backup» >> /backup/pgsql-everyweek-backup.log

# Удаляем файлы старше 3 дней с локального диска
find /backup/everyweek -type f -mtime +3 -exec rm -rf {} \;

# Удаляем файлы старше 2 дней с Яндекс.Диск.
find /Yandex.Disk/everyweek -type f -mtime +2 -exec rm -rf {} \;

Ежемесячный скрипт:

mcedit /backup/everymonth-backup-pgsql.sh

Содержимое:

#!/bin/bash
# Задаем переменные:
TIME=`date +»%Y-%m-%d_%H-%M»`

# Записываем информацию о начале бэкапа в лог:
echo «`date +»%Y-%m-%d_%H-%M-%S»` Start backup» >> /backup/pgsql-everymonth-backup.log

# Бэкапим и архивируем. При бэкапе еще одной базы — копируем эту команду с заменой имени базы с учетом регистра.
pg_dump -U postgres UPP | pigz > /backup/everymonth/$TIME-UPP.sql.gz

# Загружаем данные на Яндекс.Диск
cp /backup/everymonth/$TIME-UPP.sql.gz /Yandex.Disk/everymonth

# Записываем информацию о завершении бэкапа в лог:
echo «`date +»%Y-%m-%d_%H-%M-%S»` End backup» >> /backup/pgsql-everymonth-backup.log

# Удаляем файлы старше 2 дней с локального диска
find /backup/everymonth -type f -mtime +2 -exec rm -rf {} \;

# Удаляем файлы старше 1 дней с Яндекс.Диск.
find /Yandex.Disk/everymonth -type f -mtime +1 -exec rm -rf {} \;

Даём скриптам права на запуск:

chmod 0700 /backup/everyday-backup-pgsql.sh

chmod 0700 /backup/everyweek-backup-pgsql.sh

chmod 0700 /backup/everymonth-backup-pgsql.sh

Для временного хранения архивных копий создадим соответствующие папки everyday, everyweek, everymonth:

mkdir /backup/everyday

mkdir /Yandex.Disk/everyday

mkdir /backup/everyweek

mkdir /Yandex.Disk/everyweek

mkdir /backup/everymonth

mkdir /Yandex.Disk/everymonth

Так как системный раздел небольшой, перенесем папку Yandex.Disk в backup:

mv /Yandex.Disk /backup

Создадим символьную ссылку:

ln -s /backup/Yandex.Disk /Yandex.Disk

Так как системный раз

Добавим задание в crontab:

mcedit /etc/crontab

Добавим расписание резервного копирования:

# Выгрузка БД в будние дни в полночь
00 00 * * 1 root /backup/everyday-backup-pgsql.sh
00 00 * * 2 root /backup/everyday-backup-pgsql.sh
00 00 * * 3 root /backup/everyday-backup-pgsql.sh
00 00 * * 4 root /backup/everyday-backup-pgsql.sh
00 00 * * 5 root /backup/everyday-backup-pgsql.sh

# Выгрузка БД каждое воскресенье в полночь
30 0 * * 7 root /backup/everyweek-backup-pgsql.sh

# Выгрузка БД каждое 1-ое число месяца в 00:30
0 1 1 * * root /backup/everymonth-backup-pgsql.sh

В файле cron’а должна быть последняя пустая строка. Если файл cron’а редактировали во внешнем редакторе, тогда делаем:

crontab -e

В нем пишем:

:w

:q

Обновлено 9 апреля 2019 года

!!! Важно для пользователей УПП 1.3, КА 1.1 и тех чей CF больше 1ГБ

Спустя время, обнаружил не регулярное создание архивных копий базы данных.

При выполнении скрипта everyday-backup-pgsql.sh получил ошибку:

pg_dump: Ошибка выгрузки таблицы "config": сбой в PQgetResult().
pg_dump: Сообщение об ошибке с сервера: ОШИБКА:  invalid memory alloc request size 1146779163
pg_dump: Выполнялась команда: COPY public.config (filename, creation, modified, attributes, datasize, binarydata) TO stdout;

Ошибка связана с тем, что PostgreSQL имеет ограничение 1Gb для одного поля, а 1С хранит конфигурацию БД в одном поле.

Подобная проблема не встречается если конфигурация типовая, так как её размер обычно не превышает 500-600 Мб. Но стоит внести изменения в конфигурацию — её размер становится больше 1 Гб. По крайней мере это справедливо для конфигураций УПП 1.3 (управление производственным предприятием) и КА 1.1 (комплексная автоматизация). В этом можно убедится, ну или опровергнуть, сохранив файл конфигурации. Если размер CF-ника свыше 1Гб — скорее всего мы не сможем воспользоваться pg_dump для создания архивной копии нашей БД.

Для выхода из этого положения необходимо снять с поддержки нашу конфигурацию. Обновления делать на файловой копии БД или SQL-копии, предварительно поставив на поддержку. После обновления копии нашей базы сохраняем конфигурацию и путём сравнения и объединения обновляем рабочую базу.

Восстановление базы данных из резервной копии (бэкапа)

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

Первым делом разархивируем файл:

unpigz -c /backup/everyday/YYYY-mm-dd_HH-MM-UPP.sql.gz > UPP.sql

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

# psql -U postgres -l

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

# createdb --username postgres -T template0 upp-temp

В созданную базу данных загрузим резервную копию:

psql -U postgres upp-temp < /backup/everyday/UPP.sql

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

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

Настроим регламентные операции в PostgreSQL для предотвращения падения производительности базы данных.

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

Оформим регламентные операции в виде скрипта и будем исполнять его по расписанию.

Создадим скрипт everyday-service-pgsql.sh:

mcedit /backup/everyday-service-pgsql.sh

Запишем в него следующее содержимое:

#!/bin/sh

# Записываем информацию о начале очистки БД в лог
echo «`date +»%Y-%m-%d_%H-%M-%S»` Start vacuum» >> /backup/service.log

# Выполняем очистку и анализ базы данных
/usr/bin/vacuumdb —full —analyze —username postgres —dbname UPP

# Записываем информацию об окончании очистки БД в лог
echo «`date +»%Y-%m-%d_%H-%M-%S»` End vacuum» >> /backup/service.log

# Ставим на паузу выполнение скрипта на 10 секунд
sleep 10

# Записываем информацию о начале переиндексации таблиц БД в лог
echo «`date +»%Y-%m-%d_%H-%M-%S»` Start reindex» >> /backup/service.log

# Переиндексируем таблицы базы данных
/usr/bin/reindexdb —username postgres —dbname UPP

# Записываем информацию об окончании переиндексации таблиц БД в лог
echo «`date +»%Y-%m-%d_%H-%M-%S»` End reindex» >> /backup/service.log

Дадим скрипту право на запуск:

chmod 0700 /backup/everyday-service-pgsql.sh

Добавим исполнение данного скрипта в расписание cron. Осуществлять запуск будем ежедневно после резервного копирования, в данном случае на 2 часа ночи:

# Исполнение скрипта регламентных операций
00 02 * * * root /backup/everyday-service-pgsql.sh