Click here to Skip to main content
15,861,168 members
Articles / Database Development / PostgreSQL
Tip/Trick

Auto Backup for PostgreSQL

Rate me:
Please Sign up or sign in to vote.
4.50/5 (3 votes)
15 Sep 2012BSD4 min read 47.7K   1.6K   6   2
Generate auto backup for PostgreSQL
Sample Image

Introduction

In this article, I would like to show you how to create a Windows batch file to take backups from a PostgreSQL database.

Background

When I was working with a PostgreSQL database for one of my projects, I needed an auto system for taking backups daily. After some research, I came up with this idea to create a Windows batch file and put it under the Windows Scheduler.

To prepare for a chart, use these steps given below.

How to Configure

Step 1

Download the batch file.

Step 2

You can start the Task Scheduler MMC snap-in by using a single command from the command line or by using the Windows interface. Task Scheduler can also be started by double-clicking the Taskschd.msc file in the %SYSTEMROOT%\System32 folder.

To run Task Scheduler using the Windows Interface

Click the Start button. Click Control Panel. Click System and Maintenance. Click Administrative Tools. Double-click Task Scheduler.

To run Task Scheduler from the Command Line

Open a command prompt. To open a command prompt, click Start, click All Programs, click Accessories, and then click Command Prompt. At the command prompt, type Taskschd.msc. The Schtasks.exe command line tool enables a user to complete many of the same operations that they can complete using the Task Scheduler MMC snap-in. This tool enables a user to create, delete, query, change, run, and end scheduled tasks on a local or remote computer. This tool is located in the %SYSTEMROOT%\System32 folder. Type Schtasks.exe /? from a command prompt window to view the help for the tool.

Step 3

You can schedule a task by either creating a basic task using the Create Basic Task Wizard or by creating a task without the wizard and supplying task information in the Create Task dialog box. The procedures below describe how to create a task using either method. If you create a basic task using the Create Basic Task Wizard, most of the task properties will be set to their default values, and you choose a trigger for the task from the most commonly used triggers. For more information about triggers, see Triggers.

You can import a task that is defined in an XML file. For more information, see Import a Task. For information on creating a task on a remote computer, see Manage or Create a Task on a Remote Computer.

To Create a Basic Task by Using the Windows Interface

If Task Scheduler is not open, start Task Scheduler. For more information, see Start Task Scheduler. Find and click the task folder in the console tree that you want to create the task in. For more information about how to create the task in a new task folder, see Create a New Task Folder. In the Actions Pane, click Create Basic Task. Follow the instructions in the Create Basic Task Wizard.

To Create a Task by Using the Windows Interface

If Task Scheduler is not open, start Task Scheduler. For more information, see Start Task Scheduler. Find and click the task folder in the console tree that you want to create the task in. If you want to create the task in a new task folder, see Create a New Task Folder to create the folder. In the Actions Pane, click Create Task. On the General tab of the Create Task dialog box, enter a name for the task. Fill in or change any of the other properties on the General tab. For more information about these properties, see General Task Properties.

On the Triggers tab of the Create Task dialog box, click the New… button to create a trigger for the task, and supply information about the trigger in the New Trigger dialog box. For more information about triggers, see Triggers. On the Actions tab of the Create Task dialog box, click the New… button to create an action for the task, and supply information about the action in the New Action dialog box. For more information about actions, see Actions.

(Optional) On the Conditions tab of the Create Task dialog box, supply conditions for the task. For more information about the conditions, see Task Conditions.

(Optional) On the Settings tab of the Create Task dialog box, change the settings for the task. For more information about the settings, see Task Settings.

Click the OK button on the Create Task dialog box.

To Task by Using a Command Line

Open a command prompt. To open a command prompt, click Start, click All Programs, click Accessories, and then click Command Prompt.

Type:

schtasks /Create [/S <system> [/U <username> [/P [<password>]]]]
    [/RU <username> [/RP <password>]] /SC <schedule> [/MO <modifier>] [/D <day>]
    [/M <months>] [/I <idletime>] /TN <taskname> /TR <taskrun> [/ST <starttime>]
    [/RI <interval>] [ {/ET <endtime> | /DU <duration>} [/K] [/XML <xmlfile>] [/V1]]
    [/SD <startdate>] [/ED <enddate>] [/IT] [/Z] [/F]

To view the help for this command, type:

schtasks /Create /?
Additional Considerations

If the Remote Scheduled Tasks Management exception is disabled and the File and Printer Sharing exception is enabled in the Windows Firewall settings, and the Remote Registry service is running, a V1 task will be created on the remote computer even when the V1 parameter is not specified. The V1 parameter specifies that a task is visible to down-level systems.

Using the Code

Script

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

SET PGPATH=C:\"Program Files"\PostgreSQL\9.1\bin\
SET SVPATH=f:\
SET PRJDB=demo
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 DBDUMP=%PRJDB%_%d%_%t%.sql
@ECHO OFF
%PGPATH%pg_dump -h localhost -p 5432 -U postgres %PRJDB% > %SVPATH%%DBDUMP%

echo Backup Taken Complete %SVPATH%%DBDUMP%

Initial Value

  • PGPATH - PostgreSQL path
  • SVPATH - Backup File path
  • PRJDB - Name of the Database which will be backup
  • DBUSR - Database user name

References

License

This article, along with any associated source code and files, is licensed under The BSD License


Written By
Software Developer (Senior) icddr,b
Bangladesh Bangladesh
More than 8 years experience on Programming and Project implementation, I was primarily involved with projects for private organization,Govt.(Bangladesh Army,DG Health,RJSC), NGO (SEDF,WFP). Presently I am working at ICDDR,B and enhancing Hospital Management System developed by Microsoft Dynamic NAV and Windows Mobile Application 5.0

An active supporter of Open Source technology, my interested areas are ERP, IT Audit, Data warehouse, BI etc.

Playing Guitar for 15 years, my interested music style is Blues Rock,Neo Classical.

Certification

70-540:Microsoft® Windows Mobile® 5.0 - Application Development
MB7-514:Microsoft Dynamics™ NAV 5.0 C/SIDE Introduction
MB7-516:Microsoft Dynamics™ NAV 5.0 Solution Development
MB7-517:Microsoft Dynamics™ NAV 5.0 Installation and Configuration
MB7-515:Microsoft Dynamics™ NAV 5.0 Financials
70-432:Microsoft SQL Server 2008 - Implementation and Maintenance
70-450:PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008
70-448:Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
312-50:Certified Ethical Hacker

Web :http://masudparvezshabuz.appspot.com
Blog :http://masudparvezshabuz.wordpress.com
linkedin :http://www.linkedin.com/in/masudparvez

Comments and Discussions

 
QuestionBacking up multiple databases using the same script Pin
Member 1219455815-Dec-15 13:10
Member 1219455815-Dec-15 13:10 
QuestionPassword Pin
Sam Gerené1-Apr-14 22:23
Sam Gerené1-Apr-14 22:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.