Copying PostgreSQL database to another server

You don't need to create an intermediate file. You can do

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

or

pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

using psql or pg_dump to connect to a remote host.

With a big database or a slow connection, dumping a file and transfering the file compressed may be faster.

As Kornel said there is no need to dump to a intermediate file, if you want to work compressed you can use a compressed tunnel

pg_dump -C dbname | bzip2 | ssh  remoteuser@remotehost "bunzip2 | psql dbname"

or

pg_dump -C dbname | ssh -C remoteuser@remotehost "psql dbname"

but this solution also requires to get a session in both ends.

Note: pg_dump is for backing up and psql is for restoring. So, the first command in this answer is to copy from local to remote and the second one is from remote to local. More -> https://www.postgresql.org/docs/9.6/app-pgdump.html

pg_dump the_db_name > the_backup.sql

Then copy the backup to your development server, restore with:

psql the_new_dev_db < the_backup.sql

Use pg_dump, and later psql or pg_restore - depending whether you choose -Fp or -Fc options to pg_dump.

Example of usage:

ssh productionpg_dump -C -Fp -f dump.sql -U postgres some_database_namescp dump.sql development:rm dump.sqlssh developmentpsql -U postgres -f dump.sql