Копирование-восстановление с помощью моментального снимка базы может потребоваться в разных случаях:
- копирование в другую базу в пределах кластера
- копирование в другую базу на другом сервере с более высокой версией 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 и даже выступить в качестве докладчика.
