2

We are using AWS RDS Postgres at the moment and are thinking of shifting to AWS Redshift.

Problem

We want to create two databases (1 for STAGING, 1 for PRODUCTION) under it but we came to know that we can only create ONE database under an instance.

Research

I found this answer which said that I can create multiple schemas and then simply set the default schema for roles that way I can virtually achieve different databases for PROD and QA and I will not have to change any of my queries. But while reading the developer guide, at page 382 I read that DATABASE ROLES is listed under Unsupported PostgreSQL Features.

Question

How can I achieve this functionality given my use case in AWS Redshift?

Community
  • 1
  • 1
saadi
  • 646
  • 6
  • 29

1 Answers1

1

With Redshift you can create multiple databases within one cluster. (max of around 60)

the command 

create database newdbname;

will do this.

Any one query can ONLY access one database at a time so correct permissions can be set up per database.

So, I think you have everything you need.

ref:

https://docs.aws.amazon.com/redshift/latest/dg/r_Privileges.html https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_DATABASE.html

Jon Scott
  • 4,144
  • 17
  • 29