Создание бэкапа базы PostgreSQL для Windows

В PostgreSQL есть утилита, которая создает дамп базы данных и называется она pg_dump. Для того чтобы автоматизировать процесс создания бэкапов баз PostgreSQL нужно будет создать bat-файл, который будет вызывать утилиту pg_dump  и вызывать его с помощью планировщика заданий. Результатом выполнения такого сценария будет ежедневное копирование базы данных PostgreSQL, ведение журнала с информацией о датах и результатах выполнения, сохранение подробных сведений о ходе выполнения каждой резервной копии в отдельный текстовый файл и в случае неудачи отображение диалогового окна с сообщением.Содержимое bat-файла следующее:

REM ПРИМЕР СОЗДАНИЯ РЕЗЕРВНОЙ КОПИИ БАЗЫ ДАННЫХ POSTGRESQL
CLS
ECHO OFF
CHCP 1251
REM Установка переменных окружения
SET PGBIN=c:\Program Files\PostgreSQL\9.2.4-1.1C\bin
SET PGDATABASE=ut
SET PGHOST=localhost
SET PGPORT=5432
SET PGUSER=postgres
SET PGPASSWORD=123456
REM Смена диска и переход в папку из которой запущен bat-файл
%~d0
CD %~dp0
REM Формирование имени файла резервной копии и файла-отчета
SET DATETIME=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%-%TIME:~3,2%-%TIME:~6,2%
SET DUMPFILE=%PGDATABASE% %DATETIME%.backup
SET LOGFILE=%PGDATABASE% %DATETIME%.log
SET DUMPPATH="Backup\%DUMPFILE%"
SET LOGPATH="Backup\%LOGFILE%"
REM Создание резервной копии
IF NOT EXIST Backup MD Backup
CALL "%PGBIN%\pg_dump.exe" --format=custom --verbose --file=%DUMPPATH% 2>%LOGPATH%
REM Анализ кода завершения
IF NOT %ERRORLEVEL%==0 GOTO Error
GOTO Successfull
REM В случае ошибки удаляется поврежденная резервная копия и делается соответствующая запись в журнале
:Error
DEL %DUMPPATH%
MSG * "Ошибка при создании резервной копии базы данных. Смотрите backup.log."
ECHO %DATETIME% Ошибки при создании резервной копии базы данных %DUMPFILE%. Смотрите отчет %LOGFILE%. >> backup.log
GOTO End
REM В случае удачного резервного копирования просто делается запись в журнал
:Successfull
ECHO %DATETIME% Успешное создание резервной копии %DUMPFILE% >> backup.log
GOTO End
:End

Справочную информацию о командах, испульзуемых в этом файле можно получить из командной строки набрав следующую команду: "[Имя команды] /?"
Многие использованные здесь команды достаточно распространены и известны, поэтому хочется акцентировать внимание на нескольких менее известных.

Строки 15, 16 выполняют переход в папку в которой находится файл "backup.bat". "%0" возвращает имя bat-файла; "%~d0" и "%~dp0" возвращают соответственно диск и путь к bat-файлу. Подробные сведения о работе с параметрами файла можно посмотреть по этой ссылке.

В строке 19 формируется строковое представление даты и времени в нужном формате. При формировании происходит обращение к переменным окружения DATE и TIME, которые хранят текстовое представление даты и времени соответственно. После имени переменной указывается строка вида ":~m,n", где m - позиция в строке, n - количество символов.

В строке 27 вызывается утилита резервного копирования pg_dump.exe. Вызов выполняется с применением команды CALL, это позволяет дождаться завершения утилиты и проанализировать результат выполнения. Вызов утилиты завершается строкой "2>%LOGPATH%". Эта строка означает что поток ошибок STDERR, номер которого 2, приложения pg_dump.exe перенаправляется в файл, имя которого сохранено в переменной окружения LOGPATH. Так как приложение pg_dump.exe выводит все сообщения в стандартный поток ошибок, то в файле LOGPATH будет сохранен подробный отчет о выполнении резервного копирования.

В строках 37 и 42 выполняется перенаправление вывода в файл backup.log. Перенаправление осуществляется оператором ">>". Различие между операторами ">" и ">>" в том, что первый каждый раз создает новый файл, затирая ранее записанные данные, а второй - дописывает данные в существующий файл. Таким образом можно вести журнал с подробными сведениями о результатах резервного копирования.

Проверяем как работает bat-файл. Если дампы базы создаются, то можно приступать к созданию задачи для планировщика заданий Windows.
Создаем задание, которое будет запускать bat-файл каждый день в ночное время.

Ежедневные бэкапы со временям породят проблему свободного пространства на жестком диске. Можно чистить ручками, но лучше уж автоматизацию сделать полной. Решается этот вопрос также созданием bat-файла и задачи в планировщике заданий Windows.

