postgresql 当端口 5432 被阻塞时，来自远程服务器的 pg_dump postgres 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29648309/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pg_dump postgres database from remote server when port 5432 is blocked
提问by Anthony McGovern
I'm trying to pg_dump a SQL database on a remote server in our DMZ. There are 2 problems.
我正在尝试在我们的 DMZ 中的远程服务器上 pg_dump 一个 SQL 数据库。有2个问题。
1) there is n't a lot of space left on the remote server so the normal command run to locally backup the database
pg_dump -C database > sqldatabase.sql.bakwon't work due to space issues.
1) 远程服务器上没有太多空间，因此pg_dump -C database > sqldatabase.sql.bak由于空间问题，运行本地备份数据库的正常命令
将不起作用。
2) I also can't run the other version of pg_dump command to dump database from remote server to local server using:
2）我也无法运行其他版本的 pg_dump 命令来使用以下命令将数据库从远程服务器转储到本地服务器：
pg_dump -C -h remotehost -U remoteuser db_name | psql localhost -U localuser db_name
as the server is in our DMZ and port 5432 is blocked. What I'm looking to see is if it is possible to pg_dump the database and immediatly save it (ssh or some other form) as a file to a remote server.
What I was trying was: pg_dump -C testdb | ssh [email protected] | > /home/admin/testdb.sql.bak
因为服务器在我们的 DMZ 中并且端口 5432 被阻止。我希望看到的是是否可以 pg_dump 数据库并立即将其（ssh 或其他形式）作为文件保存到远程服务器。我正在尝试的是： pg_dump -C testdb | ssh [email protected] | > /home/admin/testdb.sql.bak
Does anyone know if what i am trying to achieve is possible?
有谁知道我想要实现的目标是否可行？
回答by OkieOth
You can connect with ssh to your remote server, do with the connect the pg_dump call and send the output back to stdout of local machine.
您可以使用 ssh 连接到远程服务器，使用连接 pg_dump 调用并将输出发送回本地机器的 stdout。
ssh user@remote_machine "pg_dump -U dbuser -h localhost -C --column-inserts" \
> backup_file_on_your_local_machine.sql
回答by Kishore Relangi
let's create a backup from remote postgresql database using pg_dump:
让我们使用 pg_dump 从远程 postgresql 数据库创建一个备份：
pg_dump -h [host address] -Fc -o -U [database user] <database name> > [dump file]
later it could be restored at the same remote server using:
稍后可以使用以下方法在同一远程服务器上恢复它：
sudo -u postgres pg_restore -C mydb_backup.dump
Ex:
前任：
pg_dump -h 67.8.78.10 -Fc -o -U myuser mydb > mydb_backup.dump
complete (all databases and objects)
完整（所有数据库和对象）
pg_dumpall -U myuser -h 67.8.78.10 --clean --file=mydb_backup.dump
restore from pg_dumpall --clean:
从 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
回答by nybon
You can try to dump part of the table to a file in your local machine like this (assume your local machine has psqlinstalled):
您可以尝试像这样将表的一部分转储到本地机器中的文件中（假设您的本地机器已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;"
And you can import the exported csv into another db later like this:
您可以稍后将导出的 csv 导入另一个数据库，如下所示：
COPY my_table FROM '/path/to/some_local_file.csv' WITH (FORMAT csv);
回答by Str.
One possible solution - pipe through ssh - has been mentioned.
已经提到了一种可能的解决方案 - 通过 ssh 管道。
You also could make your DB server listen on the public inet address, add a hostssl entry for your backup machine to pg_hba.conf, maybe configure a client certificate for security, and then simply run the dump on the client/backup machine with pg_dump -h dbserver.example.com ...
你也可以让你的数据库服务器监听公共 inet 地址，为你的备份机器添加一个 hostssl 条目到 pg_hba.conf，也许配置一个客户端证书以确保安全，然后简单地在客户端/备份机器上运行转储 pg_dump -h dbserver.example.com ...
This is simpler for unattended backups.
这对于无人值守备份更简单。
For the configuration of the connection (sslmode) see also the supported environment variables.
有关连接 (sslmode) 的配置，另请参阅支持的环境变量。
回答by Omid Raha
If you would like to periodically backup a database PostgreSQL that is inside of a container in the remote server to your local host by using pg_dumpover ssh, this is useful for you:
如果您想使用pg_dumpover定期将远程服务器中容器内的数据库 PostgreSQL 备份到本地主机ssh，这对您很有用：
