Renaming a Database in Redshift

If you want to rename a database in Redshift, you might have tried something like this:

ALTER DATABASE oldname RENAME TO newname;


But you find you get an error because people or processes are still connected to it! Fear not, there are two methods to handle this:

1. Forcefully terminate the existing connections
2. Reboot the cluster

Terminating existing connections

If you want to terminate the existing connections, you first need to list them:

SELECT *
FROM STV_SESSIONS
WHERE user_name = 'rdsdb';


Next, you need to kill each process id:

SELECT pg_terminate_backend(<process>)
FROM pg_stat_activity
WHERE
-- Don't kill your own connection
procpid <> pg_backend_pid()
-- And don't kill connections to other databases
AND datname = 'oldname';


Now you should be able to rename the database:

ALTER DATABASE oldname RENAME TO newname;


Reboot the cluster

If the above doesn’t work and you’ve got the time, you can always just reboot the Redshift cluster. You can do this by:

1. Selecting your cluster in the AWS Console
2. Clicking the “Actions” dropdown box
3. Selecting “Reboot cluster”