Using Cron Jobs in Ubuntu to Schedule Backup and Restore PostgreSQL Database

PostgreSQL Basic Commands:

Backup Command:

To create a backup of your PostgreSQL database, use the pg_dump command as follows:

pg_dump -h <db_host> -U <username> -d <database> > backup.sql

• <username>: Replace with your PostgreSQL username.
• <database>: Replace with the name of the database to back up.
• backup.sql: The file where the backup will be saved.

Restore Command:

To restore the database from a backup file, use the psql command:

psql -h <db_host> -U <username> -d <database> -f backup.sql

• <username>: PostgreSQL username.
• <database>: The database where the data will be restored.
• backup.sql: The file containing the backup data.

Automating Backups with Cron Jobs

Automating PostgreSQL backups ensures regular snapshots of your data without manual intervention. Here's how you can set it up:

1. Create a Backup Script

Create a shell script (ex: backup_database.sh) to dump the database and store the backup file.

#!/bin/bash

# Define variables
BACKUP_DIR="/path/to/backup/directory"  # Replace with your desired backup directory
DATE=$(date +%F)                        # Get current date in YYYY-MM-DD format
DB_NAME="your_database_name"            # Replace with your database name
DB_USER="your_username"                 # Replace with your PostgreSQL username
DB_PASSWORD="your_password"             # Replace with your PostgreSQL password

# Ensure the backup directory exists
mkdir -p "$BACKUP_DIR"

# Dump the database
PGPASSWORD="$DB_PASSWORD" pg_dump -U "$DB_USER" "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_backup_$DATE.sql"

# Optional: Remove backups older than 7 days
find "$BACKUP_DIR" -type f -name "${DB_NAME}_backup_*.sql" -mtime +7 -exec rm {} \;

Save this script as backup_database.sh in a secure location, such as /home/youruser/scripts/.

2. Make the Script Executable

Ensure the script has executable permissions by running:

chmod +x /home/youruser/scripts/backup_database.sh

3. Test the Script

Run the script manually to verify it works as expected:

/home/youruser/scripts/backup_database.sh

4. Schedule the Backup with Cron

To automate the script, use a cron job. Follow these steps:

1) Open the crontab editor:

crontab -e

2) Add the following line to schedule the script to run daily at 2:00 AM and generate logs:

0 2 * * * /home/youruser/scripts/backup_database.sh >> /var/log/db_backup.log 2>&1

• 0: Minute (0th minute)
• 2: Hour (2 AM)
• *: Day of the month (every day)
• *: Month (every month)
• *: Day of the week (every day)

5. Verify the Cron Job

List the existing cron jobs to confirm the backup task was added:

crontab -l

6. Check Backups and Logs

After the cron job has run, verify that:
1. Backup files are created in the specified directory (/path/to/backup/directory).
2. Logs are recorded in /var/log/db_backup.log.

Key Notes

• Database Password Security: Avoid hardcoding the database password in the script. Use environment variables or PostgreSQL’s .pgpass file for better security.

Step to use PostgreSQL's .pgpass:

1) Create a file named .pgpass in your home directory:

nano ~/.pgpass

2) Add a line with your database credentials in the following format:

hostname:port:database:username:password

3) Restrict file permissions to prevent unauthorized access:

chmod 600 ~/.pgpass

4) Update backup script:

#!/bin/bash

# Define backup directory and date
BACKUP_DIR="/path/to/backup/directory"
DATE=$(date +%F)

# Use pg_dump without specifying password (it will use .pgpass)
pg_dump -h hostname -p port -U "your_username" -d "your_database_name" > "$BACKUP_DIR/${DB_NAME}_backup_$DATE.sql"

# Optional: Remove backups older than 7 days
find "$BACKUP_DIR" -type f -name "${DB_NAME}_backup_*.sql" -mtime +7 -exec rm {} \;

5. PostgreSQL tools automatically search for .pgpass in the user’s home directory.

• Error Handling: Include error-checking mechanisms in the script to ensure smooth operation and notify you in case of failures.
• Log Rotation: Manage the size of the log file (/var/log/db_backup.log) by setting up log rotation using logrotate if needed.

By following this guide, you can set up a robust, automated backup system for your PostgreSQL database, ensuring data safety and minimizing manual work.

If you found this guide helpful, consider supporting us!