Geek Answers Handbook

Restore a dump to a remote machine

I have my own machine with the dmp postgres file that I want to recover on a remote virtual machine (for example, ip 192.168.0.190, and the postgres port is 5432) on my network. Is it possible to restore this dump using pg_restore without copying the dump to the remote machine? Since the dump size is about 12 GB and the disk space on the virtual machine is 20 GB. Thanks

+7
remote-server postgresql dump
source share
5 answers

You can start network recovery without copying the dump to the remote host.

Just run pg_restore with -h <hostname> and -p <port> (and possibly -U <username> for authentication as another user) on the host, you get a dump file, for example:

 pg_restore -h 192.168.0.190 -p 5432 -d databasename -U myuser mydump.dump

Literature:

+13
source share

Alternatively you can use psql:

psql -h 192.168.0.190 -p 5432 -d <dbname> -U <username> -W -f mydump.dump

+2
source share

You can pass the password parameter to the script before "pg_restore" using PGPASSWORD = "your_database_password"

0
source share

Remote RDS Instance Example on AWS

 psql -h mydb.dsdreetr34.eu-west-1.rds.amazonaws.com -p 5432 -d mydbname -U mydbuser -W -f mydatabase-dump.sql -f, --file=FILENAME execute commands from file, then exit -W, --password force password prompt (should happen automatically)
0
source share

I run this and work with me:

  scp backup.dump user@remotemachine :~ ssh user@remotemachine "pg_restore -h localhost -p 5432 -U databaseuser -W -F c -d databasename -v backup.dump"

You can write a script to automate this.

0
source share

More articles:


All Articles