Tạo CLI tool sử dụng Python: pgbackup

# Lưu file backup vào /path/to/file ở local storage
pgbackup postgres://vntechies@example.com:5432/test --driver local /var/local/db_one/backups

# Upload file backup tới bucket bucket_name trên S3
pgbackup postgres://vntechies@example.com:5432/test --driver s3 backups

docker run -d \
	--name vntechies_pg \
	-e POSTGRES_PASSWORD=password_kho_doan \
    -e POSTGRES_USER=vntechies \
    -e POSTGRES_DB=test \
    -p 5432:5432 \
	postgres:9.6

psql postgres://vntechies:password_kho_doan@localhost:5432/test -c "SELECT count(id) FROM employees;"

 count
-------
  1000
(1 row)

mkdir -p 010-python-cli-pgbackup/src
cd 010-python-cli-pgbackup
touch README.md setup.py src/.gitkeep
vi setup.py

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='VNTechies',
    author_email='info@vntechies.dev',
    description='A utility for backing up PostGreSQL databases',
    long_description=long_description,
    long_description_content_type = 'text/markdown',
    url = 'https://github.com/vntechies/010-python-cli-pgbackup'
    packages=find_packages('src')
)

pipenv --python python3.10
pipenv shell
vi README.md

pgbackup
========

CLI for backing up remote PostgreSQL databases locally or to AWS S3

Preparing for development
-------------------------

1. Ensure ``pip`` and ``pipenv`` are installed
2. Clone repository: ``git clone git@github.com/ahsec/pgbackup``
3. ``cd`` into repository
4. Fetch development dependencies ``make install``
5. Activate virtualenv: ``pipenv shell``

Usage
-----

Pass in a full database URL, the storage driver, and destination.

S3 Example with bucket name:

::

    $ pgbackup postgres://bob@example.com/5432/db_one --driver s3 backups

Local example with local path

::

    $ pgbackup postgres://bob@example.com/5432/db_one --driver local /var/local/db_one/backups

Running tests
-------------

Run tests locally using ``make`` if virtualenv is active:

::

    $make

If virtualenv isn't active then use:

::

    $pipenv run make

curl -o .gitignore https://raw.githubusercontent.com/github/gitignore/master/Python.gitignore

git init
git add --all
git commit -m 'Initial commit'

mkdir src/pgbackup
touch src/pgbackup/__init__.py
vi src/pgbackup/cli.py

from argparse import Action, ArgumentParser
import time


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="""
        CLI tool back up Postgre database về local hoặc S3 sử dụng Python
        """
    )

    parser.add_argument("url", help="Connection string của Postgres DB")
    parser.add_argument(
        "--driver",
        "-d",
        help="Chọn phương thức và vị trí lưu file backup",
        nargs=2,
        metavar=("DRIVER", "DESTINATION"),
        action=DriverAction,
        required=True,
    )
    return parser

python -i src/pgbackup/cli.py
>>> parser = create_parser()
>>> args = parser.parse_args(['url','--driver','s3','backup'])
>>> args.url
'url'
>>> args.driver
's3'
>>> args.destination
'backup'
>>> type(args)
<class 'argparse.Namespace'>

import subprocess
import sys


def dump(url):
    try:
        return subprocess.Popen(["pg_dump", url], stdout=subprocess.PIPE)
    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"

PYTHONPATH=./src python
from pgbackup import pgdump
dump = pgdump.dump('postgres://vntechies:password_kho_doan@localhost:5432/test')
f = open('dump.sql', 'w+b')
f.write(dump.stdout.read())
f.close()

def local(infile, outfile):
    outfile.write(infile.read())
    outfile.close()
    infile.close()

pipenv install boto3

...
def s3(client, infile, bucket, name):
    client.upload_fileobj(infile, bucket, name)

PYTHONPATH=./src python
>>> import boto3
>>> from pgbackup import storage
>>> client = boto3.client('s3')
>>> infile = open('dump.sql', 'rb')
>>> storage.s3(client, infile, 'vntechies-pg-backup', infile.name)

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="VNTechies",
    author_email="info@vntechies.dev",
    description="A utility for backing up PostGreSQL databases",
    long_description=long_description,
    long_description_content_type="text/markdown",
    url="https://github.com/vntechies/010-python-cli-pgbackup",
    packages=find_packages("src"),
    package_dir={"": "src"},
    install_requires=["boto3"],
    entry_points={
        "console_scripts": ["pgbackup=pgbackup.cli:main"],
    },
)

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

    args = create_parser().parse_args()
    dump = pgdump.dump(args.url)
    timestamp = time.strftime("%Y-%m-%dT%H-%M", time.localtime())
    file_name = pgdump.dump_file_name(args.url, timestamp)
    if args.driver == "s3":
        client = boto3.client("s3")
        print(f"Upload file backup lên S3 với tên file {file_name}")
        storage.s3(client, dump.stdout, args.destination, file_name)
    else:
        outfile = open(args.destination, "wb")
        print(f"Lưu file backup tại local với tên file {outfile.name}")
        storage.local(dump.stdout, outfile)

pipenv shell
pip install -e .
pgbackup --driver local ./local-dump.sql postgres://demo:password@54.245.63.9:80/sample
pgbackup --driver s3 pyscripting-db-backups postgres://demo:password@54.245.63.9:80/sample

...
    python_requires='>=3.6',
...

python setup.py bdist_wheel

pip uninstall pgbackup
pip install dist/pgbackup-0.1.0-py3-none-any.whl

exit
pip install --user https://github.com/vntechies/010-python-cli-pgbackup/releases/download/latest/pgbackup-0.1.0-py3-none-any.whl

vi ~/.zshrc

# Added python bin files
export PATH="$HOME/Library/Python/3.10/bin/:$PATH"

source ~/.zshrc
pgbackup --help
pgbackup postgres://vntechies:password_kho_doan@localhost:5432/test --driver local ./local-dump.sql
pgbackup postgres://vntechies:password_kho_doan@localhost:5432/test --driver s3 vntechies-pg-backup

pgbackup --help
usage: pgbackup [-h] --driver DRIVER DESTINATION url

CLI tool back up Postgre database về local hoặc S3 sử dụng Python

positional arguments:
  url                   Connection string của Postgres DB

options:
  -h, --help            show this help message and exit
  --driver DRIVER DESTINATION, -d DRIVER DESTINATION
                        Chọn phương thức và vị trí lưu file backup