Статьи‎ > ‎

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

Для реализации простейшего резервного копирования баз данных postgres я не стал использовать какие-либо сложные backup-решения, а остановился на простейшем database dump. В /usr/local/bin создал файл скрипт следующего содержания:

#!/bin/sh
# Для инструкций по восстановлению см. postgresql.org/docs/8.1/static/backup.html
# Путь, куда будем складывать резервные копии
dump_path="/mnt/arc/1C8"
# Устанавливаем разделитель для элементов массива, предварительно резервируя системный:
oldIFS==$IFS
IFS=";"
# Названия баз данных, которые будем сохранять, перечисленные через разделитель, заданный выше
dump_bases="office_upp;buh_retail;zup"
# Срок хранения резервных копий, дней:
dump_keepdays="14"
# Создаём информационный файл в каталоге назначения ("для будущих поколений")
echo Backup dumps of current databases. For restoring see postgresql.org/docs/9.3/static/backup.html > $dump_path/readme.txt
echo All data keeps $dump_keepdays days, older files will be removed automatically. >> $dump_path/readme.txt
for dump_base in $dump_bases; do
# Создаём дамп от имени пользователя postgres, в сжатый файл с именем вида %dbname%-yyyy-mm-dd.pgdump
  sudo -u postgres pg_dump -F c -Z 9 \

  -f $dump_path/$dump_base-`date \+\%Y-\%m-\%d`.pgdump $dump_base
# Ищем в каталоге резервных копий все файлы с именами похожими на бэкап текущей БД и старше срока хранения и удаляем

  find $dump_path/$dump_base*.pgdump -mtime +$dump_keepdays -exec /bin/rm '{}' \;
done
# Восстанавливаем стандартный (системный) разделитель списков
IFS=$oldIFS


Данный файл прописываем в cron для пользователя root, с вызовом или по рабочим дням, или ежедневно. Для возможности запуска sudo из cron-задания, следует разрешить sudo для пользователя root - добавить строки вида Defaults:root !requiretty

Defaults:!root requiretty

в /etc/sudoers. Проверяем результат на следующий день.
Из недостатков предложенного метода хотелось бы отметить необходимость вызова данного скрипта суперпользователем (к стати, наверное его следовало положить в /usr/local/sbin, для большей безопасности, но тогда в syslog появлялся мусор типа "Can't change path to /usr/local/sbin", судя по всему, на этапе вычисления `date \+\%Y-\%m-\%d`), но я не нашёл другого метода запускать pg_dump, не зная пароль пользователя postgres или не указывая его в явном виде.
В принципе, вместо конструкции sudo, можно использовать pg_dump --superuser=ИМЯСУПЕРПОЛЬЗОВАТЕЛЯ -w $dump_base для подключения без пароля - такую конструкцию можно использовать и на windows-установке Postgre SQL, но только в случае, если есть "суперпользователь" без пароля, которому разрешены только локальные подключения - каждому выбирать самостоятельно.

Доработанный скрипт.

В условиях отсутствия локального или сетевого тома для хранения резервных копий (несколько натянутое условие, но мне пришлось столкнуться), пришлось доработать скрипт; отличие от изначального скрипта - результат дампа не сохраняется локально, а передаётся на удалённый сервер по ssh и там же производится удаление устаревших файлов:

#!/bin/sh
# Для инструкций по восстановлению см. postgresql.org/docs/8.1/static/backup.html
# Путь на сервере резервирования, куда будем складывать резервные копии
dump_path="/mnt/backup/pgsql"
# Устанавливаем разделитель для элементов массива, предварительно резервируя системный:
oldIFS==$IFS
IFS=";"
# Названия баз данных, которые будем сохранять, перечисленные через разделитель, заданный выше
dump_bases="office_upp;buh_retail;zup"
# Срок хранения резервных копий, дней:
dump_keepdays="7"
# Создаём информационный файл в каталоге назначения ("для будущих поколений")
ssh -i /root/nbs01/backup backup@192.168.122.1 \
 "echo `date \+\%F\ \%T` Dumps of databases. For restoring see postgresql.org/docs/9.3/static/backup.html > $dump_path/readme.txt"
