Backup your Postgres database like a pro
Periodically backup your Postgres database from your EC2 Instance to S3 Bucket using python
Nowadays, our applications are hosted in the cloud using services like AWS and DigitalOcean. Instead of going for a
database-as-a-service, where everything is managed and controlled by the service provider, we might choose to do the opposite i.e. using our own cloud server instance due to cost or several other constraints, since everything is done our way, let's not forget to do the most crucial part as well i.e periodic database backups of your client data. So in this post, we will discuss how we could backup our Postgres database hosted in
AWS EC2 and store the database dumps in the
Note: I am assuming you already have a working
AWS account with access permission to
EC2 Instance &
S3 Bucket. I'm also assuming that you have hosted your Postgres database in one of your EC2 Instances.
Creating S3 bucket and policy
To create the bucket and policy, log into the AWS console and go to S3.
Create Bucketsection of the page, add a name for your database backup. You can use something simple like
my-db-backupor something more descriptive like
Permissions, make sure that both of these are checked:
Access logs(turned on by default) and
Website endpoint(also turned on by default).
Createat the bottom of this page to finish creating your bucket!
Setting up the roles and permissions
Create a new user with the permission to only upload to the bucket we just created in the step above, for that follow the instructions given below:
- Inside the
AWS console, search for
IAMand click on
- Next, click on the
create rolebutton on the top right corner of the console and select
Use casefrom the list of dropdowns.
- Again, click on the
create policybutton and then click on the
JSONtab and paste the code given below:
<bucket> with the bucket you created for your database backups, example:
my-db-backup, and then replace
<object> with the folder you would like those dumps to be uploaded on, example:
- Skip to the next part by clicking on the
Nextbuttons until you reach a page named
Review Policyand give your policy a name. If you messed up the process in between you could follow this article otherwise, skip to the next step.
- Save the
SECRET KEYprovided for the user you just created in the step above
This article is based on a repository hosted on github, please clone and check out the script and requirements in the
Since we are using python for backing up our database, we will be needing to install dependencies like
boto (AWS SDK for python) inside our
EC2 Instance. For that follow the steps given below:
- SSH to your EC2 Instance using this command:
ssh -i path/to/AccessKey.pem firstname.lastname@example.org
- Install python dependencies using
pip3 install -r requirements.txt
.envand add the following credentials
Running script manually
After completing all the steps defined above, you finally be able to run the actual script. Go ahead and run the script by going inside the root of this cloned project:
Running script like a pro
The full purpose to write this well-guided article (lol) is to do this thing like a professional like it was supposed to. There are numerous ways to do it, it's all fine if you want to go the extra mile, like using services like
PipeDream, etc but we will be using
cronjobs this time.
Run the following command to set up the cron job:
sudo service cron start (if not running) crontab -e
Now paste this cron to run the script every night at 02:00 AM NEPALI TIME (UTC+5:45)
0 22 * * * python3 /home/ubuntu/backup-my-postgres/run.py
NOTE: I'm using
my native timezone, please change that to your respective timezone if you care about when and at what time it runs a backup.
That's it. No further steps. That is how you run your backup script.
The main motivation behind writing this article is to help another internet fellow who might get overwhelmed by libraries like
awscli. Those tools could deliver greater results but needs various bells & whistles to configure. I also tried to pull this guide using
bash but it didn't feel like home, hence the python.
I hope you guys found this article useful. Please don't share or recommend this article to your parents or relatives who might go
wtf, why me? but to those who might actually need it & will remember you for this article their whole life. Drop a star in my repo (it's free) if you feel like it ;).