Database

Setting Up Scheduled Backups for PostgreSQL Using Cron

8 months ago

PostgreSQL, a powerful open-source relational database, is widely used for a variety of applications. Keeping regular backups of your PostgreSQL database is crucial for disaster recovery, preventing data loss in case of failures or accidental deletions. In this guide, we will go over the process of scheduling automated backups using cron jobs in a Linux environment, based on the commands you provided.

Steps to Set Up PostgreSQL Scheduled Backups

1. Create a Backup Directory

First, you need to create a directory to store your backups. Use the following command:

Bash
sudo mkdir -p /pgsql/backups

The -p option ensures that the entire directory path is created, even if the parent directories don’t exist. This directory will house all your database backups.

2. Create pg_backup.sh File

Bash
#!/bin/sh
#Backup storage directory
backupfolder=/pgsql/backups
#create backup folder
mkdir -p $backupfolder
##############################################################
db_name = Your Db Name

sqlfile=$backupfolder/$db_name-$(date +%d-%m-%Y_%H-%M-%S).sql
zipfile=$backupfolder/$db_name-$(date +%d-%m-%Y_%H-%M-%S).zip

# Create a backup
if PGPASSWORD="password!!" pg_dump $db_name >$sqlfile; then
   echo $db_name ' Sql dump created'
else
   echo $db_name ' pg_dump return non-zero code'
   exit
fi

# Zip Backup File
if gzip -c $sqlfile >$zipfile; then
   echo $db_name ' The backup was successfully compressed'
else
   echo $db_name ' Error compressing backup'
   exit
fi

# Delete raw SQL file
rm $sqlfile
echo $zipfile
##############################################################

The script you’ve provided is a shell script designed to create a backup of a PostgreSQL database, compress it into a .zip file, and delete the raw .sql dump.

3. Set Ownership and Permissions

For security and functionality, it’s important that the PostgreSQL user has ownership of the necessary files and directories. This allows the PostgreSQL system user to access and modify backup files.

Bash
sudo chown postgres /backups/psql
sudo chown postgres /backups/pg_backup.sh
chmod u+x /backups/pg_backup.sh
  • sudo chown postgres: Changes the ownership of the specified files and directories to the PostgreSQL system user (postgres).
  • chmod u+x: Makes the backup script (pg_backup.sh) executable by the user.

4. Scheduling the Backup with Cron

To automate the backup process, we’ll use cron, a powerful job scheduler in Unix-like systems. Cron allows you to execute commands or scripts at specific intervals.

Open the crontab file to schedule tasks:

Bash
crontab -e

Now, let’s add two cron jobs: one to back up the database and another to delete old backups to free up space.

Backup Cron Job

The following cron job schedules a database backup every day at 12:00 PM:

Plaintext
0 12 * * * su postgres -c /backups/pg_backup.sh >> /backups/pg_backup.log 2>&1
  • 0 12 * * *: This specifies the time schedule — in this case, every day at 12:00 PM.
  • su postgres -c /backups/pg_backup.sh: This switches to the postgres user and runs the backup script.
  • >> /backups/pg_backup.log 2>&1: This logs the output of the script to pg_backup.log, including any errors.
Cleanup Cron Job

To avoid filling up disk space with old backups, schedule a job that deletes old backups at 12:40 PM daily:

delete_old_backups.sh
Bash
#!/bin/sh
#Backup storage directory
backupfolder=/pgsql/backups

# Number of days to store the backup
keep_day=2

# Delete old backups
find $backupfolder -mtime +$keep_day -delete
Plaintext
40 12 * * * su root /backups/delete_old_backups.sh >> /backups/delete_old_backups.log 2>&1

This ensures that outdated backups are automatically deleted, maintaining only the most recent backups.

5. Testing the Backup Script Manually

To ensure the backup script works, you can run it manually using the following command:

Bash
su -s "/pgsql/pg_backup.sh" postgres

This switches to the postgres user and executes the backup script, /pgsql/pg_backup.sh.

6. Running the Backup with Sudo

Sometimes, to bypass permission issues or ensure elevated access, you might need to run the script using sudo:

Bash
echo Password | sudo -S -u postgres sh /pgsql/pg_backup.sh

In this command:

  • echo "Password": Sends the password to the sudo prompt (in a real-world scenario, avoid hardcoding passwords like this).
  • sudo -S -u postgres sh /pgsql/pg_backup.sh: Runs the backup script as the postgres user with elevated privileges.

Conclusion

By following these steps, you can successfully set up scheduled backups for your PostgreSQL databases using cron jobs. Automating backups ensures that your data is regularly saved and protected, without requiring manual intervention. Remember to also test your backup process and verify that the backups are valid, as part of your disaster recovery strategy.

Make sure to fine-tune the retention and backup times based on your system’s usage and storage constraints!

Tags:
Shares: