Введение

Какие задачи решались

1. Выполнение резервного копирования отдельных баз данных на сервере БД

2. Выгрузка каждой базы по отдельности в S3 хранилище

3. Очистка хранилища от старых бэкапов

4. Восстановление базы в специально созданную архивную с помощью бота

5. Интерфейс управления восстановлением из бэкап

История создания PSQLBuddy

Когда-то давно, еще сидя на виртуалках, я сделал чистый shell скрипт, который выполняет плюс-минус те же самые задачи. Спустя несколько лет, когда возникла необходимость снова сменить сервер, мне захотелось переписать это все на python и нормальные библиотеки, чтобы по-человечески запустить это все в маленьком уютном окружении и добавить возможность пользователю восстанавливать базы в архив без моего участия.
Так родилась идея PSQLBuddy
Изначально я реализовал две утилиты - одну для создания бэкапов, а другую для разворачивания бэкапа в архив. Но в итоге слил их в одно целое, сделав возможным запуск утилиты с аргументами --backup & --restore. О них детально ниже.

Минусы pg_dump, pg_dumpall, pg_restore

При использовании pg_dump для регулярного резервного копирования каждой базы данных, необходимо создавать отдельные задачи в cron. Этот подход требует значительных усилий по управлению, особенно если в системе имеется множество баз данных. Каждая задача требует указания множества атрибутов, таких как форматы вывода, параметры подключения и уровни сжатия, что увеличивает вероятность ошибок и усложняет администрирование.

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

Плюсы PSQLBuddy

PSQLBuddy - более эффективный и удобный подход к резервному копированию баз данных PostgreSQL. Внутри создается простой конфиг с настройками целевого хранилища, баз данных для резервного копирования и количество хранимых бэкапов. Утилита PSQLBuddy организует выгрузку данных в S3-хранилище, а также удаление файлов на сервере после успешного завершения операции. Предусмотрены механизмы очистки S3-хранилища в соответствии с заранее определенными правилами хранения резервных копий.

Как это работает

Утилита PSQLBuddy устанавливается на сервер с PostgreSQL.

В зависимости от необходимого функционала PSQLBuddy запускается либо в режиме --backup (по расписанию), либо в режиме --restore (висящий процесс с ботом).

Запуск в режиме --backup

Начинают последовательно обрабатываться все базы данных добавленные в конфиге.

Первым делом формируется название для бэкапа. Название состоит из трех частей и формируется по следующему шаблону: <НАЗВАНИЕ БД>-<год_месяц_день>-<ТИП БЭКАПА>.dump.

Настоящий пример: volleyball_db-2024_09_19-DAILY.dump

И если с первым и вторым все более-менее понятно, то тип бэкапа требует некоторых пояснений. Всего типов четыре - YEARLY, MONTHLY, WEEKLY, DAILY.

• YEARLY - создается только первого января

• MONTHLY - первый день месяца

• WEEKLY - первый день недели

• DAILY - каждый день

Стоит отметить, что создается только один тип резервной копии в день. То есть, если сегодня первый день месяца, но при этом первый день недели - то создастся все равно тип MONTHLY.

Затем выполняется создание бэкапа командой pg_dump, запущенной в subprocess.

pg_dump -U postgres -F c -d <ИМЯ БД> -f <ПАПКА temp>/<НАЗВАНИЕ БЭКАПА>

После создания бэкап выгружается в S3 хранилище и удаляется из папки temp, запускается бэкап следующей базы в списке.

Когда все бэкапы сделаны, выгружены, PSQLBuddy запрашивает список хранящихся в S3-бакете бэкапов, разбивает их по базам и типам бэкапов. Затем проходится по каждой базе и каждому типу бэкапа, которые для нее есть. Если количество бэкапов типа DAILY для базы volleyball_db - 8, а в конфиге мы указали, что DAILY бэкапов для этой базы надо хранить 7 штук, то лишний (самый старый) бэкап удаляется.

Запуск в режиме --restore

Запускается телеграм-бот, который отвечает только пользователям указанным в конфиге. Ниже иллюстрация незамысловатой работы этого бота.

