Application: "PostgreSQL".
Задача: обеспечить резервное копирование всех "баз данных" СУБД PostgreSQL путём снятия SQL-"дампа", с ротацией итоговых файлов.
Конечно, прежде всего целостность данных должна обеспечиваться репликацией таковых с основного сервера БД на резервный, уже с которого безболезненно для производительности схемы в произвольное время можно снимать любого вида резервные копии. При этом лучше настроить запуск задачи на ночное время - в период наименьшей активности пользователей "слепок данных" будет минимально неконсистентным.
Проще всего получить полную и точную копию "базы данных" используя утилиту "pg_basebackup", но она оперирует файлами в исходном "бинарном" формате PostgreSQL (physical backup), а иногда (в данном конкретном случае) хочется получить хотя бы отчасти человекопонятный набор SQL-команд (logical backup).
Важно понимать, что традиционный способ резервного копирования путём выгрузки содержимого таблиц в виде построчного набора SQL-команд не гарантирует получения действительно полного среза баз данных - в процессе последовательного чтения всего массива данных сервера часть содержимого может изменится, а погоня за изменениями и дополнение финального "дампа" таковыми может растянуться до бесконечности. Однако разработчики утилиты снятия SQL-дампа "pg_dump" обещают консистентность "бекапа" каждой базы данных по отдельности. Насколько это применительно к действительно большой и активной БД - мне неизвестно.
Как бы то ни было, приступим к настройке резервного копирования.
Прежде всего определимся с местом для резервных копий - в файловой системе, заведомо достаточной по объёму (оценить потребности можно примерно так: "du -sh /var/lib/postgresql/main/base/" - естественно, есть смысл учесть последующее сжатие минимум в два раза):
# mkdir -p /var/backups/postgresql
Строго-настрого закрываем месторасположение архивов от посторонних:
# chown -R postgres:postgres /var/backups/postgresql
# chmod -R go-rwx /var/backups/postgresql
# chmod -R go-rwx /var/backups/postgresql
Пишем скрипт резервного копирования как таковой:
# mkdir -p /usr/local/etc/postgresql && cd /usr/local/etc/postgresql
# vi ./pg_backup_nightly.sh && chmod ug+x ./pg_backup_nightly.sh
# vi ./pg_backup_nightly.sh && chmod ug+x ./pg_backup_nightly.sh
#!/bin/bash
# Дополняем набор переменных окружения путём к утилитам PostgreSQL
PATH=${PATH}:/usr/lib/postgresql/9.5/bin
# Указываем количество файлов резервного копирования которые должны сохранятся на сервере
CPCOUNT=6
# Явно указываем пользователя, от имени которого будет осуществляться процедура
USER="postgres"
# Указываем директорию файловой структуры СУБД
DATADIR="/var/lib/postgresql/main"
# Указываем директорию для хранения файлов резервных копий
BACKUPDIR="/var/backups/postgresql"
# Указываем файл для журнала событий
LOG="/var/log/pgsqldump.log"
# Фиксируем дату и время запуска процедуры
DATE=$(date +"%Y%m%d.%H%M%S")
# Предварительно корректируем потенциально неверные права доступа к ресурсам
chown ${USER} "${LOG}"
# Записываем время запуска процедуры
echo >> ${LOG}
echo "${DATE}" >> ${LOG}
# Выясняем количество имеющихся резервных копий и удаляем наиболее старые при превышении заданного лимита, высвобождая пространство для новых копий (делая это до "бэкапа" мы рискуем потерять часть уже имеющихся данных ещё до начала процедуры следующего "бэкапа" - неизвестно ещё насколько удачно тот завершится; но иногда лучше так, чем забить диск до упора, не имея места куда записать новые данные)
#
if [ "`ls ${BACKUPDIR} | grep .gz --count`" -gt "${CPCOUNT}" ]; then
# Удаляем один самый старый файл
cd "${BACKUPDIR}"
ls -t | tail -n 1 | xargs --no-run-if-empty -I {} echo "Remove old backup file \"{}\"" >> ${LOG}
ls -t | tail -n 1 | xargs --no-run-if-empty rm --force
fi
# Создаем "дамп" всех баз и схем PostgreSQL с упаковкой GZip-сжатием "на лету"
#
sudo -u ${USER} pg_dumpall --clean --if-exists --inserts --username="${USER}" --no-password 2>> ${LOG} | gzip -6 -c > ${BACKUPDIR}/${DATE}.sql.gz
echo "Save SQL-dump in archive \"${DATE}.sql.gz\"" >> ${LOG}
exit ${?}
# Дополняем набор переменных окружения путём к утилитам PostgreSQL
PATH=${PATH}:/usr/lib/postgresql/9.5/bin
# Указываем количество файлов резервного копирования которые должны сохранятся на сервере
CPCOUNT=6
# Явно указываем пользователя, от имени которого будет осуществляться процедура
USER="postgres"
# Указываем директорию файловой структуры СУБД
DATADIR="/var/lib/postgresql/main"
# Указываем директорию для хранения файлов резервных копий
BACKUPDIR="/var/backups/postgresql"
# Указываем файл для журнала событий
LOG="/var/log/pgsqldump.log"
# Фиксируем дату и время запуска процедуры
DATE=$(date +"%Y%m%d.%H%M%S")
# Предварительно корректируем потенциально неверные права доступа к ресурсам
chown ${USER} "${LOG}"
# Записываем время запуска процедуры
echo >> ${LOG}
echo "${DATE}" >> ${LOG}
# Выясняем количество имеющихся резервных копий и удаляем наиболее старые при превышении заданного лимита, высвобождая пространство для новых копий (делая это до "бэкапа" мы рискуем потерять часть уже имеющихся данных ещё до начала процедуры следующего "бэкапа" - неизвестно ещё насколько удачно тот завершится; но иногда лучше так, чем забить диск до упора, не имея места куда записать новые данные)
#
if [ "`ls ${BACKUPDIR} | grep .gz --count`" -gt "${CPCOUNT}" ]; then
# Удаляем один самый старый файл
cd "${BACKUPDIR}"
ls -t | tail -n 1 | xargs --no-run-if-empty -I {} echo "Remove old backup file \"{}\"" >> ${LOG}
ls -t | tail -n 1 | xargs --no-run-if-empty rm --force
fi
# Создаем "дамп" всех баз и схем PostgreSQL с упаковкой GZip-сжатием "на лету"
#
sudo -u ${USER} pg_dumpall --clean --if-exists --inserts --username="${USER}" --no-password 2>> ${LOG} | gzip -6 -c > ${BACKUPDIR}/${DATE}.sql.gz
echo "Save SQL-dump in archive \"${DATE}.sql.gz\"" >> ${LOG}
exit ${?}
Где определяющие опции "pg_dumpall":
"--verbose" - осуществляем детализированный вывод в журнал событий;
"--clean" - зачищаем целевую "базу данных" перед вставкой данных;
"--if-exists" - производим операцию только при наличии целевого объекта;
"--inserts" - применяем режим создания "дампа" с командой "INSERT" на каждую строку вместо более компактного режима по умолчанию, с выгрузкой всей таблицы через "COPY";
"--no-password" - указываем не выдавать запрос на ввод пароля (аутентификацию осуществлять только через политики доверия или файл ".pgpass").
"--clean" - зачищаем целевую "базу данных" перед вставкой данных;
"--if-exists" - производим операцию только при наличии целевого объекта;
"--inserts" - применяем режим создания "дампа" с командой "INSERT" на каждую строку вместо более компактного режима по умолчанию, с выгрузкой всей таблицы через "COPY";
"--no-password" - указываем не выдавать запрос на ввод пароля (аутентификацию осуществлять только через политики доверия или файл ".pgpass").
Закрываем доступ посторонних к скрипту резервного копирования:
# chown -R postgres:postgres /usr/local/etc/postgresql
# chmod -R o-rwx /usr/local/etc/postgresql
# chmod -R o-rwx /usr/local/etc/postgresql
Внесём в таблицу "/etc/crontab" указание на запуск скрипта резервного копирования с определённой периодичностью (каждую ночь, в четыре утра):
# vi /etc/crontab
....
# Nightly SQL-backup all PostgreSQL databases
0 4 * * * postgres /usr/local/etc/postgresql/pg_backup_nightly.sh &
....
# Nightly SQL-backup all PostgreSQL databases
0 4 * * * postgres /usr/local/etc/postgresql/pg_backup_nightly.sh &
....
При необходимости восстановить состояние "баз данных" на момент среза SQL-"дампа" проще простого (СУБД при этом должна быть запущена):
# su - postgres
$ gunzip --stdout ./dump.sql.gz | psql
$ gunzip --stdout ./dump.sql.gz | psql
Правда, для гигабайтных объёмов "баз данных" процедура вливания из SQL-дампа очень длительна - десятки минут и часы - потому на практике этот подход практически не применяется, а используется комбинация копирования файлов БД и последующего добора журналов транзакций (WAL).
