Managing a database in an Amazon Redshift cluster

Amazon Redshift consists of at least one database, and it is the highest level in the namespace hierarchy for the objects in the cluster. This recipe will guide you through the steps needed to create and manage a database in Amazon Redshift.

Getting ready

To complete this recipe, you will need the following:

• Access to any SQL interface such as a SQL client or query editor
• An Amazon Redshift cluster endpoint

How to do it…

Let's now set up and configure a database on the Amazon Redshift cluster. Use the SQL client to connect to the cluster and execute the following commands:

1. We will create a new database called qa in the Amazon Redshift cluster. To do this, use the following code:

   CREATE DATABASE qa
   WITH 
   OWNER awsuser 
   CONNECTION LIMIT 50; 

2. To view the details of the database, you will query the PG_DATABASE_INFO, as shown in the following code snippet:

   SELECT datname, datdba, datconnlimit 
   FROM pg_database_info
   WHERE datdba > 1;

   This is the expected output:

   datname datdba  datconnlimit
   qa 100 UNLIMITED

   This query will list the databases that exist in the cluster. If a database is successfully created, it will show up in the query result.

3. To make changes to the database—such as database name, owner, and connection limit—use the following command, replacing <qauser> with the respective Amazon Redshift username:

   /* Change database owner */
   ALTER DATABASE qa owner to <qauser>;
   /* Change database connection limit */
   ALTER DATABASE qa CONNECTION LIMIT 100;
   /* Change database name */
   ALTER DATABASE qa RENAME TO prod;

4. To verify that the changes have been successfully completed, you will query the system table pg_database_info, as shown in the following code snippet, to list all the databases in the cluster:

   SELECT datname, datdba, datconnlimit 
   FROM pg_database_info
   WHERE datdba > 1;

   This is the expected output:

   datname datdba datconnlimit
   prod 100 100

5. You can connect to the prod database using the connection endpoint, as follows:

   <RedshiftClusterHostname>:<Port>/prod

   Here, prod refers to the database you would like to connect to.

6. To delete the previously created database, execute the following query:

   DROP DATABASE prod;

   Important note

   It is best practice to have only one database in production per Amazon Redshift cluster. Multiple databases could be created in a development environment to enable separation of functions such a development/unit testing/quality assurance (QA). Within the same session, it is not possible to access objects across multiple databases, even though they are present in the same cluster. The only exception to this rule is database users and groups that are available across the databases.