Shell Script to delete Postgres datatase and database user

Here is sample shell script to delete existing database and database user. Before running the script, run following command, it will add execute permission to a file so it can be run as a program/script.

chmod +x script_name

How to run the script

./script_name

Here is sample shell script

#!/bin/bash
set -e
# ====== CONFIG VARIABLES ======
DB_HOST="localhost"
DB_PORT="5432"
ADMIN_USER="postgres"
ADMIN_PASSWORD="postgres_password"

DB_NAME="database_name"
DB_USER="database_user_name"

# ==============================

xport PGPASSWORD="$ADMIN_PASSWORD"

echo "Checking PostgreSQL connection..."
psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -c '\q' || {
  echo "❌ Unable to connect to PostgreSQL"
  exit 1
}

echo "Checking if database '$DB_NAME' exists..."
DB_EXISTS=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -tAc "SELECT 1 FROM pg_database WHERE datname='$DB_NAME'")

if [ "$DB_EXISTS" = "1" ]; then
  echo "Terminating active connections to '$DB_NAME'..."
  psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -c "
    SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = '$DB_NAME' AND pid <> pg_backend_pid();
  "

  echo "Dropping database '$DB_NAME'..."
  psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -c "DROP DATABASE $DB_NAME;"
else
  echo "⚠️ Database '$DB_NAME' does not exist. Skipping."
fi

echo "Checking if user '$DB_USER' exists..."
USER_EXISTS=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='$DB_USER'")

if [ "$USER_EXISTS" = "1" ]; then
  echo "Dropping user '$DB_USER'..."
  psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -c "DROP USER $DB_USER;"
else
  echo "⚠️ User '$DB_USER' does not exist. Skipping."
fi

unset PGPASSWORD

echo "🗑️ Teardown completed successfully."