Содержимое bat-файла такое:

forfiles /p "E:\BACKUP\Backup" /S /D -5 /C "cmd /c del /f /a /q @file"

Здесь указана команда при выполнении которой будут удаляться файлы старше 5 дней.
В планировщике заданий можно создать задачу на исполнения этого bat-файла раз в неделю.

36 thoughts on “Создание бэкапа базы PostgreSQL для Windows

  1. Дмитрий.

    Попробовал, работет. Будьте добры еще строчку, что бы удалялись старые копии. Типа старше 3х копий на свалку.

    1. Дмитрий.

      Пардон, кривость рук и браузера, отрезало нужную часть статьи, увидел.

  2. Селандий

    Огромное спасибо за статью! Напишите пожалуйста как теперь восстановить из этого дампа базу? На примере этого же батника.

    1. admin Автор записи

      Восстановить базу можно через pgAdmin. Выбираете в списке нужную базу > прав. клав. мыши > Восстановить.
      Если через командную строку. То так же задаете переменные окружения PGBIN, PGDATABASE, PGHOST и прочие. Потом вызываете: CALL «%PGBIN%\pg_restore.exe» —clean —verbose «C:\backup\file_name.backup»

      1. Дмитрий

        pg_restore: слишком много аргументов командной строки (первый: "-verbose")

  3. Кирилл

    Можете уточнить для новичков работы с PostgreSQL и скриптов на батниках.

    Требуется 1 раз запустить бат файл, и потом его не закрывать выполнение и тогда будет постоятнное резервное копирование Баз?

    1. admin Автор записи

      Скрипт в файле отрабатывает один раз. Для того чтобы он выполнялся регулярно, по расписанию, необходимо запускать bat-файл через планировщик заданий Windows.

  4. Александр

    Подскажите пожалуйста, возможно ли модифицировать скрипт так, чтобы он бэкапил все базы на сервере, а не только указанную явно?

    1. admin Автор записи

      Для бэкапа всех баз данных сразу есть утилита pg_dumpall. Код файла остается практически такой же. Только замените вызов утилиты CALL "%PGBIN%\pg_dump.exe" на CALL "%PGBIN%\pg_dumpall.exe" и не нужно устанавливать переменную окружения SET PGDATABASE=ut, ее удалите.

      Если нужно бэкапить не все, а несколько выбранных баз, то можно так.
      1. Создать еще один батник, например backup-favorite.bat. В нем вызывать основной батник backup.bat c передачей параметров:
      call backup.bat database1
      call backup.bat database2
      2. В файле backup.dat строку SET PGDATABASE=ut меняем на SET PGDATABASE=%1 (%1 - это параметр который будет принимать названия баз)
      3. Не забываем в планировщике указать файл backup-favorite.bat.

      1. Валерий

        Добрый день!
        Спасибо за идею и пример скрипта.
        Подскажите - не отрабатывает вариант , когда нужно делать бекапы нескольких баз, путем добавления еще одного батника backup-favorite.bat.
        -----------------------------
        Содержание:
        ECHO OFF
        CHCP 1251
        rem введите имена баз для резервного копирования
        call backup.bat pg2
        call backup.bat postgres
        call backup.bat pg5
        --------------------------------------------------------
        Причем выполняю файл backup-favorite.bat просто кликом - все отрабатывает!
        Включаю в планировщик Windows задание на выполнение файла backup-favorite.bat - и не работает. Планировщик пишет , что задание выполнено успешно. Но копий нет , и нет записей в файле backup.log

  5. Константин

    Добрый день!
    А что делать с такой ошибкой?

    c:\Program Files\PostgreSQL\10.5-24.1C\bin\pg_dumpall.exe: illegal option -- format=custom
    Try "pg_dumpall --help" for more information.

  6. Александр

    Работает все супер!
    Подскажите как сжать копию? потому что база весит прилично и занимает много места
    И еще момент, как ее залить на фтп? -это конечно не совсем прям важно, но все же

  7. Александр

    Печаль((( Не восстанавливает бэкап созданный вашим скриптом, выдает ошибку:
    Неуспешное завершение (код выхода: 1).

  8. Роман

    Подтверждаю, бэкап создался а при попытке восстановить средcтвами pgadmin выдает ошибку.
    Что делать ? Может распишите bat ник на восстановление ?

    1. Александр

      Конечно будет ошибка, вы же создает бэкап не средствами pgadmin ...
      поправьте строку
      REM Установка переменных окружения
      SET PGBIN=c:\Program Files\PostgreSQL\9.2.4-1.1C\bin
      на
      REM Установка переменных окружения
      SET PGBIN=C:\Program Files\pgAdmin 4\v4\runtime

      и после этого у вас нормально все будет закатываться и разворачиваться бэкап средствами pgadmin

      1. Роман

        Я так понимаю у вас тоже ошибка при восстановлении бэкапа ?
        Так я пытался восстановить и через командную строку, результат тот же

  9. Елена

    Спасибо БОЛЬШОЕ!!! Все получилось и бекап создать и из него восстановить!

  10. Андрей

    Подскажи, Postgre установлено 9.0.3-3.1C , с ним PGadmin 4 не стартует, только 3, и при востановлении код 1, как быть ?

    1. admin Автор записи

      Нюансов много. Нужен полный лог процесса.
      Возможно проблема в переменной окружения SET PGBIN, выше в комментариях это обсуждалось.
      Попробуйте указать ваш путь к pgAdmin.

  11. Дмитрий

    Если делать беакп по вашему скрипту на 10 POstgre не восстанавливает базу средствами PgAdmin

  12. Андрей

    Восстанавливает, просто сначала нужно:
    1. Создать базу в постгри.
    2. Востановить базу из бакапа.
    3. Создать в Администрировании серверов 1с базу с таким именем. Только галочку снимите "Создать базу в случае ее отсутствия"

  13. mr.nicos96

    Спасибо за полезную статью. Подскажите пожалуйста а как выполнять инкрементальный и дифференциальный бэкапы?

    1. admin Автор записи

      В статье используется стандартная утилита pg_dump и она не умеет делать инкрементальный и дифференциальный бэкапы. Хотя есть другие утилиты, которые это умеют делать.

      1. mr.nicos96

        Спасибо. Еще один вопрос. При ручном запуске скрипта все работает, в автоматическом режиме через планировщик задний результат нулевой. Ошибок в логе нет. Копии бд то же нет. В чем может быть проблема?

        1. mr.nicos96

          Разобрался сам. В DUMPPATH и SET LOGPATH надо указывать полные пути, а не относительные

  14. Максим

    Доброго дня! Очень Вам благодарен. Резервное копирование по вашему образцу исправно работает уже несколько месяцев.
    Но сейчас "появилась" вторая серверная база и соответственно нужно создавать резервы и для неё.
    Подскажите, что и в каком месте данного bat-файла нужно подправить, чтобы "одним выстрелом" резервировать обе базы?

    1. admin Автор записи

      Проще и правильней создать еще один скрипт в котором изменить переменную окружения
      SET PGDATABASE=<имя базы>
      Или запускать скрипт с аргуметом , в котором передавать имя базы: backup.bat ut
      Тогда указать SET PGDATABASE=%1

  15. Багдат

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

  16. Вадим

    Добрый день!
    Нужен скрипт для восстановления из бэкапа в копию. Старался следовать вашим рекомендациям.
    Вот пример:

    CLS
    ECHO OFF
    CHCP 1251
    REM Установка переменных окружения
    SET PGBIN=C:\Program Files\PostgreSQL\14.1-2.1C\bin
    SET PGDATABASE=NPZ_INV
    SET PGHOST=localhost
    SET PGPORT=5432
    SET PGUSER=postgres
    SET PGPASSWORD=*********
    REM Смена диска и переход в папку из которой запущен bat-файл
    %~d0
    CD %~dp0
    REM Формирование имени файла и файла-отчета
    SET DATETIME=%DATE:~0,2%-%DATE:~3,2%-%DATE:~6,4% %TIME:~0,2%-%TIME:~3,2%-%TIME:~6,2%
    SET LOGFILE=%PGDATABASE% %DATETIME%.log
    SET LOGPATH="NPZ_to_NPZ_INV\%LOGFILE%"
    REM Восстановление из резервной копии
    CALL "%PGBIN%\pg_restore.exe" --format=directory --jobs="8" --clean --verbose --file="NPZ_to_NPZ_INV\NPZ.backup" 2>%LOGPATH%
    REM Анализ кода завершения
    IF NOT %ERRORLEVEL%==0 GOTO Error
    GOTO Successfull
    REM В случае ошибки делается соответствующая запись в журнале
    :Error
    MSG * "Ошибка при создании резервной копии базы данных. Смотрите backup.log."
    ECHO %DATETIME% Ошибки при восстановлении из резервной копии базы данных %PGDATABASE%. Смотрите отчет %LOGFILE%. >> backup.log
    GOTO End
    REM В случае удачного восстановления просто делается запись в журнал
    :Successfull
    ECHO %DATETIME% Успешное восстановление из резервной копии %PGDATABASE% >> backup.log
    GOTO End
    :End

    Но не срабатывает, в лог файл выдает ошибку:
    pg_restore: error: no output directory specified

    Поправьте меня, если не затруднит.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *