RE: pg_dump to a remote server

from the pg_dump docs...

...
-Z 0..9
--compress=0..9

Specify the compression level to use. Zero means no compression. For the custom archive format, this specifies compression of individual table-data segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file to be compressed, as though it had been fed through gzip; but the default is not to compress.
...

so perhaps running (on host2):
pg_dump -h host1 -Z 9 dbname | zcat | psql -h host2 dbname

will generate a compressed text output on host1, which is sent over the wire to host2 where it is locally uncompressed & fed into psql...

Brent Wood

Programme leader: Environmental Information Delivery
NIWA
DDI: +64 (4) 3860529

Brent Wood
Principal Technician - GIS and Spatial Data Management
Programme Leader - Environmental Information Delivery
+64-4-386-0529 | 301 Evans Bay Parade, Greta Point, Wellington | www.niwa.co.nz<http://www.niwa.co.nz>
[NIWA]<http://www.niwa.co.nz>
To ensure compliance with legal requirements and to maintain cyber security standards, NIWA's IT systems are subject to ongoing monitoring, activity logging and auditing. This monitoring and auditing service may be provided by third parties. Such third parties can access information transmitted to, processed by and stored on NIWA's IT systems.
________________________________________
From: Christoph Moench-Tegeder [cmt(at)burggraben(dot)net]
Sent: Tuesday, April 17, 2018 18:00
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: pg_dump to a remote server

## Ron (ronljohnsonjr(at)gmail(dot)com):

> > pg_dump -h host1 dbname | psql -h host2 dbname
>
> But that assumes --format=plain which will send a whole lot of
> uncompressed text across the wire.

You can also use pg_restore with standard input, i.e. pg_dump | pg_restore.

Regards,
Christoph

--
Spare Space.