复制Postgresql数据库的更快方法（或最佳方法） [英] A faster way to copy a postgresql database (or the best way)
问题描述
我做了一个数据库的pg_dump，现在正尝试将生成的.sql文件安装到另一台服务器上。
I did a pg_dump of a database and am now trying to install the resulting .sql file on to another server.
我正在使用以下命令。 / p>
I'm using the following command.
psql -f databasedump.sql
我今天早些时候启动了数据库安装，现在7个小时后仍在填充数据库。我不知道这是否应该花费他多长时间，但我一直在监视它，到目前为止，我已经看到了12毫米以上的刀片并正在计数。我怀疑有更快的方法可以做到这一点。
I initiated the database install earlier today and now 7 hours later the database is still being populated. I don't know if this his how long it is supposed to take, but I continue to monitor it, so far I've seen over 12 millon inserts and counting. I suspect there's a faster way to do this.
推荐答案
使用
pg_dump -Fc -Z 9 --file=file.dump myDb
Fc
输出适合输入到pg_restore的自定义存档。这是最灵活的格式，因为它允许对装入数据以及对象定义进行重新排序。
Output a custom archive suitable for input into pg_restore. This is the most flexible format in that it allows reordering of loading data as well as object definitions. This format is also compressed by default.
Z 9：--compress = 0..9
指定要使用的压缩级别。零表示无压缩。对于自定义存档格式，此选项指定压缩单个表数据段，并且默认设置为中等压缩。对于纯文本输出，设置非零压缩级别会使整个输出文件被压缩，就好像它是通过gzip馈送的一样。但默认设置为不压缩。 tar存档格式目前根本不支持压缩。
Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support compression at all.
并使用
pg_restore -Fc -j 8 file.dump
-j：--jobs =工作数量
运行pg_restore中最耗时的部分-使用多个并发作业来加载数据，创建索引或创建约束的部分。此选项可以大大减少将大型数据库还原到运行在多处理器计算机上的服务器的时间。
Run the most time-consuming parts of pg_restore — those which load data, create indexes, or create constraints — using multiple concurrent jobs. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine.
每个作业是一个进程还是一个线程，具体取决于操作系统。 ，并使用与服务器的单独连接。
Each job is one process or one thread, depending on the operating system, and uses a separate connection to the server.
此选项的最佳值取决于服务器，客户端和网络的硬件设置。影响因素包括CPU内核数和磁盘设置。一个不错的起点是服务器上的CPU内核数量，但是在许多情况下，大于该数量的值也可以缩短还原时间。当然，值太高会由于抖动而导致性能降低。
The optimal value for this option depends on the hardware setup of the server, of the client, and of the network. Factors include the number of CPU cores and the disk setup. A good place to start is the number of CPU cores on the server, but values larger than that can also lead to faster restore times in many cases. Of course, values that are too high will lead to decreased performance because of thrashing.
此选项仅支持自定义和目录归档格式。输入必须是常规文件或目录（而不是管道）。发出脚本而不是直接连接到数据库服务器时，将忽略此选项。另外，不能将多个作业与--single-transaction选项一起使用。
Only the custom and directory archive formats are supported with this option. The input must be a regular file or directory (not, for example, a pipe). This option is ignored when emitting a script rather than connecting directly to a database server. Also, multiple jobs cannot be used together with the option --single-transaction.
链接：
这篇关于复制Postgresql数据库的更快方法（或最佳方法）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
