将PostgreSQL数据库复制到另一个服务器

Question

我想将生产环境的PostgreSQL数据库复制到开发环境服务器。有什么最快、最简单的方法可以实现这一目标？

- Robin Barnes

Answer 1

你不需要创建中间文件。可以直接进行操作。

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来连接到远程主机。

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

如Kornel所说，如果要进行压缩处理，则无需转储到中间文件，可以使用压缩隧道。

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

或者

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

但是这个解决方案也需要在两端获得会话。

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

- Ferran

---

无需中间文件 - 您可以使用压缩的 SSH 隧道或者简单地使用管道：pg_dump | bzip2 | ssh "bunzip2 | pg_restore" - Kornel

如果您使用bzip2，请关闭ssh压缩以加快传输速度！ - lzap

如果我需要从生产环境中将数据 拉取 到开发环境中，如何进行高效的工作？我已经在开发环境中建立了一个到生产环境的SSH连接。那么这个命令是不是可以实现：ssh remoteuser@remotehost "pg_dump -C dbname | bzip2" | bunzip2 | psql dbname？ - Jeromy French

这是我所做的：(1) 使用 pg_dump -C -h remotehost -U remoteuser x 命令将数据库 x 导出，再使用 psql -h localhost -U localuser 命令导入本地数据库；(2) 删除数据库 y；(3) 使用 psql -U postgres -c 'ALTER DATABASE "x" RENAME TO "y"' 命令将数据库 x 重命名为 y。 - Darin Peterson

如果两个服务器都要求密码，则不能使用此命令。它们会同时要求密码，而输入的密码将始终发送到错误的实例，这是根据墨菲定律（已确认两次）的结果。 - ygoe

Answer 2

pg_dump the_db_name > the_backup.sql

然后将备份文件复制到您的开发服务器上，并使用以下命令进行还原：

psql the_new_dev_db < the_backup.sql

- unmounted

---

有人告诉我，这可能会出现问题——权限问题会导致转储或还原在触发器触发时中断。 - Robin Barnes

如果存在问题，就必须加以解决。没有详细了解“某人”做了什么的情况下，没有人能够证实或否认这种说法。 - user80168

请使用pg_dump命令的--no-owner标志。这将跳过问题，本帖子的第一个编辑使用了它 -- 但后来我想您可能需要更精确地保持原始数据库的一致性。 - unmounted

对我来说，上述方法的操作如下：使用pg_dump命令将数据库模式和数据转储到指定目录下的文件中：pg_dump -C -h主机名 -U用户名 数据库名称 > /任意目录/转储模式和数据文件。还原时可使用以下命令：psql -h主机名 -U用户名 数据库名称 < 转储模式和数据文件。 - Ali Raza Bhayani

那真是让我省了很多麻烦。我使用Google Drive在机器之间移动文件。由于我已经在新机器上拥有数据库（但是为空），所以我遇到了很多重复键错误。不过，这只是一个开发环境，并没有造成任何损害。 - Chris Mendla

Answer 3

使用pg_dump，然后根据您选择的-Fp或-Fc选项，使用psql或pg_restore。

使用示例：

ssh production
pg_dump -C -Fp -f dump.sql -U postgres some_database_name
scp dump.sql development:
rm dump.sql
ssh development
psql -U postgres -f dump.sql

- user80168

---

-Fp 和 -Fc 之间有什么区别？ - Pithikos

-F，--format=c|d|t|p 输出文件格式（自定义，目录，tar，纯文本（默认）） - p13rr0m

-Fc 生成压缩且更小的文件。它可以与 -j 选项并行还原，例如 pg_restore -j 8 -d dbname dump.sql。-Fd “目录”格式还支持并行转储。 - Sergey Ponomarev

Answer 4

如果你想在不同版本之间迁移（例如你更新了Postgres，并且在localhost：5432上运行9.1和在localhost：5434上运行9.3），可以运行以下命令：

pg_dumpall -p 5432 -U myuser91 | psql -U myuser94 -d postgres -p 5434

查看迁移文档。

- Eric H.

---

我被要求输入 (myuser91/postgres) 密码多次，有没有办法只需输入一次密码？ - Martin Weber

@MartinWeber 根据此文档 https://www.postgresql.org/docs/9.4/static/libpq-pgpass.html 创建一个 .pgpass 文件。 - Scott Warren

如果它们具有相同的端口，该怎么办？ - ggnoredo

如果它们在不同的服务器上，您可以使用-h来指定主机。 - Haroldo_OK

Answer 5

pg_basebackup现在似乎是更好的方式，特别是对于大型数据库。

您可以从具有相同或旧的主版本的服务器复制数据库。或者更准确地说：

pg_basebackup适用于相同或旧的主版本的服务器，最低版本为9.1。但是，WAL流模式(-X stream)仅适用于9.3及更高版本的服务器，当前版本的tar格式模式(--format=tar)仅适用于9.5或更高版本的服务器。

因此，您需要在源服务器上执行以下操作：

