UMGUM.COM (лучше) 

PostgreSQL + SQL-backup ( Резервное копирование БД "PostgreSQL" путём создания полного "логического дампа" данных СУБД. )

16 ноября 2017  (обновлено 17 ноября 2018)

OS: Linux Debian 7/8/9, Linux Ubuntu 14/16/18 LTS.
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

Пишем скрипт резервного копирования как таковой:

# mkdir -p /usr/local/etc/postgresql && cd /usr/local/etc/postgresql
# 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 ${?}

Где определяющие опции "pg_dumpall":

"--verbose" - осуществляем детализированный вывод в журнал событий;
"--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

Внесём в таблицу "/etc/crontab" указание на запуск скрипта резервного копирования с определённой периодичностью (каждую ночь, в четыре утра):

# vi /etc/crontab

....
# 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

Правда, для гигабайтных объёмов "баз данных" процедура вливания из SQL-дампа очень длительна - десятки минут и часы - потому на практике этот подход практически не применяется, а используется комбинация копирования файлов БД и последующего добора журналов транзакций (WAL).


Заметки и комментарии к публикации:


Оставьте свой комментарий ( выразите мнение относительно публикации, поделитесь дополнительными сведениями или укажите на ошибку )