0

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!

yatta
  • 423
  • 1
  • 7
  • 22

1 Answers1

2

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 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. – yatta Apr 15 '21 at 14:17
  • 1
    Use a [password file](https://www.postgresql.org/docs/current/libpq-pgpass.html). – Laurenz Albe Apr 15 '21 at 14:35