4

I just want to know how to do backup of postgresql database on daily basis automatically?

Can someone share the complete process with me I am new to postgres and want to learn database backup, i know how to do it manually but is there is any way so that i schedule the database backup process on daily basis.?

Improve this question

1 Answer 1

Reset to default
13

Your can do the following:

Windows version

Create a file backupDB.bat

@echo off
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
set BACKUP_FILE=BackupDB_%datestr%.bck
SET PGPASSWORD=<PassWord>
echo on
pg_dump -h <HostName> -p 5432 -U <UserName> -F c -b -v -f %BACKUP_FILE% <DATABASENAME>

and then use Windows Task Scheduler

Linux version

Create a file backupDB.sh

#!/bin/bash
date=$(date '+%Y-%m-%d')
PGPASSWORD="**_PASSWORD_**" pg_dump --host 127.0.0.1 --port 5432 -U **_USERNAME_** --format custom --blobs --verbose --file "DB_backup_$date.bck" **_DBNAME_**

To run this every day - lets say at 1:00 - you can use cron

crontab -e

contab entry:

0 1 * * *   ./backupDB.sh

Hope that helps

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

hey, thanks for your help, will get back to you after running these, i think it's for ubuntu but i am working on window. Any major change which i have to do to run it in window? I will create a batch file of above and schedule a task through window task scheduler. Will this approach works?
And then daily back up the pg_dump to the cloud or somewhere off site
Updated answer, added a windows script version
It is better to add some more details.
Thanks for your answer, just want to note that on Windows, the file extension should be .bat instead of .sh. The windows task scheduler runs .bat files natively.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.