SQL PostgreSQL - 重命名数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/143756/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - Rename database
提问by Patrick Desjardins
I need to rename the database but when I do in
PGAdmin : ALTER DATABASE "databaseName" RENAME TO "databaseNameOld"it told me that it cannot.
我需要重命名数据库，但是当我这样做
PGAdmin : ALTER DATABASE "databaseName" RENAME TO "databaseNameOld"时告诉我它不能。
How can I do it?
我该怎么做？
(Version 8.3 on WindowsXP)
( WindowsXP 8.3 版)
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
PGAdminscreen that it has manyPIDbut they are inactive... I do not see how to kill them.
第一条错误消息：无法连接，因为我已连接到它。所以我选择了另一个数据库并进行了查询。
我收到第二条错误消息，告诉我它已进入用户连接。我在
PGAdmin屏幕上看到它有很多PID但它们处于非活动状态...我不知道如何杀死它们。
回答by bmdhacks
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.
还要确保当时没有其他客户端连接到数据库。最后，尝试发布它返回的错误消息，以便我们可以获得更多信息。
回答by gsiems
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_activitycolumn pidwas named as procpidin versions prior to 9.2. So if your PostgreSQL version is lower than 9.2, use procpidinstead of pid.
请注意，表pg_stat_activity列的pid名称procpid与 9.2 之前的版本相同。所以，如果你的PostgreSQL版本比9.2更低，使用procpid代替pid。
回答by smoore4
I just ran into this and below is what worked:
我刚刚遇到了这个，下面是有效的：
1) pgAdminis one of the sessions. Use psqlinstead.
2) Stop the pgBouncerand/or scheduler services on Windows as these also create sessions
1)pgAdmin是其中一个会话。使用psql来代替。
2) 停止pgBouncerWindows 上的和/或调度程序服务，因为它们也会创建会话
回答by Patrick Desjardins
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.
Unexist 在评论中告诉我重新启动数据库，它可以工作！重新启动数据库会杀死所有现有连接，然后我连接到另一个数据库并能够使用我的初始查询重命名它。
Thx all.
谢谢所有。
回答by Milen A. Radev
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.
您应该尝试关闭那些打扰您的连接，而不是部署 nuke（重新启动服务器），方法是查找它们来自何处并关闭客户端进程或使用该pg_cancel_backend()函数。
回答by rovyko
For anyone running into this issue using DBeaver and getting an error message like this:
对于使用 DBeaver 遇到此问题并收到如下错误消息的任何人：
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.
更改活动数据库是不够的。
