Is it possible to Clone a RedShift Database?

For some test and development work we would like the ability to routinely clone existing RedShift databases (all schema and all data). Something like a backup-restore.

The best I can find starts by creating a snapshot of the entire cluster, which is not practical at this point in time.

We do have scripts for creating the database and the schema, but we don't have a simple/tidy mechanism for populating the tables from another AWS RedShfit database. (We've found that we can't use INSERT INTO db_new.schema.table SELECT * FROM db_old.schema.table as cross-database syntax is not supported)

Does anyone have any suggestions?

You can do the following:

• UNLOAD your data to S3 (http://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html)

• Run your deployment scripts against the new database

• COPY the data into the new database (http://docs.aws.amazon.com/redshift/latest/dg/r_COPY.html)

This (UNLOAD/COPY) will be fairly quick for a development size dataset.

StackExchange Database Administrators Q#55228, answer score: 5