Search code examples
postgresqlpsqlpg-dumppostgresql-13

Copying Postgresql DB dump from remote server to local

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!

Solution

  • 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.