CODEWITHSUNDEEP
postgresqldumpremote-serverpg-restore
Danila Zharenkov
Danila Zharenkov

Reputation: 1863

Restore dump on the remote machine

I've got my own machine with postgres dmp file, which I want to restore on the remote virtual machine (e.g. ip is 192.168.0.190 and postgres port is 5432) in my network. Is it possible to restore this dump using pg_restore without copying dump to remote machine? Because the size of dump about 12GB and the disk space on the virtual machine is 20GB. Thanks

Upvotes: 28

Views: 48249

Answers (5)

Franco S
Franco S

Reputation: 11

I run this and works to 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.

Upvotes: 1

Dr Manhattan
Dr Manhattan

Reputation: 14097

An example for a remote RDS instance 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)

Upvotes: 9

plomovtsev
plomovtsev

Reputation: 542

Alternatively, you can use psql:

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

Upvotes: 20

Van
Van

Reputation: 11

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

Upvotes: 1

nif
nif

Reputation: 3442

You can run a restore over the network without copying the dump to the remote host.

Just invoke pg_restore with -h <hostname> and -p <port> (and probably -U <username> to authenticate as different user) on the host you got the dump file, for example:

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

References:

Upvotes: 52

Related Questions