postgresql 在 postgres 中复制数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6502466/
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
Copy database in postgres
提问by Patton
I have a requirement in which I need to take a snapshot of a database and restore it in the same machine with some other predefined name in postgres. I have tried to achieve the above task with the following command.
我有一个要求，我需要拍摄数据库的快照，并在同一台机器上使用 postgres 中的其他一些预定义名称将其恢复。我已尝试使用以下命令完成上述任务。
CREATE DATABASE destniationDb TEMPLATE sourceDb;
But this option fails when the connection/session to the sourceDb exists.So I need to truncate this option as there is high possibility of user doing read operation. All command line options like restore_db,backup_db doest suit my requirement.Hence,I need some console command/function/store procedure to achieve it i.e, I need to connect to the database and call some command/function/store procedure that achieves this objective.
但是当与 sourceDb 的连接/会话存在时，此选项会失败。因此我需要截断此选项，因为用户进行读取操作的可能性很高。所有命令行选项，如 restore_db,backup_db 都不适合我的要求。因此，我需要一些控制台命令/函数/存储过程来实现它，即，我需要连接到数据库并调用一些实现此目标的命令/函数/存储过程.
Can anyone of you suggest some kind of solution to my requirement?
你们中的任何人都可以针对我的要求提出某种解决方案吗？
回答by nemesisfixx
Why don't you just create a dump of the existing database sourceDbusing the command
为什么不sourceDb使用命令创建现有数据库的转储
pg_dump sourceDb > destinationDb.sql
And the in this SQL dump destinationDb.sql, change the db name to the new one in the CREATE DATABASEline. After that, you can then create this new DB on the server using psqllike:
在此 SQL 转储中destinationDb.sql，将 db 名称更改为该CREATE DATABASE行中的新名称。之后，您可以使用以下命令在服务器上创建这个新数据库psql：
psql destinationDb < destinationDb.sql
回答by SpliFF
Have you tried locking the tablefirst?
你有没有试过先锁定桌子？
EDIT: I may have been oversimplifying. I was thinking if you lock writes to the tables you are copying the operation might work. But seems that isn't the case when cloning the whole db.
编辑：我可能过于简单化了。我在想，如果你锁定你正在复制的表的写入操作可能会起作用。但是在克隆整个数据库时似乎并非如此。
Going from the link you provided in your comment the database must have no active sessions. I solve this by simply restarting the postgres service immediately before the operation. If the script is fast enough your subsequent copy should run before new sessions can connect. I believe that postgres will wait up to 90 seconds for sessions to end so this solution should not be too disruptive.
从您在评论中提供的链接开始，数据库必须没有活动会话。我通过在操作前立即重新启动 postgres 服务来解决这个问题。如果脚本足够快，您的后续副本应该在新会话可以连接之前运行。我相信 postgres 会等待 90 秒让会话结束，所以这个解决方案不应该太具有破坏性。