На команду /start происходит обращение в S3 хранилище и получение списка резервных копий. Из этого списка вытаскиваются все базы данных и в ответном сообщении предлагается выбрать одну из них. Проверяется корректность ввода и запрашивается подтверждение восстановления бэкапа, после чего, собственно, последовательно происходит:

• скачивание копии из хранилища

• удаление базы данных archive

• создание базы данных archive

• восстановление скачанного бэкапа в базу данных archive командой pg_restore -U postgres -d archive <ПАПКА TEMP>/<НАЗВАНИЕ БЭКАПА>

Установка и запуск

Предварительная подготовка

Предполагается, что у вас уже установлен python. Проверить это вы можете командой python -v

Запускать PSQLBuddy будем из под пользователя postgres, который по умолчанию присутствует в системе с установленным postgresql. Обычно домашняя директория для пользователя postgres находится по адресу /var/lib/postgresql/ поэтому в дальнейшем будем считать, что у нас все лежит в /var/lib/postgresql/PSQLBuddy
Переходим в целевую папку и копируем все из репозитория и переходим в скачанную папку

cd /var/lib/postgresql/ 
git clone https://github.com/dmitrymp3/PSQLBuddy.git
cd PSQLBuddy/

Создаем директорию для временных файлов (она же temp_path в конфиге)mkdir temp

Можем приступать к созданию окружения и следом активируем его.

python -m venv venv
source venv/bin/activate

После чего мы должны увидеть в начале строки (venv).

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

pip install -r requirements.txt

Заполнение конфига

Переименовываем конфиг в папке и открываем его на редактирование редактором vi, nano, или любым другим

mv conf/config.sample conf/config.py
nano conf/config.py

Здесь мы обращаем внимание на следующие настройки:

databases - экземпляр класса AllDatabases(). Должен быть инициализирован в классе CommonConfig (в образце конфига все уже сделано, только добавьте свои базы). После инициализации у экземпляра класса доступен метод установки значений по умолчанию:

databases.set_default_freq({
    'DAILY'     : 4,
    'WEEKLY'    : 4,
    'MONTHLY'   : 4,
    'YEARLY'    : 999,
})

А дальше можно добавлять базы следующим образом:

databases.add_database('volleyball_db')
databases.add_database('basketball_db', {'WEEKLY': 10})
databases.add_database('super_db', {
    'DAILY'     : 44,
    'WEEKLY'    : 22,
    'MONTHLY'   : 11,
    'YEARLY'    : 1,
})
...

Посмотреть список баз данных на вашем сервере можно командой psql --list из под пользователя postgres (su postgres)

boto_config - по умолчанию настроенный на работу с хранилищем s3 в timeweb, но можно настроить на любое другое s3 хранилище. Подробнее об этом в главе организация S3 хранилища. Примерные времязатраты - 5 минут.

Минимальный набор данных - aws_access_key_id (логин), aws_secret_access_key (пароль) и s3_bucket (папка).

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

Как получить токен бота? Пишем в телегу @BotFather. Команда /newbot запускает создание нового бота. В итоге вы получите токен, который можно использовать в конфиге. Примерные времязатраты - 2 минуты.

tg_admins - ID пользователей, которым бот будет вообще отвечать.

Узнать свой Telegram-ID можно у вот этого бота, например. Или аналогичного.

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

Донастройка

Присваиваем всем файлам владельца - postgres
chown -R postgres:postgres /home/postgres

Меняем пользователя
su postgres

Запуск

Запуск в режиме --backup

В данном режиме программа запускается единоразово, а следовательно - нам надо добавить задачу в cron (из под пользователя postgres)

Редактируем crontab

crontab -e

Добавляем строчку в конец

0 4 * * * cd /var/lib/postgresql/PSQLBuddy/ && venv/bin/python3 main.py --backup

Что будет означать: Каждый день в 4.00 переходим в папку с программой и запускаем из интерпретатора в окружении программу с аргументом backup

Запуск в режиме --restore

