Резервное копирование баз данных 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 гигабайта дампа передаются примерно за полторы минуты, что вполне приемлемо.

Используемый скрипт для Windows

Недавно попросили настроить 1С и PostgreSQL в Windows, на скорую руку набросал скрипт резервного копирования базы (к сожалению, замечаний пока много, но времени на "шлифовку" было сильно мало - переделывал древний скрипт архивирования файловой базы). Для резервного копирования используется FTP, для работы с которым я пользуюсь привычным curl (прошу обратить внимание, что если Вы не хотите компилировать его из исходников, надо скачать готовый пакет для Windows; скрипт предполагает, что curl.exe лежит рядом со скриптом):
@Echo off
: Скрипт создаёт архив томами по 4480 Мб (размер DVD), добавляя к ARCNAMEBEGIN
: текущую дату в формате -ГГГГ-ММ-ДД с расширением ARCEXT, помещая в него всё
: из каталога BACKUPPATH
: При успешном завершении архивации, удаляются все архивы, начинающиеся с
: ARCNAMEBEGIN и имеющие расширение ARCEXT (и тома) старше KEEPDAYS дней
: ВАЖНО!!!
: ARCNAMEBEGIN должен начинаться с буквы диска, т.к. ForFiles не работает с
: путями UNC (по крайней мере на имеющейся у меня версии Windows)
: Для возможности резервного копирования на локально подключенный диск и на
: сетевой общий ресурс, добавлены команды 'net use';
: Результат работы скрипта сохраняется в файле LOG (полное имя файла) в
: кодировке CP866
: Результат текущей архивации сохраняется в файл LOG.txt (там же где и основной
: протокол, но с добавлением расширения .txt; перезаписывается при каждом
: запуске) в кодировке UTF-8 и при возникновении ошибок отправляется на e-mail
: вызовом скрипта BackupSendError.vbs расположенного рядом с данным

: Подключение сетевого хранилища, при необходимости
: net use t: /delete
: net use t: \\NBS\Fileserver$

: Полный путь к файлу журнала:
SET LOG=D:\BackUp\Log\Backup.log
: Полный путь и начало имени архивов:
SET ARCNAMEBEGIN=D:\BackUp\7z\retail_o
: Расширение файла архива:
SET ARCEXT=7z
: Полный путь к архивируемому каталогу:
SET BACKUPPATH=D:\BackUp\retail_o.sql
: SET BACKUPPATH=D:\TechShare
: Срок хранения архивов (архивы старше этого срока будут удалены при архивации)
SET KEEPDAYS=30
: Пользователь и пароль FTP
SET FTPUSER=magazin
SET FTPPW=SuperPassword
 call :getdate

"C:\Program Files\PostgresPro 1C\9.6\bin\pg_dump.exe" --dbname="postgresql://postgres:SuperPassword@localhost:5432/buh" > %BACKUPPATH%

call :arch %ARCNAMEBEGIN% %BACKUPPATH% >> "%LOG%"

%~dp0curl.exe -u %FTPUSER%:%FTPPW% -T "%ARCNAMEBEGIN%-%Year%-%Month%-%Day%.%ARCEXT%" ftp://magazin:@192.168.20.77:21/exchange/BackUp/

: Отключение сетевого хранилища, при необходимости
: net use t: /delete

exit

:delold
if not exist "%1" Echo %DATE% %TIME% Path %1 NOT FOUND!!!
if not exist "%1" exit /b
Echo %DATE% %TIME% Deleting all %2 files in %1 folder, older than %3 days
Forfiles -p %1 -s -m %2 -d -%3 -c "%comspec% /c del /q @path" > nul
echo %DATE% %TIME% Clearing done with errorlevel: %errorlevel%
exit /b

:arch
echo %DATE% %TIME% ----------------------------------------------------------
echo %DATE% %TIME% Starting archiving files from %2 to %1-%Year%-%Month%-%Day%.%ARCEXT%
echo %DATE% %TIME% User: %USERNAME%
: "C:\Program Files\7-Zip\7z.exe" a -r -ssw -sccUTF-8 -v4480m -mx=3 -ms=off -y "%1-%Year%-%Month%-%Day%.%ARCEXT%" "%2" > %LOG%.txt
"C:\Program Files\7-Zip\7z.exe" a -ssw -sccUTF-8 -mx=3 -ms=off -y "%1-%Year%-%Month%-%Day%.%ARCEXT%" "%2" > %LOG%.txt
echo %DATE% %TIME% Archiving done with errorlevel: %errorlevel%
if not errorlevel 1 (
call :delold %~dp1 %~n1*.%ARCEXT%* %KEEPDAYS%
) else (
cscript %~dp0BackupSendError.vbs //Nologo %LOG%.txt
)
echo %DATE% %TIME% Backup done
exit /b

:getdate
: Чтение текущей даты в переменные окружения
 For /F "Tokens=1,3" %%i IN ('REG QUERY "HKCU\Control Panel\International" /s^|FindStr /C:"iDate" /C:"sDate"') DO Set %%i=%%j
 For /F "Tokens=1-4* Delims=%sDate% " %%A IN ("%Date%") Do (
    If %iDate% EQU 0 Set Year=%%C&Set Month=%%A&Set Day=%%B
    If %iDate% EQU 1 Set Year=%%C&Set Month=%%B&Set Day=%%A
    If %iDate% EQU 2 Set Year=%%A&Set Month=%%B&Set Day=%%C
 )
exit /b

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

На практике я последнее время создаю текстовые дампы ("доработанный скрипт"), а восстанавливаю командой:

psql upp-copy < upp-2014-06-27-14-30-04.pgdump > log-create

"upp-copy" - имя базы данных, в которую производится восстановление; "upp-2014-06-27-14-30-04.pgdump" - имя файла дампа базы; "log-create" - файл, в который помещаются сообщения об успешно созданных таблицах и т.п., иначе можно не увидеть ошибок за кучей диагностики (да и в удалённой сессии это несколько увеличивает производительность). Команда отдаётся от рута (ему я разрешил локальный доступ к pgsql). Важным является то, что на момент восстановления, база должна существовать, быть пустой, и иметь владельца, совпадающего с владельцем оригинальной базы.

Для двоичных дампов несколько иначе:

Если владелец (имя "роли входа" или пользователь 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), времени потребуется ещё больше - данные методы целесообразно использовать не для полного восстановления, а когда требуется восстановить только определённую часть базы данных.

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