Главная
База знаний
PostgreSQL
Резервное копирование всех баз PostgreSQL — Windows

Резервное копирование всех баз PostgreSQL — Windows

В примере настройки для следующей конфигурации.

Хранение баз - 5 дней.
Файлы резервного копирования - R:\sql_bak\*
Файлы журналов резервного копирования - R:\sql_bak_log\*
Формат названия файлов резервных копий - base_name_2022-06-27_13-55-20.pgdmp
Время запуска задания - Каждый день в 23:00

В скрипте меняются следующие переменные на необходимые.

set PGBIN=C:\Program Files\PostgresPro\14\bin - директория рабочих файлов pgsql
set PGBAKDIR=R:\sql_bak - расположение резервных копий
set PGBAKLOGDIR=R:\sql_bak_log - расположение журналов резервных копий
set PGBAKLOGDETAIL=R:\sql_bak_log\log_detail - расположение подробных журналов резервных копий
set PGBAKSCRIPTDIR=R:\sql_script - расположение служебных файлов (скрипты, списки баз)
set PGHOST=localhost - наименование хоста для подключения к pgsql
set PGUSER=postgres - пользователь для подключения к pgsql
set PGPASSWORD=пароль - пароль для подключения к pgsql
set PGPORT=5432 - порт для подключения к pgsql

Нужно создать следующие папки.

R:\sql_bak - расположение резервных копий
R:\sql_bak_log - расположение журналов резервных копий
R:\sql_bak_log\log_detail - расположение подробных журналов резервных копий
R:\sql_script - расположение служебных файлов (скрипты, списки баз)

Скрипт для резервного копирования, всех баз.

v1.0

@echo off
CLS
setlocal enableextensions enabledelayedexpansion

rem "ввод переменных"
set PGBIN=C:\Program Files\PostgresPro\14\bin
set PGBAK=R:\sql_bak
set PGBAKLOG=R:\sql_bak_log
set PGBAKSCRIPT=R:\sql_script
set PGBAKLOGDETAIL=R:\sql_bak_log\log_detail
set PGHOST=localhost
set PGUSER=postgres
set PGPASSWORD=<пароль>
set PGPORT=5432
set DATETIME=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%_%TIME:~0,2%-%TIME:~3,2%-%TIME:~6,2%
set GLOBALDUMPFILE="%PGBAK%\postgres_globals_%DATETIME%.pgdmp"

rem "очистка старых файлов"
forfiles.exe -p "%PGBAK%" -s -m *.* -d -5 -c "cmd /c del /F /q @path"
forfiles.exe -p "%PGBAKLOGDETAIL%" -s -m *.* -d -5 -c "cmd /c del /F /q @path"

rem "генерация списка всех баз"
rem "здесь можно задать исключения баз"
"%PGBIN%\psql" -X -c "select datname from pg_database where not datistemplate" -A -t -o %PGBAKSCRIPT%\dblist.txt -d template1

rem "дамп всех внутренних пользователей и ролей"
"%PGBIN%\pg_dumpall" --globals-only --file=%GLOBALDUMPFILE%

rem "цикл резервного копирования всех баз с анализом вывода ошибок"
for /f %%i in (%PGBAKSCRIPT%\dblist.txt) do (

rem "вывод текущего статуса внутки цикла"
echo Backing up: %%i

rem "ввод переменных внутки цикла"
set DATETIMEBASE=!DATE:~6,4!-!DATE:~3,2!-!DATE:~0,2!_!TIME:~0,2!-!TIME:~3,2!-!TIME:~6,2!
set DUMPFILE=%%i_!DATETIMEBASE!.pgdmp
set LOGFILE=%%i_!DATETIMEBASE!.log
set DUMPPATH="%PGBAK%\!DUMPFILE!"
set LOGPATH="%PGBAKLOGDETAIL%\!LOGFILE!"

rem "резервное копирование внутки цикла"
"%PGBIN%\pg_dump" --format=custom --verbose --file=!DUMPPATH! %%i 2>!LOGPATH!

rem "анализ ошибок внутки цикла"
if not %ERRORLEVEL%==0 (call :Error) else (call :Successfull)
)
exit /b

rem "в случае ошибки удаляется поврежденная резервная копия и делается соответствующая запись в журнале"
:Error
del !DUMPPATH!
echo !DATETIMEBASE! Error during dump creation !DUMPFILE!. Details in the file !LOGFILE!. >> %PGBAKLOG%\backup.log

