Automatically Backup a PostgreSQL Database

Backup a PostgreSQL Database

Are you a developer that has a great application but is living on the edge by not having a "plan B"? Or are you a developer that use a PostgreSQL docker container that was previously mean't for testing but now you use in production? Well look no further! You can backup your PostgreSQL database with the following command:

$ pg_dump --no-owner -U <db-username> <db-name> > file-name-to-backup-to.backup

Backup a PostgreSQL Database Docker Container 

Good news for developers that love to deploy their projects with Docker, you can backup the PostgreSQL database that is within a Docker container! You do so with the following command:

$ docker exec -t <postgres-container-name> pg_dump --no-owner -U <db-username> <db-name> > file-name-to-backup-to.backup

Automatically Backup a PostgreSQL Database to AWS S3

How about automating your database backups? There are a few ways to do this, if your PostgreSQL database is on a Windows server, follow this nice guide on the postgres wiki. For Linux users, you can create a cron job to automate the backup process.

But what if you want to send your backup file to a remote object storage such as AWS S3 or DigitalOcean Spaces? It wouldn't make much sense to create a backup file on a server that may be attacked or possible a problematic server, so lets create a python script that'll do the following:

1. Take a backup of a PostgreSQL database within a Docker container
2. Upload the backup file to S3/Spaces
3. Remove older backup files from S3/Spaces
4. Remove older backup files from local storage

Create a new python file named backup.py. Copy and paste the following:

import logging
import os
import boto3
from botocore.exceptions import ClientError
from datetime import datetime, timedelta

def upload_file(file_name, bucket, object_name=None):
    """Upload a file to an S3 bucket

    :param file_name: File to upload
    :param bucket: Bucket to upload to
    :param object_name: S3 object name. If not specified then file_name is used
    :return: True if file was uploaded, else False
    """

    # If S3 object_name was not specified, use file_name
    if object_name is None:
        object_name = os.path.basename(file_name)

    # Upload the file
    session = boto3.session.Session()
    s3_client = session.client('s3', 
                        region_name='nyc3',
                        endpoint_url='https://nyc3.digitaloceanspaces.com',
                        aws_access_key_id=os.getenv('SPACES_KEY'),
                        aws_secret_access_key=os.getenv('SPACES_SECRET'))
    try:
        with open(file_name, 'rb') as file_contents:
            response = s3_client.put_object(
                Bucket=bucket,
                ACL='private',
                Key='backups/' + file_name,
                Body=file_contents,
            )
    except ClientError as e:
        logging.error(e)
        return False
    return True

def delete_file(Key, Bucket):
    session = boto3.session.Session()
    s3_client = session.client('s3', 
                        region_name='nyc3',
                        endpoint_url='https://nyc3.digitaloceanspaces.com',
                        aws_access_key_id=os.getenv('SPACES_KEY'),
                        aws_secret_access_key=os.getenv('SPACES_SECRET'))
    try:
        response = s3_client.delete_object(Bucket, Key)
    except ClientError as e:
        logging.error(e)
        return False
    return True

#Takes the backup of the Dockerized PostgreSQL database
someDate = datetime.now().strftime("%m_%d_%Y")
backup_cmd = 'docker exec -t <postgres-container-name> pg_dump --no-owner -U <db-username> -F c -b -v -f <db-name> > <db-backup-filename>_' + someDate + '.backup'
os.system(backup_cmd)

#Uploads the backup file to S3 Bucket/DigitalOcean Spaces
try:
    s3_bucket = 'my-s3-bucket'#Name of bucket/Spaces
    upload_file(file_name="db-backup-filename_" + someDate + ".backup", bucket=s3_bucket)
except:
    print("Couldn't upload backup file to Spaces")

#Deletes older backups from S3 Bucket/DigitalOcean Spaces
try:
    three_days_ago = (datetime.now() - timedelta(days=3)).strftime("%m_%d_%Y")
    three_day_old_backup = "db-backup-filename_" + three_days_ago + ".backup"
    delete_file(Key= three_day_old_backup, Bucket=s3_bucket)
except:
    print("Couldn't delete three day old backup from Spaces storage")
    
#Deletes older backups from local drive
try:
    remove_from_local = 'rm ' + three_day_old_backup
    os.system(remove_from_local)
except:
    print("Couldn't delete three day old backup from local drive")

To setup the script to run automatically on Linux, you can:

1. Run the following command to install the S3 SDK module for Python

$ pip install boto3

2. Setup global environment variables (so all users can execute the script), enter the following in your terminal:

$ sudo nano/etc/environment

$ NAME=MY_VALUE

Exit edit mode by entering: CTRL X

Save buffer by entering: Y

Save file by entering: ENTER

Confirm your changes by entering the following in your terminal:

$ source /etc/environment

Lastly, test your new by entering the following in your terminal:

$ echo $NAME

3. Setup a cron job to execute the script (the example will execute the script at 2:30am local time each day). In your terminal, enter the following commands:

$ crontab -e

$ 30 2 * * * usr/bin/python /home/myuser/backup.py