ShellScript で踏み台サーバ(EC2)経由で DB にアクセスして SQL を実行する方法
- POST
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