Записки IT специалиста

Технический блог специалистов ООО»Интерфейс»

• Главная
• Резервное копирование баз данных PostgreSQL.

Резервное копирование баз данных PostgreSQL.

Мы не будем напоминать о важности резервного копирования данных, об этом немало сказано, а поговорим о практической реализации одного из сценариев. Сегодня в фокусе нашего внимания будет популярная бесплатная СУБД PostgreSQL. Актуальности данному вопросу добавляет тот факт, что PostgreSQL активно используется для хранения информационных баз системы 1С:Предприятие.

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

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

Итак, в нашем распоряжении имеется сервер СУБД на базе Ubuntu Server, где расположены базы 1С:Предприятия, наша задача обеспечить автоматическое резервное копирование в соответствии с заданными условиями.

Прежде всего настроим авторизацию для СУБД. Так как основные операции должны будут производится из скрипта, то имеет смысл разрешить локальный доступ к СУБД без авторизации. Учитывая, что доступ к серверу БД имеет ограниченный круг лиц и расположен он в периметре сети, безопасность пострадает слабо.

Откроем файл pg_hba.conf, он находится в /var/lib/pgsql/data и приведем к следующему виду строку:

На платформе Windows данный файл находится в C:\Program Files\PostgreSQL\Версия_СУБД\data и строка будет иметь несколько иное содержание:

Для создания резервной копии воспользуемся утилитой pg_dump, которая позволяет создать дамп для указанной БД. Создание дампа происходит без блокирования таблиц и представляет снимок БД на момент выполнения команды. Т.е. вы можете создавать дампы во время работы пользователей, в то время как для создания резервной копии средствами 1С вам нужен монопольный доступ к базе.

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

Кроме списка баз вывод содержит ряд полезной информации, например о кодировке базы, данная информация пригодится нам при восстановлении БД на другом сервере.

Теперь, уточнив наименование баз на сервере создадим резервную копию базы unf14:

результатом выполнения команды будет файл дампа в домашней директории. Расширение файла мы рекомендуем указывать таким образом, чтобы по нему было понятно назначение данного файла и оно может быть любым. В нашем случае мы используем pgsql.backup, глянув на такой файл сразу станет понятно о его назначении, это может быть важно, если поиском дампов будут заниматься ваши коллеги. Также мы не рекомендуем использовать расширение .bak, потому что многие утилиты «для оптимизации» удаляют такие файлы.

При необходимости можем создать сжатый дамп:

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

Теперь рассмотрим процедуру восстановления. Для примера будем использовать сервер под управлением Windows. Никаких существенных особенностей по работе с PostgreSQL на разных платформах нет. Однако под Windows следует вместо psql использовать psql.bat и указывать полный путь к утилитам C:\Program Files\PostgreSQL\Версия_СУБД\bin, либо добавить этот путь в системную переменную PATH.

Еще одно важное замечание. Кодировка исходного и целевого серверов должна совпадать, иначе вы после восстановления получите нерабочую базу. На платформе Linux СУБД обычно работает в кодировке UTF8, в то время как сборка PostgreSQL от 1С на Windows по умолчанию устанавливается в кодировке WIN1251.

Для 1С:Предприятия типичным симптомом того, что вы залили UTF8 базу на сервер с WIN1251 является невозможность авторизоваться в ИБ.

Перед восстановлением дампа следует создать целевую БД (при ее отсутствии), хотя мы рекомендуем делать это всегда. Еще одна БД есть не просит, зато избавляет от распространенной ситуации, когда залили не тот дамп или не в ту базу. Для создания базы выполним:

Теперь зальем полученный дамп в только что созданную базу unf14:

На платформе Линукс эта команда будет выглядеть так:

В нашем примере файл дампа находится в C:\backup и домашней директории соответственно.

Все что теперь остается, это через оснастку Администрирование сервера 1С:Предприятия создать новую ИБ или изменить настройки существующей, указав на новый сервер СУБД и новую базу.

С основными командами мы разобрались и убедились, что ничего сложного в процессе резервного копирования и восстановления баз PostgreSQL нет. Но не будем же мы создавать бекапы вручную. Поэтому перейдем к автоматизации. Создадим скрипт, который будет создавать резервные копии указанных баз и размещать их на FTP-сервере. В силу определенных различий между платформами, создать универсальный скрипт для Windows и Linux не получится, поэтому рассмотрим каждую платформу отдельно.

Начнем с Linux, в нашем случае это Ubuntu Server. Создадим файл скрипта:

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

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

Также не забудем создать каталог /root/backup

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

Для платформы Windows все несколько сложнее, так как встроенный архиватор отсутствует, то следует воспользоваться сторонним решением, в нашем случае будет использоваться 7zip, также нужно указывать полные пути к бинарным файлам или добавить их в переменную PATH, мы будем задавать эту переменную динамически в скрипте. Еще одна сложность связана с использованием встроенного ftp-клиента, набор команд для него необходимо подготовить в виде отдельного файла.

Создадим в Блокноте новый файл и разместим там нижеприведенный текст:

Скрипт также довольно прост для понимания и повторяет по структуре и логике скрипт для Ubuntu. Задаем переменные, устанавливаем рабочую директорию и выгружаем туда дамп, затем создаем архив. Следующим шагом формируем файл с командами для FTP-соединения, загружаем архив на FTP и делаем уборку.

Файл следует сохранить как pgsql-backup.bat и разместить в удобном месте. Затем настроить его выполнение по расписанию через Планировщик задач Windows. Также не забудьте создать директорию C:\backup (или любую другою, которую вы хотите использовать в качестве рабочей).

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

Помогла статья? Поддержи автора и новые статьи будут выходить чаще:

Или подпишись на наш Телеграм-канал:

