current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

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.

share|improve this question
    
In which OS ??.. – wingedpanther Dec 11 '15 at 5:57
    
Windows7 - 64bit – Anju Dec 11 '15 at 5:57
    
So you just want automate postgresql backup like scheduling the backup or what ?? – wingedpanther Dec 11 '15 at 5:58
    
Yes, schedule a process to take the backup. – Anju Dec 11 '15 at 6:00
    
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 dont you need a solution for this ?? – wingedpanther Dec 11 '15 at 6:01
up vote 1 down vote accepted

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

share|improve this answer
    
No.. its not the DB name.. its the path where the batch file and the bin is stored!! – Anju Dec 11 '15 at 10:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.