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.
PostgreSQL backups are vital to protect against data loss from various risks like data corruption, device failure, human error, and software issues.
We'll develop a straightforward script to back up your PostgreSQL database and store it in your Amazon S3 bucket. Additionally, we will automate this process using Cron.
Key Points to Understand Before We Begin:
Why Choose Amazon S3?
In this tutorial, we've selected Amazon S3 for its widespread use and reliability.
If you prefer a different cloud storage provider, feel free to make that choice. The steps will remain largely similar as long as the chosen provider is compatible with S3.
What is Cron?
Cron is a time-based job scheduling software utility, supporting Unix-based operating systems. Developers utilize Cron to schedule tasks, like executing commands or shell scripts, at specific intervals. These intervals can be set to daily, weekly, or any other frequency according to your needs.
Chmod, short for ‘change mode’, is a command used to set file handling rules. Through the “chmod” system call, an administrator can alter access permissions of file system objects.
🧑💻 Let's get down to coding now. You can automate the creation of postgres database backup and storing it to Amazon S3 following these steps
Navigate to Your Home Directory and create a directory for your script:
Create the PostgreSQL database backup script:
PGPASSWORD='postgres_password' pg_dump --inserts --column-inserts --username=postgres_user --host=postgres_host --port=postgres_port postgres_database_name > dbbackup.sql
Be sure to replace the following variables with your own values:
Now chmod the backup script to allow it to for execution
chmod +x ~/scripts/db_backup.sh
Now, you possess a fully operational shell script tailored for automating PostgreSQL database backups.
Next step is to automate it and to store the postgres backup on Amazon S3.
We'll use the AWS CLI to sync the backup files with Amazon S3.
Before installing the AWS CLI you need to install
python-pi. Type the following commands:
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
Set up AWS key & Secret
Configuration and credential file settings
secret_access_key as shown below
Now, let's build the script that will allow you to sync your PostgreSQL database backups with Amazon S3.
This script will be used in conjunction with the script you created in the previous section.
Copy and paste the script into
# Set your AWS CLI path (update with your actual path if needed)
# Set your Amazon S3 bucket name
# Specify the source directory where your backups are stored
# Synchronize the backups with Amazon S3
$AWS_CLI_PATH s3 sync $SOURCE_DIR s3://$S3_BUCKET
Save the file and exit the text editor.
Make the script executable:
chmod +x ~/scripts/db_sync.sh
Now, you have a shell script that can be executed to sync your Postgres database backups with your specified Amazon S3 bucket.
Don't forget to replace
"my-bucket-name" with your actual S3 bucket name and update the
AWS_CLI_PATH if necessary to point to your AWS CLI installation location.
The last step is to automate the process of PostgreSQL database backup with CRON. To do this, follow these steps:
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:
Want to trust your PostgreSQL backups are running well without hastle?
Try SimpleBackups Now →
Free 7-day trial. No credit card required.
Have a question? Need help getting started?
Get in touch via chat or at [email protected]