Backup postgresql database, both database and roles / grants.

backup-postgresq-db.py

""" Backup database.
"""

import os
import subprocess
import datetime

# DB Options.
HOST = 'localhost'
PORT = '5432'
USERNAME = 'postgres'
PASS = 'pass'
DATABASE_NAME = 'dbname'

# FOLDERS.
ROOT_DIR = os.path.abspath(os.path.join(os.sep, 'db-folder'))
BACKUP_DIR = os.path.join(os.sep, ROOT_DIR, 'backup-folder')

# DB FILENAMES.
GLOBALS_FILENAME = 'db-globals'
DB_FILENAME = 'db-name'

# LOGS.
ERROR_LOG_FILE = os.path.join(os.sep, ROOT_DIR, 'error-logs', 'db-error-log.txt')

# MISC.
CURRENT_DATE = str(datetime.datetime.now().date())


def make_dir(path):
    """ Attempts to create directory.

        @param {string} The absolute path of the new directory to be created.
    """


    try:
        os.makedirs(path)
    except (IOError, OSError) as exception:
        with open(ERROR_LOG_FILE, 'a') as f:
            f.write('%s \n %s \n\n' % (CURRENT_DATE, exception))


def backup_db():
    """Backups a database and its globals (ROLES/GRANTS).

        @desc Creates a new directory in the backup directory BACKUP_DIR, with the date as the directory name.
          Two files are created:
            1) db-globals, which is the file containing the ROLES / GRANTS.
            2) db-name, which is the database file.

        Postgres psql commands used:
            -U username
            -h host
            -p port
            -f filename
            -d database name
            -Fc custom format
            -g globals only (ROLES / GRANTS)
    """


    curr_backup_dir = os.path.join(os.sep, BACKUP_DIR, CURRENT_DATE)

    if not os.path.isdir(curr_backup_dir):
        make_dir(curr_backup_dir)

    # Avoid being prompted for password.
    os.putenv('PGPASSWORD', PASS)

    # BACKUPS GLOBALS ONLY (ROLES / GRANTS).
    file_path_globals = os.path.join(os.sep, curr_backup_dir, GLOBALS_FILENAME)
    if not os.path.isfile(file_path_globals):
        cmd = 'pg_dumpall -h %s -p %s -U %s -f "%s" -g' % (
            HOST,
            PORT,
            USERNAME,
            file_path_globals
        )

        p = subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True)
        stdout_data, stderr_data = p.communicate()
        if p.returncode != 0:
            with open(ERROR_LOG_FILE, 'a') as f:
                f.write('%s \n %s \n\n' % (CURRENT_DATE, stderr_data))


    # BACKUPS SPECIFIC DB.
    file_path_db = os.path.join(os.sep, curr_backup_dir, DB_FILENAME)
    if not os.path.isfile(file_path_db):
        cmd = 'pg_dump -h %s -p %s -U %s -d %s -f "%s" -Fc' % (
            HOST,
            PORT,
            USERNAME,
            DATABASE_NAME,
            file_path_db
        )

        p = subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True)
        stdout_data, stderr_data = p.communicate()
        if p.returncode != 0:
            with open(ERROR_LOG_FILE, 'a') as f:
                f.write('%s \n %s \n\n' % (CURRENT_DATE, stderr_data))


if __name__ == '__main__':
    backup_db()