Как настроить автоматические резервные копии для PostgreSQL с использованием pgAgent в pgAdmin

Введение

pgAgent – это инструмент, используемый для планирования заданий для баз данных PostgreSQL. У него также более мощные возможности планирования, чем часто используемый cron, потому что он специально создан для обработки задач Postgres. Например, pgAgent может планировать несколько шагов без пакетного скрипта или без повторения команды. Важно отметить, что даже если у вас установлен pgAdmin, pgAgent должен быть загружен отдельно, специально как расширение для pgAdmin.

В этом руководстве вы установите pgAgent, настроите вашу базу данных для использования pgAgent на графическом интерфейсе пользователя pgAdmin (GUI), настроите pgAgent как службу и затем используете pgAgent для планирования задания, которое будет резервировать вашу базу данных.

Предварительные требования

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

• Сервер Ubuntu 22.04, настроенный с не-root пользователем с включенным sudo и брандмауэром. Вы можете сделать это, прочитав наше руководство по начальной настройке сервера.
• PostgreSQL установлен на вашем сервере. Вы можете настроить его, следуя нашему руководству по Установке и использованию PostgreSQL в Ubuntu 22.04. В процессе выполнения этого руководства не забудьте создать новую роль и базу данных, так как вам понадобится и то, и другое, чтобы подключить pgAdmin к вашему экземпляру PostgreSQL
• pgAdmin установлен с учетной записью. Вы можете сделать это, следуя нашему учебнику по Установке и настройке pgAdmin4 в режиме сервера на Ubuntu 22.04

Шаг 1 — Установка pgAgent

Как упоминалось ранее, pgAgent не настраивается автоматически при установке pgAdmin. Вы можете установить pgAgent из терминала, выполнив команду apt install и имя пакета pgagent, как в следующей команде:

sudo apt install pgagent

После установки pgAgent переходите к следующему шагу для настройки вашей базы данных на использование pgAgent в pgAdmin.

Шаг 2 — Настройка вашей базы данных для pgAgent

После выполнения предварительных требований pgAdmin настроен и готов к использованию. Вы можете настроить свою базу данных для использования pgAgent через pgAdmin. Откройте веб-браузер и перейдите к приложению pgAdmin по адресу http://ваш_домен. После входа в свою учетную запись перейдите к управлению деревом на панели слева. Найдите базу данных, которую вы создали, с названием sammy и разверните список. В этом списке будет опция с названием Расширения. Как только вы ее найдете, щелкните правой кнопкой мыши и выберите опцию Инструмент запросов:

pgAgent требует загрузки расширения в вашу базу данных перед его использованием в pgAdmin. Для этого напишите следующий запрос и щелкните на боковую стрелку ▶, обозначающую Выполнить, чтобы выполнить команду:

CREATE EXTENSION pgagent;

Этот шаг с функцией Инструмента запросов представлен в следующем примере:

Во вкладке Сообщения будет вывод, возвращающий Запрос успешно выполнен за 300 мс. Это подтверждает успешное создание расширения pgAgent.

Output
ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.
SQL state: 42704

CREATE LANGUAGE plpgsql;

После выполнения этих запросов в разделе Расширения будут перечислены два элемента: pgagent и plpgsql:

A new item in the tree control on the left-hand side will appear called pgAgent Jobs. This signifies that pgAgent was successfully installed on your pgAdmin account. Next, you will set up pgAgent as a daemon so that it can run your jobs successfully.

Шаг 3 — Настройка pgAgent в качестве демона

Теперь, когда у вас установлен и настроен pgAgent для вашей базы данных через pgAdmin, следующим шагом будет настройка pgAgent в качестве демона на вашем сервере. Демон – это программа, которая работает в фоновом режиме и выполняет служебные задачи. pgAgent работает как демон в системах Unix и, как правило, на самом сервере базы данных.

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

Ваша строка подключения будет содержать учетные данные вашего хоста, имени базы данных и имени пользователя. В нашем примере хост будет использовать сокет Unix, имя базы данных – sammy, а пользователь – sammy. Эта строка будет добавлена к команде pgagent для запуска демона. В вашем терминале вы выполните следующий код:

