SQL PostgreSQL 如何创建数据库或模式的副本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/808735/
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 how to create a copy of a database or schema?
提问by Jin Kim
Is there a simple way to create a copy of a database or schema in PostgreSQL 8.1?
有没有一种简单的方法可以在 PostgreSQL 8.1 中创建数据库或模式的副本？
I'm testing some software which does a lot of updates to a particular schema within a database, and I'd like to make a copy of it so I can run some comparisons against the original.
我正在测试一些对数据库中特定模式进行大量更新的软件，我想制作它的副本，以便可以与原始模式进行一些比较。
回答by Jordan S. Jones
If it's on the same server, you just use the CREATE DATABASE command with the TEMPLATE parameter. For example:
如果它在同一台服务器上，您只需使用带有 TEMPLATE 参数的 CREATE DATABASE 命令。例如：
CREATE DATABASE newdb WITH TEMPLATE olddb;
回答by Ali Raza Bhayani
If you have to copy the schema from the local database to a remote database, you may use one of the following two options.
如果您必须将模式从本地数据库复制到远程数据库，您可以使用以下两个选项之一。
Option A
选项 A
Copy the schema from the local database to a dump file.
pg_dump -U postgres -Cs database > dump_fileCopy the dump file from the local server to the remote server.
scp localuser@localhost:dump_file remoteuser@remotehost:dump_fileConnect to the remote server.
ssh remoteuser@remotehostCopy the schema from the dump file to the remote database.
psql -U postgres database < dump_file
将模式从本地数据库复制到转储文件。
pg_dump -U postgres -Cs database > dump_file将转储文件从本地服务器复制到远程服务器。
scp localuser@localhost:dump_file remoteuser@remotehost:dump_file连接到远程服务器。
ssh remoteuser@remotehost将模式从转储文件复制到远程数据库。
psql -U postgres database < dump_file
Option B
选项 B
Copy the schema directly from the local database to the remote database without using an intermediate file.
不使用中间文件，直接将模式从本地数据库复制到远程数据库。
pg_dump -h localhost -U postgres -Cs database | psql -h remotehost -U postgres database
This blog postmight prove helpful for you if you want to learn more about options for copying the database using pg_dump.
该博客文章，如果你想了解更多的选择使用复制数据库可能被证明对你有帮助pg_dump。
回答by Matthew Layne
This can be done by running the following command:
这可以通过运行以下命令来完成：
CREATE DATABASE [Database to create] WITH TEMPLATE [Database to copy] OWNER [Your username];
Once filled in with your database names and your username, this will create a copy of the specified database. This will work as long as there are no other active connections to the database you wish to copy. If there are other active connections you can temporarily terminate the connections by using this command first:
填写您的数据库名称和用户名后，这将创建指定数据库的副本。只要您希望复制的数据库没有其他活动连接，这就会起作用。如果还有其他活动连接，您可以先使用以下命令暂时终止连接：
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '[Database to copy]'
AND pid <> pg_backend_pid();
A good article that I wrote for Chartio's Data School which goes a bit more in depth on how to do this can be found here: https://dataschool.com/learn/how-to-create-a-copy-of-a-database-in-postgresql-using-psql
我为 Chartio 的数据学校写的一篇好文章，它更深入地介绍了如何做到这一点，可以在这里找到：https: //dataschool.com/learn/how-to-create-a-copy-of-a -database-in-postgresql-using-psql
