PostgreSQL для 1С 8.3: ускоряем резервное копирование и восстановление для отдельной базы очень большого размера

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

Задача 1. Копирование базы 1С на лету во время работы пользователей с сохранением целостности с помощью команд pg_dump.exe, psql.exe

Для этого в общем случае алгоритм следующий:

Шаг 1. Выгружаем с помощью pg_dump.exe все таблицы из базы данных, кроме данных таблицы config (т.е. для config выгружаем только ее схему)

Шаг 2. Выгружаем с помощью psql.exe данные таблицы public.config с помощью COPY WITH BINARY

Итак,

- Шаг 1. Выгружаем с помощью pg_dump.exe все таблицы из базы данных, кроме данных таблицы config (т.е. для config  выгружаем только ее схему):

"<путь к pg_dump>\pg_dump.exe" и далее параметры с комментариями

--host <ip адрес или имя хоста сервера>

--port <порт>

--username "postgres" – имя пользователя

--role "postgres" - роль

--no-password – не спрашивать пароль в пакетном режиме

--format directory – создавать архив в виде каталога для использования параметра --jobs. При этом каждая таблица копируется в отдельный файл в каталоге, что позволяет распараллелить процесс создания архива

--jobs=<количество параллельных потоков процессора> - подбирается примерно как <количество ядер процессора>*2, можно пробовать увеличение или уменьшение параметра, чтобы максимально загрузить систему, не мешая работе пользователей. Практически на каждый процесс запускается копирование одной таблицы из базы данных. Сколько процессов задействовано, столько таблиц и обрабатывается одновременно. С помощью этого параметра можно достигнуть ускорения резервного копирования в 4 раза и более в зависимости от мощности оборудования сервера.

--blobs – позволяет выгружать поля большого размера

--encoding UTF8 - кодировка

--verbose – включить подробное комментирование

--exclude-table-data=config - исключить из выгрузки данные таблицы config, т.е. выгрузить только ее схему (config содержит записи: конфигурация, конфигурации поставщиков, отличия основной конфигурации от конфигураций поставщиков). Это требуется, когда база находится на поддержке у двух и более конфигураций поставщика и (или) очень много изменений внесено в конфигурацию. При этом размер изменений основной конфигурации относительно конфигурации одного из поставщиков приближается к 1Гб, что является пределом для поля большого размера в PostgreSQL. А 1С хранит изменения только в одной из записей таблицы config. При небольшом размере конфигурации можно не использовать этот параметр. Но при критическом (если размер хотя бы одной записи таблицы public.config (конфигурации) после чтения и распаковки в стандартный поток вывода stdout превысит 1 Гб) pg_dump.exe завершится с ошибкой:

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

Если используется --format custom, то архив выгружается в виде одного файла, и ошибка создания архива на таблице public.config обнаружится при выполнении команды pg_restore, что и есть самое неприятное:

pg_restore: обрабатываются данные таблицы "public._usersworkhistory" 
pg_restore: обрабатываются данные таблицы "public._yearoffset" 
pg_restore: обрабатываются данные таблицы "public.config" 
pg_restore: [внешний архиватор] не удалось прочитать входной файл: конец файла

(кроме того --format custom не позволит использовать --jobs - распараллеливание)

Наблюдается на больших конфигурациях KA 1.1-2.4, УПП 1.3, ERP 2.4.

--file "<имя каталога архива без таблицы config>"

"<имя базы данных>"

Шаг 2. Выгружаем с помощью psql.exe данные таблицы public.config с помощью COPY WITH BINARY:

md "<имя каталога архива только с таблицей config >" - создаем каталог для таблицы config

"<путь к psql>\psql.exe" – далее параметры

--host <ip адрес или имя хоста сервера>

--port <порт>

--username "postgres" – имя пользователя

--no-password – не спрашивать пароль в пакетном режиме

--command "COPY public.config TO '<имя каталога архива только с таблицей config с разделителями \\ для винды>' WITH BINARY;"

--dbname="<имя базы данных>"

