Skip to content

Instantly share code, notes, and snippets.

@CodMonk
Last active February 2, 2024 11:20
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • 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" :
http://www.postgresql.org/docs/8.4/static/app-pgdump.html
https://www.commandprompt.com/ppbook/x17860
http://www.brownfort.com/2014/10/backup-restore-postgresql/
Table Excluding Flags:
-T table_name
:comment
REM "Set following backup parameters to take backup"
SET PGPASSWORD=monk_5578
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%
@fashionmacker
Copy link

thank you.

@EduCanova
Copy link

Thanks, very usefull

@SoizicT
Copy link

SoizicT commented Jun 27, 2022

Thanks

@Aniketbobade
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

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