пятница, 22 января 2010 г.

Плановое резервирование базы данных PostgreSql с помощью batch

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

Особенностью планового резервного копирования является то, что пользователь не следит за ходом выполнения операции и не может проконтролировать результат. Исходя из этого, обязательным требованием является ведение журналов с информацией о результатах резервного копирования. Ниже приведен пример BAT-файла, который выполняет резервное копирование базы данных PostgreSQL, ведет журнал с информацией о датах и результатах выполнения, сохраняет подробные сведения о ходе выполнения каждой резервной копии в отдельный текстовый файл и в случае неудачи отображает диалоговое окно с сообщением.

backup.bat
  1. REM ПРИМЕР СОЗДАНИЯ РЕЗЕРВНОЙ КОПИИ БАЗЫ ДАННЫХ POSTGRESQL
  2. CLS
  3. ECHO OFF
  4. CHCP 1251

  5. REM Установка переменных окружения
  6. SET PGBIN=C:\Program Files (x86)\PostgreSQL\8.4\bin\
  7. SET PGDATABASE=mydb
  8. SET PGHOST=localhost
  9. SET PGPORT=5484
  10. SET PGUSER=admin
  11. SET PGPASSWORD=admin

  12. REM Смена диска и переход в папку из которой запущен bat-файл
  13. %~d0
  14. CD %~dp0

  15. REM Формирование имени файла резервной копии и файла-отчета
  16. SET DATETIME=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%-%TIME:~3,2%-%TIME:~6,2%
  17. SET DUMPFILE=%PGDATABASE% %DATETIME%.backup
  18. SET LOGFILE=%PGDATABASE% %DATETIME%.log
  19. SET DUMPPATH="Backup\%DUMPFILE%"
  20. SET LOGPATH="Backup\%LOGFILE%"

  21. REM Создание резервной копии
  22. IF NOT EXIST Backup MD Backup
  23. CALL "%PGBIN%\pg_dump.exe" --format=custom --verbose --file=%DUMPPATH% 2>%LOGPATH%

  24. REM Анализ кода завершения
  25. IF NOT %ERRORLEVEL%==0 GOTO Error
  26. GOTO Successfull

  27. REM В случае ошибки удаляется поврежденная резервная копия и делается соответствующая запись в журнале
  28. :Error
  29. DEL %DUMPPATH%
  30. MSG * "Ошибка при создании резервной копии базы данных. Смотрите backup.log."
  31. ECHO %DATETIME% Ошибки при создании резервной копии базы данных %DUMPFILE%. Смотрите отчет %LOGFILE%. >> backup.log
  32. GOTO End

  33. REM В случае удачного резервного копирования просто делается запись в журнал
  34. :Successfull
  35. ECHO %DATETIME% Успешное создание резервной копии %DUMPFILE% >> backup.log
  36. GOTO End

  37. :End

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

Строки 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. Задание также может быть создано из командной строки. Для этого можно воспользоваться командой SCHTASKS. Ниже приведен пример использования этой команды.

schedule.bat
  1. CLS
  2. ECHO OFF
  3. CHCP 1251
  4. SCHTASKS /Create /RU SYSTEM /SC DAILY /TN "Резервное копирование" /TR "D:\Db\backup.bat" /ST 02:00:00
  5. IF NOT %ERRORLEVEL%==0 MSG * "Ошибка при создании задачи резервного копирования."

В результате выполнения schedule.bat будет создана задача "Резервное копирование". Параметры задачи можно посмотреть и изменить через оконный интерфейс. Использование bat-файла для создания задания планировщику может быть полезным в случаях, когда настройку резервного копирования нужно выполнять многократно.

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

48 комментариев:

  1. скажите , а почему когда я открываю бэкап , там как то все в каракулях
    можно как то сделать чтобы все сохранялоссь в sql формате ?
    Спасибо

    ОтветитьУдалить
  2. Ответ легко найти в документации. http://www.postgresql.org/docs/9.0/static/app-pgdump.html опция "--format". В примере в строке 27 Вам нужно заменить "--format=custom" на "--format=plain". Хороший форум по PostgreSQL http://sql.ru/forum/actualtopics.aspx?bid=7.

    ОтветитьУдалить
  3. огромное спасибо.! даже не ожидал ответа=)
    а в каком формате эфективней сохранять ?

    ОтветитьУдалить
  4. Зависит от того, какой эффект нужен. :) Для резервного копирования я использую custom формат. Файлы сразу получаются сжатыми, удобно для хранения и переноса, нет вопросов с кодировками. Если нужен скрипт / часть скрипта, я использую plain формат.

    ОтветитьУдалить
  5. нужен самый безопасный вариант чтобы проблем с востановлением не было.

    ОтветитьУдалить
  6. Такая краткость может быть расценена как спам. :)

    ОтветитьУдалить
  7. Статья очень помогла. спасибо!

    ОтветитьУдалить
  8. Добрый день. Воспользовался вашим батником, при запуске выдает сообщение ошибка создания резервной копии. Смотрите backup.log, backup.log в свою очередь отправляет к postgresql-2012-02-15_000000.log, который пустой. Не могу найти в чем ошибка создания архива. В батнике поменял путь к пост грее, имя БД, и логин/пароль. Подскажите в чем может быть проблема?

    ОтветитьУдалить
  9. Здравствуйте. Мало информации для того чтобы можно было дать совет. Попробуйте сделать резервную копию вручную.

    ОтветитьУдалить
  10. Спасибо вам огромное, все работает!!

    ОтветитьУдалить
  11. Добрый день,
    Кто подскажет, почему если батник запускаю ручками, то все работает великолепно, а если через планировщик Windows то выходит вот такое сообщение:
    ""Z:\usr\local\pgsql-8.4\bin\\pg_dump.exe"" не является внутренней или внешней
    командой, исполняемой программой или пакетным файлом.

    ОтветитьУдалить
  12. Здравствуйте,
    Скорее всего планировщик выполняет задачу от имени пользователя для которого нет доступа к Z:\usr\local\pgsql-8.4\bin\pg_dump.exe или не существует диск Z:.

    ОтветитьУдалить
  13. Диск Z виртуальный(создается Денвером), доступ дать невозможно, а вот в безопасности установил полные права для ВСЕ(что бы убедиться) все равно та же ошбка... Ну лан, все равно спасибо))) буду разбираться)))

    Странно, фоновые службы для приложения находящегося на том же диске Z и вызванные из под планировщика Windows работают исправно

    ОтветитьУдалить
  14. Геннадий у Вас в строке Z:\usr\local\pgsql-8.4\bin\\pg_dump.exe двойной после bin слэш, возможно в этом причина

    ОтветитьУдалить
  15. А как сделать, чтобы удалялись старые копии БД?

    ОтветитьУдалить
    Ответы
    1. http://stackoverflow.com/questions/51054/batch-file-to-delete-files-older-than-n-days

      Удалить
  16. Все работает. Но не понятно как утилите передаются параметры: имя базы, сервер, порт и логин спаролем?

    ОтветитьУдалить
  17. Утилиты PostgreSQL умеют работать с переменными среды. Например, чтобы запустить программу независимо от пути, путь к программе добавляется в переменную PATH. Командой SET PATH=... Так же и для PostgreSQL есть определенные переменные окружения. Они устанавливаются в начале скрипта. Затем утилиты обращаются к ранее установленным значениям. Вот на этой странице описаны переменные окружения, которые анализирует PostgreSQL http://www.postgresql.org/docs/9.2/static/libpq-envars.html.

    ОтветитьУдалить
  18. Прошу прощения за глупый вопрос,но все же. А куда будет сохраняться файл бекапа? Я имею в виду, можно ли задать определенную директорию для бекапа в скрипте?

    ОтветитьУдалить
    Ответы
    1. В начале файла устанавливается переменная SET DUMPPATH="Backup\%DUMPFILE%". Это означает, что резервная копия будет сохранена в подпапку Backup текущей папки, т.к. указан относительный путь. Можно указать абсолютный путь, например: SET DUMPPATH="D:\DatabaseBackup\%DUMPFILE%"

      Удалить
  19. Выгрузил дамп данным скриптом. Выгружаю базу 1с.
    Далее создаю новую БД через 1с, создаю БД в постгри. После захожу в pgadmin3 нахожу новую нужную мне БД. Загружаю туда дамп через "восстановление". БД не работает.
    При восстановлении ошибки
    Warning errors ignored on restore: 10070
    Warning errors ignored on restore: 10069
    (бывают разные)

    Ошибка при запуске 1с ошибка "Тип не определен '12312312asdas'

    Вероятно я как то не так восстанавливаю?

    Windows 2008 r2 + postgre 9.1.2-1.1C(x64)

    ОтветитьУдалить
    Ответы
    1. Мало информации. Я бы искал проблему в следующем порядке. 1. Посмотреть лог создания резервной копии на наличие ошибок. Если есть - разбираться. 2. Посмотреть в исходной базе данных наличие типов, которых не хватает. 3. Посмотреть подробно лог восстановления. Возможно одна ошибка уже является следствием другой.

      Удалить
    2. спс с этим разобрался,
      Может кому-то полезно будет:
      надо поставить префикс Очистить Запрос перед восстановлением, вроде -c опция в pg_restore

      Удалить
  20. Этот комментарий был удален автором.

    ОтветитьУдалить
  21. Здравствуйте!
    Спасибо за bat-файл.

    У меня при создании резервной копии возникла следующая проблема (фрагмент лога):
    ...
    pg_dump: [custom archiver] could not write to output file: Invalid argument
    pg_dump: *** aborted because of error

    Не подскажете, что могло не понравится утилите pg_dump?

    Спасибо.

    ОтветитьУдалить
  22. Добрый день.
    Подскажите. Я создавал бэкапы, используя этот батник, при создании бэкапа ошибок нет. Но когда пытаюсь восстановить пишет:
    C:\Program Files\PostgreSQL\9.1.2-1.1C\bin\pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "BD" --role "postgres" --no-password --clean --verbose "C:\Users\12.backup"
    pg_restore: [archiver] WARNING: archive is compressed, but this installation does not support compression -- no data will be available
    pg_restore: [archiver] cannot restore from compressed archive (compression not supported in this installation)
    pg_restore: *** aborted because of error

    Процесс вернул код выхода 1.

    Восстанавливал как через оснастку pgAdmin так и через консоль.

    ОтветитьУдалить
    Ответы
    1. Судя по обсуждениям в интернете http://postgresql.ru.net/node/215078, http://comments.gmane.org/gmane.comp.db.postgresql.performance/19704 версия PostgreSQL в которой выполняется восстановление собрана без поддержки компрессии (без zlib). Можно либо исправить скрипт, который создает резервную копию, добавить параметр --compress=0, чтобы не было компрессии. Либо использовать другую версию PostgreSQL при восстановлении.

      Удалить
  23. Здравствуйте!
    При создании бэкапов вашим способов ошибок нет. Но когда пытаюсь восстановить через оснастку pgAdmin пишет:

    C:\Program Files (x86)\PostgreSQL\9.0.3-3.1C\bin\pg_restore.exe --host localhost --port 5432 --username postgres --dbname "1c_new" --verbose "E:\Backup\Backup\1c_new 2014-01-16 14-35-20.backup"


    pg_restore: creating FUNCTION vassn(boolean)
    pg_restore: [archiver (db)] Error while PROCESSING TOC:
    pg_restore: [archiver (db)] Error from TOC entry 202; 1255 16854 FUNCTION vassn(boolean) postgres
    pg_restore: [archiver (db)] could not execute query: ERROR: function "vassn" already exists with same argument types
    Command was: CREATE FUNCTION vassn(boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
    DECLARE bexpr alias for $1;
    BEGIN
    if bexpr
    th...

    WARNING: errors ignored on restore: 1

    Процесс вернул код выхода 1.

    В чем может быть проблема?

    ОтветитьУдалить
    Ответы
    1. Здравствуйте. Возможно в том что функция vassn уже есть в базе данных. Восстанавливать резервную копию нужно в пустую базу, т.е. в ту в которой даже не определена структура таблиц и функции. Попробуйте для проверки создать базу данных с использованием postgresql0 в качестве шаблона. И затем выполнить в нее восстановление.

      Удалить
    2. Здравствуйте.
      Пробовал. Перед воостановлением резервной копии удалял базу данных через оснастку pgAdmin, создавал новую через pgAdmin:

      имя:1c_new
      владелец:postgres
      кодировка:UTF8
      сопоставление и тип символа:Russian
      шаблон:postgres,template0,template1 (пробовал все 3)

      Восстанавливал в нее резервную копию. Эффект тот же:
      WARNING: errors ignored on restore: 1
      Процесс вернул код выхода 1.

      Так же пробовал делать резервную копию1 через pgAdmin (Compress) - далее удалял базу через pgAdmin - затем содавал новую через pgAdmin (как описано выше) - после восстанавливал в нее резервную копию1.
      Эффект тот же!
      WARNING: errors ignored on restore: 1
      Процесс вернул код выхода 1.

      Удалить
  24. Я так понимаю, что проблема с восстановлением базы все же существует и из за этого меня все больше и больше влечет в к MSSQL 2008r2 Express Edition (Он бесплатен и можно устанавливать не боясь за то что придут и оторвут колокольчики, звонил в Support мелкомягких и получил полное подтверждение что можно!)

    Виктор Вы бы не могли для тех кто в танке и для меня тоже, статью по восстановлению базы с бекапов? За что Вам огромная благодарность и низкий поклон из Мариуполя!

    ОтветитьУдалить
    Ответы
    1. MSSQL 2008r2 Express Edition оно конечно привлекательная, но имеет ограничения, которые не радуют

      Удалить
  25. По поводу заливки бекапов обратно в Постгре разобрался, в принципе ничего сложного я не нашел, единственное, последовательность и главное не перепутать что и как делать.

    ОтветитьУдалить
    Ответы
    1. Хорошо, потому как описывать задачи, о которых можно легко почитать в документации, не хочется. Эту статью я посвятил теме настройки "автоматического" резервного копирования, чего нет в документации. Да и давно я уже с PostgreSQL не работал. Может, что-то интересное появилось.
      Что касается надежности PostgreSQL, то она достаточно высокая. При правильной настройке проблемы возникают реже, чем в MSSQL.

      Удалить
    2. Ну и как разобрался?

      Удалить
  26. Здравствуйте! Во-первых - Большое спасибо за статью!
    Во-вторых, есть к Вам вопросик. Имеется две базы данных (например BD1 и BD2). Создал 2 БАТника по Вашему примеру на каждую из БД (bd1.bat и bd2.bat). Подскажите пожалуйста текст третьего БАТника, который будет запускать их по очереди - сначала bd1.bat, после завершения его исполнения что бы запускался bd2.bat
    Можно, конечно, просто добавить в задания планировки на разное время, но хотелось бы что бы все было красиво и правильно. Спасибо!

    ОтветитьУдалить
  27. И еще вопросик, в командной строке вместо русского шрифта кракозябры, как можно попробовать исправить? Может, кодировку другую прописать (и как прописать:))?

    ОтветитьУдалить
    Ответы
    1. Либо поменяйте кодировку в bat-файле, либо используйте команду chcp. Если не ошибаюсь 866 - dos, 1251 - win1251, 65001 - utf-8.

      Удалить
  28. mysqldump: unknown variable 'file=Backup\bazar 2015-11-18 17-21-40.backup'

    mysqldump: unknown variable 'format=custom'

    ОтветитьУдалить
  29. Вопрос, совсем нубский наверное, но простите, буду очень рад ответу:
    При запуске батника
    http://prntscr.com/d59sx9

    ОтветитьУдалить
    Ответы
    1. Добавлю, Win 2016Server http://prntscr.com/d59v71

      Удалить
  30. Проблема с кодировками, в юникоде батник не работает УТФ-8 решил проблему. Спасибо.

    ОтветитьУдалить
  31. Спасибо за отличное руководство. Но, есть вопрос - можно ли в одном этом батнике задать больше одной базы для дампа? Пробовал с различным синтаксисом - не выходит. Спасибо.

    ОтветитьУдалить
    Ответы
    1. Замените в начале файла значения на переменные
      SET PGDATABASE=mydb >> SET PGDATABASE=%1
      Создайте еще один bat-файл, например, backup-all-db.bat. Вызывайте из него основной файл столько раз, сколько баз данных нужно сохранить, передавая имя базы в качестве параметра:
      call backup-db.bat db1
      call backup-db.bat db2
      ...
      Если Вам нужно вообще все базы сохранять, то, думаю, нужен другой подход.

      Удалить