无缝更新 postgres 数据库 - 模式、重命名，如何？
其实是一个简单的问题，但我找不到任何好的结论性答案。
假设有一个生产数据库 foo_prd，以及同一 foo_new 的新版本（在同一服务器上），该版本应该替换旧版本。从 _prd 无缝切换到 _new 的最简洁方法是什么？
重命名数据库需要通过 pid 断开当前用户的连接。这将减少一些请求，新用户可能会在此过程中连接。我正在考虑将新数据库的表创建为不同的架构，然后更改 search_path，例如从 "$user",prd 更改为 "$user",new,prd。
可能会出现什么问题？您有更好的建议吗？我完全采取了错误的方法吗？
Actually a simple question, but I wasn't able to find any good conclusive answer.
Assuming a production database foo_prd, and a newer version of the same foo_new (on the same server) that is supposed to replace the old one. What is the cleanest way to seamlessly switch from _prd to _new?
RENAME-ing the databases would require to disconnect the current users via their pid. That would take down some requests, and new users might connect during the process. I was thinking of creating the tables of the new database as different SCHEMA and then change the search_path, e.g. from "$user",prd to "$user",new,prd.
What could possibly go wrong? Do you have any better suggestions? Am I taking the wrong approach altogether?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
按照您的建议进行操作：将新数据库的表创建为不同的架构，然后更改 search_path。
但还要创建一个与新架构同名的用户，并在更改 search_path 之前通过在每个应用程序中以该用户身份登录来测试所有内容 - 默认情况下，新架构将位于该用户的 search_path 中，因为名称匹配。
最后，当您删除旧模式时要小心 - 我建议首先重命名，以防任何内容使用限定引用（例如
prd.table或prd.function）。几天/几周后，就可以放心地放弃它了。Do as you suggest: create the tables of the new database as different schema and then change the search_path.
But also create a user with the same name as the new schema and test everything before changing the search_path by logging in as this user with each of your apps - the new schema will be first in that user's search_path by default because the name matches.
Finally, take care when you come to drop the old schema - I suggest renaming first in case anything refers to it's objects using a qualified reference (eg
prd.tableorprd.function). After a few days/weeks it can then be dropped with confidence.我会对我的架构进行版本控制，并在准备好后将我的应用程序更改为指向新架构。
I would version my schema, and change my app to point to the new schema when ready.