A Shell Script to backup your database

Database Backup

There may be scenarious where exorbitant amount of data is written to your database on daily basis or your database is overflowing with years of data and has become exceedingly large and costly to maintain. One of the ways to keep your database clean and efficient is to backup the older records that won’t be needed for daily transactions and delete them from your database. How do you do this? One way is to write a shell script, which takes the backup of database, archive the backup and store on the local or remote system.

Ready to backup your database? Then, let’s write our shell script. This tutorial explains backing up a table in PostgreSQL database in a Linux system. You can modify this to be used with other systems and databases as well.

The first line in our script will be:

#!/bin/sh

This is known as shebang. It points to one of the configured shell in the system. So in case if you are using #!/bin/sh in your script then the behaviour of your script will differ based on which shell sh is pointing to your system. If you are using #!/bin/bash, it always points to bash.

Then, we’ll define a folder to save the backed up files.

BACKUP_HOME='/opt/backup'

We’ll also have to define which data to backup.

if [ $# -ne 0 ]
then
  LOGS_END_DATE=$1
else
  LOGS_END_DATE=`date --date="yesterday -90 days" "+%Y-%m-%d"`
fi

In the above we have defined a variable LOGS_END_DATE. We’ll backup data which is older than 90 days from yesterday. You may be wondering what is [$# -ne 0 ]. It stands for “number of parameters not equal to zero”. If we execute the script file as filename.sh 04–07–2020, LOGS_END_DATE will be 04–07–2020. If we execute without the date paremeter, LOGS_END_DATE will be the date before 90 days from yesterday.

We’ll export our data to a CSV file.

_export_csv_file=$BACKUP_HOME/data/Audit_Logs_Until_$LOGS_END_DATE.csv

Then we should define the database connection and SQL query.

echo ‘’
echo `date`’ :: CHATBOT AUDIT logs back up to csv started…..’
echo ‘#############################################################’
echo ‘End Date is ‘$LOGS_END_DATE
CONN=”/usr/bin/psql -w -U postgres -h localhost -d your_database”
echo ‘’
echo `date`” :: Export Query ….”
QUERY_CSV=”SELECT * FROM cloud_schema.audit_log WHERE request_timestamp < ‘$LOGS_END_DATE’”
echo “$QUERY_CSV”

In my server PostgreSQL is installed in /usr/bin/psql. In linux based systems you can get this installation location with which psql. Replace postgres with your DB username and your_database with your DB name.

QUERY_CSV="SELECT * FROM cloud_schema.audit_log WHERE request_timestamp < '$LOGS_END_DATE'"

I wanted to backup the records from the audit_log table which are older than the LOGS_END_DATE.

Now, that we have queried the data we needed to backup, we have to write this data to a csv file. We’ll use the copy command in the script.

echo ‘’
echo `date`” :: File Export Started..”
echo “File : ${_export_csv_file}”
echo “copy ($QUERY_CSV) to ‘$_export_csv_file’ with (FORMAT CSV, HEADER)” | $CONN
echo ‘’
echo `date`” :: File Export completed.”
echo ‘#############################################################’

This will write your data to a csv file. If you need to remove this data from the database, you can delete them as below.

if [ $? -eq 0 ];then
  echo `date`” :: Deleting Older Data..”
  $CONN -c “DELETE FROM cloud_schema.audit_log WHERE request_timestamp < ‘$LOGS_END_DATE’”
  echo `date`” :: Export Completed”
else
  echo `date`” :: Export Failed”
fi

Here’s what the complete script would look like.

Shell script to backup database

You can run this script as ./filename.sh or with the date parameter as ./filename.sh 04–07–2020.

Let’s say that you have to run this script every Sunday 1.00 AM. No sleep on Sunday nights?? Definetely not. You can schedule a job to run the script with cron.

Go to your Linux terminal and type crontab -e. On a newline, insert the following, save and exit. Remember to give the correct path to your .sh file.

0 1 * * 0 path/to/your_sh_file/filename.sh

This job will wake up and run your script during the time you mentioned, while you can have a good nap. Also remember that your server should be up and running for this to work.