postgresql pg_dump 从远程服务器到本地主机
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30226192/
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 from remote server to localhost
提问by Al D
Hi can anyone help me dump from a postgreSQL database on a remote AWS server to a postgreSQL database on my local machine.
嗨，谁能帮我从远程 AWS 服务器上的 postgreSQL 数据库转储到本地机器上的 postgreSQL 数据库。
I've been trying to do it using the answer in thisstack post but it keeps failing.
我一直在尝试使用此堆栈帖子中的答案来做到这一点，但它一直失败。
The command I'm using is
我正在使用的命令是
pg_dump -C -h ssh [email protected] -U dev_user paycloud_dev | psql -h localhost -U dev_user paycloud_dev
But I keep getting the error
但我不断收到错误
pg_dump: too many command-line arguments (first is "paycloud_dev")
Can't figure out what I'm doing wrong
无法弄清楚我做错了什么
Just to add, dev_useris the role I've set up in postgreSQL on both the local machine and remote server. paycloud_devis the name of the database on both (owner is dev_user)
补充一点，dev_user是我在本地机器和远程服务器上的 postgreSQL 中设置的角色。paycloud_dev是两者上的数据库名称（所有者是dev_user）
Edit 1
编辑 1
Tried the command below as per a post that has since been deleted for some reason
根据由于某种原因已被删除的帖子尝试了以下命令
pg_dump -C -h ec2-59-16-143-85.eu-west-1.compute.amazonaws.com -U dev_user paycloud_dev | psql -h localhost -U dev_user paycloud_dev
This is now giving me the error
这现在给了我错误
pg_dump: [archiver (db)] connection to database "paycloud_dev" failed: could not connect to server: Connection refused
Is the server running on host "ec2-59-16-143-85.eu-west-1.compute.amazonaws.com" (59.16.143.85) and accepting
TCP/IP connections on port 5432?
I went on to AWS and noted that is the elastic ip of the server. I then tried the following (the private IP address)
我继续使用 AWS 并注意到这是服务器的弹性 ip。然后我尝试了以下（私有 IP 地址）
pg_dump -C -h 170.30.43.35 -U dev_user paycloud_dev | psql -h localhost -U dev_user paycloud_dev
This asks me for the password for paycloud_dev and when I enter it pauses for a good 2 or 3 minutes and comes back with:
这要求我输入 paycloud_dev 的密码，当我输入时，它会暂停 2 或 3 分钟，然后返回：
pg_dump: [archiver (db)] connection to database "paycloud_dev" failed: could not connect to server: Connection refused
Is the server running on host "170.30.43.35" and accepting
TCP/IP connections on port 5432?
I've tried editing the AWS security group to add a rule that accepts all traffic (port range 0-65535) but the same error is occurring.
我尝试编辑 AWS 安全组以添加接受所有流量（端口范围 0-65535）的规则，但发生了相同的错误。
Edit 2
编辑 2
Tried the following as per post by pokoli
根据 pokoli 的帖子尝试了以下操作
ssh [email protected] pg_dump -C -h -U dev_user paycloud_dev | psql -U dev_user paycloud_dev
It's not working though. It first asks me for the psql password for my laptop then before I can input anything, it gives an error.
虽然它不起作用。它首先询问我笔记本电脑的 psql 密码，然后在我输入任何内容之前，它给出了一个错误。
[sudo] password for alzer: pg_dump: too many command-line arguments (first is "paycloud_dev")
Try "pg_dump --help" for more information.
尝试“pg_dump --help”以获取更多信息。
Anyone?
任何人？
回答by pokoli
You have to connect with ssh to remote host, execute the dump and pipe it to your local machine. The following command should do:
您必须使用 ssh 连接到远程主机，执行转储并将其通过管道传输到您的本地机器。以下命令应该执行：
ssh [email protected] -C pg_dump -U dev_user paycloud_dev | psql -U dev_user paycloud_dev
The command will ask for password of both users if needed and the playcloud_devdatabase should exists on localhost, otherwise the dump will fail.
如果需要，该命令将询问两个用户的密码，并且playcloud_dev数据库应该存在于本地主机上，否则转储将失败。
回答by PRIHLOP
Try do this over ssh tunnel.
尝试通过 ssh 隧道执行此操作。
ssh -fT -L 5432:127.0.0.1:5432 %remote_user_login%@%your_aws_host% sleep 10
pg_dump -C -h localhost -U dev_user paycloud_dev | psql -h localhost -U dev_user paycloud_dev
First line create ssh tunnel and port mapping.
第一行创建 ssh 隧道和端口映射。
And view your AWS security settings in "Security Groups", may be you forgot opening ports.
并在“安全组”中查看您的 AWS 安全设置，可能是您忘记打开端口了。
