I am finding it difficult to manage and deploy schema changes to my Amazon Redshift database. Ideally, I would like is to have the code for my database under source control and use a schema comparison tool (like Redgate's Schema Compare or Microsoft's Sql Server Data Tools) to generate a delta script between the repository and the target db that can be used for deployment. However, I can't find anything out there that does this.
Would anyone out there like to share how they are managing and maintaining their redshift codebase?
1 Answer 1
In general there is very little tool support at present for Redshift. We do all of our automation and deployment via psql shell scripts and we manage the code base using git version control.
As far as schema changes we use a "migrations" approach (inspired by Rails) where we create ALTER TABLE ...ADD COLUMN and ALTER TABLE ...DROP COLUMN scripts to roll the database forward/back from one deployment to the next.
We also use 3 separate database on the same cluster for Dev/Test/Prod. We then specify the target database in the psql script using the -d option.
Hope that helps.