Purpose of the Scripts :
The purpose of these scripts is to facilitate the backup and restore process for PostgreSQL databases. The scripts offer flexibility by providing three types of backup methods: plain SQL, directory format, and binary format.
Key Features :
Versatility: The scripts support multiple backup types, allowing users to choose the method that best fits their needs.
Configurability: Users can easily configure backup settings, such as backup destination and compression options.
Merits and Demerits of this Shell scripts :
📋📋 Custom Format Backup and Restoration:
🌟🌟 Merits:
- Customizable Backup: Select specific database objects or schemas for backup, providing fine-grained control.
- Small Backup Size: Custom backups tend to be smaller compared to plain SQL text backups.
- Compression: Backup files can be compressed, saving storage space.
🚫🚫 Demerits:
- Not Human-Readable: Custom backups are not human-readable, making inspection harder.
- Dependency on pg_dump: Requires the pg_dump tool for creating custom backups.
- No Parallel Backup: Does not allow parallel backup and restore operations for improved performance on multi-core systems.
📋📋 Plain SQL Text Backup and Restoration:
🌟🌟 Merits:
- Human-Readable: SQL text backups are plain text files, making them easy to read and edit.
- Portability: Move SQL text backups between different PostgreSQL installations easily.
- No External Tools: No external tools are required for creating or restoring plain SQL text backups.
🚫🚫 Demerits:
- Large File Size: SQL text backups are usually larger in size compared to custom format backups.
- Slower Restore: Restoring from SQL text backups can be slower due to SQL command execution.
- No Parallel Backup: Does not allow parallel backup and restore operations.
📋📋 Directory Format Backup and Restoration:
🌟🌟 Merits:
- Parallel Backup and Restoration: Directory format backups support parallel processing, suitable for large databases.
- Human-Readable: Metadata files (Table of Contents) are in plain text format, making it easier to understand the backup structure.
- Optimized for Large Databases: Suitable for very large databases where custom format backups may be impractical.
🚫🚫 Demerits:
- Larger Backup Size: Directory format backups typically consume more storage space compared to custom format backups.
- Complex Directory Structure: Directory format backups have a complex directory structure, which might be challenging to manage.
It's essential to choose the backup and restoration method based on your specific use case, considering factors like database size, performance requirements, and ease of management.
#!/bin/bash
############################################################################################################
# DATABASE ADMINISTRATION SCRIPT #
# Discription: #
# #
# This script is used to for DBA activities #
# #
#----------------------------------------------------------------------------------------------------------#
# #
# Developed By : Tamilselvan and Sapthagiri #
# #
# Department : PostgreSQL DBA Team #
# #
# Deployed On : 24th Aug 2023 #
# #
# #
#----------------------------------------------------------------------------------------------------------#
# Release : #
#----------------------------------------------------------------------------------------------------------#
# Date | Version | Description #
#----------------------------------------------------------------------------------------------------------#
# 24th Aug 2023 | 1.1 | First Deployment #
############################################################################################################
# Define ANSI escape codes for bold and high-contrast colors
BOLD='\033[1m'
WHITE='\033[1;97m' # White text color
RED='\033[1;31m'
GREEN='\033[1;32m'
YELLOW='\033[1;33m'
BLUE='\033[1;34m'
RESET='\033[0m' # Reset color and style to default
# Colored text
PERF_TEAM_TEXT="${BOLD}${GREEN}💻Postgres DBA Team💻${RESET}"
# Elephant emoji
ELEPHANT_EMOJI="${WHITE}🐘${RESET}"
# Prompt for database details with different colored text
clear
# Get the terminal width
TERM_WIDTH=$(tput cols)
# Calculate the padding to center the text
PADDING=$(( (TERM_WIDTH - ${#PERF_TEAM_TEXT}) / 2 ))
# Define the text to be printed
TEXT="🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️🖥️"
ELEPHANT_EMOJI="🐘"
BATT_EMOJI="🔋🔋🔋"
# Center the text using ANSI escape codes
echo -e "\e[${PADDING}C${TEXT}"
echo -e "\e[${PADDING}C${BOLD}${GREEN}${BATT_EMOJI}${RESET} ${ELEPHANT_EMOJI} ${PERF_TEAM_TEXT} ${ELEPHANT_EMOJI} ${BOLD}${GREEN}${BATT_EMOJI}${RESET}"
echo -e "\e[${PADDING}C${TEXT}"
# Define your PostgreSQL elephant icon in yellow
ICON="
${YELLOW}🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘 📦📦🛢️🛢️🛢️ P o s t G r e S Q L D B A 🛢️🛢️🛢️📦📦
${YELLOW}🐘⡀⡀⢀⣠⠴⠶⠒⠒⠲⠶⠤⣤⡴⠶⠒⠒⠒⠶⠚⢛⣛⣛⡛⠓⠶⣤⡀⡀⡀ 🐘
${YELLOW}🐘⡀⣠⠟⣡⣶⣾⣿⣿⣿⣶⠖⢀⣴⣾⣿⣿⣿⣷⣦⣌⡙⢿⣿⣿⣶⣤⡙⢷⡀ 🐘
${YELLOW}🐘⢠⡏⢰⣿⣿⣿⣿⣿⣿⡏⣰⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣿⣦⠙⣿⣿⣿⣿⡀⣷🐘
${YELLOW}🐘⢸⡇⣿⣿⣿⣿⣿⣿⣿⡀⣿⣿⢿⣿⣿⣿⣿⣿⣿⣿⣿⣿⡿⠧⠘⣿⣿⣿⡀⣿🐘 🛢️ PostgreSQL - A powerful open-source relational database
${YELLOW}🐘⢸⡇⢻⣿⣿⣿⣿⣿⡟⢈⣤⣴⠒⡌⢻⣿⣿⣿⣿⡟⢡⡒⣲⡀⣿⣿⡟⢰⡇ 🐘 🛢️ Use PostgreSQL to manage, store, and retrieve data for your applications
${YELLOW}🐘⡀⣧⠸⣿⣿⣿⣿⣿⣿⢸⣿⣿⣿⣷⠸⣿⣿⣿⣿⣧⠸⣿⣿⡀⣿⣿⠃⣾⠁ 🐘 🛢️ It's known for its reliability, extensibility, and robust features
${YELLOW}🐘⡀⢹⡄⢿⣿⣿⣿⣿⡏⢸⣿⣿⣿⡿⢠⣿⣿⣿⣿⣿⣆⠹⣿⡀⣿⠏⣼⠃⡀ 🐘 🛢️ PostgreSQL project was initiated by Professor Michael Stonebreaker at the University of California in 1986
${YELLOW}🐘⡀⠈⣧⠸⣿⣿⣿⣿⣧⠘⣿⣿⣿⠃⣼⣿⣿⣿⣿⣿⣿⣿⣆⠹⡀⠏⣰⠏⡀⡀🐘
${YELLOW}🐘⡀⡀⠸⣆⢹⣿⣿⣿⣿⡧⢈⠛⠋⠸⢿⣿⣿⣿⣿⣿⣿⡿⠰⠷⠶⠖⢂⣠⠟ 🐘
${YELLOW}🐘⡀⡀⡀⠹⣆⠻⣿⣿⠋⡰⠿⠛⣡⣶⡀⣿⣿⣿⣿⣿⣿⡇⢸⠒⠚⠛⠋⠁⡀ 🐘
${YELLOW}🐘⡀⡀⡀⡀⠙⠷⣤⣤⠾⣦⣉⣉⣉⣤⡆⣿⣿⣿⣿⣿⣿⡇⣾⡀⡀⡀⡀⡀⡀ 🐘
${YELLOW}🐘⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⠈⠉⠉⢸⡇⣿⣿⣿⣿⣿⣿⠇⣾⡀⡀⡀⡀⡀⡀ 🐘
${YELLOW}🐘⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⠘⡇⢹⣿⣿⣿⣿⣿⢀⡟⡀⡀⡀⡀⡀⡀ 🐘
${YELLOW}🐘⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⢳⡈⠿⣿⡿⠿⢃⡼⠁⡀⡀⡀⡀⡀⡀ 🐘
${YELLOW}🐘⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⡀⢾⡋⡀⡀⢀⡺⡀⡀⡀⡀⡀⡀ 🐘
${YELLOW}🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘"
# PostgreSQL text with spaces
POSTGRESQL_TEXT="${BOLD}${RED}P ${GREEN}O ${YELLOW}S ${BLUE}T ${RESET}${BOLD}${GREEN}G ${YELLOW}R ${BLUE}E ${RED}S ${GREEN}Q ${YELLOW}L${RESET}"
# Colored "admin tool" text
ADMIN_TOOL_TEXT="${BOLD}${BLUE} A${RESET}${BOLD}${RED}d${RESET}${BOLD}${YELLOW}m${RESET}${BOLD}${GREEN}i${RESET}${BOLD}${BLUE}n ${BOLD}${RED}T${RESET}${BOLD}${GREEN}o${RESET}${BOLD}${YELLOW}o${RESET}${BOLD}${BLUE}l${RESET}"
# Print elephant emoji, colored icon, PostgreSQL text, admin tool text, and another elephant emoji
echo -e "${BOLD}${BLUE}${ICON}${RESET} ${ELEPHANT_EMOJI} 💻 ${POSTGRESQL_TEXT} ${ADMIN_TOOL_TEXT} 💻 ${ELEPHANT_EMOJI}"
# Define the location of the .config.cfg file
config_file=".config.cfg"
# Function to check if the config file is empty
is_config_empty() {
if [ ! -s "$config_file" ]; then
echo "📄 No Authorized Guest Users Found"
return 0 # No authorized users found
else
echo "📄 Authorized Guest Users Found"
return 1 # Authorized users found
fi
}
# Function to initialize the config file with an admin user and password
initialize_config() {
admin_password="$YOUR_ADMIN_PASSWORD_ENV_VAR"
# Create the configuration file if it doesn't exist
if [ ! -f "$config_file" ]; then
touch "$config_file"
echo "# config.cfg" > "$config_file"
echo >> "$config_file"
fi
# Check if the admin password is present in the configuration file
if ! grep -q "admin_password=" "$config_file"; then
echo "# Define the admin password" >> "$config_file"
echo "admin_password=\"$admin_password\"" >> "$config_file"
echo >> "$config_file"
echo "# Define authorized users and their passwords" >> "$config_file"
echo >> "$config_file"
fi
authorized_users=()
echo "authorized_users=(" >> "$config_file"
while true; do
new_username_emoji="✨"
read -p "Enter an authorized username (or leave empty to finish) $new_username_emoji : " new_username
if [ -z "$new_username" ]; then
break
fi
if [ "$new_username" != "$admin_password" ]; then
authorized_users+=("$new_username")
echo "\"$new_username\"" >> "$config_file"
read -s -p "Enter the password for $new_username 🔐 : " new_password
echo # Move to the next line
echo "\"$new_password\"" >> "$config_file"
else
echo "Admin password cannot be an authorized user. 🚫"
fi
done
for username in "${authorized_users[@]}"; do
echo "\"$username\"" >> "$config_file"
done
echo ")" >> "$config_file"
# Add this echo to display the contents of the .config.cfg file after initialization
#echo "Contents of .config.cfg after initialization:"
#cat "$config_file"
}
# Check if the config file is empty, and initialize it if necessary
if is_config_empty; then
echo "No authorized users found in the config file. Initializing... 🚀"
initialize_config
fi
# Source the configuration file
source "$config_file"
# Function to check if the user is authorized
check_user_authorization() {
local username="$1"
local password="$2"
for i in "${!authorized_users[@]}"; do
if [[ "${authorized_users[$i]}" == "$username" && "${user_passwords[$i]}" == "$password" ]]; then
echo "✅ User is authorized"
return 0 # Authorized
fi
done
echo "❌ User is unauthorized"
return 1 # Unauthorized
}
# Function to add a new guest user to the configuration file
add_new_user() {
local new_username
local new_password
read -p "Enter the new guest username 👤: " new_username
read -s -p "Enter the new guest password 🔐: " new_password
echo # Move to the next line
# Verify the admin password
read -s -p "Enter admin password to add the new user 🔑: " entered_password
echo # Move to the next line
if [ "$entered_password" != "$admin_password" ]; then
echo "Admin password incorrect. Cannot add new user. ❌"
return
fi
# Add the new user to the configuration, including the admin_password
authorized_users+=("$new_username")
user_passwords+=("$new_password")
# Save the updated configuration
echo "# Define the admin password" > "$config_file"
echo "admin_password=\"$admin_password\"" >> "$config_file"
echo "# Define authorized users and their passwords" >> "$config_file"
echo "authorized_users=(" >> "$config_file"
for ((i = 0; i < ${#authorized_users[@]}; i++)); do
echo "\"${authorized_users[$i]}\"" >> "$config_file"
done
echo ")" >> "$config_file"
echo "user_passwords=(" >> "$config_file"
for ((i = 0; i < ${#user_passwords[@]}; i++)); do
echo "\"${user_passwords[$i]}\"" >> "$config_file"
done
echo ")" >> "$config_file"
echo "New user added successfully. ✅"
}
# Function to unlock the script
unlock_script() {
local unlock_attempts=3
local unlock_success=false
login_attempts_file=".login_attempts.log" # Hidden file
for ((i = 0; i < unlock_attempts; i++)); do
read -s -p "Enter admin password to unlock the script 🔐: " entered_password
echo
if [ "$entered_password" = "$admin_password" ]; then
echo "Admin login successful. Unlocking the script. 🚀"
# Clear the login_attempts.log file upon successful unlock
if echo "0" > "$login_attempts_file"; then
unlock_success=true
echo "Login attempts file cleared. ✅"
else
echo "Error: Failed to clear login attempts file. ❌"
unlock_success=false
fi
break
else
echo "Admin login failed. Please try again. Attempts remaining: $((unlock_attempts - i - 1))"
fi
done
if [ "$unlock_success" = false ]; then
echo "Maximum unlock attempts reached. The script remains locked. 🛡️"
exit 1
fi
}
# Define the maximum number of login attempts
max_attempts=3
login_attempts_file=".login_attempts.log" # Hidden file
# Check if login attempts file exists; create it if not
if [ ! -f "$login_attempts_file" ]; then
echo "0" > "$login_attempts_file"
fi
# Read the current number of login attempts
login_attempts=$(<"$login_attempts_file")
# Check if the maximum number of attempts has been reached
if [ "$login_attempts" -ge "$max_attempts" ]; then
echo "Unauthorized access. The script is locked. 🛡️"
locked="true"
fi
# Check if the script is locked and unlock if necessary
if [ "$locked" = "true" ]; then
unlock_script
# Check if the script is still locked after unlocking
if [ -f "$login_attempts_file" ] && [ "$(cat "$login_attempts_file")" -ge "$max_attempts" ]; then
echo "The script is still locked. Exiting. 🛡️"
exit 1
else
echo "The script is unlocked. Continuing. 🚀"
login_attempts=0 # Reset login_attempts to 0 after a successful unlock
fi
fi
# Prompt the user for username and password
while true; do
if [ ${#authorized_users[@]} -eq 0 ]; then
signup_emoji="📝"
read -p "No authorized users found. Do you want to sign up? (yes/no) $signup_emoji : " signup_choice
if [ "$signup_choice" = "yes" ]; then
add_new_user
else
echo "Exiting script."
exit 0
fi
fi
# Define emojis for the prompts
username_emoji="👤"
password_emoji="🔒"
# Use the emojis in the read prompts
read -p "Please enter your PostgreSQL Tool Guest username $username_emoji : " username
read -s -p "Please enter your PostgreSQL Tool Guest password $password_emoji : " password
echo # Move to the next line
if check_user_authorization "$username" "$password"; then
echo "Welcome, $username! You are authorized to run this script. 🚀"
#echo "Clear login attempts upon successful login."
echo "0" > "$login_attempts_file"
break
else
echo "Unauthorized. Access denied. ❌"
((login_attempts++))
echo "$login_attempts" > "$login_attempts_file"
# Check if the maximum number of login attempts has been reached
if [ "$login_attempts" -ge "$max_attempts" ]; then
echo "Maximum login attempts reached. The script is locked. 🛡️"
exit 1
else
echo "You have $((max_attempts - login_attempts)) more attempts remaining."
fi
read -p "Do you want to add a new user? (yes/no) $signup_emoji : " add_user_choice
if [ "$add_user_choice" = "yes" ]; then
add_new_user
fi
fi
done
# Rest of your script goes here
# Get the number of available CPU cores
available_cores=$(nproc)
# Function to perform a backup in custom format
perform_backup_custom() {
# Check if backup folder path has been configured
check_backup_folder
# Set the PGPASSWORD environment variable
export PGPASSWORD="$db_password"
# Validate the database connection
if ! validate_database_connection "$db_host" "$db_port" "$db_name" "$db_user" "$db_password"; then
echo "❌ Backup aborted due to database connection failure."
return 1
fi
while true; do
# Prompt the user for a tag name
read -p "💼 Enter a tag for this backup: " tag
if [ -z "$tag" ]; then
echo "❌ Tag name cannot be empty. Please provide a valid tag name."
else
break # Exit the loop if a non-empty tag is provided
fi
done
# Prompt the number of parallel jobs
#read -p "Enter the number of parallel jobs (1-$available_cores): " num_jobs
# Validate user input
#if ! [[ "$num_jobs" =~ ^[1-9][0-9]*$ ]] || [ "$num_jobs" -gt "$available_cores" ]; then
# echo "Invalid input. Please enter a valid number of parallel jobs."
# exit 1
#fi
# Create a timestamp for the backup files
timestamp=$(date +"%d-%m-%Y_%H-%M-%S")
# Backup file names
custom_sql_file="$backup_folder/custom-$tag-$db_name-backup-$timestamp.backup"
gz_custom_file="$backup_folder/custom-$tag-$db_name-backup-$timestamp.backup.gz" # Use .gz extension
# Log files
custom_log_file="$backup_folder/custom-$tag-$db_name-backup_log_$timestamp.log"
# Log function
log() {
echo "$(date +"%Y-%m-%d %H:%M:%S") - $1" >> "$2"
}
# Initialize status
status=""
# Perform the backup in custom format
log "🔵 Starting custom format backup process" "$custom_log_file"
#if pg_dump -h "$db_host" -p "$db_port" -U "$db_user" -d "$db_name" -F c -b -v -j "$num_jobs" -f "$custom_sql_file" > "$custom_log_file" 2>&1; then
if pg_dump -h "$db_host" -p "$db_port" -U "$db_user" -d "$db_name" -F c -b -v -f "$custom_sql_file" > "$custom_log_file" 2>&1; then
log "🟢 Custom format backup created" "$custom_log_file"
status="Success" # Update status to "Success"
else
log "🔴 Error: pg_dump return non-zero code (Custom format)" "$custom_log_file"
status="Failure" # Update status to "Failure" in case of failure
exit 1
fi
# Compress the custom format backup using gzip
if [ "$status" == "Success" ]; then
if gzip --fast -c "$custom_sql_file" > "$gz_custom_file"; then
log "🟢 Custom format backup successfully compressed" "$custom_log_file"
echo "🟢 Custom format backup successfully compressed." # Add this line to print the message
rm "$custom_sql_file"
else
log "🔴 Error: Error compressing custom format backup" "$custom_log_file"
status="Failure" # Update status to "Failure" in case of compression failure
fi
fi
log "🔵 Backup process completed with status: $status" "$custom_log_file"
# Print a completion message
echo "🔵 Backup process completed with status: $status"
# Log backup history
log_history "Backup (Custom Format)" "$tag" "$status"
}
# Function to perform a backup in plain SQL format
perform_backup_plain() {
# Check if backup folder path has been configured
check_backup_folder
# Set the PGPASSWORD environment variable
export PGPASSWORD="$db_password"
# Validate the database connection
if ! validate_database_connection "$db_host" "$db_port" "$db_name" "$db_user" "$db_password"; then
echo "❌ Backup aborted due to database connection failure."
return 1
fi
while true; do
# Prompt the user for a tag name
read -p "💼 Enter a tag for this backup: " tag
if [ -z "$tag" ]; then
echo "❌ Tag name cannot be empty. Please provide a valid tag name."
else
break # Exit the loop if a non-empty tag is provided
fi
done
# Prompt the number of parallel jobs
#read -p "Enter the number of parallel jobs (1-$available_cores): " num_jobs
# Validate user input
#if ! [[ "$num_jobs" =~ ^[1-9][0-9]*$ ]] || [ "$num_jobs" -gt "$available_cores" ]; then
# echo "Invalid input. Please enter a valid number of parallel jobs."
# exit 1
#fi
# Create a timestamp for the backup files
timestamp=$(date +"%d-%m-%Y_%H-%M-%S")
# Backup file names
plain_sql_file="$backup_folder/plain-$tag-$db_name-backup-$timestamp.sql"
gz_plain_file="$backup_folder/plain-$tag-$db_name-backup-$timestamp.sql.gz" # Use .gz extension
# Log files
plain_log_file="$backup_folder/plain-$tag-$db_name-backup_log_$timestamp.log"
# Log function
log() {
echo "$(date +"%Y-%m-%d %H:%M:%S") - $1" >> "$2"
}
# Initialize status
status=""
# Perform the backup in plain SQL format
log "🔵 Starting plain SQL backup process" "$plain_log_file"
#if pg_dump -h "$db_host" -p "$db_port" -U "$db_user" -d "$db_name" --format plain --verbose -j "$num_jobs" -f "$plain_sql_file" > "$plain_log_file" 2>&1; then
if pg_dump -h "$db_host" -p "$db_port" -U "$db_user" -d "$db_name" --format plain --verbose -f "$plain_sql_file" > "$plain_log_file" 2>&1; then
log "🟢 Plain SQL format backup created" "$plain_log_file"
status="Success" # Update status to "Success"
else
log "🔴 Error: pg_dump returned non-zero code (Plain SQL format)" "$plain_log_file"
status="Failure" # Update status to "Failure" in case of failure
exit 1
fi
# Compress the plain SQL format backup using gzip
if [ "$status" == "Success" ]; then
if gzip --fast -c "$plain_sql_file" > "$gz_plain_file"; then
log "🟢 Plain SQL format backup successfully compressed" "$plain_log_file"
echo "🟢 Plain SQL format backup successfully compressed." # Add this line to print the message
rm "$plain_sql_file"
else
log "🔴 Error: Error compressing plain SQL format backup" "$plain_log_file"
status="Failure" # Update status to "Failure" in case of compression failure
fi
fi
log "🔵 Backup process completed with status: $status" "$plain_log_file"
echo "🔵 Backup process completed with status: $status"
# Log backup history
log_history "Backup (Plain SQL Format)" "$tag" "$status"
}
# Function to perform a backup in directory format
perform_backup_directory() {
# Check if backup folder path has been configured
check_backup_folder
# Set the PGPASSWORD environment variable
export PGPASSWORD="$db_password"
# Validate the database connection
if ! validate_database_connection "$db_host" "$db_port" "$db_name" "$db_user" "$db_password"; then
echo "🚫 Backup aborted due to database connection failure."
return 1
fi
while true; do
# Prompt the user for a tag name
read -p "💼 Enter a tag for this backup: " tag
if [ -z "$tag" ]; then
echo "🚫 Tag name cannot be empty. Please provide a valid tag name."
else
break # Exit the loop if a non-empty tag is provided
fi
done
# Prompt the number of parallel jobs
read -p "🚀 Enter the number of parallel jobs (1-$available_cores): " num_jobs
# Validate user input
if ! [[ "$num_jobs" =~ ^[1-9][0-9]*$ ]] || [ "$num_jobs" -gt "$available_cores" ]; then
echo "🚫 Invalid input. Please enter a valid number of parallel jobs."
exit 1
fi
# Create a timestamp for the backup files
timestamp=$(date +"%d-%m-%Y_%H-%M-%S")
# Log files
backup_log_file="$backup_folder/Directory-$tag-$db_name-backup_log_$timestamp.log"
# Log function
log() {
echo "📜 $(date +"%Y-%m-%d %H:%M:%S") - $1" >> "$backup_log_file"
}
# Initialize status
status=""
# Perform the backup in directory format
log "🚀 Starting directory format backup process"
if pg_dump -h "$db_host" -p "$db_port" -U "$db_user" -d "$db_name" -F d -j "$num_jobs" -f "$backup_folder/Directory-$tag-$db_name-backup-$timestamp" > "$backup_log_file" 2>&1; then
log "✅ Directory format backup created" "$backup_log_file"
echo "✅ Directory format backup created" # Add this line to print the message
status="Success" # Update status to "Success"
else
log "🚫 Error: pg_dump returned a non-zero code (Directory format)" "$backup_log_file"
status="Failure" # Update status to "Failure" in case of failure
exit 1
fi
log "🎉 Backup process completed with status: $status" "$backup_log_file"
echo "🎉 Backup process completed with status: $status"
# Log backup history
log_history "Backup (Directory Format)" "$tag" "$status"
}
# Function to perform a restore in custom format
perform_restore_custom() {
# Check if backup folder path has been configured
check_backup_folder
# Set the PGPASSWORD environment variable
export PGPASSWORD="$db_password"
# Validate the database connection
if ! validate_database_connection "$db_host" "$db_port" "$db_name" "$db_user" "$db_password"; then
echo "🚫 Restore aborted due to database connection failure."
return 1
fi
# Prompt the user for the tag to select the appropriate backup for restoration
read -p "💼 Enter the tag of the backup to restore: " tag
# Create a timestamp for the backup files
timestamp=$(date +"%d-%m-%Y_%H-%M-%S")
echo "📦 Available custom backup files:"
custom_backup_files=($(ls $backup_folder/*-$tag-*-backup-*.backup.gz)) # Use .backup.gz extension
if [ ${#custom_backup_files[@]} -eq 0 ]; then
echo "🚫 No custom backup files found for restoration."
exit 1
fi
echo "📜 Choose a custom backup file to restore:"
for i in "${!custom_backup_files[@]}"; do
echo "$i. ${custom_backup_files[$i]}"
done
read -p "🔢 Enter the number of the custom backup file to restore: " choice
if [ "$choice" -ge 0 ] && [ "$choice" -lt ${#custom_backup_files[@]} ]; then
selected_backup="${custom_backup_files[$choice]}"
echo "🔁 Restoring custom backup: $selected_backup"
# Log file for custom format restoration
custom_restore_log_file="$backup_folder/custom-$tag-$db_name-restore_log_$timestamp.log"
# Log function for restoration
restore_log() {
echo "$(date +"%Y-%m-%d %H:%M:%S") - $1" >> "$2"
}
# Initialize status
status=""
# Unzipping the selected backup file before restoring
unzipped_backup_file="${selected_backup%.gz}" # Remove .gz extension
gunzip --fast -c "$selected_backup" > "$unzipped_backup_file"
# Your restoration command goes here
if pg_restore -h "$db_host" -p "$db_port" -U "$db_user" -d "$db_name" -v "$unzipped_backup_file" >> "$custom_restore_log_file" 2>&1; then
restore_log "✅ Custom format restoration completed successfully" "$custom_restore_log_file"
status="Success" # Update status to "Success"
else
restore_log "🚫 Error: pg_restore returned a non-zero code (Custom format)" "$custom_restore_log_file"
status="Failure" # Update status to "Failure" in case of failure
fi
# Remove the unzipped backup file
rm "$unzipped_backup_file"
# Log restore history
log_history "Restore (Custom Format)" "$tag" "$status"
# Monitor the restoration log using tail
echo "👀 Monitoring restoration log..."
tail -f "$custom_restore_log_file" | while read line; do
echo "$line"
if [[ "$line" == *"✅ Custom format restoration completed successfully"* ]]; then
echo "✅ Restoration completed successfully."
break
elif [[ "$line" == *"🚫 Restoration failed"* ]]; then
echo "🚫 Restoration failed."
break
fi
done
else
echo "🚫 Invalid choice."
fi
}
# Function to perform a restore in plain SQL format
perform_restore_plain() {
# Check if backup folder path has been configured
check_backup_folder
# Set the PGPASSWORD environment variable
export PGPASSWORD="$db_password"
# Validate the database connection
if ! validate_database_connection "$db_host" "$db_port" "$db_name" "$db_user" "$db_password"; then
echo "🚫 Restore aborted due to database connection failure."
return 1
fi
# Prompt the user for the tag to select the appropriate backup for restoration
read -p "💼 Enter the tag of the backup to restore: " tag
# Create a timestamp for the backup files
timestamp=$(date +"%d-%m-%Y_%H-%M-%S")
echo "📦 Available plain SQL backup files:"
plain_backup_files=($(ls $backup_folder/*-$tag-*-backup-*.sql.gz)) # Use .sql.gz extension
if [ ${#plain_backup_files[@]} -eq 0 ]; then
echo "🚫 No plain SQL backup files found for restoration."
exit 1
fi
echo "📜 Choose a plain SQL backup file to restore:"
for i in "${!plain_backup_files[@]}"; do
echo "$i. ${plain_backup_files[$i]}"
done
read -p "🔢 Enter the number of the plain SQL backup file to restore: " choice
if [ "$choice" -ge 0 ] && [ "$choice" -lt ${#plain_backup_files[@]} ]; then
selected_backup="${plain_backup_files[$choice]}"
echo "🔁 Restoring plain SQL backup: $selected_backup"
# Log file for plain SQL format restoration
plain_restore_log_file="$backup_folder/plain-$tag-$db_name-restore_log_$timestamp.log"
# Log function for restoration
restore_log() {
echo "$(date +"%Y-%m-%d %H:%M:%S") - $1" >> "$2"
}
# Initialize status
status=""
# Unzipping the selected backup file before restoring
unzipped_backup_file="${selected_backup%.gz}" # Remove .gz extension
gunzip --fast -c "$selected_backup" > "$unzipped_backup_file"
# Your restoration command goes here
if psql -h "$db_host" -p "$db_port" -U "$db_user" -d "$db_name" -f "$unzipped_backup_file" >> "$plain_restore_log_file" 2>&1; then
restore_log "✅ Plain SQL format restoration completed successfully" "$plain_restore_log_file"
status="Success" # Update status to "Success"
else
restore_log "🚫 Error: psql returned a non-zero code (Plain SQL format)" "$plain_restore_log_file"
status="Failure" # Update status to "Failure" in case of failure
fi
# Remove the unzipped backup file
rm "$unzipped_backup_file"
# Log restore history
log_history "Restore (Plain SQL Format)" "$tag" "$status"
# Monitor the restoration log using tail
echo "👀 Monitoring restoration log..."
tail -f "$plain_restore_log_file" | while read line; do
echo "$line"
if [[ "$line" == *"✅ Plain SQL format restoration completed successfully"* ]]; then
echo "✅ Restoration completed successfully."
break
elif [[ "$line" == *"🚫 Restoration failed"* ]]; then
echo "🚫 Restoration failed."
break
fi
done
else
echo "🚫 Invalid choice."
fi
}
# Function to perform a restore from directory format backup
perform_restore_directory() {
# Check if backup folder path has been configured
check_backup_folder
# Set the PGPASSWORD environment variable
export PGPASSWORD="$db_password"
# Validate the database connection
if ! validate_database_connection "$db_host" "$db_port" "$db_name" "$db_user" "$db_password"; then
echo "🚫 Restore aborted due to database connection failure."
return 1
fi
# Prompt the user for the number of parallel jobs
read -p "🔢 Enter the number of parallel jobs (1-$available_cores): " num_jobs
# Validate user input
if ! [[ "$num_jobs" =~ ^[1-9][0-9]*$ ]] || [ "$num_jobs" -gt "$available_cores" ]; then
echo "🚫 Invalid input. Please enter a valid number of parallel jobs."
exit 1
fi
# Create a timestamp for the Restore files
timestamp=$(date +"%d-%m-%Y_%H-%M-%S")
# List available backup directories for selection
echo "📂 Available backup directories:"
for backup_dir in "$backup_folder"/*/; do
echo "$(basename "$backup_dir")"
done
# Prompt the user for a backup directory to restore
read -p "📦 Enter the name of the backup directory to restore: " restore_dir_name
# Check if the user entered an empty directory name
if [ -z "$restore_dir_name" ]; then
echo "🚫 You must specify a valid backup directory name."
exit 1
fi
# Construct the full path to the selected backup directory
restore_dir_path="$backup_folder/$restore_dir_name"
# Validate the selected backup directory
if [ ! -d "$restore_dir_path" ]; then
echo "🚫 Selected backup directory does not exist."
exit 1
fi
# Check if the selected backup directory is empty
if [ -z "$(ls -A "$restore_dir_path")" ]; then
echo "🚫 Selected backup directory is empty and does not contain any backups."
exit 1
fi
# Log file for restore from directory format
restore_log_file="$backup_folder/$restore_dir_name-$db_name-restore_log_$timestamp.log"
# Log function
log() {
echo "$(date +"%Y-%m-%d %H:%M:%S") - $1" >> "$restore_log_file"
}
# Initialize status
status=""
# Perform the restore from directory format backup
log "🔄 Starting restore from directory format backup process"
if pg_restore -h "$db_host" -p "$db_port" -U "$db_user" -d "$db_name" -j "$num_jobs" "$restore_dir_path" > "$restore_log_file" 2>&1; then
log "✅ Restore from directory format backup completed successfully"
status="Success" # Update status to "Success"
echo "✅ Restore from directory format backup completed successfully"
else
log "🚫 Error: pg_restore returned a non-zero code (Directory format restore)"
status="Failure" # Update status to "Failure" in case of failure
exit 1
fi
log "🏁 Restore process completed"
echo "🏁 Restore process completed"
# Log restore history
log_history "Restore (Directory Format)" "$restore_dir_name" "$status"
}
# Function to validate the database connection
validate_database_connection() {
local host="$1"
local port="$2"
local dbname="$3"
local user="$4"
local password="$5"
# Attempt to connect to the database
psql -h "$host" -p "$port" -U "$user" -d "$dbname" -c "SELECT 1;" > /dev/null 2>&1
# Check the exit status of the previous command
if [ $? -eq 0 ]; then
# Connection successful ✅
return 0
else
# Connection failed 🚫
return 1
fi
}
# Function to check and manage backup folders
manage_backup_folders() {
# Check if backup folder path has been configured
check_backup_folder
# Define a hidden log file for deleted backups
deleted_backup_log="$backup_folder/.deleted_backup_log.log"
# Initialize the log file if it doesn't exist
touch "$deleted_backup_log"
# Now, add housekeeping activities for each backup type here based on user input
while true; do
echo "Backup Housekeeping Options:"
echo "1. 📁 List and Delete Custom Backup Files"
echo "2. 📁 List and Delete Plain Backup Files"
echo "3. 📁 List and Delete Directory Backup Files"
echo "4. 🚪 Quit"
invalid_attempts=0 # Initialize the count of invalid attempts
while true; do
read -p "Enter your choice (1/2/3/4): " choice
case $choice in
1)
# List and delete custom backup files and log files
custom_backup_files=($(ls "$backup_folder"/custom-*"$db_name"*.backup.gz 2>/dev/null))
if [ ${#custom_backup_files[@]} -eq 0 ]; then
echo "No custom backup files found for deletion. 🚫"
else
echo "Custom Backup Files:"
for i in "${!custom_backup_files[@]}"; do
echo "$i. ${custom_backup_files[$i]}"
done
read -p "Enter the number of the custom backup file to delete (or press Enter to cancel): " choice
if [ -n "$choice" ] && [ "$choice" -ge 0 ] && [ "$choice" -lt ${#custom_backup_files[@]} ]; then
selected_backup="${custom_backup_files[$choice]}"
echo "Deleting custom backup file: $selected_backup"
rm "$selected_backup"
echo "Custom Backup file '$selected_backup' deleted. ✅"
# Log the deleted backup into the log file
echo "$(date '+%Y-%m-%d %H:%M:%S') - Deleted: $selected_backup" >> "$deleted_backup_log"
else
echo "Deletion canceled. ❌"
fi
fi
;;
2)
# List and delete plain backup files and log files
plain_backup_files=($(ls "$backup_folder"/plain-*"$db_name"*.backup.gz 2>/dev/null))
if [ ${#plain_backup_files[@]} -eq 0 ]; then
echo "No plain backup files found for deletion. 🚫"
else
echo "Plain Backup Files:"
for i in "${!plain_backup_files[@]}"; do
echo "$i. ${plain_backup_files[$i]}"
done
read -p "Enter the number of the plain backup file to delete (or press Enter to cancel): " choice
if [ -n "$choice" ] && [ "$choice" -ge 0 ] && [ "$choice" -lt ${#plain_backup_files[@]} ]; then
selected_backup="${plain_backup_files[$choice]}"
echo "Deleting plain backup file: $selected_backup"
rm "$selected_backup"
echo "Plain Backup file '$selected_backup' deleted. ✅"
# Log the deleted backup into the log file
echo "$(date '+%Y-%m-%d %H:%M:%S') - Deleted: $selected_backup" >> "$deleted_backup_log"
else
echo "Deletion canceled. ❌"
fi
fi
;;
3)
# List and delete directory backup folders
directory_backup_folders=($(ls -d "$backup_folder"/directory-*"$db_name"*/ 2>/dev/null))
if [ ${#directory_backup_folders[@]} -eq 0 ]; then
echo "No directory backup folders found for deletion. 🚫"
else
echo "Directory Backup Folders:"
for i in "${!directory_backup_folders[@]}"; do
echo "$i. ${directory_backup_folders[$i]}"
done
read -p "Enter the number of the directory backup folder to delete (or press Enter to cancel): " choice
if [ -n "$choice" ] && [ "$choice" -ge 0 ] && [ "$choice" -lt ${#directory_backup_folders[@]} ]; then
selected_folder="${directory_backup_folders[$choice]}"
echo "Deleting directory backup folder: $selected_folder"
rm -rf "$selected_folder"
echo "Directory Backup folder '$selected_folder' deleted. ✅"
# Log the deleted backup into the log file
echo "$(date '+%Y-%m-%d %H:%M:%S') - Deleted: $selected_folder" >> "$deleted_backup_log"
else
echo "Deletion canceled. ❌"
fi
fi
;;
4)
return # Exit the housekeeping function and return to the main menu
;;
*)
((invalid_attempts++)) # Increment invalid attempts count
if [ "$invalid_attempts" -ge 3 ]; then
echo "Invalid choice for 3 consecutive attempts. Returning to the main menu. ❌"
return # Exit the housekeeping function and return to the main menu
else
echo "Invalid choice. Please enter a valid option. ❌"
fi
;;
esac
done
done
}
# Function to log history
log_history() {
local operation="$1"
local tag="$2"
local status="$3" # Add status as the third parameter
local timestamp="$(date +"%Y-%m-%d %H:%M:%S")"
local history_file="$backup_folder/.Backup_Restore_history.log" # Specify the path to your history log file
echo "$timestamp - $operation - Database: $db_name - Tag: $tag - Status: $status" >> "$history_file"
}
# Prompt for database details with different colored text
clear
# ANSI escape code for yellow text
yellow_color="\033[33m"
# ANSI escape code for cyan text
cyan_color="\033[1;3;36m\033[40m"
larger_text="
🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘
🐘 ██████╗ ██████╗ ███████╗████████╗ ██████╗ ██████╗ ███████╗███████╗ ██████╗ ██╗ ████████╗ ██████╗ ██████╗ ██╗ 🐘
🐘 ██╔══██╗██╔═══██╗██╔════╝╚══██╔══╝██╔════╝ ██╔══██╗██╔════╝██╔════╝██╔═══██╗██║ ╚══██╔══╝██╔═══██╗██╔═══██╗██║ 🐘
🐘 ██████╔╝██║ ██║███████╗ ██║ ██║ ███╗██████╔╝█████╗ ███████╗██║ ██║██║ ██║ ██║ ██║██║ ██║██║ 🐘
🐘 ██╔═══╝ ██║ ██║╚════██║ ██║ ██║ ██║██╔══██╗██╔══╝ ╚════██║██║▄▄ ██║██║ ██║ ██║ ██║██║ ██║██║ 🐘
🐘 ██║ ╚██████╔╝███████║ ██║ ╚██████╔╝██║ ██║███████╗███████║╚██████╔╝███████╗ ██║ ╚██████╔╝╚██████╔╝███████╗🐘
🐘 ╚═╝ ╚═════╝ ╚══════╝ ╚═╝ ╚═════╝ ╚═╝ ╚═╝╚══════╝╚══════╝ ╚══▀▀═╝ ╚══════╝ ╚═╝ ╚═════╝ ╚═════╝ ╚══════╝🐘
🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘🐘"
# Smaller text for creators
creators_text="SCRIPTS DEVELOPED BY TAMIL & SAPTHA"
# Reset formatting
reset_formatting="\033[0m"
# Get terminal width
term_width=$(tput cols)
# Calculate padding for larger text
padding=$(( ($term_width - ${#larger_text}) / 1 ))
# Create the padding string for larger text
padding_str=$(printf "%${padding}s")
# Combine color code, padding, and larger ASCII art
centered_colored_text="${padding_str}${yellow_color}${larger_text}${reset_formatting}"
# Calculate padding for creators text
padding_creators=$(( ($term_width - ${#creators_text}) / 1 ))
# Create the padding string for creators text
padding_str_creators=$(printf "%${padding_creators}s")
# Combine color code, padding, and smaller text
centered_colored_creators="${padding_str_creators}${cyan_color}${creators_text}${reset_formatting}"
# Print centered and larger formatted text
echo -e "$centered_colored_text"
echo -e "$centered_colored_creators"
# Function to prompt for input and validate it
prompt_and_validate_input() {
local input_var="$1"
local prompt_message="$2"
local error_message="$3"
local hide_input="$4"
while [[ -z "${!input_var}" ]]; do
echo -e "$prompt_message"
local input=""
local input_char=""
if [[ "$hide_input" == "true" ]]; then
while IFS= read -r -s -n 1 input_char; do
if [[ "$input_char" == $'\0' ]]; then
break
elif [[ "$input_char" == $'\177' ]]; then
# Handle backspace (ASCII 127)
if [ -n "$input" ]; then
input=${input%?} # Remove the last character
echo -en "\b \b" # Erase the character on the screen
fi
else
input+="$input_char"
echo -n "*"
fi
done
else
read -r input
fi
echo "" # Print a newline after the password input
eval "$input_var=\"$input\""
if [[ -z "${!input_var}" ]]; then
echo -e "\e[1;31m$error_message\e[0m"
fi
done
}
# Function to prompt for and verify the password
#verify_password() {
# local password="Perf_team" # Replace with your desired password
#
# read -s -p "Enter the password: " entered_password
# echo
#
# if [ "$entered_password" != "$password" ]; then
# echo "Incorrect password. Access denied."
# exit 1
# fi
#}
# Prompt for the password before executing the script
#verify_password
# Prompt for the database name with "Multiple" option
prompt_and_validate_input "db_name" $'\e[1;93m┌──────────────────────────────────────────────────────────────┐
🌟 Enter Database Name (Create/Backup/Restore/Drop/List): 🌟
└──────────────────────────────────────────────────────────────┘\e[0m' "Please enter a valid DB operation." "false"
# Prompt for the database host (with default value)
prompt_and_validate_input "db_host" $'\e[1;32m┌───────────────────────────────────────────────────┐
🌟 Enter Database Host (default: localhost): 🌟
└───────────────────────────────────────────────────┘\e[0m' "Please enter a valid DB Host." "false"
# Prompt for the database port (with default value)
prompt_and_validate_input "db_port" $'\e[1;33m┌───────────────────────────────────────────────────┐
🌟 Enter Database Port (default: 5432): 🌟
└───────────────────────────────────────────────────┘\e[0m' "Please enter a valid DB Port." "false"
# Prompt for the database username
prompt_and_validate_input "db_user" '\e[1;34m┌─────────────────────────────────────────┐
🌟 Enter Database Username: 🌟
└─────────────────────────────────────────┘\e[0m' "Please enter a valid DB Username." "false"
# Prompt for the database password (hidden input)
prompt_and_validate_input "db_password" '\e[1;35m┌─────────────────────────────────────────┐
🌟 Enter Database Password: 🌟
└─────────────────────────────────────────┘\e[0m' "Please enter a valid DB Password." "true"
# Prompt for the superuser name
prompt_and_validate_input "superuser_name" $'\e[1;36m┌───────────────────────────────────────────────────┐
🌟 Enter Superuser Name (usually 'postgres'): 🌟
└───────────────────────────────────────────────────┘\e[0m' "Please enter a valid superuser name." "false"
echo
# Function to check if backup folder path has already been configured
check_backup_folder() {
# New configuration file name
config_file=".backup_config.conf"
# Check if the configuration file exists
if [ -f "$config_file" ]; then
# Source the configuration file to get the backup_folder variable
source "$config_file"
if [ -z "$backup_folder" ]; then
echo "Backup folder path is not configured in the configuration file. 📂"
else
echo "Backup folder path is already configured: $backup_folder 📂"
return
fi
fi
# If the configuration file does not exist or does not contain a valid path, prompt the user
read -p "📂 Enter the backup folder path (or press Enter to use the current working directory): " input_backup_folder
if [ -z "$input_backup_folder" ]; then
# If the user didn't provide a path, use the current working directory
backup_folder="$PWD"
else
# Validate the provided path and create the folder if it doesn't exist
backup_folder="$input_backup_folder"
if [ ! -d "$backup_folder" ]; then
mkdir -p "$backup_folder"
fi
fi
# Save the configured path to the configuration file
echo "backup_folder=\"$backup_folder\"" > "$config_file"
}
# Function to create a new owner, tablespace, and database on a remote server
create_remote_database() {
local superuser_name="$1"
local superuser_password="$2"
local owner_name=""
local owner_password=""
local tablespace_name=""
local tablespace_path=""
local db_name=""
local db_host="$7"
local db_port="$8"
# Authenticate the superuser and store the authentication token
local auth_token
auth_token=$(psql -U "$superuser_name" -d postgres -c "SELECT 1" -w -q -h "$db_host" -p "$db_port" -W "$superuser_password" 2>&1)
if [[ "$auth_token" == *"authentication failed"* ]]; then
echo "❌ Authentication as superuser '$superuser_name' failed. Exiting."
exit 1
else
echo "✅ Successfully authenticated as superuser '$superuser_name'."
fi
# Display existing tablespaces, their paths, and sizes
echo "📂 Existing tablespaces and their details:"
echo "+----------------------+----------------------+------------------------------------------------------+----------------------+"
echo "| DATABASE_NAME | TABLESPACE_NAME | TABLESPACE_PATH | TABLESPACE_SIZE |"
echo "+----------------------+----------------------+------------------------------------------------------+----------------------+"
psql -U "$superuser_name" -d postgres -t -A -F '|' -c "SELECT d.datname, t.spcname, pg_tablespace_location(t.oid), COALESCE(pg_size_pretty(pg_tablespace_size(t.oid)), '0 bytes') FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid;" -h "$db_host" -p "$db_port" | while IFS='|' read -r dbname tablespace_name tablespace_path tablespace_size; do
printf "| %-20s | %-20s | %-50s | %-20s |\n" "$dbname" "$tablespace_name" "$tablespace_path" "$tablespace_size"
done
echo "+----------------------+----------------------+------------------------------------------------------+----------------------+"
# Set the PGPASSWORD environment variable
export PGPASSWORD="$superuser_password"
for attempt in {1..3}; do
# Prompt for the database name
while true; do
read -p "📝 Enter the name of the database: " db_name
db_name=$(echo "$db_name" | tr -d ' ') # Remove spaces
if [ -z "$db_name" ]; then
echo "❌ Database name cannot be empty. Please enter a valid name."
else
break # Exit the loop if a valid database name is provided
fi
done
# Prompt for the owner (role) name
while true; do
read -p "📝 Enter the name of the owner (role) for the database: " owner_name
owner_name=$(echo "$owner_name" | tr -d ' ') # Remove spaces
if [ -z "$owner_name" ]; then
echo "❌ Owner (role) name cannot be empty. Please enter a valid name."
elif psql -U "$superuser_name" -d postgres -t -c "SELECT 1 FROM pg_roles WHERE rolname='$owner_name'" | grep -q "1"; then
echo "❗ Database owner '$owner_name' already exists."
read -p "Do you want to use the existing role for database creation? (y/n): " use_existing_role
if [ "$use_existing_role" = "y" ]; then
# Proceed with database creation using the existing role
echo "✅ Using the existing role '$owner_name' for database creation."
break # Exit the loop
fi
else
break # Exit the loop if a valid owner name is provided
fi
done
# Prompt for the owner (role) password
while true; do
read -sp "🔒 Enter the password for the owner (role) of the database: " owner_password
if [ -z "$owner_password" ]; then
echo "❌ Owner (role) password cannot be empty. Please enter a valid password."
else
break # Exit the loop if a valid password is provided
fi
done
# Check if the owner (role) already exists
if psql -U "$superuser_name" -d postgres -t -c "SELECT 1 FROM pg_roles WHERE rolname='$owner_name'" | grep -q "1"; then
echo "✅ Using the existing role '$owner_name' for database creation."
else
# Create the owner with the provided password
if createuser -U "$superuser_name" -P -e "$owner_name" < <(echo "$owner_password"); then
echo "✅ New database owner '$owner_name' created."
else
echo "❌ Error creating the database owner '$owner_name'."
return
fi
fi
# Prompt for the tablespace name
while true; do
read -p "📝 Enter the name for the new tablespace: " tablespace_name
# Check if the tablespace name exists
if ! psql -U "$superuser_name" -d postgres -t -c "SELECT 1 FROM pg_tablespace WHERE spcname='$tablespace_name'" | grep -q "1"; then
break
else
read -p "Tablespace name '$tablespace_name' already exists. Do you want to use the existing name? (y/n): " use_existing_name
if [ "$use_existing_name" = "n" ]; then
continue
else
break
fi
fi
done
# Prompt for the tablespace path
while true; do
read -p "📝 Enter the path for the tablespace: " tablespace_path
# Ask whether the tablespace path is on the local or remote server
read -p "Is the tablespace path for the local server (y) or remote server(n)? (y/n): " is_local_path
if [ "$is_local_path" = "n" ]; then
# Check if the directory exists on the remote server
if ssh -T "$db_host" "[ -d '$tablespace_path' ]"; then
echo "📂 Tablespace path '$tablespace_path' already exists on the remote server."
continue # Continue the loop to ask for a different path
else
# If it doesn't exist, create the remote path
if ssh -T "$db_host" "mkdir -p '$tablespace_path'"; then
echo "📂 Tablespace path '$tablespace_path' created on the remote server."
else
echo "❌ Error creating the tablespace path '$tablespace_path' on the remote server. Please check permissions or choose a different path."
continue
fi
fi
else
# Check if the directory exists locally
if [ -d "$tablespace_path" ]; then
echo "📂 Tablespace path '$tablespace_path' already exists locally."
continue # Continue the loop to ask for a different path
else
# If it doesn't exist, create the local path
if mkdir -p "$tablespace_path"; then
echo "📂 Tablespace path '$tablespace_path' created locally."
else
echo "❌ Error creating the tablespace path '$tablespace_path' locally. Please check permissions or choose a different path."
continue
fi
fi
fi
break # Exit the loop if the path is created successfully or already exists
done
if psql -U "$superuser_name" -d postgres -c "CREATE TABLESPACE $tablespace_name LOCATION '$tablespace_path';" -w -q -h "$db_host" -p "$db_port" -W "$superuser_password"; then
echo "✅ Tablespace '$tablespace_name' created."
else
echo "❌ Error creating the tablespace."
fi
# Create the database with custom options
if createdb -U "$superuser_name" -O "$owner_name" -e -T template0 -E UTF8 -D "$tablespace_name" -h "$db_host" -p "$db_port" "$db_name"; then
echo "✅ New database '$db_name' created."
break # Exit the loop if database creation is successful
else
echo "❌ Error creating the database."
fi
# Unset the PGPASSWORD environment variable to clear the password
unset PGPASSWORD
done
# Exit the function if the database creation was successful
return
}
# Example usage of the create_database function:
# create_database "superuser_name" "superuser_password" "owner_name" "owner_password" "/path/to/tablespace" "db_name"
# Function to drop a PostgreSQL database
drop_database() {
local superuser_name="$1"
local superuser_password="$2"
local db_host="$3"
local db_port="$4"
# Set the PGPASSWORD environment variable
export PGPASSWORD="$superuser_password"
if psql -U "$superuser_name" -d postgres -c "SELECT 1" -w -q -h "$db_host" -p "$db_port" -W "$superuser_password"; then
echo "✅ Successfully authenticated as superuser '$superuser_name'."
# Prompt the user for the database name to drop
read -p "📝 Enter the name of the database you want to drop: " db_name
# Check if the entered database name exists
if psql -U "$superuser_name" -d postgres -t -c "SELECT 1 FROM pg_database WHERE datname = '$db_name'" -w -q -h "$db_host" -p "$db_port" -W | grep -q "1"; then
echo "✅ Database '$db_name' exists."
# List all available databases with proper formatting
echo "+----------------------+---------+"
echo "| DATABASE_NAME | SIZE_GB |"
echo "+----------------------+---------+"
psql -U "$superuser_name" -d postgres -t -A -F '|' -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;" -h "$db_host" -p "$db_port" | while IFS='|' read -r dbname size_gb; do
printf "| %-20s | %-7s |\n" "$dbname" "$size_gb"
done
echo "+----------------------+---------+"
# Prompt for confirmation
read -p "🚧 Are you sure you want to drop the database '$db_name'? (y/n): " confirmation
if [ "$confirmation" = "y" ]; then
# Check the backup folder
check_backup_folder
# List available backups for the database
echo "📂 Available backups for database '$db_name':"
backups=("$backup_folder"/*"$db_name"*)
if [ ${#backups[@]} -eq 0 ]; then
echo "No backups found for database '$db_name'."
else
for backup in "${backups[@]}"; do
if [ -f "$backup" ] && [[ "${backup##*.}" != "log" ]]; then
echo "File: $backup"
elif [ -d "$backup" ]; then
echo "Directory: $backup"
fi
done
fi
# Ask for confirmation before dropping
read -p "🚧 Do you want to proceed with dropping the database? (y/n): " drop_confirmation
if [ "$drop_confirmation" = "y" ]; then
# Drop the database
if dropdb -U "$superuser_name" -h "$db_host" -p "$db_port" "$db_name"; then
echo "✅ Database '$db_name' dropped successfully."
else
echo "❌ Error dropping the database '$db_name'."
fi
else
echo "❌ Database drop aborted."
fi
else
echo "❌ Database drop aborted."
fi
else
echo "❌ Database '$db_name' does not exist."
# Ask the user if they want to check for backups in the backup repository
read -p "🚧 Do you want to check for backups in the backup repository for '$db_name'? (y/n): " backup_check
if [ "$backup_check" = "y" ]; then
# Check the backup folder
check_backup_folder
# List available backups for the entered database name
echo "📂 Available backups for database '$db_name':"
backups=("$backup_folder"/*"$db_name"*)
if [ ${#backups[@]} -eq 0 ]; then
echo "No backups found for database '$db_name'."
else
for backup in "${backups[@]}"; do
if [ -f "$backup" ] && [[ "${backup##*.}" != "log" ]]; then
echo "File: $backup"
elif [ -d "$backup" ]; then
echo "Directory: $backup"
fi
done
fi
fi
fi
# Unset the PGPASSWORD environment variable to clear the password
unset PGPASSWORD
else
echo "❌ Authentication as superuser '$superuser_name' failed. Exiting."
exit 1
fi
}
# Function to list all PostgreSQL databases and their sizes
list_database_sizes() {
local superuser_name="$1"
local superuser_password="$2"
local db_host="$3"
local db_port="$4"
# Set the PGPASSWORD environment variable
export PGPASSWORD="$superuser_password"
if psql -U "$superuser_name" -d postgres -c "SELECT 1" -w -q -h "$db_host" -p "$db_port" -W "$superuser_password"; then
echo "✅ Successfully authenticated as superuser '$superuser_name'."
# List all available databases with proper formatting
echo "+----------------------+---------+"
echo "| DATABASE_NAME | SIZE_GB |"
echo "+----------------------+---------+"
psql -U "$superuser_name" -d postgres -t -A -F '|' -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;" -h "$db_host" -p "$db_port" | while IFS='|' read -r dbname size_gb; do
printf "| %-20s | %-7s |\n" "$dbname" "$size_gb"
done
echo "+----------------------+---------+"
# Unset the PGPASSWORD environment variable to clear the password
unset PGPASSWORD
else
echo "❌ Authentication as superuser '$superuser_name' failed. Exiting."
exit 1
fi
}
# Function to display the merits and demerits of each backup and restoration mode
display_backup_readme() {
# Define ANSI escape codes for text color
BOLD='\033[1m'
RED='\033[1;31m'
GREEN='\033[1;32m'
YELLOW='\033[1;33m'
RESET='\033[0m' # Reset color and style to default
# Define emojis for headings and list items
HEADING_EMOJI="📋📋"
MERITS_EMOJI="🌟🌟"
DEMERITS_EMOJI="🚫🚫"
echo -e "${BOLD}${YELLOW}${HEADING_EMOJI} We will provide you with an overview of the merits and demerits for each mode of backup and restoration in PostgreSQL.${RESET}\n"
echo -e "${BOLD}${GREEN}${HEADING_EMOJI} Custom Format Backup and Restoration:${RESET}"
echo -e "${BOLD}${YELLOW}${MERITS_EMOJI} Merits:${RESET}"
echo "- Customizable Backup: Select specific database objects or schemas for backup, providing fine-grained control."
echo "- Small Backup Size: Custom backups tend to be smaller compared to plain SQL text backups."
echo "- Compression: Backup files can be compressed, saving storage space."
echo -e "${BOLD}${YELLOW}${DEMERITS_EMOJI} Demerits:${RESET}"
echo "- Not Human-Readable: Custom backups are not human-readable, making inspection harder."
echo "- Dependency on pg_dump: Requires the pg_dump tool for creating custom backups."
echo "- No Parallel Backup: Does not allow parallel backup and restore operations for improved performance on multi-core systems."
echo -e "${BOLD}${GREEN}${HEADING_EMOJI} Plain SQL Text Backup and Restoration:${RESET}"
echo -e "${BOLD}${YELLOW}${MERITS_EMOJI} Merits:${RESET}"
echo "- Human-Readable: SQL text backups are plain text files, making them easy to read and edit."
echo "- Portability: Move SQL text backups between different PostgreSQL installations easily."
echo "- No External Tools: No external tools are required for creating or restoring plain SQL text backups."
echo -e "${BOLD}${YELLOW}${DEMERITS_EMOJI} Demerits:${RESET}"
echo "- Large File Size: SQL text backups are usually larger in size compared to custom format backups."
echo "- Slower Restore: Restoring from SQL text backups can be slower due to SQL command execution."
echo "- No Parallel Backup: Does not allow parallel backup and restore operations."
echo -e "${BOLD}${GREEN}${HEADING_EMOJI} Directory Format Backup and Restoration:${RESET}"
echo -e "${BOLD}${YELLOW}${MERITS_EMOJI} Merits:${RESET}"
echo "- Parallel Backup and Restoration: Directory format backups support parallel processing, suitable for large databases."
echo "- Human-Readable: Metadata files (Table of Contents) are in plain text format, making it easier to understand the backup structure."
echo "- Optimized for Large Databases: Suitable for very large databases where custom format backups may be impractical."
echo -e "${BOLD}${YELLOW}${DEMERITS_EMOJI} Demerits:${RESET}"
echo "- Larger Backup Size: Directory format backups typically consume more storage space compared to custom format backups."
echo "- Complex Directory Structure: Directory format backups have a complex directory structure, which might be challenging to manage."
echo -e "It's essential to choose the backup and restoration method based on your specific use case, considering factors like database size, performance requirements, and ease of management."
}
# Main menu with aligned and colored options
while true; do
echo -e "▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬" # Add an empty line
echo -e "🔻\e[1;97mSelect an operation for the database:\e[0m 🔻"
echo -e "▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬" # Add an empty line
echo # Add an empty line
echo -e "\e[1;34m1. \e[0m\e[97m🏢 Create a New Database"
echo -e "\e[1;35m2. 📦 \e[0m\e[97mPerform Backup \e[38;5;162m[Custom Format] "
echo -e "\e[1;35m3. 📦 \e[0m\e[97mPerform Backup \e[38;5;162m[Plain SQL Format] "
echo -e "\e[1;35m4. 📦 \e[0m\e[97mPerform Backup \e[38;5;162m[Directory Format] "
echo -e "\e[1;36m5. 🔄 \e[0m\e[97mPerform Restore \e[38;5;45m[Custom Format] "
echo -e "\e[1;33m6. 🔄 \e[0m\e[97mPerform Restore \e[38;5;220m[Plain SQL Format] "
echo -e "\e[1;33m7. 🔄 \e[0m\e[97mPerform Restore \e[38;5;220m[Directory Format] "
echo -e "\e[1;31m8. \e[0m\e[97m🔥 Drop a Database"
echo -e "\e[1;31m9. \e[0m\e[97m🧹 Backup Housekeeping"
echo -e "\e[1;32m10. \e[0m\e[97m📊 List Databases and Sizes"
echo -e "\e[1;32m11. \e[0m\e[97m📊 About This Tool"
echo -e "\e[1;31m12. \e[0m\e[97m🚪 Exit"
read -p $'\e[1;32mEnter your choice:\e[0m ' operation
case "$operation" in
1)
# Prompt for the superuser's password
read -sp "🔐 Enter the password for the superuser 'postgres': " superuser_password
echo # Add a newline after password input
# Validate superuser password
if [ -z "$superuser_password" ]; then
echo "❌ Superuser password cannot be empty. Please enter a valid password."
continue # Restart the loop to re-enter the password
fi
# Prompt for database name and user
read -p "📝 Enter the name of the database: " db_name
read -p "👤 Enter the name of the owner (role) for the database: " db_user
# Validate database name and user
if [ -z "$db_name" ] || [ -z "$db_user" ]; then
echo "❌ Database name and owner (role) name cannot be empty. Please enter valid names."
continue # Restart the loop to re-enter the names
fi
read -sp "🔐 Enter the password for the owner (role) of the database: " db_password
echo # Add a newline after password input
# Validate database owner password
if [ -z "$db_password" ]; then
echo "❌ Database owner password cannot be empty. Please enter a valid password."
continue # Restart the loop to re-enter the password
fi
create_remote_database "$superuser_name" "$superuser_password" "$owner_name" "$owner_password" "$tablespace_path" "$db_name" "$db_host" "$db_port"
;;
2)
perform_backup_custom
;;
3)
perform_backup_plain
;;
4)
perform_backup_directory
;;
5)
perform_restore_custom
;;
6)
perform_restore_plain
;;
7)
perform_restore_directory
;;
8)
# Prompt for the superuser's password
read -sp "🔐 Enter the password for the superuser 'postgres': " superuser_password
echo # Add a newline after password input
# Validate superuser password
if [ -z "$superuser_password" ]; then
echo "❌ Superuser password cannot be empty. Please enter a valid password."
continue # Restart the loop to re-enter the password
fi
drop_database "postgres" "$superuser_password" "$db_host" "$db_port"
;;
9)
# Call the backup housekeeping function here
manage_backup_folders
;;
10)
list_database_sizes "$superuser_name" "$superuser_password" "$db_host" "$db_port" # Call the list_database_sizes function
;;
11)
# Call About This Tool
display_backup_readme
;;
12)
echo "🚪 Exiting the script."
exit 0
;;
*)
echo "❌ Invalid choice."
;;
esac
done
# Set permissions for the history log file to read-only for everyone
chmod 644 $backup_folder/.Backup_Restore_history.log
# End of script
