Skip to content

Instantly share code, notes, and snippets.

Last active June 11, 2024 09:41
Show Gist options
  • Save CodMonk/548c578a3b821e048d57 to your computer and use it in GitHub Desktop.
Save CodMonk/548c578a3b821e048d57 to your computer and use it in GitHub Desktop.
Batch file to backup PostgreSQL DB
@echo off
goto comment
Author: Code Monk
Description: This file use to backup the database using PostgreSQL backup utility "pg_dump".
Backup parameters should be setup before executing this file
File Format flags :
c = Custom
t = Tar
p = Plain SQL
Follow links below to apply more flags to "pg_dump" :
Table Excluding Flags:
-T table_name
REM "Set following backup parameters to take backup"
SET db_name=monk_db
SET file_format=c
SET host_name=localhost
SET user_name=postgres
SET pg_dump_path="C:\Program Files (x86)\PostgreSQL\9.3\bin\pg_dump.exe"
SET target_backup_path=C:\Monk\
SET other_pg_dump_flags=--blobs --verbose -c
REM Fetch Current System Date and set month,day and year variables
for /f "tokens=1-3 delims=- " %%i in ("%date%") do (
set month=%%j
set day=%%i
set year=%%k
for /f "tokens=1-3 delims=: " %%i in ("%time%") do (
set hour=%%i
set min=%%j
set sec=%%k
REM Creating string for backup file name
for /f "delims=" %%i in ('dir "%target_backup_path%" /b/a-d ^| find /v /c "::"') do set count=%%i
set /a count=%count%+1
set datestr=backup_%year%_%month%_%day%_%hour%_%min%
REM Backup File name
set BACKUP_FILE=%db_name%_%datestr%.backup
REM :> Executing command to backup database
%pg_dump_path% --host=%host_name% -U %user_name% --format=%file_format% %other_pg_dump_flags% -f %target_backup_path%%BACKUP_FILE% %db_name%
Copy link

thank you,

It helped a lot.

Copy link

It may be worthwhile to add a comment in the part that parses date/time that this is dependent on system settings and formatting of these dates and times. So, someone who has dd/MM/YYYY in its setting will cause this script to not function properly. I haven't found a good solution to this issue short of using vbscript.

Copy link

thank you.

Copy link

Thanks, very usefull

Copy link

SoizicT commented Jun 27, 2022


Copy link

when I run the script, it asking for password, is there any way to set passord in batch file and use that without asking for password

Copy link

Thanks !!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment