gistfile1.txt

#!/bin/bash

# Check if the required arguments are provided
if [ "$#" -lt 3 ]; then
    echo "Usage: $0 <current_db_uri> <db_to_delete> <user_to_delete>"
    exit 1
fi

# Variables
CURRENT_DB_URI="$1"
DB_TO_DELETE="$2"
USER_TO_DELETE="$3"

# Drop the database
usql "$CURRENT_DB_URI" -c "DROP DATABASE IF EXISTS $DB_TO_DELETE;"

# Drop the user's owned
usql "$CURRENT_DB_URI" -c "drop owned by $USER_TO_DELETE;"

# Drop the user
usql "$CURRENT_DB_URI" -c "DROP USER IF EXISTS $USER_TO_DELETE;"

echo "Database $DB_TO_DELETE and user $USER_TO_DELETE have been deleted."


#!/bin/bash

# Check if the required arguments are provided
if [ "$#" -lt 3 ]; then
    echo "Usage: $0 <current_db_uri> <new_db_name> <new_password> [new_user]"
    exit 1
fi

# Variables
CURRENT_DB_URI="$1"
NEW_DB="$2"
NEW_PASSWORD="$3"
NEW_USER="${4:-$NEW_DB}"

# Create a new user
usql "$CURRENT_DB_URI" -c "CREATE USER $NEW_USER WITH PASSWORD '$NEW_PASSWORD';"

# Create a new database using the superuser
usql "$CURRENT_DB_URI" -c "CREATE DATABASE $NEW_DB;"

# Grant all privileges on the new database to the new user
usql "$CURRENT_DB_URI" -c "GRANT ALL PRIVILEGES ON DATABASE $NEW_DB TO $NEW_USER;"

# Grant necessary permissions on the public schema in the new database
usql "$CURRENT_DB_URI" -c "GRANT ALL ON SCHEMA public TO $NEW_USER;"
usql "$CURRENT_DB_URI" -c "ALTER DATABASE $NEW_DB OWNER TO $NEW_USER;"

# Construct the new user's URI for the new database
NEW_USER_DB_URI=$(echo "$CURRENT_DB_URI" | sed -E "s/\/\/[^@]+@/\/\/$NEW_USER:$NEW_PASSWORD@/; s/\/[^\/]+$/\/$NEW_DB/")

echo "User $NEW_USER, database $NEW_DB, and permissions setup completed. $NEW_USER_DB_URI"

#!/bin/bash

# Check if correct number of arguments are passed
if [ "$#" -ne 2 ]; then
    echo "Usage: $0 <database_uri> <database_name>"
    exit 1
fi

DATABASE_URI=$1
DATABASE_NAME=$2
OUTPUT_DIR="${DATABASE_NAME}"
CSV_FILE="${DATABASE_NAME}.csv"
CURRENT_DIR=$(pwd)

# Create a folder named after the database
rm -r "$OUTPUT_DIR"
mkdir -p "$OUTPUT_DIR"

# Change into the output directory
cd "$OUTPUT_DIR"

# Use usql to output the tables of the database into a CSV file
usql "$DATABASE_URI" -c "select table_name from information_schema.tables where table_catalog='$DATABASE_NAME' and table_schema='public';" --csv --out "$CSV_FILE"

# Read the CSV file line by line
while IFS=, read -r table_name; do
    # Skip the header line if it exists
    if [ "$table_name" != "table_name" ]; then
        # pg_dump the schema of the specific table into the folder created
        echo $table_name
        pg_dump "$DATABASE_URI" -n public -t "$table_name" -s > "${table_name}.sql"
    fi
done < "$CSV_FILE"

# Change back to the previous directory
cd "$CURRENT_DIR"

echo "$(ls -lah $OUTPUT_DIR)

--

Schema dump completed. SQL files are in the folder: $OUTPUT_DIR"