pgagent host=/var/run/postgresql dbname=sammy user=sammy

Если в выводе ничего не возвращается и вы не получаете сообщение об ошибке подключения, значит настройка строки подключения прошла успешно.

После создания строки подключения вы готовы планировать задание с помощью pgAgent.

Шаг 4 — Планирование задания с pgAgent

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

Для целей этого руководства вы будете использовать pgAgent для создания задания, которое будет резервировать вашу базу данных sammy каждую минуту каждый день недели. Вы можете начать с правого щелчка по Задания pgAgent и выбора Создать, а затем Задание pgAgent…, как показано ниже:

Как только вы это сделаете, появится приглашение с заголовком Создание – задача pgAgent, и вы сможете начать заполнение необходимой информации на вкладке Общее. В этом примере мы используем имя sammy_backup и не указываем Хост агента, поскольку мы хотим иметь возможность запускать эту задачу на любом хосте. Кроме того, мы оставим Класс задачи как Регулярное обслуживание. Если вы хотите добавить какие-либо комментарии, не стесняйтесь делать это в разделе Комментарий:

Затем перейдите на вкладку Шаги. Нажмите на символ + в верхнем правом углу, чтобы создать шаг. В этом примере мы назовем этот шаг шаг1. Затем, чтобы расширить ваши возможности, нажмите на карандаш слева от значка корзины. Кнопка Включено? по умолчанию включена и означает, что этот шаг будет включен при выполнении этой задачи.

Для опции Вид вы можете выбрать либо SQL, либо Пакет; здесь мы выбрали Пакет. Причина выбора в данном примере Пакета заключается в том, что именно он выполнит соответствующие команды PostgreSQL, которые вы установите для резервного копирования вашей базы данных. Опция SQL доступна для планирования выполнения задания для выполнения непосредственно SQL-запроса. В данном случае мы выбрали Локальное для Типа подключения, чтобы шаг выполнялся на локальном сервере, но при желании вы также можете выбрать Удаленное для удаленного хоста по вашему выбору. Если вы предпочтете выполнить это на удаленном хосте, вам нужно будет указать это в поле Строка подключения. Если вы следовали Шагу 1, ваша строка подключения уже настроена и подключена.

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

В пределах того же раздела Шаги также есть вкладка Код. Если вы выбрали Пакетный режим, как мы сделали в этом примере, перейдите на эту вкладку Код. Когда вы окажетесь на этой вкладке, у вас будет пустая строка для вставки команды PostgreSQL. Здесь вы можете заменить свою собственную команду резервного копирования своим набором настроек. Любая допустимая команда подходит.

В этом руководстве будет использоваться команда pg_dump для резервного копирования вашей базы данных Postgres sammy. В этой команде укажите свое имя пользователя, имя базы данных и флаг --clean, который помогает pg_dump удалять или “очищать” объекты базы данных перед выводом каких-либо создаваемых команд. Для флага --file вы указываете точное местоположение, где будут сохранены файлы резервных копий. Финальная часть этого выражения date +%Y-%m-%d-%H-%M-%S предназначена для динамической генерации даты и нескольких файлов для каждой резервной копии. В противном случае файл резервной копии будет постоянно перезаписываться и сохраняться поверх существующего. Таким образом, вы сможете отслеживать каждый из ваших файлов резервных копий для любого указанного времени или даты, которую вы запланировали. Ваша полная команда будет следующей:

pg_dump --username=sammy --dbname=sammy --clean --file=/home/sammy/backup-`date +%Y-%m-%d-%H-%M-%S`.sql

Как только вы добавите свою команду резервного копирования, вы можете перейти на вкладку Графики. Аналогично настройке Шагов, нажмите на символ +, чтобы добавить график, затем укажите предпочтительное имя и нажмите на значок карандаша рядом с значком корзины для раскрытия ваших вариантов. Под вкладкой Общее будет Имя, которое вы написали, в этом примере, это график1. Снова для Включено это установлено по умолчанию на включение, чтобы график был правильно выполнен. Для опций Начало и Конец укажите начальный и конечный день и время для вашей запланированной задачи. Поскольку вы будете тестировать свою запланированную задачу, убедитесь, что текущее время находится в диапазоне Начало и Конец. Добавьте примечание в Комментарий, если хотите:

