Pause Amazon Redshift Cluster on AWS Management Console for Cost Saving

With new enhancements on Amazon Redshift Data Warehouse AWS service, database administrators can pause Redshift cluster when it is not used and resume when the database is required again to save costs on this Amazon Web service. Especially if the Amazon Redshift database is in use with specific schedules and is redundant in other times, data warehouse administrators can now pause and resume to keep the money spent on Amazon Redshift service at minimum levels.

In this Redshift tutorial, I want to show how to pause Redshift cluster and resume again to bring the Amazon Redshift database back online.

pause Amazon Redshift data warehouse cluster and resume when needed for cost saving

Before data warehouse administrators pause Redshift database on AWS cloud, they can first check for active sessions and which SQL queries are being executed on those database sessions using following SQL SELECT statement.


select

 s.process,

 s.db_name,

 s.user_name,

 i.starttime,

 i.text as SQLquery

from stv_sessions s

left join pg_user u

 on u.usename = s.user_name

left join stl_connection_log c

 on c.pid = s.process

 and c.event = 'authenticated'

left join stv_inflight i

 on u.usesysid = i.userid

 and s.process = i.pid

where

 username <> 'rdsdb'

 and i.starttime is not null

order by i.starttime desc;

Code

Above SQL query result will display queries being executed on current Amazon Redshift cluster.

SQL query to display active sessions on Amazon Redshift database

For the list of active SQL queries, Amazon Redshift database administrators can use the Query monitoring tab for the target Amazon Redshift cluster. Under Query monitoring tab, you will see Query history which includes a section for Queries and loads running currently on the target Amazon Redshift database.

query monitoring tool on Amazon Redshift cluster

Queries and loads shows the list of active running SQL queries on Redshift database.

SQL queries and loads running actively on Amazon Redshift database

As Redshift data warehouse administrators can realize the PID column values includes the process id of the SQL query which can also be fetched by the SQL query above. Database query is listed in SQL column on the query table.

It is also possible to kill the SQL query by using the "Terminate query"
If you mark the checkbox next to the selected SQL query, the "Terminate query" button will be active.
If you click the terminate button, following warning message will be displayed

stop / kill / teminate a SQL query on Amazon Redshift database

If you decide to pause the Redshift cluster, select the cluster and using Actions menu option "Pause", you can pause the Amazon Redshift cluster

Pause Redshift cluster using Action menu

While pausing an Amazon Redshift cluster, there are 3 options possible:
Pause now:
Pause later: Redshift administrators either choose from a schedule or explitely define a datetime to pause the cluster.
Pause and resume on schedule: This option enables the Redshift data warehouse administrators to define a schedule or a cron syntax for defining the duration start and end times of paused state of the Redshift cluster.

pause options for Amazon Redshift cluster

For example, to pause the cluster immediately click on "Pause now" button

Here is the paused Redshift cluster. It took about 6 minutes to pause my 8 nodes cluster.

Amazon Redshift cluster in paused status

Administrators can reactivate or resume Redshift cluster again on the Amazon Redshift Dashboard on AWS Management Console using the Actions options.

resume Amazon Redshift cluster from paused state

After Resume action is selected, the administrators are asked to choose from available three options including immediate resume or a predefined time resuming option