How to Backup PostgreSQL Databases on Ubuntu Automatically Daily

If you’re using Ubuntu and need to back up your PostgreSQL databases regularly, automating the backup process is a smart choice. In this guide, we’ll show you how to write a bash script to back up all PostgreSQL databases, compress the backups, and schedule them to run automatically at 11:00 PM every day.

Step 1: Write a Bash Script to Backup PostgreSQL Databases

Create a new file for the backup script (e.g., postgres_backup.sh) and paste the following content:

#!/bin/bash

# PostgreSQL login information
PG_USER="postgres"         # PostgreSQL username
PG_PASS="your_password"    # PostgreSQL password (optional for local connections)
BACKUP_DIR="/home/postgresql/backup"  # Backup directory

# Get current date in MM_DD_YYYY format
DATE=$(date +"%m_%d_%Y")

# Create a directory for today's backup if it doesn't exist
mkdir -p "$BACKUP_DIR/$DATE"

# Get a list of all databases except system databases
DATABASES=$(psql -U $PG_USER -d postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;")

# Loop through each database and create a backup
for DB in $DATABASES; do
    # Backup each database and compress it with gzip
    pg_dump -U $PG_USER $DB | gzip > "$BACKUP_DIR/$DATE/$DB.tar.gz"
done

# Remove backups older than 7 days (optional, customize as needed)
find $BACKUP_DIR -type d -mtime +7 -exec rm -rf {} \;

echo "Backup completed for all PostgreSQL databases on $DATE."

Explanation of the Script:

• PG_USER and PG_PASS: Your PostgreSQL username and password (note: password is optional for local connections if PostgreSQL is configured to allow trust authentication).
• BACKUP_DIR: The directory where backups will be stored.
• DATE: The current date in the MM_DD_YYYY format for organizing backups by date.
• DATABASES: Fetches the list of databases excluding system databases.
• pg_dump: Dumps the content of each database and compresses it with gzip.
• find: Removes backups older than 7 days. You can adjust this retention period as needed.

Step 2: Grant Execute Permission for the Script

Once you’ve created the script, make it executable by running the following command:

chmod +x /path/to/postgres_backup.sh

Step 3: Set Up a Cron Job to Run the Script Automatically

To schedule the script to run at 11:00 PM every day, edit your crontab:

crontab -e

Add the following line to the file:

0 23 * * * /path/to/postgres_backup.sh

Explanation:

• 0 23 * * *: This cron schedule means the job will run every day at 11:00 PM (23:00).
• /path/to/postgres_backup.sh: The full path to the backup script you created.

Step 4: Verify Permissions and Ownership

Ensure the PostgreSQL user has appropriate permissions, and check that the backup directory /home/postgresql/backup is writable. The script should be able to run without issues if the permissions are set correctly.

You can check the status of the cron service with:

systemctl status cron

Step 5: Check Cron Job Logs

Cron jobs run in the background, and you can check the logs to confirm that your backup script is running as scheduled:

grep CRON /var/log/syslog

If the script runs successfully, you should see new backup files in /home/postgresql/backup.

---

Additional Tips

• Make sure that PostgreSQL user (PG_USER) has the necessary permissions to access and back up the databases.
• You can securely store PostgreSQL credentials by creating a .pgpass file in the home directory for the PostgreSQL user. This file allows for password-less authentication.
• Monitor disk space to ensure there’s enough room to store the backups, especially if you have large databases.

---

Conclusion

By following these steps, you can automate PostgreSQL backups on Ubuntu. The script will back up all databases daily at 11:00 PM and compress them to save storage space. With the cron job in place, you’ll have up-to-date backups without manual intervention, ensuring the safety of your database data.