PostgreSQL - Rename database

Question

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.

You could elaborate on why it cannot. I just did it (on a different platform though) and it worked — Vinko Vrsalovic, Sep 27 '08 at 15:03
What is the exact error message and did you look up this error in the postgres docs? Maybe there's a perfect reason for that behaviour. Normally renaming should work. — unexist, Sep 27 '08 at 15:05
ok the tag and the title contain PostGreSQL since the beginning what is your point? — Patrick Desjardins, Sep 27 '08 at 16:19
What is the exact error message and did you look up this error in the postgres docs? Maybe there's a perfect reason for that behaviour. Normally renaming should work. If you have problems with pending or dead connections just restart the database to get rid of them. — unexist, Sep 27 '08 at 15:18

score 226 · Answer 1 · answered Sep 27 '08 at 15:03

226

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.

answered Sep 27 '08 at 15:03

bmdhacks

15,841
8
34
55

22

quoting is required when the name has capital letter in it. – Patrick Desjardins Sep 27 '08 at 15:09
8

This is not the case, but quoting is also required when the name has a `.` or `@`. – omar Jan 21 '15 at 16:32
7

Quoting is also required when the name contains a `-` – GreenTurtle Dec 21 '17 at 07:49
The above suggestions make me think it's better to keep table names restricted to just *lowercase letters* and *underscore* if possible! – Aswin Sanakan Feb 17 '20 at 06:52
Quoting is also required when the name contains empty space – Alvaro Rodriguez Scelza May 03 '20 at 00:46

score 114 · Answer 2 · edited Jan 16 '17 at 08:29

114

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.

edited Jan 16 '17 at 08:29

Mohayemin

3,841
4
25
54

answered Oct 06 '11 at 18:43

gsiems

3,500
1
22
24

3

it worked for me, thanx ! but the column name in pg_stat_activity is pid and not procpid. (`PostgreSQL 9.3.5 on x86_64-apple-darwin, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.9.00), 64-bit`) – 31 bit Feb 10 '15 at 17:09
The force disconnect code is extremely useful and should be in the accepted answer. – Jedidja Mar 08 '21 at 14:08

smoore4 · Answer 3 · 2019-12-11T16:11:20.390

8

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

edited Dec 11 '19 at 16:11

answered Aug 25 '13 at 15:29

smoore4

4,520
3
36
55

score 3 · Answer 4 · answered Sep 27 '08 at 15:16

3

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.

answered Sep 27 '08 at 15:16

Patrick Desjardins

136,852
88
292
341

score 3 · Answer 5 · answered Sep 27 '08 at 15:43

3

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.

answered Sep 27 '08 at 15:43

Milen A. Radev

60,241
22
105
110

score 2 · Answer 6 · answered Feb 20 '23 at 11:05

2

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;

answered Feb 20 '23 at 11:05

Somuchandra

11
2

score 1 · Answer 7 · answered Apr 05 '21 at 10:49

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.

score 0 · Answer 8 · answered Mar 06 '20 at 05:03

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.

score 0 · Answer 9 · answered Jun 08 '23 at 12:27

0

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

answered Jun 08 '23 at 12:27

user1551426

13
3

PostgreSQL - Rename database

9 Answers9

Linked