当端口5432被阻断时，如何从远程服务器上进行PostgreSQL数据库的pg_dump操作

Question

我正在尝试在我们 DMZ 中的远程服务器上使用 pg_dump 命令备份 SQL 数据库，但遇到了两个问题：

1. 远程服务器上的可用空间不多，因此无法运行常规命令将数据库备份到本地，例如：pg_dump -C database > sqldatabase.sql.bak。

2. 由于服务器位于 DMZ 中且端口 5432 被阻止，因此我也不能运行另一个版本的 pg_dump 命令来将数据库从远程服务器转储到本地服务器中。

我希望能够将数据库备份并立即保存到远程服务器中（通过 ssh 或其他方式）。我尝试过以下命令： pg_dump -C testdb | ssh [e-mail] | > /home/admin/testdb.sql.bak，请问是否可能实现这一目标呢？

- Anthony McGovern

---

【未经测试】（1）在您的本地计算机上设置一个netcat监听器（使用未被使用或阻止的端口号），例如netcat -l -p 1234 > thefile &。（2）从远程计算机上执行：pg_dump ... | netcat your.local.ip 1234。 - wildplasser

Answer 1

您可以使用ssh连接到远程服务器，并使用连接进行pg_dump调用，并将输出发送回本地机器的标准输出。

ssh user@remote_machine "pg_dump -U dbuser -h localhost -C --column-inserts" \
 > backup_file_on_your_local_machine.sql

- OkieOth

---

这比我的 netcat hack 简单多了。 - wildplasser

一个小提示是，我会用>替换>>，以避免在文件不为空的情况下写到末尾。 - Nickolay Kondratenko

我们需要指定数据库名称吗？ - Asnad Atta

@AsnadAtta 你可以这样做，重要的是通过ssh执行命令并从stdout获取结果的想法。基本上，pg_dump的所有参数都可以使用。 - OkieOth

尝试在Windows上执行此命令时，我必须删除\才能使其正确。 - Jose Kj

Answer 2

让我们使用pg_dump从远程postgresql数据库创建备份：

pg_dump -h [host address] -Fc -o -U [database user] <database name> > [dump file]

稍后可以使用以下方法在同一远程服务器上还原它：

sudo -u postgres pg_restore -C mydb_backup.dump

例：

pg_dump -h 67.8.78.10 -Fc -o -U myuser mydb > mydb_backup.dump

完整备份（所有数据库和对象）

pg_dumpall -U myuser -h 67.8.78.10 --clean --file=mydb_backup.dump

从pg_dumpall --clean恢复：

psql -f mydb_backup.dump postgres #it doesn't matter which db you select here

Copied from: https://codepad.co/snippet/73eKCuLx

- Kishore Relangi

---

这并没有回答这个问题。限制在于你无法通过5432连接到远程服务器。 - jgomo3

我在macos上遇到了pg_dump: invalid option -- o的错误。 - DevonDahon

选项是大写字母“O”：）。文档对此选项的解释是“不要输出命令以设置对象的所有权以匹配原始数据库。默认情况下，pg_dump会发出ALTER OWNER或SET SESSION AUTHORIZATION语句来设置创建的数据库对象的所有权。除非由超级用户（或拥有脚本中所有对象的相同用户）启动脚本，否则这些语句将在运行脚本时失败。为了使任何用户都可以恢复脚本，但将该用户指定为所有对象的所有者，请指定-O。” - Shantanu

请问您能否解释一下所有的参数？ - fIwJlxSzApHEZIl

这种方法适用于与服务器机器在同一本地网络中的本地机器。 - Gin

-F, --format=c|d|t|p 输出文件格式（自定义，目录，tar，纯文本（默认））和 -O, --no-owner 在纯文本格式中跳过对象所有权的恢复 - undefined

Answer 3

您可以尝试将表的部分内容导出到本地文件中，方法如下（假设您的本地机器已安装 psql）：

psql -h ${db_host} -p 5432 -U ${db_user} -d ${db_name} \
-c "\copy (SELECT * FROM my_table LIMIT 10000) to 'some_local_file.csv' csv;"

您可以像这样将导出的CSV文件导入到另一个数据库中：

COPY my_table FROM '/path/to/some_local_file.csv' WITH (FORMAT csv);

- nybon

Answer 4

已经提到了一个可能的解决方案——通过ssh传输。

您还可以使您的数据库服务器监听公共inet地址，在pg_hba.conf中添加hostssl条目以备份机器，也许为了安全起见配置客户端证书，然后在客户端/备份机器上使用pg_dump -h dbserver.example.com ...运行转储即可。

这对于无人值守备份来说更简单。

有关连接配置（sslmode），请参见支持的环境变量。

- Str.

---

最好展示完整的命令，因为我不明白你如何以这种方式还原到目标机器（当然你可以先转储再还原，但这不符合问题）。 - gilad905

抱歉，我没有简单的命令示例，我的设置非常复杂，包括用于ssh（作为ssh管道替代方案）和psql的配置文件以及用于连接的服务名称。命令是问题吗？您只需要将主机名添加到命令行即可。我更认为问题/任务是服务器上TLS的设置！一旦您完成了这个步骤，就只需要使用不同于localhost的主机名进行普通的命令行psql和pg_dump。 - Str.

Answer 5

如果您想定期备份远程服务器内部容器中的PostgreSQL数据库到本地主机，可以使用pg_dump通过ssh实现，这对您非常有用。请参考以下链接：

https://github.com/omidraha/periodic-pgdump-over-ssh

- Omid Raha