PostgreSQL - 重命名数据库

最后发布: 2008-09-27 15:00:40


问题

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 PGAdmin screen that it has many PID but they are inactive... I do not see how to kill them. 我在PGAdmin屏幕中看到它有很多PID但它们处于非活动状态......我不知道如何杀死它们。

sql postgresql
回答

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. 请注意,表pg_stat_activitypid在9.2之前的版本中被命名为procpid So if your PostgreSQL version is lower than 9.2, use procpid instead of pid . 因此,如果您的PostgreSQL版本低于9.2,请使用procpid而不是pid


回答

I just ran into this and below is what worked: 我刚碰到这个,下面是有用的:

1) pdAdmin is one of the sessions. 1) pdAdmin是其中一个会话。 Use psql instead. 请改用psql
2) Stop the pgBouncer and/or scheduler services on Windows as these also create sessions 2)在Windows上停止pgBouncer和/或调度程序服务,因为它们也会创建会话


回答

Unexist told me in comment to restart the database and it works! Unexist在评论中告诉我重新启动数据库,它的工作原理! 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. 大家好。


回答

您应该尝试通过查找它们的位置并关闭客户端进程或使用pg_cancel_backend()函数来关闭那些困扰您的连接,而不是部署nuke(重新启动服务器)。