CODEWITHSUNDEEP
sqlpostgresql
Patrick Desjardins
Patrick Desjardins

Reputation: 140753

PostgreSQL - Rename database

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

Upvotes: 197

Views: 161851

Answers (9)

user1551426
user1551426

Reputation: 31

from the default postgres db login with admin and then execute ALTER DATABASE old_database RENAME TO new_database; it will work.

Upvotes: 0

Somuchandra
Somuchandra

Reputation: 11

ALTER DATABASE old_database RENAME TO new_database;

old_database means already existing database. new_database means need to modify this name.

Example: ALTER DATABASE profile RENAME TO address;

Upvotes: 7

Valsaraj Viswanathan
Valsaraj Viswanathan

Reputation: 1567

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.

Upvotes: 3

rovyko
rovyko

Reputation: 4577

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.

Upvotes: 0

smoore4
smoore4

Reputation: 4866

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

Upvotes: 8

gsiems
gsiems

Reputation: 3790

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.

Upvotes: 134

Milen A. Radev
Milen A. Radev

Reputation: 62573

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.

Upvotes: 3

Patrick Desjardins
Patrick Desjardins

Reputation: 140753

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.

Upvotes: 3

bmdhacks
bmdhacks

Reputation: 16401

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.

Upvotes: 271

Related Questions