Daily basic backup for PostgreSQL dump and store in AWS S3

• Install AWS CLI in Ubuntu using the following cmd.

sudo apt update
sudo apt install python3-pip
curl "https://bootstrap.pypa.io/get-pip.py" -o "get-pip.py"
sudo pip install awscli

• Configure the AWS CLI using aws configure cmd.

$ aws configure
AWS Access Key ID [None]: <your-access-id>
AWS Secret Access Key [None]: <your-secret-key>
Default region name [None]: us-west-2 
Default output format [None]: json

Backup script for PostgreSQL dump

#!/bin/bash
DIR=$(date +%d-%m-%y-%H:%M)
DEST=~/db_backups/$DIR
S3_BUCKET_NAME="your-s3-bucket-name"
S3_PREFIX="db_backups"
# Create local backup directory
mkdir $DEST
cd $DEST
# PostgreSQL dump
PGPASSWORD='password' pg_dump --inserts --column-inserts --username=postgres_user --host=localhost --port=5432 database_name > dbbackup.sql
# AWS S3 upload
aws s3 cp dbbackup.sql s3://$S3_BUCKET_NAME/$S3_PREFIX/$DIR/
# Clean up local backup directory if needed
rm -rf $DEST

1. DIR=date +%d-%m-%y-%H:%M``: This line sets the DIR variable to the current date and time formatted as day-month-year-hour:minute. The date command with the specified format is used to generate this timestamp.
2. DEST=~/db_backups/$DIR: This line sets the DEST variable to the path where the backup will be stored. It uses the tilde (~) to represent the user's home directory and appends the formatted timestamp ($DIR) to the path ~/db_backups/.
3. mkdir $DEST: This line creates a directory with the path stored in the DEST variable. This directory will be used to store the backup files.
4. cd $DEST: This line changes the current working directory to the one created in the previous step ($DEST). The subsequent commands will be executed in this directory.
5. PGPASSWORD='password': This line sets the environment variable PGPASSWORD to the password required for authentication when connecting to the PostgreSQL database. Replace 'password' with the actual password.
6. pg_dump --inserts --column-inserts --username=postgres_user --host=localhost --port=5432 database_name > dbbackup.sql: This line uses the pg_dump command to perform a database dump. Here's what each option means:

• --inserts: Generates INSERT SQL statements for data.
• --column-inserts: Causes each INSERT statement to include column names.
• --username=postgres_user: Specifies the PostgreSQL username for authentication. Replace 'postgres_user' with the actual username.
• --host=localhost: Specifies the hostname or IP address of the PostgreSQL server. In this case, it is set to 'localhost'.
• --port=5432: Specifies the port number where the PostgreSQL server is running. The default PostgreSQL port is 5432.
• database_name: Specifies the name of the PostgreSQL database to be backed up. Replace 'database_name' with the actual name of your database.
• > dbbackup.sql: Redirects the output of the pg_dump command to a file named dbbackup.sql in the current working directory.
• your-s3-bucket-name: Replace this with the name of your AWS S3 bucket.

In this modified script, after creating the PostgreSQL database dump (dbbackup.sql), the AWS CLI command (aws s3 cp) is used to copy the file to the specified S3 bucket and prefix. Adjust the S3 bucket name and prefix according to your AWS setup.

Thanks for being part of our journey to create a daily backup for PostgreSQL and store it in AWS S3. We hope this guide has helped you make your database management stronger. If you have any questions or thoughts, share them in the comments. Wishing you a smooth and happy backup process!