This project is a mockup for using a Python CLI for backing up remote PostgreSQL databases locally or to a remote repository (e.g. AWS S3, Azure Blob Storage).

It’s quite basic in implementation, and could be extended to a number of different use cases. In my case, it was used for keeping track of reports on a hosted print server.

pgdump.py

import subprocess
import sys

# Dumps database information from specified Postgres server

def dump(url):
    try:
        return subprocess.Popen(['pg_dump', url], stdout=subprocess.PIPE)

    # If pg_dump not installed, raise error
    except OSError as err:
        print(f"Error: {err}")
        sys.exit(1)

def dump_file_name(url, timestamp=None):
    db_name = url.split('/')[-1]
    db_name = db_name.split('?')[0]

    if timestamp:
        return f"{db_name}-{timestamp}.sql"
    else:
        return f"{db_name}.sql"

cli.py

from argparse import ArgumentParser, Action

class DriverAction(Action):
    def __call__(self, parser, namespace, values, option_string=None):
        driver, destination = values
        namespace.driver = driver.lower()
        namespace.destination = destination

def create_parser():
    parser = ArgumentParser(description="""
    Backup PostgreSQL databases locally or remotely
    """)

    parser.add_argument("url", help="URL of the database that will be backed up")
    parser.add_argument("--driver", '-d', help="How and where to store backup", nargs=2, action=DriverAction, required=True)
    
    return parser

def main():
    import boto3
    import time
    from pgbackup import pgdump, storage

    args = create_parser().parse_args()
    dump = pgdump.dump(args.url)


    if args.driver == 'remote':
        client = boto3.client('remote')

        timestamp = time.strftime("%Y-%m-%dT%H:%M", time.localtime())
        file_name = pgdump.dump_file_name(args.url, timestamp)

        # Backup database to S3 destination with specified filename
        storage.s3(client, dump.stdout, args.destination, file_name)

    else:
        outfile = open(args.destination, 'wb')

        # Backup database locally with specified filename
        storage.local(dump.stdout, outfile)

Finally, it can be configured as a package ready for distribution.

#Used to create installable packages

from setuptools import find_packages, setup

with open('README.md', 'r') as f:
    long_description = f.read()

setup(
    name='pgbackup',
    version='0.1.0',
    author='John Random',
    author_email='jrandom@email.com',
    description='A utility for backing up PostgreSQL databases',
    long_description=long_description,
    long_description_content_type='text/markdown',
    url='https://github.com/usr/project',
    packages=find_packages('src'),
    package_dir={'':'src'},
    install_requires=['boto3'],
    python_requires='>3.6',
    entry_points={
        'console_scripts': [
            'pgbackup=pgbackup.cli:main'
        ],

    }
)