- 1 year ago
how to backup postgresql database automatically on daily basis?
You can backup a PostgreSQL database automatically on a daily basis using a combination of the pg_dump
utility and a scheduling tool like cron (on Linux) or Task Scheduler (on Windows). Here's a step-by-step guide on how to do it:
Install PostgreSQL and pg_dump:
Make sure you have PostgreSQL installed on your system, and thepg_dump
utility is available. It's usually installed along with PostgreSQL.Create a script to perform the backup:
Create a script that includes thepg_dump
command to back up the database. Save this script with a.sh
extension for Linux or.bat
for Windows.Linux (backup.sh):
bash#!/bin/bash
# Replace 'DB_NAME', 'DB_USER', 'DB_PASSWORD', and '/path/to/backup/folder' with your actual values
pg_dump -U DB_USER -h localhost DB_NAME > "/path/to/backup/folder/$(date +%Y%m%d)_backup.sql"
Windows (backup.bat):
batch@echo off
REM Replace 'DB_NAME', 'DB_USER', 'DB_PASSWORD', and 'C:\path\to\backup\folder' with your actual values
"C:\path\to\pg_dump.exe" -U DB_USER -h localhost DB_NAME > "C:\path\to\backup\folder\%date:~-4%%date:~7,2%%date:~10,2%_backup.sql"
Set up permissions (Linux only):
If you're using Linux, make sure the script is executable by running the following command:bashchmod +x /path/to/backup.sh
Test the script:
Run the script manually to ensure it's working as expected and generates a backup file in the specified folder.Schedule the backup task:
Use the scheduling tool on your operating system to schedule the script to run daily. Here's how to do it for each OS:Linux (using cron):
Open the crontab editor by running:bashcrontab -e
Add the following line to run the backup script every day at midnight (adjust the time as needed):
bash0 0 * * * /path/to/backup.sh
Save and exit the editor.
Windows (using Task Scheduler):
Open Task Scheduler and create a new basic task.- Select the trigger as "Daily" and set the desired time.
- Choose "Start a program" as the action and browse to the backup.bat file.
- Complete the wizard and save the task.
Now, the backup script will be automatically executed every day, and you'll have a new backup file for your PostgreSQL database created in the specified folder.