Automate Daily PostgreSQL Database Backups on Ubuntu

Secure your PostgreSQL databases with automated daily backups at 5:00 AM, compressed with tar.gz, and implement a 7-day backup retention policy on your Ubuntu VPS.

Prerequisites:

• Ubuntu VPS
• Root or sudo privileges
• PostgreSQL installed

Steps:

1. Create Backup Script:

nano /usr/local/bin/postgresql-backup.sh

2. Add Backup Script Content:

#!/bin/bash

# Set backup directory
backup_dir="/var/backups/postgresql"

# Get current date
current_date=$(date +%Y-%m-%d)

# Backup all databases
pg_dumpall | gzip > $backup_dir/$current_date.sql.gz

# Delete backups older than 7 days
find $backup_dir -type f -mtime +7 -delete

3. Make Script Executable:

chmod +x /usr/local/bin/postgresql-backup.sh

4. Schedule Backup:

crontab -e

5. Add Cron Job:

0 5 * * * /usr/local/bin/postgresql-backup.sh

6. Save Crontab:

Save and exit the crontab editor.

Example Backup Script Execution:

/usr/local/bin/postgresql-backup.sh

Explanation:

• backup_dir="/var/backups/postgresql": Sets the backup directory.
• current_date=$(date +%Y-%m-%d): Gets the current date.
• pg_dumpall | gzip > $backup_dir/$current_date.sql.gz": Backs up all databases and compresses them with tar.gz.
• find $backup_dir -type f -mtime +7 -delete: Deletes backups older than 7 days.

Benefits of Automated Backups:

• Data Protection: Safeguards your PostgreSQL databases from data loss in case of hardware failures or accidental deletions.
• Disaster Recovery: Enables quick restoration of databases in the event of a disaster.
• Reduced Downtime: Minimizes downtime during data recovery.

By automating daily PostgreSQL database backups, you can ensure the integrity and availability of your data, protecting your Ubuntu VPS from potential data loss incidents.