Backup PostgreSQL DB and copy file to Dropbox

PostgreSQL backup

Naming file

README.md

Description

Backup PostgreSQL DB and copy file to Dropbox.

Setup

1. Make file .pgpass with rights 0600 in home directory for the user who will run the script. Format:

    hostname:port:database:username:password
   

2. Add backup.sh to cron for periodical backup

3. Make directory for backups and logs.

4. Install dropbox module for Python3

    pip3 install dropbox
   

backup.sh

#!/bin/bash

DATE=$(date '+%Y%m%d')
DIR="/var/backups/postgresql/my_db/"
FILE="$DATE.sql"
LOG="/var/backups/postgresql/my_db/backup.log"

TIME=$(date '+%H:%M:%S %d.%m.%Y')
echo -e "\nStart job: $TIME" >> $LOG
echo "---" >> $LOG

if [ ! -d "$DIR" ]; then
    echo "$(date '+%H:%M:%S %d.%m.%Y') Error: backup directory not found: $DIR" >> $LOG
    exit 1
fi

# PostgreSQL backup of db_notes base
pg_dump my_db -f "$DIR$FILE"
if [ $? -eq 0 ]; then
    echo "$(date '+%H:%M:%S %d.%m.%Y') Dump completed" >> $LOG
else
    echo "$(date '+%H:%M:%S %d.%m.%Y') Error: dump failed" >> $LOG
    exit 1
fi

# Compress
gzip -f "$DIR$FILE"
if [ $? -eq 0 ]; then
    echo "$(date '+%H:%M:%S %d.%m.%Y') Compress completed" >> $LOG
else
    echo "$(date '+%H:%M:%S %d.%m.%Y') Error: compress failed" >> $LOG
    exit 1
fi

# Send backup to Dropbox
echo -n "$(date '+%H:%M:%S %d.%m.%Y') " >> $LOG
/var/lib/postgresql/dropbox_upload.py $DIR$FILE.gz >> $LOG

# Remove files older than 5 days
find /var/backups/postgresql/my_db/ -mtime +5 -exec rm -f {} \;

# Exit with success code
exit 0

dropbox_upload.py

#!/usr/bin/python3

import dropbox
import sys, os

files = sys.argv[1:]
token = 'my-token'
client = dropbox.client.DropboxClient(token)

for file in files:
    f = open(file, 'rb')
    try:
        remote_file = os.path.basename(file)
        response = client.put_file(remote_file, f)
    except dropbox.rest.ErrorResponse as e:
        print(str(e))
    else:
        print('Uploaded succesfully')