将 PostgreSQL 数据库复制到另一台服务器 [英] Copying PostgreSQL database to another server

问题描述

我希望将生产 PostgreSQL 数据库复制到开发服务器.执行此操作的最快、最简单的方法是什么?

I'm looking to copy a production PostgreSQL database to a development server. What's the quickest, easiest way to go about doing this?

推荐答案

您不需要创建中间文件.你可以这样做

You don't need to create an intermediate file. You can do

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

或

pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

使用 psql 或 pg_dump 连接到远程主机.

using psql or pg_dump to connect to a remote host.

对于大型数据库或慢速连接，转储文件和传输压缩文件可能会更快.

With a big database or a slow connection, dumping a file and transfering the file compressed may be faster.

正如 Kornel 所说没有必要转储到中间文件，如果你想压缩工作，你可以使用压缩隧道

As Kornel said there is no need to dump to a intermediate file, if you want to work compressed you can use a compressed tunnel

pg_dump -C dbname | bzip2 | ssh  remoteuser@remotehost "bunzip2 | psql dbname"

或

pg_dump -C dbname | ssh -C remoteuser@remotehost "psql dbname"

但是这个解决方案还需要在两端都获得一个会话.

but this solution also requires to get a session in both ends.

注意: pg_dump 用于备份，psql 用于恢复.因此，这个答案中的第一个命令是从本地复制到远程，第二个命令是从远程复制到本地.更多 -> https://www.postgresql.org/docs/9.6/app-pgdump.html

Note: pg_dump is for backing up and psql is for restoring. So, the first command in this answer is to copy from local to remote and the second one is from remote to local. More -> https://www.postgresql.org/docs/9.6/app-pgdump.html

这篇关于将 PostgreSQL 数据库复制到另一台服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！