psql で Redshift のデータを CSV に出力する bash スクリプト

• redshift_query_diag.sh

#!/usr/bin/env bash

export LC_ALL=C
BASE_NAME=$(basename $0)
SCRIPT_BASE_NAME=$(basename $0 .sh)
CURRENT_DATE=`date '+%Y%m%d-%H%M%S'`
BASE_DIR=$(cd $(dirname $0);pwd)
cd $BASE_DIR

CLUSTER_ENDPOINT="<Redshiftクラスタのエンドポイント>"  # Redshift cluster endpoint for psql -h
DB_NAME="<データベース名>"          # Database name for psql -d
USER_NAME="<管理者ユーザー>"        # Username for psql -U

# Create and move to work directory
mkdir -p "redshift_query_diag_${DB_NAME}_${CURRENT_DATE}" && cd "redshift_query_diag_${DB_NAME}_${CURRENT_DATE}"

# Execute all queries
psql -v FETCH_COUNT=1000 -h "${CLUSTER_ENDPOINT}" -p 5439 -d "${DB_NAME}" -U "${USER_NAME}" << EOF

\pset format unaligned
\pset fieldsep '|'
\t off

\o SYS_QUERY_HISTORY.csv
SELECT * FROM SYS_QUERY_HISTORY;
-- SELECT * FROM SYS_QUERY_HISTORY where start_time  between  '2026-01-06' and '2026-01-12';


\o SVV_TABLE_INFO.csv
SELECT * FROM SVV_TABLE_INFO;
EOF

# Create zip archive
zip -r "redshift_query_diag_${DB_NAME}_${CURRENT_DATE}.zip" ./*