ShellScript で踏み台サーバ(EC2)経由で DB にアクセスして SQL を実行する方法について調査した結果を備忘録として残しておく

環境

  • OS: MacOS
  • DB: PostgreSQL

フォルダ構成

./
├── sql/
│    └── execute.sql
└── execute_sql.sh

実際のコード

環境変数(xxx 部分)は実際の環境に応じて書き換える

#!/bin/sh

# DEV
EC2_HOST_DEV=xxx
EC2_PEM_KEY_DEV=xxx
DB_HOST_DEV=xxx
DB_PORT_DEV=5432
DB_NAME_DEV=xxx
DB_USER_DEV=xxx
DB_PASSWORD_DEV=xxx

# STG
EC2_HOST_STG=xxx
EC2_PEM_KEY_STG=xxx
DB_HOST_STG=xxx
DB_PORT_STG=5432
DB_NAME_STG=xxx
DB_USER_STG=xxx
DB_PASSWORD_STG=xxx

# PRD
EC2_HOST_PRD=xxx
EC2_PEM_KEY_PRD=xxx
DB_HOST_PRD=xxx
DB_PORT_PRD=5432
DB_NAME_PRD=xxx
DB_USER_PRD=xxx
DB_PASSWORD_PRD=xxx

# Shared
SQL_DIR=sql
SQL_FILE=${SQL_DIR}/execute.sql
LOG_FILE=log/batch.log

read -p "Which environment connect to? (dev/stg/prd): " input_environment

# Convert to uppercase
environment=$(echo "${input_environment}" | tr '[:lower:]' '[:upper:]')

echo "【Start Processing】$(date "+%Y/%m/%d %H:%M:%S")" >> ${LOG_FILE} 2>&1

# Check the environment
case "${input_environment}" in
  "dev" | "stg" | "prd")
    echo "Connecting to ${environment} environment..." >> ${LOG_FILE} 2>&1

    EC2_HOST=$(eval "echo \${EC2_HOST_${environment}}")
    EC2_PEM_KEY=$(eval "echo \${EC2_PEM_KEY_${environment}}")
    DB_HOST=$(eval "echo \${DB_HOST_${environment}}")
    DB_PORT=$(eval "echo \${DB_PORT_${environment}}")
    DB_NAME=$(eval "echo \${DB_NAME_${environment}}")
    DB_USER=$(eval "echo \${DB_USER_${environment}}")
    DB_PASSWORD=$(eval "echo \${DB_PASSWORD_${environment}}");;
  *)
    echo "Please enter the correct environment."
    exit;;
esac

scp -i ${EC2_PEM_KEY} -r ${SQL_DIR} ${EC2_HOST}:~/

ssh -i ${EC2_PEM_KEY} -T ${EC2_HOST} <<EOF >> ${LOG_FILE} 2>&1
# Set DB Password
export PGPASSWORD=${DB_PASSWORD}

# Execute SQL
psql -p ${DB_PORT} -h ${DB_HOST} -d ${DB_NAME} -U ${DB_USER} -f ${SQL_FILE}

# Remove SQL
rm -rf sql
EOF