pg_dump postgres database from remote server when port 5432 is blocked

postgresql ssh remote-server pg-dump
Anthony McGovern picture Anthony McGovern · Apr 15, 2015 · Viewed 85k times · Source

I'm trying to pg_dump a SQL database on a remote server in our DMZ. There are 2 problems.

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.bak won't work due to space issues.

2) I also can't run the other version of pg_dump command to dump database from remote server to local server using: 

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

Does anyone know if what i am trying to achieve is possible?

Answer

OkieOth picture OkieOth · Apr 15, 2015

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 user@remote_machine "pg_dump -U dbuser -h localhost -C --column-inserts" \
 > backup_file_on_your_local_machine.sql

Related questions

Can't connect to Postgresql on port 5432

I have PostgreSQL 9.3 installed on a server running Ubuntu Server 14.04. If I ssh into the server via terminal, I'm able to connect with psql. But when I try to configure pgAdmin III to do the remote connection, I get: Server …

Read More
PostgreSQL via SSH Tunnel

I'd like to use RazorSQL to connect to my database which is running on a remote server. I create a SSH tunnel on my localhost with the following command: ssh -L 1111:remote.server.com:5432 [email protected] I configure …

Read More
Connecting to PostgreSQL database through SSH tunneling in Python

I am trying to connect to a server remotely and then access it's local database with Python. I am successfully connecting to the server, although I can't seem to connect to the database on the server. My code is below: …

Read More