I'm trying to pg_dump a SQL database on a remote server in our DMZ. There are 2 problems.
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.
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?
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).
Restoring the data from pg_dump doesn't overwrite the data but it appends the data to the original database. Bookmark this question.
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 .
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With