for dump_base in $dump_bases; do
# Создаём дамп от имени пользователя postgres, передавая текстовый дамп по ssh на удалённый сервер,
# сжимая там результат gzip'ом и сохраняя в файл с именем вида %dbname%-yyyy-mm-dd-hh-mm-ss.pgdump.gz
  sudo -u postgres pg_dump
-Fc $dump_base | ssh -i /root/nbs01/backup backup@192.168.122.1 \
                                        "gzip > $dump_path/$dump_base-`date \+\%Y-\%m-\%d-\%H-\%M-\%S`.pgdump.gz"
  # Ищем в каталоге резервных копий все файлы с именами похожими на бэкап текущей БД и старше срока хранения и удаляем
  ssh -i /root/nbs01/backup backup@192.168.122.1 \
      "find $dump_path/$dump_base* -mtime +$dump_keepdays -exec /bin/rm '{}' \;"
done
ssh -i /root/nbs01/backup backup@192.168.122.1 \
 "echo `date \+\%F\ \%T` All data keeps $dump_keepdays days, older files will be removed automatically. >> $dump_path/readme.txt"
# Восстанавливаем стандартный (системный) разделитель списков
IFS=$oldIFS

Предварительно надо создать авторизационный ключ для пользователя (в приведённом скрипте пользователь - backup) на сервере резервного хранения и разместить приватный ключ в каталоге, доступном на чтение только пользователю root (в приведённом скрипте ключ лежит в файле /root/nbs01/backup) и настроить sshd удалённого сервера на авторизацию по ключам - об этом весьма подробно написано, например, в этой статье.
Да, трафик будет весьма серьёзным, несмотря на возможность сжатия ssh, но конкретно данное решение работает в виртуальной среде, где 3 гигабайта дампа передаются примерно за полторы минуты, что вполне приемлемо.

Восстановление базы данных из дампа.

Тут всё просто - если владелец (имя "роли входа" или пользователь postgresql, указанный владельцем изначальной БД) уже существует, но нет самой базы данных, команда восстановления будет выглядеть примерно так:

/usr/pgsql-9.2/bin/pg_restore -e -j 8 -U root -W -d upp /root/files/upp-2013-11-20.pgdump

Восстановление будет выполнено в 8 потоков (для ускорения процедуры, в документации pgsql рекомендуется использовать потоков не меньше, чем доступно ядер CPU) от имени пользователя root с интерактивным вводом пароля. Файл /mnt/arc/1C8/upp-2013-11-20-09-45-51.pgdump - распакованный .gz из второго примера или изначальный дамп из первого. Целевая база (в данном примере - upp) должна уже существовать, и быть созданной из template0.

Если пользователя-владельца создать нет возможности/желания, можно добавить ключ --no-owner да и вообще, почитать что пишут на http://www.postgresql.org/docs/9.3/static/backup.html

И ещё, если на создание дампа в пару гигабайт (несжатых) уходит пара минут, то на восстановление данного дампа в один поток (если не указать ключ распараллеливания - "-j 8" в примере выше) потребуется уже полчасика, на том же железе. А если использовать текстовые дампы (не указать "-F c" при создании дампа, и для восстановления использовать стандартную команду psql dbname < infile или использовать конвейер типа pg_restore infile.pgdump | psql), времени потребуется ещё больше - данные методы целесообразно использовать не для полного восстановления, а когда требуется восстановить только определённую часть базы данных.

Для восстановления БД с удалённой машины (сервера хранилища) можно использовать конструкцию вида:

zcat upp-2014-10-22-07-30-03.pgdump.gz | ssh 192.168.1.2 "psql upp-copy > /root/files/log-create"

Здесь zcat - команда вывода содержимого архива на stdout, upp-2013-10-22-07-30-03.pgdump.gz - имя восстанавливаемого архива, 192.168.1.2 - сервер postgresql, на который восстанавливаем дамп, upp-copy - имя базы данных, в которую разворачиваем дамп (на момент восстановления должна существовать, быть пустой, и иметь необходимые права для "роли входа", использованной в изначальной БД); чтобы не засорять экран выводом psql о процессе создания объектов, перенаправим вывод в файл (сообщения об ошибках, в случае их наличия, будут выводиться в терминал). В данном примере предполагается, что у пользователя, от имени которого мы подключаемся по ssh к серверу postgres есть право работать с базами данных, поэтому авторизация в БД не описана.

Comments