Задача 2. Восстановление базы 1С в копию во время работы пользователей с сохранением целостности с помощью команд pg_restore.exe, psql.exe. Для этого в общем случае алгоритм следующий:

Шаг 0. Создаем пустую копию базы средствами pgAdmin.exe или с помощью psql.exe, dropdb, createdb.

Шаг 1. Загружаем с помощью pg_restore.exe все таблицы из архива базы данных, кроме данных таблицы config (т.е. для config  загружаем только ее схему)

Шаг 2. Загружаем с помощью psql.exe данные таблицы public.config с помощью COPY WITH BINARY

Шаг 0. Создаем пустую копию базы средствами pgAdmin.exe или с помощью psql.exe, dropdb, createdb.

Если база данных существует, мы должны сначала удалить ее из кластера с помощью команды DROP DATABASE "<имя базы данных>", а затем создать заново с помощью команды CREATE DATABASE "<имя базы данных>". Проще всего это сделать через pgAdmin, так как в нем есть запрос на удаление базы и образец запроса на создание базы. При удалении базы необходимо закрыть все соединения с базой, иначе база не будет удалена. Для Windows запрос на создание базы выглядит так:

CREATE DATABASE "<имя базы данных>"

WITH

OWNER = postgres

ENCODING = 'UTF8'

LC_COLLATE = 'Russian_Russia.1251'

LC_CTYPE = 'Russian_Russia.1251'

TABLESPACE = pg_default

CONNECTION LIMIT = -1;

Если мы время от времени хотим проверять, что база корректно достается из архива, то имеет cмысл автоматизировать шаг 0 в батнике командами psql, dropdb, createdb (добавлено по просьбам читателей):

0.1 Перед удалением закрываем все активные соединения с базой <имя базы данных>, кроме текущего:

"<путь к psql>\psql.exe" – далее параметры

--host <ip адрес или имя хоста сервера>

--port <порт>

--username "postgres" – имя пользователя

--dbname="<имя базы данных>"

--no-password – не спрашивать пароль в пакетном режиме

--command "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '<имя базы данных>' AND pid <> pg_backend_pid();"

0.2 Удаляем базу <имя базы данных>, если она существует без подтверждения об удалении.
"<путь к dropdb>\dropdb.exe"

--host <ip адрес или имя хоста сервера>

--port <порт>

--username "postgres" – имя пользователя

--no-password – не спрашивать пароль в пакетном режиме

--if-exists - проверка существования базы

--echo - вывод команд SQL, которые выполнит postgreSQL при вызове

"<имя базы данных>" 

0.3 Создаем заново базу <имя базы данных> после удаления
"<путь к createdb>\createdb.exe"

--host <ip адрес или имя хоста сервера>

--port <порт>

--username "postgres" – имя пользователя

--no-password – не спрашивать пароль в пакетном режиме

--echo - вывод команд SQL, которые выполнит postgreSQL при вызове

--owner="postgres" - владелец базы

--encoding="UTF8" - кодировка

--locale="Russian_Russia.1251" - устанавливает одновременно параметры LC_COLLATE и LC_CTYPE для базы данных.

--tablespace="pg_default" - указывает табличное пространство, используемое по умолчанию.

"<имя базы данных>"

Теперь уже не нужно даже лезть в pgAdmin и там корячиться.

Шаг 1. Загружаем с помощью pg_restore.exe все таблицы из архива базы данных, кроме данных таблицы config (т.е. для config  загружаем только ее схему):

"<путь к pg_restore>\pg_restore.exe"

--host <ip адрес или имя хоста сервера>

--port <порт>

--username "postgres" – имя пользователя

--role "postgres" - роль

--no-password – не спрашивать пароль в пакетном режиме

--dbname "<имя базы данных в которую восстанавливаем>"

--jobs=<количество параллельных потоков процессора> - подбирается примерно как <количество ядер процессора>*2, можно пробовать увеличение или уменьшение параметра, чтобы максимально загрузить систему, не мешая работе пользователей. Практически на каждый процесс запускается восстановление одной таблицы базы данных. Сколько процессов задействовано, столько таблиц и индексов обрабатывается одновременно. С помощью этого параметра можно достигнуть ускорения восстановления базы в 2-3 раза и более в зависимости от мощности оборудования сервера.

