Automate Database backup in PostgreSQL

Question

I am looking for a solution - a script to automate the process of taking backup of a database in PostgreSQL. As of now I do it manually, that is by right clicking on the db and clicking the backup option. I did some research and ended up with a script which solves the issue pretty much, ie:

@ECHO OFF
@setlocal enableextensions
@cd /d "%~dp0"

SET PGPATH=C:\PostgreSQL\9.4\bin\
SET SVPATH=d:\
SET PRJDB=Test
SET DBUSR=postgres
FOR /F "TOKENS=1,2,3 DELIMS=/ " %%i IN ('DATE /T') DO SET d=%%i-%%j-%%k
FOR /F "TOKENS=1,2,3 DELIMS=: " %%i IN ('TIME /T') DO SET t=%%i%%j%%k

SET pg_dump=%PRJDB%_%d%_%t%.backup
@ECHO OFF
%PGPATH%pg_dump -h localhost -p 5432 -U postgres %PRJDB% > %SVPATH%%pg_dump%

echo Backup Taken Complete %SVPATH%%pg_dump%
pause 

It did take the backup, but the file generated was a sql file, though I did change the extension to .backup. As a result, if I need to restore the DB, and try to restore from the file generated it is not possible. Can someone please provide me with a solution to this problem. Thanks in advance.

Answer 1

Following script can be used to get the Postgres backup with .backup extension

 @echo off
       for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
         set dow=%%i
         set month=%%j
         set day=%%k
         set year=%%l
       )
       set datestr=%day%_%month%_%year%
       echo datestr is %datestr%

       set BACKUP_FILE=C:\Users\slan\Desktop\backup_test\DBNAME_%datestr%.backup
       echo backup file name is %BACKUP_FILE%
       SET PGPASSWORD=YOUR_PASSWORD
       echo on
       bin\pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f %BACKUP_FILE% YOUR_DB_NAME

---

you must have PostgreSQL's bin folder(Ex. C:\PostgreSQL\9.4\bin) along with this script otherwise this will not work as you expect

To schedule the task you can use Windows Task scheduler, here is an example for how to ?? - How to schedule a Batch File to run automatically in Windows 10 / 8 / 7

Answer 2

You can use PGagent for scheduling batch it have multiple advantages over windows scheduler bat scripts