Published on June 9th, 2020
Any database, needs a backup.
This article is part of “The Ultimate Backup Script” series we are creating to provide you with database backup scripts that not only allow you to create database backups, but also upload the backup dumps to Amazon S3 and automate the process daily.
One might think why backup is necessary for my database? The answer is simple, backup creates a copy of your physical, logical, and operational data. Which you can store at any safe place such as Amazon S3. This copy comes into use if the running database gets corrupted. Database backup can include files like control files, datafiles, and archived redo logs.
Remove Postgres backups from your to-do list.
Try The SimpleBackups Experience →
For this tutorial, we have chosen Amazon S3 as it is a very common choice. You can do the same thing if you would like to use another cloud storage provider. The instructions won’t differ a lot as long as the cloud provider is S3-compatible.
Below we defined some less known terms that we used in the article:
Cron is a software utility that offers time-based job scheduling. It supports Unix computer operating systems. To set up software environments, the developer uses Cron. He/she schedules commands or shell scripts so that they run at chosen times. It could be daily, once a week, or any interval as desired.
The chmod a short command of ‘change mode’ enables the admin to set rules for file handling. In other words, with the help of a “chmod” system call. An administrator can change the access permissions of file system objects.
You can automate the creation of backup and storing it to Amazon S3 within a few minutes. Below bullets brief about what you are going to learn in this part of the article:
cd ~ mkdir scripts cd scripts nano db_backup.sh
Copy and paste the script below to it
#!/bin/bash DIR=`date +%d-%m-%y` DEST=~/db_backups/$DIR mkdir $DEST PGPASSWORD='postgres_password' pg_dump --inserts --column-inserts --username=postgres_user --host=postgres_host --port=postgres_port postgres_database_name > dbbackup.sql
Now chmod the script to allow it to for execution
chmod +x ~/scripts/db_backup.sh
Copy and paste the script below to it
#!/bin/bash /usr/local/bin/aws s3 sync ~/db_backups s3://my-bucket-name
Now chmod the script to allow it for execution
chmod +x ~/scripts/db_sync.sh
cd ~ mkdir db_backups
Before installing the AWS CLI you need to install
python-pi. Type the following commands:
apt-get update apt-get -y install python-pip curl "https://bootstrap.pypa.io/get-pip.py" -o "get-pip.py"
Install the AWS CLI
Type the following command:
pip install awscli
Configuration and credential file settings
cd ~ mkdir .aws nano ~/.aws/config
secret_access_key as shown below
[default] aws_access_key_id=AKIAIOSFODNN7EXAMPLE aws_secret_access_key=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
Paste the below commands at the bottom to automate the process
0 0 * * * ~/scripts/db_backup.sh # take a backup every midnight 0 2 * * * ~/scripts/db_sync.sh # upload the backup at 2am
This way the backup script will run and also sync with Amazon S3 daily.
Hence, by using these scripts you can achieve 3 goals:
Have you tried SimpleBackups.io yet?
SimpleBackups will save you a lot of time setting up scripts and ensuring they run without problems. It will alert you when things go wrong, and allows you store your backups on many cloud storage services like Google, DigitalOcean, Wasabi, Dropbox, and more…
Docker is an open-source platform that uses containers. Developers use it to create, deploy, and run different applications. The tool works…
There are different types of database aside from the commonly used MySQL database. You might work on a project that uses another type of…
Backing up and restoring a PostgreSQL database is an essential task for any system administrator. Fortunately, there are built-in pg_dump…