--verbose – включить подробное комментирование

"<имя каталога архива без таблицы config>"

Шаг 2. Загружаем с помощью psql.exe данные таблицы public.config с помощью COPY WITH BINARY

"<путь к psql>\psql.exe" – далее параметры

--host <ip адрес или имя хоста сервера>

--port <порт>

--username "postgres" – имя пользователя

--dbname="<имя базы данных>"

--no-password – не спрашивать пароль в пакетном режиме

--command "\COPY public.config FROM '<имя каталога архива только с таблицей config с разделителями \\ для винды>' WITH BINARY;"

Обратите внимание на метакоманду \COPY вместо просто COPY.

\COPYоткрывает файл и передает содержимое на сервер, тогда как COPY сообщает серверу, что он сам открывает файл и читает его, что может быть проблематичным по разрешению или даже невозможно, если клиент и сервер работают на разных компьютерах без совместного доступа к файлам между ними.

Соответственно при использовании просто COPY может выскочить следующая ошибка:

Postgres ERROR: Permission denied (не удалось открыть файл для чтения)

Заметим, что архив базы может быть успешно восстановлен на последующих версиях  PostgreSQL. В моем случае, я переносил базу с сервера PostgreSQL 9.6 на PostgreSQL 10.3.

Все проходило гладко. Трудности теоретически могут возникнуть в команде  \COPY, т.к. она является платформо-зависимой.

На реальной базе размером 380 Гб за счет распараллеливания было достигнуто ускорение при работе pg_dump - в 4 раза, при работе pg_restore - в 2,5 раза, что весьма существенно, когда процесс занимает несколько часов.

Было: 2 часа на копирование, стало 0,5 часа на копирование; было 10 ч на восстановление, стало 4 ч на восстановление.

После перехода на более мощный сервер получили еще более существенные результаты:
Было: 2 часа на копирование, стало 0,5 часа на копирование; было 10 ч на восстановление, стало 1 ч  40 мин на восстановление. То есть количество ядер процессора и более быстрые диски при тех же параметрах дают значительное ускорение. Теперь чтобы скопировать и развернуть нашу огромную базу требуется всего 2 часа!

Ниже привожу примеры bat-файлов (качайте) для создания архивной копии базы данных DO_PROBA и восстановления в другую базу данных DO_PROBA_COPY. При этом в названии каталогов архива используется дата и время начала архивации и выводятся замеры времени на создание-восстановление. При восстановлении из вновь созданного архива необходимо каждый раз менять дату и время в bat-файле для восстановления (ее можно скопировать из имени каталога архива по образцу). Теперь будьте очень осторожны при восстановлении базы. По просьбам читателей и пользователей в bat-файлы добавлены команды автоматического удаления и создания восстанавливаемой базы в случае ее существования. Не ошибитесь в наименовании базы в команде SET ar_base_to=<Имя базы, куда восстанавливаем> !!! Иначе можно легко порушить существующие базы. 

СОВЕТ 1: периодически проверяйте (хотя бы раз в неделю) загрузку бэкапа в какую-нибудь тестовую базу и запускайте для проверки работоспособности режим конфигуратора 1С и рабочий режим. Тогда всегда будете уверены в своей архивной копии!

СОВЕТ 2: после отладки копирования и восстановления можно настроить Планировщик заданий (для Windows) на запуск нашего bat-файла по выбранному расписанию. Например, в 3:00 ночи ежедневно, пока никто из пользователей не работает.

*********

С 4 по 6 февраля 2019 года в стенах Московского государственного университета состоится конференция по PostgreSQL – PGConf.Russia 2019. Ежегодно она собирает более 500 разработчиков, администраторов баз данных и IT-менеджеров для обмена опытом и профессионального общения.

На этот раз PGConf.Russia будет особенной. Инфостарт совместно с Postgres Pro организует на конференции секцию «Postgres+1C». Мы приглашаем участников сообщества посетить PGConf и даже выступить в качестве докладчика.