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

您不需要创建中间文件。你可以做

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

---

pg_dump the_db_name > the_backup.sql

然后将备份复制到您的开发服务器，使用以下命令恢复：

psql the_new_dev_db < the_backup.sql

---

使用 pg_dump，然后使用 psql 或 pg_restore - 取决于您选择 -Fp 还是 -Fc 选项到 pg_dump。

使用示例：

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

---

如果您希望在版本之间迁移（例如，您更新了 postgres 并在 localhost:5432 上运行 9.1 并在 localhost:5434 上运行 9.3），您可以运行：

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

查看 migration docs。

---

pg_basebackup 现在似乎是更好的方法，尤其是对于大型数据库。

您可以从具有相同或更旧主要版本的服务器复制数据库。或 more precisely：

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

为此，您需要在源服务器上：

listen_addresses = '*' 以便能够从目标服务器进行连接。确保为此打开端口 5432。至少 1 个可用的复制连接：max_wal_senders = 1（-X 提取），2 用于 -X 流（PostgreSQL 12 的默认值），或更多。 wal_level = replica 或更高，以便能够设置 max_wal_senders > 0。 pg_hba.conf 中的主机复制 postgres DST_IP/32 信任。这将向 DST_IP 机器上的任何人授予对 pg 集群的访问权限。您可能想求助于更安全的选择。

更改 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

---

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

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}}

---

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

 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

---

转储您的数据库：pg_dump database_name_name > backup.sql

重新导入您的数据库：psql db_name < backup.sql

---

我挣扎了很多，最终使我能够使用 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”来重新创建它并复制粘贴内容。

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

---

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

Reference taken from this blog:

用于 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 等添加参数

---

这是使用 pg_basebackup 的示例

我选择这条路线是因为它备份了整个数据库集群（用户、数据库等）。

我将其作为解决方案发布在此处，因为它详细说明了我必须采取的每一步，在阅读此处的其他答案并进行更多研究后，请随时添加建议或改进。

对于 Postgres 12 和 Ubuntu 18.04，我必须执行以下操作：

在当前运行数据库的服务器上：

更新 pg_hba.conf，我位于 /etc/postgresql/12/main/pg_hba.conf

添加以下行（将 192.168.0.100 替换为要将数据库复制到的服务器的 IP 地址）。

host  replication  postgres  192.168.0.100/32  trust

更新 postgresql.conf，我位于 /etc/postgresql/12/main/postgresql.conf。添加以下行：

listen_addresses = '*'

重启postgres：

sudo service postgresql 重启

在要将数据库集群复制到的主机上：

sudo service postgresql stop sudo su root rm -rf /var/lib/postgresql/12/main/* exit sudo -u postgres pg_basebackup -h 192.168.0.101 -U postgres -D /var/lib/postgresql/12/main/ sudo服务 postgresql 启动

大图 - 停止服务，删除数据目录中的所有内容（我的在 /var/lib/postgreql/12 中）。此目录的权限为 drwx------，用户和组为 postgres。我只能使用 root 来执行此操作，即使使用 sudo -u postgres 也不行。我不确定为什么。确保您在要将数据库复制到的新服务器上执行此操作！您正在删除整个数据库集群。

确保将 IP 地址从 192.168.0.101 更改为您从中复制数据库的 IP 地址。使用 pg_basebackup 从原始服务器复制数据。启动服务。

更新 pg_hba.conf 和 postgresql.conf 以匹配原始服务器配置 - 在您进行任何更改之前添加 replication 行和 listen_addresses 行（在我的照顾下，我必须添加通过 md5 本地登录的功能pg_hba.conf)。

请注意，可以在 documentation 中找到有关 max_wal_senders 和 wal_level 的注意事项。我不必对此做任何事情。

---

如果您对 GUI 更熟悉，可以使用 pgAdmin 软件。

连接到您的源服务器和目标服务器

右键单击源数据库 > 备份

右键单击目标服务器 > 创建 > 数据库。使用与源db相同的属性（可以通过右键>属性查看源db的属性）

右键单击创建的数据库 > 还原。

https://i.stack.imgur.com/7xCmt.png

---