Hi ,
I recently developed a pg_dump windows based script, which can help us to schedule the pg_dump through pg_Agent or through windows scheduler.
This script also having the backup retention policy and in any case it retain the last two consistant backups. i.e, if the current backup is successful, then it will remove the 2nd last backup from the backup location .
========
Dinesh Kumar
I recently developed a pg_dump windows based script, which can help us to schedule the pg_dump through pg_Agent or through windows scheduler.
This script also having the backup retention policy and in any case it retain the last two consistant backups. i.e, if the current backup is successful, then it will remove the 2nd last backup from the backup location .
@ECHO OFFHow to run
REM pgAgent Windows Pg_Dump Script
set BIN="C:\Program Files\PostgreSQL\9.1\bin"
set BACKUPLOC="C:\Dump"
set LOGLOC="C:\Dump"
set DATABASES=%4
set USER=%3
set PORT=%2
set DT=%date%
set HOST=%1
REM DUMP MODE {CUSTOM,TAR,PLAIN}
set DUMP_MODE=CUSTOM
set RETAIN_LAST_BACKUPS=2
%BIN%\psql -Atq -h %HOST% -U %USER% -p %PORT% -c "select translate(replace('%DT%','/','_'),' ','_');" postgres >_TMP
set /p DT=<_TMP
REM Creating Scheduled Backup
%BIN%\psql -Atq -h %HOST% -U %USER% -p %PORT% -c "select replace('%DATABASES%','""','');" postgres > _TMP
set /p DATABASES=<_TMP
FOR %%D IN ( %DATABASES% ) DO (
%BIN%\psql -Atq -h %HOST% -U %USER% -p %PORT% -c "select 'Pg Dump Start For DB %%D Time :: ' || now();" postgres >> %LOGLOC%\pgDump_%DT%.log
if %DUMP_MODE% EQU CUSTOM ( %BIN%\pg_dump -f %BACKUPLOC%\%HOST%_%%D_%PORT%_pgDump_%DT%.dmp -Fc -b -c -h %HOST% -U %USER% -p %PORT% %%D 2>_Error ) else ( if %DUMP_MODE% EQU TAR ( %BIN%\pg_dump -f %BACKUPLOC%\%HOST%_%%D_%PORT%_pgDump_%DT%.dmp -Ft -b -c -h %HOST% -U %USER% -p %PORT% %%D ) else ( if %DUMP_MODE% EQU PLAIN ( %BIN%\pg_dump -f %BACKUPLOC%\%HOST%_%%D_%PORT%_pgDump_%DT%.dmp -Fp -b -c -h %HOST% -U %USER% -p %PORT% %%D ) else ( exit ) ) ) >> %LOGLOC%\pgDump-%DT%.log
FOR %%E IN ( _Error ) DO IF %%~zE EQU 0 ( FOR /f "tokens=*" %%F IN ( ' dir /a /b /O-D %BACKUPLOC%\%HOST%_%%D_%PORT%_pgDump*.dmp^|more +%RETAIN_LAST_BACKUPS% ') DO ( echo %%F >>_Files_To_Delete ) ) else (
echo Error Occured, Hence Keeping The Last Backups As It Is And Removing This Error Caused Dump ..
del /Q %BACKUPLOC%\%HOST%_%%D_%PORT%_pgDump_%DT%.dmp
)
%BIN%\psql -Atq -h %HOST% -U %USER% -p %PORT% -c "select 'Pg Dump End For DB %%D Time :: '||now();" postgres >> %LOGLOC%\pgDump-%DT%.log
REM Doing Retention Policy..
FOR /f "tokens=*" %%R IN ( 'type _Files_To_Delete' ) DO ( DEL /Q %BACKUPLOC%\%%R )
DEL /Q _Files_To_Delete
)
========
Script_Name <host> <Port> <User> "<DB1>,<DB2>,<DB3>,..."
à°¦ిà°¨ేà°·్ à°•ుà°®ాà°°్Dinesh Kumar
Not so far I have found new cool tool to work with postgresql on win - Valentina Studio. Its free edition can do things more than many commercial tools!!
ReplyDeleteI very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview
Thanks for your inputs.
ReplyDeleteGood work. You have taken care of many possibilities.
ReplyDeleteAimmarPpabeSaint Paul Christina Love https://wakelet.com/wake/EI6YbN8nioijq1a7rBkOc
ReplyDeletegenkahoge
Mexrhinbide Sarah Chambers Express VPN
ReplyDeleteCisco Packet Tracer
Kerish Doctor
andamocomp