Postgres Data Backups to S3 using Cronjob

TABLE OF CONTENTS

• Creating an EC2 Instance

• Create Database

• Creating IAM Role

• Creating Cronjob

I am excited to share my latest blog on automating database backups using cronjob and AWS S3. In this blog, I delve into the process of setting up a scheduled backup for PostgreSQL databases, or any other database, to AWS S3, eliminating the need for paid managed backup services offered by AWS RDS.

By leveraging the power of cronjob, we can create a scheduled task that initiates the backup process at specified intervals. The backups are then securely stored in AWS S3, ensuring durability and accessibility whenever needed. This approach allows us to minimize costs as we only pay for the storage utilized in S3.

In the blog, I provide a step-by-step guide on how to configure the cronjob and set up the necessary AWS S3 permissions. Additionally, I discuss the importance of choosing appropriate backup frequencies and retention policies based on individual requirements.

Creating an EC2 Instance

Log in to an AWS account using a user with admin privileges and ensure your region is set to us-east-1 N. Virginia.

Move to the EC2 console. Click Launch instance.

For name use DBbackup-CronJob

Select AMIs as Ubuntu and select Instance Type as t2.micro. Select my keypair and select the default security group

open EC and update ubuntu first

sudo apt update

clear

Now firstly, I am going to setup the database and going to create DB, Username and Password for Postgresql DB

Create Database

sudo apt install postgresql -y

sudo su - postgres
psql

CREATE DATABASE mydb;
CREATE USER myuser WITH PASSWORD '123';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Our database got created now it's time to configure the AWS IAM role to our EC2 Instance with S3FullAccess.

Creating IAM Role

Clicked on IAM role and click on create role and select below option.

Select AmazonS3FullAccess and click next

give the role name and click on create role.

role created,

After creating this role, I am going to attach this role with my EC2 Machine

Now, Installing AWSCLI on my EC2

sudo su
apt install awscli -y

Now i am creating Bucket for DataBase Backup store in s3

aws s3 ls

Created a file for storing backup

touch dbbackup.backup

Copying backup to S3

aws s3 cp database.backup s3://backup-s3-fa

After using the command our database file got copied inside the S3 now going to make a cronjob for the automation

Creating Cronjob

crontab -e

Go with by default nano

We can simply create time by using this website

Create your cronjob time

For testing, I have used 1 min time for this automation task

* * * * * pg_dump -U myuser -d mydb > /home/ubuntu/mydatabase.backup

* * * * * aws s3 cp /home/ubuntu/mydatabase.backup s3://backup-s3-fa

Now see the time July 8, 2023, 11:18:03 (UTC+05:30)

After 1 minute July 8, 2023, 11:19:02 (UTC+05:30)

Thank you for reading my blog, and I hope you find it informative and inspiring. If you have any feedback or questions, please feel free to reach out.

Thank you for reading my blog.