vacuum analyze only the tables in the specified schema

vacuum_analyze_schema-reindex.sh

#!/bin/bash
#https://stackoverflow.com/questions/29710618/vacuum-analyze-all-tables-in-a-schema-postgres
# this is to be run from postgis user
# 20211216

PG_SCHEMA='yourschema'
PG_USER='postgres'
PG_HOST='localhost'
PG_DB='yourdb'
PG_PASS=''

vacuum_analyze_schema() {
    # vacuum analyze only the tables in the specified schema

    # postgres info can be supplied by either passing it as parameters to this
    # function, setting environment variables or a combination of the two
    local pg_schema="${1:-${PG_SCHEMA}}"
    local pg_db="${2:-${PG_DB}}"
    local pg_user="${3:-${PG_USER}}"
    local pg_host="${4:-${PG_HOST}}"
    local pg_pass="${5:-${PG_PASS}}"
    export PGPASSWORD=""

    echo "Vacuuming schema \`${pg_schema}\`:"

    # extract schema table names from psql output and put them in a bash array
    local psql_tbls="\dt ${pg_schema}.*"
    local sed_str="s/${pg_schema}\s+\|\s+(\w+)\s+\|.*/\1/p"
    local table_names=$( echo "${psql_tbls}" | psql -d "${pg_db}" | sed -nr "${sed_str}" )
    local tables_array=( $( echo "${table_names}" | tr '\n' ' ' ) )

    # loop through the table names creating and executing a vacuum
    # command for each one
    for t in "${tables_array[@]}"; do
        echo "doing table \`${t}\`..."
        psql -d "${pg_db}" \
            -c "VACUUM (VERBOSE,ANALYZE) ${pg_schema}.${t};"
    done

    # loop through the table names creating and executing a REINDEX TABLES
    # command for each one
    for t in "${tables_array[@]}"; do
        echo "doing table \`${t}\`..."
        psql -d "${pg_db}" \
            -c "REINDEX TABLE ${pg_schema}.${t};"
    done

    # loop through the table names creating and executing a ANALYZE
    # command for each one
    for t in "${tables_array[@]}"; do
        echo "doing table \`${t}\`..."
        psql -d "${pg_db}" \
            -c "ANALYZE ${pg_schema}.${t};"
    done
}

vacuum_analyze_schema

exit

Codes improve by ChatGPT and Google Bard

#!/bin/bash

# Function to vacuum and analyze tables in the specified schema with logging
vacuum_analyze_schema() {
    local pg_schema="$1"
    local pg_db="$2"
    local pg_user="$3"
    local pg_host="$4"
    local pg_pass="$5"

    # Check if PGPASSWORD should be exported
    if [ -n "$pg_pass" ]; then
        export PGPASSWORD="$pg_pass"
    fi

    # Start processing the specified schema
    log_info "Starting vacuum and analyze process for schema $pg_schema in database $pg_db"

    # Retrieve table names for the specified schema
    log_info "Retrieving table names for schema $pg_schema in database $pg_db"
    local table_names
    table_names=$(psql -d "$pg_db" -U "$pg_user" -h "$pg_host" -t -c "\dt $pg_schema.*" | awk '{print $3}')

    # Handle potential error when table names cannot be retrieved
    if [ $? -ne 0 ]; then
        log_error "Error retrieving table names for schema $pg_schema in database $pg_db"
        exit 1
    fi

    # Loop through the table names and perform VACUUM, REINDEX, and ANALYZE with logging
    for t in $table_names; do
        log_info "Processing table: $t"

        # Vacuum table
        log_info "Vacuuming table $t..."
        psql -d "$pg_db" -U "$pg_user" -h "$pg_host" -c "VACUUM (VERBOSE, ANALYZE) $pg_schema.$t;"
        if [ $? -ne 0 ]; then
            log_error "Error vacuuming table $t"
            continue
        fi

        # Reindex table
        log_info "Reindexing table $t..."
        psql -d "$pg_db" -U "$pg_user" -h "$pg_host" -c "REINDEX TABLE $pg_schema.$t;"
        if [ $? -ne 0 ]; then
            log_error "Error reindexing table $t"
            continue
        fi

        # Analyze table
        log_info "Analyzing table $t..."
        psql -d "$pg_db" -U "$pg_user" -h "$pg_host" -c "ANALYZE $pg_schema.$t;"
        if [ $? -ne 0 ]; then
            log_error "Error analyzing table $t"
            continue
        fi
    done

    # Indicate successful completion
    log_info "Vacuum and analyze process for schema $pg_schema in database $pg_db completed successfully"
}

# Define logging functions
log_info() {
    echo "[INFO] $1"
}

log_error() {
    echo "[ERROR] $1" >&2
}

# Prompt the user for input parameters
read -p "Enter the schema to vacuum and analyze: " pg_schema
read -p "Enter the database name: " pg_db
read -p "Enter the username: " pg_user
read -p "Enter the host: " pg_host
read -s -p "Enter the password (optional): " pg_pass
echo

# Call the function with the user-provided parameters
vacuum_analyze_schema "$pg_schema" "$pg_db" "$pg_user" "$pg_host" "$pg_pass"

exit