Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL - Rename database

Tags:

sql

postgresql

People also ask

How do I rename an existing database?

In Object Explorer, expand Databases, right-click the database to rename, and then select Rename. If the database was your default database, see Reset your default database after rename.

What is rename database?

So, the Rename Database statement in SQL is used to change the name of the existing database. Sometimes, the Rename Database statement is used because the developers think that the original name is not more relevant to the data of the database, or they want to give a temporary name to that database.


Try not quoting the database name:

ALTER DATABASE people RENAME TO customers;

Also ensure that there are no other clients connected to the database at the time. Lastly, try posting the error message it returns so we can get a bit more information.


For future reference, you should be able to:

-- disconnect from the database to be renamed
\c postgres

-- force disconnect all other clients from the database to be renamed
SELECT pg_terminate_backend( pid )
FROM pg_stat_activity
WHERE pid <> pg_backend_pid( )
    AND datname = 'name of database';

-- rename the database (it should now have zero clients)
ALTER DATABASE "name of database" RENAME TO "new name of database";

Note that table pg_stat_activity column pid was named as procpid in versions prior to 9.2. So if your PostgreSQL version is lower than 9.2, use procpid instead of pid.


I just ran into this and below is what worked:

1) pgAdmin is one of the sessions. Use psql instead.
2) Stop the pgBouncer and/or scheduler services on Windows as these also create sessions


Unexist told me in comment to restart the database and it works! Restarting the database kill all existing connection and then I connect to an other database and was able to rename it with my initial query.

Thx all.


Instead of deploying a nuke (restarting the server) you should try to close those connections that bother you either by finding where are they from and shutting down the client processes or by using the pg_cancel_backend() function.