В режиме restore программа должна быть постоянно запущена, и лучший способ это обеспечить - запустить ее как сервис. Для этого можно воспользоваться файлом PSQLBuddy.service. Для начала надо проверить, что внутри файла. Там должны быть правильно прописаны пути WorkingDirectory & ExecStart. По умолчанию они ведут на /var/lib/postgresql/PSQLBuddy.
Затем делаем софтлинк этого файла в папку /etc/systemd/system/ и обновляем список сервисов.

Создание софтлинка и работу с systemctl мы проводим под root или любым другим административным пользователем, поскольку по умолчанию пользователю postgres не хватает прав для работы с systemd & systemctl.

Итак, вводим в консоль exit, если мы все еще под пользователем postgres и начинаем.

ln -s /var/lib/postgresql/PSQLBuddy/PSQLBuddy.service /etc/systemd/system/PSQLBuddy.service
systemctl daemon-reload

Затем активируем службу и запускаем ее

systemctl enable PSQLBuddy.service
systemctl start PSQLBuddy.service

Проверяем успешность запуска командой

systemctl status PSQLBuddy.service

Если что-то не получается с запуском бота как сервиса, всегда можно запустить скрипт вручную:

/var/lib/postgresql/PSQLBuddy/venv/bin/python3 main.py --restore

Прочие материалы

Организация S3 хранилища

Timeweb.cloud

Я лично пользуюсь уже много лет сервисом timeweb, поэтому изначально делал и опишу настройку для него.
Если вы еще не зарегистрированы в timeweb, то можете сделать это по моей реферальной ссылочке. Буду очень благодарен :)
Но если вы зарегистрируетесь напрямую, то магия никуда не исчезнет, все будет работать точно так же, просто таймвеб не угостит меня кофем :)

После регистрации создаем новое S3 хранилище.

Думаю, вы прекрасно справитесь с созданием нового бакета и увидите Примерно такую картину:

Отсюда нас интересует три вещи - S3 Access Key, S3 Secret Access Key и название бакета (339e534d-my_test_bucket на скриншоте выше). Вы без проблем сопоставите их тремя полями в конфиге (он же boto_config).

Другие S3 хранилища

Полный набор настроек описан в классе BotoConfig в файле conf/config_classes.py. Можно как задать их напрямую там (значения по умолчанию), так и переопределить в файле config.py.

Лексикон бота

На момент релиза мне было лень редактировать фразы бота, да и вообще, как мне кажется, что "Витя" - хороший собирательный образ для бухгалтера :)
В любом случае все фразочки захардкожены в файле bot/bot_init.py и много знаний, чтобы их подправить не нужно. Кастомизируйте в свое удовольствие.

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

Логирование

По умолчанию логи сыпятся в /var/log/syslog. В файле main.py можно раскомментировать строчку filename='common.log', тогда логи будут идти в соответствующий файл.

Посмотреть, что происходит в логах можно, использовав команду tail

tail -n 200 /var/log/syslog | grep python
tail -n 200 /var/log/syslog | grep cron

Заключение

Итоги внедрения утилит

Установив экземпляр программы на свою базу данных, я получаю теперь отдельные бэкапы каждый день, их ротацию и хранение в дешевом хранилище. По состоянию на конец 24го года мы платим 1200 рублей за 500 гигабайт, что гораздо адекватнее покупки дополнительного диска на VDS. Там 500 гигабайт обойдется в 5000 рублей ежемесячно и подключить больше 500 гигабайт я возможности не видел.

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

Планы по дальнейшему улучшению

• [ ] Сделать блокировку работы с базой если уже запущен один процесс

• [ ] Добавить периодичность создания бэкапов (эту базу бэкапим только по понедельникам).

• [ ] Иногда не срабатывает drop archive, если база используется. Ошибка в бот неинформативна, можно улучшить. Или добавить принудительный разрыв соединений.

• [ ] Добавить выбор названия для архивной базы данных

• [ ] Структурировать логи. Отдельным файлом - резервные копии. Отдельным - работа бота + ротация

Эпилог

Нажмете звездочку - буду счастлив.

Зарегистрируетесь в timeweb по моей рефералке - буду счастлив и прыгать до потолка.

Если возникли какие-то проблемы - пишите мне телеграм. Постараюсь разобраться и исправить.