PG Dump and Restore between two easy panel services

pgsync.sh

#!/bin/bash

# Function to find the container ID of a service's first running task
find_container_id() {
    local service_name=$1
    docker service ps --filter "desired-state=running" --format "{{.ID}}" $service_name | head -n 1 | xargs -I {} docker inspect --format "{{.NodeID}} {{.Status.ContainerStatus.ContainerID}}" {} | awk '{print $2}'
}

# Function to extract the database name from the service name
extract_db_name() {
    local service_name=$1
    echo "${service_name%%_*}"
}

# Prompt for source and destination service names
read -p "Enter Source Database Service Name: " source_service
read -p "Enter Destination Database Service Name: " dest_service

# Find container IDs for source and destination services
source_container_id=$(find_container_id $source_service)
dest_container_id=$(find_container_id $dest_service)

# Extract database names from service names
source_db_name=$(extract_db_name $source_service)
dest_db_name=$(extract_db_name $dest_service)

# Database user
db_user="postgres"

# Check if the destination database contains data
table_count=$(docker exec $dest_container_id psql -U $db_user -d $dest_db_name -t -c "SELECT COUNT(*) FROM pg_tables WHERE schemaname = 'public';")
table_count=$(echo $table_count | xargs) # Trim whitespace

if [ "$table_count" -gt 0 ]; then
    echo "Warning: The destination database contains $table_count tables."

    # Ask for user confirmation to proceed
    read -p "Are you sure you want to continue and overwrite the data? (y/n): " confirmation
    if [[ $confirmation != "y" ]]; then
        echo "Operation aborted."
        exit 1
    fi

    # Disconnect all connections to the destination database
    docker exec $dest_container_id psql -U $db_user -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '$dest_db_name' AND pid <> pg_backend_pid();"

    # Drop and recreate the destination database with proper quoting for special characters
    if ! docker exec $dest_container_id psql -U $db_user -c "DROP DATABASE IF EXISTS \"$dest_db_name\";"; then
        echo "Error: Failed to drop the database."
        exit 1
    fi
    if ! docker exec $dest_container_id psql -U $db_user -c "CREATE DATABASE \"$dest_db_name\";"; then
        echo "Error: Failed to create the database."
        exit 1
    fi
fi

# Perform the data migration
if ! docker exec $source_container_id pg_dump -U $db_user -d $source_db_name | docker exec -i $dest_container_id psql -U $db_user -d $dest_db_name; then
    echo "Error: Data migration failed."
    exit 1
fi

echo "Data migration completed successfully."