exit /b
rem "в случае успешной резервной копии, заносится запись в журнал"
:Successfull
echo !DATETIMEBASE! Successfull dump create !DUMPFILE! >> %PGBAKLOG%\backup.log
exit /b

v1.1

@echo off
CLS
setlocal enableextensions enabledelayedexpansion
rem "ввод переменных"
set PGBIN=C:\Program Files\PostgreSQL\12.6-6.1C\bin
set PGBAK=E:\sql_maintenance\sql_bak
set PGBAKLOG=E:\sql_maintenance\sql_bak_log
set PGBAKSCRIPT=C:\scripts
set PGHOST=localhost
set PGUSER=postgres
set PGPASSWORD=<пароль>
set PGPORT=5432
set DTFILE=D%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%T%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
rem "очистка старых файлов"
forfiles.exe -p "%PGBAK%" -s -m *.* -d -5 -c "cmd /c del /F /q @path"
rem "генерация списка всех баз"
rem "здесь можно задать исключения баз"
"%PGBIN%\psql" -X -c "select datname from pg_database where not datistemplate" -A -t -o %PGBAKSCRIPT%\dblist.txt -d template1
rem "дамп всех внутренних пользователей и ролей"
echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - start - pg_dump of globals >>%PGBAKLOG%\globals.log
"%PGBIN%\pg_dumpall" --globals-only --file=%PGBAK%\globals%DTFILE%.dump 2>>%PGBAKLOG%\globals.log
if not %ERRORLEVEL%==0 (
del %PGBAK%\globals%DTFILE%.dump
echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - error - pg_dump of globals >>%PGBAKLOG%\globals.log
) else (
echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - success - pg_dump of globals >>%PGBAKLOG%\globals.log
)
rem "цикл резервного копирования всех баз с анализом вывода ошибок"
for /f %%i in (%PGBAKSCRIPT%\dblist.txt) do (
set DBNAME=%%i
rem "вывод текущего статуса внутки цикла"
echo Backing up: !DBNAME!
rem "ввод переменных внутки цикла"
set DUMPFILE=!DBNAME!D%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%T%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%.dump
set LOGFILE=!DBNAME!.log
set DUMPPATH="%PGBAK%\!DUMPFILE!"
set LOGPATH="%PGBAKLOG%\!LOGFILE!"
echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - start - pg_dump of !DBNAME! >>!LOGPATH!
rem "резервное копирование внутки цикла"
"%PGBIN%\pg_dump" --format=custom --file=!DUMPPATH! !DBNAME! 2>>!LOGPATH!
rem "анализ ошибок внутки цикла"
if not %ERRORLEVEL%==0 (call :Error) else (call :Success)
)
exit /b
rem "в случае ошибки удаляется поврежденная резервная копия и делается соответствующая запись в журнале"
:Error
del !DUMPPATH!
echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - error - pg_dump of !DBNAME! >>!LOGPATH!
exit /b
rem "в случае успешной резервной копии, заносится запись в журнал"
:Success
echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - success - pg_dump of !DBNAME! >>!LOGPATH!
exit /b

v2.0 - Доработана логика, не делает бэкапы баз с «test» в любом месте названия базы.

Также динамически поддерживает актуальность .log файлов (если базы больше нет, то одноимённый .log файл удаляется из папки \sql_bak_log - это нужно для корректной работы Zabbix)

@echo off
CLS
setlocal enableextensions enabledelayedexpansion
rem "ввод переменных"
set "PGBIN=C:\Program Files\PostgreSQL\15.5-6.1C\bin"
set "PGBAK=C:\pg-dump\sql_bak"
set "PGBAKLOG=C:\pg-dump\sql_bak_log"
set "PGBAKSCRIPT=C:\pg-dump\sql_script"
set "PGHOST=localhost"
set "PGUSER=postgres"
set "PGPASSWORD="
set "PGPORT=5432"
set "DTFILE=D%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%T%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%"

rem "проверка и создание директорий"
if not exist "%PGBAK%" mkdir "%PGBAK%"
if not exist "%PGBAKLOG%" mkdir "%PGBAKLOG%"
if not exist "%PGBAKSCRIPT%" mkdir "%PGBAKSCRIPT%"

