I know there are two ways of making a copy of a database.
我知道有两种方法可以复制数据库。
One is to export the database as a giant SQL file, then load it as a separate database:
一种是将数据库导出为巨型SQL文件,然后将其作为单独的数据库加载:
pg_dump <database> | psql <new database>
Another way is to pass the database name as a template to a database creation argument:
另一种方法是将数据库名称作为模板传递给数据库创建参数:
createdb -T <database> <new database>
What is the difference between these two methods, if any? Are there any benefits of using one over another, such as performance?
这两种方法有什么区别,如果有的话?使用一个在另一个上有什么好处,比如性能?
2 个解决方案
#1
2
According to the current docs
根据目前的文档
Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose "COPY DATABASE" facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes.
虽然可以通过将其名称指定为模板来复制除template1之外的数据库,但这还不是(通常)用作通用“COPY DATABASE”工具。主要限制是在复制模板数据库时不能将其他会话连接到模板数据库。如果启动时存在任何其他连接,则CREATE DATABASE将失败;否则,在CREATE DATABASE完成之前,将锁定与模板数据库的新连接。
Apart from that mild warning, which goes back to at least version 8.2, you can make certain kinds of changes by using createdb--things like changing collation, encoding, etc. (Within limits.)
除了可以追溯到至少版本8.2的温和警告之外,您还可以使用createdb进行某些更改 - 例如更改排序规则,编码等。(在限制范围内)。
Personally, I'd have a hard time justifying the use of createdb, which takes a full database lock, to copy a production database.
就个人而言,我很难证明使用完全数据库锁的createdb来复制生产数据库。
I think the other main difference is that "dump and load" is a fully supported way of copying a database. Also, you can carry a copy of the dump to an isolated development computer for testing if you need to. (The createdb utility has to have access to both the source and the target at the same time.) But I have not used createdb to make copies, so I could be wrong.
我认为另一个主要区别是“转储和加载”是一种完全支持的复制数据库的方式。此外,您可以将转储的副本携带到隔离的开发计算机,以便在需要时进行测试。 (createdb实用程序必须同时访问源和目标。)但我没有使用createdb来制作副本,所以我可能错了。
#2
4
Using CREATE DATABASE/createdb with a template makes a directory copy, whereas pg_dump + psql has to serialize and deserialize the whole database, send them on a round-trip to the client, and has to run everything through the transaction and write-ahead logging machinery. So the former method should be much faster.
将CREATE DATABASE / createdb与模板一起使用会生成目录副本,而pg_dump + psql必须序列化和反序列化整个数据库,将它们发送到客户端,并且必须通过事务和预写日志记录运行所有内容机械。所以前一种方法应该快得多。
The disadvantage is that CREATE DATABASE locks the template database while it's being copied. So if you want to create copies of a live database, that won't work so well. But if you want to quickly make copies of an inactive/template database, then using CREATE DATABASE is probably the right solution.
缺点是CREATE DATABASE在复制模板数据库时将其锁定。因此,如果要创建实时数据库的副本,那将无法正常工作。但是,如果您想快速制作非活动/模板数据库的副本,那么使用CREATE DATABASE可能是正确的解决方案。
#1
2
According to the current docs
根据目前的文档
Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose "COPY DATABASE" facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes.
虽然可以通过将其名称指定为模板来复制除template1之外的数据库,但这还不是(通常)用作通用“COPY DATABASE”工具。主要限制是在复制模板数据库时不能将其他会话连接到模板数据库。如果启动时存在任何其他连接,则CREATE DATABASE将失败;否则,在CREATE DATABASE完成之前,将锁定与模板数据库的新连接。
Apart from that mild warning, which goes back to at least version 8.2, you can make certain kinds of changes by using createdb--things like changing collation, encoding, etc. (Within limits.)
除了可以追溯到至少版本8.2的温和警告之外,您还可以使用createdb进行某些更改 - 例如更改排序规则,编码等。(在限制范围内)。
Personally, I'd have a hard time justifying the use of createdb, which takes a full database lock, to copy a production database.
就个人而言,我很难证明使用完全数据库锁的createdb来复制生产数据库。
I think the other main difference is that "dump and load" is a fully supported way of copying a database. Also, you can carry a copy of the dump to an isolated development computer for testing if you need to. (The createdb utility has to have access to both the source and the target at the same time.) But I have not used createdb to make copies, so I could be wrong.
我认为另一个主要区别是“转储和加载”是一种完全支持的复制数据库的方式。此外,您可以将转储的副本携带到隔离的开发计算机,以便在需要时进行测试。 (createdb实用程序必须同时访问源和目标。)但我没有使用createdb来制作副本,所以我可能错了。
#2
4
Using CREATE DATABASE/createdb with a template makes a directory copy, whereas pg_dump + psql has to serialize and deserialize the whole database, send them on a round-trip to the client, and has to run everything through the transaction and write-ahead logging machinery. So the former method should be much faster.
将CREATE DATABASE / createdb与模板一起使用会生成目录副本,而pg_dump + psql必须序列化和反序列化整个数据库,将它们发送到客户端,并且必须通过事务和预写日志记录运行所有内容机械。所以前一种方法应该快得多。
The disadvantage is that CREATE DATABASE locks the template database while it's being copied. So if you want to create copies of a live database, that won't work so well. But if you want to quickly make copies of an inactive/template database, then using CREATE DATABASE is probably the right solution.
缺点是CREATE DATABASE在复制模板数据库时将其锁定。因此,如果要创建实时数据库的副本,那将无法正常工作。但是,如果您想快速制作非活动/模板数据库的副本,那么使用CREATE DATABASE可能是正确的解决方案。