Migrate from one to another AWS Redshift cluster

This week a wanted to test the migration from one to another cluster in a more flexible way than the Snapshot, restore, and resize way described in the tutorial to have more control. The tool pg_dump did not work, so I ended up using an EC2 migrator instance to unload to S3 and them loading back to the new cluster:

#!/bin/bash
set -e

TABLES=("TABLE_A" "TABLE_B")

# have your .pgpass file setup :)
RS_DB_ENDPOINT_FROM="rs-cluster-from.abcdefgh-123.us-west-5.redshift.amazonaws.com"
RS_DB_USER_FROM="from-user"

RS_DB_ENDPOINT_TO="rs-cluster-to.abcdefgh-123.us-west-5.redshift.amazonaws.com"
RS_DB_USER_TO="to-user"

RS_DB_PORT="5439"
RS_DB="rs-db"

MIGRATION_BUCKET="s3://my-migration-bucket/migration_dump"
MIGRATION_IAM_ROLE="arn:aws:iam::123456789:role/redshift-S3-migrator-from-to"

CMD_FROM="psql -h ${RS_DB_ENDPOINT_FROM} -p ${RS_DB_PORT} -U ${RS_DB_USER_FROM} -d ${RS_DB}"
CMD_TO="psql -h ${RS_DB_ENDPOINT_TO} -p ${RS_DB_PORT} -U ${RS_DB_USER_TO} -d ${RS_DB}"

# Helpers
unload_tables() {
SQL=$(cat <<-END
UNLOAD ('SELECT * FROM ${1}')
FROM '${MIGRATION_BUCKET}/${1}_'
IAM_ROLE '${MIGRATION_IAM_ROLE}'
ALLOWOVERWRITE
MANIFEST
DELIMITER '|';
END
)
echo "$SQL" | tr '\n' ' '
}

load_tables() {
SQL=$(cat <<-END
COPY ${1}
FROM '${MIGRATION_BUCKET}/${1}_manifest'
IAM_ROLE '${MIGRATION_IAM_ROLE}'
MANIFEST
DELIMITER '|';
END
)
echo "$SQL" | tr '\n' ' '
}

# Main
## dump to s3
for t in "${TABLES[@]}"; do
    echo "dump '$t' start: $(date '+%Y%m%d_%H:%M:%S')"
    ${CMD_FROM} -c "$(unload_tables $t)"
    echo "dump '$t' end:  $(date '+%Y%m%d_%H:%M:%S')"
done

## setup new tables
echo "setup: $(date '+%Y%m%d_%H:%M:%S')"
${CMD_TO} -f create_roles.sql
${CMD_TO} -f create_tables.sql

## load to new Redshift
for t in "${TABLES[@]}"; do
    echo "load '$t' start: $(date '+%Y%m%d_%H:%M:%S')"
    ${CMD_TO} -c "$(load_tables $t)"
    echo "load '$t' end:  $(date '+%Y%m%d_%H:%M:%S')"
done

The thing that I generally learned during this migration:

  • it is always a useful feature to have metrics in place
  • Much stuff can be solved in bash already. If you reach to its limits, then consider a language like go or ruby.

© 2021