Backup and restore postgres to blob

Wow, 787 views so far! Progress thanks to Perplexity ai (similar to chatGPT), a workable solution looks to be a python script in a runbook that's scheduled via Azure Automation.

Using VS Code, I've got the python script looping through the schemas and backing them up to Blob. The only bit I'm unsure of is how to provide the db password - it'll be an Azure secret - I'll get our devops guys to lend me a hand on that.

It wasn't perfect and had a couple of bugs in it, but it was a huge help. In case it helps, here's the basic python I started with:

import os
import subprocess
import datetime
import psycopg2
from azure.storage.blob import BlobServiceClient, BlobClient, ContainerClient

# Azure Storage account credentials
connection_string = "<your_connection_string>"
container_name = "<your_container_name>"

# Azure PostgreSQL credentials
pg_host = "<your_postgresql_host>"
pg_port = "<your_postgresql_port>"
pg_user = "<your_postgresql_user>"
pg_password = "<your_postgresql_password>"
pg_database = "<your_postgresql_database>"

# Get list of schemas
conn = psycopg2.connect(host=pg_host, port=pg_port, user=pg_user, password=pg_password, database=pg_database)
cur = conn.cursor()
cur.execute("SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema'")
schemas = [row for row in cur.fetchall()]
cur.close()
conn.close()

# Loop through schemas and create backup files
for schema in schemas:
    stringschema = ''.join(schema)
    # Backup file name
    backup_file_name = stringschema + "_" + datetime.datetime.now().strftime("%Y%m%d_%H%M%S") + ".backup"

    # Create backup file using pg_dump
    pg_dump_command = f"pg_dump -h {pg_host} -p {pg_port} -U {pg_user} -d {pg_database} -n {schema} -F c -b -v -f {backup_file_name}"
    subprocess.run(pg_dump_command, shell=True, check=True)

    # Upload backup file to Azure Storage account
    blob_service_client = BlobServiceClient.from_connection_string(connection_string)
    container_client = blob_service_client.get_container_client(container_name)
    blob_client = container_client.get_blob_client(backup_file_name)
    with open(backup_file_name, "rb") as data:
        blob_client.upload_blob(data, overwrite=True)

    # Delete local backup file
    os.remove(backup_file_name)