Trying to rename a database in Redshift cluster

3
votes

I'm trying to rename a database in my Redshift cluster.

You cannot rename the database when you're connected to it so I've created a temporary database, reconnected with SQL Workbench to the temporary db and issued:

ALTER DATABASE olddb RENAME to newdb;

I get an error stating ERROR: database "olddb" is being accessed by other users [SQL State=55006]

I've checked who is connected and there appear to be some connections from user rdsdb to the database. I assume this is a service account that AWS Redshift use to perform maintenance tasks etc.

How can I rename the database when this superuser is connected?

Many thanks.

4
amazon-redshift
Does Redshift expose a rename tool in its web ui or api? Often Redshift is set up so the normal PostgreSQL way doesn't work and you need to do something a bit differently via the Amazon UI. – Craig Ringer
Nothing else that I can see in the docs, just ALTER DATABASE: docs.aws.amazon.com/redshift/latest/dg/r_ALTER_DATABASE.html – novabracket

4 Answers

4
votes

We can change the database name which is already created.

Detailed steps on how to do

  1. Connect to the old database and create a new database if you do not have another one already.

    create database databasebasename

    In this example, I will call the databasename as 'newdb'.

  2. Connect to newdb using connecting string as, jdbc:redshift://.us-east-1.redshift.amazonaws.com:8192/newdb, with the same password and username of your superuser (or the other eligible users as mentioned above).

  3. Now you can alter the database name. Substitute 'database_name_new' with the desired databasename.

alter database old-db-name rename to database_name_new;

If there are any active sessions, you'll have to kill them. To find the pid of active sessions:

select * from STV_SESSIONS where user_name='rdsdb';

Then to kill a session:

  SELECT 
       pg_terminate_backend(<pid>) 
    FROM 
       pg_stat_activity 
    WHERE 
       -- don't kill my own connection!
       procpid <> pg_backend_pid()
       -- don't kill the connections to other databases
       AND datname = '<old-db-name>';
  1. Once complete, you can connect back to that new database using the new name in the connection string as

jdbc:redshift://<cluser-id>.us-east-1.redshift.amazonaws.com:8192/database_name_new

  1. You can delete the temporary 'newdb'.

drop database databasebasename

3
votes

You cannot alter the name of (or delete!) the database that is created during the initial cluster creation. I don't believe this is mentioned in the docs but I've confirmed it with them.

1
votes

That's possible now -- I just renamed the database that was created during the initial cluster creation.

0
votes

We had a similar situation.

Step 1: Connect to the database which is not the one you are trying to rename. Check the same by executing SELECT CURRENT_DATABASE();.

Step 2: Execute the query below -

SELECT
    ss.*, 'select pg_terminate_backend('||process||');'
FROM
    stv_sessions ss
ORDER BY
    db_name;

The output of the query will have a column at the end with the select statements. Execute those to kill the sessions.

Step 3(Optional): If you are not the owner of the database try to modify the ownership of the database -

ALTER DATABASE <database to be renamed>
OWNER TO <user which is going to do the rename>;

Step 4: Rename the database