rem "очистка старых файлов"
if exist "%PGBAK%\*.*" (
    forfiles.exe -p "%PGBAK%" -s -m *.* -d -5 -c "cmd /c del /F /q @path" 2>>"%PGBAKSCRIPT%\cleanup.log"
    if not %ERRORLEVEL%==0 (
        echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - error - cleanup of old backups >>"%PGBAKSCRIPT%\cleanup.log"
    ) else (
        echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - success - cleanup of old backups >>"%PGBAKSCRIPT%\cleanup.log"
    )
)

rem "генерация списка всех баз с исключением баз, содержащих 'test' в названии, кроме базы 'postgres'"
"%PGBIN%\psql" -X -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND (datname = 'postgres' OR datname NOT ILIKE '%%test%%')" -A -t -o "%PGBAKSCRIPT%\dblist.txt" -d template1 2>>"%PGBAKSCRIPT%\dblist.log"
if not %ERRORLEVEL%==0 (
    echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - error - generating database list >>"%PGBAKSCRIPT%\dblist.log"
    exit /b
)

rem "проверка существования файла dblist.txt"
if not exist "%PGBAKSCRIPT%\dblist.txt" (
    echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - error - dblist.txt not found >>"%PGBAKSCRIPT%\dblist.log"
    exit /b
)

rem "удаление устаревших логов в PGBAKLOG"
for %%F in ("%PGBAKLOG%\*.log") do (
    set "LOGNAME=%%~nF"
    set "FOUND=0"
    for /f "usebackq delims=" %%D in ("%PGBAKSCRIPT%\dblist.txt") do (
        if /i "!LOGNAME!"=="%%D" set "FOUND=1"
    )
    if !FOUND! equ 0 if /i not "!LOGNAME!"=="globals" (
        echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - deleting obsolete log %%F >>"%PGBAKSCRIPT%\cleanup.log"
        del /F /q "%%F"
    )
)

rem "дамп всех внутренних пользователей и ролей"
echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - start - pg_dump of globals >>"%PGBAKLOG%\globals.log"
"%PGBIN%\pg_dumpall" --globals-only --file="%PGBAK%\globals%DTFILE%.dump" 2>>"%PGBAKLOG%\globals.log"
if not %ERRORLEVEL%==0 (
    del "%PGBAK%\globals%DTFILE%.dump"
    echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - error - pg_dump of globals >>"%PGBAKLOG%\globals.log"
) else (
    echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - success - pg_dump of globals >>"%PGBAKLOG%\globals.log"
)

rem "цикл резервного копирования всех баз с анализом вывода ошибок"
for /f "usebackq delims=" %%i in ("%PGBAKSCRIPT%\dblist.txt") do (
    set DBNAME=%%i
    rem "вывод текущего статуса внутри цикла"
    echo Backing up: !DBNAME!
    rem "ввод переменных внутри цикла"
    set DUMPFILE=!DBNAME!%DTFILE%.dump
    set LOGFILE=!DBNAME!.log
    set DUMPPATH="%PGBAK%\!DUMPFILE!"
    set LOGPATH="%PGBAKLOG%\!LOGFILE!"
    echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - start - pg_dump of !DBNAME! >>!LOGPATH!
    rem "резервное копирование внутри цикла"
    "%PGBIN%\pg_dump" --format=custom --file=!DUMPPATH! !DBNAME! 2>>!LOGPATH!
    rem "анализ ошибок внутри цикла"
    if not %ERRORLEVEL%==0 (call :Error) else (call :Success)
)
exit /b

rem "в случае ошибки удаляется поврежденная резервная копия и делается соответствующая запись в журнале"
:Error
del !DUMPPATH!
echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - error - pg_dump of !DBNAME! >>!LOGPATH!
exit /b

rem "в случае успешной резервной копии, заносится запись в журнал"
:Success
echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2% %TIME:~0,2%:%TIME:~3,2%:%TIME:~6,2% - success - pg_dump of !DBNAME! >>!LOGPATH!
exit /b

Настройка Windows Scheduler.

Создаем отдельную папку, создаем задание

Устанавливаем запуск для всех пользователей и запуск с наивысшими правами

Запуск ежедневно в 23:00

Действие — Запуск программы - Указываем путь к скрипту

Резервное копирование всех баз PostgreSQL — Windows

Скрипт для резервного копирования, всех баз.

Настройка Windows Scheduler.

Расчёт стоимости

Спасибо за оставленную заявку!

Заявка на обслуживание

Спасибо за оставленную заявку!

Заказать звонок

Спасибо за оставленную заявку!