Copying Postgresql DB dump from remote server to local

Question

I want to take a DB dump from a remote server and then copy this dump to my local. I tried couple of commands but didn't worked.

Lastly I tried the command below;

pg_dump  -h 10.10.10.70 -p 5432  -U postgres -d mydb   | gzip > db1.gz

I succesffully take the DB and tried with restore from Pgadmin, it gives;

pg_restore: error: input file appears to be a text format dump. Please use psql

But at this point I can't use psql, I have to use Pgadmin and not sure if I'm able to get successfully DB dump to my local. I mean I can't verify with restore.

How can I take DB dump from remote server to my local?

Thanks!

Laurenz Albe · Accepted Answer · 2021-04-15 13:14:33Z

3

Use the "custom" format:

pg_dump -F c -h 10.10.10.70 -p 5432 -U postgres -f mydb.dmp mydb

That can be restores with pg_restore and hence with pgAdmin.

You do not have to use pgAdmin. pgAdmin uses pg_restore, and there is nothing that keeps you from using it too.

answered Apr 15, 2021 at 13:14

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yatta Over a year ago

One last question, how can I automate the command with password? I tried "pg_dump -F c -h 10.10.10.70 -p 5432 -U postgres -W MYPASSWORD -f mydb.dmp mydb" but it gives "too many arguments" error.

Laurenz Albe Over a year ago

Use a password file.

Collectives™ on Stack Overflow

Copying Postgresql DB dump from remote server to local

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related