Search code examples
postgresqlbackup

Automating Postgres Database Backup at Midnight

I own cloud VM to run my DB base web applicaitons on Windows OS. I am looking for a method to backup the database automatically at every midnight. Postgres does not seem to have an automatic scheduler for this task. Is there any way we can automate the DB backup at midnight every day?

Solution

  • Yup, Got it with little Search and mixed soultions from stackoverflow. It was quick for me so Made a tutorial for others.

    PostgreSQL 9.6 does not provide any built-in feature for Automatic Database Backup. Following is the step-wise tutorial for automating DB backup.

    Step 1. Login into your DB Node VM.

    Step 2. Create pgpass.conf Pgpass.conf is a text file containing the Database connection details. This must be created manually. Open Command Prompt and type the following commands in the given sequence or place them in a .BAT or .CMD file and execute it

    CD %appdata%
MD postgresql
CD postgresql
Echo <host>:<port_number>:<db_name>:<password> >> pgpass.conf

    %appdata% - Is the directory under `C:\Users<Windows_loggedin_user_ID>\AppData\Roaming\postgresql

    PS: Don’t mistake the above path with where Postgresql has been installed.

    < host> - Hostname will be localhost

    <port_number> – The default is 5432, else use the port number as configured for your Postgresql

    <db_name> is the name of the Database that is planned to backedup

    < password> - Database access password.

    Important Point: Above values need to be separated or delimited by Colon (:) as shown above.

    On executing the above commands, pgpass.conf file will be created at C:\Users\<Windows_loggedin_user_ID>\AppData\Roaming

    Step 3. Create Backup Folder A Folder where all the Backed up files will be saved.

    MD C:\<backup_folder_name>  - Will Create a New Folder For ex MD C:\RakyBackup
CD C:\<backup_folder_name>  - Will Take control that New Folder (C:\RakyBackup)

    Step 4. Create Backup Script. File and name it as BackupScript.Bat

    Create a .BAT File using any text Editor and place the following script in it.

    @echo off
Set backupPath= C:\RakyBackup
REM Gets the Current System Date into Variable datetime
For /f %%a in (‘powershell –Command “Get-Date –format dd_MMM_yyyy_HHMM”’) do set datetime=%%a
REM Backsup the DB
<path>\Postgresql\<ver_num>\bin\pg_dump -h localhost -p <5432> -U postgres -F c -b -v <db_name> > %backupPath%/DB-Backup_%datetime%h.sql

    Step 5. Create a Schedule Open Windows Task Scheduler. There will three vertical panels. The Left Panel will show Task Schedule Library. On the Rightmost Panel select Create Basic Task. Give a Name and Describe it in the input textboxes and click next. In the Trigger, Select the Daily and click Next. Set the Start Date and Time. Click Next. In the Action, again click Next. The panel will prompt for Program/Script. Click Browse and Select the C:\RakyBackup\BackupScript.Bat. Click Next and then Click Finish.

    That’s it.

    Everyday at the Set time, BackupScript.Bat will get triggered and DB will automatically be backed up.