postgres-to-postgres-migration.sh

postgres-to-postgres-migration.sh

#!/usr/bin/env bash

echo "PostgreSQL Migration Script"
echo "============================"
echo "This script will migrate custom tables from the origin database to the destination database."
echo "Make sure you have the following files in the same directory as this script:"
echo "1. origin-creds.txt"
echo "2. destination-creds.txt"
echo ""
echo "These files should contain the database credentials in the following format:"
echo "username = your_username"
echo "password = your_password"
echo "host = your_host"
echo "port = your_port"
echo "database = your_database"
echo "sslmode = require"
echo ""

# function to parse credentials
parse_creds() {
    while IFS='=' read -r key value; do
        key=$(echo $key | tr '[:upper:]' '[:lower:]' | xargs)
        value=$(echo $value | xargs)
        case "$key" in
            username) username=$value ;;
            password) password=$value ;;
            host) host=$value ;;
            port) port=$value ;;
            database) database=$value ;;
            sslmode) sslmode=$value ;;
        esac
    done < "$1"
}

# function to validate credentials
validate_creds() {
    parse_creds "$1"
    if PGPASSWORD=$password psql -h $host -U $username -p $port -d $database -c '\q' 2>/dev/null; then
        echo "Connection to $1 database successful."
    else
        echo "Failed to connect to $1 database. Please check your credentials."
        exit 1
    fi
}

# validate credentials
echo "Validating credentials..."
validate_creds "origin-creds.txt"
validate_creds "destination-creds.txt"

echo "Press Enter to start the migration..."
read

# parse origin creds
parse_creds "origin-creds.txt"

# create filename with origin db name and current date
filename="${database}-$(date +%Y%m%d).sql"

# dump custom tables
PGPASSWORD=$password pg_dump -h $host -U $username -p $port -d $database --no-owner --no-acl -t 'public.*' > "$filename"

# parse destination creds
parse_creds "destination-creds.txt"

# import the dump
PGPASSWORD=$password psql -h $host -U $username -p $port -d $database < "$filename"

echo "migration complete. dump file: $filename"