Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

pg_dump postgres database from remote server when port 5432 is blocked

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

  1. there is not 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?

like image 385
Anthony McGovern Avatar asked Apr 15 '15 10:04

Anthony McGovern


People also ask

Does pg_dump block?

Description. pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).

Does pg_dump overwrite?

Restoring the data from pg_dump doesn't overwrite the data but it appends the data to the original database. Bookmark this question.

What does pg_dump command do?

The pg_dump command extracts a PostgreSQL database into a script file or another archive file. This utility is for backing up databases. The utility makes consistent backups even if the database is being used concurrently. Readers, writers, and other users won't be blocked from using the database while using pg_dump .


2 Answers

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 
like image 112
OkieOth Avatar answered Sep 24 '22 21:09

OkieOth


let's create a backup from remote postgresql database using pg_dump:

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:

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

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

like image 35
Kishore Relangi Avatar answered Sep 24 '22 21:09

Kishore Relangi