The RENAME DATABASE statement changes the name of a database.

Warning:

Database renames are not transactional. For more information, see Database renaming considerations.

Synopsis

ALTER DATABASE name RENAME TO name

Required privileges

Only members of the admin role can rename databases. By default, the root user belongs to the admin role.

Parameters

Parameter Description
name The first instance of name is the current name of the database. The second instance is the new name for the database. The new name must be unique and follow these identifier rules. You cannot rename a database if it is set as the current database or if sql_safe_updates = true.

Database renaming considerations

Database renames are not transactional

Database renames are not transactional. There are two phases during a rename:

  1. The system.namespace table is updated. This phase is transactional, and will be rolled back if the transaction aborts.
  2. The database descriptor (an internal data structure) is updated, and announced to every other node. This phase is not transactional. The rename will be announced to other nodes only if the transaction commits, but there is no guarantee on how much time this operation will take.
  3. Once the new name has propagated to every node in the cluster, another internal transaction is run that declares the old name ready for reuse in another context.

This yields a surprising and undesirable behavior: when run inside a BEGIN ... COMMIT block, it’s possible for a rename to be half-done - not persisted in storage, but visible to other nodes or other transactions. This violates A, C, and I in ACID. Only D is guaranteed: If the transaction commits successfully, the new name will persist after that.

This is a known limitation. For an issue tracking this limitation, see cockroach#12123.

Other limitations

It is not possible to rename a database if:

  • The database is referenced by a view. For more details, see View Dependencies.
  • The database is explicitly specified in a reference to a sequence. In this case, you can drop the column in the table that references the sequence, or you can modify the reference so that it does not specify the database name.

    For example, suppose you create a database db, and in that database, a sequence seq:

    copy
    icon/buttons/copy
    > CREATE DATABASE db;
      USE db;
      CREATE SEQUENCE seq;
    

    Then you reference the sequence in a table tab:

    copy
    icon/buttons/copy
    > CREATE TABLE tab (
      id UUID DEFAULT gen_random_uuid(),
      count INT DEFAULT nextval('db.seq')
    );
    

    Attempting to rename the database will result in an error:

    copy
    icon/buttons/copy
    > SET sql_safe_updates=false;
      ALTER DATABASE db RENAME TO mydb;
    
    ERROR: cannot rename database because relation "db.public.tab" depends on relation "db.public.seq"
    SQLSTATE: 2BP01
    HINT: you can drop the column default "count" of "db.public.seq" referencing "db.public.tab" or modify the default to not reference the database name "db"
    

    In order to rename the database db, you need to drop or change the reference in the default value for the seq column to not explicitly name the database db:

    copy
    icon/buttons/copy
    > ALTER TABLE tab ALTER COLUMN count SET DEFAULT nextval('seq');
    
    copy
    icon/buttons/copy
    > USE defaultdb;
      ALTER DATABASE db RENAME TO mydb;
    

Examples

Rename a database

copy
icon/buttons/copy
> CREATE DATABASE db1;
copy
icon/buttons/copy
> SHOW DATABASES;
  database_name
-----------------
  db1
  defaultdb
  movr
  postgres
  system
(5 rows)
copy
icon/buttons/copy
> ALTER DATABASE db1 RENAME TO db2;
copy
icon/buttons/copy
> SHOW DATABASES;
  database_name
-----------------
  db2
  defaultdb
  movr
  postgres
  system
(5 rows)

Rename fails (new name already in use)

copy
icon/buttons/copy
> ALTER DATABASE db2 RENAME TO movr;
ERROR: the new database name "movr" already exists
SQLSTATE: 42P04

See also



Yes No