Small basic script to run after upgrade commands on Postgresql

postgresql_upgrade.sh

#!/bin/bash
set -e

# Use this script with
# Add space before the command to not save it into you shell history
# ./update_postgresql.sh -h pg-xxx.region.rds.amazonaws.com -p password

# Define default host and password
host="localhost"
user="admin"
password=""
defaultdb="company_name"

# Parse command line options
while getopts ":h:p:" opt; do
    case ${opt} in
        h )
            host=$OPTARG
            ;;
        p )
            password=$OPTARG
            ;;
        \? )
            echo "Usage: $0 [-h host] [-p password]" 1>&2
            exit 1
            ;;
    esac
done
shift $((OPTIND -1))

# Get a list of databases and ignore rdsadmin's tables
database_info=$(PGPASSWORD=$password psql -U $user -h $host $defaultdb -t -X -c "SELECT d.datname as \"Name\", pg_catalog.pg_get_userbyid(d.datdba) as \"Owner\" FROM pg_catalog.pg_database d WHERE pg_catalog.pg_get_userbyid(d.datdba) != 'rdsadmin' ORDER BY 1;")

# Loop through each database
while IFS='|' read -r db owner; do
    db=$(echo $db | tr -d '[:space:]')
    owner=$(echo $owner | tr -d '[:space:]')
    echo "Connecting to database: $db (owned by $owner)"

    PGPASSWORD=$password psql -X -U $user -h $host -d $db -c "ANALYZE;"

    # Enable if needed, vacuuming can be slow
    # PGPASSWORD=$password psql -U $user -h $host -d $db -c "VACUUM VERBOSE;"

    # Get a list of extensions
    extensions=$(PGPASSWORD=$password psql -X -U $user -h $host -d $db -t -c "SELECT extname FROM pg_extension;")

    # Loop through each extension and upgrade it
    for extension in $extensions; do
        echo "Trying to upgrade extension: $extension"
        PGPASSWORD=$password psql -X -U $user -h $host -d $db -c "ALTER EXTENSION \"$extension\" UPDATE;"
    done
done <<< "$database_info"