Создание бэкапа базы PostgreSQL для Windows

В PostgreSQL есть утилита, которая создает дамп базы данных и называется она pg_dump. Для того чтобы автоматизировать процесс создания бэкапов баз PostgreSQL нужно будет создать bat-файл, который будет вызывать утилиту pg_dump и вызывать его с помощью планировщика заданий. Результатом выполнения такого сценария будет ежедневное копирование базы данных PostgreSQL, ведение журнала с информацией о датах и результатах выполнения, сохранение подробных сведений о ходе выполнения каждой резервной копии в отдельный текстовый файл и в случае неудачи отображение диалогового окна с сообщением. Содержимое bat-файла следующее:

Справочную информацию о командах, испульзуемых в этом файле можно получить из командной строки набрав следующую команду: «[Имя команды] /?»
Многие использованные здесь команды достаточно распространены и известны, поэтому хочется акцентировать внимание на нескольких менее известных.

Строки 15, 16 выполняют переход в папку в которой находится файл «backup.bat». «%0» возвращает имя bat-файла; «%

dp0″ возвращают соответственно диск и путь к bat-файлу. Подробные сведения о работе с параметрами файла можно посмотреть по этой ссылке.

В строке 19 формируется строковое представление даты и времени в нужном формате. При формировании происходит обращение к переменным окружения DATE и TIME, которые хранят текстовое представление даты и времени соответственно. После имени переменной указывается строка вида «:

m,n», где m — позиция в строке, n — количество символов.

В строке 27 вызывается утилита резервного копирования pg_dump.exe. Вызов выполняется с применением команды CALL, это позволяет дождаться завершения утилиты и проанализировать результат выполнения. Вызов утилиты завершается строкой «2>%LOGPATH%». Эта строка означает что поток ошибок STDERR, номер которого 2, приложения pg_dump.exe перенаправляется в файл, имя которого сохранено в переменной окружения LOGPATH. Так как приложение pg_dump.exe выводит все сообщения в стандартный поток ошибок, то в файле LOGPATH будет сохранен подробный отчет о выполнении резервного копирования.

В строках 37 и 42 выполняется перенаправление вывода в файл backup.log. Перенаправление осуществляется оператором «>>». Различие между операторами «>» и «>>» в том, что первый каждый раз создает новый файл, затирая ранее записанные данные, а второй — дописывает данные в существующий файл. Таким образом можно вести журнал с подробными сведениями о результатах резервного копирования.

Проверяем как работает bat-файл. Если дампы базы создаются, то можно приступать к созданию задачи для планировщика заданий Windows.
Создаем задание, которое будет запускать bat-файл каждый день в ночное время.

Ежедневные бэкапы со временям породят проблему свободного пространства на жестком диске. Можно чистить ручками, но лучше уж автоматизацию сделать полной. Решается этот вопрос также созданием bat-файла и задачи в планировщике заданий Windows.

Содержимое bat-файла такое:

Здесь указана команда при выполнении которой будут удаляться файлы старше 5 дней.
В планировщике заданий можно создать задачу на исполнения этого bat-файла раз в неделю.

Automated Backup on Windows

Contents

Automated Backup on Windows

Updated to reflect changes from 8.3 to 11

• This method uses pg_dump.exe along with a batch file to call it. This batch file will create a file/directory for each day it is run.

• Keep in mind pg_dump and pg_dumpall are version specific meaning do not use pg_dump from 9.3 to backup version 11.0. The option -i & —ignore-version are ignored

Files needed to run pg_dump & pg_dumpall

• Getting the pg_dump, pg_dumpall binaries one has to extract it from a PostgreSQL Server Install, compile, or download binaries from EDB. There is no package available to get just these files.

• Should download and install the Windows C/C++ runtime libraries from Microsoft for the version Postgresql being used, version 11.0 uses VS-2013

• Go to backup server/location create a Directory called Drive:\PostgresqlBack then create a sub directory called «bin» in the Drive:\PostgresqlBack and place the following files in this directory.

Using pgdump, creates a new file for each day

• Create batch file called something, example is postgresqlBackup.bat. The file must be located in PostgresqlBack directory not the bin folder.

• Open the File then Copy/Paste the following

• Change to something. One idea is use the name of the database.(make sure there are no spaces after the word BACKUP_FILE any spaces will cause this setting not to work.)Setting is the first part of the file name then followed by the date the file was created with the extension .backup

• Change the

  setting above to the correct password for the backup users. (make sure there is no spaces after the word PGPASSWORD any spaces will cause this setting not to work. Description of pgPassword

• Change either to ip address or dns name of the server hosting Postgresql.

• Change to backup user make sure this users has access to database for backup purposes

• Change to the database name being backed up.

• Save the File

• Create Task for the MS Task Scheduler

• Once you have chosen the security context the Task in going to run in, it is advised to change the directory security where the backup is run and the files are stored, as a high level user name and password are stored in plain text.

• Another option is to modify the pg_hba.conf file adding the backup server as a trusted connection

New feature in pg_dump as of 9.1

The option -F d. This changes the backup from creating a single large TAR file, it will now will create a directory and contain individual files for each table.

the pg_dump command looks like this

There are a few advantages to the -F d option. One restores can be made significantly faster as pg_restore has the option to run parallel connections instead of one table at a time. Two its faster to extract specific table(s) to restore when using this option vs TAR format. Three when coping/moving the files off-site and it fails in mid-stream it does not have to restart at the beginning unlike one large TAR file, for large databases this is a big plus.

Using pg_dumpall

It is necessary to use pg_dumpall to get the logins/roles, and schema information as pg_dump does not include this information

This creates a sql script used to restore roles and schema information. Keep in mind if not restoring to the same server settings such TableSpaces are most likely not valid and will error.