Теперь перейдите на вкладку Повтор. Здесь вы можете настроить частоту выполнения этой запланированной задачи. Вы можете быть максимально конкретными в определении недели, месяца, даты, часов или минут. Обратите внимание, что если вы не сделаете выбор, это то же самое, что выбрать Выбрать все. Поэтому, если вы оставите пустыми Дни недели, ваш график учтет все дни недели. Аналогично, с Временем, вы можете оставить пустыми часы или минуты, и это то же самое, что и Выбрать все. Имейте в виду, что времена указываются в формате cron, поэтому для этого примера, чтобы создать резервную копию каждую минуту, вы должны выбрать каждую минуту в часу (от 00 до 59). Чтобы продемонстрировать это, мы выбрали Выбрать все для минут. Все минуты перечислены, но оставить их пустыми также даст те же результаты:

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

Когда вы закончите настройку и настройку расписания, которое вы хотите выполнить, нажмите кнопку Сохранить. На дереве управления слева появится новая задача pgAgent с именем вашей задачи. В этом примере sammy_backup появляется с Расписаниями и Шагами, перечисленными под ним:

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

Шаг 5 — Проверка вашей задачи pgAgent

Вы можете проверить, работает ли ваша запланированная задача по созданию резервной копии вашей базы данных каждую минуту, несколькими способами. В pgAdmin вы можете перейти к дереву управления слева и щелкнуть sammy_backup. Затем перейдите на вкладку Статистика. На странице Статистика будут перечислены каждый экземпляр вашей запланированной задачи следующим образом:

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

Если вы предпочитаете проверить работу своей задачи из командной строки, вы можете выполнить команду ls ~, чтобы просмотреть файлы в вашем домашнем каталоге. В этом руководстве вы настроите точное расположение этого домашнего каталога при написании команды pg_dump в Шаге 4:

ls ~

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

Шаг 6 — Изменение, Отключение, Удаление и Ручное выполнение задач pgAgent (По желанию)

pgAgent предоставляет гибкость при изменении или отключении задачи. Используя pgAdmin, вы можете перейти к конкретной задаче pgAgent, в данном случае, sammy_backup. Затем щелкните правой кнопкой мыши и выберите опцию Свойства из списка. Здесь вы можете нажать на значок карандаша сверху, чтобы внести изменения в свою задачу:

Если вы находитесь в Свойствах и нажмете на значок карандаша, вы можете перейти на вкладку График, чтобы отключить вашу задачу, как показано ниже:

Кроме того, в дереве управления слева, если вы щелкнете правой кнопкой мыши на задании pgAgent, вы можете выбрать УДАЛИТЬ/ОТМЕНИТЬ, чтобы полностью удалить задание. Если вы хотите выполнить задание вручную, вы можете повторить тот же шаг, но на этот раз выбрать ЗАПУСТИТЬ СЕЙЧАС из списка:

Хотя это только несколько функций для использования pgAgent для планирования заданий, существует много других возможностей. Например, вы можете захотеть запланировать задание, которое создает резервную копию всех пользователей и ролей в вашей базе данных, специально с помощью команды pg_dumpall --globals only. Вы даже можете использовать более сложные сценарии с пакетными заданиями, о которых вы можете узнать больше на странице вики PostgreSQL.

Вывод

pgAgent полезен для планирования различных задач, которые могут помочь сократить некоторые из ваших более однообразных, но необходимых повседневных задач, таких как регулярное создание резервных копий вашей базы данных. Как только вы станете более уверенны в использовании этого инструмента, вы можете даже попробовать создать многоэтапное задание или SQL-задачи, требующие сложного планирования. Если вы хотите узнать больше, вы можете прочитать официальную документацию pgAgent на веб-сайте pgAdmin.