PostgreSQL - Rename database

Daok_13913 0
Viewed 94469

I need to rename the database but when I do in PGAdmin : ALTER DATABASE "databaseName" RENAME TO "databaseNameOld" it told me that it cannot.

How can I do it?

(Version 8.3 on WindowsXP)

Update

  • The first error message : Cannot because I was connect to it. So I selected an other database and did the queries.

  • I get a second error message telling me that it has come user connect. I see in the PGAdmin screen that it has many PID but they are inactive... I do not see how to kill them.

postgresql sql
7 Answers

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.

bmdhacks_140320

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.

Daok_139130

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.

user_157850

For anyone running into this issue using DBeaver and getting an error message like this:

ERROR: database "my_stubborn_db" is being accessed by other users
  Detail: There is 1 other session using the database.

Disconnect your current connection, and reconnect to the same server with a connection that doesn't target the database you are renaming.

Changing the active database is not enough.

user_37694640

When connected via pgadmin, the default database will be postgres.

ALTER DATABASE postgres RENAME TO pgnew;

This will not work.

You need to right click on server in pgadmin and set Maintenance DB to some other DB and save. Then retry and it should work if no other connections exists.

user_25811740
Related