entityMap|0|type|LINK|mutability|MUTABLE|data|url|https://www.citusdata.com/blog/2018/02/21/three-approaches-to-postgresql-replication/|blocks|key|dunr2|text|您可以进行复制，一旦数据库对齐，选择从属数据库(新复制的那个)为主数据库，并开始在此数据库上写入数据。|unstyled|depth|inlineStyleRanges|entityRanges|3lceb|例如，Here，您可以开始学习postgres复制。|offset|length|9ps52^0|0|3|4|0|0^^$0|$1|$2|3|4|5|6|$7|8]]]|9|@$A|B|C|D|2|E|F|N|G|@]|H|@]|6|$]]|$A|I|C|J|2|E|F|O|G|@]|H|@$K|P|L|Q|A|R]]|6|$]]|$A|M|C|-4|2|E|F|S|G|@]|H|@]|6|$]]]]

You can do a replica and once the databases have aligned, elect the slave (the new replicated one) as master and start writing on this one.

<a href="https://www.citusdata.com/blog/2018/02/21/three-approaches-to-postgresql-replication/" rel="nofollow noreferrer">Here</a>, for example, you can start learning about postgres replication.

entityMap|blocks|key|p080|text|这将取决于瓶颈是什么。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5etue|您可以改用pg_basebackup，这样就不需要像pg_dump那样计算新索引。但是，你可能会传输更多的数据，所以这可能会减慢你的速度，而不是加快你的速度。|offset|length|style|CODE|3f9p6|如果网络带宽是一个严重的瓶颈，那么您可以登录到源服务器(123.123.123.123)并执行pg_basebackup+-Ft+-D-+-Xnone+...+%7C+pxz+>+backup.tar.xz，然后使用scp或rsync将压缩数据传输到新服务器。您将必须有一个wal存档，以便新服务器可以获取它丢失的任何WAL文件。可以随意调整复制槽，这样您就不需要这样做了，但是pg_basebackup并没有让您轻松做到这一点。|3pka8|xz非常慢。如果你有很多CPU，pxz就不会那么慢，但仍然相当慢。所以这样做是值得的，因为网络比pxz还要慢。如果网络不是很慢，您可以使用更快但不太彻底的压缩方法，如gzip或其并行版本pigz。|6nu72|你也可以用pg_dump+...+%7C+pxz+>+dump.sql.xz做同样的事情。|fm5dp|如果您不想将压缩文件存储在原始服务器上，您可以使用ssh+123.123.123.123+"pg_dump+...%7Cpxz"+%7C+xzcat+%7C+psql+...之类的东西在网络上动态地传输它。(或者你也可以用pg_basebackup来做同样的事情)。但这里的问题是，您不能从网络故障中恢复，而必须从头开始重复整个过程。这就是为什么我更喜欢将文件保存在原始服务器上，如果有空间的话。|1bc51^0|0|5|D|Q|7|0|1B|1I|0|0|5|V|0|P|1K|0^^$0|$]|1|@$2|3|4|5|6|7|8|R|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|S|9|@$E|T|F|U|G|H]|$E|V|F|W|G|H]]|A|@]|B|$]]|$2|I|4|J|6|7|8|X|9|@$E|Y|F|Z|G|H]]|A|@]|B|$]]|$2|K|4|L|6|7|8|10|9|@]|A|@]|B|$]]|$2|M|4|N|6|7|8|11|9|@$E|12|F|13|G|H]]|A|@]|B|$]]|$2|O|4|P|6|7|8|14|9|@$E|15|F|16|G|H]]|A|@]|B|$]]|$2|Q|4|-4|6|7|8|17|9|@]|A|@]|B|$]]]]

It is going to depend on what the bottleneck is.

You could use <code>pg_basebackup</code> instead, which spares you the need to compute the new indexes the way <code>pg_dump</code> does. But, you might be transfering a lot more data, so that could slow you down more than it speeds you up.

If network bandwidth is a severe bottleneck, then you could log onto the source server (123.123.123.123) and do <code>pg_basebackup -Ft -D- -Xnone ... | pxz &gt; backup.tar.xz</code>, then use scp or rsync to transfer the compressed data to the new server. You will have to have a wal archive so the new server can fetch any WAL files it missed. It might be possible to juggle replication slots so you don't need this, but pg_basebackup doesn't make it easy to do that.

xz is very slow. pxz is less slow if you have many CPUs, but still pretty slow. So doing that is only worthwhile is the network is slower still than pxz is. If the network is not quite that slow, you could use a faster but less thorough compression method, like gzip or its parallel version pigz.

You could also do the same thing with <code>pg_dump ... | pxz &gt; dump.sql.xz</code>.

If you don't want so store the compressed file on the original server, you could stream it over the network on the fly, using something like <code>ssh 123.123.123.123 "pg_dump ...|pxz" | xzcat | psql ...</code>. (Or you could do the same thing with pg_basebackup instead). But the problem here is that you cannot resume from a network glitch, but have to repeat the whole thing from scratch. That is why I prefer to save the file on the original server if there is room for it.

I need to move a quite large database of a little more than 100GB from an old remote server to a new remote server.

I am connecting via ssh to the new server and use the following command from there, but I don't think it is the best/fastest way to do it.

<pre><code>pg_dump -C -h 123.123.123.123 -U pgadmin -W -d db_name | psql -h 127.0.0.1 -U pgadmin -W -d db_name
</code></pre>

Is there a better way to do it ? I am thinking about using some sort of data compression in order to reduce the data transferred from the two machines, but I don't know how to do it and if it is useful.

Fastest way to move a PostgreSQL database on a remote server

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

 我需要将一个100 an多一点的大型数据库从一个旧的远程服务器移动到一个新的远程服务器。 我正在通过ssh连接到新的服务器，并在那里使用以下命令，但我不认为这是最好/最快的方法。 pg_dump -C -h 123.123.123.123 -U pgadmin -W -d db_name | psql -h 127....

问在远程服务器上移动PostgreSQL数据库的最快方法
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在远程服务器上移动PostgreSQL数据库的最快方法EN