Restore dump on the remote machine

Question

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

score 46 · Accepted Answer · answered Oct 15 '14 at 08:49

46

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:

pg_restore documentation

answered Oct 15 '14 at 08:49

nif

3,342
20
18

Yeah, you are right. I'm trying like this pg_restore -i -h 192.168.1.190 -p 5432 -U postgres -d MFC_sara -v /home/develop/BD_Dumps/Saratov.dmp ; But after this command it's needed to input some password for destination database. And I don't know it, on virtual machine I just create db like createdb -T template0 MFC_sara – Danila Zharenkov Oct 15 '14 at 08:55
1

If you want to access from a remote machine you will need password authentication (or any other non-local authentication). Create a user and assign privileges for the database. Or if this is no production environment, create a superuser with password: `createuser -P -s someusername` – nif Oct 15 '14 at 09:46

score 18 · Answer 2 · answered Jul 05 '17 at 13:00

18

Alternatively, you can use psql:

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

answered Jul 05 '17 at 13:00

plomovtsev

512
4
11

score 9 · Answer 3 · answered Oct 17 '17 at 10:34

9

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)

answered Oct 17 '17 at 10:34

Dr Manhattan

13,537
6
45
41

This worked! but inserting 10 rows at a time can we increase size – Rohit Parte Dec 10 '20 at 12:28

score 1 · Answer 4 · answered Jan 06 '16 at 18:27

1

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

answered Jan 06 '16 at 18:27

Van

11
1

score 1 · Answer 5 · edited Jan 29 '18 at 17:55

1

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.

edited Jan 29 '18 at 17:55

doganak

798
14
31

answered Jan 29 '18 at 17:09

Franco S

11
3

Restore dump on the remote machine

5 Answers5

Linked

Related