Windows环境下pg_dump远程备份postgresql多个表数据脚本

pg_dump备份脚本

脚本功能:
pg_dump工具远程备份postgresql数据库多个表数据，并压缩备份文件，保留最新的7个备份文件，如果需要可以修改参数即可。

将以下脚本内容拷贝到expdp_db.bat 保存为bat格式的文件，同时创建一个tablelist.txt的文本把需要备份的表名字加入到文本里面，把bat加入到定时任务即可实现自动备份。

这里只是部署了一个导出的脚本，后续会更新一个自动导入的脚本，请留意，谢谢。
tablelist.txt文本内容

table1
table2
table3

expdp_db.bat 脚本内容：

@echo off

REM database infomation
set db=dbname
set host=10.x.x.x
set port=5436
set user=postgres
set output_base_dir="D:\DMP\CCMDB"
set pg_dump_dir="D:\Program Files\PostgreSQL\12\bin\"
REM set zip tool path
set ziptool="C:\Program Files\7-Zip\7z.exe"
REM KEEP DAYS
set keep_days=7*

REM set date time
set datevar=%date:~0,4%%date:~5,2%%date:~8,2%
set timevar=%time:~0,2%
set timevar=%timevar%%time:~3,2%%time:~6,2%

REM set backup file path
set backup_file_dir=%output_base_dir%\%db%_%datevar%
if not exist "%backup_file_dir%" (
    mkdir "%backup_file_dir%"
    echo  %backup_file_dir% Create Successfully!
) else (
    echo  %backup_file_dir% is already exist!
)

REM set backup logfile path
set logfile_path="%output_base_dir%\logs"
set logfile="%output_base_dir%\logs\backuplog_%datevar%%timevar%.log"
if not exist "%logfile_path%" (
    mkdir "%logfile_path%"
    echo  %logfile_path% Create Successfully!
) else (
    echo  %logfile_path% is already exist!
)
call :LOG > %logfile%
exit /B

:LOG
echo ------------------------------------------------------------------------------------------------
echo ---------------- Record the start time ------------------------------------------------------
echo ------------------------------------------------------------------------------------------------
set /a startS=%time:~6,2%
set /a startM=%time:~3,2%
echo Start_Time %time%
echo -------------------------------------------------------------------------------------------------
echo ---------------- begin backup table ---------------------------------------------------------
echo -------------------------------------------------------------------------------------------------
echo.
echo.
REM backup all tables in table_list
set tabname=''
setlocal enabledelayedexpansion
d:
cd %output_base_dir%
echo Current floder %cd%
for /f %%a in (.\tablelist.txt) do (
	echo ---------------------------------------------------------------
    set tabname=%%a
	echo Start export !tabname!
	%pg_dump_dir%\pg_dump -d %db% -h %host% -p %port% -U %user% -w -f %backup_file_dir%\!tabname!.dmp -Fc -t !tabname!
	echo ---------------------------------------------------------------
	echo.
)
echo.
echo -------------------------------------------------------------------------------------------------
echo ---------------- end backup table ---------------------------------------------------------
echo -------------------------------------------------------------------------------------------------
echo.
echo.
echo ------------------------------------------------------------------------------------------------
echo ---------------- Start compressing the backup folder ---------------------------------------
echo ------------------------------------------------------------------------------------------------
d:
cd %output_base_dir%
echo Current floder %cd%
%ziptool% a -tzip %backup_file_dir%.zip %backup_file_dir%
echo ---------------- delele backup folder -----------------------------------------------------------
echo.
echo.
echo %backup_file_dir%
rd /s /Q %backup_file_dir%
echo ------------------------------------------------------------------------------------------------
echo ---------------- End compressing the backup folder ------------------------------------------
echo ------------------------------------------------------------------------------------------------
echo.
echo.
echo -------------------------------------------------------------------------------------------------
echo ---------------- delete before 7 days backupfile  ---------------------------------------
echo -------------------------------------------------------------------------------------------------
d:
cd %output_base_dir%
echo Current floder %cd%
forfiles /p %output_base_dir% /s /m *.zip /d -%keep_days% /c "cmd /c del @path"
echo.
echo.
echo ------------------------------------------------------------------------------------------------
echo ---------------- Record backup time stake -------------------------------------------------
echo ------------------------------------------------------------------------------------------------
echo.
set /a endS=%time:~6,2%
set /a endM=%time:~3,2%
echo End_Time %time%
set /a diffS_=%endS%-%startS%
set /a diffM_=%endM%-%startM%
echo.
echo.
echo Cost_Time %diffM_% Min %diffS_% Second
echo.
echo.
echo -------------------END----------------------