CODEWITHSUNDEEP
postgresqljob-schedulingpgadminpgagent
09Q71AO534
09Q71AO534

Reputation: 4440

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

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.

Upvotes: 3

Views: 13141

Answers (1)

foibs
foibs

Reputation: 3406

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.

Upvotes: 3

Related Questions