1. listen_addresses = '*' 可以让目标服务器连接。请确保端口5432已经开放。
2. 至少有1个可用的复制连接: max_wal_senders = 1 (-X fetch)，-X stream (PostgreSQL 12默认值)或更多。
3. wal_level = replica 或更高才能设置 max_wal_senders > 0。
4. pg_hba.conf中添加 host replication postgres DST_IP/32 trust 条目。这将允许来自 DST_IP 的任何人访问 pg 集群。您可能需要采取更安全的选项。

Changes 1, 2, 3需要重新启动服务器，更改4需要重新加载。

在目标服务器上：

# systemctl stop postgresql@VERSION-NAME
postgres$ pg_basebackup -h SRC_IP -U postgres -D VERSION/NAME --progress
# systemctl start postgresql@VERSION-NAME

- Reshad user2701173

---

你能在答案中提供更多细节，比如一个例子吗？ - Magnilex

只有在两台机器具有相同的PG版本时才能起作用。 - s.m.

很少有可能在开发和生产中使用不同的数据库版本。上一次，我与其中一位队友进行了一些不愉快的对话，因为她试图提交一个问题，即某些代码在 PG 9.6 中无法工作，而我们当时在生产中使用的是 9.5。基础备份速度更快。如果需要，那么 pg_upgrade 是正确的选择。 - Zorg

您可能希望迁移到新版本，而且不想停止使用PostgreSQL。 - x-yuri

很有可能，每当您升级数据库时，您会先在开发和测试环境上进行升级，然后再在生产环境上进行。 - andrew lorien

Answer 6

被接受的答案是正确的，但是如果你想避免交互式地输入密码，可以使用这个：

PGPASSWORD={{export_db_password}} pg_dump --create -h {{export_db_host}} -U {{export_db_user}} {{export_db_name}} | PGPASSWORD={{import_db_password}} psql -h {{import_db_host}} -U {{import_db_user}} {{import_db_name}}

- zoran

---

这是我找到这个信息的唯一来源，好提示。 - jaycarleton

有办法让这个命令支持多线程吗？ - CodeGuru

如果您使用公共IP连接Postgres，则必须添加选项“-p”以指定主机端口，例如：-h {{export_db_host}} -p {{export_db_port}}。 - Bona Ws

你也可以使用 --dbname={{import_db_host}} 来指定数据库名称。 - Bona Ws

我可以在PowerShell中使用这个语法吗？我需要把密码放在引号里吗？@zoran - Learner

Answer 7

使用要备份的数据库名称运行此命令，以获取数据库的转储。

 pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

 eg. pg_dump -U postgres mydbname -f mydbnamedump.sql

现在将此转储文件 scp 到您想要复制数据库的远程机器。

eg. scp mydbnamedump.sql user01@remotemachineip:~/some/folder/

在远程机器上，在~/some/folder中运行以下命令来还原数据库。

 psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

 eg. psql -U postgres -d mynewdb -f mydbnamedump.sql

- user01

---

如果你无法连接到 PSQL，如何备份呢？是否有文件系统的方法来实现这一点？例如：如果你的服务器配置/可执行文件感染了病毒。 - ullfindsmit

Answer 8

---

备份你的数据库: pg_dump 数据库名称 > 备份文件.sql

---

还原你的数据库: psql 数据库名称 < 备份文件.sql

- MisterJoyson

---

这在你远程连接源和目标数据库服务器时也可以工作吗？例如，我在我的工作站上，在不同的服务器上运行着两个在Docker容器中的pg服务器之间复制一个数据库（不是在我的工作站上）。 - majorgear

Answer 9

我很努力地尝试了很多方法，最终让我成功在Rails 4上运行的方法是：

在您的旧服务器上

sudo su - postgres
pg_dump -c --inserts old_db_name > dump.sql

我不得不使用postgres Linux用户创建转储。同时，我必须使用“-c”强制在新服务器上创建数据库。“--inserts”告诉它使用INSERT（）语法，否则对我来说无法正常工作:(

然后，在新服务器上，简单执行以下操作：

sudo su - postgres
psql new_database_name < dump.sql

为了在服务器之间传输dump.sql文件，我简单地使用"cat"打印内容，然后使用"nano"重新创建并复制粘贴内容。

此外，我在两个数据库上使用的角色不同，因此我不得不在转储中查找和替换所有所有者名称。

- pastullo

Answer 10

让我分享一个Linux shell脚本，可以将您的表数据从一个服务器复制到另一个PostgreSQL服务器。

参考此博客：http://www.dbrnd.com/2017/03/postgresql-shell-script-to-copy-table-data-from-one-server-to-another-server-psql-copy-command-data-migration/ 用于在PostgreSQL服务器之间进行数据迁移的Linux Bash Shell脚本：

#!/bin/bash
psql \
    -X \
    -U user_name \
    -h host_name1 \
    -d database_name \
    -c "\\copy tbl_Students to stdout" \
| \
psql \
    -X \
    -U user_name \
    -h host_name2 \
    -d database_name \
    -c "\\copy tbl_Students from stdin"

我正在迁移数据，请在目标/第二数据库服务器上创建一个空表。

这是一个实用脚本。此外，您可以通过添加host_name、database_name、table_name和其他参数等来修改脚本以供通用使用。

- Anvesh