Can we make a Script or Job Schedule for PostgreSQL database Back-UP?

Question

I am using PostgreSQL Database, I am running a Database Server .

My PostgreSQL Version is :

postgres=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 32-bit
(1 row)

My Requirement:

Can we make a Script or a Job-Schedule to Run for Keeping the Back-Up of PostgreSQL Database for every 2 Days ?

Is there a way for doing that ?

Can we Run any Scripts or Job-Scheduling Jobs for making this happen?

Is there a way of doing that using PgAgent! I am using PgAdminIII.

I assume you have postgres on a windows machine, so that should do it http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows — foibs, Nov 16 '13 at 18:00
@foibs Can u provide the Script as an answer for making backup For Every 2 Days. It will be help full for the visitors, for this Question. Regards — 09Q71AO534, Nov 24 '13 at 12:48
Ok, I added an answer, mostly taken by the link above. Did you succeed with the automatic backup? — foibs, Nov 25 '13 at 10:50

score 3 · Answer 1 · answered Nov 25 '13 at 10:38

Taken from here

To achieve an automated backup in a windows environment:

1. Create a .pgpass file

(I called mine pgpass.conf) and put it somewhere secure. I have it in a subdirectory under the script that runs the backup.

pgpass contains host access information in this format

hostname:port:database:username:password

Lock down the .pgpass file Using NTFS permissions, disable access to this file for everybody except the user pg is running as (If you're running pg under the system account, then you should set it to use it's own user credentials)

2. Create a script to call pg_dumpall

For example:

SET PGPASSFILE=C:\foo\bar\PG_BACKUP\PGPASSFILE\pgpass.conf
"C:\Program Files\PostgreSQL\9.2\bin\pg_dumpall.exe" -U postgres_username  > C:\foo\bar\PG_BACKUP\db.out current

The firstline is the location of your pgpass file. The second line is the pg_dumpall command which outputs your whole database to the file C:\foo\bar\PG_BACKUP\db.out

3. Create a scheduled task

The command is

C:\Windows\System32\cmd.exe /c "C:\foo\bar\PG_BACKUP\pg_backup.bat"

and it starts in the directory

C:\foo\bar\PG_BACKUP

Here's a good tutorial on how to create scheduled tasks on windows:

http://www.makeuseof.com/tag/how-to-automate-windows-programs-on-a-schedule/.

You can find plenty such tutorials and info with a simple search.

Can we make a Script or Job Schedule for PostgreSQL database Back-UP?

1 Answers1