how to backup postgresql database automatically on daily basis?

Question

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.?

Answer 1

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